史上性能最高Excel导出之Alibaba easyexcel_easyexcel隐藏列

EasyExcel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址:https://github.com/alibaba/easyexcel

导出示例

快速开始

在pom中引入easyExcel依赖

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>3.0.5</version>
</dependency>
1
2
3
4
5

导出的实体model

设置导出头，样式，行高等

@ColumnWidth(30)
@ContentRowHeight(25)
@HeadRowHeight(25)
@HeadFontStyle(fontHeightInPoints = 11)
public class OrderExportDTO {

    @ExcelProperty(value = "Order No.", index = 0)
    private String orderNo;

    @ExcelProperty(value = "Report No.", index = 1)
    private String reportNo;

    @ExcelProperty(value = "Boss No.", index = 2)
    private String bossOrderNo;

    @ExcelProperty(value = "Quotation No", index = 3)
    private String quotationNo;

    @ExcelProperty(value = "Tracing Order", index = 4)
    private String tracingOrder;

    @ExcelProperty(value = "Applicant Name", index = 5)
    private String applicantNameEn;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

@ColumnWidth(30) 设置列宽
@ContentRowHeight(25) 设置行高
@HeadRowHeight(25) 设置首行行高
@HeadFontStyle(fontHeightInPoints = 11) 设置首行字体大小
@ExcelProperty(value = “Applicant Name”, index = 5) 首行显示的title，及顺序

EasyExcelUtil 工具类

设置文件名及response
动态隐藏列
设置单元格样式等，自行扩展

public class EasyExcelUtil {

    private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtil.class);

    public static void buildResponse(HttpServletResponse response, String fileName) {
        response.setCharacterEncoding("utf-8");
        response.setContentType("multipart/form-data");
        response.addHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    }

    public static void export(HttpServletResponse response, ExcelModel excelModel) {
        try {
            if (excelModel.isHiddenCol()) {
                EasyExcel.write(response.getOutputStream(), excelModel.getClazz())
                        .registerWriteHandler(new ExcelCellWriteHandler(excelModel.getHiddenColIndexList()))
                        .autoCloseStream(Boolean.FALSE).sheet(excelModel.getSheetName()).doWrite(excelModel.getData());
            } else {
                EasyExcel.write(response.getOutputStream(), excelModel.getClazz())
                        .autoCloseStream(Boolean.FALSE).sheet(excelModel.getSheetName()).doWrite(excelModel.getData());
            }
        } catch (IOException e) {
            e.printStackTrace();
            logger.error("{} export failure!{}", excelModel.getFileName(), e);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

ExcelModel封装了导出的一些属性，sheetName，导出的数据，隐藏列，fileName，excel数据模板等

public class ExcelModel<T> {

    private Class clazz;
    private String sheetName;
    private String fileName;
    private List<T> data;
    private boolean isHiddenCol;
    private Set<Integer> hiddenColIndexList;

    public ExcelModel() {}

    public ExcelModel(Class clazz, String fileName, String sheetName, List<T> data, Boolean isHiddenCol, Integer[] hiddenColIndex) {
        this.clazz = clazz;
        this.sheetName = sheetName;
        this.fileName = fileName;
        this.data = data;
        this.isHiddenCol = isHiddenCol;
        if (isHiddenCol) {
            this.hiddenColIndexList = CollUtil.newHashSet(hiddenColIndex);
        }
    }

    public static <T> ExcelModel<T> buildExcelModel(Class clazz, String fileName, String sheetName, List<T> data, Boolean isHiddenCol, Integer... hiddenColIndex) {
        return new ExcelModel(clazz, fileName, sheetName, data, isHiddenCol, hiddenColIndex);
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public Class getClazz() {
        return clazz;
    }

    public void setClazz(Class clazz) {
        this.clazz = clazz;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }

    public boolean isHiddenCol() {
        return isHiddenCol;
    }

    public void setHiddenCol(boolean hiddenCol) {
        isHiddenCol = hiddenCol;
    }

    public Set<Integer> getHiddenColIndexList() {
        return hiddenColIndexList;
    }

    public void setHiddenColIndexList(Set<Integer> hiddenColIndexList) {
        this.hiddenColIndexList = hiddenColIndexList;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

导出示例

EasyExcelUtil.buildResponse(response, OdcConstant.REPORT_FOR_ORDER_FILE_NAME);
ExcelModel excelModel = ExcelModel.buildExcelModel(OrderExportDTO.class, OdcConstant.REPORT_FOR_ORDER_FILE_NAME,
                    OdcConstant.REPORT_SHEET_NAME, orderExportDTOS, Boolean.FALSE);

// 需要设置隐藏列的方式
ExcelModel excelModel = ExcelModel.buildExcelModel(OrderExportDTO.class, OdcConstant.REPORT_FOR_ORDER_FILE_NAME,
                    OdcConstant.REPORT_SHEET_NAME, orderExportDTOS, Boolean.TRUE, 29, 30);

EasyExcelUtil.export(response, excelModel);
1
2
3
4
5
6
7
8
9