EasyExcel篇
快速入门
EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel 官网
1.引入依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>4.0.3</version>
</dependency>
2.实体类映射
-
@ExcelIgnore：不映射此属性 -
@ExcelProperty(value=列头名, index=列排序：eg:value=“姓名” index=2 : 将姓名的列头设置在Excel表格的第三列 -
@ExcelProperty({"主题","列头名"})：多级主题- 第一个参数为一级主题(第一行)， 第二个参数为二级主题(第二行)
- 如果一级主题名称相同,那么他们会合并单元格
public class User implements Serializable {@ExcelProperty(value = {"基本信息","用户ID"},index = 0)private Long id;@ExcelProperty(value = {"基本信息","用户名"},index = 1)private String name;@ExcelProperty(value = "创建时间",index = 2)private Long time;
}
3.excel下载
@RestController
@RequestMapping("/api/excel")
public class ExcelController {@Autowiredprivate UserService userService;/*** 导出Excel给前端*/@GetMapping("/export")public void exportUsers(HttpServletResponse response) throws IOException {// 设置响应头，响应体类型为excelresponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");String fileName = URLEncoder.encode("用户数据", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");// 查询数据List<User> userList = userService.list();// 写入ExcelEasyExcel.write(response.getOutputStream(), User.class).sheet("用户数据").doWrite(userList);}
}
4.excel上传
@RestController
@RequestMapping("/api/excel")
public class ExcelController {@Autowiredprivate UserDataListener userDataListener;/*** 从Excel导入用户数据*/@PostMapping("/import")public String importUsers(@RequestParam("file") MultipartFile file) throws IOException {String filename = Paths.get(file.getOriginalFilename()).getFileName().toString();Path targetPath = Paths.get("excel", filename);Files.copy(file.getInputStream(),targetPath);return "OK";}
}
5.写操作
public class Main {/*** 导出Excel到指定文件*/public static void main(String[] args) throws IOException {//准备数据List<User> userDataList = new ArrayList<>();userDataList.add(new User(3L, "张三", Instant.now().toEpochMilli()));userDataList.add(new User(4L, "李四", Instant.now().toEpochMilli()));//要排除的数据HashSet<String> set = new HashSet<>();excludeColumnSet.add("time");// 写入ExcelString fileName = "excel/user.xlsx";//1 创建ExcelWriter对象，排除time字段try (ExcelWriter writer = EasyExcel.write(fileName, User.class).excludeColumnFieldNames(set).build()) {//2 创建Sheet（可以指定sheetNo和sheetName）WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "用户信息表1").build();WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "用户信息表2").build();//3 开始写入数据writer.write(userDataList, writeSheet1);writer.write(userDataList, writeSheet2);//4 关闭流//writer.finish();}// 快速写，排除time字段，适用于单sheet、一次性写，自动关闭流EasyExcel.write(fileName, User.class).excludeColumnFieldNames(set).sheet("用户信息").doWrite(userDataList);}
}
4.读操作
@Slf4j
public class Main {public static void main(String[] args) throws IOException {String fileName= "excel/wyh.xlsx";//1.监听器，用于设置处理数据的方式，不同的sheet可以创建多个对应监听器，这里演示统一监听器AnalysisEventListener<User> listener = new AnalysisEventListener<User>() {@Overridepublic void invoke(User user, AnalysisContext context) {// 每解析一条数据被调用一次System.out.println("解析的数据为: " + user);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 数据解析完成之后被调用System.out.println("数据解析完成......");}};//2.创建Reader对象try(ExcelReader reader = EasyExcel.read(fileName, User.class, listener).build()){//3.指定sheet对象，这里指定读前二个sheetReadSheet sheet1 = EasyExcel.readSheet(0).build();ReadSheet sheet2 = EasyExcel.readSheet(1).build();//4.开始读取数据reader.read(sheet1,sheet2);// 关闭流//reader.finish(); }// doRead()快速异步读，适用于单sheet、一次性读，自动关闭流EasyExcel.read(fileName, User.class,listener).sheet().doRead();// 小数据量可以使用doReadSync()同步读还可以将读取的数据以集合保存List<Object> list = EasyExcel.read(fileName, User.class, listener).sheet().doReadSync();}
}高级功能
追加写
-
EasyExcel默认每次开启流都会覆盖原有数据 -
实现追加写的方式
- 指定
WriteSheet的startRow参数，从指定行开始写入数据 - 先读取原sheet的所有数据，再合并为新数据
- 指定
public class AppendToExcel {public static void main(String[] args) {String fileName = "existing_file.xlsx";// 1. 准备要追加的数据（可以是 List<Map> 或 List<JavaBean>）List<Map<Integer, String>> newData = new ArrayList<>();Map<Integer, String> row1 = new HashMap<>();row1.put(0, "新增数据1");row1.put(1, "新增数据2");newData.add(row1);// 2. 创建 ExcelWriter（追加模式）try(ExcelWriter excelWriter = EasyExcel.write(fileName).build()){// 3. 获取 Sheet（假设追加到第一个 Sheet）WriteSheet writeSheet = EasyExcel.writerSheet(0).build();// 4. 计算已有数据的行数（例如，已有 5 行数据，从第 5 行开始追加）int existingRowCount = EasyExcel.read(fileName).sheet().doReadSync().size(); // 读取所有数据，返回行数writeSheet.setStartRow(existingRowCount); // 设置起始行// 5. 执行追加写入excelWriter.write(newData, writeSheet);// 6. 关闭 ExcelWriter//excelWriter.finish(); }}
}
遍历sheet读
String fileName = "multi_sheet.xlsx";
AnalysisEventListener<User> listener = new AnalysisEventListener<User>() {@Overridepublic void invoke(User user, AnalysisContext context) {// 每解析一条数据被调用一次System.out.println("解析的数据为: " + user);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 数据解析完成之后被调用System.out.println("数据解析完成......");}
};
try(ExcelReader excelReader = EasyExcel.read(fileName).build()) {// 获取所有SheetList<ReadSheet> sheets = excelReader.excelExecutor().sheetList();for (ReadSheet sheet : sheets) {System.out.println("开始读取Sheet: " + sheet.getSheetName());excelReader.read(sheet, DemoData.class, listener);}
}