Copying a sheet from an excel file to another excel file using Apache POI

[vandrouny Belarus]

public static void copyXSSFSheets(XSSFWorkbook sourceWB, XSSFWorkbook destinationWB) {
        for (Iterator<Sheet> it = sourceWB.sheetIterator(); it.hasNext(); ) {
            XSSFSheet sheet = (XSSFSheet) it.next();
            String sheetName = sheet.getSheetName();
            if (destinationWB.getSheetIndex(sheetName) != -1) {
                int index = 1;
                while (destinationWB.getSheetIndex(sheetName + "(" + index + ")") != -1) {
                    index++;
                }
                sheetName += "(" + index + ")";
            }
            XSSFSheet newSheet = destinationWB.createSheet(sheetName);
            copySheetSettings(newSheet, sheet);
            copyXSSFSheet(newSheet, sheet);
            copyPictures(newSheet, sheet);
        }
    }

    public static void copySheetSettings(Sheet newSheet, Sheet sheetToCopy) {

        newSheet.setAutobreaks(sheetToCopy.getAutobreaks());
        newSheet.setDefaultColumnWidth(sheetToCopy.getDefaultColumnWidth());
        newSheet.setDefaultRowHeight(sheetToCopy.getDefaultRowHeight());
        newSheet.setDefaultRowHeightInPoints(sheetToCopy.getDefaultRowHeightInPoints());
        newSheet.setDisplayGuts(sheetToCopy.getDisplayGuts());
        newSheet.setFitToPage(sheetToCopy.getFitToPage());

        newSheet.setForceFormulaRecalculation(sheetToCopy.getForceFormulaRecalculation());

        PrintSetup sheetToCopyPrintSetup = sheetToCopy.getPrintSetup();
        PrintSetup newSheetPrintSetup = newSheet.getPrintSetup();

        newSheetPrintSetup.setPaperSize(sheetToCopyPrintSetup.getPaperSize());
        newSheetPrintSetup.setScale(sheetToCopyPrintSetup.getScale());
        newSheetPrintSetup.setPageStart(sheetToCopyPrintSetup.getPageStart());
        newSheetPrintSetup.setFitWidth(sheetToCopyPrintSetup.getFitWidth());
        newSheetPrintSetup.setFitHeight(sheetToCopyPrintSetup.getFitHeight());
        newSheetPrintSetup.setLeftToRight(sheetToCopyPrintSetup.getLeftToRight());
        newSheetPrintSetup.setLandscape(sheetToCopyPrintSetup.getLandscape());
        newSheetPrintSetup.setValidSettings(sheetToCopyPrintSetup.getValidSettings());
        newSheetPrintSetup.setNoColor(sheetToCopyPrintSetup.getNoColor());
        newSheetPrintSetup.setDraft(sheetToCopyPrintSetup.getDraft());
        newSheetPrintSetup.setNotes(sheetToCopyPrintSetup.getNotes());
        newSheetPrintSetup.setNoOrientation(sheetToCopyPrintSetup.getNoOrientation());
        newSheetPrintSetup.setUsePage(sheetToCopyPrintSetup.getUsePage());
        newSheetPrintSetup.setHResolution(sheetToCopyPrintSetup.getHResolution());
        newSheetPrintSetup.setVResolution(sheetToCopyPrintSetup.getVResolution());
        newSheetPrintSetup.setHeaderMargin(sheetToCopyPrintSetup.getHeaderMargin());
        newSheetPrintSetup.setFooterMargin(sheetToCopyPrintSetup.getFooterMargin());
        newSheetPrintSetup.setCopies(sheetToCopyPrintSetup.getCopies());

        Header sheetToCopyHeader = sheetToCopy.getHeader();
        Header newSheetHeader = newSheet.getHeader();
        newSheetHeader.setCenter(sheetToCopyHeader.getCenter());
        newSheetHeader.setLeft(sheetToCopyHeader.getLeft());
        newSheetHeader.setRight(sheetToCopyHeader.getRight());

        Footer sheetToCopyFooter = sheetToCopy.getFooter();
        Footer newSheetFooter = newSheet.getFooter();
        newSheetFooter.setCenter(sheetToCopyFooter.getCenter());
        newSheetFooter.setLeft(sheetToCopyFooter.getLeft());
        newSheetFooter.setRight(sheetToCopyFooter.getRight());

        newSheet.setHorizontallyCenter(sheetToCopy.getHorizontallyCenter());
        newSheet.setMargin(Sheet.LeftMargin, sheetToCopy.getMargin(Sheet.LeftMargin));
        newSheet.setMargin(Sheet.RightMargin, sheetToCopy.getMargin(Sheet.RightMargin));
        newSheet.setMargin(Sheet.TopMargin, sheetToCopy.getMargin(Sheet.TopMargin));
        newSheet.setMargin(Sheet.BottomMargin, sheetToCopy.getMargin(Sheet.BottomMargin));

        newSheet.setPrintGridlines(sheetToCopy.isPrintGridlines());
        newSheet.setRowSumsBelow(sheetToCopy.getRowSumsBelow());
        newSheet.setRowSumsRight(sheetToCopy.getRowSumsRight());
        newSheet.setVerticallyCenter(sheetToCopy.getVerticallyCenter());
        newSheet.setDisplayFormulas(sheetToCopy.isDisplayFormulas());
        newSheet.setDisplayGridlines(sheetToCopy.isDisplayGridlines());
        newSheet.setDisplayRowColHeadings(sheetToCopy.isDisplayRowColHeadings());
        newSheet.setDisplayZeros(sheetToCopy.isDisplayZeros());
        newSheet.setPrintGridlines(sheetToCopy.isPrintGridlines());
        newSheet.setRightToLeft(sheetToCopy.isRightToLeft());
        newSheet.setZoom(100);
    }

    public static void copyXSSFSheet(XSSFSheet newSheet, XSSFSheet sheet) {
        int maxColumnNum = 0;
        Map<Integer, XSSFCellStyle> styleMap = new HashMap<>();
        // manage a list of merged zone in order to not insert two times a merged zone
        Set<String> mergedRegions = new TreeSet<>();
        List<CellRangeAddress> sheetMergedRegions = sheet.getMergedRegions();
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            XSSFRow srcRow = sheet.getRow(i);
            XSSFRow destRow = newSheet.createRow(i);
            if (srcRow != null) {
                //BaseUtils.systemLogger.info("copy row " + i);
                WriteUtils.copyXSSFRow(newSheet, srcRow, destRow, styleMap, sheetMergedRegions, mergedRegions);
                if (srcRow.getLastCellNum() > maxColumnNum) {
                    maxColumnNum = srcRow.getLastCellNum();
                }
            }
        }
        for (int i = 0; i <= maxColumnNum; i++) {
            if(newSheet.getColumnWidth(i) != sheet.getColumnWidth(i)) {
                newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
            }
        }
    }

    public static void copyXSSFRow(XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap, List<CellRangeAddress> sheetMergedRegions, Set<String> mergedRegions) {
        destRow.setHeight(srcRow.getHeight());
        // 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
                copyXSSFCell(oldCell, newCell, styleMap);
                // copy les informations de fusion entre les cellules
                CellRangeAddress mergedRegion = getMergedRegion(sheetMergedRegions, srcRow.getRowNum(), (short) oldCell.getColumnIndex());
                if (mergedRegion != null) {
                    CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                    if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
                        mergedRegions.add(newMergedRegion.formatAsString());
                        destSheet.addMergedRegion(newMergedRegion);
                    }
                }
            }
        }
    }

    public static void copyXSSFCell(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());
                    //по какой-то причине заливка не клонируется
                    newCellStyle.setFillBackgroundColor(oldCell.getCellStyle().getFillBackgroundColor());
                    styleMap.put(stHashCode, newCellStyle);
                }
                newCell.setCellStyle(newCellStyle);
            }
        }
        switch (oldCell.getCellTypeEnum()) {
            case STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case BLANK:
                newCell.setCellType(CellType.BLANK);
                break;
            case BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            default:
                break;
        }

    }

[vandrouny Belarus]

    private static void copyPictures(HSSFSheet newSheet, HSSFSheet sheet) {
        Drawing drawingOld = sheet.createDrawingPatriarch();
        Drawing drawingNew = newSheet.createDrawingPatriarch();
        CreationHelper helper = newSheet.getWorkbook().getCreationHelper();

        List<HSSFShape> shapes = ((HSSFPatriarch) drawingOld).getChildren();
        for (HSSFShape shape : shapes) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;
                HSSFPictureData picdata = pic.getPictureData();
                int pictureIndex = newSheet.getWorkbook().addPicture(picdata.getData(), picdata.getFormat());
                ClientAnchor anchor = null;
                if (pic.getAnchor() != null) {
                    anchor = helper.createClientAnchor();
                    anchor.setDx1(pic.getAnchor().getDx1());
                    anchor.setDx2(pic.getAnchor().getDx2());
                    anchor.setDy1(pic.getAnchor().getDy1());
                    anchor.setDy2(pic.getAnchor().getDy2());
                    anchor.setCol1(((HSSFClientAnchor) pic.getAnchor()).getCol1());
                    anchor.setCol2(((HSSFClientAnchor) pic.getAnchor()).getCol2());
                    anchor.setRow1(((HSSFClientAnchor) pic.getAnchor()).getRow1());
                    anchor.setRow2(((HSSFClientAnchor) pic.getAnchor()).getRow2());
                    anchor.setAnchorType(((HSSFClientAnchor) pic.getAnchor()).getAnchorType());
                }
                drawingNew.createPicture(anchor, pictureIndex);
            }
        }
    }


    private static void copyPictures(XSSFSheet newSheet, XSSFSheet sheet) {
        Drawing drawingOld = sheet.createDrawingPatriarch();
        Drawing drawingNew = newSheet.createDrawingPatriarch();
        CreationHelper helper = newSheet.getWorkbook().getCreationHelper();

        List<XSSFShape> shapes = ((XSSFDrawing) drawingOld).getShapes();
        for (XSSFShape shape : shapes) {
            if (shape instanceof XSSFPicture) {
                XSSFPicture pic = (XSSFPicture) shape;
                XSSFPictureData picdata = pic.getPictureData();
                int pictureIndex = newSheet.getWorkbook().addPicture(picdata.getData(), picdata.getPictureType());
                ClientAnchor anchor = null;
                if (pic.getAnchor() != null) {
                    anchor = helper.createClientAnchor();
                    anchor.setDx1(pic.getAnchor().getDx1());
                    anchor.setDx2(pic.getAnchor().getDx2());
                    anchor.setDy1(pic.getAnchor().getDy1());
                    anchor.setDy2(pic.getAnchor().getDy2());
                    anchor.setCol1(((XSSFClientAnchor) pic.getAnchor()).getCol1());
                    anchor.setCol2(((XSSFClientAnchor) pic.getAnchor()).getCol2());
                    anchor.setRow1(((XSSFClientAnchor) pic.getAnchor()).getRow1());
                    anchor.setRow2(((XSSFClientAnchor) pic.getAnchor()).getRow2());
                    anchor.setAnchorType(((XSSFClientAnchor) pic.getAnchor()).getAnchorType());
                }
                drawingNew.createPicture(anchor, pictureIndex);
            }
        }
    }

    public static CellRangeAddress getMergedRegion(List<CellRangeAddress> sheetMergedRegions, int rowNum, short cellNum) {
        for (CellRangeAddress merged : sheetMergedRegions) {
            if (merged.isInRange(rowNum, cellNum)) {
                return merged;
            }
        }
        return null;
    }

    private static boolean isNewMergedRegion(CellRangeAddress newMergedRegion, Set<String> mergedRegions) {
        return !mergedRegions.contains(newMergedRegion.formatAsString());
    }
}

[Rob Bres]

After fixing the bugs that impacted my use case, as well as quite a bit of refactoring for better readability and convenience, I have working java 11 code using POI 4.1.1 that can copy a Sheet from one workbook to another workbook.

See the full code at http://robsnotebook.com/copy-a-sheet-to-a-different-workbook-in-apache-poi

[Mak Cash]

Hello All -
I am not sure if this was still a problem ....

But this is what I am doing now ... to copy a sheet. ...
1> clone old sheet to new
2> rename new sheet to new name ..

    private XSSFSheet copyWorksheet(XSSFWorkbook wb, String oldSheetName, String newSheetName, boolean safeCreateFlag) throws Exception {
		XSSFSheet oldSheet = wb.getSheet(oldSheetName);
		if (oldSheet == null) {
			throw new Exception("Sheet with name " + oldSheetName + " doesnt exist");
		}
		XSSFSheet newSheet = wb.getSheet(newSheetName);
		int newSheetIndex = -1;
		if (newSheet != null) {
			if (safeCreateFlag) {
				newSheetIndex = wb.getSheetIndex(newSheetName);
				wb.removeSheetAt(newSheetIndex);
			} else {
				throw new Exception("Sheet with name " + newSheetName + " already exists");
			}
		}

		int oldSheetNo = wb.getSheetIndex(oldSheet);
		newSheet = wb.cloneSheet(oldSheetNo);
		
		int newSheetNo = wb.getSheetIndex(newSheet);
		wb.setSheetName(newSheetNo, newSheetName);
		
		if (newSheetIndex >= 0) {
			wb.setSheetOrder(newSheetName, newSheetIndex);
		}
		return newSheet;
    }