2.构建Excel帮助类
开发环境：.Net Core 3.1，NPOI 2.6.0
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
namespace XXXX.Util
{
/// <summary>
/// <para>创 建：Hou</para>
/// <para>日 期：2022年9月7日11:43:55</para>
/// <para>描 述：生成excel帮助类</para>
/// </summary>
public class ExcelHelper
{
#region 类属性
private string tempfile = null;//模板绝对地址
private string savefile = null;//保存excel文件绝对地址
/// <summary>
/// 构造函数
/// </summary>
/// <param name="tempfile">模板绝对地址</param>
/// <param name="savefile">保存excel文件绝对地址</param>
public ExcelHelper(string tempfile, string savefile)
{
this.tempfile = @tempfile;
this.savefile = @savefile;
string directory = this.savefile.Substring(0, this.savefile.LastIndexOf("\\"));
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
}
/**
* Excel 列号转数字
*
* @param excelNum Excel 列号
* @return 数字
*/
private int excelNum2Digit(string excelNum)
{
char[] chs = excelNum.ToCharArray();
int digit = 0;
/*
* B*26^2 + C*26^1 + F*26^0
* = ((0*26 + B)*26 + C)*26 + F
*/
foreach (char ch in chs)
{
digit = digit * 26 + (ch - 'A' + 1);
}
return digit;
}
/**
* 数字转 Excel 列号
*
* @param digit 数字
* @return Excel 列号
*/
private string digit2ExcelNum(int digit)
{
/*
* 找到 digit 所处的维度 len, 它同时表示字母的位数
* power 表示 26^n, 这里 n 分别等于 1, 2, 3
* pre 表示 前 n 个维度的总和, 即 26^1 + 26^2 + 26^3
*/
int len = 0, power = 1, pre = 0;
for (; pre < digit; pre += power)
{
power *= 26;
len++;
}
// 确定字母位数
char[] excelNum = new char[len];
/*
* pre 包含 digit 所处的维度
* pre - power 则是 digit 前面的维度总和
* digit 先减去前面维度和
*/
digit -= pre - power;
/*
* 比较难以理解的是这里为什么要自减 1
* 其实是相对 (digit / power + 'A') 这句代码来的
* 本应该是 (digit / power + 'A' - 1),
* digit / power 的结果是完整的维度个数, 它加上 'A' - 1 后需要再加一
* 当最后剩下的 6 个加上 'A' - 1 是应当的, 不需要做修改
* 而当 (digit / power + 'A') 中没有减 1 后,
* digit / power 的结果不需要再加一了
* 相对于 digit / power 的结果, 最后剩下的 6 需要减 1
*/
digit--;
for (int i = 0; i < len; i++)
{
power /= 26;
excelNum[i] = (char)(digit / power + 'A');
digit %= power;
}
return excelNum.ToString();
}
/// <summary>
/// xlsx绑定基础数据
/// </summary>
/// <param name="ws"></param>
/// <param name="dc"></param>
private void BindDC(ref XSSFSheet ws, Dictionary<string, string> dc)
{
foreach (string item in dc.Keys)
{
string[] items = item.Split("-");
if (items.Length == 2)
{
ws.GetRow(items[1].ParseToInt() - 1).GetCell(excelNum2Digit(items[0]) - 1).SetCellValue(dc[item]);
}
}
}
/// <summary>
/// xls绑定基础数据
/// </summary>
/// <param name="ws"></param>
/// <param name="dc"></param>
private void BindDC(ref HSSFSheet ws, Dictionary<string, string> dc)
{
foreach (string item in dc.Keys)
{
string[] items = item.Split("-");
if (items.Length == 2)
{
ws.GetRow(items[1].ParseToInt() - 1).GetCell(excelNum2Digit(items[0]) - 1).SetCellValue(dc[item]);
}
}
}
/// <summary>
/// xlsx保存文件
/// </summary>
/// <param name="ws"></param>
/// <param name="workbook"></param>
/// <param name="result"></param>
private void SaveFile(ref XSSFSheet ws, XSSFWorkbook workbook, ref TData result)
{
ws.ForceFormulaRecalculation = true;//保存文件
using (FileStream filess = File.OpenWrite(savefile))
{
workbook.Write(filess);
}
result.Message = savefile;
result.Tag = 1;
}
/// <summary>
/// xls保存文件
/// </summary>
/// <param name="ws"></param>
/// <param name="workbook"></param>
/// <param name="result"></param>
private void SaveFile(ref HSSFSheet ws, HSSFWorkbook workbook, ref TData result)
{
ws.ForceFormulaRecalculation = true;//保存文件
using (FileStream filess = File.OpenWrite(savefile))
{
workbook.Write(filess);
}
result.Message = savefile;
result.Tag = 1;
}
/// <summary>
/// 复制行格式并插入指定行数
/// </summary>
/// <param name="sheet">当前sheet</param>
/// <param name="sourceRowIndex">模板行位置</param>
/// <param name="startRowIndex">起始行位置</param>
/// <param name="insertCount">插入行数</param>
public void CopyRow(XSSFWorkbook workbook, ISheet sheet, int sourceRowIndex, int startRowIndex, int insertCount)
{
IRow sourceRow = sheet.GetRow(sourceRowIndex);
int sourceCellCount = sourceRow.Cells.Count;
//处理如果是最后一行，直接使用移动语法会报空指针，这里处理一下最后一行的特殊情况
if (startRowIndex > sheet.LastRowNum)
{
for (int i = 0; i < insertCount; i++)
{
sheet.CreateRow(startRowIndex + i);
}
}
//1. 批量移动行,清空插入区域
sheet.ShiftRows(startRowIndex, //开始行
sheet.LastRowNum, //结束行
insertCount, //插入行总数
true, //是否复制行高
false //是否重置行高
);
Dictionary<int, int> dic_mer = new Dictionary<int, int>();
int m = 0;
while (m < sourceRow.LastCellNum)
{
ICell sourceCell = sourceRow.GetCell(m);
if (sourceCell == null)
continue;
if (sourceCell.IsMergedCell)
{
dic_mer.Add(m, GetLastCol(sheet, sourceRowIndex, m));
m = dic_mer[m] + 1;
}
else
{
dic_mer.Add(m, -1);
m++;
}
}
for (int i = startRowIndex; i < startRowIndex + insertCount; i++)
{
m = 0;
IRow targetRow = null;
ICell sourceCell = null;
ICell targetCell = null;
targetRow = sheet.CreateRow(i);
targetRow.Height = sourceRow.Height;//复制行高
while (m < sourceRow.LastCellNum)
{
sourceCell = sourceRow.GetCell(m);
targetCell = targetRow.CreateCell(m, sourceCell.CellType);
targetCell.CellStyle = sourceCell.CellStyle;//赋值单元格格式
m++;
}
foreach (int item in dic_mer.Keys)
{
sourceCell = sourceRow.GetCell(item);
targetCell = targetRow.CreateCell(item, sourceCell.CellType);
targetCell.CellStyle = sourceCell.CellStyle;//赋值单元格格式
if (dic_mer[item] > 0)
{
sheet.AddMergedRegion(new CellRangeAddress(i, i, item, dic_mer[item]));
}
}
}
}
/// <summary>
/// 获取合并行的最后的列下标
/// </summary>
/// <param name="sheet"></param>
/// <param name="row"></param>
/// <param name="column"></param>
/// <returns></returns>
private int GetLastCol(ISheet sheet, int row, int column)
{
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
if (range != null && range.FirstColumn == column && range.FirstRow == row)
{
return range.LastColumn;
}
}
return -1;
}
/// <summary>
/// 删除某行所有的合并单元格
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
private void RemoveMergedRegion(ISheet sheet, int rowIndex)
{
int MergedCount = sheet.NumMergedRegions;
for (int i = MergedCount - 1; i >= 0; i--)
{
/**
CellRangeAddress对象属性有：FirstColumn，FirstRow，LastColumn，LastRow 进行操作 取消合并单元格
**/
var temp = sheet.GetMergedRegion(i);
if (temp != null && temp.FirstRow == rowIndex)
{
sheet.RemoveMergedRegion(i);
}
}
}
#endregion
#region 样式区
/// <summary>
/// 自定义的样式
/// </summary>
/// <returns></returns>
private ICellStyle styleOne(HSSFWorkbook hssfworkbook)
{
ICellStyle styleOne = hssfworkbook.CreateCellStyle();
styleOne.BorderBottom = BorderStyle.Thick;
styleOne.BorderLeft = BorderStyle.Thick;
styleOne.BorderRight = BorderStyle.Thick;
styleOne.BorderTop = BorderStyle.Thick;
styleOne.VerticalAlignment = VerticalAlignment.Center;
//styleOne.FillBackgroundColor = 244;
styleOne.WrapText = true;
NPOI.SS.UserModel.IFont font = hssfworkbook.CreateFont();
font.FontHeight = 16 * 16;
font.Boldweight = 700;
styleOne.SetFont(font);
return styleOne;
}
/// <summary>
/// 垂直居中
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
private ICellStyle style0(HSSFWorkbook hssfworkbook)
{
ICellStyle style0 = hssfworkbook.CreateCellStyle();
style0.VerticalAlignment = VerticalAlignment.Center;
return style0;
}
/// <summary>
/// 四周薄边框
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
private ICellStyle style1(HSSFWorkbook hssfworkbook)
{
ICellStyle style1 = hssfworkbook.CreateCellStyle();
style1.BorderBottom = BorderStyle.Thin;
style1.BorderLeft = BorderStyle.Thin;
style1.BorderRight = BorderStyle.Thin;
style1.BorderTop = BorderStyle.Thin;
style1.VerticalAlignment = VerticalAlignment.Center;
return style1;
}
/// <summary>
/// 四周厚边框
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
private ICellStyle style2(HSSFWorkbook hssfworkbook)
{
ICellStyle style2 = hssfworkbook.CreateCellStyle();
style2.BorderBottom = BorderStyle.Thick;
style2.BorderLeft = BorderStyle.Thick;
style2.BorderRight = BorderStyle.Thick;
style2.BorderTop = BorderStyle.Thick;
style2.VerticalAlignment = VerticalAlignment.Center; return style2;
}
/// <summary>
/// 顶部厚边框
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
private ICellStyle style3(HSSFWorkbook hssfworkbook)
{
ICellStyle style3 = hssfworkbook.CreateCellStyle();
style3.BorderTop = BorderStyle.Thick;
style3.BorderRight = BorderStyle.Thin;
style3.VerticalAlignment = VerticalAlignment.Center;
return style3;
}
/// <summary>
/// 右部厚边框
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
private ICellStyle style4(HSSFWorkbook hssfworkbook)
{
ICellStyle style4 = hssfworkbook.CreateCellStyle();
style4.BorderRight = BorderStyle.Thick;
style4.VerticalAlignment = VerticalAlignment.Center;
return style4;
}
/// <summary>
/// 左部厚边框
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
private ICellStyle style5(HSSFWorkbook hssfworkbook)
{
ICellStyle style5 = hssfworkbook.CreateCellStyle();
style5.BorderLeft = BorderStyle.Thick;
style5.BorderRight = BorderStyle.Thin;
style5.VerticalAlignment = VerticalAlignment.Center; return style5;
}
/// <summary>
/// 底部厚边框
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
private ICellStyle style6(HSSFWorkbook hssfworkbook)
{
ICellStyle style6 = hssfworkbook.CreateCellStyle();
style6.BorderTop = BorderStyle.Thin;
style6.BorderBottom = BorderStyle.Thick;
style6.BorderRight = BorderStyle.Thin;
style6.VerticalAlignment = VerticalAlignment.Center;
return style6;
}
/// <summary>
/// 文字上对齐
/// </summary>
/// <param name="hssfworkbook"></param>
/// <returns></returns>
private ICellStyle style7(HSSFWorkbook hssfworkbook)
{
ICellStyle style7 = hssfworkbook.CreateCellStyle();
style7.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style7.VerticalAlignment = VerticalAlignment.Top;
return style7;
}
#endregion
}
}这个Excel帮助类大部分都是从网络上或者官网示例中提取来的，只有方法(CopyRow复制行格式并插入指定行数)，是单独写的，为了满足以下情况而设计的：
需求：模板中定义好的字体、边框之类的基础设置，毕竟在代码中去设置字体等格式太麻烦，所以我一般都是写好示例行，然后绑定数据时复制行。例如这里插入的数据按照列名的样式即可，不用单独建立一个示例行了
最终数据图
关于方法BindDC，就是给指定位置插入文本而已，例如下方参数示例
Dictionary<string, string> dc = new Dictionary<string, string>();
dc.Add("A-1", "标题1");
dc.Add("F-2","标题2");
dc.Add("F-3", "标题3");
