Skip to main content
WolfShade
WolfShade
Legend
November 8, 2010
Answered

Format columns for data intended for Excel

  • November 8, 2010
  • 1 reply
  • 2274 views

Hello, everyone.

I'm working on a document that runs a query, populates a <table> with the data, and then spits out an Excel sheet for the user to either open or save. I'm using the CFSCRIPT equivalent of CFCONTENT and CFHEADER to create the Excel file.

Code:
  <cfscript>
    pc = getpagecontext().getresponse();
    pc.getresponse().setcontenttype('application/vnd-ms.excel');
    pc.setHeader("Content-Disposition","attachment;filename=ThisData_#DateFormat(now(),'yyyy-mm-dd')#.xls");
  </cfscript>

Everything is working great, with one exception.

One of the query columns that is being returned is a datatype of varchar(10) - the data is a 'month year' value like "May 2010" or "Jun 2011". If you look at the data in a browser, it appears in that format ("month year"). But when the Excel file is created, it shows up as "May-12" or "Dec-12" (regardless of the month, the date is always 12.)

I'm pretty sure this is because in Excel the column is formatted as "General" when I need it to be "Text". Is there a way to tell CF that a particular column in Excel needs to be formatted a particular way?

Thanks,

^_^

    This topic has been closed for replies.
    Correct answer cfjedimaster

    This may help some:

    http://support.microsoft.com/kb/260239

    Basically, if you aren't using a 'proper' solution and outputting

    HTML, you have to give hints to Excel on how to display stuff.

    1 reply

    ilssac
    ilssac
    Inspiring
    November 8, 2010

    If you are using simple CSV data sent to a browser with a header that tells the browser to send the CSV data to Excel.  NO you have very little control over how excel formats CSV data.  This is exactly the same as if you where to create a text file with the same CSV data and open it with Excel.  You would get the same output.

    To have control over how Excel is going to format the data, you need to create an actual binary Excel data file, not just a text based CSV data file.  You can do this with the newer <cfspreadsheet....> tag in ColdFusion 9, or the POI API interface that has a lot of blogging details about it available from internet searches.

    You may also beable to do something if you figure out how Excel would store the formating and data in an XML text file, and create a similar XML data file.  The usual way to do this is to create a sample Excel file and then have Excel export it to XML.  Then view this in any number of XML viewers|editors and figure out how to create a similar file in ColdFusion.

    WolfShade
    WolfShadeAuthor
    Legend
    November 8, 2010

    Thanks for your reply, ilssac.

    I'm using something similar to this:

    <cfquery name="myQuery" (connection info)>

      EXEC myStoredProcedure @var1='data', @var2=number, @var3='more data'

    </cfquery>

    <cfscript>
    pc = getpagecontext().getresponse();
    pc.getresponse().setcontenttype('application/vnd-ms.excel');
    pc.setHeader("Content-Disposition","attachment;filename=myFilename_#DateFormat(now(),'yyyy-mm-dd')#.xls");
    </cfscript>

    <html>

    <head>blah blah blah</head>

    <body>

    <table>

    <tr>

    <td>Header 1</td>

    <td>Header 2</td>

    <td>Header 3</td>

    </tr>

    <cfoutput query="myQuery">

    <tr>

    <td>#myQuery.Column1#</td>

    <td>#myQuery.Column2#</td>

    <td>#myQuery.Column3#</td>

    </tr>

    </cfoutput>

    </table>  <cfabort>

    </body>

    By using the above method, am I submitting the data in CSV?  I didn't think I was, but I don't know the internal workings of CFCONTENT or CFHEADER.

    ^_^

    cfjedimaster
    cfjedimasterCorrect answer
    Inspiring
    November 8, 2010

    This may help some:

    http://support.microsoft.com/kb/260239

    Basically, if you aren't using a 'proper' solution and outputting

    HTML, you have to give hints to Excel on how to display stuff.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices