2021-09-04

.netCore导入excel文档结合Layui表格

导入excel文件并保存到数据库然后在layui表格中显示出来

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、前端页面部分

通过layui中的文件上传按钮，来上传文件

<button type="button" class="layui-btn layui-btn-xs" id="test3"><i class="layui-icon"></i>导入</button>


layui.use(['upload', 'element', 'layer'], function () {
                var $ = layui.jquery
                    , upload = layui.upload
                    , element = layui.element
                    , layer = layui.layer;
                upload.render({ //允许上传的文件后缀
                    elem: '#test3'
                    , url: '/AreaGongyi/ImportCustomer' //此处配置你自己的上传接口即可
                    , accept: 'file' //普通文件
                    , exts: 'xls|xlsx|' //只允许上传excel文件
                    , done: function (res) {
                        layer.msg('上传成功');
                        console.log(res)
                    }
                });
            });

下面是copy一位博主的实现代码：https://www.cnblogs.com/mojie/p/10550268.html

二、控制器接收导入的文件

代码如下（示例）：

//导入excel
        public async Task<IActionResult> ImportCustomer(IFormFile file)
        {
            ResultData resultData = new ResultData();
            DataTable dt = new DataTable();
            //var json = new
            //{
            //    okMsg = "添加成功！"
            //};
            var json = "";
            if (file.Length > 0)
            {
                
                string strMsg;
                //利用IFormFile里面的OpenReadStream()方法直接读取文件流
                dt = ExcelHelper.ExcelToDatatable(file.OpenReadStream(), Path.GetExtension(file.FileName), out strMsg);
                if (!string.IsNullOrEmpty(strMsg))
                {
                    resultData.Code = -1;
                    resultData.Msg = strMsg;
                    //return dt;
                }
                if (dt.Rows.Count > 0)
                {
                   
                    for (int i = 0,j=0; i < dt.Rows.Count; i++,j++)
                    {
                        AreaGongyi areaGongyi = new AreaGongyi();
                        areaGongyi.AreaName = dt.Rows[i][0].ToString();
                        areaGongyi.TotalQuantity = decimal.Parse(dt.Rows[i][1].ToString());
                        areaGongyi.CDouble = decimal.Parse(dt.Rows[i][2].ToString());
                        areaGongyi.Hc_36 = decimal.Parse(dt.Rows[i][3].ToString());
                        areaGongyi.Kl_10= decimal.Parse(dt.Rows[i][4].ToString());
                        areaGongyi.Hc_1 = decimal.Parse(dt.Rows[i][5].ToString());
                        areaGongyi.C3D = decimal.Parse(dt.Rows[i][6].ToString());
                        areaGongyi.Kl_8 = decimal.Parse(dt.Rows[i][7].ToString());
                        conn.AreaGongyi.Add(areaGongyi);
                        await conn.SaveChangesAsync();
                    }
                    json = "{\"code\":0,\"count\":\"" + dt + "\",\"data\": " + dt.ToJson() + "  }";
                    return Content(json);
                }
            else
                {
                    resultData.Code = -1;
                    resultData.Msg = "Excel导入表无数据!";
                }
            }
            return Content(json);

        }

三、读取excel文件内容，并写入datatable

代码如下（示例）：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace Core.DAL
{
    public class ExcelHelper
    {
        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();
                            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;
        }
    }

}

总结

以上就是我记录的导入excel文件并保存到数据库，然后在layui表格中显示的代码，里面还是有些东西没有理解，也不熟悉，是在网上找的，好在拼拼凑凑功能实现了，先记录在这吧，日后会常看的，第一次记录就到这里，希望自己能坚持记录下去。