NPOI导入导出Excel文件

废话不多说。

导出：

		/// <summary>
        /// 导出数据到Excel
        /// </summary>
        /// <param name="columnList">自定义列</param>
        /// <param name="table">导出数据</param>
        /// <param name="title">标题</param>
        /// <param name="sheetName">文件名称</param>
        /// <returns></returns>
        public static Stream ToExcelStream(List<CustomTitle> columnList, DataTable table, string title, string sheetName)
        {
            //处理列信息
            for (int i = table.Columns.Count - 1; i >= 0; i--)
            {
                var columnName = table.Columns[i].ColumnName;

                //排除未指定的列
                if (columnList.All(x => x.ColumnName != columnName))
                {
                    table.Columns.RemoveAt(i);
                    continue;
                }

                //获取自定义列名
                var customTitle = columnList.Find(x => x.ColumnName == columnName);
                table.Columns[i].ColumnName = customTitle.CustomColumnName;
            }

            var missingNum = 0;
            //自定义排序
            foreach (var item in columnList)
            {
                var index = columnList.IndexOf(item);
                var realIndex = table.Columns.IndexOf(item.CustomColumnName);
                if (realIndex < 0)
                {
                    missingNum++;
                    continue;
                }

                table.Columns[realIndex].SetOrdinal(index - missingNum);
            }

            return ToExcelStream(table, title, sheetName);
        }

		public static System.IO.Stream ToExcelStream(DataTable table, string title, string sheetName)
        {
            IWorkbook workBook = new HSSFWorkbook();
            sheetName = sheetName.IsEmpty() ? "sheet1" : sheetName;
            ISheet sheet = workBook.CreateSheet(sheetName);

            //处理表格标题
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue(title);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
            row.Height = 500;

            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 17;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            row.Cells[0].CellStyle = cellStyle;


            ICellStyle titleCellStyle = workBook.CreateCellStyle();
            IFont titleFont = workBook.CreateFont();
            titleFont.FontName = "微软雅黑";
            titleFont.FontHeightInPoints = 10;
            titleFont.IsBold = true;
            titleCellStyle.SetFont(titleFont);

            //处理表格列头
            row = sheet.CreateRow(1);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                var cell = row.CreateCell(i);
                cell.CellStyle = titleCellStyle;
                cell.SetCellValue(table.Columns[i].ColumnName);
            }

            //处理数据内容
            for (int i = 0; i < table.Rows.Count; i++)
            {
                row = sheet.CreateRow(2 + i);
                row.Height = 250;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                    sheet.SetColumnWidth(j, 256 * 15);
                }
            }

            //处理列宽度
            for (int i = 0; i < table.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }

            MemoryStream ms = new MemoryStream();
            //写入数据流
            workBook.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);

            return ms;
        }

        /// <summary>
	    /// 自定义列
	    /// </summary>
	    public class CustomTitle
	    {
	        /// <summary>
	        /// 列名称
	        /// </summary>
	        public string ColumnName { get; set; }
	
	        /// <summary>
	        /// 自定义列名称
	        /// </summary>
	        public string CustomColumnName { get; set; }
	    }

使用：

public ActionResult Getexcel() 
        {
            var title = "标题";
            //需要导出的数据
            DataTable dt = new DataTable();
            List<CustomTitle> columnList = new List<CustomTitle>() 
            { 
                new CustomTitle()
                {
                    ColumnName="Id",
                    CustomColumnName="编号"
                },
                new CustomTitle()
                {
                    ColumnName="Name",
                    CustomColumnName="名称"
                },
            };
            var stream = NPOIExcel.ToExcelStream(columnList, dt, title, "文件名");
            return File(stream, "application/ms-excel", title + ".xls");
        }

导入：

		/// <summary>
        /// 从excel导入数据
        /// </summary>
        /// <param name="filePath">excel位置</param>
        /// <param name="rowNum">数据导入从第几行开始</param>
        /// <returns></returns>
        public static DataTable ExcelToDB(string filePath, int rowNum)
        {
            IWorkbook workbook = null;
            //XSSFWorkbook hssfworkbook;
            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    //2007版本
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(file);
                    // 2003版本
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
            if (System.IO.File.Exists(filePath))
            {
                System.IO.File.Delete(filePath);
            }
            ISheet sheet = workbook.GetSheetAt(0);
            DataTable table = new DataTable();
            IRow headerRow = sheet.GetRow(rowNum);
            int cellCount = headerRow.LastCellNum;
            int rowCount = sheet.LastRowNum;
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                if (headerRow.GetCell(i).CellType == CellType.Numeric)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).NumericCellValue.ToString());
                    table.Columns.Add(column);
                }
                else
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
            }
            int beginrow = (sheet.FirstRowNum + rowNum + 1), endrow = rowCount;
            for (int i = beginrow; i <= endrow; 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));
                    }
                }

                // 过滤空行
                if (dataRow.ItemArray.Any(a => !string.IsNullOrWhiteSpace(a.ToString())))
                {
                    table.Rows.Add(dataRow);
                }
            }
            return table;
        }
        
        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 HSSFDateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss") : 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();
                    }
            }
        }

使用：

		public ActionResult Upload(IFormFile excelfile)
        {
            int errorRow = 1;
            try
            {
                var filebase = Request.Form.Files[0];
                if (filebase == null)
                {
                    return Error("请导入Excel！");
                }
                string filename = filebase.FileName;
                string filetype = System.IO.Path.GetExtension(filename).ToString();
                string sWebRootFolder = _hostingEnvironment.WebRootPath;
                string sFileName = $"{Guid.NewGuid()}" + filetype;
                FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
                using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
                {
                    filebase.CopyTo(fs);
                    fs.Flush();
                }

                string savePath = Path.Combine(sWebRootFolder, sFileName);
                if(!string.IsNullOrWhiteSpace(savePath))
                {
                    savePath = savePath.ToLower();
                }
                DataTable dt = NPOIExcel.ExcelToDB(savePath, 1);
				//导入后的逻辑代码
				
                return Success("导入成功！");
            }
            catch (Exception ex)
            {

                return Error("导入时，数据有误！第" + errorRow + "行" + ex.Message);
            }
        }