Link to home
Start Free TrialLog in
Avatar of bradderick
bradderickFlag for Australia

asked on

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
Avatar of pinaldave
pinaldave
Flag of India image

<cfcontent type="application/vnd.ms-excel" reset="no">
<cfheader name="Content-Disposition" value="attachment;filename=data.xls">
Avatar of bradderick

ASKER

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?
<cfprocessingdirective suppresswhitespace="true">
     <CFHEADER NAME="content-disposition" VALUE="attachment;filename=#url.filename#">
    your table should do the trick
</cfprocessingdirective>
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!
ASKER CERTIFIED SOLUTION
Avatar of mosphat
mosphat

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mosphat
mosphat

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.