Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

Outputting a dynamic XLS file with cfcontent

Hi All,

I am working at some mailmerge functionality and am using some code that automatically outputs a query into a .xls file. The weird thing that is happening though is that the more items there are in the query the further down the page in the xls file it is created.

For example, running three different queries,

the first returned two items and the headers and then two rows of data started on line 29.

the second returned 0 rows of data and the headers are on row 11 (obviously with no rows of data below)

the third returned about 30 items and started on row ~200 (with headers and then data below)

I would like to see if i can get all the data to just output at the top of the xls file rather than hundreds of rows down.

Here is what I've got that is outputting the above:

<query name="GetInfo"> - The queries are not returning any blank lines when I run them from query analyzer (that was a thought I had that could be causing it but to no avail)

<cfheader name="content-disposition" value="inline;filename=ClientReport.xls">
<cfcontent type="application/msexcel">
<table border="1">
     <tr>
      <th>Title</th>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Address Line 1</th>
          <th>Address Line 2</th>
          <th>Suburb</th>
          <th>State</th>
          <th>Postcode</th>
            <th>WorkPhone</th>
            <th>MobilePhone</th>
            <th>HomePhone</th>
            <th>FaxPhone</th>
            <th>Email</th>
     </tr>
     <cfoutput query="GetInfo">
          <tr>
                  <td>#TitleDescription#</td>
               <td>#FirstName#</td>
               <td>#Surname#</td>
               <td>#PostalAddressLine1#</td>
               <td>#PostalAddressLine2#</td>
               <td>#Suburb#</td>
               <td>#StateName#</td>
               <td>#Postcode#</td>
                        <td>#WorkPhone#</td>
                        <td>#MobilePhone#</td>
                        <td>#HomePhone#</td>
                        <td>#FaxPhone#</td>
                  <td>#EmailAddress#</td>
          </tr>
     </cfoutput>
</table>

Any ideas on why this may be happening and how to correct it would be very much appreciated.

Thanks!
Brad
0
bradderick
Asked:
bradderick
  • 3
  • 2
  • 2
1 Solution
 
pinaldaveCommented:
<cfcontent type="application/vnd.ms-excel" reset="no">
<cfheader name="Content-Disposition" value="attachment;filename=data.xls">
0
 
bradderickAuthor Commented:
pinaldave, thanks for your quick response, i tried that top one you suggested but am still having that problem of the thirty or more blank lines before the data starts.

I tried the method of creating a tab list but then it doesnt open in excel with different information in different cells. It's all in the same line.

Anymore ideas?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
pinaldaveCommented:
<cfprocessingdirective suppresswhitespace="true">
     <CFHEADER NAME="content-disposition" VALUE="attachment;filename=#url.filename#">
    your table should do the trick
</cfprocessingdirective>
0
 
bradderickAuthor Commented:
hmmm, i tried

<cfprocessingdirective suppresswhitespace="true">
      <cfheader name="content-disposition" value="attachment;filename=AffililatesReport.xls">
      <cfcontent type="application/msexcel">
</cfprocessingdirective>

and

<cfprocessingdirective suppresswhitespace="true">
      <cfheader name="content-disposition" value="attachment;filename=AffililatesReport.xls">
      <cfcontent type="application/msexcel">
<table>
....
</table>
</cfprocessingdirective>

Neither seemed to make a lot of difference. It's a bizarre bug that's for sure!
0
 
mosphatCommented:
Maybe this will work:

<cfheader name="content-disposition" value="attachment;filename=AffililatesReport.xls">
<cfcontent type="application/msexcel" reset="true">

My guess is the extra lines are caused by whitespace created before you start building the table.
The reset="true" attribute makes sure everything outputted so far, will be erased.
0
 
mosphatCommented:
Whitespace seems to be one of the hardest problems the tackle in CF. My suggestion is a very effective way of getting rid of whitespace (and everything else for that matter).
Unfortunately it is hard to tell whether that suggestion works for bradderick, since we can't tell whether there's any other output that would get lost this way. If there's not, there's a good chance reset="true" works.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now