ColdFusion (9) mysteriously removes characters 'D' and 'F' after numbers when exporting to Microsoft Excel (2007)

Question

Here's some code snippets for an example:

theSheet = SpreadsheetNew("Rates","True");
SpreadsheetAddRow(theSheet,"4A,4B,4C,4D,4E,4F,4G,4H,4I,4J");
SpreadsheetAddRow(theSheet,"4K,4L,4M,4N,4O,4P,4Q,4R,4S,4T");
SpreadsheetAddRow(theSheet,"4U,4V,4W,4X,4Y,4Z,4D4,4F4");

and

<cfheader name="content-disposition" value="attachment; filename=#GetTickCount()#.xlsx">
<CFHEADER NAME="Expires" VALUE="#now()#">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#SpreadsheetReadBinary(theSheet)#"/>

The issue is that "4D" and "4F" (and not the others) lose the 'D' and 'F' and are formatted as a number.

I tried this:

formatText = StructNew();
formatText.dataformat="@";
SpreadsheetFormatColumns(theSheet,formatText,"1-10");

I verified that this set the format in Excel to "Text", but now I just see the number 4 in a Text-formatted cell! I also tried using the ' character, but when it opens in Excel, it just shows the ' instead of literalizing the cell.

This is rather strange; anybody have an idea about what's going?

Strange. Using '4D should produce exactly that. SpreadsheetFormatColumns is relatively useless because it is applied after the fact. So the value is already corrupted at that point. — Leigh, Mar 18 '11 at 03:03
I could have *sworn* I'd seen a bug report similar to this, but I'll be damned if I can find it. Are you using 9.0.0 or 9.0.1? — ale, Mar 18 '11 at 14:38
See this question for a different approach to the same issue: http://stackoverflow.com/questions/3081202 — CfSimplicity, Jun 20 '11 at 11:06
I think I like that better; it's a more straightforward implementation. — jtpereyda, Jun 20 '11 at 14:21

score 5 · Accepted Answer · answered Mar 18 '11 at 01:40

5

It seems that a workaround is to set the cell formula to be the literal "4D".

theSheet = SpreadsheetNew("Rates","True");
SpreadsheetAddRow(theSheet,"4A,4B,4C,,4E,,4G,4H,4I,4J");
SpreadsheetSetCellFormula(theSheet, """4D""", 1, 4);
SpreadsheetSetCellFormula(theSheet, """4F""", 1, 6);
SpreadsheetAddRow(theSheet,"4K,4L,4M,4N,4O,4P,4Q,4R,4S,4T");
SpreadsheetAddRow(theSheet,"4U,4V,4W,4X,4Y,4Z,4D4,4F4");

I still don't know why this is happening, but my idea is that SpreadsheetAddRow() and SpreadsheetSetCell() are interpreting 4D and 4F as numeric and are interpreting the D and F and suffixes standing for Double and Float, and stripping them out after conversion.

You can submit the bug to Adobe by going to https://bugbase.adobe.com/index.cfm.

answered Mar 18 '11 at 01:40

boothinator

121
3

Quoting should work with SpreadSheetAddRow too. I suspect you are right about CF interpreting the values as numeric. If you open the file and view the xml, those two values are marked as numeric, not strings like the others – Leigh Mar 18 '11 at 03:08
For me, quoting just resulted in "4D" being in the cell, including the quotes. Also, prefixing the cell value with = does not seem to make it a formula. – boothinator Mar 18 '11 at 12:27
Weird, that is the result I got when using SpreadSheetSetCellFormula. Maybe we are using different versions? I will have to check mine. – Leigh Mar 18 '11 at 15:48
Your example is right. Just tried it again and it worked fine. – Leigh Mar 19 '11 at 04:18
1

Excellent! This is a solid workaround. I was worried this would mess with uploads (as the data can be downloaded, modified, and uploaded), but whatever we're doing now still works. I'm guessing that ColdFusion, as a rule, gets the actual results of a cell when reading (hence the need for SpreadsheetGetCellFormula). – jtpereyda Mar 21 '11 at 23:56

score 0 · Answer 2 · edited May 23 '17 at 11:56

I updated code from a related stack question to search for characters (to use by prepending or appending to the given text) to hide this ColdFusion feature:

WorkBook = spreadsheetNew('Test', true);
RowNumber = 1;  
for (i = 1; i <= 255; i++){
    SpreadSheetSetCellValue(WorkBook, i, RowNumber, 1);

    // what character are we displaying
    SpreadSheetSetCellValue(WorkBook, chr(i), RowNumber, 2);

    // see if appending chr(i) allows 4F to display
    SpreadSheetSetCellValue(WorkBook, "4F#chr(i)#", RowNumber, 3);

    // see if appending chr(i) allows 4F to display
    SpreadSheetSetCellValue(WorkBook, "#chr(i)#4F", RowNumber, 4);
    RowNumber ++;
}

Turns out prepending or appending nonprintable characters chr(127) and chr(160) maintain the presentation of 4F or 4D

Related stack question I mentioned: cfspreadsheet alphanumeric values ending in d

score 0 · Answer 3 · answered Mar 18 '11 at 16:32

0

You should try to use D char code explicitly chr(68) instead of "D".

answered Mar 18 '11 at 16:32

Fkuril

1

score 0 · Answer 4 · answered Mar 18 '11 at 19:00

0

You might try the old spreadsheet trick -- going back to Lotus days -- of coercing values to text by starting the entry with a single quote: '4D.

answered Mar 18 '11 at 19:00

Ken Redler

23,863
8
57
69

ColdFusion (9) mysteriously removes characters 'D' and 'F' after numbers when exporting to Microsoft Excel (2007)

4 Answers4

Linked

Related