java代码利用Excel(poi)动态导出多sheet页

java代码利用Excel(poi)动态导出多sheet页

多张excel合并为一张excel，以多sheet页的形式导出

根据项目中的业务需求，需要把多张的excel文件合并为一张excel以多sheet页的形式导出到本地。

获取所有的excel文件，把它们组装为List

1.能够直接获取到单个的excel文件更好，如果不能获取，就想办法获取excel文件的字符串吧,我们把这个excel文件的字符串定义为

String **excelContent** =
                        bizReportService.getReportContent(reportId, branchNo, coopBranchNo, roleId, longPeriodDate);
                        
 // 将excel文件的字符串解析为字节数组
                BASE64Decoder decoder = new BASE64Decoder();
                byte[] excelByte = decoder.decodeBuffer(excelContent);    // 字节数组输入流
                ByteArrayInputStream excelInputStream = new 								ByteArrayInputStream(excelByte);    
// 转换为sheet对象
 List<XSSFSheet> sheets = this.getBankListByExcel(excelInputStream);
 // 对应的转换方法
 public List<XSSFSheet> getBankListByExcel(ByteArrayInputStream in) throws Exception {
        // 创建Excel工作薄
        XSSFWorkbook work = new XSSFWorkbook(in);
        XSSFSheet sheet = null;
        // 遍历Excel中所有的sheet
        List<XSSFSheet> sheets = new ArrayList<>();
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            sheets.add(sheet);
        }
        in.close();
        return sheets;
    }
// 遍历得到的  XSSFSheet 集合  创建新的excel文件
 XSSFSheet srcsheet = null;
            for(int i=0;i<sheetList.size();i++){
                srcsheet = sheetList.get(i);
                String sheetName = srcsheet.getSheetName();
                XSSFSheet descsheet = (XSSFSheet) excelWorkBook.createSheet();
                    excelWorkBook.setSheetName(count, sheetName);
                count++;
                copySheets(descsheet, srcsheet, true);
            }
            // 将二进制excel文件写入excel poi实体类
            excelWorkBook.write(outputStream);     
记得关闭用过的各种流 
									 excelWorkBook.close();
 									outputStream.flush();
            						outputStream.close();     
    以上代码中用到的各种方法实现
     /**
     * @param newSheet
     * @param sheet
     * @param copyStyle
     */
    public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
        int maxColumnNum = 0;
        Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;
        XSSFRow srcRow ;
        XSSFRow destRow ;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            srcRow = sheet.getRow(i);
            destRow = newSheet.createRow(i);
            if (srcRow != null && srcRow.getPhysicalNumberOfCells() != 0) {
                copyRow(sheet, newSheet, srcRow, destRow, styleMap);
                if (srcRow.getLastCellNum() > maxColumnNum) {
                    maxColumnNum = srcRow.getLastCellNum();
                }
            }
        }

        Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
//        int deltaRows = destRow.getRowNum() - srcRow.getRowNum();
        // 获取sheet页中所有的合并单元格对象
        List<CellRangeAddress> mergedRegionList =
//                getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());
                getMergedRegion(sheet);
        if (mergedRegionList != null && mergedRegionList.size()>0) {
            for (CellRangeAddress mergedRegion : mergedRegionList) {
                CellRangeAddress newMergedRegion = new CellRangeAddress(
                        mergedRegion.getFirstRow() ,
                        mergedRegion.getLastRow(), mergedRegion
                        .getFirstColumn(), mergedRegion
                        .getLastColumn());
                CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    newSheet.addMergedRegion(wrapper.range);
                }
            }
        }
        // 设置列宽
        for (int i = 0; i <= maxColumnNum; i++) {
            newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
        }
    } 
    //导出多个sheet页，复制两个sheet页间的样式使用
public  class CellRangeAddressWrapper implements Comparable<CellRangeAddressWrapper>  {

    public CellRangeAddress range;
    public CellRangeAddressWrapper(CellRangeAddress theRange) {
        this.range = theRange;
    }
    public int compareTo(CellRangeAddressWrapper craw) {
        if (range.getFirstColumn() < craw.range.getFirstColumn()&& range.getFirstRow() < craw.range.getFirstRow()) {
            return -1;
        } else if (range.getFirstColumn() == craw.range.getFirstColumn()&& range.getFirstRow() == craw.range.getFirstRow()) {
            return 0;
        } else {
            return 1;
        }
    }
}  
/**
     * @param newMergedRegion
     * @param mergedRegions
     * @return
     */
    private static boolean isNewMergedRegion(CellRangeAddressWrapper newMergedRegion,
                                             Set<CellRangeAddressWrapper> mergedRegions) {
        boolean bool = mergedRegions.contains(newMergedRegion);
        return !bool;
    }

 /**
     * 复制并合并单元格
     *
     * @param srcSheet
     * @param destSheet
     * @param srcRow
     * @param destRow
     * @param styleMap
     */
    public static void copyRow(XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow,
                               Map<Integer, XSSFCellStyle> styleMap) {
        Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
        destRow.setHeight(srcRow.getHeight());
        XSSFCell oldCell;
        XSSFCell newCell;
        for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
            // old cell
            oldCell = srcRow.getCell(j);
            newCell = destRow.getCell(j);
            if (oldCell != null) {
                if (newCell == null) {
                    newCell = destRow.createCell(j);
                }
                copyCell(oldCell, newCell, styleMap);
            }
        }
    }

    /**
     * 把原来的Sheet中cell（列）的样式和数据类型复制到新的sheet的cell（列）中
     *
     * @param oldCell
     * @param newCell
     * @param styleMap
     */
    public static void copyCell(XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
        if (styleMap != null) {
            if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
                newCell.setCellStyle(oldCell.getCellStyle());
            } else {
                int stHashCode = oldCell.getCellStyle().hashCode();
                XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
                if (newCellStyle == null) {
                    newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
                    newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
                    styleMap.put(stHashCode, newCellStyle);
                }
                newCell.setCellStyle(newCellStyle);
            }
        }
        switch (oldCell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                newCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            default:
                break;
        }

}