Solved

ColdFusion  Receive an error when exporting date with excel spreadsheet function

Posted on 2014-03-26
5
518 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 15

Expert Comment

by:myselfrandhawa
Comment Utility
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 15

Expert Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
Please convert query for an Access database.
0
 
LVL 15

Accepted Solution

by:
myselfrandhawa earned 500 total points
Comment Utility
do it like this

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

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

Author Closing Comment

by:DJPr0
Comment Utility
Thanks myselfrandhawa
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now