Add a new sheet in an existing workbook with java jxl

Question

I have an application in Java and I manage Excel workbooks with JXL API. I would like to create a new sheet in an existing workbook that already has a sheet (so I want to create a second sheet). But I don't want to erase the existing workbook and sheet, just add a new one and add data in it.

How can I achieve that ?

The following code I have tried doesn't work for me, it erases the existing workbook :

   //this is my existing workbook that I want to open and not erase
   //I can't use getWorkbook for WritableWorkbook
WritableWorkbook target = Workbook.createWorkbook(new File("C:/Novas/template_test.xls"));
   //I want to add a new sheet after the existing sheet
WritableSheet writeSheet = target.createSheet("Sheet 2", 1);
Label label = new Label(0,0,"test");
writeSheet.addCell(label);
target.write();
target.close();

Answer 1

Try following :

1 - Instead of using Workbook.createWorkbook, use Workbook.getWorkbook to load the existing workbook

2 - Get the sheets length and then incrementing it by 1 to define the new sheet's position in existing workbook

WritableWorkbook target = Workbook.getWorkbook(new File("C:/Novas/template_test.xls"));

WritableSheet writeSheet = target.createSheet("Sheet 2", target.getSheets().length+1);

....

Ref : http://jexcelapi.sourceforge.net/resources/javadocs/current/docs/jxl/Workbook.html

Hope it helps!

Answer 2

Finally I have found the solution. here is my updated code :

Workbook source = Workbook.getWorkbook(new File("C:/Novas/template_test.xls"));
   //just use the createWorkbook method that uses an existing Workbook and will copy it
WritableWorkbook target = Workbook.createWorkbook(new File("C:/Novas/template_copy.xls"),source);
   //then create the new sheet after the existing that will not be erased
WritableSheet writeSheet = target.createSheet("Sheet 2", 1);