How to show full number in Excel sheet?

Question

I'm exporting a query to an Excel file using cfspeadsheet. It's working and creating the Excel sheet. However, the problem is that one of the columns, ie card_number, contains a 15 digit number, which is displayed like this: 4.5421E+15. Is there a way I can display the full number instead: 4254218068670980 ?

<!--- create manual query for demo --->
<cfset qData = queryNew("")>
<cfset queryAddColumn(qData, "NumericCol", "BigInt",["4254218068670980"])>
<cfset queryAddColumn(qData, "StringCol", "Varchar",["4254218068670980"])>
<cfset queryAddColumn(qData, "DecimalCol", "Decimal",["4254218068670980"])>

<!--- export to file --->
<cfspreadsheet action="write" 
        filename="c:/path/to/myFile.xls"
        query="qData" 
        overwrite="true">

nope ramdom number , i can change it too in cause of any confusion — anatp_123, Sep 01 '16 at 00:31

score 2 · Accepted Answer · answered Aug 23 '16 at 02:01

2

You need to define and use a format for the cell to show complete number. Below is a sample code snippet for your code:

<cfscript> 
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "new_data.xls"; 
//Create a new Excel spreadsheet object. 
theSheet = SpreadsheetNew("Expenses"); 
//Set the value a cell. 
SpreadsheetSetCellValue(theSheet,"4254218068670980",1,4);
//Set value into another cell. 
SpreadsheetSetCellValue(theSheet,"4254218068670980",2,4);
// Define a format class for for number. 
longNum=StructNew(); 
longNum.dataformat = "0"; 
//Now use this class to format cell 
SpreadsheetFormatCell(theSheet,longNum,2,4); 
</cfscript>

There are many supported formats available; for a complete list you may check here. Also, just like SpreadsheetFormatCell you may want to use SpreadsheetFormatColumn or other related functions.

answered Aug 23 '16 at 02:01

Viv

326
1
6

You'll need to format the cell before filling it with data. I also noticed that the amount of cells and rows you can format in this way is limited to 4000, but I haven't found a way around that yet. Which is why we use Ben Nadel's POI custom tags, which you can find [here](https://github.com/bennadel/POIUtility.cfc). – Sander Aug 23 '16 at 08:40
1

@Sander - Most likely it is caused by formatting cells individually. [Excel limits the number of "styles" you can apply](http://stackoverflow.com/questions/37817997/spreadsheetformatrow-abruptly-stops-working/37820034#37820034). The limits are lower for .xls than .xlsx. Usually the solution is to apply formats once - to a range or an entire column - instead of individually. – Leigh Aug 23 '16 at 12:46

score 1 · Answer 2 · edited May 23 '17 at 12:32

(Too long for comments...)

FWIW, CFSpreadsheet is designed for very simple exports, without a lot of bells and whistles. If you need special formatting, you must use spreadsheet functions instead.

The closest equivalent to your current code is probably the SpreadsheetAddRows(sheet, query) function. It populates a worksheet with the data in the supplied query object. As Viv's answer mentions, you can then format the columns as desired. For example, if you want the value to be treated as text, use {dataformat = "@"}:

<cfscript>
   SpreadsheetAddRows(theSheet, qData); 
   SpreadsheetFormatColumns(theSheet, {dataformat = "@"}, "1-3"); 
   SpreadSheetWrite(theSheet, "c:/path/to/myFile.xls", true);
</cfscript>

As an aside, the examples in the documentation are not always the best or cleanest. Consider them a starting point, rather than using the code exactly "as is" ..

How to show full number in Excel sheet?

2 Answers2