- A+
所属分类：.NET技术
前言
我们在日常开发中对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；如下图所示：
1.1、在 “管理NuGet程序包” 中的浏览搜索：“NPOI”
点击安装以上两个即可，安装完成之后最好重新编译一下项目以防出错。
二、新建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导出效果
3.3.4、Excel导入效果
导入后的List再根据需求调用添加方法实现数据的添加
