asp.net使用NPOI导入Excel表到数据库

用NuGet工具管理包安装NPOI 即可使用

前端页面：

     <form method="post" enctype="multipart/form-data" id="fromUpload">
            <input name="file" type="file" id="fileId" style="height: 30px;display:inline-table"  />
            <input type="button" class="btn" id="JqueryUpload" value="导入Excel" style="background-color: #0094ff; height: 30px; color: #fff; border-color: #0094ff;display:inline-table" />
        </form>

JS代码

   <script>

        $(function () {
            $('#JqueryUpload').click(function () {
                //var file = $("#fromUpload input").files[0];
                var filename = $("#fileId").val();
                if (filename == "" || filename == null) {
                    alert("您还没有选择文件");
                    return;
                }
                var hz = filename.split(".");
                var last = hz[hz.length - 1];
                var AllImgExt = "xls|xlsx|";
                if (AllImgExt.indexOf(last + "|") == -1) {
                    ErrMsg = "该文件类型不允许上传。请上传 " + AllImgExt + " 类型的文件，当前文件类型为" + last;
                    alert(ErrMsg);
                    return false;
                }
                var formData = new FormData($('#fromUpload')[0]);
                showLoading('请等待...');
                $.ajax({
                    type: "POST", //必须用post
                    url: "index.aspx?Apptype=UploadFile",
                    contentType: "application/json; charset=utf-8",
                    data: formData,
                    //dataType: "json",
                    timeout: 10000, //请求10s算超时
                    contentType: false, //必须
                    processData: false,
                    async: false,
                    success: function (ret) {
                        hideLoading();
                        console.log(ret);
                        if (ret == 1) {
                            alert("导入成功");
                        } else {
                            alert("导入成功，录入的信息中有重复数据已跳过");
                        }
                    },
                    error: function (a, b, c) {
                        hideLoading();
                        alert(b + ',' + c);
                        console.log('UploadFile Error', e);
                    }
                });
            });
        });
    </script>

后端方法：

获取到datatable对象

  /// <summary>

        /// 将datatbale的数据转化为workbook，并设置相关参数

        /// </summary>

        /// <param name="dt"></param>

        /// <param name="workbook"></param>

        /// <param name="sheetName"></param>

        /// <returns></returns>
        public void ImportExcelFile()
        {
            XSSFWorkbook hssfworkbook;
            #region//初始化信息
            try
            {
                //获取选中的excel路径
                string apk_dir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Excel");
                HttpPostedFile FileName = Request.Files[0];
                var now = DateTime.UtcNow.AddHours(8);
                Random rand = new Random();
                int shu = rand.Next(100, 999);
                string sourcePath = now.ToString("yyyyMMdd_HH_mmss") + "_" + shu + "_" + FileName.FileName;
                FileName.SaveAs(Path.Combine(apk_dir, sourcePath));
                using (FileStream file = new FileStream(apk_dir + "/" + sourcePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new XSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
            //using (NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0))
            //{
            DataTable table = new DataTable();
            IRow headerRow = sheet.GetRow(0);//第一行为标题行
            int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
            int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

            //handling header.
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                if (row != null)
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = GetCellValue(row.GetCell(j));
                    }
                }

                table.Rows.Add(dataRow);
            }
            int res = 0;
            if (table != null)
            {
                 res = ExcelDal.AddUpExcel(table);
            }
            Response.Write(res);
        }
        /// <summary>
        /// 根据Excel列类型获取列的值
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }

取得导入的数据，存入数据库

        /// <summary>
        /// 导入信息上传
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public static int AddUpExcel(DataTable dataTable)
        {
            //将数据写到数据库里面
            int result = 0;
            if (dataTable.Rows.Count > 0)
            {
                DataRow dr = null;

                List<Model.SMSInfo> list = new List<Model.SMSInfo>();
                //查询数据库的数据
                List<Model.SMSInfo> alllist = GetAllSMS();
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    dr = dataTable.Rows[i];
                    Model.SMSInfo info = new Model.SMSInfo();
                    info.PhoneNumber = dr[0].ToString().Trim();
                    info.UserName = dr[1].ToString().Trim();
                    info.Identity = dr[2].ToString().Trim();
                    info.Postage = dr[3].ToString().Trim();
                    info.State = dr[4].ToString().Trim();
                    info.County = dr[5].ToString().Trim();
                    info.Department = dr[6].ToString().Trim();
                    info.Type = 0;
                    //数据库存在数据则不加入
                    var infolist = alllist.Where(a => a.PhoneNumber == dr[0].ToString().Trim()).FirstOrDefault();
                    if (infolist == null) { list.Add(info); }

                }
                //sql方法 加入数据库
                result = InsertSMS(list);
            }
            if (result == dataTable.Rows.Count)
            {
                return 1;
            }
            else
            {
                return 0;
            }
        }

 

参考地址：https://www.cnblogs.com/xiayan/p/4228222.html