From 6f1203d1cf895efc473fade3ab049fc30d92c618 Mon Sep 17 00:00:00 2001
From: MrShi <1878285526@qq.com>
Date: 星期三, 26 三月 2025 16:48:23 +0800
Subject: [PATCH] 优化
---
server/system_service/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java | 141 ++++++++++++++++++++++++++++-------------------
1 files changed, 84 insertions(+), 57 deletions(-)
diff --git a/server/system_service/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java b/server/system_service/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java
index d2fac34..670f20c 100644
--- a/server/system_service/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java
+++ b/server/system_service/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java
@@ -30,6 +30,7 @@
private static final String DEFAULT_SHEET_NAME = "Sheet1";
private Class<T> modelClass;
+ private static int maxrows = 50000;
private ExcelExporter(){}
@@ -76,6 +77,11 @@
// 璁剧疆鍒楀ご鍗曞厓鏍�
configHeaderCell(sxssfWorkbook, cell, column.columnConfig);
}
+ List<CellStyle> styleList = new ArrayList<>();
+ for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
+ ColumnInfo column = columns.get(columnIndex);
+ styleList.add( configDataCell(sxssfWorkbook, column.columnConfig));
+ }
// 鍒涘缓鏁版嵁璁板綍
for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
Row row = sheet.createRow(rowIndex + 2);
@@ -83,20 +89,15 @@
ColumnInfo column = columns.get(columnIndex);
Cell cell = row.createCell(columnIndex);
cell.setCellValue(getCellData(column, data.get(rowIndex)));
-
- // 璁剧疆鏁版嵁鍗曞厓鏍�
- configDataCell(sxssfWorkbook, cell, column.columnConfig);
-
+ cell.setCellStyle(styleList.get(columnIndex));
}
}
-
-
sheet.addMergedRegion(new CellRangeAddress(data.size()+2 ,data.size()+2,0,this.getColumns().size()-1));
Row endRow = sheet.createRow(data.size()+2);
// endRow.setHeight((short) 600);
Cell c1 = endRow.createCell(0);
c1.setCellValue(end);
- configEndCell(sxssfWorkbook,c1);
+ c1.setCellStyle( configEndCell(sxssfWorkbook));
sxssfWorkbook.write(os);
os.close();
} catch (Exception e) {
@@ -122,36 +123,21 @@
SXSSFWorkbook sxssfWorkbook;
try {
sxssfWorkbook = new SXSSFWorkbook();
- Sheet sheet = sxssfWorkbook.createSheet(sheetName);
- // 鍒涘缓鍒楀ご
- sheet.createFreezePane(0, 1);
- Row header = sheet.createRow(0);
- List<ColumnInfo> columns = this.getColumns();
- for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
- ColumnInfo column = columns.get(columnIndex);
- Cell cell = header.createCell(columnIndex);
- cell.setCellValue(column.columnConfig.name());
- // 鍒楀璁剧疆
- if (column.columnConfig.width() == -1) {
- sheet.setColumnWidth(columnIndex, column.columnConfig.name().length() * 2 * 256);
- } else {
- sheet.setColumnWidth(columnIndex, column.columnConfig.width() * 2 * 256);
+ int totalSheet =1;
+ if( data!=null && data.size()>0){
+ totalSheet = data.size()/maxrows;
+ if(data.size()%maxrows !=0){
+ totalSheet += 1;
}
- // 璁剧疆鍒楀ご鍗曞厓鏍�
- configHeaderCell(sxssfWorkbook, cell, column.columnConfig);
}
- // 鍒涘缓鏁版嵁璁板綍
- for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
- Row row = sheet.createRow(rowIndex + 1);
- for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
- ColumnInfo column = columns.get(columnIndex);
- Cell cell = row.createCell(columnIndex);
- cell.setCellValue(getCellData(column, data.get(rowIndex)));
-
- // 璁剧疆鏁版嵁鍗曞厓鏍�
- configDataCell(sxssfWorkbook, cell, column.columnConfig);
-
+ for (int i = 0; i < totalSheet; i++) {
+ List<T> list = null;
+ if(data.size() < maxrows * (i+1)) {
+ list = data.subList(maxrows*i,data.size());
+ }else{
+ list = data.subList(maxrows*i,maxrows*(i+1));
}
+ createSheetDataBiz(list,"銆�"+(i+1)+"銆�"+sheetName,sxssfWorkbook);
}
sxssfWorkbook.write(os);
os.close();
@@ -164,6 +150,43 @@
} catch (IOException e) {
e.printStackTrace();
}
+ }
+ }
+ }
+
+ private void createSheetDataBiz(List<T> data, String sheetName, SXSSFWorkbook sxssfWorkbook) throws Exception{
+ Sheet sheet = sxssfWorkbook.createSheet(sheetName);
+ // 鍒涘缓鍒楀ご
+ sheet.createFreezePane(0, 1);
+ Row header = sheet.createRow(0);
+ List<ColumnInfo> columns = this.getColumns();
+ for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
+ ColumnInfo column = columns.get(columnIndex);
+ Cell cell = header.createCell(columnIndex);
+ cell.setCellValue(column.columnConfig.name());
+ // 鍒楀璁剧疆
+ if (column.columnConfig.width() == -1) {
+ sheet.setColumnWidth(columnIndex, column.columnConfig.name().length() * 2 * 256);
+ } else {
+ sheet.setColumnWidth(columnIndex, column.columnConfig.width() * 2 * 256);
+ }
+ // 璁剧疆鍒楀ご鍗曞厓鏍�
+ configHeaderCell(sxssfWorkbook, cell, column.columnConfig);
+ }
+ List<CellStyle> styleList = new ArrayList<>();
+ for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
+ ColumnInfo column = columns.get(columnIndex);
+ styleList.add( configDataCell(sxssfWorkbook, column.columnConfig));
+ }
+ // 鍒涘缓鏁版嵁璁板綍
+ for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
+ Row row = sheet.createRow(rowIndex + 1);
+ for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
+ ColumnInfo column = columns.get(columnIndex);
+ Cell cell = row.createCell(columnIndex);
+ cell.setCellValue(getCellData(column, data.get(rowIndex)));
+ // 璁剧疆鏁版嵁鍗曞厓鏍兼牱寮�
+ cell.setCellStyle(styleList.get(columnIndex));
}
}
}
@@ -251,27 +274,29 @@
/**
* 閰嶇疆鏁版嵁鍗曞厓鏍�
*/
- private void configDataCell (SXSSFWorkbook workbook, Cell cell, ExcelColumn columnConfig) {
- CellStyle style = workbook.createCellStyle();
- style.setAlignment(columnConfig.align());
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- // 璁剧疆鑳屾櫙
- style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
- style.setFillForegroundColor(columnConfig.dataBackgroundColor().getIndex());
- // 瀛椾綋
- Font font = workbook.createFont();
- font.setFontHeightInPoints(columnConfig.fontSize());
- // 瀛椾綋棰滆壊
- font.setColor(columnConfig.color().getIndex());
- // 绮椾綋
- font.setBold(columnConfig.bold());
- // 鏂滀綋
- font.setItalic(columnConfig.italic());
- style.setFont(font);
- // 杈规
- configCellBorder(style);
- style.setWrapText(true);
- cell.setCellStyle(style);
+ private CellStyle configDataCell (SXSSFWorkbook workbook, ExcelColumn columnConfig) {
+ CellStyle configDataCellStyle = workbook.createCellStyle();
+ configDataCellStyle.setAlignment(columnConfig.align());
+ configDataCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
+ // 璁剧疆鑳屾櫙
+ configDataCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ configDataCellStyle.setFillForegroundColor(columnConfig.dataBackgroundColor().getIndex());
+ // 瀛椾綋
+ Font font = workbook.createFont();
+ font.setFontHeightInPoints(columnConfig.fontSize());
+ // 瀛椾綋棰滆壊
+ font.setColor(columnConfig.color().getIndex());
+ // 绮椾綋
+ font.setBold(columnConfig.bold());
+ // 鏂滀綋
+ font.setItalic(columnConfig.italic());
+ configDataCellStyle.setFont(font);
+ // 杈规
+ configCellBorder(configDataCellStyle);
+ configDataCellStyle.setWrapText(true);
+
+ return configDataCellStyle;
+// cell.setCellStyle(configDataCellStyle);
}
/**
@@ -310,10 +335,11 @@
configCellBorder(style);
cell.setCellStyle(style);
}
+// public static CellStyle configEndCellStyle =null;
/**
* 閰嶇疆鍒楀ご鍗曞厓鏍�
*/
- private void configEndCell (SXSSFWorkbook workbook, Cell cell ) {
+ private CellStyle configEndCell (SXSSFWorkbook workbook ) {
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.RIGHT);
style.setVerticalAlignment(VerticalAlignment.CENTER);
@@ -326,7 +352,8 @@
style.setFont(font);
// 璁剧疆杈规
configCellBorder(style);
- cell.setCellStyle(style);
+ return style;
+
}
/**
--
Gitblit v1.9.3