Apache POI（使用Java读写Excel表格数据）

1.Apache POI简介

Apache POI是一个开源的Java库，用于操作Microsoft Office格式的文件。它支持各种Office文档的读写功能，包括Word文档、Excel电子表格、PowerPoint演示文稿、Outlook电子邮件等。Apache POI提供了一组API，使得Java开发者能够轻松地在Java应用程序中处理Office文件。

Apache POI的主要特点包括：

1. 功能丰富：Apache POI提供了广泛的操作，如创建、修改、读取和写入Office文件。

2. 跨平台：由于它是用Java编写的，因此可以在任何支持Java的平台上运行，包括Windows、Linux和macOS。

3. 支持多种文件格式：Apache POI支持旧的Office文件格式（如.xls、.doc）和新的Office Open XML格式（如.xlsx、.docx）。

4. 社区支持：作为一个开源项目，Apache POI有一个活跃的社区，不断更新和改进库。

5. 易于集成：Apache POI可以轻松地集成到Java应用程序中，无论是桌面应用程序还是Web应用程序。

6. 灵活的API：Apache POI提供了多种API，以适应不同的操作需求，从简单的文本操作到复杂的样式和格式处理。

Apache POI包含几个主要的组件，每个组件负责处理不同类型的Office文件：

• POIFS（Poor Obfuscation Implementation File System）：用于处理Microsoft Office旧版本的文件格式，如HSSF（Horrible SpreadSheet Format）和HWPF（Horrible Word Processor Format）。
• XSSF（XML SpreadSheet Format）：用于处理Excel 2007及以后版本的XML文件格式（.xlsx）。
• HSSF（Horrible SpreadSheet Format）：用于处理Excel 97-2003版本的文件格式（.xls）。
• HWPF（Horrible Word Processor Format）：用于处理Word 97-2003版本的文件格式（.doc）。
• XWPF（XML Word Processor Format）：用于处理Word 2007及以后版本的XML文件格式（.docx）。
• HSLF（Horrible Slide Layout Format）和XSLF（XML Slide Layout Format）：用于处理PowerPoint演示文稿。
• HDGF（Horrible Diagram Graphics Format）和XDGF（XML Diagram Graphics Format）：用于处理Visio文件。
• HSMF（Horrible Sticky Note Format）：用于处理Outlook电子邮件文件。

Apache POI是Java开发者处理Office文件的首选库，因为它提供了强大的功能和灵活性，同时还得到了广泛的应用和社区支持。

2.引入依赖： 

        <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.16</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.16</version></dependency>

3. 读写Excel表格 入门示例

3.1 写Excel表格：（如果不存在就创建新的Excel表格，如果已经存在则覆盖Excel表格的内容）

代码：

void write() throws IOException {// 创建一个新的Excel工作簿对象XSSFWorkbook excel = new XSSFWorkbook();// 在工作簿中创建一个名为"info"的工作表XSSFSheet sheet = excel.createSheet("info");// 创建第一行（表头）XSSFRow row = sheet.createRow(0);// 在第一行创建单元格并设置内容为"姓名"row.createCell(0).setCellValue("姓名");// 在第一行创建单元格并设置内容为"年龄"row.createCell(1).setCellValue("年龄");// 在第一行创建单元格并设置内容为"班级"row.createCell(2).setCellValue("班级");// 创建第二行（数据）row = sheet.createRow(1);// 在第二行创建单元格并设置内容为"张三"row.createCell(0).setCellValue("张三");// 在第二行创建单元格并设置内容为"17"row.createCell(1).setCellValue("17");// 在第二行创建单元格并设置内容为"1班"row.createCell(2).setCellValue("1班");// 创建第三行（数据）row = sheet.createRow(2);// 在第三行创建单元格并设置内容为"李四"row.createCell(0).setCellValue("李四");// 在第三行创建单元格并设置内容为"18"row.createCell(1).setCellValue("18");// 在第三行创建单元格并设置内容为"2班"row.createCell(2).setCellValue("2班");// 创建一个文件输出流，指定要写入的文件路径FileOutputStream out = new FileOutputStream(new File("D:\\info.xlsx"));// 将Excel工作簿写入文件输出流excel.write(out);// 关闭文件输出流out.close();// 关闭Excel工作簿，释放资源excel.close();
}

生成的Excel表格如下所示： 

 3.2 读Excel表格：

void read() throws IOException {// 创建一个文件输入流，用于读取指定路径的Excel文件FileInputStream in = new FileInputStream(new File("D:\\info.xlsx"));// 使用文件输入流创建一个XSSFWorkbook对象，该对象代表整个Excel工作簿XSSFWorkbook excel = new XSSFWorkbook(in);// 从工作簿中获取第一个工作表，索引为0XSSFSheet sheet = excel.getSheetAt(0);// 获取工作表中最后一行的编号int lastRowNum = sheet.getLastRowNum();// 遍历工作表中的所有行，包括空行和有数据的行for(int i = 0; i <= lastRowNum; i++) {// 获取指定编号的行XSSFRow row = sheet.getRow(i);// 如果行不为空，则读取第一个和第二个单元格的数据if(row != null) {// 获取第一个单元格的数据，并转换为字符串String cellValue1 = row.getCell(0).getStringCellValue();// 获取第二个单元格的数据，并转换为字符串String cellValue2 = row.getCell(1).getStringCellValue();// 打印单元格的数据System.out.println(cellValue1 + " " + cellValue2);}}// 关闭文件输入流，释放资源in.close();// 关闭XSSFWorkbook对象，释放资源excel.close();
}

4.导出Excel表格数据

4.1 设计Excel模版文件

4.2 根据该模版表格创建实体BusinessDataVO 

/*** 数据概览*/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class BusinessDataVO implements Serializable {private Double turnover;//营业额private Integer validOrderCount;//有效订单数private Double orderCompletionRate;//订单完成率private Double unitPrice;//平均客单价private Integer newUsers;//新增用户数}

4.3 Controller：

    @GetMapping("/export")@ApiOperation("导出运营数据报表")public void export(HttpServletResponse response){reportService.exportBusinessData(response);}

4.4 ServiceImple:（实现exportBusinessData方法）

    /*** 导出运营数据报表* @param response*/public void exportBusinessData(HttpServletResponse response) {//1. 查询数据库，获取营业数据---查询最近30天的运营数据LocalDate dateBegin = LocalDate.now().minusDays(30);LocalDate dateEnd = LocalDate.now().minusDays(1);//查询概览数据BusinessDataVO businessDataVO = workspaceService.getBusinessData(LocalDateTime.of(dateBegin, LocalTime.MIN), LocalDateTime.of(dateEnd, LocalTime.MAX));//2. 通过POI将数据写入到Excel文件中InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");try {//基于模板文件创建一个新的Excel文件XSSFWorkbook excel = new XSSFWorkbook(in);//获取表格文件的Sheet页XSSFSheet sheet = excel.getSheet("Sheet1");//填充数据--时间sheet.getRow(1).getCell(1).setCellValue("时间：" + dateBegin + "至" + dateEnd);//获得第4行XSSFRow row = sheet.getRow(3);row.getCell(2).setCellValue(businessDataVO.getTurnover());row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());row.getCell(6).setCellValue(businessDataVO.getNewUsers());//获得第5行row = sheet.getRow(4);row.getCell(2).setCellValue(businessDataVO.getValidOrderCount());row.getCell(4).setCellValue(businessDataVO.getUnitPrice());//填充明细数据for (int i = 0; i < 30; i++) {LocalDate date = dateBegin.plusDays(i);//查询某一天的营业数据BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));//获得某一行row = sheet.getRow(7 + i);row.getCell(1).setCellValue(date.toString());row.getCell(2).setCellValue(businessData.getTurnover());row.getCell(3).setCellValue(businessData.getValidOrderCount());row.getCell(4).setCellValue(businessData.getOrderCompletionRate());row.getCell(5).setCellValue(businessData.getUnitPrice());row.getCell(6).setCellValue(businessData.getNewUsers());}// 设置内容类型为Excel文件response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");// 设置内容处置为附件，并指定文件名response.setHeader("Content-Disposition", "attachment; filename=\"business_data.xlsx\"");//3. 通过输出流将Excel文件下载到客户端浏览器ServletOutputStream out = response.getOutputStream();excel.write(out);//关闭资源out.close();excel.close();} catch (IOException e) {e.printStackTrace();}}

说明：其中 workspaceService.getBusinessData(LocalDateTime begin, LocalDateTime end)方法是获取时间段统计营业数据即根据时间段获取BusinessDataVO的相应数据

    /*** 根据时间段统计营业数据* @param begin* @param end* @return*/public BusinessDataVO getBusinessData(LocalDateTime begin, LocalDateTime end) {/*** 营业额：当日已完成订单的总金额* 有效订单：当日已完成订单的数量* 订单完成率：有效订单数 / 总订单数* 平均客单价：营业额 / 有效订单数* 新增用户：当日新增用户的数量*/Map map = new HashMap();map.put("begin",begin);map.put("end",end);//查询总订单数Integer totalOrderCount = orderMapper.countByMap(map);map.put("status", Orders.COMPLETED);//营业额Double turnover = orderMapper.sumByMap(map);turnover = turnover == null? 0.0 : turnover;//有效订单数Integer validOrderCount = orderMapper.countByMap(map);Double unitPrice = 0.0;Double orderCompletionRate = 0.0;if(totalOrderCount != 0 && validOrderCount != 0){//订单完成率orderCompletionRate = validOrderCount.doubleValue() / totalOrderCount;//平均客单价unitPrice = turnover / validOrderCount;}//新增用户数Integer newUsers = userMapper.countByMap(map);return BusinessDataVO.builder().turnover(turnover).validOrderCount(validOrderCount).orderCompletionRate(orderCompletionRate).unitPrice(unitPrice).newUsers(newUsers).build();}