Excel导出生成多个sheet页

        一段用以在导出操作时生成多个sheet页的代码。

1.controller

 /**
     * 导出方法
     **/
    @ApiOperation(value = "盘点—导出", notes = "盘点—导出")
    @ResponseBody
    @GetMapping(value = "/export", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public String download(@RequestParam(value = "categoryPk") BigInteger inventoryPk,
                           HttpServletRequest request, HttpServletResponse response
    ) throws Exception {

        String fileId = null;
        String extName = ".xls";

        HSSFWorkbook workbook = amsFixInventoryService.exportAction(inventoryPk);
        Date nowTime = new Date(System.currentTimeMillis());
        SimpleDateFormat sdFormatter = new SimpleDateFormat("yyyy-MM-dd HHmmdd");
        String currentDate = sdFormatter.format(nowTime);

        String fileName = new String(("盘点-" + currentDate).getBytes("UTF-8"), "ISO8859-1") + extName;
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();

        workbook.write(response.getOutputStream());

        MongoFile mongoFile = new MongoFile();
        // 创建临时文件
        OutputStream outputStream = response.getOutputStream();
        // 获取生成文件存储至MongoDB中
        mongoFile.setExtName(extName);
        mongoFile.setOriginalName(fileName);
        mongoFile.setContentType("text/html");

        String ops  =  outputStream.toString();
        ByteArrayInputStream bais = new ByteArrayInputStream(ops.getBytes());
        mongoFile.setFile(bais);
        this.mongoKit.saveFile(mongoFile);
        // 使用mongoKit保存文件到数据库中，返回一个fileId，然后根据fileId获取到具体的文件。关闭文件流
        mongoFile.getFile().close();
        fileId = mongoFile.getPk();
        Assert.assertNotNull(mongoFile);
        return fileId;
    }

2.service

    HSSFWorkbook exportAction(BigInteger inventoryPk);

3.serviceImpl

    @SneakyThrows
    @Override
    public HSSFWorkbook exportAction(BigInteger inventoryPk) {
        String typeName = "";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle style_title = workbook.createCellStyle();
        style_title.setAlignment(HorizontalAlignment.CENTER);
        style_title.setWrapText(true);
        /**
         * sheet页1 概况
         **/
        typeName = "概况";
        HSSFSheet sheet = workbook.createSheet(typeName);

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd"));

        Record overview = amsFixInventoryMapper.overview(PCKit.getDomainName(), inventoryPk).get(0);
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 20 * 256);

        //第一行
        HSSFRow row0 = sheet.createRow(0);
        HSSFCell cell01;
        cell01 = row0.createCell(0);
        cell01.setCellValue("盘点报告");
        cell01.setCellStyle(style_title);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

        //第二行
        HSSFRow row1 = sheet.createRow(1);
        HSSFCell cell11;
        cell11 = row1.createCell(0);
        cell11.setCellValue("1.基本信息");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));

        //第三行
        HSSFRow row2 = sheet.createRow(2);
        HSSFCell cell21;
        cell21 = row2.createCell(0);
        cell21.setCellValue("拟定编写人:");
        HSSFCell cell22;
        cell22 = row2.createCell(1);
        cell22.setCellValue(overview.get("insertUserName").toString());
//            第四行
        HSSFRow row3 = sheet.createRow(3);
        HSSFCell cell31;
        cell31 = row3.createCell(0);
        cell31.setCellValue("盘点开始时间:");
        HSSFCell cell32;
        cell32 = row3.createCell(1);
        cell32.setCellValue(overview.get("beginDate").toString());
//            第五行
        HSSFRow row4 = sheet.createRow(4);
        HSSFCell cell41;
        cell41 = row4.createCell(0);
        cell41.setCellValue("盘点开始时间:");
        HSSFCell cell42;
        cell42 = row4.createCell(1);
        cell42.setCellValue(overview.get("endDate").toString());

        //            第六行
        HSSFRow row5 = sheet.createRow(5);
        HSSFCell cell51;
        cell51 = row5.createCell(0);
        cell51.setCellValue("2、盘点结果信息汇总");
        sheet.addMergedRegion(new CellRangeAddress(5, 5, 0, 3));

        //            第七行
        HSSFRow row6 = sheet.createRow(6);
        HSSFCell cell61;
        cell61 = row6.createCell(0);
        cell61.setCellValue("状态");
        HSSFCell cell62;
        cell62 = row6.createCell(1);
        cell62.setCellValue("数量");
        HSSFCell cell63;
        cell63 = row6.createCell(2);
        cell63.setCellValue("原值金额（元）");
        HSSFCell cell64;
        cell64 = row6.createCell(3);
        cell64.setCellValue("备注");
        //            第八行
        HSSFRow row7 = sheet.createRow(7);
        HSSFCell cell71;
        cell71 = row7.createCell(0);
        cell71.setCellValue("应盘资产");
        HSSFCell cell72;
        cell72 = row7.createCell(1);
        cell72.setCellValue(overview.get("shouldNumber").toString());
        HSSFCell cell73;
        cell73 = row7.createCell(2);
        cell73.setCellValue(overview.get("shouldMoney").toString());
        HSSFCell cell74;
        cell74 = row7.createCell(3);
        cell74.setCellValue(overview.get("note").toString());
        //            第九行
        HSSFRow row8 = sheet.createRow(8);
        HSSFCell cell81;
        cell81 = row8.createCell(0);
        cell81.setCellValue("正常资产");
        HSSFCell cell82;
        cell82 = row8.createCell(1);
        cell82.setCellValue(overview.get("normalNumber").toString());
        HSSFCell cell83;
        cell83 = row8.createCell(2);
        cell83.setCellValue(overview.get("normalMoney").toString());
        HSSFCell cell84;
        cell84 = row8.createCell(3);
        cell84.setCellValue(overview.get("note").toString());

        //            第十行
        HSSFRow row9 = sheet.createRow(9);
        HSSFCell cell91;
        cell91 = row9.createCell(0);
        cell91.setCellValue("盘盈资产");
        HSSFCell cell92;
        cell92 = row9.createCell(1);
        cell92.setCellValue(overview.get("surplusNumber").toString());
        HSSFCell cell93;
        cell93 = row9.createCell(2);
        cell93.setCellValue(overview.get("surplusMoney").toString());
        HSSFCell cell94;
        cell94 = row9.createCell(3);
        cell94.setCellValue(overview.get("note").toString());

        //            第十一行
        HSSFRow row10 = sheet.createRow(10);
        HSSFCell cell101;
        cell101 = row10.createCell(0);
        cell101.setCellValue("盘亏资产");
        HSSFCell cell102;
        cell102 = row10.createCell(1);
        cell102.setCellValue(overview.get("missingNumber").toString());
        HSSFCell cell103;
        cell103 = row10.createCell(2);
        cell103.setCellValue(overview.get("missingMoney").toString());
        HSSFCell cell104;
        cell104 = row10.createCell(3);
        cell104.setCellValue(overview.get("note").toString());

        //            第十二行
        HSSFRow row11 = sheet.createRow(11);
        HSSFCell cell111;
        cell111 = row11.createCell(0);
        cell111.setCellValue("实盘资产合计");
        HSSFCell cell112;
        cell112 = row11.createCell(1);
        cell112.setCellValue(overview.get("actualNumber").toString());
        HSSFCell cell113;
        cell113 = row11.createCell(2);
        cell113.setCellValue(overview.get("actualMoney").toString());
        HSSFCell cell114;
        cell114 = row11.createCell(3);
        cell114.setCellValue(overview.get("note").toString());
        /**
         * sheet页2 资产盘点详情
         **/
        typeName = "资产盘点详情";
        HSSFSheet sheet2 = workbook.createSheet(typeName);


        List<Record> assetInventoryDetails = amsFixInventoryMapper.assetInventoryDetails(PCKit.getDomainName(), inventoryPk);
        HSSFRow row = sheet2.createRow(0);
        sheet2.setColumnWidth(0, 20 * 256);
        sheet2.setColumnWidth(1, 20 * 256);
        sheet2.setColumnWidth(2, 20 * 256);
        sheet2.setColumnWidth(3, 20 * 256);
        sheet2.setColumnWidth(4, 20 * 256);
        sheet2.setColumnWidth(5, 20 * 256);
        sheet2.setColumnWidth(6, 20 * 256);
        sheet2.setColumnWidth(7, 20 * 256);
        sheet2.setColumnWidth(8, 20 * 256);
        sheet2.setColumnWidth(9, 20 * 256);
        sheet2.setColumnWidth(10, 20 * 256);

        sheet2.setColumnWidth(11, 20 * 256);
        sheet2.setColumnWidth(12, 20 * 256);
        sheet2.setColumnWidth(13, 20 * 256);
        sheet2.setColumnWidth(14, 20 * 256);
        sheet2.setColumnWidth(15, 20 * 256);

        Cell cell200;
        cell200 = row.createCell(0);
        cell200.setCellValue("资产编码(assetCode)");

        Cell cell201;
        cell201 = row.createCell(1);
        cell201.setCellValue("资产名称(assetName)");

        Cell cell202;
        cell202 = row.createCell(2);
        cell202.setCellValue("所属分类(category_name)");

        Cell cell203;
        cell203 = row.createCell(3);
        cell203.setCellValue("状态(assetStatus)");

        Cell cell204;
        cell204 = row.createCell(4);
        cell204.setCellValue("所属资产台账编号(financial_no)");

        Cell cell205;
        cell205 = row.createCell(5);
        cell205.setCellValue("描述(describeName)");

        Cell cell206;
        cell206 = row.createCell(6);
        cell206.setCellValue("使用人(name)");

        Cell cell207;
        cell207 = row.createCell(7);
        cell207.setCellValue("使用人工号(username)");

        Cell cell208;
        cell208 = row.createCell(8);
        cell208.setCellValue("使用人手机号(phone)");


        Cell cell209;
        cell209 = row.createCell(9);
        cell209.setCellValue("存放地点(location)");

        Cell cell2010;
        cell2010 = row.createCell(10);
        cell2010.setCellValue("最新发现地点(place_name)");

        Cell cell2011;
        cell2011 = row.createCell(11);
        cell2011.setCellValue("盘点状态(status)");

        Cell cell2012;
        cell2012 = row.createCell(12);
        cell2012.setCellValue("盘点时间(created_at)");

        Cell cell2013;
        cell2013 = row.createCell(13);
        cell2013.setCellValue("盘点负责人(handler_name)");


        Cell cell2014;
        cell2014 = row.createCell(14);
        cell2014.setCellValue("盘点方式(inventory_method)");


        Cell cell2015;
        cell2015 = row.createCell(15);
        cell2015.setCellValue("验证方式(verification_method)");

        Cell cell2016;
        cell2016 = row.createCell(16);
        cell2016.setCellValue("标识码(code)");

        Cell cell2017;
        cell2017 = row.createCell(17);
        cell2017.setCellValue("标签状态(label_status)");
        for (int i = 0; i < assetInventoryDetails.size(); i++) {
            Record r = assetInventoryDetails.get(i);
            Row row21 = sheet2.createRow(i + 1);
            cell200 = row21.createCell(0);
            cell201 = row21.createCell(1);
            cell202 = row21.createCell(2);
            cell203 = row21.createCell(3);

            cell204 = row21.createCell(4);
            cell205 = row21.createCell(5);
            cell206 = row21.createCell(6);
            cell207 = row21.createCell(7);
            cell208 = row21.createCell(8);
            cell209 = row21.createCell(9);
            cell2010 = row21.createCell(10);
            cell2011 = row21.createCell(11);

            cell2012 = row21.createCell(12);
            cell2013 = row21.createCell(13);
            cell2014 = row21.createCell(14);
            cell2015 = row21.createCell(15);

            cell2016 = row21.createCell(16);
            cell2017 = row21.createCell(17);

            cell200.setCellValue(r.getStr("amsCode"));
            cell201.setCellValue(r.getStr("amsName"));
            cell202.setCellValue(r.getStr("type"));
            cell203.setCellValue(r.getStr("serviceConditionName"));
            cell204.setCellValue(r.getStr("orderNo"));
            cell205.setCellValue(r.getStr("note"));
            cell206.setCellValue(r.getStr("userName"));
            cell207.setCellValue(r.getStr("userName"));
            cell208.setCellValue(r.getStr("userPhone"));
            cell209.setCellValue(r.getStr("oldSpaceName"));
            cell2010.setCellValue(r.getStr("newSpaceName"));
            cell2011.setCellValue(r.getStr("checkStatusName"));
            cell2012.setCellValue(r.getStr("inventoryDate"));
            cell2013.setCellValue(r.getStr("inventoryUserName"));
            cell2014.setCellValue(r.getStr("limitUserTypeName"));
            cell2015.setCellValue(r.getStr("generateTypeName"));
            cell2016.setCellValue(r.getStr("statusName"));
            cell2017.setCellValue(r.getStr("tagStatusStatus"));
        }
        /**
         * sheet页3 资产盘点明细表
         **/
        typeName = "资产盘点明细表";
        HSSFSheet sheet3 = workbook.createSheet(typeName);
        List<Record> inventoryDetails = amsFixInventoryMapper.inventoryDetails(PCKit.getDomainName(), inventoryPk);
        HSSFRow row30 = sheet3.createRow(0);
        sheet3.setColumnWidth(0, 20 * 256);
        sheet3.setColumnWidth(1, 20 * 256);
        sheet3.setColumnWidth(2, 20 * 256);
        sheet3.setColumnWidth(3, 20 * 256);
        sheet3.setColumnWidth(4, 20 * 256);
        sheet3.setColumnWidth(5, 20 * 256);
        sheet3.setColumnWidth(6, 20 * 256);
        sheet3.setColumnWidth(7, 20 * 256);

        Cell cell300;
        cell300 = row30.createCell(0);
        cell300.setCellValue("序号");

        Cell cell301;
        cell301 = row30.createCell(1);
        cell301.setCellValue("资产台账编号");

        Cell cell302;
        cell302 = row30.createCell(2);
        cell302.setCellValue("总价值");

        Cell cell303;
        cell303 = row30.createCell(3);
        cell303.setCellValue("资产存放地区");

        Cell cell304;
        cell304 = row30.createCell(4);
        cell304.setCellValue("资产存放地点");

        Cell cell305;
        cell305 = row30.createCell(5);
        cell305.setCellValue("录入资产明细数");

        Cell cell306;
        cell306 = row30.createCell(6);
        cell306.setCellValue("实际盘点资产数");

        Cell cell307;
        cell307 = row30.createCell(7);
        cell307.setCellValue("盘亏数量");
        for (int i = 0; i < inventoryDetails.size(); i++) {
            Record r = inventoryDetails.get(i);
            Row row31 = sheet3.createRow(i + 1);
            cell300 = row31.createCell(0);
            cell301 = row31.createCell(1);
            cell302 = row31.createCell(2);
            cell303 = row31.createCell(3);
            cell304 = row31.createCell(4);
            cell305 = row31.createCell(5);
            cell306 = row31.createCell(6);
            cell307 = row31.createCell(7);

            cell300.setCellValue(r.getInt("number"));
            cell301.setCellValue(r.getStr("orderNo"));
            cell302.setCellValue(r.getStr("orderNo"));
            cell303.setCellValue(r.getStr("newSpaceName"));
            cell304.setCellValue(r.getStr("newSpaceName"));
            cell305.setCellValue(r.getStr("shouldNumber"));
            cell306.setCellValue(r.getStr("actualNumber"));
            cell307.setCellValue(r.getStr("missingNumber"));
        }
        return workbook;
    }