Java读写excel文件

最近在学习某马的外卖项目，需要使用Java读写excel文件。

• 导入依赖

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

• 读取excel

FileInputStream in = new FileInputStream(new File("D:\\itcast.xlsx"));
//通过输入流读取指定的Excel文件XSSFWorkbook excel = new XSSFWorkbook(in);
//获取Excel文件的第1个Sheet页
XSSFSheet sheet = excel.getSheetAt(0);
//获取Sheet页中的最后一行的行号
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i <= lastRowNum; i++) {
     //获取Sheet页中的行
     XSSFRow titleRow = sheet.getRow(i);
     //获取行的第2个单元格
     XSSFCell cell1 = titleRow.getCell(1);
     //获取单元格中的文本内容
     String cellValue1 = cell1.getStringCellValue();
     //获取行的第3个单元格
     XSSFCell cell2 = titleRow.getCell(2);
     //获取单元格中的文本内容
     String cellValue2 = cell2.getStringCellValue();
     System.out.println(cellValue1 + " " +cellValue2);
     }
     //关闭资源
     in.close();
     excel.close();

• 写excel

//在内存中创建一个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("上海");
FileOutputStream out = new FileOutputStream(new File("D:\\itcast.xlsx"));
//通过输出流将内存中的Excel文件写入到磁盘上
excel.write(out);
//关闭资源out.flush();
out.close();
excel.close();

• 下载excel

    /**
     * 导出excel表
     * @param httpServletResponse
     */
    @Override
    public void exportData(HttpServletResponse httpServletResponse) throws IOException {
        //打开文件
        InputStream in=  this.getClass().getClassLoader().getResourceAsStream("template/template.xlsx");
        //创键excel对象
        XSSFWorkbook excel=new XSSFWorkbook(in);
        XSSFSheet sheet=excel.getSheetAt(0);
        //创建日期
        LocalDate end=LocalDate.now().plusDays(-1);
        LocalDate begin=LocalDate.now().plusDays(-30);
        sheet.getRow(1).getCell(1).setCellValue(begin+"---"+end);
        //读取概览数据
        BusinessDataVO businessDataVO=this.getBusinessData(LocalDateTime.of(begin,LocalTime.MIN),LocalDateTime.of(end,LocalTime.MAX));
        sheet.getRow(3).getCell(2).setCellValue(businessDataVO.getTurnover());
        sheet.getRow(3).getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());
        sheet.getRow(3).getCell(6).setCellValue(businessDataVO.getNewUsers());
        sheet.getRow(4).getCell(2).setCellValue(businessDataVO.getValidOrderCount());
        sheet.getRow(4).getCell(4).setCellValue(businessDataVO.getUnitPrice());
        //遍历每一天
        int cnt=7;
        while(!begin.equals(end)){
            BusinessDataVO businessData=this.getBusinessData(LocalDateTime.of(begin,LocalTime.MIN),LocalDateTime.of(begin,LocalTime.MAX));
            sheet.getRow(cnt).getCell(1).setCellValue(String.valueOf(begin));
            sheet.getRow(cnt).getCell(2).setCellValue(businessData.getTurnover());
            sheet.getRow(cnt).getCell(3).setCellValue(businessData.getValidOrderCount());
            sheet.getRow(cnt).getCell(4).setCellValue(businessData.getOrderCompletionRate());
            sheet.getRow(cnt).getCell(5).setCellValue(businessData.getUnitPrice());
            sheet.getRow(cnt).getCell(6).setCellValue(businessData.getNewUsers());
            begin=begin.plusDays(1);
            cnt++;
        }
        //最后一天
        BusinessDataVO businessData=this.getBusinessData(LocalDateTime.of(begin,LocalTime.MIN),LocalDateTime.of(begin,LocalTime.MAX));
        sheet.getRow(cnt).getCell(1).setCellValue(String.valueOf(begin));
        sheet.getRow(cnt).getCell(2).setCellValue(businessData.getTurnover());
        sheet.getRow(cnt).getCell(3).setCellValue(businessData.getValidOrderCount());
        sheet.getRow(cnt).getCell(4).setCellValue(businessData.getOrderCompletionRate());
        sheet.getRow(cnt).getCell(5).setCellValue(businessData.getUnitPrice());
        sheet.getRow(cnt).getCell(6).setCellValue(businessData.getNewUsers());
        begin=begin.plusDays(1);
        cnt++;

        //设置content-type
        httpServletResponse.setContentType("application/octet-stream");
        //设置文件头
        httpServletResponse.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("example.xlsx", "UTF-8"));
        //获取输出流
        ServletOutputStream out=httpServletResponse.getOutputStream();
        excel.write(out);
        //关闭流
        in.close();
        out.close();
        excel.close();
    }