C# EPPlus按照Excel模板导出Excel文件

甲方给的Excel模板如下：

我们导出的Excel内容如下：

代码中有详细的注释，如下：

/// <summary>
/// 导出按钮点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, EventArgs e)
{
	ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 声明EPPlus许可
	SaveFileDialog saveFileDialog = new SaveFileDialog();
	saveFileDialog.Filter = "*.xlsx|*.xlsx";
	saveFileDialog.Title = "导出文件";
	saveFileDialog.InitialDirectory = "D:\\";
	saveFileDialog.FileName = $"ECU检测记录-{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
	DialogResult dialogResult = saveFileDialog.ShowDialog();
	if (dialogResult == DialogResult.OK && !saveFileDialog.FileName.Equals(""))
	{
		var fileInfo = new FileInfo(saveFileDialog.FileName);
		using (var pck = new ExcelPackage(fileInfo)) // 打开Excel文件
		{
			string sheetName = "sheet1";
			ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName)
            // 设置表头区域单元格边框、字体加粗以及文字居中显示
			using (var range = ws.Cells["A1:J2"])  // Address "A1:J2"
			{
				range.Style.Font.Bold = true;
				range.Style.Fill.PatternType = ExcelFillStyle.Solid;
				//range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
				//range.Style.Font.Color.SetColor(Color.White);
				range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
				range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
				range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
				range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
				range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
				range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
			}
			// 添加表头
			ws.Cells["A1:A2"].Merge = true; // 合并A1:A2的单元格
			ws.Cells["A1"].Value = "序列号";
			ws.Cells["B1:B2"].Merge = true; // 合并B1:B2的单元格
			ws.Cells["B1"].Value = "检测次数";
			ws.Cells["C1:C2"].Merge = true;
			ws.Cells["C1"].Value = "车型库";
			ws.Cells["D1:D2"].Merge = true;
			ws.Cells["D1"].Value = "检测时间";
			ws.Cells["E1:E2"].Merge = true;
			ws.Cells["E1"].Value = "检测人员";
			ws.Cells["F1:J1"].Merge = true;
			ws.Cells["F1"].Value = "检测结果";
			ws.Cells["F2"].Value = "CAN1";
			ws.Cells["G2"].Value = "CAN2";
			ws.Cells["H2"].Value = "PP1S";
			ws.Cells["I2"].Value = "RS422";
			ws.Cells["J2"].Value = "总评";
			// 从List集合中加载数据
			ws.Cells["A3"].LoadFromCollection(ecu13TestRecordSummList.Select(x => new
			{
				序列号 = x.number,
				检测次数 = x.testtimes,
				车型库 = x.cartypelib,
				检测时间 = x.testendtime,
				检测人员 = x.testuser,
				CAN1 = x.can1,
				CAN2 = x.can2,
				PP1S = x.pp1s,
				RS422 = x.rs422,
				总评 = (x.evl == "T" || x.evl == "合格") ? "合格" : "不合格",
			}).OrderBy(x=>x.检测时间).ToList(), false);
			// 设置数据区域单元格边框
			ws.Cells[$"A3:J{ws.Dimension.End.Row}"].Style.Fill.PatternType = ExcelFillStyle.Solid; // 格式化单元格边框
			ws.Cells[$"A3:J{ws.Dimension.End.Row}"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
			ws.Cells[$"A3:J{ws.Dimension.End.Row}"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
			ws.Cells[$"A3:J{ws.Dimension.End.Row}"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
			ws.Cells[$"A3:J{ws.Dimension.End.Row}"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
			ws.Cells[$"A3:J{ws.Dimension.End.Row}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
			ws.Cells[$"A3:J{ws.Dimension.End.Row}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
			// 格式化工作区J列总评的单元格背景颜色
			for (int i = 3; i <= ws.Dimension.End.Row; i++)
			{
				if (ws.Cells[$"J{i}"].Value.ToString() == "合格")
				{
					ws.Cells[$"J{i}"].Style.Fill.BackgroundColor.SetColor(Color.Green); // 设置单元格背景颜色
				}
				else
				{
					ws.Cells[$"J{i}"].Style.Fill.BackgroundColor.SetColor(Color.Red);
				}
			}
			ws.Cells.AutoFitColumns(); // 自适应列宽
			pck.Save(); // 保存Excel文件
		}
		DialogResult openResult = MessageBoxEx.Show($"文件{fileInfo.Name}已保存，是否打开？", "询问", MessageBoxButtonsEx.YesNo, MessageBoxIconEx.Question);
		if (openResult == DialogResult.Yes)
		{
			System.Diagnostics.Process.Start("explorer.exe", fileInfo.FullName);
		}
	}
}