November 7, 2018
Spreadsheet Data Formatting Not Working
Comments
(5)
November 7, 2018
Spreadsheet Data Formatting Not Working
Newbie 2 posts
Followers: 1 people
(5)

I have a function to generate a single worksheet in an excel xlsx workbook. I am passing in a sheet name, a comma separated list of column names and a query of data.  No matter what I do, I can’t seem to get a data format to be applied to a cell. I have seen a few examples and it’s using similar methodology, which leaves me scratching my head.

Is there a sequence that must be followed for the formatting to be applied?

Do I need to write the file and then update it with the formatting?

// Create a spreadsheet object with the sheetname passed into the function.
variables.spreadsheetObj = spreadsheetNew(“#ARGUMENTS.sheetName#”,true);

//Add the header row column labels
spreadSheetAddRow(variables.spreadsheetObj, “#ARGUMENTS.columnNames#”);

//Format the header row
SpreadsheetFormatCellRange(variables.spreadsheetObj, variables.headerFormat, 1, 1, 1, #variables.numberOfColumns#);

//Add the data from the query
spreadsheetAddRows(variables.spreadsheetObj,variables.dataQueryResults,2,1,false,[“”],false);

//Format the data rows with the formatting from above, and apply to the rest of the rows.
spreadsheetFormatRows(variables.spreadsheetObj,variables.rowFormat,”2-#variables.lastExcelRow#”);

//Set the values of a format struct
variables.cellFormat = StructNew();
variables.cellFormat.dataformat = “($#,##0_($#,##0)”;

//I Loop through a comma separated list of the columns that need formatting and run this function
spreadsheetFormatCellRange(variables.spreadsheetObj, variables.cellFormat, 2, #columnNumber#, 21, #columnNumber#);

//Write the excel file.

5 Comments
2018-11-08 19:35:25
2018-11-08 19:35:25

When I was using ColdFusion 6, 7, 8 & 9, I couldn’t get the date format to work using CFSpreadsheet either.  I don’t know if anything’s been updated since then (it would be interesting to compare code & generated Excel files), but we’ve been using the following workarounds since to output formatted dates and produce consistent results w/correctly formatted dates:1.  Ben Nadel’s POIUtility.cfc (faster, generates smaller native XLS files)2. CSV (using quoted “yyyy-mm-dd” and “yyyy-mm-dd HH:mm:ss” syntax)  (I wrote a UDF that allows formatting to be defined for each column.)3. Export as CSV (using quoted “m/d/yyyy” and “m/d/yyyy h:mm tt” syntax) then convert using command line w/Total CSV Converter to generate native Excel XLS & XLSX files. Magnitudes faster than CFSpreadsheet on large datasets.If anyone is successful consistently formatting dates using CFSpreadsheet, please provide some sample CFML source, examples of multiple date formats and indicate the version of ColdFusion used.  I’d really prefer to use only ColdFusion’s CFSpreadsheet to do this.[NOTE: The comment tool seems to not retain formatting.  I submitted 3 different times before my message could be saved and I haven’t been able to retain the same formatting after multiple attempts.]

Like
2018-11-08 14:41:20
2018-11-08 14:41:20

When I was using ColdFusion 6, 7, 8 & 9, I couldn’t get the date format to work using CFSpreadsheet either.  I don’t know if anything’s been updated since then (it would be interesting to compare code & generated Excel files), but we’ve been using the following workarounds since to output formatted dates and produce consistent results w/correctly formatted dates:

Ben Nadel’s POIUtility.cfc (faster, generates smaller native XLS files)
CSV (using quoted “yyyy-mm-dd” and “yyyy-mm-dd HH:mm:ss” syntax)  (I wrote a UDF that allows formatting to be defined for each column.)
Export as CSV (using quoted “m/d/yyyy” and “m/d/yyyy h:mm tt” syntax) then convert using command line w/Total CSV Converter to generate native Excel XLS & XLSX files. Magnitudes faster than CFSpreadsheet on large datasets.

If anyone is successful consistently formatting dates using CFSpreadsheet, please provide some sample CFML source, examples of multiple date formats and indicate the version of ColdFusion used.  I’d really prefer to use only ColdFusion’s CFSpreadsheet to do this.

Like
2018-11-08 14:38:19
2018-11-08 14:38:19

When I was using ColdFusion 6, 7, 8 & 9, I couldn’t get the date format to work using CFSpreadsheet either.  I don’t know if anything’s been updated since then (it would be interesting to compare code & generated Excel files), but we’ve been using the following workarounds since to output formatted dates and it produces consistent results w/correctly formatted dates:

Ben Nadel’s POIUtility.cfc (faster, generates smaller native XLS files)
CSV (using quoted “yyyy-mm-dd” and “yyyy-mm-dd HH:mm:ss” syntax)  (I wrote a UDF that allows formatting to be defined for each column.)
Export as CSV (using quoted “m/d/yyyy” and “m/d/yyyy h:mm tt” syntax) then convert using command line w/Total CSV Converter to generate native Excel XLS & XLSX files. Magnitudes faster than CFSpreadsheet on large datasets.

If anyone is successful consistently formatting dates using CFSpreadsheet, please provide some sample CFML source, examples of multiple date formats and indicate the version of ColdFusion used.  I’d really prefer to use only ColdFusion’s CFSpreadsheet to do this.

 

Like
2018-11-08 14:22:15
2018-11-08 14:22:15

I’ve always add data and build the table,  then format, then apply widths.
So in your code example just move your formatting functions to the bottom.
I’ve also found it’s usually better to format general things, rows, columns, then come back and format specific cells, etc.

Good luck 🙂

 

Like
2018-11-08 10:53:12
2018-11-08 10:53:12

Snazzo, since you say that you have seen examples that “should” work, can you confirm first if you run those, do THEY work? If not on one server, what about on another? Your problem (with your code or such sample code) may be a config issue on your server (perhaps failed updates, or something else).

You also don’t say what version of CF you’re running? Let us now also the update level (both are the CF admin “settings summary” page). It could even be that your problem is due to some bug long-since solved, for the version of CF you’re running.

Let us now if these help get you started. If you feel adamant that “all is right” for your server (and others you test) and that this is some bug in CF, then besides reporting the version and update level, it would help you also to offer some standalone example (in as few lines as possible) that proves what is “not working”, so that folks here might try it on their own servers and confirm if they see the same.

Like
Add Comment