Exporting to Excel for Mailmerging

Hi All,

This is more of a general question. I was wondering if anyone has the time to explain how (using what tags, functions etc) I can create a CF page that:

- creates a new excel file
-  exports certain fields of a Users table in a MSSQL database (say  into the worksheet (so i can then do a mailmerge into word)
- gives me the option to download the file
- deletes the file after downloading is done

If there is a better way to achieve the mailmerge then feel free to let me know that as well.

Thanks for your time.

Regards,
Brad
bradderickAsked:
Who is Participating?
 
mrichmonConnect With a Mentor Commented:
Okay it is actually really simple.

You can have CF generate an excel file that is streamed to the browser and so there is nevera  copy saved on the server - so no need to delete afterwards.

And no need to create another DSN

Here is the VERY SIMPLE code.  Notice that there is no <html> or <body> tags - only the few lines of code below.

<cfquery datasource="#mydsn#" name="GetInfo">
      SELECT FirstName, LastName, Address1, Address2, City, State, Zip, etc...
        FROM mytable
</cfquery>

<cfheader name="content-disposition" value="inline;filename=AddressReport.xls">
<cfcontent type="application/msexcel">
<table border="1">
      <tr>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Address Line 1</th>
            <th>Address Line 2</th>
            <th>City</th>
            <th>State</th>
            <th>Zip</th>
      </tr>
      <cfoutput query="GetInfo">
            <tr>
                  <td>#First Name#</td>
                  <td>#Last Name#</td>
                  <td>#Address Line 1#</td>
                  <td>#Address Line 2#</td>
                  <td>#City#</td>
                  <td>#State#</td>
                  <td>#Zip#</td>
            </tr>
      </cfoutput>
</table>
0
 
Tacobell777Commented:
If you are using MS SQL you should really use a DTS package to export to an excel file.. You can call the package with DTSRun, you can create the arguments for the package with DTSRunui, once you created the arguments you can execute it with cfexecute.
0
 
bradderickAuthor Commented:
Thanks for the quick reply.

Could you please be a little more explicit as I'm unsure as to what a DTS package is or how to use DTSRun

Regards,
Brad
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
bradderickAuthor Commented:
I would really like a  solution that I would be able to run on an externally hosted solution. So that should take in normal security that is applied to hosted websites.

Regards,
Brad
0
 
Tacobell777Commented:
this solution is not for you then..
0
 
anandkpCommented:
U cld create excel sheet from with MSSQL.
this wld export the results extracted by the query & save them to a excel file on the server.
u cld then use CFCONTENT tag to download the fiel & set the attribute for DELETEFILE="Yes" - so that it deletes as soon as the download it complete.
0
 
bradderickAuthor Commented:
hi anand,

could you elaborate a bit on your idea? How would I do that with MSSQL? Could i do it with a hosted solution?

Could I run the creation of the excel spreadsheet with a coldfusion tag somehow? So although the sheet is created with MSSQL it can be kicked off via CF somehow?

Regards,
Brad
0
 
anandkpCommented:
b4 u execute the MSSQL code - u cld have the file created via <CFFILE action="Write"
u cld create a new file & have a DSN point to it & then export ur results to that excel file.
[but i am searching for the code that cld write it from MSSQL itself - i remember having something similar to it]
0
 
bradderickAuthor Commented:
so how do you make a dsn point to it and use coldfusion to export results from a cfquery to an excel file after having created it with cffile ?
0
 
anandkpCommented:
u'll have to create the DSN via CFAdmin - have it point to this excel file [uncheck maintain connections - so the file can be deleted]
once the dsn is set - query it just the way u wld query ur table - via the DataSource name in CFquery.
u cld isnert/update/delete the values from this excel file thru ur CFM code.

Depending on the version of CF ur using - u cld also use DSNLess Connections [version 5 & earlier]
0
 
bradderickAuthor Commented:
interesting, interesting...

I'll have to do some more reading up on that method.

Did you find any more info for the mssql direct method and how to activate it using cf?

0
 
anandkpCommented:
I've used this earlier to export results - in a report format available for users to download ... via excel ... so i know it has to work for sure ... abt the direct method via MSSQL - i am looking into it ...
0
 
lxdevCommented:
As an alternative to mrichmon's code above, you can also create it as a CSV file:

<!--- START OF CODE --->
<cfsetting enablecfoutputonly="Yes">
<cfquery datasource="#mydsn#" name="GetInfo">
     SELECT FirstName, LastName, Address1, Address2, City, State, Zip
        FROM mytable
</cfquery>

<cfcontent type="application/csv">
<cfheader name="Content-Disposition" value="attachment; filename=AddressReport.csv">

<cfoutput>"First Name","Last Name","Address Line 1","Address Line 2","City","State","Zip"
</cfoutput>
<cfoutput query="GetInfo">"#GetInfo.FirstName#","#GetInfo.LastName#","#GetInfo.Address1#","#GetInfo.Address2#","#GetInfo.City#","#GetInfo.State#","#GetInfo.Zip#"
</cfoutput>
<!--- END OF CODE --->

If you use this solution, then you MUST remember to use the CFSETTING tag, which will enable output only between CFOUTPUT tags.
0
 
bradderickAuthor Commented:
Hi Guys,

Those are some excellent examples, I really appreciate you being so detailed.

If I did stream it to my browser, could I then use it in a mailmerge in word or would it be easier to create a downloadable copy to use with word? I would much rather not have any extraneous files floating around the server so if I can do a mailmerge using it just in my browser so much the better! In fact, the more streamlined it is the better.

Cheers,
Brad
0
 
mrichmonCommented:
If you use my method it does not create a file on the server.  Instead it sends the file to the user and either

a) prompts for download to save to client harddrive
or
b) displays in browser so user can save

A or B depends on the user's browser settings, but in 99% of the cases it will be A

Then once saved it is just a regular excel file that can be used with mailmerge.
0
 
bradderickAuthor Commented:
Hi Guys,

Thank you all for your input on this general question. Mrichmon's solution seems to be the cleanest and most efficient so I have accepted that as the answer.

Thanks again for your time all.

Regards,
Brad
0
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.

All Courses

From novice to tech pro — start learning today.