From 1b3db8f5683373dd8e179b006e7314783dd1f9db Mon Sep 17 00:00:00 2001
From: cnf <3200815559@qq.com>
Date: 星期三, 10 九月 2025 14:42:12 +0800
Subject: [PATCH] 文件补充
---
WebApi/Gs.Toolbox/ExcelHelper.cs | 916 ++++++++++----------------------------------------------
1 files changed, 170 insertions(+), 746 deletions(-)
diff --git a/WebApi/Gs.Toolbox/ExcelHelper.cs b/WebApi/Gs.Toolbox/ExcelHelper.cs
index 7378881..818bf82 100644
--- a/WebApi/Gs.Toolbox/ExcelHelper.cs
+++ b/WebApi/Gs.Toolbox/ExcelHelper.cs
@@ -3,9 +3,11 @@
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
+using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
+
namespace Gs.Toolbox;
@@ -33,12 +35,12 @@
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936)
.GetBytes(item.ColumnName).Length;
for (var i = 0; i < dtSource.Rows.Count; i++)
- for (var j = 0; j < dtSource.Columns.Count; j++)
- {
- var intTemp = Encoding.GetEncoding(936)
- .GetBytes(dtSource.Rows[i][j].ToString()).Length;
- if (intTemp > arrColWidth[j]) arrColWidth[j] = intTemp;
- }
+ for (var j = 0; j < dtSource.Columns.Count; j++)
+ {
+ var intTemp = Encoding.GetEncoding(936)
+ .GetBytes(dtSource.Rows[i][j].ToString()).Length;
+ if (intTemp > arrColWidth[j]) arrColWidth[j] = intTemp;
+ }
var rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
@@ -182,12 +184,12 @@
.GetBytes(item.ColumnName).Length;
if (fixW <= 0)
for (var i = 0; i < dtSource.Rows.Count; i++)
- for (var j = 0; j < dtSource.Columns.Count; j++)
- {
- var intTemp = Encoding.GetEncoding(936)
- .GetBytes(dtSource.Rows[i][j].ToString()).Length;
- if (intTemp > arrColWidth[j]) arrColWidth[j] = intTemp;
- }
+ for (var j = 0; j < dtSource.Columns.Count; j++)
+ {
+ var intTemp = Encoding.GetEncoding(936)
+ .GetBytes(dtSource.Rows[i][j].ToString()).Length;
+ if (intTemp > arrColWidth[j]) arrColWidth[j] = intTemp;
+ }
var cusRow = sheet.CreateRow(0);
var _rr = 0;
@@ -224,8 +226,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++)
@@ -295,6 +297,7 @@
}
/// <summary>
+ ///
/// </summary>
/// <param name="workbook"></param>
/// <param name="fontSize">瀛椾綋澶у皬</param>
@@ -302,68 +305,64 @@
/// <param name="isCenter">鏄惁涓棿</param>
/// <param name="border">鏄惁杈规</param>
/// <returns></returns>
- private static ICellStyle getStyle(HSSFWorkbook workbook,
- double fontSize = 11, bool isBold = false, bool isCenter = false,
- bool border = true)
+ private static ICellStyle getStyle(HSSFWorkbook workbook, double fontSize = 11, bool isBold = false, bool isCenter = false, bool border = true)
{
- var font = workbook.CreateFont();
+ IFont font = workbook.CreateFont();
font.FontName = "瀹嬩綋";
font.FontHeightInPoints = fontSize;
- if (isBold)
+ if (isBold == true)
font.Boldweight = (short)FontBoldWeight.Bold;
// 鍒涘缓鍗曞厓鏍兼牱寮� - 甯﹁竟妗嗗拰鎸囧畾瀛椾綋
- var style = workbook.CreateCellStyle();
+ ICellStyle style = workbook.CreateCellStyle();
style.SetFont(font);
- if (border)
+ if (border == true)
{
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
}
-
- if (isCenter)
+ if (isCenter == true)
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
return style;
}
/// <summary>
- /// 璁剧疆鍗曞厓鏍肩殑鍊�
+ /// 璁剧疆鍗曞厓鏍肩殑鍊�
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIdx"></param>
/// <param name="colIdx"></param>
/// <param name="val"></param>
- private static void setCellVal(ISheet sheet, int rowIdx, int colIdx,
- string val)
+ private static void setCellVal(ISheet sheet, int rowIdx, int colIdx, string val)
{
- var row = sheet.GetRow(rowIdx) ?? sheet.CreateRow(rowIdx);
- var cell = row.GetCell(colIdx) ?? row.CreateCell(colIdx);
+ IRow row = sheet.GetRow(rowIdx) ?? sheet.CreateRow(rowIdx);
+ ICell cell = row.GetCell(colIdx) ?? row.CreateCell(colIdx);
cell.SetCellValue(val); // 鍙互璁剧疆瀛楃涓层�佹暟瀛楃瓑绫诲瀷鐨勬暟鎹�
- }
+ }
/// <summary>
- /// 鐢熸垚棣栨
+ /// 鐢熸垚棣栨
/// </summary>
/// <param name="dtSource"></param>
public static void ExportShouJian(DataSet dtSource, string strFileName)
{
//琛岄珮
- var rowHeight = 25;
+ int rowHeight = 25;
//鎬�7鍒�
- var maxCols = 7;
+ int maxCols = 7;
// 鍒涘缓宸ヤ綔绨�
- var workbook = new HSSFWorkbook();
- var sheet = workbook.CreateSheet("Sheet1");
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ ISheet sheet = workbook.CreateSheet("Sheet1");
// 鍒涘缓鍗曞厓鏍兼牱寮� - 甯﹁竟妗嗗拰鎸囧畾瀛椾綋
- var style = getStyle(workbook);
- var styleHeader = getStyle(workbook, 20, true, true);
- var style12 = getStyle(workbook, 11, false, true);
+ ICellStyle style = getStyle(workbook);
+ ICellStyle styleHeader = getStyle(workbook, 20, true, true);
+ ICellStyle style12 = getStyle(workbook, 11, false, true);
// 璁剧疆鍒楀锛�6鍒楋級
- sheet.SetColumnWidth(0, 4000); // 绗竴鍒楃◢瀹�
+ sheet.SetColumnWidth(0, 4000); // 绗竴鍒楃◢瀹�
sheet.SetColumnWidth(1, 3000);
sheet.SetColumnWidth(2, 3000);
sheet.SetColumnWidth(3, 3000);
@@ -371,25 +370,24 @@
sheet.SetColumnWidth(5, 3000);
sheet.SetColumnWidth(6, 3000);
// 鍒涘缓20琛�
- for (var rowIndex = 0; rowIndex <= 6; rowIndex++)
+ for (int rowIndex = 0; rowIndex <= 6; rowIndex++)
{
- var row = sheet.CreateRow(rowIndex);
+ IRow row = sheet.CreateRow(rowIndex);
// 璁剧疆琛岄珮锛堟墍鏈夎鐩稿悓楂樺害锛�
row.HeightInPoints = rowHeight;
// 鍒涘缓6涓崟鍏冩牸
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row.CreateCell(colIndex);
+ ICell cell = row.CreateCell(colIndex);
if (rowIndex == 0)
cell.CellStyle = styleHeader;
else
cell.CellStyle = style;
}
}
-
//杩欐槸琛ㄥご
- var tb0 = dtSource.Tables[0];
- var row0 = tb0.Rows[0];
+ DataTable tb0 = dtSource.Tables[0];
+ DataRow row0 = tb0.Rows[0];
// 1. 绗竴琛屽垪鍚堝苟
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));
// 2. 绗簩琛屽垪鍚堝苟
@@ -424,171 +422,44 @@
setCellVal(sheet, 5, 5, "鐢熶骇鏃ユ湡");
setCellVal(sheet, 5, 6, row0["scrq"].ToString());
string[] ary = { "椤圭洰", "鎶�鏈�/鍝佽川瑕佹眰", "娴嬭瘯鏁版嵁", "妫�楠岀粨鏋�", "A", "B", "C" };
- for (var i = 0; i < ary.Length; i++) setCellVal(sheet, 6, i, ary[i]);
+ for (int i = 0; i < ary.Length; i++)
+ {
+ setCellVal(sheet, 6, i, ary[i]);
+ }
//浠庣7琛屽紑濮嬶紝灏辨槸寰幆鏁版嵁搴�
- var _idx = 7;
- var tb1 = dtSource.Tables[1];
- var dd = "";
- var _blPscs = false;
- var _blGjyzx = false;
+ int _idx = 7;
+ DataTable tb1 = dtSource.Tables[1];
foreach (DataRow rrr in tb1.Rows)
{
- //澧炲姞鍝佹按娴嬭瘯鏍囬
- if (!_blPscs && rrr["椤圭洰"].ToString() == "鍝佹按娴嬭瘯")
- {
- var row700 = sheet.CreateRow(_idx);
- for (var i = 0; i < ary.Length; i++)
- {
- var 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 && rrr["椤圭洰"].ToString() == "鍏抽敭閮ㄤ欢涓�鑷存��")
- {
- var row700 = sheet.CreateRow(_idx);
- for (var i = 0; i < ary.Length; i++)
- {
- var 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;
- }
-
- var row7 = sheet.CreateRow(_idx);
+ IRow row7 = sheet.CreateRow(_idx);
row7.HeightInPoints = rowHeight;
- var _tmphb = rrr["椤圭洰鍚堝苟琛屾暟"].ToString();
- var _tmp = "";
- if (dd == rrr["椤圭洰"].ToString())
- _tmp = "";
- else
- _tmp = rrr["椤圭洰"].ToString();
- dd = rrr["椤圭洰"].ToString();
- for (var i = 0; i < ary.Length; i++)
+ for (int i = 0; i < ary.Length; i++)
{
- var cell7 = row7.CreateCell(i);
+ ICell cell7 = row7.CreateCell(i);
cell7.CellStyle = style;
- 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));
- }
+ cell7.SetCellValue(rrr[ary[i]].ToString());
- 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++;
}
-
- var row8 = sheet.CreateRow(_idx);
+ IRow row8 = sheet.CreateRow(_idx);
row8.HeightInPoints = rowHeight;
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row8.CreateCell(colIndex);
+ ICell cell = row8.CreateCell(colIndex);
cell.CellStyle = style12;
}
-
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
setCellVal(sheet, _idx, 0, "缁煎悎鍒ゅ畾");
setCellVal(sheet, _idx, 1, "鍚堟牸");
_idx++;
- var row9 = sheet.CreateRow(_idx);
+ IRow row9 = sheet.CreateRow(_idx);
row9.HeightInPoints = rowHeight;
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row9.CreateCell(colIndex);
+ ICell cell = row9.CreateCell(colIndex);
cell.CellStyle = style;
}
-
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 3));
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 4, 6));
setCellVal(sheet, _idx, 1, "宸ヨ壓 宸茬‘璁わ細鍚堟牸");
@@ -596,30 +467,48 @@
sheet.AddMergedRegion(new CellRangeAddress(_idx - 1, _idx, 0, 0));
_idx++;
- var row10 = sheet.CreateRow(_idx);
+ IRow row10 = sheet.CreateRow(_idx);
row10.HeightInPoints = rowHeight;
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row10.CreateCell(colIndex);
+ ICell cell = row10.CreateCell(colIndex);
cell.CellStyle = style12;
}
-
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
setCellVal(sheet, _idx, 0, "瀹℃牳鎰忚");
setCellVal(sheet, _idx, 1, "鍚堟牸");
_idx++;
- var row11 = sheet.CreateRow(_idx);
+ IRow row11 = sheet.CreateRow(_idx);
row11.HeightInPoints = rowHeight;
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row11.CreateCell(colIndex);
+ ICell cell = row11.CreateCell(colIndex);
cell.CellStyle = style12;
}
-
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())
{
@@ -637,26 +526,26 @@
}
/// <summary>
- /// 鐢熸垚iqc
+ /// 鐢熸垚iqc
/// </summary>
/// <param name="dtSource"></param>
public static void ExportIqc(DataSet dtSource, string strFileName)
{
//琛岄珮
- var rowHeight = 25;
+ int rowHeight = 25;
//鎬诲垪
- var maxCols = 8;
+ int maxCols = 8;
// 鍒涘缓宸ヤ綔绨�
- var workbook = new HSSFWorkbook();
- var sheet = workbook.CreateSheet("Sheet1");
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ ISheet sheet = workbook.CreateSheet("Sheet1");
// 鍒涘缓鍗曞厓鏍兼牱寮� - 甯﹁竟妗嗗拰鎸囧畾瀛椾綋
- var style = getStyle(workbook);
- var styleHeader = getStyle(workbook, 20, true, true);
- var style12 = getStyle(workbook, 11, false, true);
+ ICellStyle style = getStyle(workbook);
+ ICellStyle styleHeader = getStyle(workbook, 20, true, true);
+ ICellStyle style12 = getStyle(workbook, 11, false, true);
// 璁剧疆鍒楀锛�6鍒楋級
- sheet.SetColumnWidth(0, 3000); // 绗竴鍒楃◢瀹�
+ sheet.SetColumnWidth(0, 3000); // 绗竴鍒楃◢瀹�
sheet.SetColumnWidth(1, 3000);
sheet.SetColumnWidth(2, 3000);
sheet.SetColumnWidth(3, 3000);
@@ -665,15 +554,15 @@
sheet.SetColumnWidth(6, 3000);
sheet.SetColumnWidth(7, 3000);
// 鍒涘缓9琛�
- for (var rowIndex = 0; rowIndex <= 8; rowIndex++)
+ for (int rowIndex = 0; rowIndex <= 8; rowIndex++)
{
- var row = sheet.CreateRow(rowIndex);
+ IRow row = sheet.CreateRow(rowIndex);
// 璁剧疆琛岄珮锛堟墍鏈夎鐩稿悓楂樺害锛�
row.HeightInPoints = rowHeight;
// 鍒涘缓涓崟鍏冩牸
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row.CreateCell(colIndex);
+ ICell cell = row.CreateCell(colIndex);
if (rowIndex == 1)
cell.CellStyle = styleHeader;
else
@@ -682,10 +571,9 @@
cell.SetCellValue($"琛寋rowIndex + 1}鍒梴colIndex + 1}");
}
}
-
//杩欐槸琛ㄥご
- var tb0 = dtSource.Tables[0];
- var row0 = tb0.Rows[0];
+ DataTable tb0 = dtSource.Tables[0];
+ DataRow row0 = tb0.Rows[0];
//绗竴琛屽垪鍚堝苟
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 4, 7));
@@ -737,27 +625,26 @@
setCellVal(sheet, 6, 6, "涓嶅悎鏍兼暟");
setCellVal(sheet, 6, 7, row0["bhgs"].ToString());
setCellVal(sheet, 7, 0, "鍒ゅ畾Ac/Re");
- setCellVal(sheet, 7, 1, row0["cr"].ToString());
- setCellVal(sheet, 7, 2, row0["ma"].ToString());
- setCellVal(sheet, 7, 3, row0["mi"].ToString());
+ setCellVal(sheet, 7, 1, "0/1");
+ setCellVal(sheet, 7, 2, "0/1");
+ setCellVal(sheet, 7, 3, "0/1");
setCellVal(sheet, 8, 0, "椤圭洰");
setCellVal(sheet, 8, 1, "璐ㄩ噺瑕佹眰");
setCellVal(sheet, 8, 6, "妫�楠岃褰�");
setCellVal(sheet, 8, 7, "妫�楠岀粨鏋�");
////浠庣9琛屽紑濮嬶紝灏辨槸寰幆鏁版嵁搴�
- var _idx = 9;
- var tb1 = dtSource.Tables[1];
+ int _idx = 9;
+ DataTable tb1 = dtSource.Tables[1];
foreach (DataRow rrr in tb1.Rows)
{
- var row7 = sheet.CreateRow(_idx);
+ IRow row7 = sheet.CreateRow(_idx);
row7.HeightInPoints = rowHeight;
- for (var i = 0; i < maxCols; i++)
+ for (int i = 0; i < maxCols; i++)
{
- var cell7 = row7.CreateCell(i);
+ ICell cell7 = row7.CreateCell(i);
cell7.CellStyle = style;
// cell7.SetCellValue(rrr[ary[i]].ToString());
}
-
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 5));
setCellVal(sheet, _idx, 0, rrr["椤圭洰"].ToString());
setCellVal(sheet, _idx, 1, rrr["璐ㄩ噺瑕佹眰"].ToString());
@@ -765,33 +652,29 @@
setCellVal(sheet, _idx, 7, rrr["妫�楠岀粨鏋�"].ToString());
_idx++;
}
-
//澶囨敞
- var row8 = sheet.CreateRow(_idx);
+ IRow row8 = sheet.CreateRow(_idx);
row8.HeightInPoints = rowHeight;
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row8.CreateCell(colIndex);
+ ICell cell = row8.CreateCell(colIndex);
cell.CellStyle = style12;
}
-
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 7));
setCellVal(sheet, _idx, 0, "澶囨敞锛�");
_idx++;
//缁煎悎鍒ゅ畾
- for (var i = 0; i < 2; i++)
+ for (int i = 0; i < 2; i++)
{
- var row9 = sheet.CreateRow(_idx);
+ IRow row9 = sheet.CreateRow(_idx);
row9.HeightInPoints = rowHeight;
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row9.CreateCell(colIndex);
+ ICell cell = row9.CreateCell(colIndex);
cell.CellStyle = style;
}
-
_idx++;
}
-
sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 1, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 2, 1, 7));
sheet.AddMergedRegion(new CellRangeAddress(_idx - 1, _idx - 1, 1, 7));
@@ -799,19 +682,17 @@
setCellVal(sheet, _idx - 2, 1, row0["zhpd"].ToString());
setCellVal(sheet, _idx - 1, 1, row0["zhpdqz"].ToString());
//瀹℃牳鎰忚锛�
- for (var i = 0; i < 2; i++)
+ for (int i = 0; i < 2; i++)
{
- var row9 = sheet.CreateRow(_idx);
+ IRow row9 = sheet.CreateRow(_idx);
row9.HeightInPoints = rowHeight;
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row9.CreateCell(colIndex);
+ ICell cell = row9.CreateCell(colIndex);
cell.CellStyle = style;
}
-
_idx++;
}
-
sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 1, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(_idx - 2, _idx - 2, 1, 7));
sheet.AddMergedRegion(new CellRangeAddress(_idx - 1, _idx - 1, 1, 7));
@@ -819,31 +700,26 @@
setCellVal(sheet, _idx - 2, 1, row0["shyj"].ToString());
setCellVal(sheet, _idx - 1, 1, row0["shyjqz"].ToString());
string[] ary = { "閲囪喘閮ㄦ剰瑙�", "閿�鍞儴鎰忚", "鎶�鏈儴鎰忚", "鐢熶骇閮ㄦ剰瑙�", "鍝佽川閮ㄦ剰瑙�" };
- for (var i = 0; i < ary.Length; i++)
+ for (int i = 0; i < ary.Length; i++)
{
- var row9 = sheet.CreateRow(_idx);
+ IRow row9 = sheet.CreateRow(_idx);
row9.HeightInPoints = rowHeight;
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row9.CreateCell(colIndex);
+ ICell cell = row9.CreateCell(colIndex);
cell.CellStyle = style;
}
-
// sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 1));
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 2, 7));
_idx++;
}
-
setCellVal(sheet, _idx - ary.Length, 0, "涓嶅悎鏍艰瘎瀹★細");
- sheet.AddMergedRegion(new CellRangeAddress(_idx - ary.Length, _idx - 1,
- 0, 0));
- for (var i = 0; i < ary.Length; i++)
+ sheet.AddMergedRegion(new CellRangeAddress(_idx - ary.Length, _idx - 1, 0, 0));
+ for (int i = 0; i < ary.Length; i++)
{
setCellVal(sheet, _idx - i - 1, 1, ary[i]);
- setCellVal(sheet, _idx - i - 1, 2,
- "鈻� 鍚屾剰 鈻� 涓嶅悓鎰� 绛惧悕/鏃ユ湡锛�");
+ setCellVal(sheet, _idx - i - 1, 2, "鈻� 鍚屾剰 鈻� 涓嶅悓鎰� 绛惧悕/鏃ユ湡锛�");
}
-
//****鍚堝苟濂藉悗寮�濮嬭祴鍊糴nd**************
//淇濆瓨
using (var ms = new MemoryStream())
@@ -863,26 +739,28 @@
/// <summary>
- /// 鐢熸垚宸℃
+ /// 鐢熸垚宸℃
/// </summary>
/// <param name="dtSource"></param>
public static void ExportXunJian(DataSet dtSource, string strFileName)
{
- var tb0 = dtSource.Tables[0];
- var row0 = tb0.Rows[0];
- var tb1 = dtSource.Tables[1];
+ DataTable tb0 = dtSource.Tables[0];
+ DataRow row0 = tb0.Rows[0];
+ DataTable tb1 = dtSource.Tables[1];
//琛岄珮
- var rowHeight = 25;
+ int rowHeight = 25;
//鎬诲垪
- var maxCols = 10;
- var workbook = new HSSFWorkbook();
- var sheet = workbook.CreateSheet("Sheet1");
+ int maxCols = tb1.Columns.Count;
+ HSSFWorkbook workbook = new HSSFWorkbook();
+ ISheet sheet = workbook.CreateSheet("Sheet1");
+
// 鍒涘缓鍗曞厓鏍兼牱寮� - 甯﹁竟妗嗗拰鎸囧畾瀛椾綋
- var style = getStyle(workbook);
- var styleHeader = getStyle(workbook, 20, true, true);
- var style12 = getStyle(workbook, 11, false, true);
- // 璁剧疆鍒楀锛�10鍒楋級
- sheet.SetColumnWidth(0, 3000); // 绗竴鍒楃◢瀹�
+ ICellStyle style = getStyle(workbook);
+ ICellStyle styleHeader = getStyle(workbook, 20, true, true);
+ ICellStyle style12 = getStyle(workbook, 11, false, true);
+
+ // 璁剧疆鍒楀锛�6鍒楋級
+ sheet.SetColumnWidth(0, 3000); // 绗竴鍒楃◢瀹�
sheet.SetColumnWidth(1, 3000);
sheet.SetColumnWidth(2, 3000);
sheet.SetColumnWidth(3, 3000);
@@ -890,108 +768,61 @@
sheet.SetColumnWidth(5, 3000);
sheet.SetColumnWidth(6, 3000);
sheet.SetColumnWidth(7, 3000);
- sheet.SetColumnWidth(8, 3000);
- sheet.SetColumnWidth(9, 3000);
- // 鍒涘缓澶磋
- for (var rowIndex = 0; rowIndex <= 2; rowIndex++)
+ // 鍒涘缓琛�
+ for (int rowIndex = 0; rowIndex <= 1; rowIndex++)
{
- var row = sheet.CreateRow(rowIndex);
+ IRow row = sheet.CreateRow(rowIndex);
// 璁剧疆琛岄珮锛堟墍鏈夎鐩稿悓楂樺害锛�
row.HeightInPoints = rowHeight;
// 鍒涘缓涓崟鍏冩牸
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row.CreateCell(colIndex);
+ ICell cell = row.CreateCell(colIndex);
if (rowIndex == 0)
cell.CellStyle = styleHeader;
else
cell.CellStyle = style;
}
}
-
//绗竴琛屽垪鍚堝苟
- sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 9));
+ sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7));
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());
//寰幆鏁版嵁搴�
- var _idx = 3;
+ 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++;
foreach (DataRow rrr in tb1.Rows)
{
- var row7 = sheet.CreateRow(_idx);
+ IRow row7 = sheet.CreateRow(_idx);
row7.HeightInPoints = rowHeight;
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < tb1.Columns.Count; colIndex++)
{
- var cell7 = row7.CreateCell(colIndex);
+ ICell cell7 = row7.CreateCell(colIndex);
cell7.CellStyle = style;
- // 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;
- }
+ cell7.SetCellValue(rrr[tb1.Columns[colIndex]].ToString());
}
-
_idx++;
}
-
- ////澶囨敞
- var row8 = sheet.CreateRow(_idx);
+ //澶囨敞
+ IRow row8 = sheet.CreateRow(_idx);
row8.HeightInPoints = rowHeight;
- for (var colIndex = 0; colIndex < maxCols; colIndex++)
+ for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
- var cell = row8.CreateCell(colIndex);
+ ICell cell = row8.CreateCell(colIndex);
cell.CellStyle = style12;
}
-
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 4));
+ sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 2));
setCellVal(sheet, _idx, 0, "宸℃浜猴細");
- 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());
+ for (int colIndex = 2; colIndex < tb1.Columns.Count; colIndex++)
+ {
+ setCellVal(sheet, _idx, colIndex, "浣欏悏鏋�");
+ }
//淇濆瓨
using (var ms = new MemoryStream())
{
@@ -1008,413 +839,6 @@
}
}
- /// <summary>
- /// 鐢熸垚鎴愬搧妫�楠屾姤鍛婏紙鎸夊疄闄呮ā鏉跨粨鏋勶級
- /// </summary>
- /// <param name="dtSource"></param>
- /// <param name="strFileName"></param>
- public static void ExportChengPin(DataSet dtSource, string strFileName)
- {
- //琛岄珮
- var rowHeight = 25;
- //鎬诲垪鏁� - 鏍规嵁妯℃澘鍒嗘瀽锛岃嚦灏戦渶瑕�18鍒�
- var maxCols = 18;
- // 鍒涘缓宸ヤ綔绨�
- var workbook = new HSSFWorkbook();
- var sheet = workbook.CreateSheet("Sheet1");
-
- // 鍒涘缓鍗曞厓鏍兼牱寮�
- var style = getStyle(workbook);
- var styleHeader = getStyle(workbook, 16, true, true);
- var styleTitle = getStyle(workbook, 14, true, true);
- var styleSmall = getStyle(workbook, 10);
-
- // 璁剧疆鍒楀
- sheet.SetColumnWidth(0, 3500); // A鍒楋細椤圭洰鍚嶇О
- sheet.SetColumnWidth(1, 5000); // B鍒楋細鏍囧噯/瑙勮寖
- for (var i = 2; i < 18; i++) // C鍒癛鍒楋細16涓娴嬪�煎垪
- sheet.SetColumnWidth(i, 2500);
-
- //鑾峰彇琛ㄥご鏁版嵁
- var tb0 = dtSource.Tables[0];
- var row0 = tb0.Rows[0];
- var _idx = 0;
-
- // 绗�1琛岋細鎶ュ憡鏍囬
- var titleRow = sheet.CreateRow(_idx);
- titleRow.HeightInPoints = 30;
- for (var i = 0; i < maxCols; i++)
- {
- var cell = titleRow.CreateCell(i);
- cell.CellStyle = styleHeader;
- }
-
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, maxCols - 1));
- setCellVal(sheet, _idx, 0, "鎴愬搧妫�楠屾姤鍛�");
- _idx++;
-
- // 绗�2琛岋細鍩烘湰淇℃伅绗竴琛�
- var info1Row = sheet.CreateRow(_idx);
- info1Row.HeightInPoints = rowHeight;
- for (var i = 0; i < maxCols; i++)
- {
- var cell = info1Row.CreateCell(i);
- cell.CellStyle = style;
- }
-
- setCellVal(sheet, _idx, 0,
- "鐢熶骇杞﹂棿锛�" + (row0["workShop"]?.ToString() ?? ""));
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 5));
-
- setCellVal(sheet, _idx, 12,
- "瀹ゆ俯锛�" + (row0["temperature"]?.ToString() ?? ""));
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 12, 17));
- _idx++;
-
- // 绗�3琛岋細浜у搧淇℃伅
- var info2Row = sheet.CreateRow(_idx);
- info2Row.HeightInPoints = rowHeight;
- for (var i = 0; i < maxCols; i++)
- {
- var 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, "鐢靛帇");
- setCellVal(sheet, _idx, 13, row0["voltage"]?.ToString() ?? "");
-
- setCellVal(sheet, _idx, 14, "绾垮彿");
- setCellVal(sheet, _idx, 15, row0["lineNo"]?.ToString() ?? "");
- _idx++;
-
- // 绗�4琛岋細鍟嗘爣绛変俊鎭�
- var info3Row = sheet.CreateRow(_idx);
- info3Row.HeightInPoints = rowHeight;
- for (var i = 0; i < maxCols; i++)
- {
- var 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鏍囧噯
- var acreRow = sheet.CreateRow(_idx);
- acreRow.HeightInPoints = rowHeight;
- for (var i = 0; i < maxCols; i++)
- {
- var 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琛岋細鎶芥牱淇℃伅
- var sampleRow = sheet.CreateRow(_idx);
- sampleRow.HeightInPoints = rowHeight;
- for (var i = 0; i < maxCols; i++)
- {
- var 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++;
-
- // 绗�7琛岋細妫�楠岄」鐩〃澶�
- var headerRow = sheet.CreateRow(_idx);
- headerRow.HeightInPoints = rowHeight;
- for (var i = 0; i < maxCols; i++)
- {
- var cell = headerRow.CreateCell(i);
- cell.CellStyle = styleTitle;
- }
-
- setCellVal(sheet, _idx, 0, "椤圭洰");
- setCellVal(sheet, _idx, 1, "鏍囧噯");
- setCellVal(sheet, _idx, 2, "1");
- setCellVal(sheet, _idx, 3, "2");
- // 鏍规嵁闇�瑕佺户缁坊鍔犳洿澶氬垪鏍囬锛岃繖閲岀畝鍖栦负涓昏鍒�
- for (var i = 4; i < 18; i++)
- setCellVal(sheet, _idx, i, (i - 1).ToString());
- _idx++;
-
- // 妫�楠岄」鐩暟鎹�
- if (dtSource.Tables.Count > 1)
- {
- var tb1 = dtSource.Tables[1];
- foreach (DataRow rrr in tb1.Rows)
- {
- var dataRow = sheet.CreateRow(_idx);
- dataRow.HeightInPoints = rowHeight;
-
- for (var i = 0; i < maxCols; i++)
- {
- var cell = dataRow.CreateCell(i);
- cell.CellStyle = style;
- }
-
- setCellVal(sheet, _idx, 0, rrr["RPB003"]?.ToString() ?? "");
- setCellVal(sheet, _idx, 1, rrr["RPB004"]?.ToString() ?? "");
-
- // 濉厖16涓祴璇曞��
- for (var i = 0; i < 16; i++)
- {
- var 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++;
- }
- }
-
- // 涓嶅悎鏍煎唴瀹硅褰�
- var defectHeaderRow = sheet.CreateRow(_idx);
- defectHeaderRow.HeightInPoints = rowHeight;
- for (var i = 0; i < maxCols; i++)
- {
- var 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)
- {
- var tb2 = dtSource.Tables[2];
- foreach (DataRow rrr in tb2.Rows)
- {
- if (rrr["Description"]?.ToString() == "涓嶈壇鏁伴噺")
- break;
- var dataRow = sheet.CreateRow(_idx);
- dataRow.HeightInPoints = rowHeight;
-
- for (var i = 0; i < maxCols; i++)
- {
- var 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++;
- }
-
- var dr = tb2.Rows[tb2.Rows.Count - 1];
- // 澶囨敞琛�
- var remarkRow = sheet.CreateRow(_idx);
- remarkRow.HeightInPoints = rowHeight;
- for (var i = 0; i < maxCols; i++)
- {
- var 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++;
-
- // 澶х殑澶囨敞鍐呭鍖哄煙
- var remarkContentRow = sheet.CreateRow(_idx);
- remarkContentRow.HeightInPoints = 60;
- for (var i = 0; i < maxCols; i++)
- {
- var 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 (var judgeRowIndex = 0; judgeRowIndex < 3; judgeRowIndex++)
- {
- var judgmentRow = sheet.CreateRow(_idx);
- judgmentRow.HeightInPoints = rowHeight;
- for (var i = 0; i < maxCols; i++)
- {
- var cell = judgmentRow.CreateCell(i);
- cell.CellStyle = styleTitle;
- }
-
- if (judgeRowIndex == 0)
- {
- // 绗竴琛岋細妫�楠屽垽瀹�
- setCellVal(sheet, _idx, 0, "妫�楠屽垽瀹�");
- sheet.AddMergedRegion(
- new CellRangeAddress(_idx, _idx + 2, 0, 0)); // 妫�楠屽垽瀹氳法3琛�
-
- if (row0["CheckResult"]?.ToString() == "鍚堟牸")
- setCellVal(sheet, _idx, 1, "鈭� 鍚堟牸 鈻� 涓嶅悎鏍�");
- else if (row0["CheckResult"]?.ToString() == "涓嶅悎鏍�")
- setCellVal(sheet, _idx, 1, "鈻� 鍚堟牸 鈭� 涓嶅悎鏍�");
- else
- setCellVal(sheet, _idx, 1, "鈻� 鍚堟牸 鈻� 涓嶅悎鏍�");
- sheet.AddMergedRegion(
- new CellRangeAddress(_idx, _idx + 1, 1, 5)); // 鍚堟牸/涓嶅悎鏍艰法2琛�
-
- setCellVal(sheet, _idx, 6, "瀹℃牳");
- sheet.AddMergedRegion(
- new CellRangeAddress(_idx, _idx + 2, 6, 6)); // 瀹℃牳璺�3琛�
-
- if (row0["ProcessResults"]?.ToString() == "鏃�")
- setCellVal(sheet, _idx, 7, "鈭� 鏃� 鈻� 杩斿伐");
- else if (row0["ProcessResults"]?.ToString() == "杩斿伐")
- setCellVal(sheet, _idx, 7, "鈻� 鏃� 鈭� 杩斿伐");
- else
- setCellVal(sheet, _idx, 7, "鈻� 鏃� 鈻� 杩斿伐");
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 11));
-
- setCellVal(sheet, _idx, 12, "瀹℃壒");
- sheet.AddMergedRegion(
- new CellRangeAddress(_idx, _idx + 2, 12, 12)); // 瀹℃壒璺�3琛�
-
- if (row0["ProcessResults"]?.ToString() == "鏃�")
- setCellVal(sheet, _idx, 13, "鈭� 鏃� 鈻� 杩斿伐");
- else if (row0["ProcessResults"]?.ToString() == "杩斿伐")
- setCellVal(sheet, _idx, 13, "鈻� 鏃� 鈭� 杩斿伐");
- else
- setCellVal(sheet, _idx, 13, "鈻� 鏃� 鈻� 杩斿伐");
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 13, 17));
- }
- else if (judgeRowIndex == 1)
- {
- if (row0["ProcessResults"]?.ToString() == "璁╂鎺ユ敹")
- setCellVal(sheet, _idx, 7, "鈭� 璁╂鎺ユ敹 鈻� 鐗归噰");
- else if (row0["ProcessResults"]?.ToString() == "鐗归噰")
- setCellVal(sheet, _idx, 7, "鈻� 璁╂鎺ユ敹 鈭� 鐗归噰");
- else
- setCellVal(sheet, _idx, 7, "鈻� 璁╂鎺ユ敹 鈻� 鐗归噰");
- // 绗簩琛岋細璁╂鎺ユ敹閫夐」
-
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 11));
-
- if (row0["ProcessResults"]?.ToString() == "璁╂鎺ユ敹")
- setCellVal(sheet, _idx, 13, "鈭� 璁╂鎺ユ敹 鈻� 鐗归噰");
- else if (row0["ProcessResults"]?.ToString() == "鐗归噰")
- setCellVal(sheet, _idx, 13, "鈻� 璁╂鎺ユ敹 鈭� 鐗归噰");
- else
- setCellVal(sheet, _idx, 13, "鈻� 璁╂鎺ユ敹 鈻� 鐗归噰");
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 13, 17));
- }
- else
- {
- // 绛惧悕琛�
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 2));
- setCellVal(sheet, _idx, 1, "绛惧悕/鏃ユ湡锛�");
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 3, 5));
- setCellVal(sheet, _idx, 3,
- $"{row0["JY_USER"]?.ToString() ?? ""}/{row0["JY_DATE"]?.ToString() ?? ""}");
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 7, 8));
- setCellVal(sheet, _idx, 7, "绛惧悕/鏃ユ湡锛�");
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 9, 11));
- setCellVal(sheet, _idx, 9,
- $"{row0["CHECK_USER"]?.ToString() ?? ""}/{row0["CHECK_DATE"]?.ToString() ?? ""}");
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 13, 14));
- setCellVal(sheet, _idx, 13, "绛惧悕/鏃ユ湡锛�");
- sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 15, 17));
- setCellVal(sheet, _idx, 15,
- $"{row0["SP_USER"]?.ToString() ?? ""}/{row0["SP_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
@@ -1504,4 +928,4 @@
return "=" + cell.CellFormula;
}
}
-}
\ No newline at end of file
+}
--
Gitblit v1.9.3