How to fix Number stored as text in cfspreadsheet

Question

I'm making a excel cell format in cfspreadsheet (Coldfusion 10) as a number but when it convert to excel it show warning at cell which is

Number Stored as Text.

Can I know how to fix this? Because I need the format as a number.Here is my code:

<cfscript> 
  theSheet = SpreadsheetNew("Order Details 1");
  SpreadsheetAddRow(theSheet, "NO,VENDOR, PART NUMBER, PART NAME, PSI, LEAD TIME, ,N-5, N-4, N-3,N-2, N-1, N, N+1, N+2, N+3, N+4, PACKING MONTH, PRODUCTION MONTH ,MONTH,YEAR",5,1);
  myFormat2=StructNew();
  myFormat2.bold=false;

  SpreadsheetFormatRow(theSheet,myFormat2,6);
  SpreadsheetAddRows(theSheet,getROW);
  SpreadsheetFormatColumn(theSheet,{dataformat="0"},5);
  SpreadsheetFormatColumn(theSheet,{alignment="right"},5);
  SpreadsheetFormatCellRange (theSheet,{font="Calibri"}, 7, 1, 2006, 17);
</cfscript>

Updated From Comments:

Sample Query value is 50 the datatype is number. My query is look like this.

SELECT psi||'%' FROM  vendor 

I think this is because the datatype is number and concatenate with % that is why it stored as text.

Answer 1

As Shawn said in the comments, if the query value includes a "%" (or appends one) then it's not a number. It's a string and that's why it's not working as expected.

-- returns a string
SELECT  numericColumnName ||'%' FROM tableName

Instead, the query should return the raw numeric value:

  SELECT  numericColumnName FROM tableName

Then format the spreadsheet cell using {dataformat='0"%"'}. Note nested quotes to prevent Excel from multiplying the value by 100 to make it a percent.

SpreadsheetFormatColumn(theSheet,{dataformat='0"%"'}, colNumber);

Runnable Example

Updated:

But now when I tried to sum with other value from other cell lets say column E1=50% , column D1=8 it will come to 58 instead of 8.5

Ohh... that's different than just displaying 50 with a "%". It sounds like you really want the cell value to be 0.50 (not 50), but display it as 50%. To do that, divide the value by 100 in your query.

 SELECT NumericColumnName / 100.0 AS NumericColumnName FROM tableName

Then format the spreadsheet cell as "0%"

 SpreadsheetFormatColumn(theSheet,{dataformat="0%"}, colNumber);

Runnable Example

Answer 2

Try:

SpreadsheetFormatCell(sheet, {dataformat="0"}, rowCounter, columnCounter)

There is a full list of the formatting options available for individual cells on the Adobe Help docs for SpreadSheetFormatCell.