Insert or move worksheets

May 4, 2011 at 11:47 AM

I would like to know if it is possible to move or to insert worksheets?  I have a template with 2 spreadsheets and I want to add more sheets to it but I would like the second (original spreadsheet) moved to the end of the final document.

Template: Sheet1, Sheet2

End result; Sheet1, Sheet3, Sheet4, Sheet2


Can this be done?

May 6, 2011 at 7:46 PM
Edited May 6, 2011 at 7:54 PM

@jpretorius: if you haven't already come across the answer, here is a VB.NET sample that will open an existing file, create 2 new sheets, move the 2nd created sheet to the second sheet position in the file and move the 1st created sheet to the next-to-last sheet position, then save the entire workbook to a new file. If you need a C# example, let me know. It's very similar...

Edit: I meant to mention that due to the similarities of NPOI with the original Java-based POI project, you can often search Google (for example, POI Change Sheet Index) and find something that might give a hint to the correct method or property.

            Dim FS as IO.FileStream
FS = New IO.FileStream("C:\Test.xls", IO.FileMode.Open) Me.MessageLabel.Text = sStep WorkBook = New HSSFWorkbook(FS) WorkBook.CreateSheet("Test") WorkBook.CreateSheet("Test2") WorkBook.SetSheetOrder("Test2", 1) WorkBook.SetSheetOrder("Test", WorkBook.NumberOfSheets - 1) FS = New IO.FileStream("C:\TestNew.xls", IO.FileMode.Create) WorkBook.Write(FS) FS.Close()

May 17, 2011 at 2:38 PM

Thank you brimars!


That is so easy!  Many thanks for your help!