CODEWITHSUNDEEP
coldfusioncfspreadsheet
anatp_123
anatp_123

Reputation: 1205

How to show full number in Excel sheet?

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">

Upvotes: 2

Views: 766

Answers (2)

Leigh
Leigh

Reputation: 28873

(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" ..

Upvotes: 1

Viv
Viv

Reputation: 326

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.

Upvotes: 2

Related Questions