Exporting an HTML Table to Excel While Applying Relative Cell References Using ColdFusion Markup Language

A new problem I was tasked with was to make enhancements to a report built using CFML by changing the totals column to a SUM() function when the report is exported to Microsoft Excel.

The trick here was that the table was being exported as HTML code, and then opened in Excel. Since the location of the totals row would vary depending on the number of rows in the report, it was uncertain as to how the SUM() function would be constructed. Regular cell references (such as A1:A10, $A1:$A10, etc.) would not be sufficient. The key was to use a combination of the Excel functions INDIRECT(), ADDRESS(), ROW(), and COLUMN().

After much experimentation, I discovered that the magic expression that will give sum all of the rows in a given column and place that value in the cell beneath those rows is:

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

Finding this was a huge step in being able to complete this task. However, this report had multiple sections and I didn’t want every total to SUM all of the rows in previous sections. Fortunately, I knew how to get the number of rows in each query run by ColdFusion against the database using the RecordCount variable that is a result of the cfquery function.

By changing the CFML code to include the Excel expression in this format:

=SUM(INDIRECT(ADDRESS(ROW()-#whatever.RecordCount#,COLUMN())  ":"  ADDRESS(ROW()-1,COLUMN())))

the resulting spreadsheet replaced the ColdFusion-calculated totals with the Excel expression, and the Excel-calculated totals.

<cfif variables.Export EQ 'True' AND Isdefined("variables.ExportToExcel")>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td class="num">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
    <td colspan="9" style="visibility:hidden;display:none;">&nbsp;</td>
    <td class="num" style="visibility:hidden;display:none;">=SUM(INDIRECT(ADDRESS(ROW()-#oFundDataOM.InLe.RecordCount#,COLUMN())":"ADDRESS(ROW()-1,COLUMN())))</td>
<cfelse>
    <td class="num">#Numberformat(variables.TotalYear,'999,999,999,999')#</td>
    <td class="num">#Numberformat(variables.TotalOMPI,'999,999,999,999')#</td>
    <td class="num">#Numberformat(variables.TotalNBC,'.99')#</td>
    <td class="num">#Numberformat(variables.TotalNetBen,'999,999,999,999.9')#</td>
    <td colspan="9" style="visibility:hidden;display:none;">&nbsp;</td>
    <td class="num" style="visibility:hidden;display:none;">#Numberformat(variables.TotalInv,'999,999,999,999')#</td>
</cfif>
Houston, TX 77002

Leave a Reply