Export html/cf to an excel sheet with a twist

[KOVMoe]

I am trying to take a table, HTML, and export to Excel. here is the code I sue to create the table:
<table id="TableID" class="G_TBL_Info" G_rollover id="#VARIABLES.strFuncID#_tblReportTable1" width="#VARIABLES.strModuleWidth#" cellspacing="0" cellpadding="0">
<tr>
<th align="left" colspan="20"> &nbsp;&nbsp; #dateformat(qryEstimateDispatch.dat_val_dt,"mmmm dd, yyyy - dddd")#</th>
</tr>
<tr>
<!---<th style="width:1px"></th>--->
<th>Location</td>
<th>Type</th>
<th>Delivery Volume</th>
<th>Delivery Stops</th>
<th>Pickup Stops</th>
<th>Total SPC</th>
<th>Total FSP</th>
<th>FSP SPC</th>
<th>Total PSP</th>
<th>Helpers</th>
<th>Teams</th>
<th>Team SPC</th>
<th>AM Hours</th>
<th>SL Hours</th>
<th>PPH</th>
<th>In CTR</th>
<th>Under 8</th>
<th>Over 9.5</th>
<th>% In Range</th>
</tr>


<cfloop query="qryEstimateDispatchTotal">
<tr>
<td rowspan="6">
Location Name</a></td>
</tr>
<tr >
<td align="center">ACT</td>
<td align="center">#numberformat(a,'___,___,___')#</td>
<td align="center">#numberformat(b,'___,___,___')#</td>
<td align="center">#numberformat(c,'___,___,___')#</td>
<td align="center">#numberformat(d,'___.0')#</td>
<td align="center">#numberformat(e,'___,___,___')#</td>
<td align="center">#numberformat(f,'___.0')#</td>
<td align="center">#numberformat(g,'___,___,___')#</td>
<td align="center">#numberformat(h,'___,___,___')#</td>
<td align="center">#numberformat(i,'___,___,___')#</td>
<td align="center">#numberformat(j,'___.0')#</td>
<td align="center">#numberformat(k,'____.00')#</td>
<td align="center">#numberformat(l,'____.00')#</td>
<td align="center">#numberformat(m,'____.0')#</td>
<td align="center">#numberformat(n'___,___,___')#</td>
<td align="center">#numberformat(p,'___,___,___')#</td>
<td align="center">#numberformat(q,'___,___,___')#</td>
<td align="center">#numberformat(r,'____.00')#</td>

</tr>

</tr>
</cfloop>
</table>

When the sheet sexports, It comes out as:
 February 16, 2005 ? Wednesday
Location Type Delivery Volume Delivery Stops Pickup Stops Total SPC Total FSP FSP SPC Total PSP Helpers Teams Team SPC AM Hours SL Hours PPH In CTR Under 8 Over 9.5 % In Range
88gone
ACT 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
PLN 299,014 118,491 24,979 117.4 1,093 129.4 129 27 22 166.4 234.98 21 83.2 599 0 0
EST 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
%ACT 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
%PLN 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

What I am seeing is the location name/number is not showing as a column, but as a row.

The code below is used to create the export:

if (eval(tableName).rows.rowSpan>1){
for (var z=0;z<eval(tableName).rows.rowSpan;z++)
{
oSheet.Rows(y+1+a+rownumber,xx+1+rowheight+z).BorderAround(1 , 2);
}
oSheet.Range(eval('"' + oSheet.rows(y+1+a+rownumber).Address + ':'
+ oSheet.Cells(y+1+a+rownumber).Address + '"')).Merge();
rowheight=rowheight+eval(tableName).rows.rowSpan-1;
}


//If colspan greater than one
if(eval(tableName).rows.cells(x).colSpan>1) {
for (var d=0;d<eval(tableName).rows.cells(x).colSpan;d++)
{
oSheet.Cells(y+1+a+rownumber,xx+1+colwidth+d).BorderAround(1 , 2); // 1 = linestyle=single , 2 = borderweight=thin
}
oSheet.Range(eval('"' + oSheet.Cells(y+1+a+rownumber, xx+1+colwidth).Address + ':' + oSheet.Cells(y+1+a+rownumber,
colwidth+xx+d).Address + '"')).Merge();
colwidth=colwidth+eval(tableName).rows.cells(x).colSpan-1;
}

}

Does any one have any ideas what I am doing wrong?

Thank you,

Moe-King of the Village Idiots.

"When in trouble,
when in doubt;
Run in circles-
SCREAM & SHOUT!!"
Burma Shave

 

[dadrobson]

You have a closing </tr> tag after the Location Name field, followed by an openint <tr> tag. As long as you have that, I think you'll have the Location Name in a separate row.

blog.robsondesign.com

 

[iguessthehandlecheck]

I have not gone in detail of your code, but did you try using cfcontent with type ms-excel to save it as an excel. That would be lot simpler. Also, your <tr> and </tr> before and after Location is making it a separate row. You can take those out, make it like follows:

<tr>
<td rowspan="6">
Location Name</a></td>
<td align="center">ACT</td>

but you probably would have to check the rowspan.
Hope that answers.