使用NPOI做Excel简单报表

前言

前面介绍了NPOI单元格样式的使用、单元格合并，以及NPOI提供的颜色。现在用上前面的一些知识点，做一个测试结果表格。

1、 介绍NPOI 的颜色卡、名称以及索引 https://editor.csdn.net/md/?articleId=130265415
2、 NPOI的CellStyle单元格样式 https://editor.csdn.net/md/?articleId=130245869
3、 将DataTable中的数据保存到Excel （二） 使用NPOI https://editor.csdn.net/md/?articleId=130225379

初版表格，单元格的合并

先上一个简单版本的，主要内容在于跨行和跨列单元格的合并。涉及的主要函数是Sheet的AddMergedRegion 以及NPOI.SS.Util.CellRangeAddress这两个函数。
这里使用的NPOI版本是 2.0.6.0， 框架 .NET Framework 4.0。

using NPOI.HSSF.Util;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

        /// <summary>/// 合并单元格/// </summary>void MergeCell(){//创建工作簿对象HSSFWorkbook workBook = new HSSFWorkbook();//创建一个sheetISheet sheet = workBook.CreateSheet("MergeTable");IRow row;ICell cell;//行索引int rowIndex = 0;//标题row = sheet.CreateRow(rowIndex);cell = row.CreateCell(0);cell.SetCellValue("测试报告");for (int i=1; i<8; i++){cell = row.CreateCell(i);}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 7));rowIndex++;row = sheet.CreateRow(rowIndex);cell = row.CreateCell(0);cell.SetCellValue("机型：单相   功率：2.2kw   电流：1.8A  测试时间：2023/04/20  耗时：3分钟");for (int i = 1; i < 8; i++){cell = row.CreateCell(i);}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 7));rowIndex++;//记录起始项int startIndex = rowIndex;//第一个小表格的标题row = sheet.CreateRow(rowIndex);cell = row.CreateCell(0);cell.SetCellValue("品质测试");List<string> valueList = new List<string>(){"序号","测试项", "产品", "合格数据", "测试结果"};for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i+1);cell.SetCellValue (valueList[i]);}rowIndex++;//第一条测试结果row = sheet.CreateRow(rowIndex);valueList.Clear();valueList.AddRange(new string[]{"", "1", "通讯测试","成功", "", "/", "", "合格"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i);cell.SetCellValue(valueList[i]);}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 3, 4));sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 5, 6));rowIndex++;//第二条测试结果row = sheet.CreateRow(rowIndex);valueList.Clear();valueList.AddRange(new string[]{"", "2", "温度测试","25℃", "", "23-30℃", "", "合格"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i);cell.SetCellValue(valueList[i]);}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 3, 4));sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 5, 6));rowIndex++;//第三条测试结果row = sheet.CreateRow(rowIndex);valueList.Clear();valueList.AddRange(new string[]{"", "3", "故障测试","E001", "", "/", "", "不合格"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i);cell.SetCellValue(valueList[i]);}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 3, 4));sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 5, 6));//品质测试 要跨行sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startIndex, rowIndex, 0, 0));rowIndex++;startIndex = rowIndex;//第二个 小表格row = sheet.CreateRow(rowIndex);cell = row.CreateCell(0);cell.SetCellValue("性能测试");valueList.Clear();valueList.AddRange(new string[]{"序号","测试项", "产品", "对比项", "最小值" ,"最大值", "测试结果"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i+1);cell.SetCellValue(valueList[i]);}rowIndex++;//第一条测试结果row = sheet.CreateRow(rowIndex);valueList.Clear();valueList.AddRange(new string[]{"", "1", "版本检测","112", "112", "", "", "合格"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i);cell.SetCellValue(valueList[i]);}rowIndex++;//第二条测试结果row = sheet.CreateRow(rowIndex);valueList.Clear();valueList.AddRange(new string[]{"","2", "带载检测","10.4/10.2", "", "10.1", "10.6", "合格"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i);cell.SetCellValue(valueList[i]);}//品质测试 要跨行sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startIndex, rowIndex, 0, 0));rowIndex++;row = sheet.CreateRow(rowIndex);cell = row.CreateCell(0);cell.SetCellValue("测试总结果：");for (int i = 1; i < valueList.Count; i++){cell = row.CreateCell(i+1);if( i == 3){cell.SetCellValue("不合格");}}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 2));sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 3, 7));using (FileStream file = new FileStream(@"测试结果.xls", FileMode.Create)){workBook.Write(file);file.Close();}}

效果图：

进阶表格，单元格美化

设置行高列宽，对单元格的对齐方式、边框、字体大小IFont、颜色进行设置美化, 使用的是ICellStyle.
先看一下最终的效果图，由结果分析向上分析。

测试报告
行高30，居中显示，字体： 黑体，16号，加粗， 设置单元格边框。

            //大标题 黑体 16号 加边框  居中显示 行高30string fontName = "黑体";ICellStyle totalHeader = workBook.CreateCellStyle();//垂直居中对齐totalHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;totalHeader.VerticalAlignment = VerticalAlignment.Center;//边框totalHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;totalHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;totalHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;totalHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//字体IFont totalFont = workBook.CreateFont();totalFont.FontName = fontName;totalFont.FontHeightInPoints = 16;totalFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;totalHeader.SetFont(totalFont);

第二行 机型
行高20， 居中显示， 字体 ：微软雅黑12号 设置单元格边框。

           //中间行样式 机型 行高20 居中显示 字体 微软雅黑12号 加边框ICellStyle lineStyle = workBook.CreateCellStyle();//垂直居中对齐lineStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;lineStyle.VerticalAlignment = VerticalAlignment.Center;//边框lineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;lineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;lineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;lineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//字体大小IFont midFont = workBook.CreateFont();midFont.FontHeightInPoints = 12;midFont.FontName = "微软雅黑";lineStyle.SetFont(midFont);

左侧跨单元格 品质（性能）测试 总结果
居中显示， 字体微软雅黑12号加粗 ，设置单元格边框。在单元格合并后再设置样式。

          //左侧跨行单元格的样式 居中显示 字体微软雅黑12号加粗 设置单元格边框ICellStyle leftCellStyle = workBook.CreateCellStyle();//对齐leftCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;leftCellStyle.VerticalAlignment = VerticalAlignment.Center;//边框leftCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;leftCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;leftCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;leftCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//字体大小IFont leftFont = workBook.CreateFont();leftFont.FontHeightInPoints = 12;leftFont.FontName = "微软雅黑";leftFont.Boldweight = (short)(short)NPOI.SS.UserModel.FontBoldWeight.Bold; ;leftCellStyle.SetFont(leftFont);

带背景小标题
行高20，居中显示，字体字体微软雅黑12号，设置单元格边框，设置背景色。
要想设置的背景色起作用，必须要设置 midTitleStyle.FillPattern = FillPattern.SolidForeground;这句

          //小标题的样式 行高20，居中显示，字体字体微软雅黑12号，设置单元格边框，设置背景色ICellStyle midTitleStyle = workBook.CreateCellStyle();//对齐midTitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;midTitleStyle.VerticalAlignment = VerticalAlignment.Center;//边框midTitleStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;midTitleStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;midTitleStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;midTitleStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//需要设置图例，颜色才会起作用midTitleStyle.FillPattern = FillPattern.SolidForeground;//背景颜色midTitleStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;midTitleStyle.SetFont(midFont);

中间的测试内容
行高15，左对齐，设置单元格边框。

            //内容的样式  行高15 左对齐 设置单元格边框ICellStyle contentStyle = workBook.CreateCellStyle();//对齐contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;contentStyle.VerticalAlignment = VerticalAlignment.Center;//边框contentStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;contentStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;contentStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;contentStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

全部代码

        /// <summary>/// 合并单元格/// </summary>void MergeCell(){//创建工作簿对象HSSFWorkbook workBook = new HSSFWorkbook();//创建一个sheetISheet sheet = workBook.CreateSheet("MergeTable");//设置列宽sheet.SetColumnWidth(0, 15 * 256);sheet.SetColumnWidth(1, 10 * 256);sheet.SetColumnWidth(2, 10 * 256);sheet.SetColumnWidth(3, 10 * 256);sheet.SetColumnWidth(4, 10 * 256);sheet.SetColumnWidth(5, 10 * 256);sheet.SetColumnWidth(7, 12 * 256);IRow row;ICell cell;//大标题 黑体 16号 加边框  居中显示 行高30string fontName = "黑体";ICellStyle totalHeader = workBook.CreateCellStyle();//垂直居中对齐totalHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;totalHeader.VerticalAlignment = VerticalAlignment.Center;//边框totalHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;totalHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;totalHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;totalHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//字体IFont totalFont = workBook.CreateFont();totalFont.FontName = fontName;totalFont.FontHeightInPoints = 16;totalFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;totalHeader.SetFont(totalFont);//行索引int rowIndex = 0;//标题row = sheet.CreateRow(rowIndex);//设置行高row.HeightInPoints = 30;cell = row.CreateCell(0);cell.SetCellValue("测试报告");//设置单元格样式cell.CellStyle = totalHeader;for (int i=1; i<8; i++){cell = row.CreateCell(i);//设置单元格样式cell.CellStyle = totalHeader;}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 7));rowIndex++;//中间行样式 机型 行高20 居中显示 字体 微软雅黑12号 加边框ICellStyle lineStyle = workBook.CreateCellStyle();//垂直居中对齐lineStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;lineStyle.VerticalAlignment = VerticalAlignment.Center;//边框lineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;lineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;lineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;lineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//字体大小IFont midFont = workBook.CreateFont();midFont.FontHeightInPoints = 12;midFont.FontName = "微软雅黑";lineStyle.SetFont(midFont);row = sheet.CreateRow(rowIndex);//行高20row.HeightInPoints = 20;cell = row.CreateCell(0);cell.SetCellValue("机型：单相   功率：2.2kw   电流：1.8A  测试时间：2023/04/20  耗时：3分钟");//设置样式cell.CellStyle = lineStyle;for (int i = 1; i < 8; i++){cell = row.CreateCell(i);cell.CellStyle = lineStyle;}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 7));rowIndex++;//小标题的样式 行高20，居中显示，字体字体微软雅黑12号，设置单元格边框，设置背景色ICellStyle midTitleStyle = workBook.CreateCellStyle();//对齐midTitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;midTitleStyle.VerticalAlignment = VerticalAlignment.Center;//边框midTitleStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;midTitleStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;midTitleStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;midTitleStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//需要设置图例，颜色才会起作用midTitleStyle.FillPattern = FillPattern.SolidForeground;//背景颜色midTitleStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;midTitleStyle.SetFont(midFont);//内容的样式  行高15 左对齐 设置单元格边框ICellStyle contentStyle = workBook.CreateCellStyle();//对齐contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;contentStyle.VerticalAlignment = VerticalAlignment.Center;//边框contentStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;contentStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;contentStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;contentStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//左侧跨行单元格的样式 居中显示 字体微软雅黑12号加粗 设置单元格边框ICellStyle leftCellStyle = workBook.CreateCellStyle();//对齐leftCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;leftCellStyle.VerticalAlignment = VerticalAlignment.Center;//边框leftCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;leftCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;leftCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;leftCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//字体大小IFont leftFont = workBook.CreateFont();leftFont.FontHeightInPoints = 12;leftFont.FontName = "微软雅黑";leftFont.Boldweight = (short)(short)NPOI.SS.UserModel.FontBoldWeight.Bold; ;leftCellStyle.SetFont(leftFont);//记录起始项int startIndex = rowIndex;//第一个小表格的标题row = sheet.CreateRow(rowIndex);//行高row.HeightInPoints = 20;cell = row.CreateCell(0);cell.SetCellValue("品质测试");List<string> valueList = new List<string>(){"序号","测试项", "产品", "", "合格数据","", "测试结果"};for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i+1);cell.SetCellValue (valueList[i]);//样式cell.CellStyle = midTitleStyle;}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 3, 4));sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 5, 6));rowIndex++;//第一条测试结果row = sheet.CreateRow(rowIndex);row.HeightInPoints = 15;valueList.Clear();valueList.AddRange(new string[]{"", "1", "通讯测试","成功", "", "/", "", "合格"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i);cell.SetCellValue(valueList[i]);//内容样式cell.CellStyle = contentStyle;}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 3, 4));sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 5, 6));rowIndex++;//第二条测试结果row = sheet.CreateRow(rowIndex);row.HeightInPoints = 15;valueList.Clear();valueList.AddRange(new string[]{"", "2", "温度测试","25℃", "", "23-30℃", "", "合格"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i);cell.SetCellValue(valueList[i]);//内容样式cell.CellStyle = contentStyle;}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 3, 4));sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 5, 6));rowIndex++;//第三条测试结果row = sheet.CreateRow(rowIndex);row.HeightInPoints = 15;valueList.Clear();valueList.AddRange(new string[]{"", "3", "故障测试","E001", "", "/", "", "不合格"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i);cell.SetCellValue(valueList[i]);//内容样式cell.CellStyle = contentStyle;}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 3, 4));sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 5, 6));//品质测试 要跨行sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startIndex, rowIndex, 0, 0));sheet.GetRow(startIndex).GetCell(0).CellStyle = leftCellStyle;rowIndex++;startIndex = rowIndex;//第二个 小表格row = sheet.CreateRow(rowIndex);//行高row.HeightInPoints = 20;cell = row.CreateCell(0);cell.SetCellValue("性能测试");valueList.Clear();valueList.AddRange(new string[]{"序号","测试项", "产品", "对比项", "最小值" ,"最大值", "测试结果"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i+1);cell.SetCellValue(valueList[i]);cell.CellStyle = midTitleStyle;}rowIndex++;//第一条测试结果row = sheet.CreateRow(rowIndex);row.HeightInPoints = 15;valueList.Clear();valueList.AddRange(new string[]{"", "1", "版本检测","112", "112", "", "", "合格"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i);cell.SetCellValue(valueList[i]);//内容样式cell.CellStyle = contentStyle;}rowIndex++;//第二条测试结果row = sheet.CreateRow(rowIndex);row.HeightInPoints = 15;valueList.Clear();valueList.AddRange(new string[]{"","2", "带载检测","10.4/10.2", "", "10.1", "10.6", "合格"});for (int i = 0; i < valueList.Count; i++){cell = row.CreateCell(i);cell.SetCellValue(valueList[i]);//内容样式cell.CellStyle = contentStyle;}//性能测试 要跨行sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startIndex, rowIndex, 0, 0));sheet.GetRow(startIndex).GetCell(0).CellStyle = leftCellStyle;rowIndex++;row = sheet.CreateRow(rowIndex);row.HeightInPoints = 20;cell = row.CreateCell(0);cell.SetCellValue("测试总结果：");cell.CellStyle = leftCellStyle;for (int i = 1; i < valueList.Count; i++){cell = row.CreateCell(i);if( i == 3){cell.SetCellValue("不合格");}cell.CellStyle = leftCellStyle;}//单元格合并 合并列sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 2));sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 3, 7));sheet.GetRow(rowIndex).GetCell(0).CellStyle = leftCellStyle;sheet.GetRow(rowIndex).GetCell(1).CellStyle = leftCellStyle;using (FileStream file = new FileStream(@"测试结果.xls", FileMode.Create)){workBook.Write(file);file.Close();}}

小结

1 单元格合并 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 2));
2 设置行高 row.HeightInPoints = 20;
3 设置列宽 sheet.SetColumnWidth(0, 15 * 256);
4 样式 ICellStyle leftCellStyle = workBook.CreateCellStyle();
5 字体 IFont font = workBook.CreateFont();