[Advanced Excel] Exporting a Workbook with multiple pages

[Advanced Excel] Exporting a Workbook with multiple pages

  
Forge Component
(21)
Published on 5 Nov by João Rosado
21 votes
Published on 5 Nov by João Rosado
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?
obrigado
Hi Even I am facing the same issue. I want to write multisheet Excel using Excel_Packge, but can't.
Please suggest and help.

Hi,

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
Hi,

It works when the "Workbook_Create" function is changed?

José Vieira
José Vieira wrote:
Hi,

It works when the "Workbook_Create" function is changed?

José Vieira
 
 Hi José,

Yes, it works ;)

João Rodrigues

solución al problema "The given key was not present in the dictionary"

Yo no podía escribir en la segunda hoja y me arrojaba el error antes mencionado cuando intentaba 

Worksheet_SelectByIndex

poner WorkSheetNumber = 2

:(


Solución: Use 

Workbook_Open

en lugar de un Workbook_create

Para eso utilice una plantilla : 

"http://localhost"+ GetOwnerURLPath() +"Template.xlsx"

y ya pude hacer uso de 

Worksheet_SelectByIndex

WorkSheetNumber = 2


Espero que les sea de utilidad

Saludos ;)