Apache POI操作Excel详解

Maven依赖 

<!-- 核心库（支持.xls） -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId>
</dependency><!-- 支持.xlsx格式 -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId>
</dependency>

核心概念 

基础操作

private void test() {//创建工作簿Workbook workbook = new SXSSFWorkbook();Sheet sheet = workbook.createSheet();//创建表头//创建第一行Row row = sheet.createRow(0);//第一行 第一列row.createCell(0).setCellValue("姓名");//第一行 第二列row.createCell(1).setCellValue("年龄");//创建第二行Row row1 = sheet.createRow(1);//第二行 第一列row1.createCell(0).setCellValue("张三");//第二行 第二列row1.createCell(1).setCellValue("18");//创建第三行Row row2 = sheet.createRow(2);//第三行 第一列row2.createCell(0).setCellValue("李四");//第三行 第二列row2.createCell(1).setCellValue("20");try (FileOutputStream fileOutputStream = new FileOutputStream("学生信息.xlsx")) {workbook.write(fileOutputStream);workbook.close();} catch (Exception e) {throw new RuntimeException();}}

封装工具类使用 ExcelUtils

读取文件

ExcelUtils.read(OPCPackage.open(inputStream), 1, (row, index) -> {String keyword = ExcelUtils.getString(row.getCell(0));String cityName = ExcelUtils.getString(row.getCell(1));String positionType = ExcelUtils.getString(row.getCell(2));if (StringUtils.isEmpty(keyword) || "未匹配到城市".equals(cityName) || "未匹配到工种".equals(positionType)) {return;}PositionSeoKeywordRequest keywordRequest = PositionSeoKeywordRequest.builder().keyword(keyword).positionType(positionType).cityName(cityName).deleted(0L).createTime(now).updateTime(now).build();list.add(keywordRequest);
});

写文件

//创建工作簿实例
Workbook workbook = new SXSSFWorkbook();
// sheet页1-拉新业务看板数据
int sheet1Volume = ExcelUtils.write(workbook,(page, size) -> this.customerShareRelationRecordService.listCustomerShareSummaries(param.getPeriod(), param.getSharerId(), Paging.of(page, size)),(row, summary, i) -> {EmployeeDTO employee = employeesMap.get(summary.getSharerId());// sharerId = 0 代表是合计总数row.createCell(0).setCellValue(summary.getSharerId() == 0L ? "合计" : employee == null ? null : employee.getName());row.createCell(1).setCellValue(employee == null ? null : employee.getMobile());row.createCell(2).setCellValue(summary.getRegisterCount());row.createCell(3).setCellValue(summary.getReleasePositionNormalCount() + summary.getReleasePositionAbnormalCount());row.createCell(4).setCellValue(summary.getReleasePositionNormalCount());row.createCell(5).setCellValue(summary.getReleasePositionAbnormalCount());},"销售人员", "手机号", "注册人数", "发布职位人数", "发布职位人数-正常用户", "发布职位人数-异常用户"
);
workbook.setSheetName(0, "汇总数据");// sheet页2-异常用户明细数据
int sheet2Volume = ExcelUtils.write(workbook,(page, size) -> this.customerShareRelationRecordService.listEachTypeUsers(param.getPeriod(), param.getSharerId(), Paging.of(page, size), CustomerShareType.ABNORMAL),(row, abnormal, i) -> {EmployeeDTO employee = employeesMap.get(abnormal.getSharerId());row.createCell(0).setCellValue(ObjectUtils.ifNull(employee, EmployeeDTO::getName));row.createCell(1).setCellValue(abnormal.getMobile());row.createCell(2).setCellValue(DateUtils.format(abnormal.getOperateTime()));row.createCell(3).setCellValue(abnormal.getAbnormalMobile());},"销售人员", "用户登录手机号", "发布职位日期", "发布职位信息填写联系电话"
);
workbook.setSheetName(1, "发布职位人-异常用户明细");// sheet页3-正常用户明细数据
int sheet3Volume = ExcelUtils.write(workbook,(page, size) -> this.customerShareRelationRecordService.listEachTypeUsers(param.getPeriod(), param.getSharerId(), Paging.of(page, size), CustomerShareType.NORMAL),(row, abnormal, i) -> {EmployeeDTO employee = employeesMap.get(abnormal.getSharerId());row.createCell(0).setCellValue(ObjectUtils.ifNull(employee, EmployeeDTO::getName));row.createCell(1).setCellValue(abnormal.getMobile());row.createCell(2).setCellValue(DateUtils.format(abnormal.getOperateTime()));row.createCell(3).setCellValue(abnormal.getAbnormalMobile());},"销售人员", "用户登录手机号", "发布职位日期", "发布职位信息填写联系电话"
);
workbook.setSheetName(2, "发布职位人-正常用户明细");// 保存文件
File attachment = new File(this.getExportDirectory(), String.valueOf(exportationId));
try (OutputStream output = new FileOutputStream(attachment)) {
workbook.write(output);
workbook.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
//导出完成
this.exportationService.completeExporting(exportationId, sheet1Volume + sheet2Volume + sheet3Volume);

private File doExportRecruitData(List<PositionEmailSendDTO> positionEmailSendDTOS) {File attachment = new File(getExportDirectory(), UUID.randomUUID() + EXCEL_EXTENSION);try (Workbook workbook = new SXSSFWorkbook()) {Sheet sheet = ExcelUtils.initializeSheet(workbook,EXCEL_HEADERS);for (int i = 0, size = positionEmailSendDTOS.size(); i < size; i++) {PositionEmailSendDTO label = positionEmailSendDTOS.get(i);Row row = sheet.createRow(i+1);row.createCell(0).setCellValue(label.getTitle());row.createCell(1).setCellValue(label.getDescription());row.createCell(2).setCellValue(label.getRegionName());row.createCell(3).setCellValue(label.getType().getDescription());row.createCell(4).setCellValue(label.getMobile());}try (OutputStream output = new FileOutputStream(attachment)) {workbook.write(output);workbook.close();}return attachment;} catch (Exception e) {log.error("【招聘数据邮件发送定时器】导出失败:{}",e.getMessage());throw new RuntimeException("Failed to export recruitment data", e);}
}