cfspreadsheet and empty Dates

[Miguel_Arvelo]

Hi everyone,

I'm currently working on a project that has been using Html tables to generate CSV files for a long time and since we moved recently from CF7MX to CF10 we decided to make use of the spreadsheet functions.  However, we are dealing with a database that has some date fields that can be empty from time to time and the Excel download is throwing an error that says

'' is not a valid Date or Timestring

I was wondering if there was a known way to go around this and still get the fields in the Excel file generated as Date fields.

Cordially,

Miguel Arvelo

[Piyush Nayak]

Miguel,

What do you mean by "excel download"? Can you please supply some details ( perhaps, the relevant code extract ) on how you are doing that.

If you are using CF to create those spreadsheets you can try using the "SpreadsheetFormatCell" function to set cells to a date-type format. CF11 allows you to set the datatype with

SpreadsheetAddRow(s) function as well.

Ref.

https://wikidocs.adobe.com/wiki/display/coldfusionen/SpreadsheetFormatCell

https://wikidocs.adobe.com/wiki/display/coldfusionen/SpreadsheetAddRows

[Miguel_Arvelo]

Piyush,

By "Excel Download" I mean that i have this HTML table my users are seeing on their screens that we allow them to export into Excel and download. For many years we simply created the Excel file by simply dropping that HTML table into a file and adding a xls extension on it, but with the upgrade to CF10 we want to make use of the spreadsheet functions.

We are using those functions already (not SpreadsheetFormatCell but SpreadsheetFormatColumn) in a way similar to this

<!--- Do a query, this is an example and does not reflect my actual query but provides a good idea for what I'm asking --->
<cfquery name="myAssignedRequests">

  Select
    req.ID,

    req.rqstr,

    req.desiredCompletionDate,

    req.actualCompletionDate
  From
    Requests req
  Where

    req.assignedWorker = "Miguel"

</cfquery>

<!--- Set up the Column Headers and Data Types for the Excel file being generated; Note that Days Remaining does not come from the previous query, we're calculating it (we are actually doing something similar for some data fields we can't get from the DB --->

<cfset colHeaders = "ID, Requester, Desired Completion Date, Actual Completion Date, Days Remaining"

<cfset colDataTypes = "VarChar, VarChar, Date, Date, Number" />

<!--- Create a new query object with the correct Header names and Data Types to be exported to Excel --->

<cfset qryForExcel = QueryNew(colHeaders, colDataTypes) />

<!--- Fill the second query object using the first query's data --->

<cfloop query="myAssignedRequests">
  <!--- Add new row at the end of the query --->

  <cfset QueryAddRow(qryForExcel) />
  <cfscript>

    // Set the values for each row

    qryForExcel["ID"][qryForExcel.recordCount] = ID;

    qryForExcel["Requester"][qryForExcel.recordCount] = rqstr;

    qryForExcel["Desired Completion Date"][qryForExcel.recordCount] = desiredCompletionDate;

    qryForExcel["Actual Completion Date"][qryForExcel.recordCount] = actualCompletionDate;

    qryForExcel["Days Remaining"][qryForExcel.recordCount] = dateDiff("d", Now(), desiredCompletionDate);
  </cfscript>

</cfloop>

<!--- Create the Spreadsheet, add the Header and then add the data --->

<cfset spreadsheet = spreadsheetNew("My Assigned Work") />
<cfset SpreadsheetAddRow(spreadsheet, colHeaders, 1, 1, false />

<cfset SpreadsheetAddRows(spreadsheet, qryForExcel) />

<!--- Prepare file for download  --->

<cfset filename = "MyWork.xls" />
<cfheader name="Content-Disposition" value="attachment; filename=#filename#" />

<cfcontent type="application/msexcel" variable="#SpreadSheetReadBinary(spreadsheet)#" />

<!--- END OF CODE --->

This is an idea of what we're doing but since if any row has a NULL in actual completion date when the code gets to the SpreadSheetAddRows call it returns the following error message

'' is not a valid Date or Timestring

I hope this is clearer, and thanks for any help you can provide

[EddieLotter]

Have you tried, for example:

if ( desiredCompletionDate != "" )

{

     qryForExcel["Desired Completion Date"][qryForExcel.recordCount] = desiredCompletionDate;

}

[Piyush Nayak]

Miguel,

The issue is reproducible. Thanks for the details and for raising the bug.

BTW, this works in CF11, which uses a newer library and has some spreadsheet related enhancements.