##.Net Core Nopi 工具的使用
以下代码效果如图所示：
public class OutputUtil { /// <summary> /// 将数据导出Excel中去，导出文件后缀名为.xlxs /// @author 王伟 /// @weather 多云 /// @date 2020-07-01 16:53 /// @local 北京 /// </summary> /// <param name="dataTable"></param> /// <param name="columns"></param> /// <returns></returns> public static Task<byte[]> OutputFile(DataTable dataTable, string sheetName) { return Task.Run(() => { var workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName);//创建一个Sheet表 IRow title = null, rows = null; for (int i = 1; i <= dataTable.Rows.Count + 1; i++) { if (i - 1 == 0) { //创建列 title = sheet.CreateRow(0); ICell cell_0 = title.CreateCell(0); cell_0.CellStyle = GetCellStyle(workbook, GetFontStyle(workbook, "宋体", new XSSFColor(Color.Red), 10), new XSSFColor(Color.Blue), FillPattern.NoFill, new XSSFColor(Color.Green), HorizontalAlignment.Left, VerticalAlignment.None); cell_0.SetCellValue("序号"); for (int c = 1; c <= dataTable.Columns.Count; c++) { ICell cell = title.CreateCell(c); cell.CellStyle = GetCellStyle(workbook, GetFontStyle(workbook, "宋体", new XSSFColor(Color.Red), 10), new XSSFColor(Color.Blue), FillPattern.NoFill, new XSSFColor(Color.Green), HorizontalAlignment.Left, VerticalAlignment.None); cell.SetCellValue(dataTable.Columns[c - 1].ColumnName); } continue; } else { //创建行 rows = sheet.CreateRow(i - 1); ICell cell_0 = rows.CreateCell(0); cell_0.CellStyle = GetCellStyle(workbook, GetFontStyle(workbook, "宋体", new XSSFColor(Color.Gray), 10), new XSSFColor(Color.Gray), FillPattern.NoFill, new XSSFColor(Color.Gray), HorizontalAlignment.Left, VerticalAlignment.None); cell_0.SetCellValue(i - 1); for (int j = 1; j <= dataTable.Columns.Count; j++) { string obj = dataTable.Rows[i - 2][j - 1].ToString(); ICell cell = rows.CreateCell(j); cell.CellStyle = GetCellStyle(workbook, GetFontStyle(workbook, "微软雅黑", new XSSFColor(Color.Red), 10), new XSSFColor(Color.Blue), FillPattern.NoFill, new XSSFColor(Color.Green), HorizontalAlignment.Left, VerticalAlignment.None); cell.SetCellValue(obj); } } } //列宽自适应，只对英文和数字有效 for (int i = 0; i <= dataTable.Rows.Count; i++) { sheet.AutoSizeColumn(i); } for (int columnNum = 0; columnNum <= dataTable.Rows.Count; columnNum++) { int columnWidth = sheet.GetColumnWidth(columnNum) / 200; for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++) { IRow currentRow; //当前行未被使用过 if (sheet.GetRow(rowNum) == null) { currentRow = sheet.CreateRow(rowNum); } else { currentRow = sheet.GetRow(rowNum); } if (currentRow.GetCell(columnNum) != null) { ICell currentCell = currentRow.GetCell(columnNum); int length = Encoding.Default.GetBytes(currentCell.ToString()).Length; if (columnWidth < length) { columnWidth = length; } } } sheet.SetColumnWidth(columnNum, columnWidth * 200); } byte[] buffer = new byte[1024 * 5]; using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); buffer = ms.ToArray(); workbook = null; ms.Close(); } return buffer; }); } /// <summary> /// 获取单元格样式 /// @author 王伟 /// @date 2020-07-01 16:53 /// @weather 多云 /// @local 北京 /// </summary> /// <param name="hssfworkbook">Excel操作类</param> /// <param name="font">单元格字体</param> /// <param name="fillForegroundColor">图案的颜色</param> /// <param name="fillPattern">图案样式</param> /// <param name="fillBackgroundColor">单元格背景</param> /// <param name="ha">垂直对齐方式</param> /// <param name="va">垂直对齐方式</param> /// <returns></returns> public static ICellStyle GetCellStyle(XSSFWorkbook hssfworkbook, IFont font, XSSFColor fillForegroundColor, FillPattern fillPattern, XSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va) { ICellStyle cellstyle = hssfworkbook.CreateCellStyle(); cellstyle.FillPattern = fillPattern; cellstyle.Alignment = ha; cellstyle.VerticalAlignment = va; if (fillForegroundColor != null) { cellstyle.FillForegroundColor = fillForegroundColor.Indexed; } if (fillBackgroundColor != null) { cellstyle.FillBackgroundColor = fillBackgroundColor.Indexed; } if (font != null) { cellstyle.SetFont(font); } //有边框 cellstyle.BorderBottom = BorderStyle.Thin; cellstyle.BorderLeft = BorderStyle.Thin; cellstyle.BorderRight = BorderStyle.Thin; cellstyle.BorderTop = BorderStyle.Thin; return cellstyle; } /// <summary> /// 获取字体样式 /// @author 王伟 /// @date 2020-07-01 16:53 /// @weather 多云 /// @local 北京 /// </summary> /// <param name="hssfworkbook">Excel操作类</param> /// <param name="fontname">字体名</param> /// <param name="fontcolor">字体颜色</param> /// <param name="fontsize">字体大小</param> /// <returns></returns> public static IFont GetFontStyle(XSSFWorkbook hssfworkbook, string fontfamily, XSSFColor fontcolor, int fontsize) { IFont font1 = hssfworkbook.CreateFont(); if (string.IsNullOrEmpty(fontfamily)) { font1.FontName = fontfamily; } if (fontcolor != null) { font1.Color = fontcolor.Indexed; } font1.IsItalic = false; font1.FontHeightInPoints = (short)fontsize; return font1; } } 其中涉及到表格字体、边框等的设置！欢迎评论点赞。
即使爬到最高的山上，一次也只能脚踏实地地迈一步。
