Solved

ColdFusion  Receive an error when exporting date with excel spreadsheet function

Posted on 2014-03-26
5
529 Views
Last Modified: 2014-03-27
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
Comment
Question by:DJPr0
  • 3
  • 2
5 Comments
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 39958052
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
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 39958053
I am making use of MYSQL Database, if any database let me know, i will add the query for that
0
 

Author Comment

by:DJPr0
ID: 39958848
Please convert query for an Access database.
0
 
LVL 16

Accepted Solution

by:
Gurpreet Singh Randhawa earned 500 total points
ID: 39959079
do it like this

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

http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 

Author Closing Comment

by:DJPr0
ID: 39959107
Thanks myselfrandhawa
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question