I have a SpreadsheetDataWriter component writing to an XLSX file. The incoming metadata contains a field called "Zone" and its value is a single alpha character, such as "A". I need to write the entire input record as a row in the Excel workbook, but in a sheet named "Zone A".
The problem is, I cannot specify anything other than a hard-coded sheet name/number or an input metadata field for the Sheet property. I cannot setup a dictionary value to use since the Sheet property will not accept it. The component has no input mapping. And I have tried to place it in a subgraph and put that subgraph in a loop to use a parameter, since the Sheet property will accept a parameter. But the problem there is that the loop still sends all records to the subgraph at once, even though its inside of a loop, so they all write to one sheet.
Because of the lack of input mapping and usability of dictionary values in the Sheet property, what I am trying to accomplish seems impossible. What am I missing? Is there a workaround?
Thanks.
The problem is, I cannot specify anything other than a hard-coded sheet name/number or an input metadata field for the Sheet property. I cannot setup a dictionary value to use since the Sheet property will not accept it. The component has no input mapping. And I have tried to place it in a subgraph and put that subgraph in a loop to use a parameter, since the Sheet property will accept a parameter. But the problem there is that the loop still sends all records to the subgraph at once, even though its inside of a loop, so they all write to one sheet.
Because of the lack of input mapping and usability of dictionary values in the Sheet property, what I am trying to accomplish seems impossible. What am I missing? Is there a workaround?
Thanks.
-
The point is that the SpreadSheetDataWriter has been primarily designed to be able to partition data into separate files with user-defined file names (like output_<fieldValue1>). However, there isn't an easy way how to do that inside one file for sheet names yet.
In order to have a field-related name of each sheet you just need to make sure that there is the exact field in your data (storing exactly the same value as is the desired name of the sheet). Then you can simply setup property "Sheet" to "Partition data into sheets by data fields" and select the right field from the list of available fields.
In your situation, let me suggest a little workaround. In order to name the sheets to be something like "Zone <fieldValue1>, you should add a Reformat component right before the SpreadsheetDataWriter. Setup the Reformat component to add an extra field to your data (please note that you have to also create appropriate metadata to it). You can define the field in the Transform attribute as follows:concat("Zone " , $in.0.Zone)
Then you can split data into sheets using the Sheet attribute and the new field (as described above). Also, as I understand that you don't want to have this extra field in your excel file, I would also recommend you to use "Exclude fields" property in SpreadsheetData Writer and set the newly created field to this attribute.
I am attaching an example graph so that you can review all setups that I've suggested.
I will also log a ticket to our development queue to revise the Sheet functionality for the future releases of CloverETL.
Best Regards,
Eva
Please sign in to leave a comment.
Comments 1