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
bradderickAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.