cfspreadsheet can I format columns to text when creating new doc.

[Joshua_Normal]

Here is my issue. I have a column of data I am attempting to write using cfspreadsheet. The data in the column is a client's identification code for a shipment.  The format they use is two numeric one alpha five numeric, example 01E23456. Lots of their shipments use E for the alpha character. When I write this to the new excel spreadsheet it ends up doing something to the data. When I open the Excel Spreadsheet I get an error, "File error: data may have been lost." Looking at the data in the column any value using E as the alpha character become "#NUM!" in the spreadsheet.

Here is the code I'm using.

<cfset SpreadsheetObj = SpreadsheetNew()>

<cfset spreadsheetAddRows(SpreadsheetObj,resultData)>

<cfspreadsheet action="write" filename="#theFile#" name="SpreadsheetObj" sheet=1 sheetname="Invoice" overwrite=true></cfspreadsheet>

I have tried the following to correct this issue without success.

Settings the column format using the following code.

<cfscript>

formatText=StructNew();

formatText.dataformat="Text";

</cfscript>

<cfset SpreadsheetFormatColumns(SpreadsheetObj, formatText, "19") />

This did not correct the issue.

Appending an apostrophe to the front of the cell data when grabbing it from MySQL, suggestion for avoiding this issue in Excel via MS Forums. This corrected the error I was getting when opening the spreadsheet but left the apostrophy in front of the field example '01E23456.

Checking the raw data in the .xls I am unable to find any of the data where an E was used for the alpha character but can find other data from the same field. When I tested adding the apostrophy I was able to find all of these records.

Has anyone else come across this issue and come up with a solution?

[Adam Cameron.]

I'm pretty sure the problem is on the CF side here, not the Excel side of things.  Both CF and Excel will treat "1E2" as a scientific notation representation of 100.  However CF is not sending "1E2" to Excel, it's sending "100".  Even if the source data is typed as being varchar.  This sux.

I've worked out a way around it.  If you change "1E2" to be "1#chr(9)#E2" (chr(9) being a tab character), then CF passes it as a string, and the tab does not render in Excel.  That said, the value will still be "1[TAB]E2", not "1E2", but it'll look OK.

This is a horrible hack, but the only thing I could think to trick CF into doing what it's told, and second guess - wrongly - what it should be doing with your data.

I'd raise a bug for this if I was you:

http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html

--

Adam

[Joshua_Normal]

Adam, thanks for the suggestion. If it comes down to it I will give that a try. I have opened a bug on this since no one else has responded yet with any other suggestions.

[Adam Cameron.]

I've voted for it.

For anyone else's reference, it's http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=86934.

--

Adam

[RodneyD]

Adding a tab was helpful, but didn't solve it for me because some of my users are taking the data from the spreadsheet and importing into another system.

Here is some code to recreate the problem.

<cfset myQuery = QueryNew("RoomCode, Desc")>

<cfset newRow = QueryAddRow(MyQuery, 3)>

<cfset temp = QuerySetCell(myQuery, "RoomCode", "2D3", 1)>

<cfset temp = QuerySetCell(myQuery, "Desc", "should be 2D3", 1)>

<cfset temp = QuerySetCell(myQuery, "RoomCode", "2E3", 2)>

<cfset temp = QuerySetCell(myQuery, "Desc", "should be 2E3", 2)>

<cfset temp = QuerySetCell(myQuery, "RoomCode", replace("2E3", 'E', '#chr(9)#E' ), 3)>

<cfset temp = QuerySetCell(myQuery, "Desc", "has a tab between the 2 and the E (to see it, paste it into notepad)", 3)>

<cfset theDir=GetTempDirectory()>

<cfset filename = createUUID() & ".xls">

<cfset FullFileName = theDir & filename>

<cfoutput>FullFileName:#FullFileName#</cfoutput>

<cfspreadsheet action="write" query="myQuery" fileName=#FullFileName# overwrite="true"  />

<cfheader name="Content-Disposition" value="inline; filename=#filename#">

<cfcontent type="application/csv" file=#FullFileName# deletefile="yes">

The problem is in row two 2E3 is showing up as 2000 in excel.

Anybody else out there have any other workarounds?

fyi - I voted on the bug at http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#b ugId=86934 but I have a hunch a fix from adobe isn't coming soon and I need to fix this for my customer soon.

Thanks for your time

[RodneyD]

I was hoping that adding the tab would work for my user, but it ends up looking ok in excel2010 but not in excel2007. It shows the tab in excel2007.

[RobertAtGaN]

I've had a similar problem and logged a bug:

https://tracker.adobe.com/#/view/CF-4215050

[RobertAtGaN]

Actual I looked at bug I logged and they posted a solution that fixed the issue. Use format 'string' to force it to display as a string:

<cfset SpreadsheetSetCellValue(xl,"2D",#xl.rowcount#,3,"string")>