Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

ColdFusion Receive an error when exporting date with excel spreadsheet function

My Excel export function throws and error if there is no value for date:
'' is an invalid date or time string.

Formatting code:
<cfset SpreadsheetFormatColumn(sObj, {alignment="right", dataformat="mm/dd/yyyy"}, 21)>
0
DJPr0
Asked:
DJPr0
  • 3
  • 2
1 Solution
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
The date/time will not get populated in cfspreadsheet if it is null, that is a bug as of now. all you have to do is use the CAST or convert function of sql to convert your datetime in varchar like the following example:

<cfquery name="myQueryDetails">
    	SELECT `ID`, `fname`, `lname`, `subject`, 
        CONVERT(`createddate`, CHAR(50)) as created 
        FROM mytable 
 <cfquery>

<!--- Make a spreadsheet object --->
<cfset s = spreadsheetNew()>
<!--- Add header row --->
<cfset spreadsheetAddRow(s, "Firstname,Lastname,Subject,CreatedOn")>
<!--- format header --->    
<cfset spreadsheetFormatRow(s, {bold=true, fgcolor="lemon_chiffon",fontsize=14}, 1)>
<!--- Add query --->
<cfset spreadsheetAddRows(s, myQueryDetails)>

<cfset filename = expandPath("myexcel.xls")>

<cfset spreadsheetWrite(s, filename, true)>



<!--- <cfset SpreadsheetFormatColumn(sObj, {alignment="right", dataformat="mm/dd/yyyy"}, 21)> --->

Open in new window

0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
I am making use of MYSQL Database, if any database let me know, i will add the query for that
0
 
DJPr0Author Commented:
Please convert query for an Access database.
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
do it like this

CONVERT(varchar(10), [MyDateTimecolumn], 20)

http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
DJPr0Author Commented:
Thanks myselfrandhawa
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now