.NET Core使用NPOI导出复杂美观的Excel-武穆逸仙

前言

这段时间一直专注于数据报表的开发，当然涉及到相关报表的开发数据导出肯定是一个不可避免的问题啦。客户要求要导出优雅，美观的 Excel 文档格式的来展示数据，当时的第一想法就是使用NPOI开源库来做数据导出 Excel 文档（当时想想真香，网上随便搜一搜教程一大堆），但是当自己真正的实践起来才知道原来想要给不同的单元格设置相关的字体样式、边框样式以及单元格样式一个简单的样式需要写这么多行代码来实现。作为一个喜欢编写简洁代码的我而言肯定是受不了的，于是乎提起袖子说干就干，我自己根据网上的一些资料自己封装了一个通用的NPOI 导出Excel 帮助类，主要包括行列创建，行内单元格常用样式封装（如：字体样式，字体颜色，字体大小，单元格背景颜色，单元格边框，单元格内容对齐方式等常用属性），希望在以后的开发中能够使用到，并且也希望能够帮助到更多有需要的同学。

一、引入NPOI NuGet

NPOI GitHub 源码地址：https://github.com/tonyqus/npoi

版本说明：NPOI 2.4.1 （注意不同版本可能使用的姿势有点小差别）

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

Install-Package NPOI -Version 2.4.1

通过 NuGet 管理解决方案安装

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

.NET Core 使用 NPOI 导出复杂美观的 Excel

搜索：NPOI进行安装

.NET Core 使用 NPOI 导出复杂美观的 Excel

二、导出 Excel 文档内容展示格式如下如所示

.NET Core 使用 NPOI 导出复杂美观的 Excel

三、CellStyle 单元格常用样式概述

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 字体常用属性概述

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）

.NET Core 使用 NPOI 导出复杂美观的 Excel

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

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

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

.NET Core 使用 NPOI 导出复杂美观的 Excel

创建 Excel 中指定的行

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

指定合并的行列

//TODO:关于 Excel 行列单元格合并问题(注意：合并单元格后，只需对第一个位置赋值即可)
/**
  第一个参数：从第几行开始合并
  第二个参数：到第几行结束合并
  第三个参数：从第几列开始合并
  第四个参数：到第几列结束合并
**/

CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
sheet.AddMergedRegion(region);

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

设置单元格的列宽

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