earwig75
asked on
Can I format excel columns with coldfusion 11? Using CFSPREADSHEET?
I have a simple ColdFusion query that returns 4 columns. 1 column may contain leading zeros. I'd like to display these numbers as text, or at least format it so the whole number shows up properly. Can someone assist?
I've read through coldfusion's documentation and looked at tons of examples but nothing works.
I've read through coldfusion's documentation and looked at tons of examples but nothing works.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Updated - Fixed code example
First, not sure if you saw the updated answer. Assuming the query column stores values with leading zeroes, ie "0000123" or "00000", then specifying cell type "string" should accomplish what you need, without any extra formatting:
<cfset SpreadSheetSetCellValue(sh
But to answer your question, yes you can also use "@" to format a cell, row or column as text:
<cfset SpreadsheetFormatCell(shee
However ... formatting doesn't always work as expected. CF has some issues with that. Sometimes CF ignores formats unless a cell(s) have values, but ... if you add the values first they get converted to numbers. Kind of a catch 22.
Try the updated example first. It worked for me w/CF11. If so, you can skip formatting.
First, not sure if you saw the updated answer. Assuming the query column stores values with leading zeroes, ie "0000123" or "00000", then specifying cell type "string" should accomplish what you need, without any extra formatting:
<cfset SpreadSheetSetCellValue(sh
But to answer your question, yes you can also use "@" to format a cell, row or column as text:
<cfset SpreadsheetFormatCell(shee
However ... formatting doesn't always work as expected. CF has some issues with that. Sometimes CF ignores formats unless a cell(s) have values, but ... if you add the values first they get converted to numbers. Kind of a catch 22.
Try the updated example first. It worked for me w/CF11. If so, you can skip formatting.
ASKER
I am formatting entire columns, do I just change it to the function for the column?
Would something like this work?
<cfset SpreadsheetFormatColumn(sO
Would something like this work?
<cfset SpreadsheetFormatColumn(sO
Did you try the original example (updated)? Because you shouldn't need SpreadsheetFormatColumn. The example already preserves leading zeroes in the query column values.
Maybe, but ... probably not. Like I mentioned earlier, CF has issues with formatting. SpreadsheetFormatColumn did not work for me. That's why I recommended the code above instead.
Side note, if you do use formatting, the correct syntax to create a "text" cell is:
{dataFormat="@"}
.... rather than this:
{dataformat="string"}.
Would something like this work?
Maybe, but ... probably not. Like I mentioned earlier, CF has issues with formatting. SpreadsheetFormatColumn did not work for me. That's why I recommended the code above instead.
Side note, if you do use formatting, the correct syntax to create a "text" cell is:
{dataFormat="@"}
.... rather than this:
{dataformat="string"}.
ASKER
I need this ability for another page that has 50 columns, and I was hoping I wouldn't have to re-do the entire cfspreadsheet code etc.
Well without seeing the code, I can't say if it'll work or not. It didn't in this example, but you could always test it with the correct format ie {dataFormat="@"} and see. Unfortunately though, some of the CF spreadsheet functions are still hit or miss ...
ASKER
<cfset SpreadsheetFormatColumn(sO