博客

一、使用easy poi注解实现二级表头效果

使用注解写的话easy poi 貌似最多只能实现二级表头, 更多级需求用easy poi的模板, 或者用阿里的easy excel; 下面是注解实现下图效果代码。官网: http://doc.wupaas.com/docs/easypoi/easypoi-1c0u96flii98v

二、实现

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://doc.wupaas.com/docs/easypoi/easypoi-1c0u96flii98v

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