cfsavecontent

[newcf]

Hi,,

I am query from my table and want to save this query results into excel file in to specific location.

1.  Is there the way to overrite the file each time the page is run?

2. when open the myReport.xls, i got message: The file you are trying to open "myReport.xls" is in diffrent format than specific the file extension.

3. I then change myReport.xls to myReport.xlsx then run the page to get new file and sitll got diffrent error when open it : This file can't be previewed because of error in MS Excel previewever.

4. If i change myReport.xls to myReport.csv then create the file again, and re-open and it show no mesage but it shows all HTML attribute (td, tr, table).  How can i save the resust into csv file?

What can i do not having either one of message ablove when i open the file? i am using MX excel 2010 version. Woul you pls advice this?

Thanks

<cfsavecontent variable="saveContent1">

<cfcontent type="application/msexcel">

<cfheader name="Content-Disposition" value="filename=report/myReport.xls">

    <table cols="4" border="1">

        <tr>

            <td>sale</td>

            <td>delivery</td>

        </tr>

        <cfloop query="q_sale">

            <tr>

                <td>#sale#</td>

                <td>#del#</td>

            </tr>

        </cfloop>

    </table>

</cfoutput>

</cfsavecontent>

<cffile action="write" mode="777" output="#saveContent1#" file="D:Inetpub/myapps/report/myReport.xls">

[Adam Cameron.]

What version of ColdFusion are you using? You should be using spreadsheet operations to create Excel files.

All you are doing here is saving some HTML to file, and sving it with an XLS extension. That doesn't make it an XLS file, it just makes it incorrectly named.

--

Adam

[Dan_Bracuk]

What you are attempting worked quite well until Microsoft introduced Office 2007.  There are better options available now.  If you are on CF 9 or higher, there are ColdFusion tags and functions for this.  Adam gave you a link.  If you are on a lower version, download the apache poi and learn how to use it.  That's what we did when Office 2007 came out.  We were using CF 7 at the time.

[newcf]

i am using CF 7. i've try apache poi but never been sucess cause always got error for poi.classes is undefined even i have all files from lib.

if i want to export results into csv file, is there the way to do it?

thanks

[Adam Cameron.]

Ben Nadel wrote a wrapper for POI which is fairly straight forward: http://www.bennadel.com/projects/poi-utility.htm. Maybe give that ago.

But if you want a CSV, why are you even trying to involve Excel or POI? Just create a CSV. There are even UDFs out there already to do so: http://www.cflib.org/index.cfm?event=page.udfbyid&udfid=1197

--

Adam

[newcf]

This code worked fine but it didn't import the date over to csv file.  all i have is 00:00.0 for all dates.  Can one one tell me why? thx

00:00.0

<cfscript>

/**

* Convert the query into a CSV format using Java StringBuffer Class.

*

* @param query      The query to convert. (Required)

* @param headers      A list of headers to use for the first row of the CSV string. Defaults to all the columns. (Optional)

* @param cols      The columns from the query to transform. Defaults to all the columns. (Optional)

* @return Returns a string.

* @author Qasim Rasheed (qasimrasheed@hotmail.com)

* @version 1, March 23, 2005

*/

function QueryToCSV2(query){

    var csv = createobject( 'java', 'java.lang.StringBuffer');

    var i = 1;

    var j = 1;

    var cols = "";

    var headers = "";

    var endOfLine = chr(13) & chr(10);

    if (arraylen(arguments) gte 2) headers = arguments[2];

    if (arraylen(arguments) gte 3) cols = arguments[3];

    if (not len( trim( cols ) ) ) cols = query.columnlist;

    if (not len( trim( headers ) ) ) headers = cols;

    headers = listtoarray( headers );

    cols = listtoarray( cols );

    for (i = 1; i lte arraylen( headers ); i = i + 1)

        csv.append( '"' & headers & '",' );

    csv.append( endOfLine );

    for (i = 1; i lte query.recordcount; i= i + 1){

        for (j = 1; j lte arraylen( cols ); j=j + 1){

            if (isNumeric( query[cols] ) )

                csv.append( query[cols] & ',' );

            else

                csv.append( '"' & query[cols] & '",' );

        }

        csv.append( endOfLine );

    }

    return csv.toString();

}

</cfscript>

<cfset csvText = QueryToCSV2(myquery)>

<cffile action="write" output="#csvText#" file="#ExpandPath('./report/test.csv')#">

[p_sim]

Use Ben Nadel's POI as Adam suggested.

To answer your last question, you might want to experiment with Date/Time functions. Here: csv.append( '"' & query[cols] & '",' );, you dump the date value; and see why generates zeros.