Apache POI及导出运营数据

一：Apache POI概念：

Apache POI 是一个处理Miscrosoft Office各种文件格式的开源项目。简单来说就是，我们可以使用 POI 在 Java 程序中对Miscrosoft Office各种文件进行读写操作。

一般情况下，POI 都是用于操作 Excel 文件。

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：写操作具体代码实现：

package com.sky.test;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;

/**
 * 通过POI操作excel表格
 */
public class POITest {
    public static void write() throws Exception {
        //在内存中创建一个Excel文件
        XSSFWorkbook excel = new XSSFWorkbook();
        //在Excel文件中创建一个sheet页
        final XSSFSheet sheet = excel.createSheet("info2");
        //在Excel文件中创建对象，row下标从0开始
        XSSFRow row = sheet.createRow(0);
        //创建单元格并写入文件
        row.createCell(0).setCellValue("name");
        row.createCell(1).setCellValue("age");
        row.createCell(2).setCellValue("password");
        row = sheet.createRow(1);
        row.createCell(0).setCellValue("zhangsan");
        row.createCell(1).setCellValue("18");
        row.createCell(2).setCellValue("123456");
        //通过输出流将内存中的excel文件写入磁盘中
        final FileOutputStream fileOutputStream = new FileOutputStream("E:\\SkyTakeaway\\test.xlsx");
        excel.write(fileOutputStream);
        //关闭资源
        fileOutputStream.close();
        excel.close();
    }

    public static void main(String args[]) throws Exception{
        write();
    }
}

3：读操作具体代码实现：

public static void read() throws Exception{
        //获取文件的输入流
        FileInputStream in = new FileInputStream(new File("E:\\SkyTakeaway\\test.xlsx"));
        //读取磁盘上已经存在的Excel文件
        final XSSFWorkbook excel = new XSSFWorkbook(in);
        //读取Excel文件中的第一个Sheet页
        final XSSFSheet sheetAt = excel.getSheetAt(0);
        //获取Sheet中最后一行的行号
        final int lastRowNum = sheetAt.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            //获取某一行
            final XSSFRow row = sheetAt.getRow(i);
            final String cellValue1 = row.getCell(0).getStringCellValue();
            final String cellValue2 = row.getCell(1).getStringCellValue();
            final String cellValue3 = row.getCell(2).getStringCellValue();
            System.out.println(cellValue1 +" "+cellValue2+" "+cellValue3);
        }
        //关闭资源
        in.close();
        excel.close();
    }

三：运营数据导出：

需求分析和接口设计：

导出的Excel报表格式：

 业务规则：

• 导出Excel形式的报表文件
• 导出最近30天的运营数据

 接口设计：

注意：

当前接口没有返回数据，因为报表导出功能本质上是文件下载， 服务端会通过输出流将Excel文件下载到客户端浏览器

实现步骤分析： 

• 设计Excel模板文件（因为Excel文件格式有时候比较复杂，直接通过代码编写特别麻烦）
• 查询近30天的运营数据
• 将查询到的运营数据写入模板文件
• 通过输出流将Excel文件下载到客户端浏览器

 模板文件：

 在自己的工程的resource下面创建一个包template放入这个模板文件

Control层： 

/**
     * 导出数据报表
     * @param httpResponse
     */
    @GetMapping("/export")
    @ApiOperation("导出数据报表")
    public void export(HttpServletResponse httpResponse){
        log.info("导出数据报表");
        reportService.exportBusinessDate(httpResponse);
    }

Service层：

    /**
     * 导出数据报表
     * @param httpResponse
     */
    @Override
    public void exportBusinessDate(HttpServletResponse httpResponse) {
        //1.查询数据库，获取营业数据--查询最近30天的运营数据
        LocalDate dateBegin = LocalDate.now().minusDays(30); //减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()); //第3个单元格
            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);
                //查询某一天的营业数据
                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(businessDatavo.getTurnover());
                row.getCell(3).setCellValue(businessDatavo.getValidOrderCount());
                row.getCell(4).setCellValue(businessDatavo.getOrderCompletionRate());
                row.getCell(5).setCellValue(businessDatavo.getUnitPrice());
                row.getCell(6).setCellValue(businessDatavo.getNewUsers());
            }
            //3.通过输出流将Excel文件下载到客户端浏览器
            ServletOutputStream out = httpResponse.getOutputStream();
            excel.write(out);
            //关闭资源
            out.close();
            excel.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }