java - 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.