Springboot整合EasyExcel用于导出

1.背景

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便

2.依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

3.使用

3.1.实体类

package com.zyp.controller.easyexcel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;

/**
 * @author syl
 * @description TODO
 * @since 2022/3/21
 */
@Data
//表头行高,只能设置所有行
@HeadRowHeight(20)
//设置表头样式，可设置所有列，也可以每列单独设置
@HeadStyle(verticalAlignment = VerticalAlignmentEnum.CENTER,horizontalAlignment = HorizontalAlignmentEnum.CENTER
            ,fillBackgroundColor = 10,fillForegroundColor = 10)
//设置表头字体，可设置所有列，也可以每列单独设置
@HeadFontStyle(fontName = "黑体",bold = BooleanEnum.TRUE)
//设置内容行高 默认动态行高
@ContentRowHeight((short)20)
//设置内容字体，可设置所有列，也可以每列单独设置
@ContentFontStyle(fontName = "宋体",bold = BooleanEnum.FALSE)
//设置内容样式，可设置所有列，也可以每列单独设置
@ContentStyle(verticalAlignment = VerticalAlignmentEnum.CENTER,horizontalAlignment = HorizontalAlignmentEnum.CENTER,wrapped = BooleanEnum.TRUE))
public class StudentExport {

    /**
     * ExcelProperty：value()导出：当你有多少个头时，它会自动合并
     *                       导入：当你有多少个头时，先拿第一个头
     *                       列名指定的话指定的名，不指定则默认是属性名
     *                index():默认为-1，index越小列越靠前
     *                order():默认Integer.MAX_VALUE，order越小越靠前
     *                注：优先级:index>order>默认
     *                converter:自定义转换器,修饰符必须为public
     */
    @ExcelProperty(value = {"学生信息","序号"},order = 0)
    //设置列宽，可设置所有列，也可以每列单独设置
    @ColumnWidth(10)
    //不导出==excludeColumnFiledNames(Arrays.asList("id"))
    //@ExcelIgnore
    private Integer id;

    @ExcelProperty(value = {"学生信息","姓名"},order = 1)
    @ColumnWidth(20)
    private String name;

    @ExcelProperty(value = {"学生信息","年龄"},order = 2)
    @ColumnWidth(10)
    private Integer age;

    @ExcelProperty(value = {"创建时间"},order = 3,converter = DateTimeConverter1.class)
    @ColumnWidth(20)
    private Long createTime;

}

3.2.转换类

package com.zyp.controller.easyexcel;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Objects;

/**
 * @author syl
 * @description 把long转成datetime
 * @since 2022/3/21
 */
public class DateTimeConverter1 implements Converter<Long> {


    private static final DateTimeFormatter DATE_TIME_FORMATTER=DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @Override
    public Class<?> supportJavaTypeKey() {
        return Long.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public WriteCellData<?> convertToExcelData(Long value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) throws Exception {
        if (Objects.isNull(value)){
            new WriteCellData(CellDataTypeEnum.STRING,null);
        }
        LocalDateTime localDateTime = LocalDateTime.ofInstant(Instant.ofEpochMilli(value), ZoneId.systemDefault());
        String dateStr = localDateTime.format(DATE_TIME_FORMATTER);
        return new WriteCellData(CellDataTypeEnum.STRING,dateStr);
    }
}

3.控制层

package com.zyp.controller.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.google.common.collect.Lists;
import com.zyp.common.NoLogin;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.stream.Collectors;

/**
 * @author syl
 * @description 测试easyexcel导出
 * @since 2022/3/21
 */
@RestController
@Api(tags = "测试easyexcel导出")
@RequestMapping("EasyExcelExport/")
public class EasyExcelExportController {

    /**
     * 一次生成多少
     */
    public static final int NUM = 50;

    /**
     * 初始化数据
     * @return
     */
    private ArrayList<StudentExport> initData() {
        ArrayList<StudentExport> studentList = Lists.newArrayList();
        StudentExport studentExport;
        for (int i = 0; i < NUM; i++) {
            studentExport = new StudentExport();
            studentExport.setId(i + 1);
            studentExport.setAge((int) (Math.random() * 10) + 20);
            studentExport.setName(UUID.randomUUID().toString().replaceAll("-", ""));
            studentExport.setCreateTime(System.currentTimeMillis());
            studentList.add(studentExport);
        }
        return studentList;
    }

    @GetMapping(value = "singleSheet",produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
    @ApiOperation(value = "测试单个sheet页导出")
    @NoLogin
    public void single(HttpServletResponse response){
        ArrayList<StudentExport> studentList = initData();
        String name="学生信息表"+System.currentTimeMillis();
        OutputStream out = null;
        try {
            setResponse(name,response);
            out= response.getOutputStream();
            EasyExcel.write(out,StudentExport.class).sheet("zyp").doWrite(studentList);
            //excludeColumnFiledNames(Arrays.asList("id"))指定id列不导出，其他列导出==includeColumnIndexes(Arrays.asList(0))
            //EasyExcel.write(out,StudentExport.class).sheet("zyp").excludeColumnFiledNames(Arrays.asList("id")).doWrite(studentList);
            //includeColumnFiledNames(Arrays.asList("id"))指定id列导出,其他列不导出==includeColumnIndexes(Arrays.asList(0))
            //EasyExcel.write(out,StudentExport.class).sheet("zyp").includeColumnFiledNames(Arrays.asList("id")).doWrite(studentList);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if(out!=null){
                try {
                    out.close();
                } catch (IOException ioException) {
                    ioException.printStackTrace();
                }finally {
                    out=null;
                }
            }
        }
    }

    @GetMapping(value = "manySheet",produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
    @ApiOperation(value = "测试多个sheet页导出")
    @NoLogin
    public void many(HttpServletResponse response) throws Exception {
        ArrayList<StudentExport> studentList = initData();

        String name="学生信息表"+System.currentTimeMillis();
        setResponse(name, response);
        OutputStream out = response.getOutputStream();
        //构建excel输出对象
        ExcelWriter excelWriter = EasyExcel.write(out, StudentExport.class).build();
        WriteSheet sheet;
        Map<Boolean, List<StudentExport>> studentMap = studentList.stream().
                collect(Collectors.partitioningBy(s -> s.getAge() > 25));
        //以年龄为25为分界线输出
        for (Map.Entry<Boolean, List<StudentExport>> entry : studentMap.entrySet()) {
            if (entry.getKey()){
                //一个sheet页
                sheet=EasyExcel.writerSheet("年龄大于25的").build();
                excelWriter.write(entry.getValue(),sheet);
            }else {
                //另一个sheet页
                sheet=EasyExcel.writerSheet("年龄不大于25的").build();
                excelWriter.write(entry.getValue(),sheet);
            }
        }
        //关闭io
        if(excelWriter!=null){
            excelWriter.finish();
        }
    }

    /**
     * 设置响应
     * @param name
     * @param response
     * @throws UnsupportedEncodingException
     */
    private void setResponse(String name, HttpServletResponse response) throws UnsupportedEncodingException {
        response.reset();
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename="+ URLEncoder.encode(name, "utf-8")+".xlsx");
    }
}