We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.
Our Support systems migrated on Saturday, May 21. We'll automatically forward you to the new location for this content.

Writing to Excel Template


1 comment

  • Avatar
    Adrian Williams

    I did some tests to look at the relative performance of writing the same data set with and without the use of a template. The number of columns was similar to your use case (55) but the number of records was reduced for the tests to 10K as the execution time for 1M records was also over 2 hours for me when using a template file.

    The use case where a template file was not configured was used to set a baseline when writing 10K records.

    When a template file was configured for the node, the execution time was approximately 12 times longer compared with the no template use case. The node's Optional properties were set at their defaults.

    In the next use case the FormatOutput property was set to use the cell format from the template file. In this case the node execution was approximately 2.7 times longer than the no template use case.

    In a further use case the PreserveCellStyles property was set to False. In this case the cell values are output in a format to match the input data type but the existing cell styles are not maintained. The execution time for this use case was comparable with the no template use case.

    So looking at these results, setting the node to not preserve the cell styles from the template file produced the best performance. However, depending on your template this may or may not be practical. If you can construct your template file to have a 'Raw_Data' worksheet you could then populate this worksheet with the data from Analyze without having to be concerned with maintaining any cell styles. Other worksheets in the workbook could then reference the values in the Raw_Data worksheet to produce the final formatted report.

    You may want to investigate the options yourself (using a moderate data set) to see what works best for you. 

    Note, the Excel specifications say the maximum number of records in a .xlsx is just over 1M.

    Comment actions Permalink

Please sign in to leave a comment.