1, Review

Following the last "dynamic header easyExcel import and export"( https://blog.csdn.net/qq_37337660/article/details/114680512)"，
And the last dynamic header import( https://blog.csdn.net/qq_37337660/article/details/110288393 )”After that.
We have a third wave easyExcel Torture, this time export complex Excel, by the way hutool Excel function inside. (hyperlink to the official website)
1. The first step is to design a template and use the template to export a new xlsx;
2. The second step is to use hutool to read the new xlsx, then add data, adjust the format, merge cells, change the sheet name, etc.

Requirements: Export: multi page sheet, complex header, sheet name change, cell merging, simple format adjustment

Analysis: according to the above format requirements of sheet0, we can see that the format of the first few columns is very complex and fixed, and the header is relatively fixed. We can just fill the remaining blank cells and titles. The template is as follows:

2, Filling function of easyExcel

Official website filling: https://www.yuque.com/easyexcel/doc/fill
That's less nonsense. The code is annotated

private String doFill() {
        String fileName = unit.getShortname() + startTime + "—" + endTime + "export.xlsx";
        // Specify the path and name of the new file
        File file = new File(FORM_STORE_PATH + fileName);
        if (!file.getParentFile().exists()) {
            boolean mkdirs = file.getParentFile().mkdirs();
        }
        String newFile = file.getPath();
        // Here is the filled template. Put it under resource
        InputStream template = this.getClass().getResourceAsStream("/template/Template.xlsx");
        com.alibaba.excel.ExcelWriter excelWriter = EasyExcel.write(newFile).withTemplate(template).build();
        // Start filling (I have 8 sheets here, each of which has a Title Dynamic filling. Fill in the title first, and fill in the contents of sheets 0 and 2)
        for (int sheetNo = 0; sheetNo < 8; sheetNo++) {
            WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetNo).build();
            writeSheet.setSheetName(getSheetNameBySheetNo(sheetNo));
            FillConfig fillConfig =
                    FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
            excelWriter.fill(
                    new FillWrapper("CommonTitle", Collections.singleton(title)),// This is the prefix in the template
                    fillConfig,
                    writeSheet);
            switch (sheetNo) {
                case 0:
                    excelWriter.fill(
                            new FillWrapper("data", getData()),// "data" is the prefix in the template
                            writeSheet);
                    break;
                case 2:
                    excelWriter.fill(
                            new FillWrapper("data", getData()),// This is the prefix in the template
                            writeSheet);
                    break;
                default:
                    break;
            }
        }
        excelWriter.finish();
        return fileName;
    }
//Here, the data is encapsulated strictly according to the defined order. If the list has several rows, it will be filled with a few rows down (of course, you can also use map to load the data. You can see how to use the official website)
private List<Sheet02Statistic> getData() {}
//Entity classes are defined as follows
public class Sheet02Statistic {
    /** Current period */
    private Long bq;
    /** Last period */
    private Long sq;
    /** Current growth rate */
    private String bqRate;
    /** corresponding period of last year */
    private Long qntq;
    /** Year on year growth rate */
    private String qntqRate;
    /** since this year on */
    private Long jnyl;
    /** Growth rate since this year */
    private String jnylRate;
}

3, Excel generation of hutool - ExcelWriter

Refer to the official website for various usage: https://www.hutool.cn/docs/#/poi/Excel%E7%94%9F%E6%88%90-ExcelWriter
Original 8th sheet:

Generated by the following code:

Some codes are intercepted here:

// newFilePath is the path to the file just generated with the template
private void doWrite(String newFilePath) {
        cn.hutool.poi.excel.ExcelWriter writer = ExcelUtil.getWriter(newFilePath);
        writeSheet1(writer);
        writeSheet3(writer);
        writeSheet5(writer);
        writeSheet6(writer);
        writeSheet7(writer);
        writeSheet8(writer);
        //Change the sheet name here
        writer.renameSheet(0, title.getSheet0());
        writer.renameSheet(1, title.getSheet1());
        writer.renameSheet(2, title.getSheet2());
        writer.renameSheet(7, title.getSheet7());
        //If a formula is useful, use this to make the formula effective
        Workbook workbook = writer.getWorkbook();
        workbook.setForceFormulaRecalculation(true);
        writer.close();
    }
    
private void writeSheet8(cn.hutool.poi.excel.ExcelWriter writer) {
		//Set font
        Font font = writer.createFont();
        font.setFontName("Blackbody");
        font.setFontHeightInPoints((short) 12);
        //Set header style. Remove background setFillPattern
        CellStyle headCellStyle = writer.getHeadCellStyle();
        headCellStyle.setFillPattern(FillPatternType.NO_FILL);
        headCellStyle.setFont(font);
        headCellStyle.setBorderTop(BorderStyle.NONE);
        headCellStyle.setBorderRight(BorderStyle.NONE);
        headCellStyle.setAlignment(HorizontalAlignment.LEFT);
        List<Sheet8Statistic> data1 = getSheet8StatisticData(1);
        writer.setSheet(8);
        //Skip the first few lines
        writer.passRows(4);
        //Write contents of Table 1
        writer.write(data1);
        //When table 1 is finished, skip another line
        writer.passRows(1);
        //Generate the header information of the second table. Merge cells
        writer.merge(5 + data1.size(), 5 + data1.size(), 0, 7, "(3) Title", true);
        writer.merge(6 + data1.size(), 7 + data1.size(), 0, 0, "", false);
        writer.merge(6 + data1.size(), 7 + data1.size(), 1, 1, "Current period", false);
        writer.merge(6 + data1.size(), 6 + data1.size(), 2, 3, "Last period", false);
        writer.merge(6 + data1.size(), 6 + data1.size(), 4, 5, "corresponding period of last year", false);
        writer.merge(6 + data1.size(), 6 + data1.size(), 6, 7, "since this year on", false);
        //Skip the header information line just
        writer.passRows(2);
        List<Sheet8Statistic> data2 = getSheet8StatisticData(2);
        data2.add(0, new Sheet8Statistic("nothing", "Current period", "piece", "±%", "piece", "±%", "piece", "±%"));
        writer.write(data2);
    }
//Here, the data is encapsulated strictly according to the defined order. If the list has several rows, it will be filled with a few rows down (of course, you can also use map to load the data. You can see how to use the official website)
private List<Sheet8Statistic> getSheet8StatisticData(int tableNo) {}
//Entity classes are defined as follows
public class Sheet8Statistic {
    /** region */
    private Object area;
    /** Current period */
    private Object bq;
    /** Last period */
    private Object sq;
    /** Current growth rate */
    private Object bqRate;
    /** corresponding period of last year */
    private Object qntq;
    /** Year on year growth rate */
    private Object qntqRate;
    /** since this year on */
    private Object jnyl;
    /** Growth rate since this year */
    private Object jnylRate;
}

The data written here are almost the same. If there are problems or errors, please comment and give advice. Use the filling function of easyExcel and the writing function of hutool to export a report. It should be noted that the data here is very small. It doesn't matter. Hutool writes all the data into memory first and then into a file. If the data is too large, it will have to be processed again, and the official website will be closed The methods of multiple writes are mentioned.

Some common methods used are attached below:

4, Calculate the growth rate, yoy, proportion, etc

/**
     * Calculate the growth rate to 2 decimal places
     *
     * @param bq Current period
     * @param sq Last period
     */
    protected String calculateRate(Long bq, Long sq) {
        DecimalFormat df = new DecimalFormat("0.00");
        if (sq == 0) {
            return "0";
        }
        return df.format((float) (bq - sq) / sq * 100);
    }


    /**
     * Calculate year-on-year, month on month and proportion
     *
     * @param bq Current period
     * @param sq Last period
     */
    protected static String calculateTbHb(Long bq, Long sq) {
        if (sq == 0) {
            return "0";
        }
        BigDecimal bqBigDecimal = BigDecimal.valueOf(bq);
        BigDecimal sqBigDecimal = BigDecimal.valueOf(sq);
        BigDecimal result =
                (bqBigDecimal.subtract(sqBigDecimal))
                        .divide(sqBigDecimal, 4, BigDecimal.ROUND_UP)
                        .multiply(BigDecimal.valueOf(100L))
                        .setScale(2, BigDecimal.ROUND_UP);
        if (result.compareTo(BigDecimal.ZERO) > 0) {
            return "+" + result.doubleValue();
        } else if (result.compareTo(BigDecimal.ZERO) < 0) {
            return String.valueOf(result.doubleValue());
        } else {
            return "0";
        }
    }