SpreadsheetAddRow + SpreadsheetFormatCell with dataformat = "text"

[CrownInc]

Hi,

I am trying to export barcodes to an excel sheet using SpreadsheetAddRow. 

Being that barcodes are strings of numbers, the SpreadsheetAddRow seems to automatically interpret the string as a number.. e.g.,

Barcode = 0194112066037

In excel it gets returned as 1.94112E+11 (as datatype General)

Using SpreadsheetFormatCell(objSheet,{dataformat=0},RowNumber,CellNumber

In excel it gets returned as 194112066037 (without the '0' preceding the string) (as datatype Number).

Using SpreadsheetFormatCell(objSheet,{dataformat="text"},RowNumber,CellNumber

In excel it again gets returned as 1.94112E+11 (as datatype Text).

It seems that Excel shouldn't alter any value in a datatype text field. 

It's very strange. Especially considering we have not run into this issue when using SpreadsheetAddRows.

ColdFusion Server Developer 2016,0,11,314546

<cfset strXLS = "C:\Example.xls">

<cfset objSheet = SpreadsheetNew(" Export")>

<cfset SpreadsheetAddRow(objSheet,"Barcode")>

<cfset strBarcode1 = 0194112066037>

<cfset strBarcode2 = 0194112066037>

<cfset SpreadsheetAddRow(objSheet,'#strBarcode1#')>

<cfset SpreadsheetAddRow(objSheet,'#strBarcode2#')>

<cfset SpreadsheetFormatCell(objSheet,{dataformat='text'},2,1)>

<cfset SpreadsheetFormatCell(objSheet,{dataformat=0},3,1)>

<cfspreadsheet action="write" filename="#strXLS#" name="objSheet" sheetname="Example" overwrite=true >

Thank you! 

[WolfShade]

This is going to sound strange, but I've experienced something similar a while ago.  I learned by myself that if I formatted the cell both before and after setting cell value, it cleared up my issue.  Give that a shot.  Meanwhile, sometime tomorrow I can take a closer look.

V/r,

^ _ ^