Feb 26, 2012 at 10:46 AM
Edited Feb 27, 2012 at 3:23 PM
I have investigated a little into your problem and the problem comes from the Comments in cells Q10, U10, and K41 in your excel sheet.
The error is raised in the function XLWorkbook.LoadColorsAndLines<T> when loading the excel autoshape stream vmlDrawing1.vml from within the Excel file. These comments have been given a solid fill color of yellow, and for some reason in Excel 2010
instead of putting an html color reference (like the default #ffffe1) which is what ClosedXML is expecting, it sets the fillcolor attribute to "yellow " as you can see in the excerpt from the vmlDrawing1.vml stream below :
<v:shape id="_x0000_s1026" type="#_x0000_t202" style='position:absolute; margin-left:916.5pt;margin-top:147.75pt;width:108pt;height:59.25pt;z-index:2; visibility:visible' fillcolor="yellow " o:insetmode="auto">
The comment loading code is trying to interpret the fillcolor attribute as a HTML hex color reference, and what it is getting (as you can see) is the color name.
A quick workaround would be to remove the comments and re-insert them without changing the fill color of the comment text box (leaving it as the default value which on my Excel 2010 is a kind of pale beige). I have tried this with your file, and it then
loads correctly in ClosedXML
I need to investigate more to determine if this is correct functionality in Excel 2010 or if this is a fault within Excel. Maybe Manuel can shed some more light onto this, but at least you have a workaround that will get your sheet working.
Hope this helps you.
After more testing it seems that when a named color is selected as the fill for a comment, Excel is storing the color name in the fillcolor attribute. (It happens with blue, red, yellow etc, but not with others like 'Gold'). A simple patch would be to check
if the attribute starts with a '#' indicating it is a HTML hex color reference, and if not try to parse the color names. (From what I can determine, even if Excel is localized into a different language, the color names saved in the file seem to be in English)
In some cases Excel seems to be saving a combination of the HTML color code and a number within , as is the case below with a Gold fill for a comment
<v:shape id="_x0000_s1026" type="#_x0000_t202" style='position:absolute; margin-left:916.5pt;margin-top:147.75pt;width:108pt;height:59.25pt;z-index:2; visibility:visible' fillcolor="#fc0 " o:insetmode="auto">