Highlighted

SpreadsheetAddRow + SpreadsheetFormatCell with dataformat = "text"

New Here ,
Jun 15, 2020

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! 

Views

60

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

SpreadsheetAddRow + SpreadsheetFormatCell with dataformat = "text"

New Here ,
Jun 15, 2020

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! 

Views

61

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jun 15, 2020 0
LEGEND ,
Jun 15, 2020

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,

 

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 15, 2020 0