多个LIST 转化为 EXCEL 多 sheet

1. excel 工具 方法，自定义其他表格样式，可修改CellStyle

public static void buildExcel(List<Map<String, Object>> list, HSSFWorkbook wb, String sheetName, String[] titleArray, String[] fieldArray, String dest) {

        HSSFSheet sheet = wb.createSheet(sheetName);
        //样式
        //设置默认行高、列宽
        sheet.setDefaultRowHeight((short) 500);
        sheet.setDefaultColumnWidth(30);
        //字体
        HSSFFont font = getFont(wb);
        //标题字体
        HSSFFont titleFont = getWhiteFont(wb);
        titleFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //标题样式
        CellStyle titleStyle = getCellStyle(wb, titleFont, IndexedColors.GREY_25_PERCENT.getIndex());
        //基础样式
        CellStyle style = getCellStyle(wb, font);
        style.setAlignment(HorizontalAlignment.LEFT);

        int firstRowNum = 0;
        if (StringUtils.isNotBlank(dest)) {
            HSSFFont destFont = getRedFont(wb);
            CellStyle destStyle = getCellStyle(wb, destFont);
            destStyle.setAlignment(HorizontalAlignment.LEFT);
            Row rowDest = sheet.createRow(firstRowNum);
            Cell bigDetailTitleCell = rowDest.createCell(0);
            bigDetailTitleCell.setCellStyle(destStyle);
            bigDetailTitleCell.setCellValue(dest);
            sheet.addMergedRegion(new CellRangeAddress(firstRowNum, firstRowNum, 0, fieldArray.length - 1));

            firstRowNum++;
        }

        //第一行，标题
        Row row0 = sheet.createRow(firstRowNum);
        for (int i = 0; i < titleArray.length; i++) {
            Cell cell = row0.createCell(i);
            cell.setCellStyle(titleStyle);
            cell.setCellValue(titleArray[i]);
        }
        //第2行开始填充数据
        for (int i = 0; i < list.size(); i++) {
            //从第2行开始
            Row row = sheet.createRow(i + 1 + firstRowNum);
            for (int j = 0; j < fieldArray.length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(style);
                cell.setCellValue(MapUtil.getStr(list.get(i), fieldArray[j], StringUtils.EMPTY));
            }
        }
    }

2. list 数据填充 HSSFWorkbook ，调用excel 工具 方法

  /**
     *  数据excel构建：不同的list，就再创建一个该方法，设置表头
     *
     * @param wb       Workbook
     * @param loanList 信息
     * @param dest     首栏提示 默认可空
     * @param pageName 底部分页表名称
     */
public static boolean push1(HSSFWorkbook wb, List<Loan> loanList, String dest, String pageName) {
        if (A.isEmpty(loanList)) {
            return false;
        }
        String[] titleArray = new String[]{"融资单号", "客户名称", "资金方", "融资产品", "融资模式", "融资类型", "融资状态", "货柜号", "订单号"};
        String[] fieldArray = new String[]{"loanCode", "customerName", "funderName", "financeProductName", "financeMode", "financeType", "loanStatus",
                "containerCode", "orderRegNo"};
        List<Map<String, Object>> list = new ArrayList<>();

        for (Loan loanInformation : loanList) {
            Map<String, Object> map = BeanUtils.beanToMap(loanInformation);
            list.add(map);
        }

        if (A.isNotEmpty(list)) {
            ExcelUtil.buildExcel(list, wb, pageName, titleArray, fieldArray, dest);
            return true;
        }
        return false;
    }

3.业务接口多list调用

public void moreListToFile(List<ReceiptDot> receiptDotList, List<Loan> loanList, List<Receipt> receiptList) {
        HSSFWorkbook wb = new HSSFWorkbook();
        // 是否有数据需要构建excel推送：一个方法就是一个sheet，可自定义sheet名字
        boolean flag0 = PushOutsideExcelUtil.push1(wb, loanList, "", "融资单");
        //可以自定义写不同的表格式，写入HSSFWorkbook (sheet2)
        boolean flag1 = PushOutsideExcelUtil.push2(wb, receiptList, "", "借据单");
        // (sheet3)
        boolean flag2 =  PushOutsideExcelUtil.push3(wb, receiptDotList, "", "货柜");
         

        String fileName = LocalDate.now() + "测试合并sheet"+ RandomUtil.randomString(4)+".xlsx";
        File file = new File(fileName);
        try {
            //文件写入
            wb.write(file);
  
            //上传文件至指定的文件系统，返回文件key；这里以飞书群为例：文件直接推送至飞书
            String fileKey = feishuMessageService.uploadFile(file, "xls", fileName);
            //飞书群id
            String chatId = "oc_8dd99f716bc153ab962dxxxxxxxx";

            //@群的用户id
            String text="<at user_id=\""+1111+"\"></at>";
            //自定义方法推送飞书
            feishuMessageService.sendGroupMsg(chatId,"text", text);
            //发送excel文件到飞书群
            feishuMessageService.sendGroupMsg(chatId,"file", fileKey);
        } catch (Exception e) {
            log.error("测试合并sheet", e);
        } finally {
            if (file.exists()) {
                boolean deleteFlag = file.delete();
                log.debug("测试合并sheet delete tmp file: {}, {}", fileName, deleteFlag);
            }
        }

    }

下载导出结果：