/// <summary>
/// 插入密码映射表
/// </summary>
/// <param name="passwordMapping">密码映射实体</param>
/// <returns>密码修改成功标号</returns>
public int AddPasswordMapping(PasswordMapping passwordMapping)
{
SqlParameter[] parameters = {
new SqlParameter("@userNo", SqlDbType.NChar,10),
new SqlParameter("@userName", SqlDbType.NChar,10),
new SqlParameter("@initializePassword", SqlDbType.NChar,4),
new SqlParameter("@termTag", SqlDbType.NVarChar,50),
new SqlParameter("@userType", SqlDbType.SmallInt,2)};
parameters[0].Value = passwordMapping.userNo;
parameters[1].Value = passwordMapping.userName;
parameters[2].Value = passwordMapping.initializePassword;
parameters[3].Value = passwordMapping.termTag;
parameters[4].Value = passwordMapping.userType;
SqlHelper.ExecuteNonQuery(conn, "spPasswordMappingAdd", parameters);
conn.Close();
return 1;
}
/// <summary>
/// 读取Excel数据并返回ExcelSheetData类型的集合给Dal进行处理
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="colsCount">记录列数</param>
/// <param name="FileUpload1"></param>
/// <returns>返回ExcelData实体类</returns>
public static ExcelData BllImportExcelData(string filePath, int[] colsCount, FileUpload FileUpload1)
{
//设定一个自定义ExcelData类型，用于返回全部的Excel数据
ExcelData excelData = null;
//出错的工作薄名称
string exceptionSheetName = string.Empty;
//出错的行号
int exceptionRowNo = 0;
////出错的列号
//int exceptionColNo = 0;
try
{
List<ExcelSheetData> excelSheetDataArray = new List<ExcelSheetData>();
List<PasswordMapping> excelPasswordMapping = new List<PasswordMapping>();
int sheetCount = 0;
using (FileStream file = new FileStream(filePath, FileMode.Open))
{
//建立WorkBook
HSSFWorkbook HSSFWorkbook = new HSSFWorkbook(file);
//获取工作薄的数目
sheetCount = HSSFWorkbook.NumberOfSheets;
//循环存储值
for (int i = 0; i < sheetCount; i++)
{
HSSFSheet Sheet = HSSFWorkbook.GetSheetAt(i);
string sheetName = HSSFWorkbook.GetSheetName(i);
//设置当前的工作薄名称
exceptionSheetName = sheetName;
IEnumerator rows = Sheet.GetRowEnumerator();
//当前行编号
int currentRowNo = 0;
while (rows.MoveNext())
{
if (currentRowNo > 0)
{
exceptionRowNo = currentRowNo;
HSSFRow row = (HSSFRow)rows.Current;
List<string> ilistTemp = new List<string>();
//初始密码（4位数字）
string initializePwd = CommonUtility.GenerateRandomPassword();
for (int j = 0; j < colsCount[i]; j++)
{
//exceptionColNo = j + 1;
ilistTemp.Add((row.GetCell(j) != null ? row.GetCell(j).ToString().Trim() : string.Empty));
if (j == (colsCount[i] - 1))
{
//try
//{
//实例化一个初始密码映射实体类
PasswordMapping passwordMapping = null;
switch (sheetName)
{
case "Sheet1":
passwordMapping = new PasswordMapping
{
userNo = row.GetCell(0).ToString(),
userName = row.GetCell(1).ToString(),
initializePassword = initializePwd
};
passwordMapping.userType = 1;
excelPasswordMapping.Add(passwordMapping);
ilistTemp.Add(CommonUtility.EncodeUsingMD5(initializePwd));
break;
case "Sheet2":
passwordMapping = new PasswordMapping
{
userNo = row.GetCell(0).ToString(),
userName = row.GetCell(1).ToString(),
initializePassword = initializePwd
};
passwordMapping.userType = 2;
excelPasswordMapping.Add(passwordMapping);
ilistTemp.Add(CommonUtility.EncodeUsingMD5(initializePwd));
break;
case "Sheet3":
passwordMapping = new PasswordMapping
{
userNo = row.GetCell(0).ToString(),
userName = row.GetCell(1).ToString(),
initializePassword = initializePwd
};
passwordMapping.userType = 3;
excelPasswordMapping.Add(passwordMapping);
ilistTemp.Add(CommonUtility.EncodeUsingMD5(initializePwd));
break;
default:
break;
}
}
}
//将要执行的SQL信息添加到ExcelSheetData数据中
ExcelSheetData excelSheetDataTemp = ReturnSqlJudgeBySheetName(sheetName, ReturnModelDataJudgeBySheetName(sheetName, ilistTemp));
excelSheetDataTemp.sheetName = exceptionSheetName;
excelSheetDataTemp.sheetRowNo = exceptionRowNo;
excelSheetDataArray.Add(excelSheetDataTemp);
}
//行编号值加1
currentRowNo += 1;
}
}
excelData = new ExcelData { excelSheetData = excelSheetDataArray, excelPasswordMapping = excelPasswordMapping };
}
}
catch (Exception ex)
{
MongoDBLog.LogRecord(ex);
HttpContext.Current.Response.Write("<script type='text/javascript'>alert('很抱歉，读取Excel文件数据失败！此次操作未更改任何数据库数据，相关信息如下：\\n\\n出错的工作薄名称为："
+ exceptionSheetName
+ "\\n出错的单元格行号为：" + exceptionRowNo
//+ "\n出错的单元格列号为：" + exceptionColNo
+ "\\n可能的原因为：\\n 此单元格数据格式可能不正确，例如：单元格数据是否存在多余的空格。"
+ "请检查Excel文件数据，修改后重新上传！');history.go(-1);</script>");
}
finally
{
if (File.Exists(HttpContext.Current.Server.MapPath("/" + FileUpload1.FileName)))
{
File.Delete(HttpContext.Current.Server.MapPath("/" + FileUpload1.FileName));
}
}
return excelData;
}
