Copying Excel Worksheets in POI

Question

Does anyone know of a means to copy a worksheet from one workbook to another using POI? The Workbook class has a cloneSheet method, but there doesn't seem to be able to insert a cloned sheet into a new workbook?

If there isn't an API to do this easily, does anyone have the code to copy all of the data (styles, column widths, data, etc) from one sheet to another?

The jxls has methods to copy sheets, but they don't work when copying between workbooks.

Answer 1

Hi Patrick, Did you find any solution for this problem, Actually I am also looking for this solution, searched out google till......................but no solution yet. If you got any idea from some one or you yourself resolved this issue then would you please give me an idea, how to achieve this.

Thanks in advance

Answer 2

How about deleting all the other worksheets in the workbook and then saving the file under a different name? (Disclaimer: my experience with NPOI now totals 1 hour)

This still doesn't solve the problem if you want to put the sheet into an existing workbook.

I just spent a few minutes time digging through the source code, and it seems like this could be implemented.

Clearly, the logic is all there to clone a sheet, and in the HSSFWorkbook class there is a private ArrayList that holds all the HSSFSheets. Could it be so simple as to add this method to the HSSFWorkbook class?

public void AddSheet(HSSFSheet sheet)
{
   _sheets.Add(sheet);
}

There must be something huge I'm missing, because otherwise I would expect this to have been implemented long ago.

I might try this out next week and see what can be done. If anyone else has time to try it in the interim, by all means do.

There is a recent question (2 wks ago -- 2010.01.15) about the ability to do this on the NPOI discussions page, but there has been no response as of yet.

Answer 3

I created a workitem for NPOI: http://npoi.codeplex.com/WorkItem/View.aspx?WorkItemId=6057.

Answer 4

This link should be helpful:

Answer 5

If you are using the Java POI library the best would be to load the Spreadsheet into memory,,, then create a new one and write each one of the records you want to copy... not the best way but acomplishes the copy function...