Platform
Learn
Community
Support
Partner Center
Sign up
or
Log in
logout
Account Settings
Profile
messages
Messages
logout
Logout
Platform
Home
Downloads
IPP
Licensing
Project Sizing
Learn
Training
Documentation
Evaluation Guide
What's New
Community
Home
Forums
Forge
Ideas
Members
Support
Partner Guide
Resources Library
Opportunities
Account Management
Sign up
or
Log in
Home
Forums
Forge
Ideas
Members
Dear ,
What would improve your OutSystems Community experience? Let us know by taking this 2-minute survey.
Pick up the survey
Community
›
Forums
›
Technology & Integration
Export multi-sheet Excel
Community
›
Forums
›
Technology & Integration
Export multi-sheet Excel
New Post
New Post
Lake Pointe Church LPC
Posted on 2013-06-05
Lake Pointe Church LPC
Rank: #5053
Posted on 2013-06-05
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:
Workbook_Create
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.
André Vieira
Posted on 2013-06-05
André Vieira
Rank: #1
Posted on 2013-06-05
Solution
Hi Guy,
Take a look at the following post that might just be what you're looking for:
http://www.outsystems.com/forums/discussion/5526/multi-tab-excel-report/
Cheers,
André
Solution
Lake Pointe Church LPC
Posted on 2013-06-06
Lake Pointe Church LPC
Rank: #5053
Posted on 2013-06-06
Solution
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):
Workbook_Create
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 :)
Solution
Lake Pointe Church LPC
Posted on 2013-06-07
Lake Pointe Church LPC
Rank: #5053
Posted on 2013-06-07
Solution
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?
Solution
Quick Reply
attachment
Choose File
No file chosen
Submit
Login to reply
New Post
Available Forums
Technology & Integration
News and Announcements
Forge Discussions
Meta
Community Quick Guides
Usability
Installation Troubleshooting
Personal Environment Troubleshooting
Forum Notifications
Email Digest Settings
Loading...