Skip to main content
J
JC13
Inspiring
April 20, 2006
Answered

Generating Excel File?

  • April 20, 2006
  • 4 replies
  • 612 views
Hi,

I have never generated an Excel file via CF before and was hoping that someone here could help me? I have looked through past threads and I don't think they have totally answered my questions. What I need to do is create a piece of code that I will schedule to run at a specific interval of time that will generate an Excel file using data queried, save the file to the server, and then e-mail the Excel file to a vendor. This sounds feasible to me, I am just not sure how to approach it. I have seen in past threads people using the <cfcontent> tag to generate excel formatted data into a browser, but I need this data to actually be posted into excel. Please help.

Thanks,
Joe
    This topic has been closed for replies.
    Correct answer JC13
    Well I did not go with the CSV, I came up with my own solution that generates the Excel file, saves it to the server, and e-mails it to whomever with all proper excel formatting. This is how I did it, hopefully this may help someone else in the future...

    Since Excel can read HTML table formatting and use it for importing into cells/columns I figured this would be the best route to go for formatting an XLS file. So first thing needed is to set a variable
    <cfparam name="myContent" default="">
    Then go through and setup your table and every time you have any table information you will need to append it to your set variable as text make sure that all <table><tr> and <td> tags are also included otherwise it will throw everything off...
    <cfset myContent = myContent&"<table cols=30>">
    <cfset myContent = myContent&"<tr><td>Hello World</td>">
    <cfif VARIABLE EQ SOMETHING>
    <cfset myContent = myContent&"<td>Line 2</td></tr>">
    <cfelse>
    <cfset myContent = myContent&"</tr></table>">
    Now what has been created is a variable with content that creates a cell with Hello world in it, and if the if statement is true then another cell is created within the row with Line 2 displayed. Now in order for this to be created as a file you will need to use the cffile command as such...
    <cffile action="write" file="#application.paht#\FILENAME.xls" output="#myContent#" nameconflict="overwrite">

    If you run this code you will get your excel file on the server.

    jc

    4 replies

    J
    JC13Author
    Inspiring
    May 3, 2006
    Oh, and to set column widths that Excel will obey you can do the following:

    <cfset myContent = myContent&"<td width=80 style=""text-align:center;""> 04</td>">

    The   before any variable will preserve leading zeros.

    jc
    J
    JC13AuthorCorrect answer
    Inspiring
    May 3, 2006
    Well I did not go with the CSV, I came up with my own solution that generates the Excel file, saves it to the server, and e-mails it to whomever with all proper excel formatting. This is how I did it, hopefully this may help someone else in the future...

    Since Excel can read HTML table formatting and use it for importing into cells/columns I figured this would be the best route to go for formatting an XLS file. So first thing needed is to set a variable
    <cfparam name="myContent" default="">
    Then go through and setup your table and every time you have any table information you will need to append it to your set variable as text make sure that all <table><tr> and <td> tags are also included otherwise it will throw everything off...
    <cfset myContent = myContent&"<table cols=30>">
    <cfset myContent = myContent&"<tr><td>Hello World</td>">
    <cfif VARIABLE EQ SOMETHING>
    <cfset myContent = myContent&"<td>Line 2</td></tr>">
    <cfelse>
    <cfset myContent = myContent&"</tr></table>">
    Now what has been created is a variable with content that creates a cell with Hello world in it, and if the if statement is true then another cell is created within the row with Line 2 displayed. Now in order for this to be created as a file you will need to use the cffile command as such...
    <cffile action="write" file="#application.paht#\FILENAME.xls" output="#myContent#" nameconflict="overwrite">

    If you run this code you will get your excel file on the server.

    jc
    J
    JC13Author
    Inspiring
    April 20, 2006
    Thanks for the advice guys. I'll give the CSV a go.

    -Joe
    P
    philh
    Inspiring
    April 20, 2006
    CSV files are like rough drafts. It's impossible to format cells, insert pictures and charts, etc. using this approach.

    If you want a polished product, use the Excel object or inspect the Office XML document model.
    N
    Newsgroup_User
    Inspiring
    April 20, 2006
    Joe,
    I had poor results with the Excel format. Specifically, the generated excel
    file from CF wouldn't work with MS Word Mail Merge documents. What I ended
    up doing was creating a CSV document and it worked best. Do to that I just
    opened my template with the <cfcontent> tag. Here is some sample code for
    generating a roster list for a class:

    <cfset cr = Chr(13) & Chr(10)>
    <cfcontent type="application/csv">
    <cfquery name="qRoster" datasource="#TRN_DSN#">
    select firstname, lastname, secdescr
    from registrants
    </cfquery>
    <!--- Determine temp filename --->
    <cfheader name="Content-Disposition" value="filename=roster.csv">
    First,Last,Location
    <cfoutput query="qRoster">
    #FIRSTNAME#,#LASTNAME#,#SecDescr##cr#
    </cfoutput>

    This generates a csv file that the user is prompted for download, but I
    suspect if you run it with full path and filename for the filename variable
    in cfheader, it will create the file on the server. I think you'll need to
    expirement a bit here. Then you could mail the file in question after it
    was created.

    Hope this helps.

    ....Brad



    "JC13" <webforumsuser@macromedia.com> wrote in message
    news:e285qi$8ut$1@forums.macromedia.com...
    Hi,

    I have never generated an Excel file via CF before and was hoping that
    someone
    here could help me? I have looked through past threads and I don't think
    they
    have totally answered my questions. What I need to do is create a piece of
    code that I will schedule to run at a specific interval of time that will
    generate an Excel file using data queried, save the file to the server, and
    then e-mail the Excel file to a vendor. This sounds feasible to me, I am
    just
    not sure how to approach it. I have seen in past threads people using the
    <cfcontent> tag to generate excel formatted data into a browser, but I need
    this data to actually be posted into excel. Please help.

    Thanks,
    Joe


    tclaremont
    tclaremont
    Inspiring
    April 20, 2006
    I, too, have had to resort to the CSV format approach.

    When I left it as XLS, the file would automatically open in the visitors browser (assuming IE) and would not prompt them to save the file.

    When I changed it to CSV, it generates the "save as" dialog box instead of assuming that the user wants to open it.

    I know this is not the exact same problem that you are describing, but it is another vote to go with CSV.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices