Springboot+poi实现浏览器下载excel文件

1、引入依赖包

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.0</version>
        </dependency>

2、控制层

 @ApiOperation(value = "导出项目信息表",  produces = "application/octet-stream")
    @PostMapping(value = "/downloadProjectExcel")
    public ResponseEntity<byte[]> downloadProjectExcel (@RequestBody List<String> ids) throws Exception {
        List<ProjectHtHkMo> list =  projectService.createProjectHtHkMosList(ids);
        ResponseEntity<byte[]> responseEntity = projectService.createProjectExcel(list);
        return responseEntity;
    };

3、接口层

 ResponseEntity<byte[]> createProjectExcel(List<ProjectHtHkMo> list) throws IOException;

4、实现层

 public ResponseEntity<byte[]> createProjectExcel(List<ProjectHtHkMo> list) throws IOException {
        File file = new File("D:\\projectjianshi\\项目信息表.xls");
        String name = file.getName();
        int c = 2+list.size();
        if (file.exists()) {
            file.delete();
        }
        Workbook sheets = new HSSFWorkbook();
        Sheet sheet = sheets.createSheet();
        sheets.setSheetName(0,"项目回款详情");
        CellStyle headCellStyle = sheets.createCellStyle();//创建单元格样式对象
        headCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        //创建第一行
        Row row = sheet.createRow(0);
        for (int i = 0; i < 40; i++) {
            Cell cell = row.createCell(i);
            if(i==0){
                cell.setCellValue("合同信息");
                cell.setCellStyle(headCellStyle);
            }
            if (i == 9) {
                cell.setCellValue("项目信息");
                cell.setCellStyle(headCellStyle);
            }
            if (i == 21) {
                cell.setCellValue("回款信息");
                cell.setCellStyle(headCellStyle);
            }
        }
        //第二行
        Row row2 = sheet.createRow(1);
        //合同信息标题
        row2.setRowStyle(headCellStyle);
        row2.createCell(0).setCellValue("合同编号");
        row2.createCell(1).setCellValue("合同简称");
        row2.createCell(2).setCellValue("合同签订法人");
        row2.createCell(3).setCellValue("项目利润率");
        row2.createCell(4).setCellValue("合同额");
        row2.createCell(5).setCellValue("税率");
        row2.createCell(6).setCellValue("不含税金额");
        row2.createCell(7).setCellValue("合同签订时间");
        row2.createCell(8).setCellValue("市场区域");
        //项目信息标题
        row2.createCell(9).setCellValue("项目名称");
        row2.createCell(10).setCellValue("项目状态");
        row2.createCell(11).setCellValue("所属公司");
        row2.createCell(12).setCellValue("所属BU");
        row2.createCell(13).setCellValue("所属部门");
        row2.createCell(14).setCellValue("客户名称");
        row2.createCell(15).setCellValue("项目立项时间");
        row2.createCell(16).setCellValue("项目交付时间");
        row2.createCell(17).setCellValue("项目实际交付时间");
        row2.createCell(18).setCellValue("项目负责人");
        row2.createCell(19).setCellValue("项目财务经理");
        row2.createCell(20).setCellValue("项目客户经理");
        //回款信息表
        row2.createCell(21).setCellValue("年度合同应回款总计");
        row2.createCell(22).setCellValue("年度实际已回款总计");
        row2.createCell(23).setCellValue("年度实际回款率");
        int d1 = 23;
        List<HkMo> hks = list.get(0).getHks();
        for (HkMo hk : hks) {
            row2.createCell(d1++).setCellValue(hk.getHklx());
        row2.createCell(d1++).setCellValue("预计回款金额");
        row2.createCell(d1++).setCellValue("实际已回款");
        row2.createCell(d1++).setCellValue("实际回款时间");
        row2.createCell(d1++).setCellValue("当期超期未回");
        }
        //循环添加内容
        for (int i = 2; i < c; i++) {
            Row row1 = sheet.createRow(i);
            row1.setRowStyle(headCellStyle);
            int f = i-2;
            ProjectHtHkMo projectHtHkMo = list.get(f);
            row1.createCell(0).setCellValue(projectHtHkMo.getHtbh());
            row1.createCell(1).setCellValue(projectHtHkMo.getHtName());
            row1.createCell(2).setCellValue(projectHtHkMo.getHtqdfr());
            row1.createCell(3).setCellValue(projectHtHkMo.getLrl());
            row1.createCell(4).setCellValue(projectHtHkMo.getHtprice());
            row1.createCell(5).setCellValue(projectHtHkMo.getSl());
            row1.createCell(6).setCellValue(projectHtHkMo.getNhsPrice());
            row1.createCell(7).setCellValue(projectHtHkMo.getHtqdTime());
            row1.createCell(8).setCellValue(projectHtHkMo.getMarketArea());
            //项目信息
            row1.createCell(9).setCellValue(projectHtHkMo.getProjectname());
            row1.createCell(10).setCellValue(projectHtHkMo.getStatus());
            row1.createCell(11).setCellValue(projectHtHkMo.getBelongsgs());
            row1.createCell(12).setCellValue(projectHtHkMo.getBelongsbu());
            row1.createCell(13).setCellValue(projectHtHkMo.getBelongsbm());
            row1.createCell(14).setCellValue(projectHtHkMo.getCustomerunit());
            row1.createCell(15).setCellValue(projectHtHkMo.getInitiationtime());
            row1.createCell(16).setCellValue(projectHtHkMo.getCompletiontime());
            row1.createCell(17).setCellValue(projectHtHkMo.getSijftime());
            row1.createCell(18).setCellValue(projectHtHkMo.getProjectleader());
            row1.createCell(19).setCellValue(projectHtHkMo.getFinancemanager());
            row1.createCell(20).setCellValue(projectHtHkMo.getAccountmanager());
            row1.createCell(21).setCellValue(projectHtHkMo.getHks().get(0).getYearTotalTrice());
            row1.createCell(22).setCellValue(projectHtHkMo.getHks().get(0).getYearsjtotalprice());
            row1.createCell(23).setCellValue(projectHtHkMo.getHks().get(0).getYearsjhkl());
            List<HkMo> hks2 = projectHtHkMo.getHks();
//            HkMo hk = hks.get(0);
            int b= 23;
            for (HkMo hk : hks2) {
                row1.createCell(b++).setCellValue(hk.getFirsttime());
                row1.createCell(b++).setCellValue(hk.getYjhkprice());
                row1.createCell(b++).setCellValue(hk.getSjhkprice());
                row1.createCell(b++).setCellValue(hk.getSihktime());
                if (hk.getNoreturnprice() == null) {
                    row1.createCell(b++).setCellValue(0.00);
                }else {
                    row1.createCell(b++).setCellValue(hk.getNoreturnprice());
                }
            }
        }
        int i = sheet.addMergedRegion(new CellRangeAddress(0,0,0,8));
        sheet.addMergedRegion(new CellRangeAddress(0,0,9,20));
        sheet.addMergedRegion(new CellRangeAddress(0,0,21,48));
        OutputStream os = null;
        os = new FileOutputStream(file);
        sheets.write(os);
        os.flush();
        os.close();
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        //headers.setContentDispositionFormData("attachment",StringUtils.newStringIso8859_1(StringUtils.getBytesUtf8(file.getName()))));
        headers.setContentDispositionFormData("attachment", name=java.net.URLEncoder.encode(file.getName(), "UTF-8"));
        return new ResponseEntity<byte[]>(Files.toByteArray(file),headers, HttpStatus.CREATED);
    }

5、测试

6、成功