From bcd62be27acf68adb54415f75b37f5e26d65c11b Mon Sep 17 00:00:00 2001
From: jiangping <jp@doumee.com>
Date: 星期三, 07 五月 2025 10:34:44 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master'
---
server/system_service/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java | 211 ++++++++++++++++++++++++++++++++++++++--------------
1 files changed, 153 insertions(+), 58 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..0b02a91 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
@@ -2,6 +2,7 @@
import com.doumee.core.constants.ResponseStatus;
import com.doumee.core.exception.BusinessException;
+import com.doumee.core.utils.Constants;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
@@ -30,6 +31,7 @@
private static final String DEFAULT_SHEET_NAME = "Sheet1";
private Class<T> modelClass;
+ private static int maxrows = 50000;
private ExcelExporter(){}
@@ -76,6 +78,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 +90,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 +124,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 +151,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 +275,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 +336,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,13 +353,14 @@
style.setFont(font);
// 璁剧疆杈规
configCellBorder(style);
- cell.setCellStyle(style);
+ return style;
+
}
/**
* 閰嶇疆鍗曞厓鏍艰竟妗�
*/
- private void configCellBorder (CellStyle style) {
+ private static void configCellBorder(CellStyle style) {
style.setBorderTop(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
@@ -401,4 +429,71 @@
private Field field;
}
+ /**
+ * 瀵煎嚭鍒版寚瀹氳緭鍑烘祦
+ * @param os 杈撳嚭娴�
+ */
+ public static void exportList (List<List<String>> dataList ,String sheetName, OutputStream os) {
+ SXSSFWorkbook sxssfWorkbook;
+ try {
+ sxssfWorkbook = new SXSSFWorkbook();
+ Sheet sheet = sxssfWorkbook.createSheet(sheetName);
+ // 鍒涘缓鍒楀ご
+ sheet.createFreezePane(0, 1);
+ Row header = sheet.createRow(0);
+ CellStyle hstyle = configHeaderCellStatic(sxssfWorkbook);
+ for (int i = 0; i < dataList.size(); i++) {
+ Cell cell = header.createCell(i);
+ cell.setCellValue(dataList.get(i).get(Constants.ZERO));
+ // 鍒楀璁剧疆
+ sheet.setColumnWidth(i, dataList.get(i).get(Constants.ZERO).length() * 2 * 256);
+ // 璁剧疆鍒楀ご鍗曞厓鏍�
+ cell.setCellStyle(hstyle);
+ }
+
+ //鎬昏鏁�
+ Integer rowSize = dataList.get(Constants.ZERO).size();
+ //鎬诲垪鏁�
+ Integer columnSize = dataList.size();
+ for (int i = 1; i < rowSize; i++) {
+ Row row = sheet.createRow(i);
+ for (int j = 0; j < columnSize; j++) {
+ Cell cell = row.createCell(j);
+ cell.setCellValue(dataList.get(j).get(i));
+ }
+ }
+ sxssfWorkbook.write(os);
+ os.close();
+ } catch (Exception e) {
+ throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
+ } finally {
+ if (os != null) {
+ try {
+ os.close();
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ }
+ }
+ }
+
+ private static CellStyle configHeaderCellStatic (SXSSFWorkbook workbook) {
+ CellStyle style = workbook.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ // 璁剧疆鑳屾櫙
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
+ // 瀛椾綋
+ Font font = workbook.createFont();
+ font.setFontHeightInPoints((short) 12);
+ style.setFont(font);
+ // 璁剧疆杈规
+ configCellBorder(style);
+ return style;
+ }
+
+
+
+
}
--
Gitblit v1.9.3