Java 导出多个sheet的excel，每个sheet页数据动态添加，transformMultipleSheetsList方法的使用

1. 导入依赖

        <dependency>
            <groupId>net.sf.jxls</groupId>
            <artifactId>jxls-core</artifactId>
            <version>1.0.6</version>
        </dependency>

2. 创建数据

private static List<Company> getData() {
        // 公司对账信息
        List<Company> companyVOList = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            Company company = new Company();
            company.setCompanyName("腾讯："+i);
            company.setAccountNo("8047226100"+i);

            List<AccountAssets> accountAssetsList = new ArrayList<>();
            for (int j = 0; j < 4; j++) {
                AccountAssets assets = new AccountAssets();
                assets.setCurrency("HKD");
                assets.setSRedeemedDeposit("111"+i);
                assets.setUnavailableCash("222"+i);
                assets.setNoSettlementDateBalance("333"+i);
                assets.setSettlementDateBalance("444"+i);
                assets.setTradeDateBalance("555"+i);
                accountAssetsList.add(assets);
            }
            company.setAccountAssetsList(accountAssetsList);


            List<AccountPosition>  accountPositionList = new ArrayList<>();
            for (int j = 0; j < 6; j++) {
                AccountPosition accountPosition = new AccountPosition();
                accountPosition.setStockCode("9988"+i);
                accountPosition.setAvailableQuantity("666"+i);
                accountPosition.setMarketValue("777"+i);
                accountPosition.setQuantity("888"+i);
                accountPosition.setSharesOnHand("999"+i);
                accountPosition.setUnavailableQuantity("000"+i);
                accountPosition.setStockOnHold("9999"+i);
                accountPositionList.add(accountPosition);
            }
            company.setAccountPositionList(accountPositionList);

            companyVOList.add(company);
        }
        return companyVOList;
    }

4. 核心代码

    private String testGenerateExcelFile(List<CompanyInfo> companyInfoList) throws IOException{
        String filePath = "";
        InputStream inputStream = null;
        ByteArrayOutputStream outputStream = null;
        // 创建工作簿
        Workbook workbook = null;
        try {
            // 读取文件
            inputStream = new FileInputStream(new File("D:\\对账邮件\\模板\\company_reconciliation_test.xlsx"));
            List<String> sheetNameList = new ArrayList<>();
            List<CompanyCheckInfo> companyList = new ArrayList<>();
            for (CompanyInfo companyInfo : companyInfoList) {
                sheetNameList.add(companyInfo.getCompanyName());
                CompanyCheckInfo checkInfo = new CompanyCheckInfo();
                checkInfo.setCompanyName(companyInfo.getCompanyName());
                checkInfo.setAccountNo(companyInfo.getAccountNo());
                checkInfo.setAccountStatus(companyInfo.getAccountStatus());
                checkInfo.setCheckResult(companyInfo.getCheckResult());
            }
            XLSTransformer xlsTransformer = new XLSTransformer();
            Map<String,Object> map = new HashMap<>();
            map.put("company",companyList);
            xlsTransformer.transformXLS(inputStream,map);
            workbook = xlsTransformer.transformMultipleSheetsList(inputStream,companyInfoList,sheetNameList,"company",new HashMap<>(),1);
            // 输出文件到本地
            long fileName = System.currentTimeMillis();
            String outPath = "D:\\对账邮件\\输出文件\\"+fileName+".xlsx";
            FileOutputStream out = new FileOutputStream(outPath);
            workbook.write(out);

        }catch (Exception e){
            e.printStackTrace();
        }
        return filePath;
    }

6. transformMultipleSheetsList(InputStream is, List objects, List newSheetNames, String beanName, Map beanParams, int startSheetNum)方法解释
   is：读取模板文件所生成的流
   objects：需要填充到excel中的数据
   newSheetNames：每个sheet页的名称
   beanName：模板中的映射名称
   beanParams：
   startSheetNum：表示从第几个sheet开始
   

7. 模板内容
   

8. 结果