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.

SOS · Accepted Answer · 2019-01-11T04:05:45.977

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

Yeah it will show the value with the percent sign.Thanks for that. 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. — SySyBy, Jan 10 '19 at 16:21
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. Then format the spreadsheet cell as "0%". — SOS, Jan 10 '19 at 17:08

score 0 · Answer 2 · answered Jan 08 '19 at 14:11

0

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.

answered Jan 08 '19 at 14:11

Brian

565
3
5

I already use that. Is it because of the data inside the cell has percent sign?Because the query include percent sign. – SySyBy Jan 09 '19 at 01:12
If the query includes a percent sign, it is a character-type. Does the database hold the character values with the percent sign, or does it store it in decimal format and then your query converts it to add a %? – Shawn Jan 09 '19 at 04:47
@SySyBy - The exact behavior of spreadsheet functions varies a LOT by version and what values/datatypes are used. Could you post a sample of the query column value AND the data type? – SOS Jan 09 '19 at 13:25
@Ageax - 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. But I need the cell format value is number. – SySyBy Jan 10 '19 at 07:24
@Ageax - is coldfusion has function to convert stored text to number in cfspreadsheet? because I try to use a formula =VALUE(F7) to stored it as a number but when we copy the value of the cell and paste it to other sheet or column it will give the value zero because the cell contain of formula not the value. – SySyBy Jan 10 '19 at 08:12
Yeah, like @Shawn said, concatenating it with "%" makes it a string, not a number. You should remove "%" from the SELECT and just return the number instead. You can format it as a percent with spreadsheet functions if needed. – SOS Jan 10 '19 at 15:21
@Ageax - I already tried. But when I format it as percent it give me 5000%. Because when use percent format it will multiply the value with 100 and put symbol %. – SySyBy Jan 10 '19 at 15:44
Yes, you just need to change the dataformat slightly. See my answer below. – SOS Jan 10 '19 at 15:57
@SySyBy What is the datatype of the number coming from the query? Can you update the question to include the query you're using to get the data? – Shawn Jan 10 '19 at 16:26
@SySyBy Sorry, missed that. Is this query used elsewhere, or only to populate this Excel sheet? If it won't break something else, change your query to something like `COALESCE(psi,0)/100` to get a decimal value that Excel can translate to a percent. Note: `coalesce()` is used to handle `null` values. – Shawn Jan 10 '19 at 16:36
I would also give my regular PSA that CF10 ended core support almost 2 years ago and will end extended support in May of this year (2019), so you are very likely running a version of CF with some security issues. Plus CF10 may be affected by Oracle's 1/1/2019 changes to Java licensing. I would highly recommend some form of update. – Shawn Jan 10 '19 at 16:42
@shawn the query is used for populate the excel only. I already divide the value by 100 in my query and format the cell into "0%". Its work. Thanks for the idea – SySyBy Jan 10 '19 at 17:31
Didn't see Shawn's comments. @SySyBy - They're not mutually exclusive (-; While the code may be working under CF10, that version is still EOL'd for core support - which means **it no longer gets security updates** - without a platinum support contract, and even that ends soon. So upgrading to a newer version is strongly recommended - for security reasons at least. – SOS Jan 10 '19 at 17:57

How to fix Number stored as text in cfspreadsheet

2 Answers2