tracker issue : CF-3043393

select a category, or use search below
(searches all categories and all time range)
Title:

Bug 86400:When a spreadsheet is read using cfspreadsheet, it will error when the first characters of a field is a pound sign (#) and then field has formatting of at least "Custom" or "Date"

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): JJ BLODGETT / jj blodgett (jj blodgett)

Created: 02/17/2011

Components: Document Management, Office Integration

Versions: 10.0

Failure Type: Unspecified

Found In Build/Fixed In Build: CF9,0,1,274733 / 288992

Priority/Frequency: Trivial / Unknown

Locale/System: English / Win All

Vote Count: 2

Problem:

When a spreadsheet is read using cfspreadsheet, it will error when the first characters of a field is a pound sign (#) and then field has formatting of at least "Custom" or "Date".  May happen with others as well but those are the only two I tested. It will not error if the pound sign is in the middle or at the end.  Only when it's at the beginning.
Method:

- Create a spreadsheet.- Select at least one cell and choose "Format Cell".- Choose "Date" as the format (or Custom)- Enter "#ABC" or any string beginning with a pound sign as the content of the cell- Save and attempt to read with CFSPREADSHEET
Result:

An exception occurred while using action=read.  java.lang.RuntimeException: Unexpected celltype (5)

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3043393

External Customer Info:
External Company:  
External Customer Name: jj blodgett
External Customer Email: 02245D5E4471E38E992016B6
External Test Config: 02/17/2011

Attachments:

Comments:

It seems that when almost any field starts with '#' you get this error. Would be great to have fixed.
Vote by External U.
21174 | November 11, 2011 07:35:48 AM GMT
Yep... Just tried to upload an xlsx spreadsheet that had fields with #VALUE! in them and I got the same error as above. This really needs to be fixed ASAP!
Comment by External U.
21169 | July 24, 2012 03:43:14 AM GMT
It means that spreadsheets can't reliably be processed automatically and will require manual intervention and editing to remove the offending characters.
Vote by External U.
21175 | July 24, 2012 03:45:04 AM GMT
This bug can be avoided by adding # with this function, replace function REReplace("#data[c][currentRow]#","##","##","ALL")
Comment by External U.
21170 | March 27, 2013 08:57:21 PM GMT
Why is this closed/withdrawn/cannot reproduce? If I create a new XLSX with single column and enter "#1", "#2", and "#3" in the first 3 rows, a "Unexpected celltype" error if thrown... even though the column format is set to "general" or "text" A client of ours has a non-ColdFusion automated process that generates an XLSX file and NULL values in the database export a "#VALUE!" in the Excel cell. Please reopen this. I've verified that this bug still exists on both ColdFusion 9,0,1,274733 and ColdFusion 10,0,11,285380.
Comment by External U.
21171 | July 12, 2013 10:19:05 AM GMT
fixed in the upcoming major version of CF.
Comment by Piyush K.
21172 | March 08, 2014 08:36:11 AM GMT
It's nice to have it fixed in a new major version but considering the lack of backwards compatibility for charting in CF11, for those of us that are staying with CF10 for the time being we need it fixed as well
Comment by External U.
21173 | March 20, 2015 09:37:46 AM GMT