springboot EasyExcel 实现导入导出

1. 添加依赖

确保 Maven 依赖中包含 EasyExcel 3.0.5：

java"><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version></dependency><!-- excel工具 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency>

确保代码与 EasyExcel 3.0.5 及依赖的 POI 版本兼容

2. 自定义样式处理器

创建 CustomCellStyleHandler 类实现 CellWriteHandler 接口，处理标题和内容的样式：

java">import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;import java.util.List;@Component
public class CustomCellStyleHandler implements CellWriteHandler {private CellStyle titleStyle;private CellStyle contentStyle;private void initStyles(Workbook workbook) {if (titleStyle == null) {// 标题样式titleStyle = workbook.createCellStyle();Font titleFont = workbook.createFont();titleFont.setBold(true); // 加粗titleFont.setFontHeightInPoints((short) 14); // 字体大小titleStyle.setFont(titleFont);titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 背景色}if (contentStyle == null) {// 内容样式contentStyle = workbook.createCellStyle();Font contentFont = workbook.createFont();contentFont.setFontHeightInPoints((short) 12);contentStyle.setFont(contentFont);contentStyle.setAlignment(HorizontalAlignment.CENTER);contentStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中contentStyle.setBorderTop(BorderStyle.THIN); // 细边框contentStyle.setBorderBottom(BorderStyle.THIN);contentStyle.setBorderLeft(BorderStyle.THIN);contentStyle.setBorderRight(BorderStyle.THIN);}}@Overridepublic void afterCellDispose(CellWriteHandlerContext context) {// 拿到poi的workbookWorkbook workbook = context.getWriteWorkbookHolder().getWorkbook();initStyles(workbook);// 当前事件会在 数据设置到poi的cell里面才会回调// 判断不是头的情况 如果是fill 的情况 这里会==null 所以用not trueif (BooleanUtils.isNotTrue(context.getHead())) {Cell cell = context.getCell();if (cell != null) {cell.setCellStyle(contentStyle);}// 这里要把 WriteCellData的样式清空， 不然后面还有一个拦截器 FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到// cell里面去 会导致自己设置的不一样context.getFirstCellData().setWriteCellStyle(null);}}
}

3. 使用处理器导出数据

在写入 Excel 时注册自定义处理器：

java">import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.hieasy.e3.common.excel.CustomCellStyleHandler;import java.util.ArrayList;
import java.util.List;public class ExcelWriter {public static void main(String[] args) {String fileName = "custom_style_example.xlsx";List<DemoData> dataList = new ArrayList<>();dataList.add(new DemoData("Alice", 30));dataList.add(new DemoData("Bob", 28));EasyExcel.write(fileName, DemoData.class).registerWriteHandler(new CustomCellStyleHandler()).sheet("用户信息").doWrite(dataList);}
}// 数据模型类
class DemoData {@ExcelProperty("姓名")private String name;@ExcelProperty("年龄")private Integer age;public DemoData(String name, Integer age) {this.name = name;this.age = age;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}
}

java">@ApiOperation("一键导入")@PostMapping("/import")@ResponseBodypublic R<String> importExcel(@RequestPart MultipartFile file) throws IOException {try {//获取文件的输入流InputStream inputStream = file.getInputStream();List<ApiSyncLog> lst = EasyExcel.read(inputStream) //调用read方法.head(ApiSyncLog.class) //对应导入的实体类.sheet(0) //导入数据的sheet页编号，0代表第一个sheet页，如果不填，则会导入所有sheet页的数据.headRowNumber(1) //列表头行数，1代表列表头有1行，第二行开始为数据行.doReadSync(); //开始读Excel，返回一个List<T>集合，继续后续入库操作//模拟导入数据库操作for (ApiSyncLog log1:lst){System.out.println(log1.toString());}}catch (IOException exception){throw new  RuntimeException(exception);}return R.ok("Success");}@ApiOperation("一键导出")@GetMapping("/export")public void downloadExcel(HttpServletResponse response) throws IOException {// 设置响应头信息response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");String fileName = URLEncoder.encode("日志文件", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");//        List<ApiSyncLog> data = new ArrayList<>();// 准备数据List<ApiSyncLog> data = apiSyncLogMapper.selectList(new QueryWrapper<ApiSyncLog>().eq("BillName", "my_jmmd"));// 写入数据到输出流EasyExcel.write(response.getOutputStream(), ApiSyncLog.class).registerWriteHandler(new CustomCellStyleHandler()).sheet("Sheet1").doWrite(data);}