SpringBoot实用知识（七）——Apache POI的使用

最新推荐文章于 2024-08-03 23:24:18 发布

岂曰无衣qwq

最新推荐文章于 2024-08-03 23:24:18 发布

阅读量132

点赞数 3

分类专栏： java后端开发文章标签： spring boot apache 后端

本文链接：https://blog.csdn.net/2201_75415493/article/details/140505282

版权

java后端开发专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1.导入maven坐标

<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();//在内存中创建一个excel文件
XSSFSheet sheet = excel.createSheet("sheet1");//在excel文件中创建一个sheet
XSSFRow row = sheet.createRow(0);//在sheet1中创建一个行对象
row.createCell(0).setCellValue("hello world");//在行对象中创建一个单元格并写入内容
FileOutputStream out = new FileOutputStream(new File("D:\\test.xlsx"));
excel.write(out);//将excel文件写入到输出流中再写入磁盘
out.close();
excel.close();//关闭资源

3.excel读文件操作

InputStream in=new FileInputStream(new File("D:\\test.xlsx"));
XSSFWorkbook excel = new XSSFWorkbook(new FileInputStream(new File("D:\\test.xlsx")));//读取磁盘中已存在的文件
XSSFSheet sheet = excel.getSheetAt(0);//获取excel文件中的第一个sheet页
 int lastRowNum = sheet.getLastRowNum();//获取最后一行的行号
for (int i = 0; i <= lastRowNum; i++) {
   XSSFRow row = sheet.getRow(i);//获取第i行
   row.getCell(2).getStringCellValue();//获取第i行的第3列的内容
}
in.close();
excel.close();//关闭资源

4.excel导出示例

controller层

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

service层

@Override
    public void exportBusinessData(HttpServletResponse response) {
        //1.查询数据库，获取相应数据
        //...
        //2.通过POI将数据写入到Excel中
        InputStream in =  this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");//获取数据模板
        try {
            XSSFWorkbook excel = new XSSFWorkbook(in);
            //获取sheet
            XSSFSheet sheet = excel.getSheet("Sheet1");
            //填充数据时间
            sheet.getRow(1).getCell(1).setCellValue("时间:"+dateBegin + "至" + dateEnd);
            XSSFRow row = sheet.getRow(3);
           row.getCell(2).setCellValue(businessDataVO.getTurnover());
           row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());
           row.getCell(6).setCellValue(businessDataVO.getNewUsers());
           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=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 = response.getOutputStream();
           excel.write(out);
           out.close();
           excel.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }