ASP.NET WebApi 批量导入数据库

批量导入数据库

最近在WebApi项目开发中，用到批量导入人员信息功能。下面对知识进行简单的总结和大家分享一下（最重要的是批量导入数据库的思路）。

思路：在WebApi项目中，要想实现批量导入数据，并且通过客户端浏览器向服务器上传数据。由于服务器端和客户端基本上是分离（服务器本机除外）。因此，批量录入数据的逻辑：
一步骤：首先将Excel文件上传至服务器。
二步骤：然后在服务器端读取Excel文件并转化为可插入数据信息的数据集合，执行批量插入数据库操作。

实现方法：
1.项目中引入NPOI插件：http://download.youkuaiyun.com/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[] { "工号", "姓名", "性别", "籍贯", "出生日期", "身份证号", "参加工作时间", "到院时间", "拟分配科室", "虚拟网号", "备注" };
                        #endregion
                        bflag = 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);
                        #endregion
                        for (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 获取拟分配科室的下拉id
                                        deptValue = 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.youkuaiyun.com/realjh/article/details/78857387

欢迎批评和指正。