Here we describe some commonly-used coding idioms that can help in developing Excel templates. This document applies to Apsona's Excel merge tool
Showing row numbers in generated lists
This example illustrates an Excel trick to show row numbers alongside the expanded data rows that the document generator produces. The
screen shot below illustrates the result we want: In the first column (the one to the left of the Name
column), we show the
record number of the record in the list. This record number is not generated by the merge tool, but rather by Excel.
To achieve this effect, we add an Excel formula to the corresponding cell in the template, as in the screen shot below.
The idea is to use Excel's
, which returns the row number of the
current row. In this example, we know that
the header row in the template is row 16, so in the record number cell of template's data row — which is cell
— we add a formula
=ROW() - ROW(A16)
. The formula calculates the difference between the current row number and the
row number of the header row. So when the template expander produces the full list of rows into the output Excel sheet, it propagates
that formula to all the rows it produces, and Excel takes care of calculating the necessary record number values.
Notice that, in the above screen shots, the formula in the template uses the reference
, but the actual data in the
produced output begins at row 20. This is because there is other dynamically produced data above the generated row, and Apsona has
automatically recalculated the formulas in the template so that they apply to the correct rows after the data has been filled into the template.