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 ROW() function, 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 A17 — 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 A16, 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.
