#打印+导出

zhou zhou

16 小时以前 44ac1c9a2a6ee6ac9f618f4a63510f8f94d1b1a9

 rsf-server/src/main/java/com/vincent/rsf/server/common/utils/ExcelUtil.java

@@ -20,17 +20,20 @@
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.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by vincent on 2/17/2024
 */
@Slf4j
public class ExcelUtil {
    private static final Pattern EXTEND_FIELD_SOURCE_PATTERN = Pattern.compile("^extendFields\\.\\[(.+)]$");

    public static void build(Workbook workbook, HttpServletResponse response) {
        response.reset();
        Http.cors(response);
@@ -114,14 +117,7 @@
                    } 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());
                        }
                    }
                    writeCellValue(row, cellIndex, value);
                    cellIndex++;
                }
            }
@@ -133,260 +129,206 @@
        return workbook;
    }

    public static Workbook create(List<?> list, List<ColumnMeta> columns) {
        return create(list, columns, null);
    public static Workbook create(List<Map<String, Object>> rows, List<ExportColumn> columns) {
        return create(rows, columns, null);
    }

    public static Workbook create(List<?> list, List<ColumnMeta> columns, ReportMeta reportMeta) {
    public static Workbook create(List<Map<String, Object>> rows, List<ExportColumn> columns, ExportMeta exportMeta) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("export");
        List<ColumnMeta> safeColumns = columns == null ? Collections.emptyList() : columns;
        int sheetColumnCount = safeColumns.size() + 1;
        configureA4PrintLayout(sheet);
        int titleColumnCount = Math.max(columns.size(), 4);
        int currentRowIndex = 0;

        CellStyle titleStyle = createTitleStyle(workbook);
        CellStyle metaLabelStyle = createMetaLabelStyle(workbook);
        CellStyle metaValueStyle = createMetaValueStyle(workbook);
        CellStyle subHeaderStyle = createSubHeaderStyle(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);
        if (exportMeta != null && StringUtils.isNotBlank(exportMeta.getReportTitle())) {
            Row titleRow = sheet.createRow(currentRowIndex++);
            titleRow.setHeightInPoints(24);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellValue(StringUtils.defaultIfBlank(reportMeta.getTitle(), "报表"));
            titleCell.setCellValue(exportMeta.getReportTitle());
            titleCell.setCellStyle(titleStyle);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, Math.max(0, sheetColumnCount - 1)));
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleColumnCount - 1));

            Row metaRow = sheet.createRow(rowIndex++);
            int metaCol = 0;
            metaCol = writeMetaPair(metaRow, metaCol, "报表日期", reportMeta.getReportDate(), metaLabelStyle, metaValueStyle);
            writeMetaPair(metaRow, metaCol, "打印人", reportMeta.getPrintedBy(), metaLabelStyle, metaValueStyle);
            Row subHeaderRow = sheet.createRow(currentRowIndex++);
            writeSubHeaderCell(subHeaderRow, 0, getSubHeaderText("报表日期", exportMeta.getReportDate()), subHeaderStyle);
            writeSubHeaderCell(subHeaderRow, 1, getSubHeaderText("打印人", exportMeta.getOperator()), subHeaderStyle);
            writeSubHeaderCell(subHeaderRow, 2, getSubHeaderText("打印时间", exportMeta.getPrintedAt()), subHeaderStyle);
            writeSubHeaderCell(subHeaderRow, 3, getSubHeaderText("记录数", String.valueOf(exportMeta.getCount())), subHeaderStyle);

            rowIndex++;
            currentRowIndex++;
        }

        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()
            );
        Row header = sheet.createRow(currentRowIndex++);
        for (int index = 0; index < columns.size(); index++) {
            Cell headerCell = header.createCell(index);
            headerCell.setCellValue(columns.get(index).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());
                        }
                    }
                }
        int rowIndex = currentRowIndex;
        for (Map<String, Object> rowData : rows) {
            Row row = sheet.createRow(rowIndex++);
            for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
                Object value = getRowValue(rowData, columns.get(columnIndex).getSource());
                writeCellValue(row, columnIndex, value, bodyStyle);
            }
        }

        for (int i = 0; i <= safeColumns.size(); i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, Math.min(sheet.getColumnWidth(i) + 1024, 12000));
        for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
            sheet.autoSizeColumn(columnIndex);
        }

        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);
    private static Object getRowValue(Map<String, Object> rowData, String source) {
        if (rowData == null || StringUtils.isBlank(source)) {
            return null;
        }

        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
        printSetup.setLandscape(true);
        printSetup.setFitWidth((short) 1);
        printSetup.setFitHeight((short) 0);
        if (rowData.containsKey(source)) {
            return rowData.get(source);
        }

        sheet.setMargin(Sheet.LeftMargin, 0.3);
        sheet.setMargin(Sheet.RightMargin, 0.3);
        sheet.setMargin(Sheet.TopMargin, 0.4);
        sheet.setMargin(Sheet.BottomMargin, 0.4);
        Matcher matcher = EXTEND_FIELD_SOURCE_PATTERN.matcher(source);
        if (matcher.matches()) {
            Object extendFields = rowData.get("extendFields");
            if (extendFields instanceof Map<?, ?> extendFieldMap) {
                return extendFieldMap.get(matcher.group(1));
            }
        }

        return rowData.get(source);
    }

    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);
    private static void writeCellValue(Row row, int cellIndex, Object value) {
        writeCellValue(row, cellIndex, value, null);
    }

        Cell valueCell = row.createCell(startCol + 1);
        valueCell.setCellValue(StringUtils.defaultString(value));
        valueCell.setCellStyle(valueStyle);
        return startCol + 2;
    private static void writeCellValue(Row row, int cellIndex, Object value, CellStyle cellStyle) {
        Cell cell = row.createCell(cellIndex);
        if (cellStyle != null) {
            cell.setCellStyle(cellStyle);
        }
        if (value == null) {
            return;
        }
        if (value instanceof Date) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            cell.setCellValue(sdf.format((Date) value));
            return;
        }
        cell.setCellValue(value.toString());
    }

    private static void writeSubHeaderCell(Row row, int cellIndex, String value, CellStyle cellStyle) {
        Cell cell = row.createCell(cellIndex);
        cell.setCellValue(value);
        cell.setCellStyle(cellStyle);
    }

    private static String getSubHeaderText(String label, String value) {
        return label + ": " + StringUtils.defaultString(value);
    }

    private static CellStyle createTitleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THICK);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 16);
        style.setFont(font);
        return style;
        font.setFontHeightInPoints((short) 14);
        cellStyle.setFont(font);
        return cellStyle;
    }

    private static CellStyle createMetaLabelStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
    private static CellStyle createSubHeaderStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        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;
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);
        return cellStyle;
    }

    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);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);
        return cellStyle;
    }

    private static CellStyle createBodyStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
        cellStyle.setWrapText(true);
        return cellStyle;
    }

    private static CellStyle createCenteredBodyStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }
    public static class ExportColumn {
        private final String source;
        private final String label;

    private static Object getColumnValue(Object rowObj, String source) {
        if (rowObj == null || StringUtils.isBlank(source)) {
            return null;
        public ExportColumn(String source, String label) {
            this.source = source;
            this.label = label;
        }

        if (rowObj instanceof Map) {
            return getValueFromMap((Map<?, ?>) rowObj, source);
        public String getSource() {
            return 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;
        public String getLabel() {
            return label;
        }
    }

    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;
    }
    public static class ExportMeta {
        private final String reportTitle;
        private final String reportDate;
        private final String printedAt;
        private final String operator;
        private final int count;

    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();
            }
        public ExportMeta(String reportTitle, String reportDate, String printedAt, String operator, int count) {
            this.reportTitle = reportTitle;
            this.reportDate = reportDate;
            this.printedAt = printedAt;
            this.operator = operator;
            this.count = count;
        }
        return null;

        public String getReportTitle() {
            return reportTitle;
        }

        public String getReportDate() {
            return reportDate;
        }

        public String getPrintedAt() {
            return printedAt;
        }

        public String getOperator() {
            return operator;
        }

        public int getCount() {
            return count;
        }
    }

    /**
@@ -479,102 +421,6 @@
            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;
        }
    }