[Advanced Excel] Exporting a Workbook with multiple pages

[Advanced Excel] Exporting a Workbook with multiple pages

Forge Component
Published on 2015-10-06 by Carlos Freitas
19 votes
Published on 2015-10-06 by Carlos Freitas
I am trying to export the contents of a number (more than 1) table into a workbook which has a worksheet per table.  I have the following process:
1. Workbook Create
2. Worksheet_SelectByIndex (workbook = step 1; worksheet number = 1)
3. Cell_WriteRange (Worksheet = step 2; RowStart = 1; ColumnStart = 1; DataSet = ToObject({RecordList1})
4. Worksheet_SelectByIndex (workbook = step 1; worksheet number = 2)
5. Cell_WriteRange (Worksheet = step 4; RowStart = 1; ColumnStart = 1; DataSet = ToObject({RecordList2})
6. Workbook_GetBinaryData (Workbook = step 1)
7. Workbook_Close (Workbook = step 1)
8. Download (File Name = Excel_Export.xlsx; File Content = step 6; Mime-Type = application/octet-stream; Save to Disk = Yes)
 This is erroring at step 4 where it selects the 2nd worksheet.  I think this is because the create workbook only creates it with 1 worksheet.  If i remove steps 4 and 5 the process completes successfully.

Is there a way to create a workbook in such a way that I can write to more than 1 sheet?
Also is there a way to format the end result as dates/currencies are coming out as integers?

What if you wrote the first page, closed it  - then reopened it and wrote the second page?
Even if I try to write to worksheet 2 first without "opening" or writing to sheet 1 it still gives an error.  Message is "The given key was not present in the dictionary."
Also not able to close a worksheet only a workbook.  Then trying to re-open the workbook requires a filename which we dont have as its all in memory at the moment.
Any solution available yet? I have the same issues as described in step 4
Também estou com o mesmo problema, erro "The given key was not present in the dictionary" ao tentar gerar a segunda página. 
Alguma solução para este caso?
Hi Even I am facing the same issue. I want to write multisheet Excel using Excel_Packge, but can't.
Please suggest and help.


You must change the "Workbook_Create" function to receive a new parameter to the number of sheet's you want to create because, by default, it only adds one sheet.

public void MssWorkbook_Create(out object ssWorkbook, int ssNumberOfSheets)
            ExcelPackage p = new ExcelPackage();

            ssNumberOfSheets = ssNumberOfSheets <= 0 ? 1 : ssNumberOfSheets;

            for(int i = 0; i<ssNumberOfSheets; i++)
                 p.Workbook.Worksheets.Add("Sheet" + (i+1));

            ssWorkbook = p;
        } // MssWorkbook_Create

João Rodrigues

It works when the "Workbook_Create" function is changed?

José Vieira
José Vieira wrote:

It works when the "Workbook_Create" function is changed?

José Vieira
 Hi José,

Yes, it works ;)

João Rodrigues