Poi导入导出Excel文件

1.导入Excel文件

/**
 * 导入excel文件,插入数据库
 *
 * @param files 文件
 * @throws Exception 异常
 */
@PostMapping("/station/excelImport.do")
public void importExcel(MultipartFile files) throws Exception {
    //获取Excel文件输入流
    InputStream is = files.getInputStream();
    //判空校验 直接点保存没有文件传入的情况
    if (is.available() == 0) {
        return;
    }
    //用Excel导入工具把数据传输到集合
    List<ElecStationbug> stationBugs = ExcelImportUtil.importExcel(is, ElecStationbug.class, new ImportParams());
    //打印参数
    log.info("stationBugs:{}", stationBugs);
    //判空校验
    if (stationBugs != null && !stationBugs.isEmpty()) {
        //如果数据不为空 就把集合中的数据传入数据库
        runService.importExcel(stationBugs);
    }else {
        //如果数据为空 就报异常
        throw new IllegalArgumentException("excel文件内容有错误");
    }
}

2.导出Excel文件

/**
 * 导出营业数据报表
 *
 * @param response
 */
public void exportBusinessData(HttpServletResponse response) {
    //1.到数据库中查询营业数据

    //最近30天
    LocalDate begin = LocalDate.now().minusDays(30);
    LocalDate end = LocalDate.now().minusDays(1);

    //查询营业数据
    BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(begin, LocalTime.MIN), LocalDateTime.of(end, LocalTime.MAX));

    //获取excel模版输入流
    InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");
    try {
        //创建excel对象
        XSSFWorkbook excel = new XSSFWorkbook(in);
        //获取表格文件的sheet页
        XSSFSheet sheet = excel.getSheet("Sheet1");
        //2.POI将数据写入excel
        //填充数据---时间
        sheet.getRow(1).getCell(1).setCellValue("时间:" + begin + "至" + end);

        //填充概览数据
        //获取第4行
        XSSFRow row = sheet.getRow(3);
        row.getCell(2).setCellValue(businessData.getTurnover());
        row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
        row.getCell(6).setCellValue(businessData.getNewUsers());
        //获取第5行
        row = sheet.getRow(4);
        row.getCell(2).setCellValue(businessData.getValidOrderCount());
        row.getCell(4).setCellValue(businessData.getUnitPrice());

        //填充明细数据
        for (int i = 0; i < 30; i++) {
            LocalDate date = begin.plusDays(i);
            //获取当天营业数据
            BusinessDataVO nowData = 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(nowData.getTurnover());
            row.getCell(3).setCellValue(nowData.getValidOrderCount());
            row.getCell(4).setCellValue(nowData.getOrderCompletionRate());
            row.getCell(5).setCellValue(nowData.getUnitPrice());
            row.getCell(6).setCellValue(nowData.getNewUsers());
        }

        //获取输出流
        ServletOutputStream out = response.getOutputStream();

        //3.导出excel到客户端浏览器
        excel.write(out);

        //关流
        in.close();
        excel.close();
        out.close();

    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}