LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gmullin

I want to be able to dynamically create the XML in the WorkbookSpec parameter when acquiring an Excel file. Ideally I would just build it up in BrainScript depending on certain conditions, and be able to plug it straight by saying OK use the data from Field X, but I can't seem to be able to do that.

At the minute I've come up with a bit of a long workaround of having a graph that acquires the Excel file and then has a bunch of run parameters for sheet name, data start row, header etc. and outputs to a brd. I saved that as a BRX and dropped it on the server to be called so that I can dynamically pick which sheet on the excel workbook I need, or where the data starts from and so on. Seems like a very roundabout way of doing it though.

Any suggestions?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: prasmussen

You can do this with an indirect parameter. Essentially, you write the workbook spec to a tmp file on the server and then load in that tmp file to the workspec field. See below:

node:Write_tmp_Workbook_Spec
bretype:lal1::Output Raw
editor:Label=Write tmp Workbook Spec
editor:sortkey=5584f10a64924421
input:@5106ebc83f035045/=^.55fc79c503144270
output:@4ffae39e126d75ee/=
prop:DataField=workbook_spec
prop:File={{^WkbookFile^}}
prop:FileExistsBehavior=Overwrite
prop:FileParamType=Literal
prop:PassThroughFields=Unused
editor:XY=460,180
end:Write_tmp_Workbook_Spec

node:New_Version
bretype:core::Excel File
editor:Label=New Version
editor:sortkey=55fc7501407247d8
output:@48cf6cd2305e5f95/=
inclock:/=Output_XML
inclock:/=Write_tmp_Workbook_Spec
prop:CellErrorAction=Ignore
prop:DuplicateFieldNameAction=AutoName
prop:File=C:\filename.xlsx
prop:WorkbookSpec=<<EOX
{{^WkbookFile^}}
EOX
editor:XY=590,70
editor:propdef=WorkbookSpec|string|Input|ls.brain. node.excelFile.workbookSpec|None
end:New_Version

node:Filter_2
bretype:core::Filter
editor:sortkey=55fc784f173a4ca2
input:@40fd2c74167f1ca2/=Write_tmp_Workbook_Spec.4ffae39e126d75ee
output:@40fd2c7420761db6/=
inclock:/=New_Version
prop:Script=<<EOX
deleteFile("{{^WkbookFile^}}"
emit *

EOX
editor:XY=790,180
end:Filter_2

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: gmullin

I got a solution off Larry that worked for me. It can be changed to read in directly from a filter node and use BrainScript to build up the WorkbookSpec.

IndirectParamsExcel.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mlam

Thanks guys, this is great. In case it is useful for others, I've extended the example above to loop through each worksheet and append data to a single Excel file, which can then be read in in one go and subsequent logic applied.

IndirectParamsExcel_withloop.zip


I've come across a few scenarios where there were varying number of worksheets and WorksheetSpec parameter was needed so adding a metadata output was not sufficient here (metadata output reference: http://community.lavastorm.com/threa...ple+worksheets ).

Use case example: I have a workbook for a client that has 50+ worksheets and after each reporting period a new worksheet is added. The worksheet is formatted as a report and has image/ logo headers and footers. Then I have a different workbook for each client, all of whom have different number of worksheets (different reporting periods, products and regions etc). The way I want to process the data is similar but I want a faster and more dynamic way to read in so many worksheets for each client.