Highlighted

SpreadsheetFormatColumns Not working What's going wrong?

Explorer ,
Mar 26, 2016

Copy link to clipboard

Copied

Server: CF10

Platform: Windows and Linux (Win in DEV windows in PROD)

So I'm generating this awesome excel file for a client but having trouble getting date fields to behave properly. Everything else seems to work just fine but when I send the data to excel to be generated, excel treats it as just text and when this filed gets sorted it handles it as such.

More info: The column I'm trying to configure is called Arrival Date it is arriving formatted as mm/dd/yyyy (I have tried to format is as m/d/yy but when it arrives in the sheet that doesn't work as well)

I'm also including an image of the sorted row. It does think its a valid but just doesn't sort it in chronological order.

DateSort.PNG

Code:

<cfset filename = expandPath("./TDYData_(#DateFormat(now(),'mmddyy')#).xls")>

<!--- Make a spreadsheet object --->

<cfset s = spreadsheetNew("TDYData")>

<!--- Add header row --->

<cfset spreadsheetAddRow(s, "TDY Phase,Full Name,Employment Category,Gender,Originating Agency,Agency Comments,Originating Office,Office Comments,Originating Country,TDY Request Received,Mission Office Supported,Type of TDY Support,eCC Submission,eCC Approval,eCC Point of Contact,Date of Departure from Originating Country,Arrival Date,Departure Date,Accomodation Type,Accomodation Comments,Assigned Desk,Local Mobile Number,TDY Comments")>

<!--- format header --->

<cfset spreadsheetFormatRow(s,

{

bold=true,

fgcolor="lemon_chiffon",

fontsize=10

},

1)>

<!--- Add query --->

<cfset spreadsheetAddRows(s, myExcel)>

<cfset SpreadSheetAddFreezePane(s,0,1)>

<cfset spreadsheetFormatColumn(s, {dataformat="m/d/yy"}, 17) />

<cfset spreadsheetFormatColumn(s, {alignment="right"}, 16) />

<cfheader name="content-disposition" value="attachment; filename=TDY_Data_(#DateFormat(now(),'mmddyy')#).xls">

<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

Any ideas?

Thanks

I believe the issue is because you're not using SpreadsheetSetCellValue() to enter data.  It will respect your dataFormat.

It's kind of a pain to manually enter (via cfoutput or cfloop) data into the cells, but it does give you more granular control over things like formatting and such.

Also, if you're using integers (and only integers) in any cells, it will appear as though that gets inserted as text, too.  You have to set the dataformat, insert the data, then set the dataformat, again.  Weird, I know, but it works.

HTH,

^_^

Views

875

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

SpreadsheetFormatColumns Not working What's going wrong?

Explorer ,
Mar 26, 2016

Copy link to clipboard

Copied

Server: CF10

Platform: Windows and Linux (Win in DEV windows in PROD)

So I'm generating this awesome excel file for a client but having trouble getting date fields to behave properly. Everything else seems to work just fine but when I send the data to excel to be generated, excel treats it as just text and when this filed gets sorted it handles it as such.

More info: The column I'm trying to configure is called Arrival Date it is arriving formatted as mm/dd/yyyy (I have tried to format is as m/d/yy but when it arrives in the sheet that doesn't work as well)

I'm also including an image of the sorted row. It does think its a valid but just doesn't sort it in chronological order.

DateSort.PNG

Code:

<cfset filename = expandPath("./TDYData_(#DateFormat(now(),'mmddyy')#).xls")>

<!--- Make a spreadsheet object --->

<cfset s = spreadsheetNew("TDYData")>

<!--- Add header row --->

<cfset spreadsheetAddRow(s, "TDY Phase,Full Name,Employment Category,Gender,Originating Agency,Agency Comments,Originating Office,Office Comments,Originating Country,TDY Request Received,Mission Office Supported,Type of TDY Support,eCC Submission,eCC Approval,eCC Point of Contact,Date of Departure from Originating Country,Arrival Date,Departure Date,Accomodation Type,Accomodation Comments,Assigned Desk,Local Mobile Number,TDY Comments")>

<!--- format header --->

<cfset spreadsheetFormatRow(s,

{

bold=true,

fgcolor="lemon_chiffon",

fontsize=10

},

1)>

<!--- Add query --->

<cfset spreadsheetAddRows(s, myExcel)>

<cfset SpreadSheetAddFreezePane(s,0,1)>

<cfset spreadsheetFormatColumn(s, {dataformat="m/d/yy"}, 17) />

<cfset spreadsheetFormatColumn(s, {alignment="right"}, 16) />

<cfheader name="content-disposition" value="attachment; filename=TDY_Data_(#DateFormat(now(),'mmddyy')#).xls">

<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

Any ideas?

Thanks

I believe the issue is because you're not using SpreadsheetSetCellValue() to enter data.  It will respect your dataFormat.

It's kind of a pain to manually enter (via cfoutput or cfloop) data into the cells, but it does give you more granular control over things like formatting and such.

Also, if you're using integers (and only integers) in any cells, it will appear as though that gets inserted as text, too.  You have to set the dataformat, insert the data, then set the dataformat, again.  Weird, I know, but it works.

HTH,

^_^

Views

876

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
Mar 26, 2016 0
LEGEND ,
Mar 28, 2016

Copy link to clipboard

Copied

I believe the issue is because you're not using SpreadsheetSetCellValue() to enter data.  It will respect your dataFormat.

It's kind of a pain to manually enter (via cfoutput or cfloop) data into the cells, but it does give you more granular control over things like formatting and such.

Also, if you're using integers (and only integers) in any cells, it will appear as though that gets inserted as text, too.  You have to set the dataformat, insert the data, then set the dataformat, again.  Weird, I know, but it works.

HTH,

^_^

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...
Mar 28, 2016 0
Explorer ,
Mar 28, 2016

Copy link to clipboard

Copied

WolfShade,

Thanks for answering. I would love to test this out but I guess I am confused at to where the dataformat will stick. There is no place in SpreadsheetSetCellValue to set dataformat. Or will it respect it in the spreadsheetFormatColumn that is being set?

Also, it does sound like a pain to do this but I could generate the whole spreadsheet then go back and reset the values for that one column through the entire spreadsheet before exporting it.

Do you have an quick example to show me what you mean? I just need to get started.

Thanks

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...
Mar 28, 2016 0
LEGEND ,
Mar 28, 2016

Copy link to clipboard

Copied

Hi, mwoods1971‌,

As long as you use SpreadsheetAddRow() to insert column headers and data from a query, your date format will be essentially ignored and the dates will be entered as "General", which is what is throwing off your sorting.

I find that using CFSCRIPT and manually looping the query to insert the data is tedious and time-consuming, but worth the effort.  I had posted a URL in one of Raymond Camden's blog posts that linked to a code repo I created that outlined exactly what I did.  I'll see if I can find it.  Barring that, I can try to make another one.

BRB,

^_^

UPDATE:  Okay.. apparently when I deleted my Disqus account (because it's a STUPID service that really doesn't work), it deleted every comment I've ever made.  So.. that link to the code repo is lost.  I'll try to get another one set up and post the link, here.

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...
Mar 28, 2016 0
Explorer ,
Mar 28, 2016

Copy link to clipboard

Copied

Thanks I'll be sure to try it.

I have added this:

    <cfset spreadsheetFormatColumn(s, {dataformat="m/d/yy"}, 13) />
    <cfset spreadsheetFormatColumn(s, {dataformat="m/d/yy"}, 14) />
    <cfset spreadsheetFormatColumn(s, {dataformat="m/d/yy"}, 16) />
    <cfset spreadsheetFormatColumn(s, {dataformat="m/d/yy"}, 17) />
    <cfset spreadsheetFormatColumn(s, {dataformat="m/d/yy"}, 18) />   
    <cfset spreadsheetFormatColumn(s, {alignment="right"}, 16) />

<cfset therow = 0>

<cfoutput query="myExcel" startrow="1">

  <cfset therow = myExcel.currentrow + 1>

  <cfset SpreadsheetSetCellValue(s, DateArrive, therow, 17)>

</cfoutput>

So my idea was to loop back over the original query and rewrite using the date value and inserting it back into the cell.

I found something along the way that someone else had done that had a similar problem:

Forcing values to be inserted into spreadsheets as text | cfSimplicity

So, I'm guessing if we do it the manual way, I would have to do a SpreadsheetSetCellValue to enter in my column headers, then loop over the query to insert the data cell by cell.

But, if I do that will it automatically create a new row in the spreadsheet every time?

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...
Mar 28, 2016 0
LEGEND ,
Mar 28, 2016

Copy link to clipboard

Copied

No.. you have to manually specify the cell by column and row, which is where the CFSCRIPT loop comes in.  You can use the loop index to specify the row, then manually enter the column number that corresponds with the query column.  I typically enter the column headers in row 1, then leave row 2 empty, and add 2 to the loop index for the row in which to enter the query data.

HTH,

^_^

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...
Mar 28, 2016 0
Explorer ,
Mar 28, 2016

Copy link to clipboard

Copied

I may have a different solution:

<cfset therow = 0>

<cfoutput query="myExcel" startrow="1">

  <cfset therow = myExcel.currentrow + 1>

  <cfif len(eCCSubDate) GT 0>

  <cfset SpreadsheetSetCellFormula(s,"DATEVALUE(#Chr(34)##eCCSubDate##Chr(34)#)",therow,13)>

  </cfif>

  <cfif len(eCCApproved) GT 0>

  <cfset SpreadsheetSetCellFormula(s,"DATEVALUE(#Chr(34)##eCCApproved##Chr(34)#)",therow,14)>

  </cfif>

  <cfif len(DateDepartCntry) GT 0>

  <cfset SpreadsheetSetCellFormula(s,"DATEVALUE(#Chr(34)##DateDepartCntry##Chr(34)#)",therow,16)>

  </cfif>

  <cfif len(DateArrive) GT 0>

  <cfset SpreadsheetSetCellFormula(s,"DATEVALUE(#Chr(34)##DateArrive##Chr(34)#)",therow,17)>

  </cfif>

  <cfif len(DateDepart) GT 0>

  <cfset SpreadsheetSetCellFormula(s,"DATEVALUE(#Chr(34)##DateDepart##Chr(34)#)",therow,18)>

  </cfif>         

</cfoutput>

I'm using the Excel DateValue function that makes it accept the cell value as a date. It does appear to work and sort properly now.

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...
Mar 28, 2016 1
LEGEND ,
Mar 28, 2016

Copy link to clipboard

Copied

Glad to hear you got it working.

Just in case, I have created a sample of how I do it on jsfiddle, for anyone to review.

HTH,

^_^

PS  Thank you for marking my answer correct.  I do appreciate it.

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...
Mar 28, 2016 0
Explorer ,
Mar 28, 2016

Copy link to clipboard

Copied

Thanks for answering, Are you on Stackoverflow? I'd love to give you some street cred on there too!

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...
Mar 28, 2016 0
LEGEND ,
Mar 28, 2016

Copy link to clipboard

Copied

I appreciate the thought.  However, I deleted my SO account just days after creating it.

I feel that SO (and the other related forums) are more of a popularity contest than an actual support system.  I used to stare in slackjawed disbelief at the number of downvotes for no reason other than just to downvote perfectly decent questions and/or answers.

V/r,

^_^

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...
Mar 28, 2016 0