This project has moved. For the latest updates, please go here.

Exception while creating Workbook from File

Feb 23, 2012 at 6:25 AM

Hallo,

I use this code to open an existing file:

        public bool Open(string filename)
        {
            byte[] byteArray = File.ReadAllBytes(filename);
            _stream = new MemoryStream();
            _stream.Write(byteArray, 0, byteArray.Length);
 
            _workbook = new XLWorkbook(_stream);

and get an Exception 'yellow [13] ist kein gültiger Wert für Int32.'. No Problem with other files.
The file opens well in Excel.

I use the latest Version 0.64.

You may find the file here
http://dl.dropbox.com/u/11418288/Fakturadruckbericht.xlsx

Thank you
Rainer

Feb 26, 2012 at 11:46 AM
Edited Feb 27, 2012 at 4:23 PM

Hi Rainer

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 [13]" 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 [13]" 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.

Kind Regards

Alistair

PS.

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 [51]" o:insetmode="auto">

Feb 26, 2012 at 1:06 PM
Edited Feb 26, 2012 at 5:05 PM

Hi

After investigating the problem reported by Rainer above, I would like to propose the following modification in the XLWorkbook.LoadColorsAndLines<T> function to correctly parse the fill color when it is saved by name :

 

 // Modified by alijmoor
            var fillColor = shape.Attribute("fillcolor");
            if (!string.IsNullOrEmpty(fillColor.Value) && !fillColor.Value.ToLower().Contains("infobackground"))
            {
                // Modified to detect HTML color tags or names colors (after removing the [nn])
                var colName = new string(fillColor.Value.TakeWhile(c => c != '[').ToArray()).Trim();
                var firstChar = colName.First();
                if (firstChar == '#')
                    drawing.Style.ColorsAndLines.FillColor = XLColor.FromHtml(colName);     // Parse HTML color code
                else if (char.IsLetter(firstChar))
                    drawing.Style.ColorsAndLines.FillColor = XLColor.FromName(colName);     // Parse color name
            }

 

If the color in the file is a valid HTML color code it will parse it as before, if it is text beginning with a letter (eg "yellow [13]" as in Rainer's example, then it will take the color name up to the first '[', remove trailing spaces and try and parse it as a color name (in this case "yellow"), and the workbook is loaded correctly.

This could be als refactored into a separate parsing function in case this logic is required elsewhere in ClosedXML, again I think Manuel would be the best judge of that requirement.

I have no experience submitting patches in CodePlex, so I leave it to Manuel as the judge of whether or not this code is valid or not, I only offer it as a proposed change to solve Rainer's problem.

Kind Regards

Alistair

Feb 27, 2012 at 7:05 AM

Hi Alistair,

thank you very mutch for your help. I remove the commands to solve the error.

Greetings

Rainer