Apache POI. Copying sheets

Question

I'm using apache poi to create an excel document. To create new sheet in workbook I write next code:

Workbook wb = new HSSFWorkbook(); 
Sheet sh = wb.createSheet();

this code create and add sheet to workbook. But I want to create sheet formerly and then add it to workbook. Smth like this:

Sheet sh = new HSSFSheet();
wb.addSheet(sh);

I need such thing, because I want to copy data from one sheet of one workbook to another sheet of another workbook(Workbook interface has method Sheet cloneSheet(int)). But Workbook interface doesn't have method like addSheet(Sheet sh). Also HSSFWorkbook is final class so I can't extend it to implement add method How can I do this?

Answer 1

You can't just take a Sheet object from one Workbook, and add it to a different Workbook.

What you'll need to do is to open the old workbook and the new workbooks at the same time, and create the sheet in the new workbook. Next, clone all the styles you used in the old sheet onto the new one (HSSFCellStyle has a method for cloning a style from one workbook to another). Finally, iterate over all the cells and copy them over.

Answer 2

POI version < v4.0

Okay I tried to do what Gagravarr said above. This solution works for me. This code will work if the sheets don't have tables, etc. If the sheets contain simple text (String, boolean, int etc), formulas, this solution will work.

    Workbook oldWB = new XSSFWorkbook(new FileInputStream("C:\\input.xlsx"));
    Workbook newWB = new XSSFWorkbook();
    CellStyle newStyle = newWB.createCellStyle(); // Need this to copy over styles from old sheet to new sheet. Next step will be processed below
    Row row;
    Cell cell;
    for (int i = 0; i < oldWB.getNumberOfSheets(); i++) {
        XSSFSheet sheetFromOldWB = (XSSFSheet) oldWB.getSheetAt(i);
        XSSFSheet sheetForNewWB = (XSSFSheet) newWB.createSheet(sheetFromOldWB.getSheetName());
        for (int rowIndex = 0; rowIndex < sheetFromOldWB.getPhysicalNumberOfRows(); rowIndex++) {
            row = sheetForNewWB.createRow(rowIndex); //create row in this new sheet
            for (int colIndex = 0; colIndex < sheetFromOldWB.getRow(rowIndex).getPhysicalNumberOfCells(); colIndex++) {
                cell = row.createCell(colIndex); //create cell in this row of this new sheet
                Cell c = sheetFromOldWB.getRow(rowIndex).getCell(colIndex, Row.CREATE_NULL_AS_BLANK ); //get cell from old/original WB's sheet and when cell is null, return it as blank cells. And Blank cell will be returned as Blank cells. That will not change.
                    if (c.getCellType() == Cell.CELL_TYPE_BLANK){
                        System.out.println("This is BLANK " +  ((XSSFCell) c).getReference());
                    }
                    else {  //Below is where all the copying is happening. First It copies the styles of each cell and then it copies the content.              
                    CellStyle origStyle = c.getCellStyle();
                    newStyle.cloneStyleFrom(origStyle);
                    cell.setCellStyle(newStyle);            
                    
                     switch (c.getCellTypeEnum()) {
                        case STRING:                            
                            cell.setCellValue(c.getRichStringCellValue().getString());
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {                             
                                cell.setCellValue(c.getDateCellValue());
                            } else {                              
                                cell.setCellValue(c.getNumericCellValue());
                            }
                            break;
                        case BOOLEAN:
                           
                            cell.setCellValue(c.getBooleanCellValue());
                            break;
                        case FORMULA:
                           
                            cell.setCellValue(c.getCellFormula());
                            break;
                        case BLANK:
                            cell.setCellValue("who");
                            break;
                        default:
                            System.out.println();
                        }
                    }
                }
            }

        }
        //Write over to the new file
        FileOutputStream fileOut = new FileOutputStream("C:\\output.xlsx");
        newWB.write(fileOut);
        oldWB.close();
        newWB.close();
        fileOut.close();

If your requirement is to copy full sheets without leaving or adding anything. I think The process of elimination works better and faster then the above code. And you don't have to worry about losing formulas, drawings, tables, styles, fonts, etc.

    XSSFWorkbook wb = new XSSFWorkbook("C:\\abc.xlsx");
    for (int i = wb.getNumberOfSheets() - 1; i >= 0; i--) {
            if (!wb.getSheetName(i).contentEquals("January")) //This is a place holder. You will insert your logic here to get the sheets that you want.  
                wb.removeSheetAt(i); //Just remove the sheets that don't match your criteria in the if statement above               
    }
    FileOutputStream out = new FileOutputStream(new File("C:\\xyz.xlsx"));
    wb.write(out);
    out.close();

POI version >= v4.0

As of version 4.0, Cell.CELL_TYPE_BLANK and Row.CREATE_NULL_AS_BLANK don't exist (they deprecated). Use CellType.* and Row.MissingCellPolicy.* instead.