From b98df034707a61fd2857dbccd5a232605076ca59 Mon Sep 17 00:00:00 2001
From: lg <123456>
Date: 星期四, 20 十一月 2025 10:26:08 +0800
Subject: [PATCH] 标准版初始化
---
WebApi/Gs.Toolbox/ExcelHelper.cs | 910 ++++++++++++++++++++++++++++++++++++++++++++++++--------
1 files changed, 773 insertions(+), 137 deletions(-)
diff --git a/WebApi/Gs.Toolbox/ExcelHelper.cs b/WebApi/Gs.Toolbox/ExcelHelper.cs
index 818bf82..47b087c 100644
--- a/WebApi/Gs.Toolbox/ExcelHelper.cs
+++ b/WebApi/Gs.Toolbox/ExcelHelper.cs
@@ -1,12 +1,14 @@
-锘縰sing System.Collections;
-using System.Data;
-using System.Text;
+锘縰sing Newtonsoft.Json;
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;
+using System.Collections;
+using System.Data;
+using System.Security.Cryptography.Xml;
+using System.Text;
+using System.Timers;
namespace Gs.Toolbox;
@@ -176,13 +178,13 @@
{
var roct = aryHeader.Count;
IWorkbook workbook = new HSSFWorkbook();
- var sheet = workbook.CreateSheet();
//鍙栧緱鍒楀
var arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936)
.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++)
{
@@ -190,59 +192,80 @@
.GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j]) arrColWidth[j] = intTemp;
}
-
- var cusRow = sheet.CreateRow(0);
- var _rr = 0;
- foreach (string _hh in aryHeader)
- {
- cusRow = sheet.CreateRow(_rr);
- cusRow.CreateCell(0).SetCellValue(_hh);
- //cellRangAddress鍙傛暟璇存槑锛氳捣濮嬭锛岀粨鏉熻锛岃捣濮嬪垪锛岀粨鏉熷垪
- sheet.AddMergedRegion(new CellRangeAddress(_rr, _rr, 0,
- dtSource.Columns.Count - 1));
- // sheet.AddMergedRegion(new Region(0, 0, _rr, dtSource.Columns.Count - 1));
- _rr++;
}
- //濉厖琛ㄥご
- var headerRow = sheet.CreateRow(roct);
- var headStyle = workbook.CreateCellStyle();
- headStyle.Alignment = HorizontalAlignment.Center;
- headStyle.FillPattern = FillPattern.SolidForeground;
- headStyle.FillForegroundColor = HSSFColor.LightBlue.Index;
- var font = workbook.CreateFont();
- font.Color = HSSFColor.White.Index;
- font.FontHeightInPoints = 12;
- font.Boldweight = 700;
- headStyle.SetFont(font);
- foreach (DataColumn column in dtSource.Columns)
+ const int maxRowsPerXlsSheet = 65535; // HSSF 鐨勫崟琛ㄨ鏁颁笂闄愶紙绱㈠紩浠�0锛�
+ int headerRowsCount = roct + 1; // aryHeader 琛屾暟 + 鍒楀ご琛�
+ int rowsPerSheet = maxRowsPerXlsSheet - headerRowsCount;
+ if (rowsPerSheet <= 0) rowsPerSheet = 1;
+
+ int totalRows = dtSource.Rows.Count;
+ int sheetIndex = 0;
+ ISheet sheet = null;
+ ICellStyle headStyle = null;
+
+ // 鍒嗙墖鍐欏叆锛屾瘡鐗囧啓鍏� header + 鏁版嵁
+ for (int startRow = 0; startRow < totalRows; startRow += rowsPerSheet)
{
- headerRow.CreateCell(column.Ordinal)
- .SetCellValue(column.ColumnName);
- headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
- sheet.SetColumnWidth(column.Ordinal,
- (arrColWidth[column.Ordinal] + 6) * 256);
+ sheet = workbook.CreateSheet("Sheet" + (sheetIndex + 1));
+
+ // 鍒涘缓骞跺簲鐢ㄥ垪瀹斤紙姣忎釜 sheet 閮介渶瑕侊級
+ for (int c = 0; c < dtSource.Columns.Count; c++)
+ {
+ sheet.SetColumnWidth(c, (arrColWidth[c] + 6) * 256);
+ }
+
+ // 鍐欏叆 aryHeader锛堝琛屽悎骞舵樉绀猴級
+ var cusRow = sheet.CreateRow(0);
+ var _rr = 0;
+ foreach (string _hh in aryHeader)
+ {
+ cusRow = sheet.CreateRow(_rr);
+ cusRow.CreateCell(0).SetCellValue(_hh);
+ sheet.AddMergedRegion(new CellRangeAddress(_rr, _rr, 0,
+ dtSource.Columns.Count - 1));
+ _rr++;
+ }
+
+ // 濉厖琛ㄥご
+ var headerRow = sheet.CreateRow(roct);
+ headStyle = workbook.CreateCellStyle();
+ headStyle.Alignment = HorizontalAlignment.Center;
+ headStyle.FillPattern = FillPattern.SolidForeground;
+ headStyle.FillForegroundColor = HSSFColor.LightBlue.Index;
+ var font = workbook.CreateFont();
+ font.Color = HSSFColor.White.Index;
+ font.FontHeightInPoints = 12;
+ font.Boldweight = 700;
+ headStyle.SetFont(font);
+ for (int colIdx = 0; colIdx < dtSource.Columns.Count; colIdx++)
+ {
+ headerRow.CreateCell(colIdx).SetCellValue(dtSource.Columns[colIdx].ColumnName);
+ headerRow.GetCell(colIdx).CellStyle = headStyle;
+ }
+
+ // 鍐欏叆鏁版嵁娈�
+ int endRow = Math.Min(startRow + rowsPerSheet, totalRows);
+ for (int i = startRow; i < endRow; i++)
+ {
+ var dataRow = sheet.CreateRow((i - startRow) + roct + 1);
+ for (var j = 0; j < dtSource.Columns.Count; j++)
+ {
+ dataRow.CreateCell(j)
+ .SetCellValue(dtSource.Rows[i][j].ToString());
+ }
+ }
+
+ sheetIndex++;
}
- //sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //棣栬绛涢��
- // if (fixW <= 0)
- sheet.CreateFreezePane(dtSource.Columns.Count,
- 1 + aryHeader.Count); //棣栬鍐荤粨
- //濉厖鍐呭
- var dataRow = sheet.CreateRow(roct + 1);
- for (var i = 0; i < dtSource.Rows.Count; i++)
+ // 濉厖 footer锛堝彧鍐欏湪鏈�鍚庝竴涓� sheet 涓婏級
+ if (aryFotter != null && aryFotter.Count > 0 && sheet != null)
{
- dataRow = sheet.CreateRow(i + roct + 1);
- for (var j = 0; j < dtSource.Columns.Count; j++)
- dataRow.CreateCell(j)
- .SetCellValue(dtSource.Rows[i][j].ToString());
- }
-
- //濉厖footer
- if (aryFotter != null && aryFotter.Count > 0)
- {
- var _rot = roct + dtSource.Rows.Count + 2;
- var ftRow = sheet.CreateRow(_rot);
+ var lastSheet = sheet;
+ var _rot = roct + (totalRows - ((sheetIndex - 1) * rowsPerSheet)) + 1;
+ if (_rot <= roct) _rot = roct + 1;
+ var ftRow = lastSheet.CreateRow(_rot);
var _ftidx = 0;
var _ftw = dtSource.Columns.Count / 2;
foreach (string _hh in aryFotter)
@@ -258,16 +281,14 @@
ftStyle.BorderBottom = BorderStyle.Thin;
else
ftStyle.BorderBottom = BorderStyle.None;
- ftRow = sheet.CreateRow(_rot);
+
+ ftRow = lastSheet.CreateRow(_rot);
ftRow.CreateCell(0).SetCellValue(_hh);
ftRow.GetCell(0).CellStyle = ftStyle;
- //cellRangAddress鍙傛暟璇存槑锛氳捣濮嬭锛岀粨鏉熻锛岃捣濮嬪垪锛岀粨鏉熷垪
- sheet.AddMergedRegion(
- new CellRangeAddress(_rot, _rot, 0, _ftw - 1));
- ftRow.CreateCell(_ftw)
- .SetCellValue(aryFotter2[_ftidx].ToString());
+ lastSheet.AddMergedRegion(new CellRangeAddress(_rot, _rot, 0, _ftw - 1));
+ ftRow.CreateCell(_ftw).SetCellValue(aryFotter2[_ftidx].ToString());
ftRow.GetCell(_ftw).CellStyle = ftStyle;
- sheet.AddMergedRegion(new CellRangeAddress(_rot, _rot, _ftw,
+ lastSheet.AddMergedRegion(new CellRangeAddress(_rot, _rot, _ftw,
dtSource.Columns.Count - 1));
for (var c = 0; c < dtSource.Columns.Count; c++)
{
@@ -280,7 +301,7 @@
}
}
- //淇濆瓨
+ // 淇濆瓨
using (var ms = new MemoryStream())
{
using (var fs = new FileStream(strFileName, FileMode.Create,
@@ -360,13 +381,15 @@
ICellStyle style = getStyle(workbook);
ICellStyle styleHeader = getStyle(workbook, 20, true, true);
ICellStyle style12 = getStyle(workbook, 11, false, true);
-
+ style.WrapText = true; // 璁剧疆鑷姩鎹㈣
+ styleHeader.WrapText = true; // 璁剧疆鑷姩鎹㈣
+ style12.WrapText = true; // 璁剧疆鑷姩鎹㈣
// 璁剧疆鍒楀锛�6鍒楋級
sheet.SetColumnWidth(0, 4000); // 绗竴鍒楃◢瀹�
- sheet.SetColumnWidth(1, 3000);
+ sheet.SetColumnWidth(1, 4000);
sheet.SetColumnWidth(2, 3000);
sheet.SetColumnWidth(3, 3000);
- sheet.SetColumnWidth(4, 3000);
+ sheet.SetColumnWidth(4, 4000);
sheet.SetColumnWidth(5, 3000);
sheet.SetColumnWidth(6, 3000);
// 鍒涘缓20琛�
@@ -429,16 +452,134 @@
//浠庣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());
+ string ddd = rrr["鍒剁儹鎬ц兘table"].ToString();
+ string ccc = getTableToString(ddd);
+ cell7.SetCellValue(ccc);
+ row7.HeightInPoints = rowHeight;
+ 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++;
}
@@ -487,28 +628,9 @@
cell.CellStyle = style12;
}
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 0, 6));
- setCellVal(sheet, _idx, 0, "棣栦欢濉啓,鏉ユ枡妫�楠�");
+ 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));
-
+ getRowHeight(_idx, sheet, maxCols, "XlsFontXj");
//淇濆瓨
using (var ms = new MemoryStream())
{
@@ -531,6 +653,8 @@
/// <param name="dtSource"></param>
public static void ExportIqc(DataSet dtSource, string strFileName)
{
+ string isHg = "";
+
//琛岄珮
int rowHeight = 25;
//鎬诲垪
@@ -543,6 +667,12 @@
ICellStyle style = getStyle(workbook);
ICellStyle styleHeader = getStyle(workbook, 20, true, true);
ICellStyle style12 = getStyle(workbook, 11, false, true);
+ style.WrapText = true; // 璁剧疆鑷姩鎹㈣
+ styleHeader.WrapText = true; // 璁剧疆鑷姩鎹㈣
+ style12.WrapText = true; // 璁剧疆鑷姩鎹㈣
+ //style.ShrinkToFit = true;
+ //styleHeader.ShrinkToFit = true;
+ //style12.ShrinkToFit = true;
// 璁剧疆鍒楀锛�6鍒楋級
sheet.SetColumnWidth(0, 3000); // 绗竴鍒楃◢瀹�
@@ -552,7 +682,7 @@
sheet.SetColumnWidth(4, 3000);
sheet.SetColumnWidth(5, 3000);
sheet.SetColumnWidth(6, 3000);
- sheet.SetColumnWidth(7, 3000);
+ sheet.SetColumnWidth(7, 3500);
// 鍒涘缓9琛�
for (int rowIndex = 0; rowIndex <= 8; rowIndex++)
{
@@ -574,6 +704,7 @@
//杩欐槸琛ㄥご
DataTable tb0 = dtSource.Tables[0];
DataRow row0 = tb0.Rows[0];
+ isHg = row0["zhpd"].ToString();
//绗竴琛屽垪鍚堝苟
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 4, 7));
@@ -625,9 +756,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, "妫�楠岃褰�");
@@ -658,10 +789,11 @@
for (int colIndex = 0; colIndex < maxCols; colIndex++)
{
ICell cell = row8.CreateCell(colIndex);
- cell.CellStyle = style12;
+ cell.CellStyle = style;
}
sheet.AddMergedRegion(new CellRangeAddress(_idx, _idx, 1, 7));
setCellVal(sheet, _idx, 0, "澶囨敞锛�");
+ setCellVal(sheet, _idx, 1, row0["iqcRemark"].ToString());
_idx++;
//缁煎悎鍒ゅ畾
for (int i = 0; i < 2; i++)
@@ -699,28 +831,36 @@
setCellVal(sheet, _idx - 2, 0, "瀹℃牳鎰忚锛�");
setCellVal(sheet, _idx - 2, 1, row0["shyj"].ToString());
setCellVal(sheet, _idx - 1, 1, row0["shyjqz"].ToString());
- string[] ary = { "閲囪喘閮ㄦ剰瑙�", "閿�鍞儴鎰忚", "鎶�鏈儴鎰忚", "鐢熶骇閮ㄦ剰瑙�", "鍝佽川閮ㄦ剰瑙�" };
- for (int i = 0; i < ary.Length; i++)
+
+ //濡傛灉鏄悎鏍间欢锛岄偅涓嬮潰涓嶈浜哹egin
+ if (isHg != "Y:鍚堟牸")
{
- IRow row9 = sheet.CreateRow(_idx);
- row9.HeightInPoints = rowHeight;
- for (int colIndex = 0; colIndex < maxCols; colIndex++)
+ string[] ary = { "閲囪喘閮ㄦ剰瑙�", "閿�鍞儴鎰忚", "鎶�鏈儴鎰忚", "鐢熶骇閮ㄦ剰瑙�", "鍝佽川閮ㄦ剰瑙�" };
+ for (int i = 0; i < ary.Length; i++)
{
- ICell cell = row9.CreateCell(colIndex);
- cell.CellStyle = style;
+ IRow row9 = sheet.CreateRow(_idx);
+ row9.HeightInPoints = rowHeight;
+ for (int colIndex = 0; colIndex < maxCols; 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++;
}
- // 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 (int i = 0; i < ary.Length; i++)
+ {
+ setCellVal(sheet, _idx - i - 1, 1, ary[i]);
+ setCellVal(sheet, _idx - i - 1, 2, "鈻� 鍚屾剰 鈻� 涓嶅悓鎰� 绛惧悕/鏃ユ湡锛�");
+ }
}
- setCellVal(sheet, _idx - ary.Length, 0, "涓嶅悎鏍艰瘎瀹★細");
- 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, "鈻� 鍚屾剰 鈻� 涓嶅悓鎰� 绛惧悕/鏃ユ湡锛�");
- }
+ //濡傛灉鏄悎鏍间欢锛岄偅涓嬮潰涓嶈浜唀nd
+
//****鍚堝苟濂藉悗寮�濮嬭祴鍊糴nd**************
+ getRowHeight(_idx, sheet, maxCols, "XlsFont");
//淇濆瓨
using (var ms = new MemoryStream())
{
@@ -736,7 +876,82 @@
}
}
}
+ /// <summary>
+ /// 璁＄畻楂樺害
+ /// </summary>
+ /// <param name="_idx"></param>
+ /// <param name="sheet"></param>
+ /// <param name="maxCols"></param>
+ private static void getRowHeight(int _idx, ISheet sheet, int maxCols, string XlsFont)
+ {
+ int _XlsFont = int.Parse(AppSettingsHelper.getValueByKey(XlsFont).ToString());
+ for (int i = 0; i < _idx; i++)
+ {
+ if (sheet.GetRow(i) == null)
+ {
+ continue;
+ }
+ IRow ICurRow = sheet.GetRow(i);
+ int OldHg = ICurRow.Height;
+ for (int j = 0; j < maxCols; j++)
+ {
+ if (ICurRow.GetCell(j) == null)
+ {
+ continue;
+ }
+ ICell CurCell = ICurRow.GetCell(j);
+ if (CurCell.CellType == NPOI.SS.UserModel.CellType.String && CurCell.StringCellValue != string.Empty)
+ {
+ double CurHeight = ICurRow.Sheet.GetColumnWidth(CurCell.ColumnIndex) / 277;
+ double length = Encoding.Default.GetBytes(CurCell.ToString()).Length;
+ short height = Convert.ToInt16(Math.Ceiling(length / CurHeight));
+ if (height * _XlsFont > OldHg)
+ {
+ OldHg = height * _XlsFont;
+ }
+ }
+ }
+ if (ICurRow.Height < OldHg)
+ {
+ ICurRow.Height = Convert.ToInt16(OldHg);
+ }
+ //鍥犱负杩欓噷鍏ㄩ儴 鏁板瓧锛屾劅瑙夊彇鐨勯珮搴︿笉瀵�
+ if (ICurRow.GetCell(0).ToString() == "鍒剁儹鎬ц兘")
+ {
+ ICurRow.Height = Convert.ToInt16(OldHg / 3);
+ }
+ }
+ }
+ /// <summary>
+ /// 璇诲彇鍒剁儹鎬ц兘锛屾妸table杞负strin
+ /// </summary>
+ /// <param name="json"></param>
+ /// <returns></returns>
+ private static string getTableToString(string json)
+ {
+ if (string.IsNullOrEmpty(json))
+ return "";
+ System.Text.StringBuilder sbLine = new StringBuilder();
+ var data = JsonConvert.DeserializeObject<Dictionary<string, Dictionary<string, string>>>(json);
+ List<string> resultLines = new List<string>();
+ foreach (var entry in data)
+ {
+ string key = entry.Key;
+ var values = entry.Value;
+ string temp = values["temp"];
+ sbLine.Append(key + "/" + temp + "锛歕t");
+ System.Text.StringBuilder dbdb = new StringBuilder();
+ foreach (var dddddd in values)
+ {
+ if (dddddd.Key == "temp")
+ continue;
+ dbdb.Append(dddddd.Key + "-" + dddddd.Value + "銆�");
+ }
+ sbLine.Append(dbdb.ToString() + "\n");
+ }
+ return sbLine.ToString();
+ }
/// <summary>
/// 鐢熸垚宸℃
@@ -750,16 +965,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 +981,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 +1000,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 +1071,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,13 +1094,374 @@
}
}
+ /// <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);
+ style.WrapText = true;
+ styleHeader.WrapText = true;
+ styleTitle.WrapText = true;
+ styleSmall.WrapText = true;
+
+ // 璁剧疆鍒楀
+ sheet.SetColumnWidth(0, 3500); // A鍒楋細椤圭洰鍚嶇О
+ sheet.SetColumnWidth(1, 5000); // B鍒楋細鏍囧噯/瑙勮寖
+ for (int i = 2; i < 18; i++) // C鍒癛鍒楋細16涓娴嬪�煎垪
+ {
+ sheet.SetColumnWidth(i, 3500);
+ }
+
+ //鑾峰彇琛ㄥご鏁版嵁
+ 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++;
+ }
+
+ getRowHeight(_idx, sheet, maxCols, "XlsFontXj");
+
+ //淇濆瓨
+ 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
+ /// Excel瀵煎叆鎴怐atable
/// </summary>
/// <param name="file">瀵煎叆璺緞(鍖呭惈鏂囦欢鍚嶄笌鎵╁睍鍚�)</param>
+ /// <param name="strType">宸ュ崟缂栧彿锛岃繃婊ゅ琛ㄦ牸</param>
/// <returns></returns>
- public static DataTable ExcelToTable(string file)
+ public static DataTable ExcelToTable(string file, string strType = "")
{
var dt = new DataTable();
IWorkbook workbook;
@@ -865,7 +1481,16 @@
var sheet = workbook.GetSheetAt(0);
//琛ㄥご
- var header = sheet.GetRow(sheet.FirstRowNum + 1);
+ int intBt = 0;
+ //濡傛灉鏄悗鐩栫爜锛屽彇绗�1琛岋紝鍚﹀垯鍙栫浜岃
+ if (string.IsNullOrEmpty(strType))
+ {
+ intBt = sheet.FirstRowNum + 1;
+ }
+ else
+ intBt = sheet.FirstRowNum;
+
+ var header = sheet.GetRow(intBt);
var columns = new List<int>();
for (var i = 0; i < header.LastCellNum; i++)
{
@@ -880,8 +1505,19 @@
for (var act = 0; act < numberOfSheets; act++)
{
var _sheet = workbook.GetSheetAt(act);
+ #region 鍚庣洊鐮佺壒娈�
+ if (!string.IsNullOrEmpty(strType))
+ {
+ string _sheetName = _sheet.SheetName.Trim();
+ if (strType != _sheetName)
+ {
+ continue;
+ }
+ }
+ #endregion
+
//鏁版嵁
- for (var i = _sheet.FirstRowNum + 1 + 1;
+ for (var i = intBt + 1;
i <= _sheet.LastRowNum;
i++)
{
@@ -903,7 +1539,7 @@
}
/// <summary>
- /// 鑾峰彇鍗曞厓鏍肩被鍨�
+ /// 鑾峰彇鍗曞厓鏍肩被鍨�
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
--
Gitblit v1.9.3