Export multi-sheet Excel

Export multi-sheet Excel

Has anyone been able to pull this off successfully yet? I have found a handfull of posts, but have yet to successfully pull it off. I have tried the Advanced Excel extension from Forge, and it was the most promising, however the only data that prints in the cells is "System.Collections.BitArray". I have tried various versions of the Excel_Binary extensions all over the place, and either run into IPP issues, or they don't work as expected. Is anyone doing this successfully? I have a dataset that has the data I need, and I am iterating it over one of the fields, trying to make a separate sheet for each value in that field. I can't even get to testing the iterating part because I can't even get a basic static entity output to work. My flow is as follows:

Worksheet_SelectByIndex (set index to 1 to grab sheet 1, workbook setting = Workbook_Crate.workbook. This step works with Excel_package extension from "Advanced Excel" on Forge, but bombs out with a key error on all others I've tried)
Cell_WriteRange (worksheet=Worksheet_SelectByIndex.worksheet, RowStart=1, ColumnStart=2, DataSet = ToObject(MyQuery.List) with "MyQuery" being a simple query pulling info from a static entity)
Workbook_GetBinaryData (workbook = Workbook_Create.Workbook)
Download Widget (File Content = Workbook_GetBinaryData.BinaryData, Mime-Type = "application/octet-stream"

Does anyone see anything wrong with this flow? With this setup, most extensions I have tried throw a key error at the Worksheet_selectByIndex action. The Excel_Package extension does not have this problem, and indeed goes through all the actions, and downloads the file. I end up with a single sheet excel file with only one column (should be 3), with the correct number of rows, only every cell is filled with "System.Collections.BitArray". I am desperately needing to be able to do this. Any help would be greatly appreciated.

Hi Guy,

Take a look at the following post that might just be what you're looking for:

Thank you for the reply, Andre. That was one of the extensions I tried, but would get the error at the Worksheet_SelectByIndex action. I have, however, gotten a solution to work, though it is not as efficient as I would like.

I ended up creating a structure with all text and integer data types for attributes. I then use a for loop to iterate through all rows of my table list and assign the appropriate values to the structure attributes. I call this ineficient, because I am essentially rebuilding the record list in a much less efficient for loop than the original SQL query. However, once I move the data into a variable record with this structure, and build a list of these records, I finally get the data I am looking for in the downloaded spreadsheet. 

To build off the flow in my previous post, this is what I now have (replacing the Excel_binary extension with the Excel_Package extension from the Advanced Excel posting on Forge):

Simple Query used to isolate the distinct fields I want to break the pages by
For Each1 (Start) iterate throgh the Simple Query above
    Refresh Query refresh main RecordList source query, filtered by current field value of Simple Query
    Assign build a header row into a Record variable for the each worksheet with specific text values
    List Append Add the header record just built fromthe Assign above to a Record List variable
    For Each2 (Start) - Cycle through each record from the Refreshed "master" query
         Assign overwrite Record variable with desired data from current row
         List Append add Record to the Record List variable
     End For Each2
    Worksheet_SelectByIndex (set index to index+1 to grab next sheet (uses variable with default of 0), workbook setting = Workbook_Crate.workbook.
   Worksheet_Rename set the name of the new worksheet dynamically to give a logical description
    Cell_WriteRange (worksheet=Worksheet_SelectByIndex.worksheet, RowStart=1, ColumnStart=1, DataSet = ToObject(RecordList) with 'RecordList being the variable I have been appending to.
End For Each1
Workbook_GetBinaryData (workbook = Workbook_Create.Workbook)
Download Widget (File Content = Workbook_GetBinaryData.BinaryData, Mime-Type = "application/octet-stream"

This setup produces exactly what I need. I feel the building of record variables and record list variables to re-store all the data into should not be necessary. In any case, I got it to work, and maybe this post will help someone else :)
I spoke to soon. This setup DID work for 1 sheet. As soon as I added another item that would cause the outer loop to have more than one iteration, it errors out. It errors when it hits the Worksheet_SelectByIndex action for the second time. It tries to grab index = 2 to grab the second sheet, then errors. My guess is that the workbook_create action only creates a workbook with 1 worksheet, and therefore there is no other worksheets to grab and write to. Anyone know of an action to add a worksheet?