From 4fabfe4dbd2eb28d07a4350597d314958cc1c281 Mon Sep 17 00:00:00 2001
From: MrShi <1878285526@qq.com>
Date: 星期四, 09 十月 2025 11:16:43 +0800
Subject: [PATCH] 优化
---
server/services/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java | 741 +++++++++++++++++++-------------------------------------
1 files changed, 250 insertions(+), 491 deletions(-)
diff --git a/server/services/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java b/server/services/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java
index cfeb782..c6090e0 100644
--- a/server/services/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java
+++ b/server/services/src/main/java/com/doumee/core/annotation/excel/ExcelExporter.java
@@ -1,43 +1,30 @@
package com.doumee.core.annotation.excel;
+import com.doumee.core.constants.Constants;
import com.doumee.core.constants.ResponseStatus;
import com.doumee.core.exception.BusinessException;
+import com.doumee.dao.business.model.Goodsorder;
import lombok.AllArgsConstructor;
import lombok.Data;
-import org.apache.commons.lang3.BooleanUtils;
-import org.apache.commons.lang3.CharUtils;
-import org.apache.commons.lang3.StringUtils;
-import org.apache.commons.lang3.math.NumberUtils;
-import org.apache.poi.hssf.usermodel.HSSFRichTextString;
-import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
-import org.apache.poi.xssf.usermodel.XSSFWorkbook;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.AnnotationConfigurationException;
-import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
-import java.io.InputStream;
import java.io.OutputStream;
-import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
-import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.*;
-import java.util.stream.Collectors;
-import java.util.stream.Stream;
/**
* Excel瀵煎嚭瀹炵幇
* @author Eva.Caesar Liu
- * @date 2022/04/18 18:12
+ * @date 2023/02/14 11:14
*/
@Data
public class ExcelExporter<T> {
@@ -48,13 +35,6 @@
private ExcelExporter(){}
-
-
- private final static Logger log = LoggerFactory.getLogger(ExcelExporter.class);
-
- private final static String EXCEL2003 = "xls";
- private final static String EXCEL2007 = "xlsx";
-
/**
* 鏋勯�犲櫒
* @param modelClass 瀹炰綋Class瀵硅薄
@@ -63,6 +43,75 @@
ExcelExporter<T> excelExporter = new ExcelExporter<>();
excelExporter.setModelClass(modelClass);
return excelExporter;
+ }
+ /**
+ * 瀵煎嚭鍒版寚瀹氳緭鍑烘祦
+ * @param data 鏁版嵁
+ * @param sheetName Sheet鍚嶇О
+ */
+ 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, 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);
+ 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);
+ }
+ // 鍒涘缓鏁版嵁璁板綍
+ for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
+ 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);
+
+ }
+ }
+
+
+ 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);
+ 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();
+ }
+ }
+ }
}
/**
@@ -75,8 +124,6 @@
SXSSFWorkbook sxssfWorkbook;
try {
sxssfWorkbook = new SXSSFWorkbook();
-
- CellStyle style = sxssfWorkbook.createCellStyle();
Sheet sheet = sxssfWorkbook.createSheet(sheetName);
// 鍒涘缓鍒楀ご
sheet.createFreezePane(0, 1);
@@ -93,7 +140,7 @@
sheet.setColumnWidth(columnIndex, column.columnConfig.width() * 2 * 256);
}
// 璁剧疆鍒楀ご鍗曞厓鏍�
- configHeaderCell(sxssfWorkbook, cell, column.columnConfig,style);
+ configHeaderCell(sxssfWorkbook, cell, column.columnConfig);
}
// 鍒涘缓鏁版嵁璁板綍
for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
@@ -102,10 +149,95 @@
ColumnInfo column = columns.get(columnIndex);
Cell cell = row.createCell(columnIndex);
cell.setCellValue(getCellData(column, data.get(rowIndex)));
+
// 璁剧疆鏁版嵁鍗曞厓鏍�
- configDataCell(sxssfWorkbook, cell, column.columnConfig,style);
+ configDataCell(sxssfWorkbook, cell, column.columnConfig);
+
}
}
+ 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();
+ }
+ }
+ }
+ }
+
+// public void exportList (List<List<String>> data, String fileName, String sheetName, 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("eva-opera-type", "download");
+// response.setHeader("eva-download-filename", encodeFileName);
+// this.exportList(data, sheetName, response.getOutputStream());
+// } catch (IOException e) {
+// throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
+// }
+// }
+
+
+ /**
+ * 瀵煎嚭鍒版寚瀹氳緭鍑烘祦
+ * @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));
+ }
+ }
+//
+// List<String> headerList =dataList.get(0);
+// for (int i = 0; i < headerList.size(); i++) {
+// Cell cell = header.createCell(i);
+// cell.setCellValue(headerList.get(i));
+// // 鍒楀璁剧疆
+// sheet.setColumnWidth(i, headerList.get(i).length() * 2 * 256);
+// // 璁剧疆鍒楀ご鍗曞厓鏍�
+// cell.setCellStyle(hstyle);
+// }
+// // 鍒涘缓鏁版嵁璁板綍
+// for (int rowIndex = 1; rowIndex < dataList.size(); rowIndex++) {
+// Row row = sheet.createRow(rowIndex );
+// List<String> rowList = dataList.get(rowIndex);
+// for (int i = 0; i < rowList.size(); i++) {
+// Cell cell = row.createCell(i);
+// cell.setCellValue(rowList.get(i));
+// // 鍒楀璁剧疆
+// cell.setCellStyle(cstyle);
+// }
+// }
sxssfWorkbook.write(os);
os.close();
} catch (Exception e) {
@@ -140,26 +272,6 @@
throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
}
}
-
- /**
- * 瀵煎嚭鑷冲搷搴旀祦
- * @param data 鏁版嵁
- * @param fileName Excel鏂囦欢鍚�
- * @param response HttpServletResponse瀵硅薄
- */
- 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);
- }
-
/**
* 瀵煎嚭鑷冲搷搴旀祦
* @param data 鏁版嵁
@@ -179,74 +291,48 @@
throw new BusinessException(ResponseStatus.EXPORT_EXCEL_ERROR, e);
}
}
- /**
- * 瀵煎嚭鍒版寚瀹氳緭鍑烘祦
- * @param data 鏁版嵁
- * @param sheetName Sheet鍚嶇О
- */
- 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, 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);
- header.setHeight((short)600);
- 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(new HSSFRichTextString(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);
- }
- // 鍒涘缓鏁版嵁璁板綍
- for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) {
- 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);
- }
- }
- if(StringUtils.isNotBlank(end)){
- 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);
- }
- 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();
- }
- }
- }
+ /**
+ * 瀵煎嚭鑷冲搷搴旀祦
+ * @param data 鏁版嵁
+ * @param fileName Excel鏂囦欢鍚�
+ * @param response HttpServletResponse瀵硅薄
+ */
+ 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 () {
+ Map<Integer, ColumnInfo> sortedFields = new TreeMap<>();
+ Field[] fields = modelClass.getDeclaredFields();
+ int index = 0;
+ for (Field field : fields) {
+ ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
+ if (excelColumn == null) {
+ continue;
+ }
+ if (sortedFields.get(excelColumn.index()) != null) {
+ throw new AnnotationConfigurationException("Excel column contains the same index.");
+ }
+ sortedFields.put(excelColumn.index() == -1 ? index : excelColumn.index(), new ColumnInfo(excelColumn, field));
+ index++;
+ }
+ return new ArrayList<>(sortedFields.values());
+ }
+
/**
* 閰嶇疆鏁版嵁鍗曞厓鏍�
*/
@@ -272,6 +358,54 @@
style.setWrapText(true);
cell.setCellStyle(style);
}
+
+ /**
+ * 閰嶇疆鍒楀ご鍗曞厓鏍�
+ */
+ private void configHeaderCell (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.backgroundColor().getIndex());
+ // 瀛椾綋
+ Font font = workbook.createFont();
+ font.setFontHeightInPoints(columnConfig.fontSize());
+ style.setFont(font);
+ // 璁剧疆杈规
+ configCellBorder(style);
+ cell.setCellStyle(style);
+ }
+ 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;
+ }
+ private static CellStyle configCellStatic (SXSSFWorkbook workbook) {
+ CellStyle style = workbook.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ // 璁剧疆鑳屾櫙
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ // 瀛椾綋
+ Font font = workbook.createFont();
+ font.setFontHeightInPoints((short) 12);
+ style.setFont(font);
+ // 璁剧疆杈规
+ configCellBorder(style);
+ return style;
+ }
/**
* 閰嶇疆鍒楀ご鍗曞厓鏍�
*/
@@ -281,11 +415,10 @@
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 璁剧疆鑳屾櫙
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
- style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
+ style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
// 瀛椾綋
Font font = workbook.createFont();
- font.setFontHeightInPoints((short)24);
- font.setBold(true);
+ font.setFontHeightInPoints((short)18);
style.setFont(font);
// 璁剧疆杈规
configCellBorder(style);
@@ -309,101 +442,11 @@
configCellBorder(style);
cell.setCellStyle(style);
}
- /**
- * 閰嶇疆鍒楀ご鍗曞厓鏍�
- */
- private void configHeaderCell (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.backgroundColor().getIndex());
- style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
- // 瀛椾綋
- Font font = workbook.createFont();
- font.setFontHeightInPoints((short)11);
- font.setColor(columnConfig.color().index);
- font.setBold(true);
- style.setFont(font);
- // 璁剧疆杈规
- style.setWrapText(true);
- configCellBorder(style);
- cell.setCellStyle(style);
- }
- /**
- * 鑾峰彇鍒楅泦鍚�
- */
- private List<ColumnInfo> getColumns () {
- Map<Integer, ColumnInfo> sortedFields = new TreeMap<>();
- Field[] fields = modelClass.getDeclaredFields();
- int index = 0;
- for (Field field : fields) {
- ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
- if (excelColumn == null) {
- continue;
- }
- if (sortedFields.get(excelColumn.index()) != null) {
- throw new AnnotationConfigurationException("Excel column contains the same index.");
- }
- sortedFields.put(excelColumn.index() == -1 ? index : excelColumn.index(), new ColumnInfo(excelColumn, field));
- index++;
- }
- return new ArrayList<>(sortedFields.values());
- }
-
- public static void main(String[] args) {
- ExcelExporter m = new ExcelExporter();
-// ExcelExporter.build(UserSalaryListVO.class).getColumns();
- }
- /**
- * 閰嶇疆鏁版嵁鍗曞厓鏍�
- */
- private void configDataCell (SXSSFWorkbook workbook, Cell cell, ExcelColumn columnConfig, CellStyle style) {
- // 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 void configHeaderCell (SXSSFWorkbook workbook, Cell cell, ExcelColumn columnConfig,CellStyle style) {
- // CellStyle style = workbook.createCellStyle();
- style.setAlignment(columnConfig.align());
- style.setVerticalAlignment(VerticalAlignment.CENTER);
- // 璁剧疆鑳屾櫙
- style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
- style.setFillForegroundColor(columnConfig.backgroundColor().getIndex());
- // 瀛椾綋
- Font font = workbook.createFont();
- font.setFontHeightInPoints(columnConfig.fontSize());
- style.setFont(font);
- // 璁剧疆杈规
- configCellBorder(style);
- cell.setCellStyle(style);
- }
/**
* 閰嶇疆鍗曞厓鏍艰竟妗�
*/
- private void configCellBorder (CellStyle style) {
+ private static void configCellBorder (CellStyle style) {
style.setBorderTop(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
@@ -471,289 +514,5 @@
private Field field;
}
-
-
-
- public static <T> List<T> readExcel(String path, Class<T> cls, MultipartFile file) {
-
- String fileName = file.getOriginalFilename();
- if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
- log.error("涓婁紶鏂囦欢鏍煎紡涓嶆纭�");
- }
- List<T> dataList = new ArrayList<>();
- Workbook workbook = null;
- try {
- InputStream is = file.getInputStream();
- if (fileName.endsWith(EXCEL2007)) {
- workbook = new XSSFWorkbook(is);
- }
- if (fileName.endsWith(EXCEL2003)) {
- workbook = new HSSFWorkbook(is);
- }
- if (workbook != null) {
- //绫绘槧灏� 娉ㄨВ value-->bean columns
- Map<String, List<Field>> classMap = new HashMap<>();
- List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
- fields.forEach(
- field -> {
- ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
- if (annotation != null) {
- String value = annotation.name();
- if (StringUtils.isBlank(value)) {
- return;//return璧峰埌鐨勪綔鐢ㄥ拰continue鏄浉鍚岀殑 璇硶
- }
- if (!classMap.containsKey(value)) {
- classMap.put(value, new ArrayList<>());
- }
- field.setAccessible(true);
- classMap.get(value).add(field);
- }
- }
- );
- //绱㈠紩-->columns
- Map<Integer, List<Field>> reflectionMap = new HashMap<>(16);
- //榛樿璇诲彇绗竴涓猻heet
- Sheet sheet = workbook.getSheetAt(0);
-
- boolean firstRow = true;
- for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
- Row row = sheet.getRow(i);
- //棣栬 鎻愬彇娉ㄨВ
- if (firstRow) {
- for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
- Cell cell = row.getCell(j);
- String cellValue = getCellValue(cell);
- if (classMap.containsKey(cellValue)) {
- reflectionMap.put(j, classMap.get(cellValue));
- }
- }
- firstRow = false;
- } else {
- //蹇界暐绌虹櫧琛�
- if (row == null) {
- continue;
- }
- try {
- T t = cls.newInstance();
- //鍒ゆ柇鏄惁涓虹┖鐧借
- boolean allBlank = true;
- for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
- if (reflectionMap.containsKey(j)) {
- Cell cell = row.getCell(j);
- String cellValue = getCellValue(cell);
- if (StringUtils.isNotBlank(cellValue)) {
- allBlank = false;
- }
- List<Field> fieldList = reflectionMap.get(j);
- fieldList.forEach(
- x -> {
- try {
- handleField(t, cellValue, x);
- } catch (Exception e) {
- log.error(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e);
- }
- }
- );
- }
- }
- if (!allBlank) {
- dataList.add(t);
- } else {
- log.warn(String.format("row:%s is blank ignore!", i));
- }
- } catch (Exception e) {
- log.error(String.format("parse row:%s exception!", i), e);
- }
- }
- }
- }
- } catch (Exception e) {
- log.error(String.format("parse excel exception!"), e);
- } finally {
- if (workbook != null) {
- try {
- workbook.close();
- } catch (Exception e) {
- log.error(String.format("parse excel exception!"), e);
- }
- }
- }
- return dataList;
- }
-
- private static <T> void handleField(T t, String value, Field field) throws Exception {
- Class<?> type = field.getType();
- if (type == null || type == void.class || StringUtils.isBlank(value)) {
- return;
- }
- if (type == Object.class) {
- field.set(t, value);
- //鏁板瓧绫诲瀷
- } else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
- if (type == int.class || type == Integer.class) {
- field.set(t, NumberUtils.toInt(value));
- } else if (type == long.class || type == Long.class) {
- field.set(t, NumberUtils.toLong(value));
- } else if (type == byte.class || type == Byte.class) {
- field.set(t, NumberUtils.toByte(value));
- } else if (type == short.class || type == Short.class) {
- field.set(t, NumberUtils.toShort(value));
- } else if (type == double.class || type == Double.class) {
- field.set(t, NumberUtils.toDouble(value));
- } else if (type == float.class || type == Float.class) {
- field.set(t, NumberUtils.toFloat(value));
- } else if (type == char.class || type == Character.class) {
- field.set(t, CharUtils.toChar(value));
- } else if (type == boolean.class) {
- field.set(t, BooleanUtils.toBoolean(value));
- } else if (type == BigDecimal.class) {
- field.set(t, new BigDecimal(value));
- }
- } else if (type == Boolean.class) {
- field.set(t, BooleanUtils.toBoolean(value));
- } else if (type == Date.class) {
- field.set(t, value);
- } else if (type == String.class) {
- field.set(t, value);
- } else {
- Constructor<?> constructor = type.getConstructor(String.class);
- field.set(t, constructor.newInstance(value));
- }
- }
-
- private static String getCellValue(Cell cell) {
- if (cell == null) {
- return "";
- }else{
- return StringUtils.trimToEmpty(cell.getStringCellValue());
- }
- /* if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
- if (HSSFDateUtil.isCellDateFormatted(cell)) {
- return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
- } else {
- return new BigDecimal(cell.getNumericCellValue()).toString();
- }
- } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
- return StringUtils.trimToEmpty(cell.getStringCellValue());
- } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
- return StringUtils.trimToEmpty(cell.getCellFormula());
- } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
- return "";
- } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
- return String.valueOf(cell.getBooleanCellValue());
- } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
- return "ERROR";
- } else {
- return cell.toString().trim();
- }*/
- }
-
- /* public static <T> void writeExcel(HttpServletResponse response, List<T> dataList, Class<T> cls, String fileName) {
- Field[] fields = cls.getDeclaredFields();
- List<Field> fieldList = Arrays.stream(fields)
- .filter(field -> {
- ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
- if (annotation != null && annotation.col() > 0) {
- field.setAccessible(true);
- return true;
- }
- return false;
- }).sorted(Comparator.comparing(field -> {
- int col = 0;
- ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
- if (annotation != null) {
- col = annotation.col();
- }
- return col;
- })).collect(Collectors.toList());
-
- Workbook wb = new XSSFWorkbook();
- Sheet sheet = wb.createSheet("Sheet1");
- AtomicInteger ai = new AtomicInteger();
- {
- Row row = sheet.createRow(ai.getAndIncrement());
- AtomicInteger aj = new AtomicInteger();
- //鍐欏叆澶撮儴
- fieldList.forEach(field -> {
- ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
- String columnName = "";
- if (annotation != null) {
- columnName = annotation.value();
- }
- Cell cell = row.createCell(aj.getAndIncrement());
- CellStyle cellStyle = wb.createCellStyle();
- cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
- Font font = wb.createFont();
- cellStyle.setFont(font);
- cell.setCellStyle(cellStyle);
- cell.setCellValue(columnName);
- });
- }
- if (CollectionUtils.isNotEmpty(dataList)) {
- dataList.forEach(t -> {
- Row row1 = sheet.createRow(ai.getAndIncrement());
- AtomicInteger aj = new AtomicInteger();
- fieldList.forEach(field -> {
- Class<?> type = field.getType();
- Object value = "";
- try {
- value = field.get(t);
- } catch (Exception e) {
- e.printStackTrace();
- }
- Cell cell = row1.createCell(aj.getAndIncrement());
- if (value != null) {
- if (type == Date.class) {
- cell.setCellValue(value.toString());
- } else {
- cell.setCellValue(value.toString());
- }
- cell.setCellValue(value.toString());
- }
- });
- });
- }
- //鍐荤粨绐楁牸
- wb.getSheet("Sheet1").createFreezePane(0, 1, 0, 1);
- //娴忚鍣ㄤ笅杞絜xcel
- buildExcelDocument(fileName, wb, response);
- }*/
-
- /**
- * 娴忚鍣ㄤ笅杞絜xcel
- *
- * @param fileName
- * @param wb
- * @param response
- */
-/*
- private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {
- try {
- response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
- response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
- response.flushBuffer();
- wb.write(response.getOutputStream());
- } catch (IOException e) {
- e.printStackTrace();
- }
- }*/
-
- /**
- * 鐢熸垚excel鏂囦欢
- *
- * @param path 鐢熸垚excel璺緞
- * @param wb
- */
- /* private static void buildExcelFile(String path, Workbook wb) {
- File file = new File(path);
- if (file.exists()) {
- file.delete();
- }
- try {
- wb.write(new FileOutputStream(file));
- } catch (Exception e) {
- e.printStackTrace();
- }
- }*/
}
--
Gitblit v1.9.3