Copy link to clipboard
Copied
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!
Copy link to clipboard
Copied
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,
^ _ ^