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