提供一个使用Npoi生成excel文档的例子,应能帮助到各位使用Npoi的朋友,因为具有多种情形处理
照例,文章开始之前,先看下最终效果:
实现的需求点如下:
1.第一行大标题加了背景色,且这个背景色,不在常用的颜色范围中,需要自定义。同时标题的字体大小要进行变化。
2.第二行的表格信息是单独的,每一项信息对应一个单元格给它填写值。如线体,然后跟着一个单元格给它写值。
3.第三行,要实现单元格合并。且根据单元格的文字多少,自适应文档的单元格宽度范围。
4.第四行,从第4行开始,就是填充表格数据了。要注意的是跟第3行一样,要合并单元格。
5.要冻结“点检状态”的单元格位置。
6.第1行的高度要最高,第2行的高度要次之,第3行的高度再次之,第4行用excel的默认高度。
7.单元格实现黑色边框。
由于代码注释已经很详细了,所以,我就直接贴上完整代码,就不啰唆了,我知道,读者其实也不想看我放屁,你们只想要源码,而且还不想放在下载中(因为那个要积分要钱)。
那么好的,来了(NPOI用的版本是2.6.0):
using System;
using System.Collections.Generic;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Util;using static EquipmentInspectionExcelGenerator;
using SixLabors.ImageSharp;/// <summary>
/// 用于设备点检数据的Excel生成
/// </summary>
public class EquipmentInspectionExcelGenerator
{/// <inheritdoc cref="EquipmentInspectionExcelGenerator"/>public EquipmentInspectionExcelGenerator() { }/// <summary>/// 点检信息/// </summary>public class InspectionInformation{/// <summary>/// 线体/// </summary>public string LineBody { get; set; }/// <summary>/// 工序名称/// </summary>public string ProcessName { get; set; }/// <summary>/// 设备编号/// </summary>public string EquipmentNumber { get; set; }/// <summary>/// 开机人员/// </summary>public string Operator { get; set; }/// <summary>/// 日期/// </summary>public string Date { get; set; }/// <summary>/// 班次/// </summary>public string Shift { get; set; }/// <summary>/// 点检项目数/// </summary>public string InspectionItemCount { get; set; }/// <summary>/// NG项目数/// </summary>public string NGItemCount { get; set; }}/// <summary>/// 点检项目/// </summary>public class InspectionData{/// <summary>/// 功能/// </summary>public string Functionality { get; set; }/// <summary>/// 生产时状态要求/// </summary>public string ProductionStatusRequirements { get; set; }/// <summary>/// 不符合要求设备反应计划/// </summary>public string NoncomplianceReactionPlan { get; set; }/// <summary>/// 点检状态/// </summary>public string InspectionStatus { get; set; }/// <summary>/// 判定结果/// </summary>public string JudgmentResult { get; set; }/// <summary>/// 点检时间/// </summary>public string InspectionTime { get; set; }/// <summary>/// 反应计划/// </summary>public string ReactionPlan { get; set; }/// <summary>/// 当班点检次数/// </summary>public string InspectionCount { get; set; }}/// <summary>/// 生成excel/// </summary>/// <param name="filePath">存储路径</param>/// <param name="information"><inheritdoc cref="InspectionInformation" path="/summary"/></param>/// <param name="datas"><inheritdoc cref="InspectionData" path="/summary"/></param>public void GenerateExcel(string filePath, InspectionInformation information, List<InspectionData> datas){if (string.IsNullOrEmpty(filePath)){throw new ArgumentException("filePath is required", nameof(filePath));}if (information == null){throw new ArgumentNullException("information is required", nameof(information));}if (datas == null){throw new ArgumentNullException("datas is required", nameof(datas));}// 创建Excel工作簿IWorkbook workbook = new XSSFWorkbook();ISheet sheet = workbook.CreateSheet("设备功能开关点检表格");// 设置第1行的表头,合并16列并居中显示IRow row1 = sheet.CreateRow(0);row1.Height = Convert.ToInt16(row1.Height * 2.5);IFont headerFont = workbook.CreateFont();headerFont.FontHeightInPoints = 18; // 设置字体大小headerFont.IsBold = true; // 设置字体加粗ICell cell1 = row1.CreateCell(0);var headerCellStyle = CreateHeaderCellStyle(workbook);headerCellStyle.SetFont(headerFont);cell1.CellStyle = headerCellStyle;cell1.SetCellValue("设备功能开关点检表格");sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 15));// 设置第2行的列标题IRow row2 = sheet.CreateRow(1);row2.Height = Convert.ToInt16(row2.Height * 2);IFont columnHeaderFont = workbook.CreateFont();columnHeaderFont.IsBold = true; // 设置字体加粗string[] columnHeaders = new string[]{"线体", information.LineBody, "工序名称", information.ProcessName, "设备编号", information.EquipmentNumber, "开机人员", information.Operator, "日期", information.Date, "班次", information.Shift, "点检项目数", information.InspectionItemCount, "NG项目数", information.NGItemCount};for (int i = 0; i < columnHeaders.Length; i++){ICell cell = row2.CreateCell(i);var columnHeaderCellStyle = CreateColumnHeaderCellStyle(workbook);if (i % 2 == 0){columnHeaderCellStyle.SetFont(columnHeaderFont);}cell.CellStyle = columnHeaderCellStyle;cell.SetCellValue(columnHeaders[i]);sheet.SetColumnWidth(i, 200 * 15); // 设置列宽自适应内容}// 设置第3行的列标题IRow row3 = sheet.CreateRow(2);row3.Height = Convert.ToInt16(row3.Height * 1.6);string[] subColumnHeaders = new string[]{"序号", "功能", "", "生产时状态要求", "", "不符合要求设备反应计划", "", "点检状态", "", "判定结果", "", "点检时间", "","反应计划", "", "当班点检次数"};for (int i = 0; i < subColumnHeaders.Length; i++){ICell cell = row3.CreateCell(i);cell.CellStyle = CreateColumnHeaderCellStyle(workbook);cell.SetCellValue(subColumnHeaders[i]);}// 循环设置数据for (var i = 0; i < datas.Count; i++){var data = datas[i];IRow dataRow = sheet.CreateRow(i + 3);dataRow.Height = Convert.ToInt16(dataRow.Height * 1.2);string[] values = new string[]{(i+1).ToString(),data.Functionality,string.Empty,data.ProductionStatusRequirements,string.Empty,data.NoncomplianceReactionPlan,string.Empty,data.InspectionStatus,string.Empty,data.JudgmentResult,string.Empty,data.InspectionTime,string.Empty,data.ReactionPlan,string.Empty,data.InspectionCount,};for (int j = 0; j < values.Length; j++){ICell cell = dataRow.CreateCell(j);cell.CellStyle = CreateCellStyle(workbook);cell.SetCellValue(values[j]);}}// 合并单元格int rowCount = sheet.LastRowNum + 1;for (int i = 2; i < rowCount; i++){sheet.AddMergedRegion(new CellRangeAddress(i, i, 1, 2));sheet.AddMergedRegion(new CellRangeAddress(i, i, 3, 4));sheet.AddMergedRegion(new CellRangeAddress(i, i, 5, 6));sheet.AddMergedRegion(new CellRangeAddress(i, i, 7, 8));sheet.AddMergedRegion(new CellRangeAddress(i, i, 9, 10));sheet.AddMergedRegion(new CellRangeAddress(i, i, 11, 12));sheet.AddMergedRegion(new CellRangeAddress(i, i, 13, 14));}// 冻结“点检状态”列的位置sheet.CreateFreezePane(7, 2);// 保存Excel文件using (FileStream fileStream = new FileStream(filePath, FileMode.Create)){workbook.Write(fileStream, true);}}/// <summary>/// 创建表头样式/// </summary>/// <param name="workbook"></param>/// <returns></returns>private ICellStyle CreateHeaderCellStyle(IWorkbook workbook){ICellStyle style = workbook.CreateCellStyle();//style.Alignment = HorizontalAlignment.Center;style.VerticalAlignment = VerticalAlignment.Center;style.FillForegroundColor = 0;// IndexedColors.Grey25Percent.Index;//Grey25Percent的颜色还是太深了。用自定义的颜色让他更淡些。style.FillPattern = FillPattern.SolidForeground;((XSSFColor)style.FillForegroundColorColor).SetRgb(new byte[] { 242, 242, 242 });//自定义一个淡色style.BorderTop = BorderStyle.Thin;style.BorderBottom = BorderStyle.Thin;style.BorderLeft = BorderStyle.Thin;style.BorderRight = BorderStyle.Thin;style.TopBorderColor = IndexedColors.Black.Index;style.BottomBorderColor = IndexedColors.Black.Index;style.LeftBorderColor = IndexedColors.Black.Index;style.RightBorderColor = IndexedColors.Black.Index;return style;}/// <summary>/// 创建列头样式/// </summary>/// <param name="workbook"></param>/// <returns></returns>private ICellStyle CreateColumnHeaderCellStyle(IWorkbook workbook){ICellStyle style = CreateCellStyle(workbook);style.FillForegroundColor = IndexedColors.Grey40Percent.Index;return style;}/// <summary>/// 创建单元格样式/// </summary>/// <param name="workbook"></param>/// <returns></returns>private ICellStyle CreateCellStyle(IWorkbook workbook){ICellStyle style = workbook.CreateCellStyle();style.Alignment = HorizontalAlignment.Center;style.VerticalAlignment = VerticalAlignment.Center;style.BorderTop = BorderStyle.Thin;style.BorderBottom = BorderStyle.Thin;style.BorderLeft = BorderStyle.Thin;style.BorderRight = BorderStyle.Thin;style.TopBorderColor = IndexedColors.Black.Index;style.BottomBorderColor = IndexedColors.Black.Index;style.LeftBorderColor = IndexedColors.Black.Index;style.RightBorderColor = IndexedColors.Black.Index;return style;}
}
调用方式如下:
public class Example
{/// <summary>/// 请参照这个方法的调用代码,来进行调用/// </summary>public void 演示调用(){string filePath = @"C:\Users\Administrator\Desktop\excel.xlsx";InspectionInformation information = new InspectionInformation();information.LineBody = "JITA-003";information.ProcessName = "侧边涂胶";information.EquipmentNumber = "JIT02TM";information.Operator = "Marc";information.Date = "2023-5-29";information.Shift = "day shift";information.InspectionItemCount = "4";information.NGItemCount = "28";List<InspectionData> datas = new List<InspectionData>();datas.Add(new InspectionData{Functionality = "前段联机",ProductionStatusRequirements = "启用",NoncomplianceReactionPlan = "停机",InspectionStatus = "屏蔽",JudgmentResult = "NG",InspectionTime = "2023/4/21/8:34",ReactionPlan = "停机",InspectionCount = "2"});datas.Add(new InspectionData{Functionality = "后段联机",ProductionStatusRequirements = "启用",NoncomplianceReactionPlan = "停机",InspectionStatus = "屏蔽",JudgmentResult = "NG",InspectionTime = "2023/4/21/8:34",ReactionPlan = "停机",InspectionCount = "2"});datas.Add(new InspectionData{Functionality = "上位机联机",ProductionStatusRequirements = "启用",NoncomplianceReactionPlan = "停机",InspectionStatus = "屏蔽",JudgmentResult = "NG",InspectionTime = "2023/4/21/8:34",ReactionPlan = "停机",InspectionCount = "2"});datas.Add(new InspectionData{Functionality = "点胶门禁",ProductionStatusRequirements = "启用",NoncomplianceReactionPlan = "停机",InspectionStatus = "屏蔽",JudgmentResult = "NG",InspectionTime = "2023/4/21/8:34",ReactionPlan = "停机",InspectionCount = "2"});EquipmentInspectionExcelGenerator generator = new EquipmentInspectionExcelGenerator();generator.GenerateExcel(filePath, information, datas);}
}
祝您用餐愉快。