/// <summary>
/// 导入Excel数据
/// </summary>
/// <param name="excelData">Excel数据实体</param>
/// <returns></returns>
public int ImportExcelData(ExcelData excelData)
{
//出错的工作薄名称
string exceptionSheetName = string.Empty;
//出错的行号
int exceptionRowNo = 0;
////出错的列号
//int exceptionColNo = 0;
using (TransactionScope scope = new TransactionScope())
{
try
{
//int count = 0;
//try
//{
foreach (ExcelSheetData excelSheetData in excelData.excelSheetData)
{
//count += 1;
//HttpContext.Current.Response.Write(count + "<br/>");
exceptionSheetName = excelSheetData.sheetName;
exceptionRowNo = excelSheetData.sheetRowNo;
//exceptionColNo = excelSheetData.sheetColNo;
SqlHelper.ExecuteNonQuery(conn, excelSheetData.spName, excelSheetData.sqlParammeters);
//执行插入操作
//SqlHelper.ExecuteNonQuery(conn, excelSheetData.spName, excelSheetData.sqlParammeters);
}
//}
//catch
//{
// HttpContext.Current.Response.Write(count);
// //HttpContext.Current.Response.End();
//}
//HttpContext.Current.Response.End();
//获取当前学期标识
string termTag = DalCommon.GetTermTag(conn);
//HttpContext.Current.Response.Write(termTag.ToString());
//HttpContext.Current.Response.End();
//以下需要添加插入密码映射表操作
//foreach (ExcelPasswordMapping excelPasswordMapping in excelData.excelPasswordMapping)
//{
// AddPasswordMapping(new PasswordMapping
// {
// userName = excelPasswordMapping.listPasswordMappingUserName[0],
// userNo = excelPasswordMapping.listPasswordMappingUserNo[0],
// initializePassword = excelPasswordMapping.listPasswordMappingInitializePassword[0],
// //此处暂时添加为0
// termTag = termTag
// });
//}
//count = 1;
foreach (PasswordMapping passwordMapping in excelData.excelPasswordMapping)
{
AddPasswordMapping(new PasswordMapping
{
userName = passwordMapping.userName,
userNo = passwordMapping.userNo,
initializePassword = passwordMapping.initializePassword,
//此处暂时添加为0
termTag = termTag,
userType = passwordMapping.userType
});
}
scope.Complete();
HttpContext.Current.Response.Write("<script type='text/javascript'>alert('导入Excel数据成功！确定后将转至系统首页');location.href='/';</script>");
}
catch (Exception ex)
{
MongoDBLog.LogRecord(ex);
HttpContext.Current.Response.Write("<script type='text/javascript'>alert('导入Excel数据失败！此次操作未更改任何数据库数据，相关信息如下：\\n\\n出错的工作薄名称为："
+ exceptionSheetName
+ "\\n出错的单元格行号为：" + exceptionRowNo
//+ "\n出错的单元格列号为：" + exceptionColNo
+ "\\n可能的原因为：\\n1. 此单元格数据格式可能不正确，例如：单元格数据是否存在多余的空格。\\n2. 此单元格数据存在重复而发生冲突。即可能数据库中已经存在此条记录，或者在Excel文件中存在两条重复的记录。');history.go(-1);</script>");
}
}
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;
}