目录

• 参考
• 一、引入包
• 二、导出到文件并输出到后台
• 三、过滤字段
• • 方式1：类上加注解 @ExcelIgnoreUnannotated，过滤属性没有@ExcelProperty注解的字段
  • 方式2：指定字段加注解
  • 方式3：代码指定过滤字段, 同一个excel生成两个sheet分别过滤不同字段
• 四、冻结列
• • 冻结列, 冻结姓名列
  • 注册handler
• 五、格式化
• • 把保留2位小数
  • 统一数字转换器
  • 枚举转换器
  • 自定义格式转换器
  • 自定义转换器
  • 配置excel导出模型
• 六、导出
• • 通过controller导出
  • 统一导出转换器导出
• 七、效果

参考

easyexcel使用教程-导出篇

一、引入包

com.alibabaeasyexcel2.2.6

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

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;@SneakyThrowspublic static  ExcelUtil create(HttpServletResponse response,String fileNamePrefix,  Class 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;}@SneakyThrowspublic 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();}@SneakyThrowspublic  void writeData(List data){excelWriter.write(data, writeSheet);}
}

三、过滤字段

过滤字段不生成excel

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

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

方式2：指定字段加注解

@ExcelIgnore // demo2不生成excel
private String demo2;

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

/*** 过滤字段*/
@Test
public void exportExcludeColumn() {Consumer 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

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 {@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 获取到当前的sheetSheet sheet = writeSheetHolder.getSheet();/***第一个参数：冻结的列数*第二个参数：冻结的行数*第三个参数：冻结后第一列的列号*第四个参数：冻结后第一行的行号**/sheet.createFreezePane(1, 0, 1, 0);}
}

注册handler

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

五、格式化

把保留2位小数

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

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

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

@ContentStyle(dataFormat = 2)
private BigDecimal weight2;

统一数字转换器

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 {@Overridepublic Class supportJavaTypeKey() {return BigDecimal.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.NUMBER;}@Overridepublic BigDecimal convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return cellData.getNumberValue();}@Overridepublic CellData convertToExcelData(BigDecimal value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return new CellData(value.setScale(2, RoundingMode.DOWN));}

枚举转换器

public class StatusConverter implements Converter {@Overridepublic Class supportJavaTypeKey() {return Integer.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) {return "正常".equals(cellData.getStringValue()) ? 1 : 0;}@Overridepublic CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration)  {return new CellData<>(integer.equals(1) ? "正常" : "异常");}
}

自定义格式转换器

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

自定义转换器

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 {@Overridepublic Class supportJavaTypeKey() {return Integer.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) {return "正常".equals(cellData.getStringValue()) ? 1 : 0;}@Overridepublic CellData 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 list =  StringUtil.split(expression, SymbolConstants.SEMICOLON);for(String dic : list){List 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导出模型

@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导出

@Operation(summary = "导出")
@GetMapping("/data/export")
void export(HttpServletResponse response) {ExcelUtil excelUtil = ExcelUtil.create(response,"设备实时数据", DataModelDTO.class);// 写入数据excelUtil.writeData( getData());// 导出excelUtil.export();
}private List getData(){List 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 ;
}

统一导出转换器导出

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

七、效果