0

I can't figure out what's going on. I'm exporting data from our Oracle 12c db to Excel from a simple select query, using cfspreadsheet:

    <cfset var_filenameis = "report.xlsx">
    <cfset SpreadsheetObj = spreadsheetNew("true")>
    <cfset SpreadsheetObj = spreadsheetNew("#var_filenameis#","yes")>    
    <cfspreadsheet action="write" filename="#var_filenameis#" query="get_data" overwrite="true">    
    <cflocation url = "#var_filenameis#">

One column of data contains catalog numbers in various formats. Some of them become truncated when exported to xlsx. For example, 02923F becomes 2923 and 08552D becomes 8552. However, 08566A stays 08566A and 02584C also stays the same. The data shows correctly when viewed in the browser. Direct export from the DB also shows correct data. I could understand the leading 0 disappearing, but not the letter.

I have re-entered the problem catalog numbers in the DB to make sure there were no extra characters, to no avail. If I add a single quote in front of the catalog number, the display is correct except that I can't have a single quote showing in the output.

I get the same result with CF9 and CF11. I can't even tell if the problem is with cfspreadsheet or xlxs. Any good ideas? Thanks!

Leigh
  • 28,765
  • 10
  • 55
  • 103
cmadsen
  • 1
  • 1
  • Unfortunately this question was probably overlooked due to the tags. For future reference, CF related questions get more attention if you include the "coldfusion" tag (or at least a version specific variant, ie "coldfusion-2016"). – Leigh Oct 12 '16 at 01:43

1 Answers1

0

(Too long for comments)

If you are using CF11, try using SpreadSheetAddRows instead of cfspreadsheet. It is a bit smarter and uses the data types of the query columns to determine the proper cell type. As long as the query column is some sort of VARCHAR, the string value will be preserved. 

<!--- sample data --->
<cfset qData = queryNew("")>
<cfset queryAddColumn(qData, "StringValue", "varchar", ["02923F","08552D","08566A","02584C"])>

<!--- populate sheet --->
<cfset sheet = SpreadsheetNew("Sheet1", true)>
<cfset spreadSheetAddRows(sheet, qData)>

<!--- display results --->
<cfheader name="Content-Disposition" value="attachment;filename=testFile.xlsx">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#SpreadSheetReadBinary(sheet)#">

I can't figure out what's going on

It is because POI (or possibly CF) interprets the "F" and "D" as special literals indicating the values are floating point numbers:

Floating-Point Literals

A floating-point literal is of type float if it ends with the letter F or f; otherwise its type is double and it can optionally end with the letter D or d.

So your strings are being converted to numbers. That is why the leading zero and trailing "F" or "D" disappear.

Leigh
  • 28,765
  • 10
  • 55
  • 103