• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

SpreadsheetAddRow + SpreadsheetFormatCell with dataformat = "text"

New Here ,
Jun 15, 2020 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

189

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
community guidelines
LEGEND ,
Jun 15, 2020 Jun 15, 2020

Copy link to clipboard

Copied

LATEST

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,

 

^ _ ^

Votes

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
community guidelines
Resources
Documentation