How to consolidate multiple workbook into one workbook using Apache POI

Question

I have a Java code producing bunch of workbooks and would like to copy all of them into one workbook.

The original answer to this question was posted on:

How to copy a sheet between Excel workbooks in Java

Answer 1

My original idea was to use the cloneSheet method of XSSFWorkbook to do the trick, I was successful in copying the Relations and Drawings however failed to copy the data itself. Why? Because the XSSFSheet's method write and read are protected, I did come up with my own version of XSSFSheet by extending it and making the two methods write and read public, but that would mean I will have to copy and edit every source file using XSSFSheet replacing it with my version of XSSFSheet that would mean too much coding I didn't have that kind of time for the project.

The original answer to this question was posted on:

How to copy a sheet between Excel workbooks in Java

However, my project requirement were different, so I improvised the answer.

I added a method called combine which accepts target workbook, and source workbook.

The methods loops through all the worksheets in the source workbook and adds them to target workbook. The original answer lacked copying of relations and drawing.

Also the workbooks I was to copy from lacked Footer, neede Gridlines to be turned off, and fit to page. So here is my solution

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.POIXMLDocumentPart.RelationPart;
import org.apache.poi.POIXMLException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.POILogger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;

import com.hcsc.eas.framework.services.logging.Logger;


public class XSSFWorkbookHelper {
    static protected Logger logger = Logger.getLogger(XSSFWorkbookHelper.class.getName());  

    public static void combine(XSSFWorkbook tgt, XSSFWorkbook src) throws InvalidFormatException {
        // begin sheets loop
        boolean first=true;
        String firstSheetName = null;
        XSSFSheet tgtSheet = null;
        for (int i = 0; i < src.getNumberOfSheets(); i++) {
            XSSFSheet srcSheet = src.getSheetAt(i);
            String sheetName = srcSheet.getSheetName().replaceAll("_", "-");
            if (first) {
                firstSheetName = srcSheet.getSheetName();
                if (sheetName.equals(firstSheetName)) sheetName = sheetName + "_";
                first = false;
            } 
            tgtSheet = tgt.createSheet(sheetName);

            copyRelations(tgtSheet, srcSheet);

            copySheets(tgtSheet, srcSheet);
        } // end sheets loop
        tgtSheet = tgt.getSheet(firstSheetName);
        if(tgtSheet != null)   {
            tgt.removeSheetAt(tgt.getSheetIndex(tgtSheet));
        }
    }

    private static void copyRelations(XSSFSheet tgtSheet,XSSFSheet srcSheet) {
        // copy sheet's relations
        List<RelationPart> rels = srcSheet.getRelationParts();
        // if the sheet being cloned has a drawing then rememebr it and re-create it too
        XSSFDrawing dg = null;
        for(RelationPart rp : rels) {
            POIXMLDocumentPart r = rp.getDocumentPart();
            // do not copy the drawing relationship, it will be re-created
            if(r instanceof XSSFDrawing) {
                dg = (XSSFDrawing)r;
                continue;
            }

            addRelation(rp, tgtSheet);
        }

        try {
            for(PackageRelationship pr : srcSheet.getPackagePart().getRelationships()) {
                if (pr.getTargetMode() == TargetMode.EXTERNAL) {
                    tgtSheet.getPackagePart().addExternalRelationship
                        (pr.getTargetURI().toASCIIString(), pr.getRelationshipType(), pr.getId());
                }
            }
        } catch (InvalidFormatException e) {
            throw new POIXMLException("Failed to clone sheet", e);
        }

        CTWorksheet ct = tgtSheet.getCTWorksheet();
        if(ct.isSetLegacyDrawing()) {
            logger.warn(POILogger.WARN + "Cloning sheets with comments is not yet supported.");
            ct.unsetLegacyDrawing();
        }
        if (ct.isSetPageSetup()) {
            logger.warn(POILogger.WARN + "Cloning sheets with page setup is not yet supported.");
            ct.unsetPageSetup();
        }

        tgtSheet.setSelected(false);

        // clone the sheet drawing alongs with its relationships
        if (dg != null) {
            if(ct.isSetDrawing()) {
                // unset the existing reference to the drawing,
                // so that subsequent call of tgtSheet.createDrawingPatriarch() will create a new one
                ct.unsetDrawing();
            }
            XSSFDrawing clonedDg = tgtSheet.createDrawingPatriarch();
            // copy drawing contents
            clonedDg.getCTDrawing().set(dg.getCTDrawing());

            clonedDg = tgtSheet.createDrawingPatriarch();

            // Clone drawing relations
            List<RelationPart> srcRels = srcSheet.createDrawingPatriarch().getRelationParts();
            for (RelationPart rp : srcRels) {
                addRelation(rp, clonedDg);
            }
        }
    }

    private static void addRelation(RelationPart rp, POIXMLDocumentPart target) {
        PackageRelationship rel = rp.getRelationship();
        if (rel.getTargetMode() == TargetMode.EXTERNAL) {
            target.getPackagePart().addRelationship(rel.getTargetURI(), rel.getTargetMode(), rel.getRelationshipType(),
                    rel.getId());
        } else {
            XSSFRelation xssfRel = XSSFRelation.getInstance(rel.getRelationshipType());
            if (xssfRel == null) {
                // Don't copy all relations blindly, but only the ones we know
                // about
                throw new POIXMLException(
                        "Can't clone sheet - unknown relation type found: " + rel.getRelationshipType());
            }
            target.addRelation(rel.getId(), xssfRel, rp.getDocumentPart());
        }
    }

    /**
     * @param newSheet
     *            the sheet to create from the copy.
     * @param sheet
     *            the sheet to copy.
     */
    public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet) {
        copySheets(newSheet, sheet, true);
    }

    /**
     * @param newSheet
     *            the sheet to create from the copy.
     * @param sheet
     *            the sheet to copy.
     * @param copyStyle
     *            true copy the style.
     */
    public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
        setupSheet(newSheet);

        int maxColumnNum = 0;
        Map<Integer, CellStyle> styleMap = (copyStyle) ? new HashMap<Integer, CellStyle>() : null;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            XSSFRow srcRow = sheet.getRow(i);
            XSSFRow destRow = newSheet.createRow(i);
            if (srcRow != null) {
                copyRow(sheet, newSheet, srcRow, destRow, styleMap);
                if (srcRow.getLastCellNum() > maxColumnNum) {
                    maxColumnNum = srcRow.getLastCellNum();
                }
            }
        }
        for (int i = 0; i <= maxColumnNum; i++) {
            newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
        }
    }

    public static void setupSheet(XSSFSheet newSheet) {
        newSheet.getFooter().setCenter("&7 _x0000_ A Division of Health Care Service Corporation, a Mutual Legal Reserve Company, \n _x0000_  an Independent Licensee of the Blue Cross and Blue Shield Association");
        newSheet.setDisplayGridlines(false);
        newSheet.setMargin(Sheet.RightMargin, 0.5 /* inches */ );
        newSheet.setMargin(Sheet.LeftMargin, 0.5 /* inches */ );
        newSheet.setMargin(Sheet.TopMargin, 0.5 /* inches */ );
        newSheet.setMargin(Sheet.BottomMargin, 0.5 /* inches */ );
        newSheet.setFitToPage(true);
    }

    /**
     * @param srcSheet
     *            the sheet to copy.
     * @param destSheet
     *            the sheet to create.
     * @param srcRow
     *            the row to copy.
     * @param destRow
     *            the row to create.
     * @param styleMap
     *            -
     */
    public static void copyRow(XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow,
            Map<Integer, CellStyle> styleMap) {
        try {
            // manage a list of merged zone in order to not insert two times a
            // merged zone
            Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
            destRow.setHeight(srcRow.getHeight());
            // reckoning delta rows
            int deltaRows = destRow.getRowNum() - srcRow.getRowNum();
            // pour chaque row
            for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
                XSSFCell oldCell = srcRow.getCell(j); // ancienne cell
                XSSFCell newCell = destRow.getCell(j); // new cell
                if (oldCell != null) {
                    if (newCell == null) {
                        newCell = destRow.createCell(j);
                    }
                    // copy chaque cell
                    copyCell(oldCell, newCell, styleMap);
                    // copy les informations de fusion entre les cellules
                    // System.out.println("row num: " + srcRow.getRowNum() + " ,
                    // col: " + (short)oldCell.getColumnIndex());
                    CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(),
                            (short) oldCell.getColumnIndex());

                    if (mergedRegion != null) {
                        // System.out.println("Selected merged region: " +
                        // mergedRegion.toString());
                        CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow() + deltaRows,
                                mergedRegion.getLastRow() + deltaRows, mergedRegion.getFirstColumn(),
                                mergedRegion.getLastColumn());
                        // System.out.println("New merged region: " +
                        // newMergedRegion.toString());
                        CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                        if (isNewMergedRegion(wrapper, mergedRegions)) {
                            mergedRegions.add(wrapper);
                            destSheet.addMergedRegion(wrapper.range);
                        }
                    }
                }
            }
        } catch (Exception e) {
            //e.printStackTrace();
            logger.warn(POILogger.WARN + "merge area failure, happens when a merge area overlaps.");
        }
    }

    /**
     * @param oldCell
     * @param newCell
     * @param styleMap
     */
    public static void copyCell(XSSFCell oldCell, XSSFCell newCell, Map<Integer, CellStyle> styleMap) {
        if (styleMap != null) {
            if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
                newCell.setCellStyle(oldCell.getCellStyle());
            } else {
                int stHashCode = oldCell.getCellStyle().hashCode();
                CellStyle 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;
        }
    }

    /**
     * Retrieves cell merge information in the source sheet
     * to apply them to the destination sheet ... Get all zones
     * merged in the source sheet and look for each of them if she
     * find in the current row that we are dealing. If yes, return the object
     * CellRangeAddress.
     * 
     * @param sheet
     *            the sheet containing the data.
     * @param rowNum
     *            the num of the row to copy.
     * @param cellNum
     *            the num of the cell to copy.
     * @return the CellRangeAddress created.
     */
    public static CellRangeAddress getMergedRegion(XSSFSheet sheet, int rowNum, short cellNum) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress merged = sheet.getMergedRegion(i);
            if (merged.isInRange(rowNum, cellNum)) {
                return merged;
            }
        }
        return null;
    }

    /**
     * Check that the merged region has been created in the destination sheet.
     * 
     * @param newMergedRegion
     *            the merged region to copy or not in the destination sheet.
     * @param mergedRegions
     *            the list containing all the merged region.
     * @return true if the merged region is already in the list or not.
     */
    private static boolean isNewMergedRegion(CellRangeAddressWrapper newMergedRegion,
            Set<CellRangeAddressWrapper> mergedRegions) {
        return !mergedRegions.contains(newMergedRegion);
    }

}

class CellRangeAddressWrapper implements Comparable<CellRangeAddressWrapper> {

    public CellRangeAddress range;

    /**
     * @param theRange
     *            the CellRangeAddress object to wrap.
     */
    public CellRangeAddressWrapper(CellRangeAddress theRange) {
        this.range = theRange;
    }

    /**
     * @param o
     *            the object to compare.
     * @return -1 the current instance is prior to the object in parameter, 0:
     *         equal, 1: after...
     */
    public int compareTo(CellRangeAddressWrapper o) {

        if (range.getFirstColumn() < o.range.getFirstColumn() && range.getFirstRow() < o.range.getFirstRow()) {
            return -1;
        } else if (range.getFirstColumn() == o.range.getFirstColumn() && range.getFirstRow() == o.range.getFirstRow()) {
            return 0;
        } else {
            return 1;
        }

    }

}