.NET Core 实现Excel的导入导出

  • .NET Core 实现Excel的导入导出已关闭评论
  • 80 次浏览
  • A+
所属分类:.NET技术
摘要

我们在日常开发中对Excel的操作可能会比较频繁,好多功能都会涉及到Excel的操作。在.Net Core中大家可能使用Npoi比较多,这款软件功能也十分强大,而且接近原始编程。但是直接使用Npoi大部分时候我们可能都会自己封装一下,毕竟根据二八原则,我们百分之八十的场景可能都是进行简单的导入导出操作,这里就引出我们的主角Npoi。


前言

我们在日常开发中对Excel的操作可能会比较频繁,好多功能都会涉及到Excel的操作。在.Net Core中大家可能使用Npoi比较多,这款软件功能也十分强大,而且接近原始编程。但是直接使用Npoi大部分时候我们可能都会自己封装一下,毕竟根据二八原则,我们百分之八十的场景可能都是进行简单的导入导出操作,这里就引出我们的主角Npoi。

NPOI简介

NPOI是指构建在POI 3.x版本之上的一个程序,NPOI可以在没有安装Office的情况下对Word或Excel文档进行读写操作。NPOI是一个开源的C#读写Excel、WORD等微软OLE2组件文档的项目。

一、安装相对应的程序包

在 .Net Core 中使用NPOI首先必须先安装NPOI;如下图所示:
.NET Core 实现Excel的导入导出

1.1、在 “管理NuGet程序包” 中的浏览搜索:“NPOI”

.NET Core 实现Excel的导入导出
点击安装以上两个即可,安装完成之后最好重新编译一下项目以防出错

二、新建Excel帮助类

在项目中新建“ExcelHelper”类;此类用于封装导入导出以及其他配置方法。代码如下:

using System; using System.Collections.Generic; using System.Data; using System.IO; using NPOI; using System.Text; using NPOI.HSSF.UserModel; using NPOI.XSSF.UserModel; using NPOI.SS.Formula.Eval; using NPOI.SS.UserModel; using NPOI.SS.Util; using System.Text.RegularExpressions; using System.Reflection; using System.Collections; using NPOI.HSSF.Util;  namespace WebApplication1 //命名空间依据自己的项目进行修改 {     /// <summary>     /// Excel帮助类     /// 功能:     ///   1、导出数据到Excel文件中     ///   2、将Excel文件的数据导入到List<T>对象集合中     /// </summary>     public static class ExcelHelper     {         /// <summary>         /// 导出列名         /// </summary>         public static SortedList ListColumnsName;          #region 从DataTable导出到excel文件中,支持xls和xlsx格式          #region 导出为xls文件内部方法          /// <summary>         /// 从DataTable 中导出到excel         /// </summary>         /// <param name="strFileName">excel文件名</param>         /// <param name="dtSource">datatabe源数据</param>         /// <param name="strHeaderText">表名</param>         /// <param name="sheetnum">sheet的编号</param>         /// <returns></returns>         static MemoryStream ExportDT(string strFileName, DataTable dtSource, string strHeaderText, Dictionary<string, string> dir, int sheetnum)         {             //创建工作簿和sheet             IWorkbook workbook = new HSSFWorkbook();             using (Stream writefile = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read))             {                 if (writefile.Length > 0 && sheetnum > 0)                 {                     workbook = WorkbookFactory.Create(writefile);                 }             }             ISheet sheet = null;             ICellStyle dateStyle = workbook.CreateCellStyle();             IDataFormat format = workbook.CreateDataFormat();             dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");             int[] arrColWidth = new int[dtSource.Columns.Count];             foreach (DataColumn item in dtSource.Columns)             {                 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(Convert.ToString(item.ColumnName)).Length;             }             for (int i = 0; i < dtSource.Rows.Count; i++)             {                 for (int j = 0; j < dtSource.Columns.Count; j++)                 {                     int intTemp = Encoding.GetEncoding(936).GetBytes(Convert.ToString(dtSource.Rows[i][j])).Length;                     if (intTemp > arrColWidth[j])                     {                         arrColWidth[j] = intTemp;                     }                 }             }             int rowIndex = 0;             foreach (DataRow row in dtSource.Rows)             {                 #region 新建表,填充表头,填充列头,样式                 if (rowIndex == 0)                 {                     string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());                     if (workbook.GetSheetIndex(sheetName) >= 0)                     {                         workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));                     }                     sheet = workbook.CreateSheet(sheetName);                     #region 表头及样式                     {                         sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));                         IRow headerRow = sheet.CreateRow(0);                         headerRow.HeightInPoints = 25;                         headerRow.CreateCell(0).SetCellValue(strHeaderText);                         ICellStyle headStyle = workbook.CreateCellStyle();                         headStyle.Alignment = HorizontalAlignment.Center;                         IFont font = workbook.CreateFont();                         font.FontHeightInPoints = 20;                         font.Boldweight = 700;                         headStyle.SetFont(font);                         headerRow.GetCell(0).CellStyle = headStyle;                          rowIndex = 1;                     }                     #endregion                      #region 列头及样式                      if (rowIndex == 1)                     {                         IRow headerRow = sheet.CreateRow(1);//第二行设置列名                         ICellStyle headStyle = workbook.CreateCellStyle();                         headStyle.Alignment = HorizontalAlignment.Center;                         IFont font = workbook.CreateFont();                         font.FontHeightInPoints = 10;                         font.Boldweight = 700;                         headStyle.SetFont(font);                         //写入列标题                         foreach (DataColumn column in dtSource.Columns)                         {                             headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);                             headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                             //设置列宽                             sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);                         }                         rowIndex = 2;                     }                     #endregion                 }                 #endregion                  #region 填充内容                  IRow dataRow = sheet.CreateRow(rowIndex);                 foreach (DataColumn column in dtSource.Columns)                 {                     NPOI.SS.UserModel.ICell newCell = dataRow.CreateCell(column.Ordinal);                     string drValue = row[column].ToString();                     switch (column.DataType.ToString())                     {                         case "System.String": //字符串类型                             double result;                             if (isNumeric(drValue, out result))                             {                                 //数字字符串                                 double.TryParse(drValue, out result);                                 newCell.SetCellValue(result);                                 break;                             }                             else                             {                                 newCell.SetCellValue(drValue);                                 break;                             }                          case "System.DateTime": //日期类型                             DateTime dateV;                             DateTime.TryParse(drValue, out dateV);                             newCell.SetCellValue(dateV);                              newCell.CellStyle = dateStyle; //格式化显示                             break;                         case "System.Boolean": //布尔型                             bool boolV = false;                             bool.TryParse(drValue, out boolV);                             newCell.SetCellValue(boolV);                             break;                         case "System.Int16": //整型                         case "System.Int32":                         case "System.Int64":                         case "System.Byte":                             int intV = 0;                             int.TryParse(drValue, out intV);                             newCell.SetCellValue(intV);                             break;                         case "System.Decimal": //浮点型                         case "System.Double":                             double doubV = 0;                             double.TryParse(drValue, out doubV);                             newCell.SetCellValue(doubV);                             break;                         case "System.DBNull": //空值处理                             newCell.SetCellValue("");                             break;                         default:                             newCell.SetCellValue(drValue.ToString());                             break;                     }                 }                 #endregion                 rowIndex++;             }             using (MemoryStream ms = new MemoryStream())             {                 workbook.Write(ms, true);                 ms.Flush();                 ms.Position = 0;                 return ms;             }         }          #endregion          #region 导出为xlsx文件内部方法          /// <summary>         /// 从DataTable 中导出到excel         /// </summary>         /// <param name="dtSource">DataTable数据源</param>         /// <param name="strHeaderText">表名</param>         /// <param name="fs">文件流</param>         /// <param name="readfs">内存流</param>         /// <param name="sheetnum">sheet索引</param>         static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs, MemoryStream readfs, Dictionary<string, string> dir, int sheetnum)         {             IWorkbook workbook = new XSSFWorkbook();             if (readfs.Length > 0 && sheetnum > 0)             {                 workbook = WorkbookFactory.Create(readfs);             }             ISheet sheet = null;             ICellStyle dateStyle = workbook.CreateCellStyle();             IDataFormat format = workbook.CreateDataFormat();             dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");              //取得列宽             int[] arrColWidth = new int[dtSource.Columns.Count];             foreach (DataColumn item in dtSource.Columns)             {                 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(Convert.ToString(item.ColumnName)).Length;             }             for (int i = 0; i < dtSource.Rows.Count; i++)             {                 for (int j = 0; j < dtSource.Columns.Count; j++)                 {                     int intTemp = Encoding.GetEncoding(936).GetBytes(Convert.ToString(dtSource.Rows[i][j])).Length;                     if (intTemp > arrColWidth[j])                     {                         arrColWidth[j] = intTemp;                     }                 }             }             int rowIndex = 0;              foreach (DataRow row in dtSource.Rows)             {                 #region 新建表,填充表头,填充列头,样式                  if (rowIndex == 0)                 {                     #region 表头及样式                     {                         string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());                         if (workbook.GetSheetIndex(sheetName) >= 0)                         {                             workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));                         }                         sheet = workbook.CreateSheet(sheetName);                         sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));                         IRow headerRow = sheet.CreateRow(0);                         headerRow.HeightInPoints = 25;                         headerRow.CreateCell(0).SetCellValue(strHeaderText);                          ICellStyle headStyle = workbook.CreateCellStyle();                         headStyle.Alignment = HorizontalAlignment.Center;                         IFont font = workbook.CreateFont();                         font.FontHeightInPoints = 20;                         font.Boldweight = 700;                         headStyle.SetFont(font);                         headerRow.GetCell(0).CellStyle = headStyle;                     }                     #endregion                      #region 列头及样式                     {                         IRow headerRow = sheet.CreateRow(1);                         ICellStyle headStyle = workbook.CreateCellStyle();                         headStyle.Alignment = HorizontalAlignment.Center;                         IFont font = workbook.CreateFont();                         font.FontHeightInPoints = 10;                         font.Boldweight = 700;                         headStyle.SetFont(font);                         foreach (DataColumn column in dtSource.Columns)                         {                             headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);                             headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                             //设置列宽                             sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);                         }                     }                      #endregion                      rowIndex = 2;                 }                 #endregion                  #region 填充内容                 IRow dataRow = sheet.CreateRow(rowIndex);                 foreach (DataColumn column in dtSource.Columns)                 {                     NPOI.SS.UserModel.ICell newCell = dataRow.CreateCell(column.Ordinal);                     string drValue = row[column].ToString();                     switch (column.DataType.ToString())                     {                         case "System.String": //字符串类型                             double result;                             if (isNumeric(drValue, out result))                             {                                 double.TryParse(drValue, out result);                                 newCell.SetCellValue(result);                                 break;                             }                             else                             {                                 newCell.SetCellValue(drValue);                                 break;                             }                         case "System.DateTime": //日期类型                             DateTime dateV;                             DateTime.TryParse(drValue, out dateV);                             newCell.SetCellValue(dateV);                              newCell.CellStyle = dateStyle; //格式化显示                             break;                         case "System.Boolean": //布尔型                             bool boolV = false;                             bool.TryParse(drValue, out boolV);                             newCell.SetCellValue(boolV);                             break;                         case "System.Int16": //整型                         case "System.Int32":                         case "System.Int64":                         case "System.Byte":                             int intV = 0;                             int.TryParse(drValue, out intV);                             newCell.SetCellValue(intV);                             break;                         case "System.Decimal": //浮点型                         case "System.Double":                             double doubV = 0;                             double.TryParse(drValue, out doubV);                             newCell.SetCellValue(doubV);                             break;                         case "System.DBNull": //空值处理                             newCell.SetCellValue("");                             break;                         default:                             newCell.SetCellValue(drValue.ToString());                             break;                     }                 }                 #endregion                 rowIndex++;             }             workbook.Write(fs,true);             fs.Close();         }          #endregion          #region 导出excel表格          /// <summary>         ///  DataTable导出到Excel文件,xls文件         /// </summary>         /// <param name="dtSource">数据源</param>         /// <param name="strHeaderText">表名</param>         /// <param name="strFileName">excel文件名</param>         /// <param name="dir">DataTable和excel列名对应字典</param>         /// <param name="sheetRow">每个sheet存放的行数</param>         public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName, Dictionary<string, string> dir, bool isNew, int sheetRow = 50000)         {             int currentSheetCount = GetSheetNumber(strFileName);//现有的页数sheetnum             if (sheetRow <= 0)             {                 sheetRow = dtSource.Rows.Count;             }             string[] temp = strFileName.Split('.');             string fileExtens = temp[temp.Length - 1];             int sheetCount = (int)Math.Ceiling((double)dtSource.Rows.Count / sheetRow);//sheet数目             if (temp[temp.Length - 1] == "xls" && dtSource.Columns.Count < 256 && sheetRow < 65536)             {                 if (isNew)                 {                     currentSheetCount = 0;                 }                 for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++)                 {                     DataTable pageDataTable = dtSource.Clone();                     int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow;                     for (int j = 0; j < hasRowCount; j++)                     {                         pageDataTable.ImportRow(dtSource.Rows[(i - currentSheetCount) * sheetRow + j]);                     }                      using (MemoryStream ms = ExportDT(strFileName, pageDataTable, strHeaderText, dir, i))                     {                         using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))                         {                              byte[] data = ms.ToArray();                             fs.Write(data, 0, data.Length);                             fs.Flush();                         }                     }                 }             }             else             {                 if (temp[temp.Length - 1] == "xls")                     strFileName = strFileName + "x";                 if (isNew)                 {                     currentSheetCount = 0;                 }                 for (int i = currentSheetCount; i < currentSheetCount + sheetCount; i++)                 {                     DataTable pageDataTable = dtSource.Clone();                     int hasRowCount = dtSource.Rows.Count - sheetRow * (i - currentSheetCount) < sheetRow ? dtSource.Rows.Count - sheetRow * (i - currentSheetCount) : sheetRow;                     for (int j = 0; j < hasRowCount; j++)                     {                         pageDataTable.ImportRow(dtSource.Rows[(i - currentSheetCount) * sheetRow + j]);                     }                     FileStream readfs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read);                     MemoryStream readfsm = new MemoryStream();                     readfs.CopyTo(readfsm);                     readfs.Close();                     using (FileStream writefs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))                     {                          ExportDTI(pageDataTable, strHeaderText, writefs, readfsm, dir, i);                     }                     readfsm.Close();                 }             }         }          /// <summary>         /// 导出Excel(//超出10000条数据 创建新的工作簿)         /// </summary>         /// <param name="dtSource">数据源</param>         /// <param name="dir">导出Excel表格的字段名和列名的字符串字典实例;例如:dir.Add("IllegalKeywords", "姓名");</param>         public static XSSFWorkbook ExportExcel(DataTable dtSource, Dictionary<string, string> dir)         {             XSSFWorkbook excelWorkbook = new XSSFWorkbook();              //int columnsCount = columnsNames.GetLength(0);             int columnsCount = dir.Count;             if (columnsCount > 0)             {                 ListColumnsName = new SortedList(new NoSort());                 //for (int i = 0; i < columnsCount; i++)                 //{                 //    ListColumnsName.Add(columnsNames[i, 0], columnsNames[i, 1]);                 //}                 foreach (KeyValuePair<string,string> item in dir)                 {                     ListColumnsName.Add(item.Key, item.Value);                 }                 if (ListColumnsName == null || ListColumnsName.Count == 0)                 {                     throw (new Exception("请对ListColumnsName设置要导出的列明!"));                 }                 else                 {                     excelWorkbook = InsertRow(dtSource);                 }             }             else             {                 throw (new Exception("请对ListColumnsName设置要导出的列明!"));             }             return excelWorkbook;         }          #endregion          /// <summary>         /// 创建Excel文件         /// </summary>         /// <param name="filePath"></param>         private static XSSFWorkbook CreateExcelFile()         {             XSSFWorkbook xssfworkbook = new XSSFWorkbook();              //右击文件“属性”信息             #region 文件属性信息             {                 POIXMLProperties props = xssfworkbook.GetProperties();                 props.CoreProperties.Creator = "Joy";//Excel文件的创建作者                 props.CoreProperties.Title = "";//Excel文件标题                 props.CoreProperties.Description = "";//Excel文件备注                 props.CoreProperties.Category = "";//Excel文件类别信息                 props.CoreProperties.Subject = "";//Excel文件主题信息                 props.CoreProperties.Created = DateTime.Now;//Excel文件创建时间                 props.CoreProperties.Modified = DateTime.Now;//Excel文件修改时间                 props.CoreProperties.SetCreated(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));                 props.CoreProperties.LastModifiedByUser = "Joy";//Excel文件最后一次保存者                 props.CoreProperties.SetModified(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//Excel文件最后一次保存日期             }             #endregion              return xssfworkbook;         }          /// <summary>         /// 创建excel表头         /// </summary>         /// <param name="dgv"></param>         /// <param name="excelSheet"></param>         private static void CreateHeader(XSSFSheet excelSheet, XSSFWorkbook excelWorkbook, XSSFCellStyle cellStyle)         {             int cellIndex = 0;             //循环导出列             foreach (System.Collections.DictionaryEntry de in ListColumnsName)             {                 XSSFRow newRow = (XSSFRow)excelSheet.CreateRow(0);                 XSSFCellStyle? headTopStyle = CreateStyle(excelWorkbook, cellStyle,HorizontalAlignment.Center, VerticalAlignment.Center, 18, true, true, "宋体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Grey25Percent.Index, HSSFColor.Black.Index,FontUnderlineType.None, FontSuperScript.None, false);                 XSSFCell newCell = (XSSFCell)newRow.CreateCell(cellIndex);                 newCell.SetCellValue(de.Value.ToString());                 newCell.CellStyle = headTopStyle;                 cellIndex++;             }         }          /// <summary>         /// 插入数据行         /// </summary>         private static XSSFWorkbook InsertRow(DataTable dtSource)         {             XSSFWorkbook excelWorkbook = CreateExcelFile();             int rowCount = 0;             int sheetCount = 1;             XSSFSheet newsheet = null;              //循环数据源导出数据集             newsheet = (XSSFSheet)excelWorkbook.CreateSheet("Sheet" + sheetCount);             XSSFCellStyle headCellStyle = (XSSFCellStyle)excelWorkbook.CreateCellStyle(); //创建列头单元格实例样式             CreateHeader(newsheet, excelWorkbook, headCellStyle);             //单元格内容信息             foreach (DataRow dr in dtSource.Rows)             {                 rowCount++;                 //超出10000条数据 创建新的工作簿                 if (rowCount == 10000)                 {                     rowCount = 1;                     sheetCount++;                     newsheet = (XSSFSheet)excelWorkbook.CreateSheet("Sheet" + sheetCount);                     CreateHeader(newsheet, excelWorkbook, headCellStyle);                 }                 XSSFRow newRow = (XSSFRow)newsheet.CreateRow(rowCount);                 XSSFCellStyle cellStyle = (XSSFCellStyle)excelWorkbook.CreateCellStyle(); //创建单元格实例样式                 XSSFCellStyle? style = CreateStyle(excelWorkbook, cellStyle, HorizontalAlignment.Center, VerticalAlignment.Center, 14, true, false);                 InsertCell(dtSource, dr, newRow, style, excelWorkbook);             }             //自动列宽             //for (int i = 0; i <= dtSource.Columns.Count; i++)             //{             //    newsheet.AutoSizeColumn(i, true);             //}             return excelWorkbook;         }          /// <summary>         /// 导出数据行         /// </summary>         /// <param name="dtSource"></param>         /// <param name="drSource"></param>         /// <param name="currentExcelRow"></param>         /// <param name="excelSheet"></param>         /// <param name="excelWorkBook"></param>         private static void InsertCell(DataTable dtSource, DataRow drSource, XSSFRow currentExcelRow, XSSFCellStyle cellStyle, XSSFWorkbook excelWorkBook)         {             for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)             {                 //列名称                 string columnsName = ListColumnsName.GetKey(cellIndex).ToString();                 XSSFCell newCell = null;                 System.Type rowType = drSource[columnsName].GetType();                 string drValue = drSource[columnsName].ToString().Trim();                 switch (rowType.ToString())                 {                     case "System.String"://字符串类型                         drValue = drValue.Replace("&", "&");                         drValue = drValue.Replace(">", ">");                         drValue = drValue.Replace("<", "<");                         newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);                         newCell.SetCellValue(drValue);                         newCell.CellStyle = cellStyle;                         break;                     case "System.DateTime"://日期类型                         DateTime dateV;                         DateTime.TryParse(drValue, out dateV);                         newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);                         newCell.SetCellValue(dateV);                         newCell.CellStyle = cellStyle;                         break;                     case "System.Boolean"://布尔型                         bool boolV = false;                         bool.TryParse(drValue, out boolV);                         newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);                         newCell.SetCellValue(boolV);                         newCell.CellStyle = cellStyle;                         break;                     case "System.Int16"://整型                     case "System.Int32":                     case "System.Int64":                     case "System.Byte":                         int intV = 0;                         int.TryParse(drValue, out intV);                         newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);                         newCell.SetCellValue(intV.ToString());                         newCell.CellStyle = cellStyle;                         break;                     case "System.Decimal"://浮点型                     case "System.Double":                         double doubV = 0;                         double.TryParse(drValue, out doubV);                         newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);                         newCell.SetCellValue(doubV);                         newCell.CellStyle = cellStyle;                         break;                     case "System.DBNull"://空值处理                         newCell = (XSSFCell)currentExcelRow.CreateCell(cellIndex);                         newCell.SetCellValue("");                         newCell.CellStyle = cellStyle;                         break;                     default:                         throw (new Exception(rowType.ToString() + ":类型数据无法处理!"));                 }             }         }          /// <summary>         /// 行内单元格常用样式设置         /// </summary>         /// <param name="workbook">Excel文件对象</param>         /// <param name="cellStyle">Excel文件中XSSFCellStyle对象</param>         /// <param name="hAlignment">水平布局方式</param>         /// <param name="vAlignment">垂直布局方式</param>         /// <param name="fontHeightInPoints">字体大小</param>         /// <param name="isAddBorder">是否需要边框</param>         /// <param name="boldWeight">字体加粗 (None = 0,Normal = 400,Bold = 700</param>         /// <param name="fontName">字体(仿宋,楷体,宋体,微软雅黑...与Excel主题字体相对应)</param>         /// <param name="isAddBorderColor">是否增加边框颜色</param>         /// <param name="isItalic">是否将文字变为斜体</param>         /// <param name="isLineFeed">是否自动换行</param>         /// <param name="isAddCellBackground">是否增加单元格背景颜色</param>         /// <param name="fillPattern">填充图案样式(FineDots 细点,SolidForeground立体前景,isAddFillPattern=true时存在)</param>         /// <param name="cellBackgroundColor">单元格背景颜色(当isAddCellBackground=true时存在)</param>         /// <param name="fontColor">字体颜色</param>         /// <param name="underlineStyle">下划线样式(无下划线[None],单下划线[Single],双下划线[Double],会计用单下划线[SingleAccounting],会计用双下划线[DoubleAccounting])</param>         /// <param name="typeOffset">字体上标下标(普通默认值[None],上标[Sub],下标[Super]),即字体在单元格内的上下偏移量</param>         /// <param name="isStrikeout">是否显示删除线</param>         /// <param name="dataFormat">格式化日期显示</param>         /// <returns></returns>         public static XSSFCellStyle CreateStyle(XSSFWorkbook workbook, XSSFCellStyle cellStyle, HorizontalAlignment hAlignment, VerticalAlignment vAlignment, short fontHeightInPoints, bool isAddBorder, bool boldWeight, string fontName = "宋体", bool isAddBorderColor = true, bool isItalic = false, bool isLineFeed = true, bool isAddCellBackground = false, FillPattern fillPattern = FillPattern.NoFill, short cellBackgroundColor = HSSFColor.Yellow.Index, short fontColor = HSSFColor.Black.Index, FontUnderlineType underlineStyle =             FontUnderlineType.None, FontSuperScript typeOffset = FontSuperScript.None, bool isStrikeout = false,string dataFormat="yyyy-MM-dd HH:mm:ss")         {             cellStyle.Alignment = hAlignment; //水平居中             cellStyle.VerticalAlignment = vAlignment; //垂直居中             cellStyle.WrapText = isLineFeed;//自动换行              //格式化显示             XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();             cellStyle.DataFormat = format.GetFormat(dataFormat);              //背景颜色,边框颜色,字体颜色都是使用 HSSFColor属性中的对应调色板索引,关于 HSSFColor 颜色索引对照表,详情参考:https://www.cnblogs.com/Brainpan/p/5804167.html              //TODO:引用了NPOI后可通过ICellStyle 接口的 FillForegroundColor 属性实现 Excel 单元格的背景色设置,FillPattern 为单元格背景色的填充样式              //TODO:十分注意,要设置单元格背景色必须是FillForegroundColor和FillPattern两个属性同时设置,否则是不会显示背景颜色             if (isAddCellBackground)             {                 cellStyle.FillForegroundColor = cellBackgroundColor;//单元格背景颜色                 cellStyle.FillPattern = fillPattern;//填充图案样式(FineDots 细点,SolidForeground立体前景)             }             else             {                 cellStyle.FillForegroundColor = HSSFColor.White.Index;//单元格背景颜色             }              //是否增加边框             if (isAddBorder)             {                 //常用的边框样式 None(没有),Thin(细边框,瘦的),Medium(中等),Dashed(虚线),Dotted(星罗棋布的),Thick(厚的),Double(双倍),Hair(头发)[上右下左顺序设置]                 cellStyle.BorderBottom = BorderStyle.Thin;                 cellStyle.BorderRight = BorderStyle.Thin;                 cellStyle.BorderTop = BorderStyle.Thin;                 cellStyle.BorderLeft = BorderStyle.Thin;             }              //是否设置边框颜色             if (isAddBorderColor)             {                 //边框颜色[上右下左顺序设置]                 cellStyle.TopBorderColor = XSSFFont.DEFAULT_FONT_COLOR;//DarkGreen(黑绿色)                 cellStyle.RightBorderColor = XSSFFont.DEFAULT_FONT_COLOR;                 cellStyle.BottomBorderColor = XSSFFont.DEFAULT_FONT_COLOR;                 cellStyle.LeftBorderColor = XSSFFont.DEFAULT_FONT_COLOR;             }              /**              * 设置相关字体样式              */             var cellStyleFont = (XSSFFont)workbook.CreateFont(); //创建字体              //假如字体大小只需要是粗体的话直接使用下面该属性即可             //cellStyleFont.IsBold = true;              cellStyleFont.IsBold = boldWeight; //字体加粗             cellStyleFont.FontHeightInPoints = fontHeightInPoints; //字体大小             cellStyleFont.FontName = fontName;//字体(仿宋,楷体,宋体 )             cellStyleFont.Color = fontColor;//设置字体颜色             cellStyleFont.IsItalic = isItalic;//是否将文字变为斜体             cellStyleFont.Underline = underlineStyle;//字体下划线             cellStyleFont.TypeOffset = typeOffset;//字体上标下标             cellStyleFont.IsStrikeout = isStrikeout;//是否有删除线              cellStyle.SetFont(cellStyleFont); //将字体绑定到样式             return cellStyle;         }          #endregion          #region 从excel文件中将数据导出到List<T>对象集合          /// <summary>         /// 将制定sheet中的数据导出到DataTable中         /// </summary>         /// <param name="sheet">需要导出的sheet</param>         /// <param name="HeaderRowIndex">列头所在行号,-1表示没有列头</param>         /// <param name="dir">excel列名和DataTable列名的对应字典</param>         /// <returns></returns>         static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, Dictionary<string, string> dir)         {             DataTable table = new DataTable();             IRow headerRow;             int cellCount;             try             {                 //没有标头或者不需要表头用excel列的序号(1,2,3..)作为DataTable的列名                 if (HeaderRowIndex < 0)                 {                     headerRow = sheet.GetRow(0);                     cellCount = headerRow.LastCellNum;                      for (int i = headerRow.FirstCellNum; i <= cellCount; i++)                     {                         DataColumn column = new DataColumn(Convert.ToString(i));                         table.Columns.Add(column);                     }                 }                 //有表头,使用表头做为DataTable的列名                 else                 {                     headerRow = sheet.GetRow(HeaderRowIndex);                     cellCount = headerRow.LastCellNum;                     for (int i = headerRow.FirstCellNum; i <cellCount; i++)                     {                         //如果excel某一列列名不存在:以该列的序号作为DataTable的列名,如果DataTable中包含了这个序列为名的列,那么列名为重复列名+序号                         if (headerRow.GetCell(i) == null)                         {                             if (table.Columns.IndexOf(Convert.ToString(i)) > 0)                             {                                 DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));                                 table.Columns.Add(column);                             }                             else                             {                                 DataColumn column = new DataColumn(Convert.ToString(i));                                 table.Columns.Add(column);                             }                          }                         //excel中的某一列列名不为空,但是重复了:对应的DataTable列名为“重复列名+序号”                         else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)                         {                             DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));                             table.Columns.Add(column);                         }                         else                         //正常情况,列名存在且不重复:用excel中的列名作为DataTable中对应的列名                         {                             string aaa = headerRow.GetCell(i).ToString();                             string colName = dir.Where(s => s.Value == headerRow.GetCell(i).ToString()).First().Key;                             DataColumn column = new DataColumn(colName);                             table.Columns.Add(column);                         }                     }                 }                 int rowCount = sheet.LastRowNum;                 for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)//excel行遍历                 {                     try                     {                         IRow row;                         if (sheet.GetRow(i) == null)//如果excel有空行,则添加缺失的行                         {                             row = sheet.CreateRow(i);                         }                         else                         {                             row = sheet.GetRow(i);                         }                          DataRow dataRow = table.NewRow();                          for (int j = row.FirstCellNum; j <= cellCount; j++)//excel列遍历                         {                             try                             {                                 if (row.GetCell(j) != null)                                 {                                     switch (row.GetCell(j).CellType)                                     {                                         case CellType.String://字符串                                             string str = row.GetCell(j).StringCellValue;                                             if (str != null && str.Length > 0)                                             {                                                 dataRow[j] = str.ToString();                                             }                                             else                                             {                                                 dataRow[j] = default(string);                                             }                                             break;                                         case CellType.Numeric://数字                                             if (DateUtil.IsCellDateFormatted(row.GetCell(j)))//时间戳数字                                             {                                                 dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);                                             }                                             else                                             {                                                 dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);                                             }                                             break;                                         case CellType.Boolean:                                             dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);                                             break;                                         case CellType.Error:                                             dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);                                             break;                                         case CellType.Formula://公式                                             switch (row.GetCell(j).CachedFormulaResultType)                                             {                                                 case CellType.String:                                                     string strFORMULA = row.GetCell(j).StringCellValue;                                                     if (strFORMULA != null && strFORMULA.Length > 0)                                                     {                                                         dataRow[j] = strFORMULA.ToString();                                                     }                                                     else                                                     {                                                         dataRow[j] = null;                                                     }                                                     break;                                                 case CellType.Numeric:                                                     dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);                                                     break;                                                 case CellType.Boolean:                                                     dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);                                                     break;                                                 case CellType.Error:                                                     dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);                                                     break;                                                 default:                                                     dataRow[j] = "";                                                     break;                                             }                                             break;                                         default:                                             dataRow[j] = "";                                             break;                                     }                                 }                             }                             catch (Exception exception)                             {                                 //loger.Error(exception.ToString());                             }                         }                         table.Rows.Add(dataRow);                     }                     catch (Exception exception)                     {                         //loger.Error(exception.ToString());                     }                 }             }             catch (Exception exception)             {                 //loger.Error(exception.ToString());             }             return table;         }          /// <summary>         /// DataTable 转换为List<T>对象集合         /// </summary>         /// <typeparam name="TResult">类型</typeparam>         /// <param name="dt">DataTable</param>         /// <returns></returns>         public static List<TResult> DataTableToList<TResult>(this DataTable dt) where TResult : class, new()         {             //创建一个属性的列表             List<PropertyInfo> prlist = new List<PropertyInfo>();             //获取TResult的类型实例  反射的入口             Type t = typeof(TResult);             //获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表             Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });             //创建返回的集合             List<TResult> oblist = new List<TResult>();             foreach (DataRow row in dt.Rows)             {                 //创建TResult的实例                 TResult ob = new TResult();                 //找到对应的数据  并赋值                 prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });                 //放入到返回的集合中.                 oblist.Add(ob);             }             return oblist;         }          /// <summary>         /// DataTable转化为List集合         /// </summary>         /// <typeparam name="T">实体对象</typeparam>         /// <param name="dt">datatable表</param>         /// <param name="isStoreDB">是否存入数据库datetime字段,date字段没事,取出不用判断</param>         /// <returns>返回list集合</returns>         private static List<T> DataTableToList<T>(DataTable dt, bool isStoreDB = true)         {             List<T> list = new List<T>();             Type type = typeof(T);             //List<string> listColums = new List<string>();             PropertyInfo[] pArray = type.GetProperties(); //集合属性数组             foreach (DataRow row in dt.Rows)             {                 T entity = Activator.CreateInstance<T>(); //新建对象实例                  foreach (PropertyInfo p in pArray)                 {                     if (!dt.Columns.Contains(p.Name) || row[p.Name] == null || row[p.Name] == DBNull.Value)                     {                         continue;  //DataTable列中不存在集合属性或者字段内容为空则,跳出循环,进行下个循环                        }                     if (isStoreDB && p.PropertyType == typeof(DateTime) && Convert.ToDateTime(row[p.Name]) < Convert.ToDateTime("1753-01-01"))                     {                         continue;                     }                     try                     {                         var obj = Convert.ChangeType(row[p.Name], p.PropertyType);//类型强转,将table字段类型转为集合字段类型                           p.SetValue(entity, obj, null);                     }                     catch (Exception)                     {                         // throw;                     }                              }                 list.Add(entity);             }             return list;         }          /// <summary>         /// DataSet 转换成List         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="ds"></param>         /// <param name="tableIndext"></param>         /// <returns></returns>         private static List<T> DataTable2List<T>(DataTable dt)         {             //确认参数有效              if (dt == null || dt.Rows.Count <= 0)             {                 return null;             }              IList<T> list = new List<T>(); //实例化一个list                                             // 在这里写 获取T类型的所有公有属性。 注意这里仅仅是获取T类型的公有属性,不是公有方法,也不是公有字段,当然也不是私有属性              PropertyInfo[] tMembersAll = typeof(T).GetProperties();              for (int i = 0; i < dt.Rows.Count; i++)             {                 //创建泛型对象。为什么这里要创建一个泛型对象呢?是因为目前我不确定泛型的类型。                  T t = Activator.CreateInstance<T>();                  //获取t对象类型的所有公有属性。但是我不建议吧这条语句写在for循环里,因为没循环一次就要获取一次,占用资源,所以建议写在外面                  //PropertyInfo[] tMembersAll = t.GetType().GetProperties();                 for (int j = 0; j < dt.Columns.Count; j++)                 {                     //遍历tMembersAll                      foreach (PropertyInfo tMember in tMembersAll)                     {                         //取dt表中j列的名字,并把名字转换成大写的字母。整条代码的意思是:如果列名和属性名称相同时赋值                          if (dt.Columns[j].ColumnName.ToUpper().Equals(tMember.Name.ToUpper()))                         {                             //dt.Rows[i][j]表示取dt表里的第i行的第j列;DBNull是指数据库中当一个字段没有被设置值的时候的值,相当于数据库中的“空值”。                              if (dt.Rows[i][j] != DBNull.Value)                             {                                 //SetValue是指:将指定属性设置为指定值。 tMember是T泛型对象t的一个公有成员,整条代码的意思就是:将dt.Rows[i][j]赋值给t对象的tMember成员,参数详情请参照http://msdn.microsoft.com/zh-cn/library/3z2t396t(v=vs.100).aspx/html                                 tMember.SetValue(t, Convert.ToString(dt.Rows[i][j]), null);                             }                             else                             {                                 tMember.SetValue(t, null, null);                             }                             break;//注意这里的break是写在if语句里面的,意思就是说如果列名和属性名称相同并且已经赋值了,那么我就跳出foreach循环,进行j+1的下次循环                          }                     }                 }                 list.Add(t);             }             dt.Dispose();             return list.ToList();          }          /// <summary>         /// 读取Excel文件特定名字sheet的内容到List<T>对象集合         /// </summary>         /// <param name="strFileName">excel文件路径</param>         /// <param name="dir">excel列名和DataTable列名的对应字典</param>         /// <param name="SheetName">excel表名</param>         /// <param name="HeaderRowIndex">列头所在行号,-1表示没有列头</param>         /// <returns></returns>         public static List<T> ImportExceltoDt<T>(string strFileName, Dictionary<string, string> dir, string SheetName, int HeaderRowIndex = 0)         {             DataTable table = new DataTable();             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))             {                 if (file.Length > 0)                 {                     IWorkbook wb = WorkbookFactory.Create(file);                     ISheet isheet = wb.GetSheet(SheetName);                     table = ImportDt(isheet, HeaderRowIndex, dir);                     isheet = null;                 }             }             List<T> results = DataTableToList<T>(table);             table.Dispose();             return results;         }          /// <summary>         /// 读取Excel文件某一索引sheet的内容到DataTable         /// </summary>         /// <param name="strFileName">excel文件路径</param>         /// <param name="sheet">需要导出的sheet序号</param>         /// <param name="HeaderRowIndex">列头所在行号,-1表示没有列头</param>         /// <param name="dir">excel列名和DataTable列名的对应字典</param>         /// <returns></returns>         public static List<T> ImportExceltoDt<T>(string strFileName, Dictionary<string, string> dir, int HeaderRowIndex = 0, int SheetIndex = 0)         {             DataTable table = new DataTable();             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))             {                 if (file.Length > 0)                 {                     IWorkbook wb = WorkbookFactory.Create(file);                     ISheet isheet = wb.GetSheetAt(SheetIndex);                     table = ImportDt(isheet, HeaderRowIndex, dir);                     isheet = null;                 }             }             List<T> results = DataTableToList<T>(table);             table.Dispose();             return results;         }          #endregion          /// <summary>         /// 获取excel文件的sheet数目         /// </summary>         /// <param name="outputFile"></param>         /// <returns></returns>         public static int GetSheetNumber(string outputFile)         {             int number = 0;             using (FileStream readfile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Read))             {                 if (readfile.Length > 0)                 {                     IWorkbook wb = WorkbookFactory.Create(readfile);                     number = wb.NumberOfSheets;                 }             }             return number;         }          /// <summary>         /// 判断内容是否是数字         /// </summary>         /// <param name="message"></param>         /// <param name="result"></param>         /// <returns></returns>         public static bool isNumeric(String message, out double result)         {             Regex rex = new Regex(@"^[-]?d+[.]?d*$");             result = -1;             if (rex.IsMatch(message))             {                 result = double.Parse(message);                 return true;             }             else                 return false;         }          /// <summary>         /// 验证导入的Excel是否有数据         /// </summary>         /// <param name="excelFileStream"></param>         /// <returns></returns>         public static bool HasData(Stream excelFileStream)         {             using (excelFileStream)             {                 IWorkbook workBook = new HSSFWorkbook(excelFileStream);                 if (workBook.NumberOfSheets > 0)                 {                     ISheet sheet = workBook.GetSheetAt(0);                     return sheet.PhysicalNumberOfRows > 0;                 }             }             return false;         }      }      /// <summary>     /// 排序实现接口 不进行排序 根据添加顺序导出     /// </summary>     public class NoSort : IComparer     {         public int Compare(object x, object y)         {             return -1;         }     }  } 

三、调用

3.1、增加一个“keywords”模型类,用作导出

public class keywords {     [Column("姓名")]     public string IllegalKeywords { get; set; } } 

3.2、添加一个控制器

3.3、编写导入导出的控制器代码

3.3.1、重写“Close”函数

在导出时,为了防止MemoryStream无法关闭从而报错,所以我们继承MemoryStream;代码如下:

namespace WebApplication1    //命名空间依据自己的项目进行修改 {     public class NpoiMemoryStream : MemoryStream     {         public NpoiMemoryStream()         {             AllowClose = true;         }          public bool AllowClose { get; set; }          public override void Close()         {             if (AllowClose)                 base.Close();         }     } } 

3.3.2、添加控制器代码

/// <summary> /// 本地环境 /// </summary> private IHostingEnvironment _hostingEnv;   /// <summary> /// Excel导入的具体实现 /// </summary> /// <returns></returns> public IActionResult import_excel() {     string filepath = _hostingEnv.WebRootPath + "/在线用户20230324.xlsx";//导入的文件地址路径,可动态传入     Dictionary<string, string> dir = new Dictionary<string, string>();//申明excel列名和DataTable列名的对应字典     dir.Add("IllegalKeywords","姓名");     List<keywords> keyWordsList = ExcelHelper.ImportExceltoDt<keywords>(filepath, dir,"Sheet1",0);     #region 将List动态添加至数据库     //……     #endregion     return Json(new { code = 200, msg = "导入成功" }); }  /// <summary> /// Excel导出的具体实现 /// </summary> /// <returns></returns> public IActionResult export_excel() {     #region 添加测试数据     List<keywords> keys = new List<keywords>();     for (int i = 0; i < 6; i++)     {         keywords keyword = new keywords();         keyword.IllegalKeywords = "测试_" + i;         keys.Add(keyword);     }     #endregion     #region 实例化DataTable并进行赋值     DataTable dt = new DataTable();     dt = listToDataTable(keys);//List<T>对象集合转DataTable     #endregion     string filename = DateTime.Now.ToString("在线用户yyyyMMdd") + ".xlsx";     Dictionary<string, string> dir = new Dictionary<string, string>();     dir.Add("IllegalKeywords", "姓名");     XSSFWorkbook book= ExcelHelper.ExportExcel(dt,  dir);     dt.Dispose();//释放DataTable所占用的数据资源      NpoiMemoryStream ms = new NpoiMemoryStream();     ms.AllowClose = false;     book.Write(ms, true);     ms.Flush();     ms.Position = 0;     ms.Seek(0, SeekOrigin.Begin);     ms.AllowClose = true;     book.Dispose();//使用由XSSFWorkbook所占用的资源      return File(ms, "application/vnd.ms-excel", Path.GetFileName(filename));//进行浏览器下载 } 

3.3.3、Excel导出效果

.NET Core 实现Excel的导入导出

3.3.4、Excel导入效果

导入后的List再根据需求调用添加方法实现数据的添加
.NET Core 实现Excel的导入导出