.Net Core Nopi 工具的使用

电玩女神 2023-02-22 05:05 3阅读 0赞

\#\#**.Net Core Nopi** 工具的使用

以下代码效果如图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzczMjI5_size_16_color_FFFFFF_t_70]

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

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzczMjI5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200701171601587.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzczMjI5,size_16,color_FFFFFF,t_70