当前位置:   article > 正文

史上性能最高Excel导出之Alibaba easyexcel_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
本文内容由网友自发贡献,转载请注明出处:https://www.wpsshop.cn/w/不正经/article/detail/170336
推荐阅读
相关标签
  

闽ICP备14008679号