CODEWITHSUNDEEP
javaexcelapache-poixssf
RickMx
RickMx

Reputation: 1519

Cloning sheets between files

I have three or more excel files with different sheets among them and I need to create a new blank file with a copy (or clone) that sheets into the new file and place them in the order I need so I can fill out the respective forms with data.

How can I do this by using Jakarta POI (XSSFWorkbook)?

Upvotes: 1

Views: 1645

Answers (3)

Faraz
Faraz

Reputation: 6275

This is my implementation of copying sheets from one workbook to another. I did everything as described by Gagravarr. 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();

Upvotes: 0

user6532240
user6532240

Reputation: 1

FileOutputStream os = new FileOutputStream("differnetFileName") readWorkbook.write(os);

guess we can make use of write operation to OS with diff file name will work .

Upvotes: 0

Gagravarr
Gagravarr

Reputation: 48336

First up, I think you mean Apache POI - it hasn't been Apache Jakarta POI for quite a few years now...

In terms of copying sheets from one workbook to another, it can be done, but it will require some coding. First you'll want to identify the cell styles you use, and clone those across. Make sure you keep track of which source Cell Style goes to which destination Cell Style, as you don't want to keep re-creating or you'll hit the limit! CellStyle.cloneStyleFrom(CellStyle) is the method you'll want.

Then, for each source sheet, create a sheet in the target workbook. Loop over all the source rows, creating new target ones. Then loop over the cells, switch by cell type, grab the appropriate value and set it. Rinse and repeat!

Upvotes: 3

Related Questions