批量导入数据库
最近在WebApi项目开发中，用到批量导入人员信息功能。下面对知识进行简单的总结和大家分享一下（最重要的是批量导入数据库的思路）。
思路：在WebApi项目中，要想实现批量导入数据，并且通过客户端浏览器向服务器上传数据。由于服务器端和客户端基本上是分离（服务器本机除外）。因此，批量录入数据的逻辑：
一步骤：首先将Excel文件上传至服务器。
二步骤：然后在服务器端读取Excel文件并转化为可插入数据信息的数据集合，执行批量插入数据库操作。
实现方法：
1.项目中引入NPOI插件：http://download.csdn.net/download/realjh/10108407
2.通过客户端浏览器页面通过form表单的形式，提交Excel批量数据文件。
3.服务器端接收客户端浏览器提交的Excel文件流，并在服务器端重命名Excel文件。
4.接下来在服务器端检测待导入的Excel文件是存在，存在则进行读取数据，执行批量插入数据库操作。
代码实现如下：
/// <summary>/// 批量导入数据库/// </summary>/// <returns></returns>[HttpPost, Route("import")]public IHttpActionResult ImportExcelToDatabase(){//接收form表单中提交过来的excel数据表var file = HttpContext.Current.Request.Files["File"];//限定上传excel扩展名string[] extensionName = new string[] { ".XLSX", ".XLS" };string serverPath = string.Empty;//上传至服务器的路径//首先将excel文件上传至服务器//然后转换成从服务器端读取数据插入数据中(浏览器相当于客户端，是无法直接读取远程客户端excel中的数据的)//判断excel文件已经跟随表单被传递if (!string.IsNullOrWhiteSpace(file.FileName)){//说明文件已经上传string newName = string.Empty;//获取excel文件扩展名string extName = Path.GetExtension(file.FileName);//获取服务器根路径string rootPath = AppDomain.CurrentDomain.BaseDirectory;//上传值服务器全路径string fullPath = string.Empty;//判断excel文件是否符合上传标准if (extensionName.Contains(extName.ToUpper())){//符合上传的文件标准newName = Guid.NewGuid().ToString();//此时是文件上传至服务器的文件全名newName = newName + extName;//上传至服务器的路径serverPath = "Excels/"; //+ newName;fullPath = rootPath + serverPath;//判断文件上传文件路径if (!Directory.Exists(fullPath)){//如果不存在，则创建目录Directory.CreateDirectory(fullPath);}//对文件进行上传，读取和插入数据库操作try{//执行上传值服务器操作file.SaveAs(HttpContext.Current.Server.MapPath("~/" + serverPath + newName));//文件上传至服务器以后，进行读取并导入数据库中fullPath = fullPath + newName;string message = string.Empty;#region 设置插入插入数据库属性字段//在这里用了字典做数据库字段和Excel列名称匹配，为了实现动态拼接sql语句，使能够更加灵活的实现批量导入。但此处没有，而是根据字符串数组实现特定的判断，并拼接sql语句的，后期有待完善。Dictionary<string, string> dictionaryColumn = new Dictionary<string, string>();dictionaryColumn.Add("Emp_NO", "工号");dictionaryColumn.Add("Emp_Name", "姓名");dictionaryColumn.Add("Emp_Sex", "性别");dictionaryColumn.Add("Emp_Native", "籍贯");dictionaryColumn.Add("Emp_Birthday", "出生日期");dictionaryColumn.Add("Emp_IDCard", "身份证号");dictionaryColumn.Add("Emp_ToWorkDate", "参加工作时间");dictionaryColumn.Add("Emp_ArriveDate", "到院时间");dictionaryColumn.Add("Emp_DeptId", "拟分配科室");dictionaryColumn.Add("Emp_VirtualNum", "虚拟网号");dictionaryColumn.Add("Emp_Remark", "备注");string[] Column = new string[] { "工号", "姓名", "性别", "籍贯", "出生日期", "身份证号", "参加工作时间", "到院时间", "拟分配科室", "虚拟网号", "备注" };#endregionbflag = ImportDataToDataBase(fullPath, ref message, dictionaryColumn, Column);msg = message;}catch{//异常时删除上传至服务器的文件File.Delete(fullPath);}finally{//异常时，手动关闭文件流，并释放内存！file.InputStream.Close();file.InputStream.Dispose();if (!bflag){//异常时删除上传至服务器的文件File.Delete(fullPath);}}}else{msg = "数据导入失败，文件格式不符合标准，请选择后缀名为：.xlsx，.xls类型文件！";}}else{msg = "导入数据失败，请在表单中选中要导入excel数据表！";}return MyJson(new { flag = bflag, msg = msg });}/// <summary>/// 读取excel表中的数据，导入插入数据库/// </summary>/// <param name="fullPath"></param>/// <param name="message"></param>/// <param name="dic"></param>/// <param name="column"></param>/// <returns></returns>private bool ImportDataToDataBase(string fullPath, ref string message, Dictionary<string, string> dic, string[] column){bool aflag = false;//首先监测文件是否存在if (File.Exists(fullPath)){#region 打开读取excel文件中的数据using (FileStream file = new FileStream(fullPath, FileMode.Open, FileAccess.Read)){//获取需要被导入的excel工作薄IWorkbook workBook = new XSSFWorkbook(file);//判断Excel文件中有几个sheet表数据if (workBook.NumberOfSheets > 0){#region 声明数据遍历变量//声明行变量IRow row = null;//声明单元格变量ICell cell = null;//声明sheet表变量ISheet sheet = null;//批量导入数据库sql的list集合List<string> query = new List<string>();#endregion#region 拿到默认录入的密码string password = string.Empty;sql = "select Param_Value from PUB_Param where Param_Status='1' and Param_Id='1'";DataTable dt = DB.ListDataTable(sql);if (dt.Rows.Count > 0){password = dt.Rows[0].ItemArray[0].ToString();}#endregion#region 拿到数据库中已经存在工号string emp_no = string.Empty;sql = "select Emp_NO from HR_Employee ";dt = DB.ListDataTable(sql);#endregionfor (int v = 0; v < workBook.NumberOfSheets; v++){#region 开始循环导入sheet表中的数据string sexValue = string.Empty;string deptValue = string.Empty;//拿到Sheet数据表以后，遍历获取数据，并插入数据库sheet = workBook.GetSheetAt(v);for (int i = 0; i < sheet.LastRowNum + 1; i++){//说明：//i=0 拿到的是excel列字段名称//i>=1 拿到的是excel的行数据//首先拿到excel第一行列字段属性名，进行判断是否符合导入数据库的模板规范if (i == 0){#region 对excel表的列字段属性进行判断，顺序是否符合模板规范//单元格是以最后一个有数据的cell为最后一个row = sheet.GetRow(i);//监测excel文件的sheet表中列字段是否符合模板规范bool bflag = false;//数据列和数据库字段属性是否吻合if (row.Cells.Count == dic.Count){for (int j = 0; j < row.Cells.Count; j++){//判断excel的表头字段属性是否符合上传的规范if (!dic.ContainsValue(row.Cells[j].StringCellValue) && (column[j] != row.Cells[j].StringCellValue)){message = "抱歉，sheet名为[" + workBook.GetSheetAt(v).SheetName + "]导入数据出错，Excel表中的列字段不符合模板规范！";//说明不符合模板规范bflag = true;break;}}//做一次判断用户给定的数据是否符合模板规范if (bflag){break;}}else{message = "抱歉，sheet名为[" + workBook.GetSheetAt(v).SheetName + "]导入数据出错，Excel表中的列字段不符合模板规范！";}#endregion}else{ //获取excel表中非 列名称属性的数据集合row = sheet.GetRow(i);try{#region 对性别进行判断插入sexValue = row.Cells[2].StringCellValue;if (!string.IsNullOrWhiteSpace(sexValue) && sexValue.Contains("*")){sexValue = sexValue.Split(new char[1] { '*' })[0];//Emp_Sex 的id}else{sexValue = "56";//给个默认值为男生}#endregion#region 获取拟分配科室的下拉iddeptValue = row.Cells[8].StringCellValue;if (!string.IsNullOrWhiteSpace(deptValue) && deptValue.Contains("*")){deptValue = deptValue.Split(new char[1] { '*' })[0];}else{message = "姓名为[ " + row.Cells[1].StringCellValue + " ]的科室分配不符合规范，请按照excel模板中的下拉选择！";return aflag;}#endregion#region 判断工号是否重复if (dt.Rows.Count > 0){for (int k = 0; k < dt.Rows.Count; k++){if (ConvertNullToString.GetString(dt.Rows[k].ItemArray[0]) == ConvertNullToString.GetString(row.Cells[0].StringCellValue)){message = "姓名为[ " + row.Cells[1].StringCellValue + " ]分配的工号数据库中已存在，请重新分配未使用的工号！";return aflag;}}}#endregion#region 插入员工表int emp_id = SequenceManager.Instance.GetSequence("Employee");sql = @"insert into employee( Emp_Id,Emp_NO,Emp_Name,Emp_Sex,Emp_Native,Emp_Birthday,Emp_IDCard,Emp_ToWorkDate,Emp_ArriveDate,Emp_DeptId,Emp_VirtualNum,Emp_Remark,Emp_Status ) values('" + emp_id + "','" + ConvertNullToString.GetString(row.Cells[0].StringCellValue) + "','" + ConvertNullToString.GetString(row.Cells[1].StringCellValue) + "','" + sexValue + "','" + row.Cells[3].StringCellValue + "','" + row.Cells[4].DateCellValue.ToString("yyyy-MM-dd") + "','" + row.Cells[5].StringCellValue + "','" + row.Cells[6].DateCellValue.ToString("yyyy-MM-dd") + "','" + row.Cells[7].DateCellValue.ToString("yyyy-MM-dd") + "','" + deptValue + "','" + ConvertNullToString.GetString(row.Cells[9].StringCellValue) + "','" + row.Cells[10].StringCellValue + "','1')";query.Add(sql);#endregion#region 插入用户表int user_id = SequenceManager.Instance.GetSequence("User");sql = "insert into User(User_Id, Emp_Id, User_NO, User_Name, User_Sex, Dept_Id, User_PassWord, User_PYCode, User_Status,Modified_Date)values('" + user_id + "', '" + emp_id + "', '" + ConvertNullToString.GetString(row.Cells[0].StringCellValue) + "', '" + ConvertNullToString.GetString(row.Cells[1].StringCellValue) + "', '" + sexValue + "', '" + deptValue + "', '" + MD5Encode.Encode(password) + "', '', '1', getdate())";query.Add(sql);#endregion#region 插入用户组表sql = "insert into Group(Group_Id, User_Id, Modified_Date,Use_Status)values(3,'" + user_id + "', getdate(),'1')";query.Add(sql);#endregion}catch{message = "sheet名为[" + workBook.GetSheetAt(v).SheetName + " ]导入数据出错，请检查 姓名为[" + row.Cells[1].StringCellValue + "]记录！";//关闭工作薄资源workBook.Close();//清楚缓冲区file.Flush();//释放内存file.Dispose();}}}//sql数据获取结束，执行插入操作DB.ExecuteForList(query);aflag = true;message = "sheet名为[" + workBook.GetSheetAt(v).SheetName + " ]导入数据成功！";#endregion}}else{message = "Excel文件中没有sheet表数据，无法导入数据库，请仔细检查文件！";}}//end using filestream#endregion}else{message = "没有找到文件，插入操作失败！";}return aflag;}说明：本篇文章主要是提供实现批量将Excel表的数据导入数据库中，还有很多地方需要改善，后期进行单独学习和完善，使能够在项目中更好更灵活的实现批量录入数据。
这是最新优化灵活获取excel工作薄中数据的解决方案：http://blog.csdn.net/realjh/article/details/78857387欢迎批评和指正。