#打印+导出

zhou zhou

昨天 44ac1c9a2a6ee6ac9f618f4a63510f8f94d1b1a9

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

@@ -2,12 +2,9 @@

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.alibaba.excel.EasyExcel;
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.framework.exception.CoolException;
import com.vincent.rsf.server.manager.entity.Matnr;
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;
@@ -16,22 +13,27 @@
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
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);
@@ -48,7 +50,7 @@
    }

    public static <T> Workbook create(List<T> list, Class<T> clz, boolean flagTemplate) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(clz.getSimpleName());

        Row header = sheet.createRow(0);
@@ -115,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++;
                }
            }
@@ -134,6 +129,208 @@
        return workbook;
    }

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

    public static Workbook create(List<Map<String, Object>> rows, List<ExportColumn> columns, ExportMeta exportMeta) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("export");
        int titleColumnCount = Math.max(columns.size(), 4);
        int currentRowIndex = 0;

        CellStyle titleStyle = createTitleStyle(workbook);
        CellStyle subHeaderStyle = createSubHeaderStyle(workbook);
        CellStyle headerStyle = createHeaderStyle(workbook);
        CellStyle bodyStyle = createBodyStyle(workbook);

        if (exportMeta != null && StringUtils.isNotBlank(exportMeta.getReportTitle())) {
            Row titleRow = sheet.createRow(currentRowIndex++);
            titleRow.setHeightInPoints(24);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellValue(exportMeta.getReportTitle());
            titleCell.setCellStyle(titleStyle);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleColumnCount - 1));

            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);

            currentRowIndex++;
        }

        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);
        }

        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 columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
            sheet.autoSizeColumn(columnIndex);
        }

        return workbook;
    }

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

        if (rowData.containsKey(source)) {
            return rowData.get(source);
        }

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

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

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

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

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

    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

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

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

    public static class ExportColumn {
        private final String source;
        private final String label;

        public ExportColumn(String source, String label) {
            this.source = source;
            this.label = label;
        }

        public String getSource() {
            return source;
        }

        public String getLabel() {
            return label;
        }
    }

    public static class ExportMeta {
        private final String reportTitle;
        private final String reportDate;
        private final String printedAt;
        private final String operator;
        private final int count;

        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;
        }

        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;
        }
    }

    /**
     * 添加导入excel配置参数
     * 注：默认配置可满足当前需求
@@ -143,69 +340,10 @@
    public static ImportParams getDefaultImportParams() {
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(0);
//        importParams.setNeedVerify(true);
        importParams.setHeadRows(1);
        importParams.setSheetNum(1);
        return importParams;
    }

    /**
     * @author Ryan
     * @description Excel导出Map格式表格
     * @param
     * @return
     * @time 2025/3/18 09:30
     */
    public static void exportForMap(HttpServletResponse response, Class cls, String fileName, List<List<Object>> data) {
        try {
            response.setContentType("application/octet-stream; charset=utf-8");
            response.setCharacterEncoding("utf-8");
            String name = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx");
            EasyExcel.write(response.getOutputStream()).head(getHeader(cls)).sheet().doWrite(null);
        } catch (IOException exception) {
            exception.printStackTrace();
        }
    }

    /**
     * @author Ryan
     * @description excel导出map模式
     * @param  Class<T> 模板类型
     * @return  List<List<String>> 表头信息
     * @time 2025/3/18 09:26
     */
    public static List<List<String>> getHeader(Class<T> t) {
        List<List<String>> headList = new ArrayList<List<String>>();
        if (t.getSuperclass().isInstance(MatnrsTemplate.class)) {
            Field[] allFields = Cools.getAllFields(t);
            if (Objects.isNull(allFields) ||  allFields.length < 1) {
                throw new CoolException("模板列不能为空！！");
            }
            for (Field field : allFields) {
                List<String> list = new ArrayList<String>();
                String fieldName = "";
                if (field.isAnnotationPresent(Excel.class)){
                    fieldName = field.getAnnotation(Excel.class).name();
                }

                list.add(field.getName());
                headList.add(list);
            }

            FieldsService itemService = SpringUtils.getBean(FieldsService.class);
            List<Fields> sysFields = itemService.list(new LambdaQueryWrapper<Fields>()
                    .eq(Fields::getStatus, 1)
                    .eq(Fields::getFlagEnable, 1));
            //添加扩展字段别名
            if (!sysFields.isEmpty()) {
                sysFields.forEach(fields1 -> {
                    List<String> list = new ArrayList<String>();
                    list.add(fields1.getFieldsAlise());
                    headList.add(list);
                });
            }
        }
        return headList;
    }

    /**
@@ -287,3 +425,4 @@


}