CODEWITHSUNDEEP
coldfusioncoldfusion-10
Steve
Steve

Reputation: 3095

CFSpreadSheet - format column not formatting the entire column

Using CF10 Standard to create a spreadsheet from a query. No matter what I've tried so far, the formatting for a specific column stops at row 32 (1 header row, 31 data), even though the entire sheet is populated to 186 rows.

<cfscript>
 dfStyle=StructNew();
 dfStyle.fgcolor="pale_blue";
 dfStyle.dataformat="mm/dd/yyyy";
 theSheet = SpreadSheetNew('mysheet');
 SpreadSheetAddRow(theSheet,'SID,FIRST,LAST,ADDRESS1,ADDRESS2,CITY,STATE,ZIP,EMAIL,ADDED,PID');
 SpreadSheetAddRows(theSheet,qry);
 SpreadSheetFormatColumn(theSheet,dfStyle,10);
</cfscript>

I'm trying to get a consistent mm/dd/yyyy format on the 'ADDED' column. Instead, I'm getting that through row 31, and then getting dates like 41937.56594 on the of the rows.

enter image description here

In the formula bar I show "10/20/2014 12:25:23 PM" as the first value and "41932.552037037" as the 2nd value.

If I format the date in the query (i.e. date_format(sp_add,'%c/%e/%Y') AS spadd) I do get a nice date format all the way down the column, but the blue still stops at row 32.

Here is the cfoutput of the anonymized query - top row is formatted, bottom row loses formatting (background color - formatting dates in query). I also dropped the ADDRESS2 column from the query for now.

enter image description here

Upvotes: 1

Views: 2159

Answers (1)

Mark A Kruger
Mark A Kruger

Reputation: 7193

I'm not sure what the problem is, but try using the XML format option (the option to produce an xlsx file instead of the older style xls file). In your code add the "true" as a second argument to your spreadsheetnew() function call.

<cfscript>
 dfStyle=StructNew();
 dfStyle.fgcolor="pale_blue";
 dfStyle.dataformat="mm/dd/yyyy";

 theSheet = SpreadSheetNew('mysheet',TRUE);

 SpreadSheetAddRow(theSheet,'SID,FIRST,LAST,ADDRESS1,ADDRESS2,CITY,STATE,ZIP,EMAIL,ADDED,PID');
 SpreadSheetAddRows(theSheet,qry);
 SpreadSheetFormatColumn(theSheet,dfStyle,10);
</cfscript>

This will solve the problem - though we don't know why :)

Upvotes: 4

Related Questions