portal entry

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.

Comment by Charlie Arehart

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 :)  

Comment by Jim Priest

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 <a href="https://github.com/bennadel/POIUtility.cfc" rel="nofollow">POIUtility.cfc</a> (faster, generates smaller native XLS files) CSV (using quoted "yyyy-mm-dd" and "yyyy-mm-dd HH:mm:ss" syntax)  <em>(I wrote a <a href="https://gist.github.com/JamoCA/b2d0467dd914909f00bf" rel="nofollow">UDF</a> that allows formatting to be defined for each column.)</em> Export as CSV (using quoted "m/d/yyyy" and "m/d/yyyy h:mm tt" syntax) then convert using command line w/<a href="https://www.coolutils.com/TotalCSVConverter" rel="nofollow">Total CSV Converter</a> 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.  

Comment by James Moberg

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 <a href="https://github.com/bennadel/POIUtility.cfc" rel="nofollow">POIUtility.cfc</a> (faster, generates smaller native XLS files) CSV (using quoted "yyyy-mm-dd" and "yyyy-mm-dd HH:mm:ss" syntax)  (I wrote a <a href="https://gist.github.com/JamoCA/b2d0467dd914909f00bf" rel="nofollow">UDF</a> 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/<a href="https://www.coolutils.com/TotalCSVConverter" rel="nofollow">Total CSV Converter</a> 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.

Comment by James Moberg

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 <a href="https://github.com/bennadel/POIUtility.cfc" rel="nofollow">POIUtility.cfc</a> (faster, generates smaller native XLS files)2. CSV (using quoted “yyyy-mm-dd” and “yyyy-mm-dd HH:mm:ss” syntax)  (I wrote a <a href="https://gist.github.com/JamoCA/b2d0467dd914909f00bf" rel="nofollow">UDF</a> 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/<a href="https://www.coolutils.com/TotalCSVConverter" rel="nofollow">Total CSV Converter</a> 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.]

Comment by James Moberg