From c01d18bc7e003a025e06fc1a2d1ac62fbd036269 Mon Sep 17 00:00:00 2001
From: tjx <t2856754968@163.com>
Date: 星期六, 11 十月 2025 20:06:40 +0800
Subject: [PATCH] 1
---
WebApi/Gs.Toolbox/ExcelHelper.cs | 593 ++++++++++++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 542 insertions(+), 51 deletions(-)
diff --git a/WebApi/Gs.Toolbox/ExcelHelper.cs b/WebApi/Gs.Toolbox/ExcelHelper.cs
index 818bf82..beee68d 100644
--- a/WebApi/Gs.Toolbox/ExcelHelper.cs
+++ b/WebApi/Gs.Toolbox/ExcelHelper.cs
@@ -1,6 +1,8 @@
锘縰sing System.Collections;
using System.Data;
using System.Text;
+using System.Timers;
+using Masuit.Tools;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Formula.Functions;
@@ -226,8 +228,8 @@
//sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //棣栬绛涢��
// if (fixW <= 0)
- sheet.CreateFreezePane(dtSource.Columns.Count,
- 1 + aryHeader.Count); //棣栬鍐荤粨
+ //sheet.CreateFreezePane(dtSource.Columns.Count,
+ // 1 + aryHeader.Count); //棣栬鍐荤粨
//濉厖鍐呭
var dataRow = sheet.CreateRow(roct + 1);
for (var i = 0; i < dtSource.Rows.Count; i++)
@@ -429,16 +431,131 @@
//浠庣7琛屽紑濮嬶紝灏辨槸寰幆鏁版嵁搴�
int _idx = 7;
DataTable tb1 = dtSource.Tables[1];
+ string dd = "";
+ bool _blPscs = false;
+ bool _blGjyzx = false;
foreach (DataRow rrr in tb1.Rows)
{
+ //澧炲姞鍝佹按娴嬭瘯鏍囬
+ if (_blPscs == false && rrr["椤圭洰"].ToString() == "鍝佹按娴嬭瘯")
+ {
+ IRow row700 = sheet.CreateRow(_idx);
+ for (int i = 0; i < ary.Length; i++)
+ {
+ ICell cell700 = row700.CreateCell(i);
+ cell700.CellStyle = style;
+ if (i == 0)
+ cell700.SetCellValue(rrr["椤圭洰"].ToString());
+ if (i == 1)
+ cell700.SetCellValue("娓╁害");
+ if (i == 3)
+ cell700.SetCellValue("娴侀噺");
+ if (i == 5)
+ cell700.SetCellValue("鏄惁鏈夊紓鍛�");
+ if (i == 6)
+ cell700.SetCellValue("妫�楠岀粨鏋�");
+ }
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 2));
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 3, 4));
+ _idx++;
+ _blPscs = true;
+ }
+ //澧炲姞鏍囬
+ if (_blGjyzx == false && rrr["椤圭洰"].ToString() == "鍏抽敭閮ㄤ欢涓�鑷存��")
+ {
+ IRow row700 = sheet.CreateRow(_idx);
+ for (int i = 0; i < ary.Length; i++)
+ {
+ ICell cell700 = row700.CreateCell(i);
+ cell700.CellStyle = style;
+ if (i == 0)
+ cell700.SetCellValue(rrr["椤圭洰"].ToString());
+ if (i == 1)
+ cell700.SetCellValue("鍏抽敭鍚嶇О");
+ if (i == 2)
+ cell700.SetCellValue("鐢熶骇鏃ユ湡");
+ if (i == 3)
+ cell700.SetCellValue("浣跨敤鏁伴噺");
+ if (i == 4)
+ cell700.SetCellValue("璁よ瘉淇℃伅");
+ if (i == 5)
+ cell700.SetCellValue("鏄惁绗﹀悎CCC涓�鑷存��");
+ if (i == 6)
+ cell700.SetCellValue("鎿嶄綔");
+ }
+ _idx++;
+ _blGjyzx = true;
+ }
+
IRow row7 = sheet.CreateRow(_idx);
row7.HeightInPoints = rowHeight;
+ string _tmphb = rrr["椤圭洰鍚堝苟琛屾暟"].ToString();
+ string _tmp = "";
+ if (dd == rrr["椤圭洰"].ToString())
+ _tmp = "";
+ else
+ _tmp = rrr["椤圭洰"].ToString();
+ dd = rrr["椤圭洰"].ToString();
for (int i = 0; i < ary.Length; i++)
{
ICell cell7 = row7.CreateCell(i);
cell7.CellStyle = style;
- cell7.SetCellValue(rrr[ary[i]].ToString());
-
+ if (i == 0)
+ {
+ cell7.SetCellValue(_tmp);
+ if (!string.IsNullOrEmpty(_tmp) && int.Parse(_tmphb) > 1)
+ {
+ if (rrr["椤圭洰"].ToString() == "鍝佹按娴嬭瘯" || rrr["椤圭洰"].ToString() == "鍏抽敭閮ㄤ欢涓�鑷存��" )
+ sheet.AddMergedRegion(new CellRangeAddress(_idx-1, _idx + int.Parse(_tmphb)-1, 0, 0));
+ else
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx + int.Parse(_tmphb) - 1, 0, 0));
+ }
+ }
+ else
+ {
+ switch (rrr["椤圭洰"].ToString())
+ {
+ case "鍒剁儹鎬ц兘":
+ cell7.SetCellValue(rrr["鍒剁儹鎬ц兘table"].ToString());
+ row7.HeightInPoints = rowHeight * 5;
+ if (i == ary.Length - 1)
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 6));
+ break;
+ case "鍝佹按娴嬭瘯":
+ //搴︽暟
+ if (i == 1)
+ cell7.SetCellValue(rrr["鎶�鏈�/鍝佽川瑕佹眰"].ToString());
+ if (i == 3)
+ cell7.SetCellValue(rrr["鍝佹按娴嬭瘯娴侀噺"].ToString());
+ if (i == 5)
+ cell7.SetCellValue(rrr["鏄惁鏈夊紓鍛�"].ToString());
+ if (i == 6)
+ cell7.SetCellValue(rrr["妫�楠岀粨鏋�"].ToString());
+ if (i == ary.Length - 1)
+ {
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 2));
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 3, 4));
+ }
+ break;
+ case "鍏抽敭閮ㄤ欢涓�鑷存��":
+ if (i == 1)
+ cell7.SetCellValue(rrr["鎶�鏈�/鍝佽川瑕佹眰"].ToString());
+ if (i == 2)
+ cell7.SetCellValue(rrr["鐢熶骇鏃ユ湡"].ToString());
+ if (i == 3)
+ cell7.SetCellValue(rrr["浣跨敤鏁伴噺"].ToString());
+ if (i == 4)
+ cell7.SetCellValue(rrr["璁よ瘉淇℃伅"].ToString());
+ if (i == 5)
+ cell7.SetCellValue(rrr["鏄惁绗﹀悎CCC涓�鑷存��"].ToString());
+ //if (i == 6)
+ // cell7.SetCellValue(rrr["妫�楠岀粨鏋�"].ToString());
+ break;
+ default:
+ cell7.SetCellValue(rrr[ary[i]].ToString());
+ break;
+ }
+ }
}
_idx++;
}
@@ -489,26 +606,6 @@
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 6));
setCellVal(sheet, _idx, 0, "棣栦欢濉啓,鏉ユ枡妫�楠�");
//****鍚堝苟濂藉悗寮�濮嬭祴鍊糴nd**************
-
- // 5. 绗�8鍒�10琛屼腑鐨勭涓�鍒楀簲琚悎骞�
- //sheet.AddMergedRegion(new CellRangeAddress(7, 9, 0, 0));
-
- //// 6. 绗�17锛�18琛屼腑鐨勭涓�鍒楀悎骞�
- //sheet.AddMergedRegion(new CellRangeAddress(16, 17, 0, 0));
-
- //// 7. 绗�17琛屼腑鐨�2锛�3锛�4锛�5鍒楀悎骞�
- //sheet.AddMergedRegion(new CellRangeAddress(16, 16, 1, 4));
-
- //// 8. 绗�18琛屼腑鐨勶紝2锛�3鍒楀悎骞讹紝4锛�5锛�6鍒楀悎骞�
- //sheet.AddMergedRegion(new CellRangeAddress(17, 17, 1, 2));
- //sheet.AddMergedRegion(new CellRangeAddress(17, 17, 3, 5));
-
- //// 9. 绗�19琛屼腑鐨勶紝鍒�2锛�3锛�4鍚堝苟
- //sheet.AddMergedRegion(new CellRangeAddress(18, 18, 1, 3));
-
- //// 10. 绗�20琛屼腑鐨勫垪鍏ㄩ儴鍚堝苟
- //sheet.AddMergedRegion(new CellRangeAddress(19, 19, 0, 5));
-
//淇濆瓨
using (var ms = new MemoryStream())
{
@@ -625,9 +722,9 @@
setCellVal(sheet, 6, 6, "涓嶅悎鏍兼暟");
setCellVal(sheet, 6, 7, row0["bhgs"].ToString());
setCellVal(sheet, 7, 0, "鍒ゅ畾Ac/Re");
- setCellVal(sheet, 7, 1, "0/1");
- setCellVal(sheet, 7, 2, "0/1");
- setCellVal(sheet, 7, 3, "0/1");
+ setCellVal(sheet, 7, 1, row0["cr"].ToString());
+ setCellVal(sheet, 7, 2, row0["ma"].ToString());
+ setCellVal(sheet, 7, 3, row0["mi"].ToString());
setCellVal(sheet, 8, 0, "椤圭洰");
setCellVal(sheet, 8, 1, "璐ㄩ噺瑕佹眰");
setCellVal(sheet, 8, 6, "妫�楠岃褰�");
@@ -750,16 +847,14 @@
//琛岄珮
int rowHeight = 25;
//鎬诲垪
- int maxCols = tb1.Columns.Count;
+ int maxCols = 10;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
-
// 鍒涘缓鍗曞厓鏍兼牱寮� - 甯﹁竟妗嗗拰鎸囧畾瀛椾綋
ICellStyle style = getStyle(workbook);
ICellStyle styleHeader = getStyle(workbook, 20, true, true);
ICellStyle style12 = getStyle(workbook, 11, false, true);
-
- // 璁剧疆鍒楀锛�6鍒楋級
+ // 璁剧疆鍒楀锛�10鍒楋級
sheet.SetColumnWidth(0, 3000); // 绗竴鍒楃◢瀹�
sheet.SetColumnWidth(1, 3000);
sheet.SetColumnWidth(2, 3000);
@@ -768,8 +863,10 @@
sheet.SetColumnWidth(5, 3000);
sheet.SetColumnWidth(6, 3000);
sheet.SetColumnWidth(7, 3000);
- // 鍒涘缓琛�
- for (int rowIndex = 0; rowIndex <= 1; rowIndex++)
+ sheet.SetColumnWidth(8, 3000);
+ sheet.SetColumnWidth(9, 3000);
+ // 鍒涘缓澶磋
+ for (int rowIndex = 0; rowIndex <= 2; rowIndex++)
{
IRow row = sheet.CreateRow(rowIndex);
// 璁剧疆琛岄珮锛堟墍鏈夎鐩稿悓楂樺害锛�
@@ -785,31 +882,70 @@
}
}
//绗竴琛屽垪鍚堝苟
- sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7));
+ sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 9));
setCellVal(sheet, 0, 0, row0["title"].ToString());
+ //绗簩琛屽垪鍚堝苟
+ sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 1));
+ sheet.AddMergedRegion(new CellRangeAddress(1, 1, 6, 9));
+ setCellVal(sheet, 1, 0, "宸ュ崟鍙�");
+ setCellVal(sheet, 1, 2, row0["daaNo"].ToString());
+ setCellVal(sheet, 1, 3, "浜у搧鍚嶇О");
+ setCellVal(sheet, 1, 4, row0["itemName"].ToString());
+ setCellVal(sheet, 1, 5, "瑙勬牸鍨嬪彿");
+ setCellVal(sheet, 1, 6, row0["itemModel"].ToString());
+ //绗笁琛屽垪鍚堝苟
+ sheet.AddMergedRegion(new CellRangeAddress(2, 2,2, 4));
+ setCellVal(sheet,2, 0, "搴忓彿");
+ setCellVal(sheet, 2, 1, "妫�楠岄」鐩�");
+ setCellVal(sheet, 2, 2, "妫�楠屾爣鍑�");
+ setCellVal(sheet, 2, 5, row0["tm1"].ToString());
+ setCellVal(sheet, 2, 6, row0["tm2"].ToString());
+ setCellVal(sheet, 2, 7, row0["tm3"].ToString());
+ setCellVal(sheet, 2, 8, row0["tm4"].ToString());
+ setCellVal(sheet, 2, 9, row0["tm5"].ToString());
//寰幆鏁版嵁搴�
- int _idx = 1;
- IRow row6 = sheet.CreateRow(_idx);
- for (int colIndex = 0; colIndex < maxCols; colIndex++)
- {
- ICell cell6 = row6.CreateCell(colIndex);
- cell6.CellStyle = style;
- cell6.SetCellValue(tb1.Columns[colIndex].Caption.ToString().Trim());
- }
- _idx++;
+ int _idx = 3;
foreach (DataRow rrr in tb1.Rows)
{
IRow row7 = sheet.CreateRow(_idx);
row7.HeightInPoints = rowHeight;
- for (int colIndex = 0; colIndex < tb1.Columns.Count; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
ICell cell7 = row7.CreateCell(colIndex);
cell7.CellStyle = style;
- cell7.SetCellValue(rrr[tb1.Columns[colIndex]].ToString());
+ // cell7.SetCellValue("1");
+ switch (colIndex)
+ {
+ case 0:
+ cell7.SetCellValue(rrr["搴忓彿"].ToString());
+ break;
+ case 1:
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 2, 4));
+ cell7.SetCellValue(rrr["妫�楠岄」鐩�"].ToString());
+ break;
+ case 2:
+ cell7.SetCellValue(rrr["妫�楠屾爣鍑�"].ToString());
+ break;
+ case 5:
+ cell7.SetCellValue(rrr["tm1"].ToString());
+ break;
+ case 6:
+ cell7.SetCellValue(rrr["tm2"].ToString());
+ break;
+ case 7:
+ cell7.SetCellValue(rrr["tm3"].ToString());
+ break;
+ case 8:
+ cell7.SetCellValue(rrr["tm4"].ToString());
+ break;
+ case 9:
+ cell7.SetCellValue(rrr["tm5"].ToString());
+ break;
+ }
}
_idx++;
}
- //澶囨敞
+ ////澶囨敞
IRow row8 = sheet.CreateRow(_idx);
row8.HeightInPoints = rowHeight;
for (int colIndex = 0; colIndex < maxCols; colIndex++)
@@ -817,12 +953,13 @@
ICell cell = row8.CreateCell(colIndex);
cell.CellStyle = style12;
}
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 2));
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 4));
setCellVal(sheet, _idx, 0, "宸℃浜猴細");
- for (int colIndex = 2; colIndex < tb1.Columns.Count; colIndex++)
- {
- setCellVal(sheet, _idx, colIndex, "浣欏悏鏋�");
- }
+ setCellVal(sheet, _idx, 5, row0["xjr1"].ToString());
+ setCellVal(sheet, _idx, 6, row0["xjr2"].ToString());
+ setCellVal(sheet, _idx, 7, row0["xjr3"].ToString());
+ setCellVal(sheet, _idx, 8, row0["xjr4"].ToString());
+ setCellVal(sheet, _idx, 9, row0["xjr5"].ToString());
//淇濆瓨
using (var ms = new MemoryStream())
{
@@ -839,6 +976,360 @@
}
}
+ /// <summary>
+ /// 鐢熸垚鎴愬搧妫�楠屾姤鍛婏紙鎸夊疄闄呮ā鏉跨粨鏋勶級
+ /// </summary>
+ /// <param name="dtSource"></param>
+ /// <param name="strFileName"></param>
+ public static void ExportChengPin(DataSet dtSource, string strFileName)
+ {
+ //琛岄珮
+ int rowHeight = 25;
+ //鎬诲垪鏁� - 鏍规嵁妯℃澘鍒嗘瀽锛岃嚦灏戦渶瑕�18鍒�
+ int maxCols = 18;
+ // 鍒涘缓宸ヤ綔绨�
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ ISheet sheet = workbook.CreateSheet("Sheet1");
+
+ // 鍒涘缓鍗曞厓鏍兼牱寮�
+ ICellStyle style = getStyle(workbook);
+ ICellStyle styleHeader = getStyle(workbook, 16, true, true);
+ ICellStyle styleTitle = getStyle(workbook, 14, true, true);
+ ICellStyle styleSmall = getStyle(workbook, 10, false, false);
+
+ // 璁剧疆鍒楀
+ sheet.SetColumnWidth(0, 3500); // A鍒楋細椤圭洰鍚嶇О
+ sheet.SetColumnWidth(1, 5000); // B鍒楋細鏍囧噯/瑙勮寖
+ for (int i = 2; i < 18; i++) // C鍒癛鍒楋細16涓娴嬪�煎垪
+ {
+ sheet.SetColumnWidth(i, 2500);
+ }
+
+ //鑾峰彇琛ㄥご鏁版嵁
+ DataTable tb0 = dtSource.Tables[0];
+ DataRow row0 = tb0.Rows[0];
+ int _idx = 0;
+
+ // 绗�1琛岋細鎶ュ憡鏍囬
+ IRow titleRow = sheet.CreateRow(_idx);
+ titleRow.HeightInPoints = 30;
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = titleRow.CreateCell(i);
+ cell.CellStyle = styleHeader;
+ }
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, maxCols - 1));
+ setCellVal(sheet, _idx, 0, "鎴愬搧妫�楠屾姤鍛�");
+ _idx++;
+
+ // 绗�2琛岋細鍩烘湰淇℃伅绗竴琛�
+ IRow info1Row = sheet.CreateRow(_idx);
+ info1Row.HeightInPoints = rowHeight;
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = info1Row.CreateCell(i);
+ cell.CellStyle = style;
+ }
+ setCellVal(sheet, _idx, 0, "鐢熶骇杞﹂棿锛�" + (row0["workShop"]?.ToString() ?? ""));
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 5));
+ _idx++;
+
+ // 绗�3琛岋細浜у搧淇℃伅
+ IRow info2Row = sheet.CreateRow(_idx);
+ info2Row.HeightInPoints = rowHeight;
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = info2Row.CreateCell(i);
+ cell.CellStyle = style;
+ }
+ setCellVal(sheet, _idx, 0, "浜у搧鍚嶇О");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 4));
+ setCellVal(sheet, _idx, 1, row0["itemName"]?.ToString() ?? "");
+
+ setCellVal(sheet, _idx, 5, "鍨嬪彿瑙勬牸");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 6, 11));
+ setCellVal(sheet, _idx, 6, row0["itemModel"]?.ToString() ?? "");
+
+ setCellVal(sheet, _idx, 12, "绾垮彿");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 13, 15));
+ setCellVal(sheet, _idx, 13, row0["lineNo"]?.ToString() ?? "");
+ _idx++;
+
+ // 绗�4琛岋細鍟嗘爣绛変俊鎭�
+ IRow info3Row = sheet.CreateRow(_idx);
+ info3Row.HeightInPoints = rowHeight;
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = info3Row.CreateCell(i);
+ cell.CellStyle = style;
+ }
+ setCellVal(sheet, _idx, 0, "鍟嗘爣");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 4));
+ setCellVal(sheet, _idx, 1, row0["brand"]?.ToString() ?? "");
+
+ setCellVal(sheet, _idx, 5, "鐢熶骇鏃ユ湡");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 6, 11));
+ setCellVal(sheet, _idx, 6, row0["productionDate"]?.ToString() ?? "");
+
+ setCellVal(sheet, _idx, 12, "鐝");
+ setCellVal(sheet, _idx, 13, row0["classes"]?.ToString() ?? "");
+
+ setCellVal(sheet, _idx, 14, "鎵归噺");
+ setCellVal(sheet, _idx, 15, row0["batch"]?.ToString() ?? "");
+ _idx++;
+
+ // 绗�5琛岋細AC/Re鏍囧噯
+ IRow acreRow = sheet.CreateRow(_idx);
+ acreRow.HeightInPoints = rowHeight;
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = acreRow.CreateCell(i);
+ cell.CellStyle = style;
+ }
+ setCellVal(sheet, _idx, 0, "Ac/Re(A绫伙級");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
+ setCellVal(sheet, _idx, 1, row0["acRe_A"]?.ToString() ?? "");
+
+ setCellVal(sheet, _idx, 6, "Ac/Re(B绫伙級");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 11));
+ setCellVal(sheet, _idx, 7, row0["acRe_B"]?.ToString() ?? "");
+
+ setCellVal(sheet, _idx, 12, "Ac/Re(C绫伙級");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 13, 17));
+ setCellVal(sheet, _idx, 13, row0["acRe_C"]?.ToString() ?? "");
+ _idx++;
+
+ // 绗�6琛岋細鎶芥牱淇℃伅
+ IRow sampleRow = sheet.CreateRow(_idx);
+ sampleRow.HeightInPoints = rowHeight;
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = sampleRow.CreateCell(i);
+ cell.CellStyle = style;
+ }
+ setCellVal(sheet, _idx, 0, "鎶芥牱");
+ setCellVal(sheet, _idx, 1, "鍖�閫�");
+ setCellVal(sheet, _idx, 2, row0["SampleMethod"]?.ToString() == "鍖�閫�" ? "鈭�" : "");
+ setCellVal(sheet, _idx, 3, "闅忔満");
+ setCellVal(sheet, _idx, 4, row0["SampleMethod"]?.ToString() == "闅忔満" ? "鈭�" : "");
+
+ setCellVal(sheet, _idx, 5, "鏍锋湰鏁�");
+ setCellVal(sheet, _idx, 6, "鍖�閫熸娊鏍锋暟");
+ setCellVal(sheet, _idx, 7, row0["sampleSize1"]?.ToString() ?? "");
+ setCellVal(sheet, _idx, 8, "闅忔満鎶芥牱鏁�");
+ setCellVal(sheet, _idx, 9, row0["sampleSize2"]?.ToString() ?? "");
+ setCellVal(sheet, _idx, 10, "鏍锋湰");
+ setCellVal(sheet, _idx, 11, row0["sampleSize3"]?.ToString() ?? "");
+ _idx++;
+
+ int num;
+ if(row0["SampleMethod"]?.ToString() == "鍖�閫�")
+ {
+ if (int.TryParse(row0["sampleSize1"]?.ToString(), out int size))
+ {
+ num = size;
+ }
+ else
+ {
+ num = 0;
+ }
+ }
+ else
+ {
+ if (int.TryParse(row0["sampleSize2"]?.ToString(), out int size))
+ {
+ num = size;
+ }
+ else
+ {
+ num = 0;
+ }
+ }
+ if (num > 16)
+ num = 16;
+
+ // 绗�7琛岋細妫�楠岄」鐩〃澶�
+ IRow headerRow = sheet.CreateRow(_idx);
+ headerRow.HeightInPoints = rowHeight;
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = headerRow.CreateCell(i);
+ cell.CellStyle = styleTitle;
+ }
+ setCellVal(sheet, _idx, 0, "椤圭洰");
+ setCellVal(sheet, _idx, 1, "鏍囧噯");
+ // 鏍规嵁闇�瑕佺户缁坊鍔犳洿澶氬垪鏍囬锛岃繖閲岀畝鍖栦负涓昏鍒�
+ for (int i = 2; i < num+2; i++)
+ {
+ setCellVal(sheet, _idx, i, (i-1).ToString());
+ }
+ _idx++;
+
+ // 妫�楠岄」鐩暟鎹�
+ if (dtSource.Tables.Count > 1)
+ {
+ DataTable tb1 = dtSource.Tables[1];
+ foreach (DataRow rrr in tb1.Rows)
+ {
+ IRow dataRow = sheet.CreateRow(_idx);
+ dataRow.HeightInPoints = rowHeight;
+
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = dataRow.CreateCell(i);
+ cell.CellStyle = style;
+ }
+
+ setCellVal(sheet, _idx, 0, rrr["RPB003"]?.ToString() ?? "");
+ setCellVal(sheet, _idx, 1, rrr["RPB004"]?.ToString() ?? "");
+
+ // 濉厖16涓祴璇曞��
+ for (int i = 0; i < num; i++)
+ {
+ string colName = $"RPB{i + 5:D3}"; // RPB005, RPB006, ..., RPB020
+ if (rrr.Table.Columns.Contains(colName))
+ {
+ setCellVal(sheet, _idx, i + 2, rrr[colName]?.ToString() ?? "");
+ }
+ else
+ {
+ setCellVal(sheet, _idx, i + 2, "");
+ }
+ }
+
+ _idx++;
+ }
+ }
+
+ // 涓嶅悎鏍煎唴瀹硅褰�
+ IRow defectHeaderRow = sheet.CreateRow(_idx);
+ defectHeaderRow.HeightInPoints = rowHeight;
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = defectHeaderRow.CreateCell(i);
+ cell.CellStyle = styleTitle;
+ }
+ setCellVal(sheet, _idx, 0, "搴忓彿");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 10)); // 涓嶅悎鏍煎唴瀹瑰崰澶氬垪
+ setCellVal(sheet, _idx, 1, "涓嶅悎鏍煎唴瀹�");
+ setCellVal(sheet, _idx, 11, "A绫�");
+ setCellVal(sheet, _idx, 12, "B绫�");
+ setCellVal(sheet, _idx, 13, "C绫�");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 14, 17)); // 娉ㄦ剰鐐瑰崰鍓╀綑鍒�
+ setCellVal(sheet, _idx, 14, "娉ㄦ剰鐐�");
+ _idx++;
+
+ if (dtSource.Tables.Count > 2)
+ {
+ DataTable tb2 = dtSource.Tables[2];
+ foreach (DataRow rrr in tb2.Rows)
+ {
+ if (rrr["Description"]?.ToString() == "涓嶈壇鏁伴噺")
+ break;
+ IRow dataRow = sheet.CreateRow(_idx);
+ dataRow.HeightInPoints = rowHeight;
+
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = dataRow.CreateCell(i);
+ cell.CellStyle = style;
+ }
+
+ setCellVal(sheet, _idx, 0, rrr["Seq"]?.ToString() ?? "");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 10)); // 涓嶅悎鏍煎唴瀹瑰崰澶氬垪
+ setCellVal(sheet, _idx, 1, rrr["Description"]?.ToString() ?? "");
+ setCellVal(sheet, _idx, 11, rrr["AClass"]?.ToString() ?? "");
+ setCellVal(sheet, _idx, 12, rrr["BClass"]?.ToString() ?? "");
+ setCellVal(sheet, _idx, 13, rrr["CClass"]?.ToString() ?? "");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 14, 17)); // 娉ㄦ剰鐐瑰崰鍓╀綑鍒�
+ setCellVal(sheet, _idx, 14, rrr["Point"]?.ToString() ?? "");
+ _idx++;
+ }
+
+ DataRow dr = tb2.Rows[tb2.Rows.Count - 1];
+ // 澶囨敞琛�
+ IRow remarkRow = sheet.CreateRow(_idx);
+ remarkRow.HeightInPoints = rowHeight;
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = remarkRow.CreateCell(i);
+ cell.CellStyle = style;
+ }
+ setCellVal(sheet, _idx, 0, "澶囨敞锛�");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 8));
+ setCellVal(sheet, _idx, 9, "涓嶈壇鏁伴噺锛�");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 9, 10));
+ setCellVal(sheet, _idx, 11, dr["AClass"]?.ToString() ?? "");
+ setCellVal(sheet, _idx, 12, dr["BClass"]?.ToString() ?? "");
+ setCellVal(sheet, _idx, 13, dr["CClass"]?.ToString() ?? "");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 14, 17)); // 娉ㄦ剰鐐瑰崰鍓╀綑鍒�
+ setCellVal(sheet, _idx, 14, dr["Point"]?.ToString() ?? "");
+ _idx++;
+
+ // 澶х殑澶囨敞鍐呭鍖哄煙
+ IRow remarkContentRow = sheet.CreateRow(_idx);
+ remarkContentRow.HeightInPoints = 60;
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = remarkContentRow.CreateCell(i);
+ cell.CellStyle = style;
+ }
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 17));
+ setCellVal(sheet, _idx, 0, dr["Remark"]?.ToString() ?? "");
+ _idx++;
+ }
+
+ // 妫�楠屽垽瀹氬尯鍩燂紙3琛屽竷灞�锛�
+ for (int judgeRowIndex = 0; judgeRowIndex < 2; judgeRowIndex++)
+ {
+ IRow judgmentRow = sheet.CreateRow(_idx);
+ judgmentRow.HeightInPoints = rowHeight;
+ for (int i = 0; i < maxCols; i++)
+ {
+ ICell cell = judgmentRow.CreateCell(i);
+ cell.CellStyle = styleTitle;
+ }
+
+ if (judgeRowIndex == 0)
+ {
+ // 绗竴琛岋細妫�楠屽垽瀹�
+ setCellVal(sheet, _idx, 0, "妫�楠屽垽瀹�");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx + 1, 0, 0)); // 淇敼涓鸿法2琛岃�屼笉鏄�3琛�
+ setCellVal(sheet, _idx, 1, row0["CheckResult"]?.ToString() ?? "");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5)); // 鍙悎骞跺綋鍓嶈
+
+ setCellVal(sheet, _idx, 6, "妫�楠岀鍚�");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 10)); // 鍙悎骞跺綋鍓嶈
+ setCellVal(sheet, _idx, 7, row0["JY_NAME"]?.ToString() ?? "");
+ setCellVal(sheet, _idx, 11, "瀹℃牳绛惧悕");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 12, 16)); // 鍙悎骞跺綋鍓嶈
+ setCellVal(sheet, _idx, 12, row0["CHECK_USER"]?.ToString() ?? "");
+ }
+ else if (judgeRowIndex == 1)
+ {
+ setCellVal(sheet, _idx, 6, "妫�楠屾棩鏈�");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 10)); // 鍙悎骞跺綋鍓嶈
+ setCellVal(sheet, _idx, 7, row0["JY_DATE"]?.ToString() ?? "");
+ setCellVal(sheet, _idx, 11, "瀹℃牳鏃ユ湡");
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 12, 16)); // 鍙悎骞跺綋鍓嶈
+ setCellVal(sheet, _idx, 12, row0["CHECK_DATE"]?.ToString() ?? "");
+ }
+ _idx++;
+ }
+ //淇濆瓨
+ using (var ms = new MemoryStream())
+ {
+ using (var fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
+ {
+ workbook.Write(ms);
+ ms.Flush();
+ ms.Position = 0;
+ var data = ms.ToArray();
+ fs.Write(data, 0, data.Length);
+ fs.Flush();
+ }
+ }
+ }
/// <summary>
/// Excel瀵煎叆鎴怐atable
--
Gitblit v1.9.3