Solved

ColdFusion  Receive an error when exporting date with excel spreadsheet function

Posted on 2014-03-26
5
544 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

628 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