Reputation: 413
I am trying to export a query to an Excel document.
Here is the code I am using:
<cfscript>
//Use an absolute path for the files. --->
theDir=GetDirectoryFromPath(GetCurrentTemplatePath());
theFile=theDir & "invoicesDue.xls";
//Create an empty ColdFusion spreadsheet object. --->
theSheet = SpreadsheetNew("invoicesData");
//Populate the object with a query. --->
SpreadsheetAddRows(theSheet,invoicesDue);
</cfscript>
<!--- Write the sheet to a file --->
<cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheetname="invoicesDue" overwrite=true>
The error I am getting is:
'' is an invalid date or time string.
121: SpreadsheetAddRows(theSheet,invoicesDue);
The thing is, I have dumped out my query and there are no quotation marks anywhere to be seen and all the date/time cells are either populated with a datetime such as "2011-03-31 00:00:00.0" or an empty string.
I wondered if anyone else has encountered this error before as I can't see the cause for it.
Upvotes: 3
Views: 1555
Reputation: 403
As an alternative, I use Ben Nadel's queryToCSV() function and bypass the "quirkiness" of cfspreadsheet
. When all I need is a single page csv / xls file, it works like a charm.
Upvotes: 0
Reputation: 413
cfspreadsheet doesn't like empty values for date fields. In the end I used:
CAST(emptyDate as varchar)
Which seems to be a fix.
Upvotes: 2