Append Excel Template

Comments

1 comment

  • Avatar
    Adrian Williams

    As noted in the Help text for the Append Excel Node, the node provides limited support for the preservation of cell formatting and formulas. You can maximize the amount of information preserved by changing the ExistingDataPreservation property to PreserveAll. However, please note the caveat in the Help text relating to the memory utilization when this feature is used.

    Depending on the requirements for the final spreadsheet you may want to consider modifying your template spreadsheet to include an additional sheet that is used as the staging table for the data written to the spreadsheet. You can then reference the staging data from your main sheet in the workbook. The cell formatting would be applied to the cells in the main sheet rather than the cells in the staging table.

    In this example, Sheet 1 is the main sheet and its cells have the required formatting (A1 - C5). These cells reference the appropriate cells in the Data sheet (where the data will be written to). 

     

    The input pin of the Append Data node is renamed from 'in1' to 'Data' meaning that - with the SheetMatchMethod set to Name (the default) - the node will write the data at the input pin to the Data sheet. The OutputSpec property is configured to start writing the data at the second row in the Data sheet:

    In this example the DataAppendMethod was set to clear the existing data in the Data sheet and the ExistingDataPreservation property was set to Preserve All. If you want to retain existing data in the staging Data sheet you can set the DataAppendMethod to Append.

    The updated spreadsheet retained the formatting in the main sheet (Sheet 1)

    and the raw data was written to the second sheet (Data)

    0
    Comment actions Permalink

Please sign in to leave a comment.



Powered by Zendesk