Using a Template File when Writing Excel Data

Introduction

This example expands on the article Overview of Excel Writer Parameters. In that example, you learned how to write weather data stored in an Excel Spreadsheet, added a column then wrote the data back to the same Excel Spreadsheet. We're going to expand on that example.
We decided we needed more data, so we broke the precipitation values into both rain and snow for each month. Additionally, to quickly compare the data we moved it all into a single spreadsheet. Now with this data, we carried out some analysis of the data: added some additional worksheets with charts, data summaries, and pivot tables:

After doing all this work you obtain data for a different city or, in this case, a new CSV file with weather data from 1940-2012 instead of 2018-2020 for the original data. Using FME, you can rewrite the RawData worksheet, and the results will be reflected in the charts, summaries, and pivot table you built. This is how you use an Excel Template File in the FME Excel writer.
 

Excel Template File

The Excel Template File used by FME can be an Excel file that has a consistent location where you can write the raw data. The easiest location to use is a named range. In the example below, we'll be using a template file called WeatherDataTemplate.xlsx. The data location is on the RawData sheet in a named range called WeatherTable. All the other sheets in the Excel file, Summary&Chart, Pivot, and PivotTable reference the named range.
 

Step-by-Step Instructions

1. Create a Blank Workspace
Open FME Workbench and create a blank workspace. Add a CSV reader to the canvas and browse to the Vancouver Weather Data1940-2012.csv file. Open the parameters and confirm that the table is being read correctly, then click OK twice.


2. Create Row ID
When using a template file, you will need to create a row ID that will correspond to the template. That way FME will know exactly which data is being inserted where. To do this add a Counter transformer to the canvas and connect it to the CSV reader feature type. In the Counter parameters, change the Count Output Attribute to _row, then have the count start at 2. We want the count to start at 2 because the first row in our Excel Template is the header row with the attribute (column names)  and the data starts at row 2. 


3. Create Cumulative Formula
Within FME we can create Excel formulas so that if you update the Excel spreadsheet directly, the formula will reflect the updates. We're going to create a formula to calculate the cumulative precipitation for each year. For the first row of data, the Excel formula will look like:

 =SUM(C2:N2)

Add an AttributeManager to the canvas and connect it to the Counter. In the parameters, create a new attribute called CUMULATIVE, but leave the value blank. This attribute will hold the values calculated by the formula. Next, create another attribute called CUMULATIVE.formula. The .formula is important as it tells FME to put the formula values in the backend and not write it directly. If you forget the .formula, the formula will be written as a string. 
For the CUMULATIVE.formula attribute value, click the ellipsis to open the text editor. In the text editor, enter the following formula:

=SUM(C@Value(_row):N@Value(_row))

This formula will calculate the values from column C to column N for each row.


4. Write to Microsoft Excel
Add a Microsoft Excel writer to the canvas, browse to a location to save the file, and call it WeatherDataOutput.xlsx. Change the Sheet Definition to Automatic, then open the writer parameters. Set Overwrite Existing File to Yes; this will ensure that the data is overwritten, and not appended to an existing file (if a file already exists). Next, set the Template File parameter to WeatherDataTemplate.xlsx. Click OK twice to add the writer. 


In the Feature Type dialog, change the Sheet Name to RawData/WeatherTable. This means you'll be writing to the named range called WeatherTable in the RawData sheet. You can write to multiple named ranges in the same sheet.
Then expand the Drop/Truncate section, and set the Truncate Existing Sheet/Named Range parameter to Yes. FME will drop the Raw Data WeatherTable named range and recreate it with the new data. Click OK to finish adding the writer.


5. Connect and Modify Writer
Finish connecting the writer feature type to the AttributeManager, once it is connected, reopen the parameters. We need to modify which attributes are written out. Switch to the User Attributes tab and change the Definition to Manual. Remove _row and then click OK. Because CUMULATIVE.formula only contains the formula values, the attribute won’t show up directly in the final Excel file. For more information, refer to the section on Formulas in the Excel reader/writer documentation.

6. Run the Workspace
Run the workspace and check the results in Microsoft Excel. Your RawData sheet should be populated with the new data and the charts and pivot tables automatically updated by Excel

Data Attribution

The data used here originates from open data made available by the City of Vancouver , British Columbia. It contains information licensed under the Open Government License - Vancouver.