Highlighted

cfspreadsheet and empty Dates

New Here ,
Sep 04, 2014

Copy link to clipboard

Copied

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

Views

444

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

cfspreadsheet and empty Dates

New Here ,
Sep 04, 2014

Copy link to clipboard

Copied

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

Views

445

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 04, 2014 0
Explorer ,
Sep 04, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 04, 2014 0
New Here ,
Sep 05, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 05, 2014 0
Advocate ,
Sep 08, 2014

Copy link to clipboard

Copied

Have you tried, for example:

if ( desiredCompletionDate != "" )

{

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

}

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 08, 2014 0
PiyushN LATEST
Explorer ,
Sep 09, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 09, 2014 1