When JavaScript’s deleteRow() Method Doesn’t Work

A section of JavaScript code that uses the method deleteRow() was giving me a headache in IE 11. I did not encounter this when using Chrome.

In the code segment below, the variable “oHtmlTable” is assigned to an HTML table that already exists on a webpage.

//delete prior entries
while(oHtmlTable.rows.length > 1)
{
	oHtmlTable.deleteRow(-1);
}

For no apparent reason, during the execution of the while loop, the deleteRow() method would quit working, but did not cause an exception to be thrown. Since the condition on the while loop would never be met if the rows in the table were no longer being deleted, this became an infinite loop.

IE gave no obvious indication as to what the problem was, just the standard “[domain name] is not responding” message at the bottom of the screen.

not responding

The code inside the while loop deletes the last row in the table. Whatever was inside the last row was keeping IE from successfully deleting it. First, I tried emptying the innerHTML contents of the last row to see if that would allow the deletion to continue. This had no effect. However, deleting the outerHTML contents of the row did the trick.

I created a variable in which to store the number of rows in the table, then an if statement to determine whether the row count changed after the deleteRow method was executed. If it was not, then on the next pass through the loop, the row would be deleted.

//delete prior entries
var nLength = 0;
while(oHtmlTable.rows.length > 1)
{
	nLength = oHtmlTable.rows.length;
	oHtmlTable.deleteRow(-1);
	if (nLength == oHtmlTable.rows.length)
	{
		oHtmlTable.rows[nLength-1].outerHTML = '';
	}
}

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>