提供一个使用Npoi生成excel文档的例子,应能帮助到各位使用Npoi的朋友,因为具有多种情形处理

news/2024/11/26 5:57:17/

提供一个使用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);}
}

祝您用餐愉快。


http://www.ppmy.cn/news/238959.html

相关文章

脉脉、兼职猫逐梦AIGC,在线招聘江湖酝酿新变?

配图来自Canva可画 求职难与招聘难同时出现&#xff0c;人力资源行业供需双方互相嫌弃的问题如何解决&#xff1f;人力资源平台给出了新答案——AIGC。 2023年是AIGC在人力资源市场集中爆发的一年。脉脉平台上线类ChatGPT产品“万能的脉友”&#xff1b;数字化灵活用工平台兼…

常用正则表达式整理

校验密码强度 长度为6-10&#xff0c;必须包含大小写字母和数字组合&#xff0c;不能使用特殊字符。 ^(?.*\\d)(?.*[a-z])(?.*[A-Z]).{6,10}$校验中文 字符串只能是中文字符。 ^[\\u4e00-\\u9fa5]{0,}$由数字和26个英文字母或下划线组成的字符串 ^\\w$校验E-Mail地址 同密…

关于Spring中自带的@Schedule实现自动任务

SpringBoot中自带了一个能够实现定时任务的注解Schedule 如果定时任务比较简单&#xff0c;则可以使用SpringBoot中自带的Schedule&#xff0c;但是如果任务调度很复杂的话&#xff0c;就建议使用Quartz组件了。 说一下这个Schedule注解怎么用&#xff1f; 基本使用 第一步&a…

当外网通过FTP方式访问NAS,文件名出现乱码后的解决方法

当外网通过FTP方式访问NAS&#xff0c;文件名出现乱码后的解决方法 现象&#xff1a;文件管理器打开NAS后&#xff0c;发现一些中文名的文件出现乱码。 解决方法&#xff1a; 登陆群晖DSM&#xff1b;打开控制面板&#xff1b;点击文件传输&#xff0c;找到FTP;在UTF-8编码栏…

华为交换机S3700清空配置方法

1、用户视图下输入&#xff1a;reset saved-configuration&#xff1b;输入&#xff1a;Y&#xff0c;确认清除 2、输入&#xff1a;reboot&#xff1b;重启系统&#xff08;第1次提示输入&#xff1a;N 不保存配置&#xff1b;第2次提示输入&#xff1a;Y 确认重启&#xff0…

华为交换机清空配置(S3700)

1、用户视图下输入&#xff1a;reset saved-configuration&#xff1b;输入&#xff1a;Y&#xff0c;确认清除。 2、输入&#xff1a;reboot&#xff1b;重启系统&#xff08;第1次提示输入&#xff1a;N 不保存配置&#xff1b;第2次提示输入&#xff1a;Y 确认重启&#xff…

互联网晚报 | 董明珠称格力员工一人一套房承诺不变;工信部:自动续费前5日应以显著方式提醒;诺基亚近60年来首次更换新Logo...

董明珠&#xff1a;格力员工一人一套房承诺不变 格力电器董事长兼总裁董明珠透露&#xff0c;格力此前作出的“集团员工一人分得一套房”的承诺不变。董明珠说&#xff1a;“我们之前第一批3700套本来已经分了&#xff0c;但是确实受到这几年特殊环境的影响。”针对近期网络上流…