FME and Excel – Writing Dynamic Formulas

Using FME to write data out to an Excel spreadsheet, nothing too difficult with that, but I want to have the last line in the worksheet include some totals of columns.  The number of rows is changeable and I want the total in the same column as the data so I can’t just do =Sum(C:C).

The first step is to find a formula that sums all the data for the column for all row above, but not including the current cell.

By using COLUMN() and ROW() functions to get the current cell, and the INDIRECT() function to use the reference the formula is:

=SUM(INDIRECT(ADDRESS(1,COLUMN())&”:”&ADDRESS(ROW()-1,COLUMN())))

Or if there is a header row to skip:

=SUM(INDIRECT(ADDRESS(2,COLUMN())&”:”&ADDRESS(ROW()-1,COLUMN())))

The result is:

Now we have that next step is how to write formulas into a cell with FME.

The documentation on how to do this is here:

https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/xlsx/XLSX_writer.htm

Using the above example we have three features each with a single attribute called “header”.  The values of which are 1, 2, 3.

The workspace to do this is simple:

And the results are:

To add a formula in row 5 we have to create another feature and make sure it is last.  There are a number of ways to do this, I’ll create an extra feature in the Creator and use a sampler to get the last feature and for now set the value of header to “sum goes here” :

The result of this is:

Now to put a formula in that cell. For the last feature make the header attribute blank and create a new header.formula attribute with the formula above:

Add a header.formula field to the Excel writer as well.

Run the workspace and the Excel file should be as required:

Test it with another number of features created to make sure it is really dynamic: