NET Core导入Excel表格到数据库

2022年01月15日 阅读数:0
这篇文章主要向大家介绍NET Core导入Excel表格到数据库,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

一、NuGet包:NPOI数据库

 

二、利用接口的IFormFile获取Excel表格文件spa

 

     #region ******
        /// <summary>
        ///******
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public ActionResult<SysStatus> daotru(IFormFile fct)
        {
            SysStatus status = new SysStatus();
            try
            {
                        DataTable dt = new DataTable();
                        PictureMethods Picture = new PictureMethods();
                        string strMsg = "";
                        //利用IFormFile里面的OpenReadStream()方法直接读取文件流
                        dt = PictureMethods.ExcelToDatatable(fct.OpenReadStream(), Path.GetExtension(fct.FileName), out strMsg);
                        if (!string.IsNullOrEmpty(strMsg))
                        {
                            status.Code = 5;
                            status.Message = strMsg;
                        }
                        else
                        {
                            if (dt.Rows.Count > 0)
                            {
                                if (service.Posdate(dt, department, _userSession.GetCurrentUserId().ToString()))
                                {
                                    status.Code = 1;
                                    status.Message = "数据导入成功";
                                }
                                else
                                {
                                    status.Code = 2;
                                    status.Message = "数据导入失败";
                                }
                            }
                            else
                            {
                                status.Code = 3;
                                status.Message = "Excel导入表无数据!";
                            }
                        }
                    }
            }
            catch (Exception ex)
            {

                status.Code = 4;
                status.Message = ex.Message;
            }

            return status;
        }
        #endregion

三、表格中的数据导入数据库中excel

#region excel表格数据加载数据库表
        /// <summary>
        /// 将Excel单表转为Datatable
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="fileType"></param>
        /// <param name="strMsg"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataTable ExcelToDatatable(Stream stream, string fileType, out string strMsg, string sheetName = null)
        {
            strMsg = "";
            DataTable dt = new DataTable();
            ISheet sheet = null;
            IWorkbook workbook = null;
            try
            {
                #region 判断excel版本
                //2007以上版本excel
                if (fileType == ".xlsx")
                {
                    workbook = new XSSFWorkbook(stream);
                }
                //2007如下版本excel
                else if (fileType == ".xls")
                {
                    workbook = new HSSFWorkbook(stream);
                }
                else
                {
                    throw new Exception("传入的不是Excel文件!");
                }
                #endregion
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;
                    for (int i = firstRow.FirstCellNum; i < cellCount; i++)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue.Trim();
                            switch (cellValue)//改变头部名称为英文
                            {
                                case "证书类型":
                                    cellValue = "CertificateType";
                                    break;
                                case "姓名":
                                    cellValue = "CraftsmanName";
                                    break;
                                case "性别":
                                    cellValue = "CraftsmanSex";
                                    break;
                                case "学历":
                                    cellValue = "CraftsmanEducation";
                                    break;
                                case "手机号":
                                    cellValue = "CraftsmanPhone";
                                    break;
                                case "身份证号":
                                    cellValue = "IdCard";
                                    break;
                                case "证书编号":
                                    cellValue = "CertificateId";
                                    break;
                                case "证书有效期":
                                    cellValue = "CertificateTimes";
                                    break;
                                case "户籍地址":
                                    cellValue = "CzAddress";
                                    break; 
                                case "常住地址":
                                    cellValue = "HjAddress";
                                    break;
                                case "所属乡镇":
                                    cellValue = "Township";
                                    break;
                                case "证书连接":
                                    cellValue = "CertificateUrl";
                                    break;
                                case "头像":
                                    cellValue = "CraftsmanUrl";
                                    break;
                                case "身份证正面":
                                    cellValue = "IcFrontUrl";
                                    break;
                                case "身份证反面":
                                    cellValue = "IcBackUrl";
                                    break;
                            }
//switch中是为了将中文名替换成英文名
if (!string.IsNullOrEmpty(cellValue)) { DataColumn dataColumn = new DataColumn(cellValue); dt.Columns.Add(dataColumn); } } } DataRow dataRow = null; //遍历行 for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++) { IRow row = sheet.GetRow(j); dataRow = dt.NewRow(); if (row == null || row.FirstCellNum < 0) { continue; } //遍历列 for (int i = row.FirstCellNum; i < cellCount; i++) { ICell cellData = row.GetCell(i); if (cellData != null) { //判断是否为数字型,必须加这个判断否则下面的日期判断会异常 if (cellData.CellType == CellType.Numeric) { //判断是否日期类型 if (DateUtil.IsCellDateFormatted(cellData)) { dataRow[i] = cellData.DateCellValue; } else { dataRow[i] = cellData.ToString().Trim(); } } else { dataRow[i] = cellData.ToString().Trim(); } } } dt.Rows.Add(dataRow); } } else { throw new Exception("没有获取到Excel中的数据表!"); } } catch (Exception ex) { strMsg = ex.Message; } return dt; } #endregion