.Net Core Nopi 工具的使用

##.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;
        }
    }

其中涉及到表格字体、边框等的设置！欢迎评论点赞。     

    

即使爬到最高的山上，一次也只能脚踏实地地迈一步。