package com.vincent.rsf.server.common.utils;
|
|
import cn.afterturn.easypoi.excel.annotation.Excel;
|
import cn.afterturn.easypoi.excel.entity.ImportParams;
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.vincent.rsf.framework.common.Cools;
|
import com.vincent.rsf.framework.common.SpringUtils;
|
import com.vincent.rsf.server.manager.entity.excel.MatnrsTemplate;
|
import com.vincent.rsf.server.manager.entity.excel.annotation.ExcelComment;
|
import com.vincent.rsf.server.system.entity.Fields;
|
import com.vincent.rsf.server.system.service.FieldsService;
|
import io.swagger.annotations.ApiModelProperty;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
|
import jakarta.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.lang.reflect.Field;
|
import java.lang.reflect.Method;
|
import java.lang.reflect.Modifier;
|
import java.net.URLEncoder;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
|
/**
|
* Created by vincent on 2/17/2024
|
*/
|
@Slf4j
|
public class ExcelUtil {
|
public static void build(Workbook workbook, HttpServletResponse response) {
|
response.reset();
|
Http.cors(response);
|
response.setContentType("application/octet-stream; charset=utf-8");
|
try {
|
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("export", "UTF-8"));
|
workbook.write(response.getOutputStream());
|
} catch (IOException ignore) {
|
}
|
}
|
|
public static <T> Workbook create(List<T> list, Class<T> clz) {
|
return create(list, clz, false);
|
}
|
|
public static <T> Workbook create(List<T> list, Class<T> clz, boolean flagTemplate) {
|
XSSFWorkbook workbook = new XSSFWorkbook();
|
Sheet sheet = workbook.createSheet(clz.getSimpleName());
|
|
Row header = sheet.createRow(0);
|
Field[] fields = Cools.getAllFields(clz);
|
int headerIdx = 0;
|
FieldsService itemService = SpringUtils.getBean(FieldsService.class);
|
List<Fields> sysFields = itemService.list(new LambdaQueryWrapper<Fields>()
|
.eq(Fields::getStatus, 1)
|
.eq(Fields::getFlagEnable, 1));
|
|
|
for (Field field : fields) {
|
if (Modifier.isFinal(field.getModifiers())
|
|| Modifier.isStatic(field.getModifiers())
|
|| Modifier.isTransient(field.getModifiers())) {
|
continue;
|
}
|
String memo = "Undefined";
|
if (flagTemplate) {
|
memo = field.getName();
|
} else {
|
if (field.isAnnotationPresent(Excel.class)) {
|
memo = field.getAnnotation(Excel.class).name();
|
}
|
}
|
if (field.isAnnotationPresent(ApiModelProperty.class)) {
|
memo = field.getAnnotation(ApiModelProperty.class).value();
|
}
|
header.createCell(headerIdx).setCellValue(memo);
|
headerIdx++;
|
}
|
if (clz.equals(MatnrsTemplate.class)) {
|
//添加扩展字段别名
|
if (!sysFields.isEmpty()) {
|
for (Fields field : sysFields) {
|
if (flagTemplate) {
|
header.createCell(headerIdx).setCellValue(field.getFields());
|
} else {
|
header.createCell(headerIdx).setCellValue(field.getFieldsAlise());
|
}
|
headerIdx++;
|
}
|
}
|
}
|
|
int rowIndex = 1;
|
if (!Objects.isNull(list)) {
|
for (T t : list) {
|
Row row = sheet.createRow(rowIndex++);
|
|
int cellIndex = 0;
|
for (Field field : fields) {
|
if (Modifier.isFinal(field.getModifiers())
|
|| Modifier.isStatic(field.getModifiers())
|
|| Modifier.isTransient(field.getModifiers())) {
|
continue;
|
}
|
|
// 此行很重要，特别是字段为private时
|
field.setAccessible(true);
|
Object value = null;
|
try {
|
value = field.get(t);
|
} catch (IllegalAccessException e) {
|
e.printStackTrace();
|
}
|
if (value != null) {
|
if (value instanceof Date) {
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
row.createCell(cellIndex).setCellValue(sdf.format((Date) value));
|
} else {
|
row.createCell(cellIndex).setCellValue(value.toString());
|
}
|
}
|
cellIndex++;
|
}
|
}
|
}
|
for (int i = 0; i <= fields.length; i++) {
|
sheet.autoSizeColumn(i);
|
}
|
|
return workbook;
|
}
|
|
public static Workbook create(List<?> list, List<ColumnMeta> columns) {
|
return create(list, columns, null);
|
}
|
|
public static Workbook create(List<?> list, List<ColumnMeta> columns, ReportMeta reportMeta) {
|
XSSFWorkbook workbook = new XSSFWorkbook();
|
Sheet sheet = workbook.createSheet("export");
|
List<ColumnMeta> safeColumns = columns == null ? Collections.emptyList() : columns;
|
int sheetColumnCount = safeColumns.size() + 1;
|
configureA4PrintLayout(sheet);
|
|
CellStyle titleStyle = createTitleStyle(workbook);
|
CellStyle metaLabelStyle = createMetaLabelStyle(workbook);
|
CellStyle metaValueStyle = createMetaValueStyle(workbook);
|
CellStyle headerStyle = createHeaderStyle(workbook);
|
CellStyle bodyStyle = createBodyStyle(workbook);
|
CellStyle serialStyle = createCenteredBodyStyle(workbook);
|
|
int rowIndex = 0;
|
if (reportMeta != null) {
|
Row titleRow = sheet.createRow(rowIndex++);
|
titleRow.setHeightInPoints(28);
|
Cell titleCell = titleRow.createCell(0);
|
titleCell.setCellValue(StringUtils.defaultIfBlank(reportMeta.getTitle(), "报表"));
|
titleCell.setCellStyle(titleStyle);
|
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, Math.max(0, sheetColumnCount - 1)));
|
|
Row metaRow = sheet.createRow(rowIndex++);
|
int metaCol = 0;
|
metaCol = writeMetaPair(metaRow, metaCol, "报表日期", reportMeta.getReportDate(), metaLabelStyle, metaValueStyle);
|
writeMetaPair(metaRow, metaCol, "打印人", reportMeta.getPrintedBy(), metaLabelStyle, metaValueStyle);
|
|
rowIndex++;
|
}
|
|
int headerRowIndex = rowIndex;
|
Row header = sheet.createRow(rowIndex++);
|
Cell serialHeaderCell = header.createCell(0);
|
serialHeaderCell.setCellValue("序号");
|
serialHeaderCell.setCellStyle(headerStyle);
|
|
for (int i = 0; i < safeColumns.size(); i++) {
|
ColumnMeta column = safeColumns.get(i);
|
Cell headerCell = header.createCell(i + 1);
|
headerCell.setCellValue(
|
StringUtils.isBlank(column.getLabel()) ? column.getSource() : column.getLabel()
|
);
|
headerCell.setCellStyle(headerStyle);
|
}
|
|
if (list != null) {
|
int serialNo = 1;
|
for (Object rowObj : list) {
|
Row row = sheet.createRow(rowIndex++);
|
Cell serialCell = row.createCell(0);
|
serialCell.setCellValue(String.format("%03d", serialNo++));
|
serialCell.setCellStyle(serialStyle);
|
for (int i = 0; i < safeColumns.size(); i++) {
|
Object value = getColumnValue(rowObj, safeColumns.get(i).getSource());
|
Cell cell = row.createCell(i + 1);
|
cell.setCellStyle(bodyStyle);
|
if (value != null) {
|
if (value instanceof Date) {
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
cell.setCellValue(sdf.format((Date) value));
|
} else {
|
cell.setCellValue(value.toString());
|
}
|
}
|
}
|
}
|
}
|
|
for (int i = 0; i <= safeColumns.size(); i++) {
|
sheet.autoSizeColumn(i);
|
sheet.setColumnWidth(i, Math.min(sheet.getColumnWidth(i) + 1024, 12000));
|
}
|
|
sheet.setRepeatingRows(CellRangeAddress.valueOf((headerRowIndex + 1) + ":" + (headerRowIndex + 1)));
|
|
return workbook;
|
}
|
|
private static void configureA4PrintLayout(Sheet sheet) {
|
sheet.setAutobreaks(true);
|
sheet.setFitToPage(true);
|
sheet.setHorizontallyCenter(true);
|
sheet.setDisplayGridlines(false);
|
|
PrintSetup printSetup = sheet.getPrintSetup();
|
printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
|
printSetup.setLandscape(true);
|
printSetup.setFitWidth((short) 1);
|
printSetup.setFitHeight((short) 0);
|
|
sheet.setMargin(Sheet.LeftMargin, 0.3);
|
sheet.setMargin(Sheet.RightMargin, 0.3);
|
sheet.setMargin(Sheet.TopMargin, 0.4);
|
sheet.setMargin(Sheet.BottomMargin, 0.4);
|
}
|
|
private static int writeMetaPair(Row row, int startCol, String label, String value, CellStyle labelStyle, CellStyle valueStyle) {
|
Cell labelCell = row.createCell(startCol);
|
labelCell.setCellValue(label + "：");
|
labelCell.setCellStyle(labelStyle);
|
|
Cell valueCell = row.createCell(startCol + 1);
|
valueCell.setCellValue(StringUtils.defaultString(value));
|
valueCell.setCellStyle(valueStyle);
|
return startCol + 2;
|
}
|
|
private static CellStyle createTitleStyle(Workbook workbook) {
|
CellStyle style = workbook.createCellStyle();
|
style.setAlignment(HorizontalAlignment.CENTER);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
style.setBorderBottom(BorderStyle.THICK);
|
Font font = workbook.createFont();
|
font.setBold(true);
|
font.setFontHeightInPoints((short) 16);
|
style.setFont(font);
|
return style;
|
}
|
|
private static CellStyle createMetaLabelStyle(Workbook workbook) {
|
CellStyle style = workbook.createCellStyle();
|
style.setAlignment(HorizontalAlignment.LEFT);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
style.setBorderBottom(BorderStyle.THIN);
|
Font font = workbook.createFont();
|
font.setBold(true);
|
style.setFont(font);
|
return style;
|
}
|
|
private static CellStyle createMetaValueStyle(Workbook workbook) {
|
CellStyle style = workbook.createCellStyle();
|
style.setAlignment(HorizontalAlignment.LEFT);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
style.setBorderBottom(BorderStyle.THIN);
|
return style;
|
}
|
|
private static CellStyle createHeaderStyle(Workbook workbook) {
|
CellStyle style = workbook.createCellStyle();
|
style.setAlignment(HorizontalAlignment.CENTER);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
|
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
|
Font font = workbook.createFont();
|
font.setBold(true);
|
style.setFont(font);
|
return style;
|
}
|
|
private static CellStyle createBodyStyle(Workbook workbook) {
|
CellStyle style = workbook.createCellStyle();
|
style.setAlignment(HorizontalAlignment.LEFT);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
return style;
|
}
|
|
private static CellStyle createCenteredBodyStyle(Workbook workbook) {
|
CellStyle style = workbook.createCellStyle();
|
style.setAlignment(HorizontalAlignment.CENTER);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
return style;
|
}
|
|
private static Object getColumnValue(Object rowObj, String source) {
|
if (rowObj == null || StringUtils.isBlank(source)) {
|
return null;
|
}
|
|
if (rowObj instanceof Map) {
|
return getValueFromMap((Map<?, ?>) rowObj, source);
|
}
|
|
String extendFieldKey = extractExtendFieldKey(source);
|
if (extendFieldKey != null) {
|
Object extendFields = getBeanValue(rowObj, "extendFields");
|
if (extendFields instanceof Map) {
|
return ((Map<?, ?>) extendFields).get(extendFieldKey);
|
}
|
return null;
|
}
|
|
return getBeanValue(rowObj, source);
|
}
|
|
private static Object getValueFromMap(Map<?, ?> rowObj, String source) {
|
String extendFieldKey = extractExtendFieldKey(source);
|
if (extendFieldKey != null) {
|
Object extendFields = rowObj.get("extendFields");
|
if (extendFields instanceof Map) {
|
return ((Map<?, ?>) extendFields).get(extendFieldKey);
|
}
|
return null;
|
}
|
return rowObj.get(source);
|
}
|
|
private static String extractExtendFieldKey(String source) {
|
if (source == null || !source.startsWith("extendFields.[")) {
|
return null;
|
}
|
int startIndex = source.indexOf('[');
|
int endIndex = source.indexOf(']');
|
if (startIndex < 0 || endIndex <= startIndex) {
|
return null;
|
}
|
return source.substring(startIndex + 1, endIndex);
|
}
|
|
private static Object getBeanValue(Object rowObj, String source) {
|
Object value = invokeGetter(rowObj, source);
|
if (value != null) {
|
return value;
|
}
|
Field field = findField(rowObj.getClass(), source);
|
if (field == null) {
|
return null;
|
}
|
try {
|
field.setAccessible(true);
|
return field.get(rowObj);
|
} catch (IllegalAccessException ignore) {
|
return null;
|
}
|
}
|
|
private static Object invokeGetter(Object target, String source) {
|
String suffix = Character.toUpperCase(source.charAt(0)) + source.substring(1);
|
String[] methodNames = new String[] { "get" + suffix, "is" + suffix };
|
for (String methodName : methodNames) {
|
try {
|
Method method = target.getClass().getMethod(methodName);
|
return method.invoke(target);
|
} catch (Exception ignore) {
|
}
|
}
|
return null;
|
}
|
|
private static Field findField(Class<?> clazz, String source) {
|
Class<?> current = clazz;
|
while (current != null && current != Object.class) {
|
try {
|
return current.getDeclaredField(source);
|
} catch (NoSuchFieldException ignore) {
|
current = current.getSuperclass();
|
}
|
}
|
return null;
|
}
|
|
/**
|
* 添加导入excel配置参数
|
* 注：默认配置可满足当前需求
|
*
|
* @return
|
*/
|
public static ImportParams getDefaultImportParams() {
|
ImportParams importParams = new ImportParams();
|
importParams.setTitleRows(0);
|
// importParams.setNeedVerify(true);
|
importParams.setHeadRows(1);
|
importParams.setSheetNum(1);
|
return importParams;
|
}
|
|
/**
|
* 根据 {@code tClass} 相关成员变量的 {@link ExcelComment#example()} 字段创建模拟数据，暂不支持 复杂类型
|
*
|
* @param tClass
|
* @return
|
*/
|
public static <T> T mockData(Class<T> tClass) {
|
if (tClass == null) {
|
return null;
|
}
|
T instance = null;
|
|
try {
|
instance = tClass.newInstance();
|
Field[] declaredFields = tClass.getDeclaredFields();
|
for (Field declaredField : declaredFields) {
|
ExcelComment comment = declaredField.getAnnotation(ExcelComment.class);
|
if (comment == null) {
|
continue;
|
}
|
declaredField.setAccessible(true);
|
Class<?> fieldType = declaredField.getType();
|
|
String exampleValue = comment.example();
|
Object value = null;
|
|
if (fieldType == int.class || fieldType == Integer.class) {
|
value = StringUtils.isBlank(exampleValue) ? 0 : Integer.parseInt(exampleValue);
|
} else if (fieldType == short.class || fieldType == Short.class) {
|
value = StringUtils.isBlank(exampleValue) ? 0 : Short.parseShort(exampleValue);
|
} else if (fieldType == long.class || fieldType == Long.class) {
|
value = StringUtils.isBlank(exampleValue) ? 0 : Long.parseLong(exampleValue);
|
} else if (fieldType == double.class || fieldType == Double.class) {
|
value = StringUtils.isBlank(exampleValue) ? 0 : Double.parseDouble(exampleValue);
|
} else if (fieldType == boolean.class || fieldType == Boolean.class) {
|
value = StringUtils.isNotBlank(exampleValue) && Boolean.parseBoolean(exampleValue);
|
} else if (fieldType == String.class) {
|
value = exampleValue;
|
} else if (fieldType == Date.class) {
|
value = DateUtils.parse(exampleValue);
|
}
|
|
if (value == null && !isBaseType(fieldType)) {
|
declaredField.set(instance, null);
|
} else {
|
declaredField.set(instance, value);
|
}
|
}
|
} catch (Exception e) {
|
log.error("数据构造失败，请查询详细信息", e);
|
return instance;
|
}
|
|
return instance;
|
}
|
|
|
/**
|
* 是否是基础数据类型
|
*
|
* @param className
|
* @return
|
*/
|
private static boolean isBaseType(Class<?> className) {
|
if (className.equals(java.lang.Integer.class) ||
|
className.equals(java.lang.Byte.class) ||
|
className.equals(java.lang.Long.class) ||
|
className.equals(java.lang.Double.class) ||
|
className.equals(java.lang.Float.class) ||
|
className.equals(java.lang.Character.class) ||
|
className.equals(java.lang.Short.class) ||
|
className.equals(java.lang.Boolean.class)) {
|
return true;
|
}
|
return false;
|
}
|
|
public static class ColumnMeta {
|
private String key;
|
private String source;
|
private String label;
|
private Boolean extendField;
|
|
public String getKey() {
|
return key;
|
}
|
|
public ColumnMeta setKey(String key) {
|
this.key = key;
|
return this;
|
}
|
|
public String getSource() {
|
return source;
|
}
|
|
public ColumnMeta setSource(String source) {
|
this.source = source;
|
return this;
|
}
|
|
public String getLabel() {
|
return label;
|
}
|
|
public ColumnMeta setLabel(String label) {
|
this.label = label;
|
return this;
|
}
|
|
public Boolean getExtendField() {
|
return extendField;
|
}
|
|
public ColumnMeta setExtendField(Boolean extendField) {
|
this.extendField = extendField;
|
return this;
|
}
|
}
|
|
public static class ReportMeta {
|
private String title;
|
private String companyName;
|
private String printedBy;
|
private String reportDate;
|
private String reportDateValue;
|
|
public String getTitle() {
|
return title;
|
}
|
|
public ReportMeta setTitle(String title) {
|
this.title = title;
|
return this;
|
}
|
|
public String getCompanyName() {
|
return companyName;
|
}
|
|
public ReportMeta setCompanyName(String companyName) {
|
this.companyName = companyName;
|
return this;
|
}
|
|
public String getReportDate() {
|
return reportDate;
|
}
|
|
public ReportMeta setReportDate(String reportDate) {
|
this.reportDate = reportDate;
|
return this;
|
}
|
|
public String getReportDateValue() {
|
return reportDateValue;
|
}
|
|
public ReportMeta setReportDateValue(String reportDateValue) {
|
this.reportDateValue = reportDateValue;
|
return this;
|
}
|
|
public String getPrintedBy() {
|
return printedBy;
|
}
|
|
public ReportMeta setPrintedBy(String printedBy) {
|
this.printedBy = printedBy;
|
return this;
|
}
|
}
|
|
|
}
|
