参考

easyexcel使用教程-导出篇

一、引入包

<!--easyexcel -->
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>2.2.6</version>
 </dependency>

二、导出到文件并输出到后台

java">

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.SneakyThrows;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.List;

/**
 * excel导出
 * @create 2022-12-09
 */
public class ExcelUtil {

    private ExcelWriter excelWriter;
    private  File file;
    private String fileName;
    private HttpServletResponse response;
    private WriteSheet writeSheet;


    @SneakyThrows
    public static <T> ExcelUtil create(HttpServletResponse response,String fileNamePrefix,  Class<T> excelModeClass){
        ExcelUtil excelUtil = new ExcelUtil();
        excelUtil.response = response;
        excelUtil.fileName = fileNamePrefix+ ".xlsx";
        // 临时文件
        String filePath =  "/" +fileNamePrefix+ System.currentTimeMillis() + ".xlsx";
        excelUtil.file = new File(filePath);
            // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        excelUtil.excelWriter = EasyExcel.write(filePath, excelModeClass).build();
        excelUtil.writeSheet = EasyExcel.writerSheet("第一页").build();

            // 写入数据
        return excelUtil;
    }

    @SneakyThrows
    public void export(){
            ServletOutputStream out = response.getOutputStream();

            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();

            // 导出
            String fileName = new String(this.fileName
                    .getBytes(StandardCharsets.UTF_8), "iso8859-1");
            response.setHeader("Content-disposition", "attachment;filename="+fileName);
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            //4.获取要下载的文件输入流
            InputStream in = Files.newInputStream(Paths.get(this.file.getPath()));
            int len;
            //5.创建数据缓冲区
            byte[] buffer = new byte[1024];
            //6.通过response对象获取OutputStream流
            //7.将FileInputStream流写入到buffer缓冲区
            while ((len = in.read(buffer)) > 0) {
                //8.使用OutputStream将缓冲区的数据输出到客户端浏览器
                out.write(buffer,0,len);
            }
            in.close();
            this.file.deleteOnExit();
            out.flush();
    }


    @SneakyThrows
    public <T> void writeData(List<T> data){
        excelWriter.write(data, writeSheet);
    }
}

三、过滤字段

过滤字段不生成excel

方式1：类上加注解 @ExcelIgnoreUnannotated，过滤属性没有@ExcelProperty注解的字段

java">@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor  // 一定要有无参构造方法
@ExcelIgnoreUnannotated
public class Student {
    .....
}

方式2：指定字段加注解

java">@ExcelIgnore // demo2不生成excel
private String demo2;

excelsheet_125">方式3：代码指定过滤字段, 同一个excel生成两个sheet分别过滤不同字段

java">/**
 * 过滤字段
 */
@Test
public void exportExcludeColumn() {
    Consumer<ExcelWriter> consumer = writer ->
            writer.write(generateStudent(10), EasyExcel.writerSheet(1, "学生信息")
                    .excludeColumnFiledNames(Arrays.asList("name", "sex")) // sheet1过滤姓名、性别
                    .head(Student.class)
                    .build());
    consumer = consumer.andThen(writer ->
            writer.write(generateStudent(10), EasyExcel.writerSheet(2, "学生信息2")
                    .excludeColumnFiledNames(Arrays.asList("birthday", "weight")) // sheet2过滤生日和体重
                    .head(Student.class)
                    .build()));
    export("D:/报表.xlsx", consumer);

四、冻结列

冻结列, 冻结姓名列

冻结列handler,FreezeNameHandler.java

java">package com.learning.easyexcel.converter;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Sheet;

/**
 * 冻结姓名列
 */
public class FreezeNameHandler implements SheetWriteHandler {
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 获取到当前的sheet
        Sheet sheet = writeSheetHolder.getSheet();
        /**
         *第一个参数：冻结的列数
         *第二个参数：冻结的行数
         *第三个参数：冻结后第一列的列号
         *第四个参数：冻结后第一行的行号
         **/
        sheet.createFreezePane(1, 0, 1, 0);
    }
}

注册handler

java">  /**
     * 冻结姓名列
     */
    @Test
    public void exportFreezeColumn() {
        Consumer<ExcelWriter> consumer = writer -> {
            writer.write(generateStudent(10), EasyExcel.writerSheet("学生信息")
                    .registerWriteHandler(new FreezeNameHandler()) // 冻结姓名列
                    .head(Student.class)
                    .build());
        };
        export("D:/报表.xlsx", consumer);

五、格式化

把保留2位小数

• 方法1，@NumberFormat 注解。修改Student类，如下做法会以字符串导出到excel，单元格靠左

java">@ExcelProperty(value = "体重KG")
@NumberFormat("0.##") // 会以字符串形式生成单元格，要计算的列不推荐
private BigDecimal weight;

• 方法2：@ContentStyle(dataFormat = 2) 注解 ，我们新建一个字段weight2，会以数字导出，单元格中靠右

@ContentStyle(dataFormat = 2)
private BigDecimal weight2;

统一数字转换器

java">package com.test.easyexcel.converter;

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

import java.math.BigDecimal;
import java.math.RoundingMode;

public class BigDecimalConverter implements Converter<BigDecimal> {

    @Override
    public Class supportJavaTypeKey() {
        return BigDecimal.class;
    }

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

    @Override
    public BigDecimal convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return cellData.getNumberValue();
    }

    @Override
    public CellData convertToExcelData(BigDecimal value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new CellData(value.setScale(2, RoundingMode.DOWN));
    }

枚举转换器

java">public class StatusConverter implements Converter<Integer> {

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

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

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) {
        return "正常".equals(cellData.getStringValue()) ? 1 : 0;
    }

    @Override
    public CellData<String> convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration)  {
        return new CellData<>(integer.equals(1) ? "正常" : "异常");
    }
}

自定义格式转换器

java">@Target(value = {ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelPropertyExt {
    String expression() default "";
}

自定义转换器

java">
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;

/**
 * easyExcel枚举转换
 * @create 2022-12-07
 */
public class CommonIntegerConverter implements Converter<Integer> {

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

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

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) {
        return "正常".equals(cellData.getStringValue()) ? 1 : 0;
    }

    @Override
    public CellData<String> convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration)  {
        String value = integer.toString();
        Field field = excelContentProperty.getField();
        ExcelPropertyExt annotation = field.getAnnotation(ExcelPropertyExt.class);
        if(annotation != null){
            Method[] meth = annotation.annotationType().getDeclaredMethods();
            for(Method me : meth){
                if(!me.isAccessible()){
                    me.setAccessible(true);
                }
                try {
                    //给字段重新赋值
                    String expression = (String) me.invoke(annotation);
                    List<String> list =  StringUtil.split(expression, SymbolConstants.SEMICOLON);
                    for(String dic : list){
                        List<String> items = StringUtil.split(dic, SymbolConstants.C_COMMA);
                        String key = items.get(0);
                        String v = items.get(1);
                        if(value.equals(key)) {
                            value = v;
                            break;
                        }
                    }
//                    System.out.println("expression:"+expression);
                } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
        return new CellData<>(value);
    }
}

excel_365">配置excel导出模型

java">@Data
@AllArgsConstructor
@NoArgsConstructor
public class DataModelDTO implements Serializable {

    private static final long serialVersionUID = 15353L;

    @ExcelProperty(value = "设备名称")
    private String deviceName;

    @ExcelPropertyExt(expression = "0,停止;1,上;2,下")
    @ExcelProperty(value = "运行方向",converter = CommonIntegerConverter.class)
    private Integer direction;

    @ExcelProperty(value = "状态", converter = StatusConverter.class)
    private Integer Status;

    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = "数据上报时间")
    private Date timestamp;
}

六、导出

通过controller导出

java">@Operation(summary = "导出")
@GetMapping("/data/export")
void export(HttpServletResponse response) {
    ExcelUtil excelUtil = ExcelUtil.create(response,"设备实时数据", DataModelDTO.class);
    // 写入数据
	excelUtil.writeData( getData());
	        // 导出
	excelUtil.export();
}

private List<UserDataModelDTO> getData(){
	List<UserDataModelDTO> list = new ArrayList<>();
	DataModelDTO data1 = new DataModelDTO("设备1",1,1,new Date());
	DataModelDTO data2 = new DataModelDTO("设备2",1,1,new Date());
	list.add(data1);
	list.add(data2);
	return list ;
}
        

统一导出转换器导出

java">Consumer<ExcelWriter> consumer = writer -> {
            writer.write(generateStudent(10), EasyExcel.writerSheet("学生信息")
                    .registerConverter(new BigDecimalConverter())
                    .head(Student.class)
                    .build());
        };
        export("D:/报表.xlsx", consumer);

七、效果