From 30e858fa504b268b9b436afca0a1259cf6e8c488 Mon Sep 17 00:00:00 2001
From: MrShi <1878285526@qq.com>
Date: 星期二, 19 八月 2025 11:01:40 +0800
Subject: [PATCH] 优化
---
server/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java | 266 ++++++++++++++++++++++++++++++++++++++++++-----------
1 files changed, 211 insertions(+), 55 deletions(-)
diff --git a/server/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java b/server/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java
index dd72cdc..477d2fd 100644
--- a/server/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java
+++ b/server/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java
@@ -5,6 +5,7 @@
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.core.annotation.AnnotationConfigurationException;
@@ -14,14 +15,14 @@
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
-import java.nio.charset.StandardCharsets;
+import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Excel瀵煎嚭瀹炵幇
- * @author dm
- * @since 2025/03/31 16:44
+ * @author Eva.Caesar Liu
+ * @date 2023/02/14 11:14
*/
@Data
public class ExcelExporter<T> {
@@ -29,12 +30,12 @@
private static final String DEFAULT_SHEET_NAME = "Sheet1";
private Class<T> modelClass;
+ private static int maxrows = 50000;
private ExcelExporter(){}
/**
* 鏋勯�犲櫒
- *
* @param modelClass 瀹炰綋Class瀵硅薄
*/
public static <T> ExcelExporter<T> build(Class<T> modelClass) {
@@ -42,22 +43,26 @@
excelExporter.setModelClass(modelClass);
return excelExporter;
}
-
/**
* 瀵煎嚭鍒版寚瀹氳緭鍑烘祦
- *
* @param data 鏁版嵁
* @param sheetName Sheet鍚嶇О
- * @param os 杈撳嚭娴�
*/
- public void exportData(List<T> data, String sheetName, OutputStream os) {
+ public void exportWithFirstAndEnd (List<T> data, String sheetName,String first,String end, OutputStream os) {
SXSSFWorkbook sxssfWorkbook;
try {
sxssfWorkbook = new SXSSFWorkbook();
Sheet sheet = sxssfWorkbook.createSheet(sheetName);
// 鍒涘缓鍒楀ご
- sheet.createFreezePane(0, 1);
- Row header = sheet.createRow(0);
+ sheet.createFreezePane(0, 2);
+ sheet.addMergedRegion(new CellRangeAddress(0 ,0,0,this.getColumns().size()-1));
+ Row title = sheet.createRow(0);
+ title.setHeight((short) 1000);
+ Cell c = title.createCell(0);
+ c.setCellValue(first);
+ configFirstCell(sxssfWorkbook,c);
+
+ Row header = sheet.createRow(1);
List<ColumnInfo> columns = this.getColumns();
for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
ColumnInfo column = columns.get(columnIndex);
@@ -72,17 +77,27 @@
// 璁剧疆鍒楀ご鍗曞厓鏍�
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);
+ Row row = sheet.createRow(rowIndex + 2);
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);
+ 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);
+ c1.setCellStyle( configEndCell(sxssfWorkbook));
sxssfWorkbook.write(os);
os.close();
} catch (Exception e) {
@@ -99,21 +114,117 @@
}
/**
+ * 瀵煎嚭鍒版寚瀹氳緭鍑烘祦
+ * @param data 鏁版嵁
+ * @param sheetName Sheet鍚嶇О
+ * @param os 杈撳嚭娴�
+ */
+ public void export (List<T> data, String sheetName, OutputStream os) {
+ SXSSFWorkbook sxssfWorkbook;
+ try {
+ sxssfWorkbook = new SXSSFWorkbook();
+ int totalSheet =1;
+ if( data!=null && data.size()>0){
+ totalSheet = data.size()/maxrows;
+ if(data.size()%maxrows !=0){
+ totalSheet += 1;
+ }
+ }
+ 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();
+ } catch (Exception e) {
+ throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
+ } finally {
+ if (os != null) {
+ try {
+ os.close();
+ } 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));
+ }
+ }
+ }
+
+ /**
* 瀵煎嚭鑷冲搷搴旀祦
- *
* @param data 鏁版嵁
* @param fileName Excel鏂囦欢鍚�
* @param sheetName Sheet鍚嶇О
* @param response HttpServletResponse瀵硅薄
*/
- public void exportData(List<T> data, String fileName, String sheetName, HttpServletResponse response) {
+ public void export (List<T> data, String fileName, String sheetName, HttpServletResponse response) {
try {
- String encodeFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString()) + ".xlsx";
+ String encodeFileName = URLEncoder.encode(fileName, Charset.forName("UTF-8").toString()) + ".xlsx";
response.setHeader("Content-Disposition","attachment;filename=" + encodeFileName);
response.setContentType("application/octet-stream");
response.setHeader("doumee-opera-type", "download");
response.setHeader("doumee-download-filename", encodeFileName);
- this.exportData(data, sheetName, response.getOutputStream());
+ this.export(data, sheetName, response.getOutputStream());
+ } catch (IOException e) {
+ throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
+ }
+ }
+ /**
+ * 瀵煎嚭鑷冲搷搴旀祦
+ * @param data 鏁版嵁
+ * @param fileName Excel鏂囦欢鍚�
+ * @param sheetName Sheet鍚嶇О
+ * @param response HttpServletResponse瀵硅薄
+ */
+ public void exportWithFirstAndEnd (List<T> data, String fileName, String sheetName, String first,String end ,HttpServletResponse response) {
+ try {
+ String encodeFileName = URLEncoder.encode(fileName, Charset.forName("UTF-8").toString()) + ".xlsx";
+ response.setHeader("Content-Disposition","attachment;filename=" + encodeFileName);
+ response.setContentType("application/octet-stream");
+ response.setHeader("doumee-opera-type", "download");
+ response.setHeader("doumee-download-filename", encodeFileName);
+ this.exportWithFirstAndEnd(data, sheetName,first,end, response.getOutputStream());
} catch (IOException e) {
throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
}
@@ -121,16 +232,25 @@
/**
* 瀵煎嚭鑷冲搷搴旀祦
- *
* @param data 鏁版嵁
* @param fileName Excel鏂囦欢鍚�
* @param response HttpServletResponse瀵硅薄
*/
- public void exportData(List<T> data, String fileName, HttpServletResponse response) {
- this.exportData(data, fileName, DEFAULT_SHEET_NAME, response);
+ public void export (List<T> data, String fileName, HttpServletResponse response) {
+
+ this.export(data, fileName, DEFAULT_SHEET_NAME, response);
+ }
+ /**
+ * 瀵煎嚭鑷冲搷搴旀祦
+ * @param data 鏁版嵁
+ * @param fileName Excel鏂囦欢鍚�
+ * @param response HttpServletResponse瀵硅薄
+ */
+ public void exportWithFirstAndEnd (List<T> data, String fileName,String first,String end, HttpServletResponse response) {
+ this.exportWithFirstAndEnd(data, fileName, DEFAULT_SHEET_NAME, first,end,response);
}
- /**
+ /**O
* 鑾峰彇鍒楅泦鍚�
*/
private List<ColumnInfo> getColumns () {
@@ -138,12 +258,12 @@
Field[] fields = modelClass.getDeclaredFields();
int index = 0;
for (Field field : fields) {
- ExcelExportColumn excelColumn = field.getAnnotation(ExcelExportColumn.class);
+ ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
if (excelColumn == null) {
continue;
}
if (sortedFields.get(excelColumn.index()) != null) {
- throw new AnnotationConfigurationException("EVA: excel column contains the same index.");
+ throw new AnnotationConfigurationException("Excel column contains the same index.");
}
sortedFields.put(excelColumn.index() == -1 ? index : excelColumn.index(), new ColumnInfo(excelColumn, field));
index++;
@@ -154,32 +274,35 @@
/**
* 閰嶇疆鏁版嵁鍗曞厓鏍�
*/
- private void configDataCell (SXSSFWorkbook workbook, Cell cell, ExcelExportColumn 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);
- 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);
}
/**
* 閰嶇疆鍒楀ご鍗曞厓鏍�
*/
- private void configHeaderCell (SXSSFWorkbook workbook, Cell cell, ExcelExportColumn columnConfig) {
+ private void configHeaderCell (SXSSFWorkbook workbook, Cell cell, ExcelColumn columnConfig) {
CellStyle style = workbook.createCellStyle();
style.setAlignment(columnConfig.align());
style.setVerticalAlignment(VerticalAlignment.CENTER);
@@ -193,6 +316,44 @@
// 璁剧疆杈规
configCellBorder(style);
cell.setCellStyle(style);
+ }
+ /**
+ * 閰嶇疆鍒楀ご鍗曞厓鏍�
+ */
+ private void configFirstCell (SXSSFWorkbook workbook, Cell cell ) {
+ 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)18);
+ style.setFont(font);
+ // 璁剧疆杈规
+ configCellBorder(style);
+ cell.setCellStyle(style);
+ }
+// public static CellStyle configEndCellStyle =null;
+ /**
+ * 閰嶇疆鍒楀ご鍗曞厓鏍�
+ */
+ private CellStyle configEndCell (SXSSFWorkbook workbook ) {
+ CellStyle style = workbook.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ // 璁剧疆鑳屾櫙
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
+ // 瀛椾綋
+ Font font = workbook.createFont();
+ font.setFontHeightInPoints((short)14);
+ style.setFont(font);
+ // 璁剧疆杈规
+ configCellBorder(style);
+ return style;
+
}
/**
@@ -221,19 +382,19 @@
}
String stringValue = value.toString();
// 瀛樺湪鑷畾涔夋暟鎹鐞嗗櫒
- if (!columnInfo.columnConfig.converter().equals(ExcelDataConverterAdapter.class)) {
+ if (!columnInfo.columnConfig.handler().equals(ExcelDataHandlerAdapter.class)) {
try {
- Object instance = columnInfo.columnConfig.converter().newInstance();
- Method convertMethod = columnInfo.columnConfig.converter().getMethod("convert", Object[].class);
+ Object instance = columnInfo.columnConfig.handler().newInstance();
+ Method formatMethod = columnInfo.columnConfig.handler().getMethod("format", Object[].class);
List<Object> args = new ArrayList<>();
args.add(value);
for (String arg : columnInfo.columnConfig.args()) {
args.add(arg);
}
- value = convertMethod.invoke(instance, new Object[]{args.toArray()});
+ value = formatMethod.invoke(instance, new Object[]{args.toArray()});
stringValue = value.toString();
} catch (Exception e) {
- throw new IllegalStateException("EVA: can not convert data by " + columnInfo.columnConfig.converter(), e);
+ throw new IllegalStateException("Can not format data by " + columnInfo.columnConfig.handler(), e);
}
}
// 鏃ユ湡澶勭悊
@@ -258,17 +419,12 @@
return stringValue;
}
- /**
- * 鍒椾俊鎭�
- */
@Data
@AllArgsConstructor
private static class ColumnInfo {
- // 鍒楅厤缃�
- private ExcelExportColumn columnConfig;
+ private ExcelColumn columnConfig;
- // 瀛楁淇℃伅
private Field field;
}
--
Gitblit v1.9.3