[Advanced Excel] How do I populate multiple worksheets with different tables to each?
Question
excel-png
Web icon
Forge component by Carlos Freitas
Application Type
Reactive

Hi, I'm using the Advanced Excel forge component to export excel file.

I want to export multiple entity lists to different worksheets in one excel file. But I can't seem to find out how and was only able to pass one table in the first worksheet 

Sample output I want.

Worksheet1 contains - Employee lists

Worksheet2 contains - Department lists


I'm using the Workbook_Open to pass the Binary data that contains the Employee lists, but don't know where to pass the Department lists. Assuming the Workbook is the excel file, shouldn't we be passing the lists to a Worksheet instead of Workbook, or I'm not understanding this. Hoping someone could help me with this. Thank you.

You can use the AddSheet to create it, then use "Select" to select the worksheet and then use the "WriteRange" to insert data to it.



Hi @Kenneth1,

You can use the action "WorkBook_AddSheet" to add another worksheet to your workbook (excel file).

Then, you can select your new Worksheet and write there all the values you want.

Hope this helps,

Pedro


I already tried to use the WorkBook_AddSheet but it doesn't have a parameter to pass binary data, or I need to use another action for it? Not sure which one.

You can use the AddSheet to create it, then use "Select" to select the worksheet and then use the "WriteRange" to insert data to it.



after doing your suggestion, it returns this list in 2nd sheet. however it shows 'ChangedAttributes' with value 'FALSE' which I don't know why it does not show the real column name and its values.

I passed ToObject(GetDepartments.List) to the DataSet parameter. GetDepartments is an Aggregate


Oh yeah, you can't just use the output of a query, you need to define a structure.

Glad it worked!

Could you please show what you did, cause i tried doing the same whatever was in the other post but it shows "empty" in the first cell in the excel. No other data is displayed. 

Hi @Akash Venugopal,

If you want the oml with the tests I made it's here.

Hope it helps,

Pedro

AdvancedExcelDemo.oml

I'll update if this works. I tried it but now the everything is empty.

No worries @Pedro Marques, i was able to find the problem. Thank you for helping me out

Hi Kenneth,

The other approach is to mention the sheets while creating the Workbook Create action something like below


Please fins attached oml for creating excel with two sheets.

Best Regards

Devendra

ExcelTest.oml

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.