数据导出到写好的excel模板中

最新推荐文章于 2024-09-09 12:20:13 发布

原创最新推荐文章于 2024-09-09 12:20:13 发布 · 142 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了如何通过Spring MVC控制器和Service，利用Apache POI操作Excel，将查询结果以特定报告年份为标识导出至Excel文件。涉及数据查询、模板读取、数据映射及文件下载功能。

实现查询数据导出到excel中

- controller
- service

controller

@ApiOperation(value = "excel", httpMethod = "GET", notes = "excel", produces="application/octet-stream")
    @GetMapping(value = "/excel/{reportYear}")
    public void exportExcel(@PathVariable Integer reportYear, HttpServletResponse response) {
        service.exportExcel(reportYear, response);
    }

service

@Override
    public void exportExcel(Integer reportYear, HttpServletResponse response) {
    	//查询需要导出到excel的数据用ResponseDTO接收
        ArrayList<ResponseExcelExportDTO> dataList = baseService.selectReportRecordPageByReportYear(reportYear);
        try {
        	//文件名
            String fileName = reportYear+".xlsx";
            //读取已经存在的excel模板并获取输入流
            ClassPathResource classPathResource = new ClassPathResource("templates/xxxx.xlsx");
            InputStream inputStream = classPathResource.getInputStream();
			
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            // 获取DTO所有属性
            Class<ResponseExcelExportDTO> responseExcelDTOClass = ResponseExcelExportDTO.class;
            Field[] field = responseExcelDTOClass.getDeclaredFields();

            // 写入数据
            for (int i = 0; i < dataList.size(); i++) {
                //从excel模板表中第七行开始写
                Row row1 = sheet.createRow(i + 6);
                if (row1 != null) {
                    for (int j = 0; j < field.length; j++) {
                        Cell cell1 = row1.createCell(j);
                        if (cell1 != null) {
                            Field f = field[j];
                            f.setAccessible(true); // 设置些属性是可以访问的

                            Object value = null;// 得到此属性的值
                            value = f.get(dataList.get(i));

                            if (value != null) {
                                if (f.getName().endsWith("Name")) {
                                    cell1.setCellValue((String.valueOf(value)));
                                } else if (Integer.parseInt(String.valueOf(value)) == 0) {
                                    cell1.setCellValue("否");
                                } else if (Integer.parseInt(String.valueOf(value)) == 1) {
                                    cell1.setCellValue("是");
                                } else {
                                    break;
                                }
                            }
                        }
                    }
                }
            }

            ServletOutputStream outputStream = response.getOutputStream();
            response.setCharacterEncoding("utf-8");
            // 设置强制下载不打开
            response.setContentType("application/force-download");
            // 设置文件名
            String fileNameCode = URLEncoder.encode(fileName, "UTF8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
            response.setHeader("filename", fileNameCode);
            response.setHeader("Access-Control-Expose-Headers", "filename");

            workbook.write(outputStream);
            outputStream.close();
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }