Tool class: ExcelUtil.java
package com.hand.wms.infra.utils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* @author: jinpeng.zhang
* @date: 2023/05/15
* @description: export custom implementation
*/
@Component
public class ExcelUtil {
public static <T> void exportToExcel(List<T> dataList, List<Section<T>> sections, String fileName, HttpServletResponse response) {
try (Workbook workbook = new XSSFWorkbook()) {
Sheet sheet = workbook. createSheet("Sheet1");
// set the headline style
CellStyle titleStyle = workbook. createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setBorderTop(BorderStyle.THIN);
titleStyle.setBorderBottom(BorderStyle.THIN);
titleStyle.setBorderLeft(BorderStyle.THIN);
titleStyle.setBorderRight(BorderStyle.THIN);
Font titleFont = workbook. createFont();
titleFont. setBold(true);
titleFont. setFontHeightInPoints((short) 14);
titleStyle. setFont(titleFont);
// set subtitle style
CellStyle subtitleStyle = workbook. createCellStyle();
subtitleStyle.setAlignment(HorizontalAlignment.CENTER);
subtitleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
subtitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
subtitleStyle.setBorderTop(BorderStyle.THIN);
subtitleStyle.setBorderBottom(BorderStyle.THIN);
subtitleStyle.setBorderLeft(BorderStyle.THIN);
subtitleStyle.setBorderRight(BorderStyle.THIN);
Font subtitleFont = workbook. createFont();
subtitleFont. setBold(true);
subtitleStyle. setFont(subtitleFont);
int rowIndex = 0;
int columnIndex = 0;
int dataIndex = 2;
// create the header row
Row titleRow = sheet. createRow(rowIndex);
// Create subtitle row
Row headerRow = sheet. createRow(rowIndex + 1);
// Export all major and minor titles
for (Section<T> section : sections) {
String sectionTitle = section. getTitle();
Map<String, String> columnMappings = section. getColumnMappings();
Cell titleCell = titleRow. createCell(columnIndex);
titleCell.setCellValue(sectionTitle);
titleCell.setCellStyle(titleStyle);
if (section. getColumnMappings(). size() > 1) {
sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, columnIndex, columnIndex + columnMappings. size() - 1));
}
for (String columnName : columnMappings. keySet()) {
String columnTitle = columnMappings. get(columnName);
Cell headerCell = headerRow.createCell(columnIndex);
headerCell.setCellValue(columnTitle);
headerCell.setCellStyle(subtitleStyle);
columnIndex++;
}
}
// export data
Row dataRow = sheet. createRow(dataIndex);
for (T data : dataList) {
columnIndex = 0;
for (Section<T> section : sections) {
Map<String, String> columnMappings = section. getColumnMappings();
for (String fieldName : columnMappings. keySet()) {
Cell dataCell = dataRow.createCell(columnIndex++);
setCellValue(dataCell, getValue(data, fieldName));
}
}
dataRow = sheet. createRow( + + dataIndex);
}
// Set column width adaptive
for (int i = 0; i < columnIndex; i ++ ) {
// Adjust the width of each column
sheet. autoSizeColumn((short) i);
// Solve the problem of automatically setting the column width Chinese invalid
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
}
// write Excel content to the output stream
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
String encodedFileName = URLEncoder. encode(fileName + ".xlsx", "UTF-8");
response.setHeader("Content-Disposition", "attachment; filename="" + encodedFileName + """);
try (OutputStream outputStream = response. getOutputStream()) {
workbook.write(outputStream);
}
} catch (IOException e) {
e.printStackTrace();
}
}
private static <T> Object getValue(T data, String fieldName) {
try {
Field field = data.getClass().getDeclaredField(fieldName);
field.setAccessible(true);
return field. get(data);
} catch (NoSuchFieldException | IllegalAccessException e) {
e.printStackTrace();
}
return null;
}
private static void setCellValue(Cell cell, Object value) {
if (value == null) {
cell.setCellValue("");
} else if (value instanceof String) {
cell.setCellValue((String) value);
} else if (value instanceof Integer || value instanceof Long || value instanceof Short) {
cell.setCellValue(Long.parseLong(value.toString()));
} else if (value instanceof Double || value instanceof Float) {
cell.setCellValue(Double.parseDouble(value.toString()));
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else if (value instanceof BigDecimal) {
cell.setCellValue(((BigDecimal) value).doubleValue());
} else if (value instanceof java.util.Date) {
cell.setCellValue((java.util.Date) value);
CellStyle dateCellStyle = cell.getSheet().getWorkbook().createCellStyle();
CreationHelper createHelper = cell. getSheet(). getWorkbook(). getCreationHelper();
dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
cell.setCellStyle(dateCellStyle);
} else {
cell.setCellValue(value.toString());
}
}
public static class Section<T> {
private String title;
private Map<String, String> columnMappings;
public Section(String title, Map<String, String> columnMappings) {
this.title = title;
this.columnMappings = columnMappings;
}
public String getTitle() {
return title;
}
public Map<String, String> getColumnMappings() {
return columnMappings;
}
}
/**
* Create a mapping of major titles and subtitles
* @param values
* @return
*/
public Map<String, String> createSectionMap(String... values) {
if (values. length % 2 != 0) {
throw new IllegalArgumentException("Invalid number of arguments");
}
Map<String, String> map = new HashMap<>();
for (int i = 0; i < values. length; i + = 2) {
map.put(values[i], values[i + 1]);
}
return map;
}
/**
* Add key-value pairs in order
* @param keyValues
* @return
* @param <K>
* @param <V>
*/
public static <K, V> LinkedHashMap<K, V> createSectionLinkedMap(Object... keyValues) {
if (keyValues. length % 2 != 0) {
throw new IllegalArgumentException("Invalid number of arguments. Key-value pairs are expected.");
}
LinkedHashMap<K, V> map = new LinkedHashMap<>();
for (int i = 0; i < keyValues. length; i + = 2) {
K key = (K) keyValues[i];
V value = (V) keyValues[i + 1];
map. put(key, value);
}
return map;
}
}
Example:
public List<MtPoLineResVO> exportForUiNew(Long tenantId, MtPoLineResDTO dto, HttpServletResponse response){
List<MtPoLineResVO> dataList = queryList(tenantId, dto);
List<ExcelUtil. Section<MtPoLineResVO>> sections = new ArrayList<>();
sections.add(new ExcelUtil.Section<>("Purchase Order", ExcelUtil.createSectionLinkedMap(
"poNumber", "Purchase Order Number",
"lineNum", "Purchase order line number",
"demandDate", "demand date",
"quantityOrdered", "Purchase Quantity",
"supplierName", "supplier",
"buyerCode", "Buyer",
"poUomCode", "Purchasing Unit",
"poQty", "Pricing Quantity",
"poUom", "Pricing unit")));
sections.add(new ExcelUtil.Section<>("material", ExcelUtil.createSectionLinkedMap(
"materialCode", "material code",
"materialName", "Material Description",
"model", "Specification model",
"uomCode", "unit")));
sections.add(new ExcelUtil.Section<>("External Cooperation Issue", ExcelUtil.createSectionLinkedMap(
"componentMaterialCode", "Component Material",
"componentMaterialName", "component material name",
"componentModel", "Specification Model",
"createQty", "Create order quantity",
"materialQty", "Material Qty",
"materialLastUpdateDate", "last material delivery time",
"materialLastUpdateByName", "Last Material Issuer")));
sections.add(new ExcelUtil.Section<>("Supplier Delivery Note Response Delivery Date", ExcelUtil.createSectionLinkedMap(
"expectedArrivalTime", "expected delivery date",
"creationDate", "Creation Date",
"quantity", "delivery quantity")));
sections.add(new ExcelUtil.Section<>("The warehouse has received", ExcelUtil.createSectionLinkedMap(
"lastUpdateDate", "Received Date",
"actualQty", "Received Quantity",
"lastUpdateByName", "Recipient")));
sections.add(new ExcelUtil.Section<>("Incoming inspection", ExcelUtil.createSectionLinkedMap(
"okQty", "Number of good products",
"noQty", "Number of defective products",
"iqcCheckQty", "Quantity to be checked",
"iqcBackQty", "Check and return quantity",
"iqcLastUpdateDate", "Last inspection time",
"iqcLastUpdateByName", "Inspector")));
sections.add(new ExcelUtil.Section<>("Storage Quantity", ExcelUtil.createSectionLinkedMap(
"wareHouse", "warehouse",
"okQuantity", "Quantity",
"locatorName", "Location")));
sections.add(new ExcelUtil.Section<>("Note", ExcelUtil.createSectionLinkedMap(
"lineDescription", "Remarks")));
String fileName = "Outsourcing order progress overview report";
// output file path
ExcelUtil. exportToExcel(dataList, sections, fileName, response);
return dataList;
}