Tag: CFML

Posted on

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

Adobe ColdFusion logo

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>
Posted on

Issues with CFML Syntax when Switching from Adobe ColdFusion to Railo

Adobe ColdFusion logo

Railo is an open source CFML server that can replace Adobe (or Macromedia) ColdFusion. I ported several CFML-based apps from an old instance of Macromedia ColdFusion MX 6 to Railo 3.2.1 about two years ago, and it’s been relatively trouble-free since the change.

However, the change itself was fraught with difficulties. That was due, not to problems with Railo, but to the forgiving nature of ColdFusion (CF). If the CFML wasn’t written exactly right, CF would parse it fine, but Railo wouldn’t take it. An example of this is the following code:

Before:

	<option value="A" < cfif #Sec_Level# eq 'A'>selected
		</cfif>>Administrator
		<option value="D" <cfif #Sec_Level# eq 'D'>selected
		</cfif>>Data Entry/General User

After:

	<option value="A" <cfif #Sec_Level# eq 'A'>selected="selected"</cfif>>Administrator</option>
	<option value="D" <cfif #Sec_Level# eq 'D'>selected="selected"</cfif>>Data Entry/General User</option>

The “Before” code gave the error: “no matching start tag for end tag [cfif]”.

The key change that was required wasn’t the closing option tag or the change to the selected attribute; that was just good form. It was the space between the “<” and the “cfif” in the opening cfif statements that caused the error. Simply removing that space fixed the problem.

Seemingly small problems like this will cause Railo (at least version 3.2.1) to throw errors. If the code is written properly to begin with, this will likely not happen.