springboot导出excel文件

1.Apache POI

用于操纵excel表格

1.导入坐标

<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>

2.入门案例

学习操作表格

//在内存中创建一个Excel文件对象

XSSFWorkbook excel = new XSSFWorkbook();

//创建Sheet页

XSSFSheet sheet = excel.createSheet("itcast");

//在Sheet页中创建行，0表示第1行

XSSFRow row1 = sheet.createRow(0);

//创建单元格并在单元格中设置值，单元格编号也是从0开始，1表示第2个单元格 row1.createCell(1).setCellValue("姓名");

row1.createCell(2).setCellValue("城市");

XSSFRow row2 = sheet.createRow(1);

row2.createCell(1).setCellValue("张三");

row2.createCell(2).setCellValue("北京");

XSSFRow row3 = sheet.createRow(2);

row3.createCell(1).setCellValue("李四");

row3.createCell(2).setCellValue("上海");

//通过输出流将内存中的Excel文件写入到磁盘上

FileOutputStream out = new FileOutputStream(new File("D:\\itcast.xlsx"));

excel.write(out);

//关闭资源

out.flush();

out.close();

excel.close();

 2.如何导出excel文件

                                                                         模板参照

 

 1.创建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;//新增用户数

}

2.controller层

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

 3.service层

public interface WorkspaceService {

    /**
     * 根据时间段统计营业数据
     * @param begin
     * @param end
     * @return
     */
    BusinessDataVO getBusinessData(LocalDateTime begin, LocalDateTime end);

}

 总之就是将你要导出的数据先找出来

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();
}

4. 将获取的数据放入excel表

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文件中。运营数据报表模板.xlsx，放在resource/template下
    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());
        }

        //3. 通过输出流将Excel文件下载到客户端浏览器
        ServletOutputStream out = response.getOutputStream();
        excel.write(out);

        //关闭资源
        out.close();
        excel.close();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

 本文可以作为参考思路，具体实现代码要根据自己所需的excel模板进行修改