From bd7f147b15efd57c5f054e537e715486c53c25dc Mon Sep 17 00:00:00 2001
From: 快乐的昕的电脑 <快乐的昕的电脑@DESKTOP-C2BQPQU>
Date: 星期五, 28 十一月 2025 17:08:34 +0800
Subject: [PATCH] 标准版
---
WebApi/Gs.Toolbox/ExcelHelper.cs | 131 +++++++++++++++++++++++++------------------
1 files changed, 75 insertions(+), 56 deletions(-)
diff --git a/WebApi/Gs.Toolbox/ExcelHelper.cs b/WebApi/Gs.Toolbox/ExcelHelper.cs
index 45bc4b2..47b087c 100644
--- a/WebApi/Gs.Toolbox/ExcelHelper.cs
+++ b/WebApi/Gs.Toolbox/ExcelHelper.cs
@@ -178,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++)
{
@@ -192,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)
@@ -260,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++)
{
@@ -282,7 +301,7 @@
}
}
- //淇濆瓨
+ // 淇濆瓨
using (var ms = new MemoryStream())
{
using (var fs = new FileStream(strFileName, FileMode.Create,
--
Gitblit v1.9.3