使用easy poi註解實現二級表頭

一、使用easy poi註解實現二級表頭效果

使用註解寫的話easy poi 貌似最多隻能實現二級表頭, 更多級需求用easy poi的模板, 或者用阿里的easy excel; 下面是註解實現下圖效果程式碼。官網: http://easypoi.mydoc.io/#category_41961

二、實現

1、依賴

<!-- spring boot直接用這個就行 -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>3.3.0</version>
</dependency>

<!-- 非spring boot用這個 -->
<dependency>
     <groupId>cn.afterturn</groupId>
     <artifactId>easypoi-base</artifactId>
     <version>3.3.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.3.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.3.0</version>
</dependency>

2、實體bean

@Data
public class RealIncome {

    /**
     * 運營專案名稱
     */
    @Excel(name = "運營專案名稱", orderNum = "0", width = 15, height = 13.5d, mergeVertical = true)
    private String projectName;

    /**
     * 我方主體名稱
     */
    @Excel(name = "我方主體名稱", orderNum = "1", width = 15, isWrap = false)
    private String myEntityName;

    /**
     * 收入_含稅 1-12月
     */
    @Excel(name = "1月", orderNum = "7", groupName = "收入_含稅")
    private BigDecimal incomeContainTax1;
    @Excel(name = "2月", orderNum = "8", groupName = "收入_含稅")
   
    @Excel(name = "12月", orderNum = "18", groupName = "收入_含稅")
    private BigDecimal incomeContainTax12;
    @Excel(name = "總計", orderNum = "19", groupName = "收入_含稅")
    private BigDecimal incomeContainTaxSum;
    ...
}

上面height屬性設定行高不生效, 官網寫使用@ExcelTarget的height, 但是裡面只有一個value屬性, 沒有找到height

更多引數見官網: http://easypoi.mydoc.io/#category_41961

3、生成匯出

/**
 * excel 匯出
 *
 * @param list      資料
 * @param title     標題
 * @param sheetName sheet名稱
 * @param pojoClass pojo型別
 * @param fileName  檔名稱
 * @param response
 */
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                               HttpServletResponse response) throws IOException {
    defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
}

/**
 * 預設的 excel 匯出
 *
 * @param list         資料
 * @param pojoClass    pojo型別
 * @param fileName     檔名稱
 * @param response
 * @param exportParams 匯出引數
 */
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
                                  ExportParams exportParams) throws IOException {
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
    downLoadExcel(fileName, response, workbook);
}

/**
 * 下載
 *
 * @param fileName 檔名稱
 * @param response
 * @param workbook excel資料
 */
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)
    throws IOException {
    try {
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename="
                           + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
        workbook.write(response.getOutputStream());
    } catch (Exception e) {
        throw new IOException(e.getMessage());
    }
}

4、問題

使用上述註解實現, 無法動態傳入表頭!!!

可以使用下面的ExcelExportEntity實現, 但是對資料封裝結構要求比較嚴格, 沒法直接用List實現!!!

5、使用ExcelExportEntity實現動態表頭傳值

資料封裝結構比較複雜, 不好實現, 不推薦, 直接使用模板應該可以。

public static void main(String[] args) {
    List<Map<String, Object>> list = new ArrayList<>();

    Map<String, Object> map = new HashMap<>();
    map.put("projectName", "雲");
    map.put("myEntityName", "en");
    List<Map<String, Object>> incomeCTList = new ArrayList<>();
    Map<String, Object> subMap = new HashMap<>();
    subMap.put("incomeContainTax1", "12");
    subMap.put("incomeContainTax2", "13");
    subMap.put("incomeContainTaxSum", "25");
    incomeCTList.add(subMap);
    map.put("incomeContainTax", incomeCTList);
    list.add(map);

    Map<String, Object> map2 = new HashMap<>();
    map2.put("projectName", "雲2");
    map2.put("myEntityName", "en2");
    List<Map<String, Object>> incomeCTList2 = new ArrayList<>();
    Map<String, Object> subMap2 = new HashMap<>();
    subMap2.put("incomeContainTax1", "120");
    subMap2.put("incomeContainTax2", "130");
    subMap2.put("incomeContainTaxSum", "250");
    incomeCTList2.add(subMap2);
    map2.put("incomeContainTax", incomeCTList2);
    List<Map<String, Object>> incomeNCTList2 = new ArrayList<>();
    Map<String, Object> subMap22 = new HashMap<>();
    subMap22.put("incomeNotContainTax1", "120");
    subMap22.put("incomeNotContainTax2", "130");
    subMap22.put("incomeNotContainTaxSum", new BigDecimal("23.567"));
    incomeNCTList2.add(subMap22);
    map2.put("incomeNotContainTax", incomeNCTList2);
    list.add(map2);

    try {
        test(list);
    } catch (IOException e) {
        e.printStackTrace();
    }
}

private static void test(List list) throws IOException {
    List<ExcelExportEntity> colList = new ArrayList<>();

    ExcelExportEntity colEntity = new ExcelExportEntity("運營專案名稱", "projectName");
    colEntity.setNeedMerge(true);
    colList.add(colEntity);

    colEntity = new ExcelExportEntity("我方主體名稱", "myEntityName");
    colEntity.setNeedMerge(true);
    colList.add(colEntity);

    String year = "2020";

    ExcelExportEntity incomeContainTaxGroup = new ExcelExportEntity("收入_含稅", "incomeContainTax");
    List<ExcelExportEntity> incomeContainTaxList = new ArrayList<>();
    incomeContainTaxList.add(new ExcelExportEntity("1月", "incomeContainTax1"));
    incomeContainTaxList.add(new ExcelExportEntity("2月", "incomeContainTax2"));
    incomeContainTaxList.add(new ExcelExportEntity("3月", "incomeContainTax3"));
    incomeContainTaxList.add(new ExcelExportEntity(year, "incomeContainTaxSum"));
    incomeContainTaxGroup.setList(incomeContainTaxList);
    colList.add(incomeContainTaxGroup);

    ExcelExportEntity incomeNotContainTaxGroup = new ExcelExportEntity("收入_不含稅", "incomeNotContainTax");
    List<ExcelExportEntity> incomeNotContainTaxList = new ArrayList<>();
    incomeNotContainTaxList.add(new ExcelExportEntity("1月", "incomeNotContainTax1"));
    incomeNotContainTaxList.add(new ExcelExportEntity("2月", "incomeNotContainTax2"));
    incomeNotContainTaxList.add(new ExcelExportEntity("3月", "incomeNotContainTax3"));
    incomeNotContainTaxList.add(new ExcelExportEntity(year, "incomeNotContainTaxSum"));
    incomeNotContainTaxGroup.setList(incomeNotContainTaxList);
    colList.add(incomeNotContainTaxGroup);

    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("利潤彙總表", "sheetName啊"), colList,
                                                    list);
    FileOutputStream fos = new FileOutputStream("E:/利潤彙總表.xls");
    workbook.write(fos);
    fos.close();
}