EasyExcel实现动态表头

2023-12-26 16:44:54

?要实现上述动态头,按每日统计,每月统计,每年统计。而时间是一直变化,所以我们需要表头也一直动态生成。

首先,我们需要定义所需要实体类

public class CountDayData {

    @ExcelProperty(value = "业务员姓名")
    private String saleName;
    @ExcelProperty({"${day}", "订单总数"})
    private Integer orderNum;
    @ExcelProperty({"${day}", "销售额"})
    private BigDecimal totalAmount;
    @ExcelProperty({"${day}", "平均单价"})
    private BigDecimal avaAmount;

    @ExcelProperty({"${month}", "订单总数"})
    private Integer orderNum1;
    @ExcelProperty({"${month}", "销售额"})
    private BigDecimal totalAmount1;
    @ExcelProperty({"${month}", "平均单价"})
    private BigDecimal avaAmount1;

    @ExcelProperty({"${year}", "订单总数"})
    private Integer orderNum2;
    @ExcelProperty({"${year}", "销售额"})
    private BigDecimal totalAmount2;
    @ExcelProperty({"${year}", "平均单价"})
    private BigDecimal avaAmount2;

    public String getSaleName() {
        return saleName;
    }

    public void setSaleName(String saleName) {
        this.saleName = saleName;
    }

    public Integer getOrderNum() {
        return orderNum;
    }

    public void setOrderNum(Integer orderNum) {
        this.orderNum = orderNum;
    }

    public BigDecimal getTotalAmount() {
        return totalAmount;
    }

    public void setTotalAmount(BigDecimal totalAmount) {
        this.totalAmount = totalAmount;
    }

    public BigDecimal getAvaAmount() {
        return avaAmount;
    }

    public void setAvaAmount(BigDecimal avaAmount) {
        this.avaAmount = avaAmount;
    }

    public Integer getOrderNum1() {
        return orderNum1;
    }

    public void setOrderNum1(Integer orderNum1) {
        this.orderNum1 = orderNum1;
    }

    public BigDecimal getTotalAmount1() {
        return totalAmount1;
    }

    public void setTotalAmount1(BigDecimal totalAmount1) {
        this.totalAmount1 = totalAmount1;
    }

    public BigDecimal getAvaAmount1() {
        return avaAmount1;
    }

    public void setAvaAmount1(BigDecimal avaAmount1) {
        this.avaAmount1 = avaAmount1;
    }

    public Integer getOrderNum2() {
        return orderNum2;
    }

    public void setOrderNum2(Integer orderNum2) {
        this.orderNum2 = orderNum2;
    }

    public BigDecimal getTotalAmount2() {
        return totalAmount2;
    }

    public void setTotalAmount2(BigDecimal totalAmount2) {
        this.totalAmount2 = totalAmount2;
    }

    public BigDecimal getAvaAmount2() {
        return avaAmount2;
    }

    public void setAvaAmount2(BigDecimal avaAmount2) {
        this.avaAmount2 = avaAmount2;
    }
}

创建 EasyExcelCellWriteHandler并继承CellWriteHandler

public class EasyExcelCellWriteHandler implements CellWriteHandler {
    /**
     错误信息处理时正则表达式的格式
     */
    private final String EXCEL_ERROR_REG = "^(.*)(\\(错误:)(.*)(\\))$";
    /**
     操作列
     */
    private final List<Integer> columnIndex;

    private JSONObject headTitle;

    PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");

    public EasyExcelCellWriteHandler(List<Integer> columnIndex, Short colorIndex, HashMap<Integer, String> annotationsMap, HashMap<Integer, String[]> dropDownMap , String time, String month, String year, JSONObject sheetTitle) {
        this.columnIndex = columnIndex;
        this.headTitle = headTitle;
    }

    public EasyExcelCellWriteHandler(List<Integer> columnIndex, JSONObject headTitle) {
        this.columnIndex = columnIndex;
        this.headTitle = headTitle;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        // 动态设置表头字段
        if (!ObjectUtils.isEmpty(head)) {
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)) {

                ArrayList<Properties> propertiesList = new ArrayList<>();

                for (String key : headTitle.keySet()){
                    Properties properties = new Properties();
                    properties.setProperty(key, headTitle.getString(key));
                    propertiesList.add(properties);
                }
                for (int i = 0 ; i < headNameList.size() ; i++){
                    for (Properties properties : propertiesList) {
                        //表头中如果有${}设置的单元格,则可以自定义赋值。 根据构造方法传入的jsonObject对象
                        headNameList.set(i, placeholderHelper.replacePlaceholders(headNameList.get(i), properties));
                    }
                }
            }
        }
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }

headTitle格式为json,目的是为了方便有多个变化的表头一次性传递。

创建 EasyExcelSheetWriteHandler并继承SheetWriteHandler(这是为了创建标题)

public class EasyExcelSheetWriteHandler implements SheetWriteHandler {

    private String title;
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    public EasyExcelSheetWriteHandler() {
        super();
    }

    public EasyExcelSheetWriteHandler(String title) {
        this.title = title;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 800);
        Cell cell = row1.createCell(0);
        //设置标题
        cell.setCellValue(title);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 400);
        font.setFontName("宋体");
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 9));
    }

业务层

            EasyExcelSheetWriteHandler writeHandler = new EasyExcelSheetWriteHandler(reportTitle);
            JSONObject obj = new JSONObject();
            obj.put("day", DateUtils.getDate(day));
            obj.put("month", DateUtils.getMonth(day));
            obj.put("year", DateUtils.getYear(day));
            EasyExcelCellWriteHandler easyExcelTitleHandler = new EasyExcelCellWriteHandler(null, obj);
            ExcelUtil<Report> util = new ExcelUtil<>(Report.class);
            util.getLocalFile(filePath);
            util.exportEasyExcelFile(filePath, saleDayList, "统计", writeHandler, easyExcelTitleHandler);
            注:filePath为文件的全路径(文件存放路径+文件名)
            ExcelUtil为easyExcel的方法类

exportEasyExcelFile方法

    /**
     * 对list数据源将其里面的数据导入到excel表单(EasyExcel)保存到本地
     * @param list 导出数据集合
     * @param sheetName 工作表的名称
     * @return 结果
     */
    public void exportEasyExcelFile(String fileName, List<T> list, String sheetName, EasyExcelSheetWriteHandler writeHandler, EasyExcelCellWriteHandler easyExcelTitleHandler) throws Exception
    {
        EasyExcel.write(fileName, clazz)
                .sheet(sheetName)
                .relativeHeadRowIndex(1)
                .registerWriteHandler(writeHandler)
                .registerWriteHandler(easyExcelTitleHandler)
                .doWrite(list);
    }

参考文章:https://blog.csdn.net/Jul_C18672868641/article/details/129022583 ? ?https://blog.csdn.net/m0_47185078/article/details/125444869

文章来源:https://blog.csdn.net/wen_3370/article/details/135222012
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。