如何通过XSSFWorkbook文件流数组，将其合并为一个XSSFWorkbook文件流并发送给前端

        最近项目上遇到了一个问题，如何将多个XSSFWorkbook合并为一个excel文件，最终找到的方法是，使用excel导入导出 POI版本为5.2.2版本进行操作，具体操作步骤如下：

        

public void exportExcelMerge(List<String> statementIdArray, List<DataSetQueryVo> queryParam, HttpServletRequest request, HttpServletResponse response, String authorization, Map<String, String> chartImages,List<Map<String, String>> statementNameArray) throws Exception {
            // 新建一个新的XSSFWorkbook
        try (XSSFWorkbook mergedWorkbook = new XSSFWorkbook()) {
            for (String statementId : statementIdArray) {
// 这里是根据我自己的报表，获取到他的XSSFWorkbook文件流，你们可以改为自己的
                try (XSSFWorkbook workbook = byteArray(Collections.singletonList(statementId), queryParam, request, response, authorization, chartImages).get(0)) {
// 读取文件流中的每一个sheet
                    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                        Sheet sourceSheet = workbook.getSheetAt(i);
                        // 遍历 List<Map>，查找对应的值
                        String sheetName = "";
                        for (Map<String, String> map : statementNameArray) {
                            if (map.containsKey(statementId)) {
                                sheetName = map.get(statementId);
                                break;
                            }
                        }
                        // 合并工作蒲
                        if(workbook.getNumberOfSheets()>1){
                            copySheet(sourceSheet, mergedWorkbook, sheetName+i);
                        }else {
                            copySheet(sourceSheet, mergedWorkbook, sheetName);
                        }
                    }
                }
            }

            // 将工作流输入
            try (FileOutputStream fos = new FileOutputStream("merged_excel.xlsx")) {
                mergedWorkbook.write(fos);
            }

            // 修改名字发送给前端
            setResponseHeader(response, "merged_excel.xlsx");
            mergedWorkbook.write(response.getOutputStream());
        }
    }

    private void copySheet(Sheet sourceSheet, Workbook destWorkbook, String sheetName) {
        Sheet newSheet = destWorkbook.createSheet(sheetName);

        // 复制样式和内容
        for (int rowIndex = sourceSheet.getFirstRowNum(); rowIndex <= sourceSheet.getLastRowNum(); rowIndex++) {
            Row sourceRow = sourceSheet.getRow(rowIndex);
            Row newRow = newSheet.createRow(rowIndex);
            if (sourceRow != null) {
                copyRow(sourceRow, newRow, destWorkbook);
            }
        }

        // 复制合并单元格
        for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sourceSheet.getMergedRegion(i);
            CellRangeAddress newMergedRegion = new CellRangeAddress(
                    mergedRegion.getFirstRow(), mergedRegion.getLastRow(),
                    mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
            newSheet.addMergedRegion(newMergedRegion);
        }

        // 复制打印设置
        PrintSetup ps = sourceSheet.getPrintSetup();
        PrintSetup newPs = newSheet.getPrintSetup();
        newPs.setPaperSize(ps.getPaperSize());
        newPs.setFitWidth(ps.getFitWidth());
        newPs.setFitHeight(ps.getFitHeight());

        // 复制列宽
        for (int columnIndex = 0; columnIndex < sourceSheet.getRow(0).getLastCellNum(); columnIndex++) {
            newSheet.setColumnWidth(columnIndex, sourceSheet.getColumnWidth(columnIndex));
        }
    }

    private void copyRow(Row sourceRow, Row newRow, Workbook destWorkbook) {
        for (int i = sourceRow.getFirstCellNum(); i < sourceRow.getLastCellNum(); i++) {
            Cell sourceCell = sourceRow.getCell(i);
            if (sourceCell != null) {
                Cell newCell = newRow.createCell(i);
                CellStyle newCellStyle = destWorkbook.createCellStyle();
                newCellStyle.cloneStyleFrom(sourceCell.getCellStyle());

                // 复制自动换行和单元格截断样式
                newCellStyle.setWrapText(true);
                if (sourceCell.getCellStyle().getShrinkToFit()) {
                    newCellStyle.setShrinkToFit(sourceCell.getCellStyle().getShrinkToFit());
                }


                newCell.setCellStyle(newCellStyle);

                switch (sourceCell.getCellType()) {
                    case STRING:
                        newCell.setCellValue(sourceCell.getStringCellValue());
                        break;
                    case BOOLEAN:
                        newCell.setCellValue(sourceCell.getBooleanCellValue());
                        break;
                    case NUMERIC:
                        newCell.setCellValue(sourceCell.getNumericCellValue());
                        break;
                    case FORMULA:
                        // 复制公式以及单元格引用
                        String formula = sourceCell.getCellFormula();
                        newCell.setCellFormula(adjustFormula(formula, sourceRow.getRowNum(), newRow.getRowNum()));
                        break;
                    default:
                        break;
                }
            }
        }
    }

    private static String adjustFormula(String formula, int sourceRowIndex, int destRowIndex) {
        return formula.replace(String.valueOf(sourceRowIndex + 1), String.valueOf(destRowIndex + 1));
    }

    private void setResponseHeader(HttpServletResponse response, String filename) throws IOException {
        response.setHeader(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_OCTET_STREAM_VALUE);
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        response.setCharacterEncoding("UTF-8");
    }

目前已经实现的功能，复制样式与内容、合并单元格、打印设置、列宽、自动换行以及单元格截断、公式以及单元格引用。

应该能满足日常所需，并且支持中文。但是会存在一些小问题，比如单元格宽度有的时候可能复制的比原来的窄，目前还没解决。希望以后能有大手子进行一些优化。