.NET Core NPOI导出复杂Excel

2023-12-28 22:02:07

一、引入NPOI NuGet:

NPOI GitHub源码地址:

GitHub - tonyqus/npoi: a .NET library that can read/write Office formats without Microsoft Office installed. No COM+, no interop.

版本说明:

  NPOI 2.4.1 (注意不同版本可能使用的姿势有点小差别)

程序包管理器控制台输入一下命令安装:

1

Install-Package NPOI -Version 2.4.1

通过NuGet管理解决方案安装:

选择=>工具=>NuGet包管理器=>程序包管理器控制台:

?搜索:NPOI进行安装:

? 二、CellStyle单元格常用样式概述:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();?//创建列头单元格实例样式

cellStyle.Alignment = hAlignment;?//水平布局方式(HorizontalAlignment hAlignment)

cellStyle.VerticalAlignment = vAlignment;?//垂直布局方式(VerticalAlignment vAlignment)

cellStyle.WrapText =false;//是否自动换行

//TODO:十分注意,要设置单元格背景色必须是FillForegroundColor和FillPattern两个属性同时设置,否则是不会显示背景颜色

//如下设置黄色背景色

cellStyle.FillForegroundColor = cellBackgroundColor;//单元格背景颜色(short cellBackgroundColor = HSSFColor.Yellow.Index)

cellStyle.FillPattern = fillPattern;//填充图案样式(FineDots 细点,SolidForeground立体前景,isAddFillPattern=true时存在(FillPattern fillPattern = FillPattern.SolidForeground)

//设置单元格边框样式

//常用的边框样式 None(没有),Thin(细边框,瘦的),Medium(中等),Dashed(虚线),Dotted(星罗棋布的),Thick(厚的),Double(双倍),Hair(头发)[上右下左顺序设置]

cellStyle.BorderBottom = BorderStyle.Thin;

cellStyle.BorderRight = BorderStyle.Thin;

cellStyle.BorderTop = BorderStyle.Thin;

cellStyle.BorderLeft = BorderStyle.Thin;

//设置单元格边框颜色[上右下左顺序设置]

cellStyle.TopBorderColor = HSSFColor.DarkGreen.Index;//DarkGreen(黑绿色)

cellStyle.RightBorderColor = HSSFColor.DarkGreen.Index;

cellStyle.BottomBorderColor = HSSFColor.DarkGreen.Index;

cellStyle.LeftBorderColor = HSSFColor.DarkGreen.Index;

三、Font字体常用属性概述:

1

2

3

4

5

6

7

8

9

10

11

12

13

var?cellStyleFont = (HSSFFont)workbook.CreateFont();//创建字体对象实例

//假如字体大小只需要是粗体的话直接使用下面该属性即可

cellStyleFont.IsBold =?true;

//或者通过下面属性,设置字体weight来设置字体是否加粗

cellStyleFont.Boldweight = boldWeight;?//字体加粗(字体加粗 (None = 0,Normal = 400,Bold = 700)

cellStyleFont.FontHeightInPoints = fontHeightInPoints;?//字体大小(short fontHeightInPoints)

cellStyleFont.FontName = fontName;//字体(仿宋,楷体,宋体 )

cellStyleFont.Color = fontColor;//设置字体颜色(short fontColor = HSSFColor.Black.Index)

cellStyleFont.IsItalic =true;//是否将文字变为斜体(true是,false否)

cellStyleFont.Underline = underlineStyle;//字体下划线(下划线样式(无下划线[None],单下划线[Single],双下划线[Double],会计用单下划线[SingleAccounting],会计用双下划线[DoubleAccounting]))

cellStyleFont.TypeOffset = typeOffset;//字体上标下标(字体上标下标(普通默认值[None],上标[Sub],下标[Super]),即字体在单元格内的上下偏移量)

cellStyleFont.IsStrikeout =true;//是否显示删除线(true显示,false不显示)

四、NPOI HSSFColor 颜色索引对照表:

说明:通过对相关属性的介绍,我们可以清楚的知道无论是字体颜色,表格边框颜色,还是单元格背景颜色我们都需要用到HSSFColor对象中的颜色属性索引(该索引的字符类型为short类型)。

HSSFColor颜色索引对照表如下表所示:

表格来源:https://www.cnblogs.com/Brainpan/p/5804167.html??(潘小博1992)

颜色Class名称(注意由于NPOI的版本原因,我们在使用对应Class名称时首字母可能需要小写)short
 Black8
 Brown60
 Olive_Green59
 Dark_Green58
 Dark_Teal56
 Dark_Blue18
 Indigo62
 Grey_80_PERCENT63
 Dark_Red16
 Orange53
 DARK_YELLOW19
 Green17
 Teal21
 Blue12
 Blue_Grey54
 Grey_50_PERCENT23
 Red10
 LIGHT_ORANGE52
 LIME50
 SEA_GREEN57
 AQUA49
 LIGHT_BLUE48
 VIOLET20
 GREY_40_PERCENT55
 Pink14
 Gold51
 Yellow13
 BRIGHT_GREEN11
 TURQUOISE15
 SKY_BLUE40
 Plum61
 GREY_25_PERCENT22
 Rose45
 Tan47
 LIGHT_YELLOW43
 LIGHT_GREEN42
 LIGHT_TURQUOISE41
 PALE_BLUE44
 LAVENDER46
 White9
 CORNFLOWER_BLUE24
 LEMON_CHIFFON26
 MAROON25
 ORCHID28
 CORAL29
 ROYAL_BLUE30
 LIGHT_CORNFLOWER_BLUE31
 AUTOMATIC64

?五、关于NPOI对Excel中的行列常用操作方法概述:

创建Excel工作表,给工作表赋一个名称(Excel底部名称):

1

var?sheet = workbook.CreateSheet("人才培训课程表");

?创建Excel中指定的行:

1

2

3

4

5

6

7

8

9

10

11

12

13

/// <summary>

/// TODO:先创建行,然后在创建对应的列

/// 创建Excel中指定的行

/// </summary>

/// <param name="sheet">Excel工作表对象</param>

/// <param name="rowNum">创建第几行(从0开始)</param>

/// <param name="rowHeight">行高</param>

public?HSSFRow CreateRow(ISheet sheet,?int?rowNum,?float?rowHeight)

{

????HSSFRow row = (HSSFRow)sheet.CreateRow(rowNum);?//创建行

????row.HeightInPoints = rowHeight;?//设置列头行高

????return?row;

}

创建行内指定的单元格:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

/// <summary>

/// 创建行内指定的单元格

/// </summary>

/// <param name="row">需要创建单元格的行</param>

/// <param name="cellStyle">单元格样式</param>

/// <param name="cellNum">创建第几个单元格(从0开始)</param>

/// <param name="cellValue">给单元格赋值</param>

/// <returns></returns>

public?HSSFCell CreateCells(HSSFRow row, HSSFCellStyle cellStyle,?int?cellNum,?string?cellValue)

{

????HSSFCell cell = (HSSFCell)row.CreateCell(cellNum);?//创建单元格

????cell.CellStyle = cellStyle;?//将样式绑定到单元格

????if?(!string.IsNullOrWhiteSpace(cellValue))

????{

????????//单元格赋值

????????cell.SetCellValue(cellValue);

????}

????return?cell;

}

指定合并的行列:  

1

2

3

4

5

6

7

8

9

10

11

//TODO:关于Excel行列单元格合并问题(注意:合并单元格后,只需对第一个位置赋值即可)

/**

??第一个参数:从第几行开始合并

??第二个参数:到第几行结束合并

??第三个参数:从第几列开始合并

??第四个参数:到第几列结束合并

**/

CellRangeAddress region =?new?CellRangeAddress(0, 0, 0, 5);

sheet.AddMergedRegion(region);

cell.SetCellValue("人才培训课程表");//TODO:顶部标题

设置单元格的列宽:

1

sheet.SetColumnWidth(单元格索引,1000);//设置对应列宽(单元格索引从0开始,后面接宽度)  

六、NPOI生成Excel文档完整代码:

NPOI之Excel数据导出帮助类(创建Excel表格行列,设置行高,设置字体样式,单元格边框样式,单元格背景颜色和样式,单元格内容对齐方式等常用属性和样式封装):

/**
 * Author:追逐时光
 * Description:Npoi之Excel数据导出帮助类(创建Excel表格行列,设置行高,设置字体样式,单元格边框样式,单元格背景颜色和样式,单元格内容对齐方式等常用属性和样式封装)
 * Description:2020年3月29日
 */
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;

namespace YY_Utility
{
    public class NpoiExcelExportHelper
    {
        private static NpoiExcelExportHelper _exportHelper;

        public static NpoiExcelExportHelper _
        {
            get => _exportHelper ?? (_exportHelper = new NpoiExcelExportHelper());
            set => _exportHelper = value;
        }

        /// <summary>
        /// TODO:先创建行,然后在创建对应的列
        /// 创建Excel中指定的行
        /// </summary>
        /// <param name="sheet">Excel工作表对象</param>
        /// <param name="rowNum">创建第几行(从0开始)</param>
        /// <param name="rowHeight">行高</param>
        public HSSFRow CreateRow(ISheet sheet, int rowNum, float rowHeight)
        {
            HSSFRow row = (HSSFRow)sheet.CreateRow(rowNum); //创建行
            row.HeightInPoints = rowHeight; //设置列头行高
            return row;
        }

        /// <summary>
        /// 创建行内指定的单元格
        /// </summary>
        /// <param name="row">需要创建单元格的行</param>
        /// <param name="cellStyle">单元格样式</param>
        /// <param name="cellNum">创建第几个单元格(从0开始)</param>
        /// <param name="cellValue">给单元格赋值</param>
        /// <returns></returns>
        public HSSFCell CreateCells(HSSFRow row, HSSFCellStyle cellStyle, int cellNum, string cellValue)
        {
            HSSFCell cell = (HSSFCell)row.CreateCell(cellNum); //创建单元格
            cell.CellStyle = cellStyle; //将样式绑定到单元格
            if (!string.IsNullOrWhiteSpace(cellValue))
            {
                //单元格赋值
                cell.SetCellValue(cellValue);
            }

            return cell;
        }


        /// <summary>
        /// 行内单元格常用样式设置
        /// </summary>
        /// <param name="workbook">Excel文件对象</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>
        /// <returns></returns>
        public HSSFCellStyle CreateStyle(HSSFWorkbook workbook, HorizontalAlignment hAlignment, VerticalAlignment vAlignment, short fontHeightInPoints, bool isAddBorder, short boldWeight, string fontName = "宋体", bool isAddBorderColor = true, bool isItalic = false, bool isLineFeed = false, 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)
        {
            HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //创建列头单元格实例样式
            cellStyle.Alignment = hAlignment; //水平居中
            cellStyle.VerticalAlignment = vAlignment; //垂直居中
            cellStyle.WrapText = isLineFeed;//自动换行

            //背景颜色,边框颜色,字体颜色都是使用 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立体前景)
            }


            //是否增加边框
            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 = HSSFColor.DarkGreen.Index;//DarkGreen(黑绿色)
                cellStyle.RightBorderColor = HSSFColor.DarkGreen.Index;
                cellStyle.BottomBorderColor = HSSFColor.DarkGreen.Index;
                cellStyle.LeftBorderColor = HSSFColor.DarkGreen.Index;
            }

            /**
             * 设置相关字体样式
             */
            var cellStyleFont = (HSSFFont)workbook.CreateFont(); //创建字体

            //假如字体大小只需要是粗体的话直接使用下面该属性即可
            //cellStyleFont.IsBold = true;

            cellStyleFont.Boldweight = 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;
        }
    }
}

生成并保存指定的Excel文档内容:

using System;
using System.IO;
using Microsoft.AspNetCore.Hosting;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using YY_Utility;

namespace YY_Services
{
    /// <summary>
    /// Excel文档生成并保存操作类
    /// </summary>
    public class NpoiExcelOperationService
    {
        private static IHostingEnvironment _environment;

        public NpoiExcelOperationService(IHostingEnvironment iEnvironment)
        {
            _environment = iEnvironment;
        }

        /// <summary>
        /// Excel数据导出简单示例
        /// </summary>
        /// <param name="resultMsg">导出结果</param>
        /// <param name="excelFilePath">保存excel文件路径</param>
        /// <returns></returns>
        public bool ExcelDataExport(out string resultMsg, out string excelFilePath)
        {
            var result = true;
            excelFilePath = "";
            resultMsg = "successfully";
            //Excel导出名称
            string excelName = "人才培训课程表";
            try
            {
                //首先创建Excel文件对象
                var workbook = new HSSFWorkbook();

                //创建工作表,也就是Excel中的sheet,给工作表赋一个名称(Excel底部名称)
                var sheet = workbook.CreateSheet("人才培训课程表");

                //sheet.DefaultColumnWidth = 20;//默认列宽

                sheet.ForceFormulaRecalculation = true;//TODO:是否开始Excel导出后公式仍然有效(非必须)

                #region table 表格内容设置

                #region 标题样式

                //设置顶部大标题样式
                var cellStyleFont = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 20, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Coral.Index, HSSFColor.White.Index,
                    FontUnderlineType.None, FontSuperScript.None, false);

                //第一行表单
                var row = NpoiExcelExportHelper._.CreateRow(sheet, 0, 28);

                var cell = row.CreateCell(0);
                //合并单元格 例: 第1行到第2行 第3列到第4列围成的矩形区域

                //TODO:关于Excel行列单元格合并问题
                /**
                  第一个参数:从第几行开始合并
                  第二个参数:到第几行结束合并
                  第三个参数:从第几列开始合并
                  第四个参数:到第几列结束合并
                **/
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
                sheet.AddMergedRegion(region);

                cell.SetCellValue("人才培训课程表");//合并单元格后,只需对第一个位置赋值即可(TODO:顶部标题)
                cell.CellStyle = cellStyleFont;

                //二级标题列样式设置
                var headTopStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 15, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Grey25Percent.Index, HSSFColor.Black.Index,
                FontUnderlineType.None, FontSuperScript.None, false);

                //表头名称
                var headerName = new[] { "课程类型", "序号", "日期", "课程名称", "内容概要", "讲师简介" };

                row = NpoiExcelExportHelper._.CreateRow(sheet, 1, 24);//第二行
                for (var i = 0; i < headerName.Length; i++)
                {
                    cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, i, headerName[i]);

                    //设置单元格宽度
                    if (headerName[i] == "讲师简介" || headerName[i] == "内容概要")
                    {
                        sheet.SetColumnWidth(i, 10000);
                    }
                    else

                    {
                        sheet.SetColumnWidth(i, 5000);
                    }

                }
                #endregion


                #region 单元格内容信息

                //单元格边框样式
                var cellStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 10, true, 400);

                //左侧列单元格合并 begin
                //TODO:关于Excel行列单元格合并问题(合并单元格后,只需对第一个位置赋值即可)
                /**
                  第一个参数:从第几行开始合并
                  第二个参数:到第几行结束合并
                  第三个参数:从第几列开始合并
                  第四个参数:到第几列结束合并
                **/
                CellRangeAddress leftOne = new CellRangeAddress(2, 7, 0, 0);

                sheet.AddMergedRegion(leftOne);

                CellRangeAddress leftTwo = new CellRangeAddress(8, 11, 0, 0);

                sheet.AddMergedRegion(leftTwo);

                //左侧列单元格合并 end

                var currentDate = DateTime.Now;

                string[] curriculumList = new[] { "艺术学", "设计学", "材料学", "美学", "心理学", "中国近代史", "管理人员的情绪修炼", "高效时间管理", "有效的目标管理", "沟通与协调" };

                int number = 1;

                for (var i = 0; i < 10; i++)
                {
                    row = NpoiExcelExportHelper._.CreateRow(sheet, i + 2, 20); //sheet.CreateRow(i+2);//在上面表头的基础上创建行
                    switch (number)
                    {
                        case 1:
                            cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, "公共类课程");
                            break;
                        case 7:
                            cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, "管理类课程");
                            break;
                    }

                    //创建单元格列公众类课程
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 1, number.ToString());
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 2, currentDate.AddDays(number).ToString("yyyy-MM-dd"));
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 3, curriculumList[i]);
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 4, "提升,充实,拓展自己综合实力");
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 5, "追逐时光_" + number + "号金牌讲师!");

                    number++;
                }
                #endregion

                #endregion

                string folder = DateTime.Now.ToString("yyyyMMdd");


                //保存文件到静态资源文件夹中(wwwroot),使用绝对路径
                var uploadPath = _environment.WebRootPath + "/UploadFile/" + folder + "/";

                //excel保存文件名
                string excelFileName = excelName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

                //创建目录文件夹
                if (!Directory.Exists(uploadPath))
                {
                    Directory.CreateDirectory(uploadPath);
                }

                //Excel的路径及名称
                string excelPath = uploadPath + excelFileName;

                //使用FileStream文件流来写入数据(传入参数为:文件所在路径,对文件的操作方式,对文件内数据的操作)
                var fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);

                //向Excel文件对象写入文件流,生成Excel文件
                workbook.Write(fileStream);

                //关闭文件流
                fileStream.Close();

                //释放流所占用的资源
                fileStream.Dispose();

                //excel文件保存的相对路径,提供前端下载
                var relativePositioning = "/UploadFile/" + folder + "/" + excelFileName;

                excelFilePath = relativePositioning;
            }
            catch (Exception e)
            {
                result = false;
                resultMsg = e.Message;
            }
            return result;
        }

    }
}

文章来源:https://blog.csdn.net/softshow1026/article/details/135277736
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。