使用NPOI导入Excel文件到数据库

矫情吗；* 2022-01-06 08:29 491阅读 0赞

项目先安装好NPOI依赖包，依赖完成后开始代码实现。

其实整个过程也不麻烦，就是把文件写流再转成DataTable形式，实在数据导入到数据库.我这里使用的IFormFile类。前端使用的lay-ui自带的文件上传，代码:

1.  准备工作
2.  .开始

layui.use(['upload', 'element', 'layer', 'form', 'laydate'], function () {
            var $ = layui.jquery
                , upload = layui.upload;
    
            var layer = layui.layer;
    
            //指定允许上传的文件类型
            upload.render({
                elem: '#test1'
                , url: '/YuanQiShouSi/Import'
                , accept: 'file' //普通文件
                , done: function (res) {
    
                    layer.msg(res.message);
    
                }
            });
        })

后面方法直接写在控制器的：

/// <summary>
            /// excel数据导入到数据库(mapcoderela表)
            /// </summary>
            /// <param name="excelfile"></param>
            /// <returns></returns>
            [HttpPost]
            public FuncResult Import()
            {
                FuncResult result = new FuncResult() { IsSuccess=true };
                try
                {
                    var excelfile = Request.Form.Files[0];
                    List<MapCodeRela> mapcoderelalist = new List<MapCodeRela>();
                    if (excelfile.Length > 0)
                    {
                        DataTable dt = new DataTable();
                        string strMsg;
                        string region = excelfile.FileName;//文件名即为区域名
                        //利用IFormFile里面的OpenReadStream()方法直接读取文件流
                        dt = ExcelHelper.ExcelToDatatable(excelfile.OpenReadStream(), Path.GetExtension(excelfile.FileName), out strMsg);
                        if (!string.IsNullOrEmpty(strMsg))
                        {
                            result.IsSuccess = false;
                            result.Message = strMsg;
    
                        }
                        if (dt.Rows.Count > 0)
                        {
                            //需要导入到数据库的数据
                            mapcoderelalist = JsonConvert.DeserializeObject<List<MapCodeRela>>(JsonConvert.SerializeObject(dt));
    
                            //查看当前区域是否有数据，若有区域，则先新原来数据写入mapcoderelahistory表，再清除掉写入新的数据
                            var handelResult = mapCodelRelaBll.WriteData(mapcoderelalist,region.Split('.')[0]);
    
                            if (!handelResult.IsSuccess )
                            {
                                return handelResult;
                            }
    
                        }
                        else
                        {
                            result.IsSuccess = false;
                            result.Message = "excel无数据！";
                        }
                    }
                    
    
    
                    return result;
                }
                catch (Exception ex)
                {
                    result.IsSuccess = false;
                    result.Message = ex.Message;
                    return result;
                }
    
            }

将文件以流形式读出来，再通过方法转换成DataTable:

/// <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();
                                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 && cellData.ToString() != "")
                                {
                                    //判断是否为数字型，必须加这个判断不然下面的日期判断会异常
                                    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();
                                    }
                                }
                                else
                                {
                                    dataRow[i] = Guid.NewGuid().ToString();
                                }
                            }
                            dt.Rows.Add(dataRow);
                        }
                    }
                    else
                    {
                        throw new Exception("没有获取到Excel中的数据表！");
                    }
                }
                catch (Exception ex)
                {
                    workbook = new HSSFWorkbook(stream);
                    strMsg = ex.Message;
                }
                return dt;
            }

================================================================================================

捣鼓了下从局域网共享文件夹访问Excel导入，先和局域网文件地址建立连接，再从目录拿文件，其他的和上面的一样。建立连接：

public static bool connectState(string path, string userName, string passWord)
            {
                bool Flag = false;
                Process proc = new Process();
                try
                {
                    proc.StartInfo.FileName = "cmd.exe";
                    proc.StartInfo.UseShellExecute = false;
                    proc.StartInfo.RedirectStandardInput = true;
                    proc.StartInfo.RedirectStandardOutput = true;
                    proc.StartInfo.RedirectStandardError = true;
                    proc.StartInfo.CreateNoWindow = true;
                    proc.Start();
                    string dosLine = "net use " + path + " " + passWord + " /user:" + userName;
                    proc.StandardInput.WriteLine(dosLine);
                    proc.StandardInput.WriteLine("exit");
                    while (!proc.HasExited)
                    {
                        proc.WaitForExit(1000);
                    }
                    string errormsg = proc.StandardError.ReadToEnd();
                    proc.StandardError.Close();
                    if (string.IsNullOrEmpty(errormsg))
                    {
                        Flag = true;
                    }
                    else
                    {
                        throw new Exception(errormsg);
                    }
                }
                catch (Exception ex)
                {
                    //throw ex;
                }
                finally
                {
                    proc.Close();
                    proc.Dispose();
                }
                return Flag;
            }

访问方法：

//直接用IP也可以
    status = connectState(@"\\192.168.1.189", @"Administrator\CqingShared", "001");