1
Vote

package.Workbook.Styles.CreateNamedStyle fails validation

description

Creating a workbook and using the CreateNamedStyles method results in a file that does not pass validation. I am using the following code to create a Excel spreadsheet:
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            // add a new worksheet to the empty workbook
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory");
            worksheet.Cells["A1"].Value = "ID";
            package.Workbook.Styles.CreateNamedStyle(string.Concat("Syle_", "A1"));
            package.Save();
        }
The resulting output file contains a cellXfs record with the xfId of -2147483648 which is the default value of int.MinValue

<xf numFmtId="0" fontId="0" xfId="-2147483648" />

Using Microsoft's Open XML Format SDK 2.0 to verify the ouput file results in the error: "/x:styleSheet[1]/x:cellXfs[1]/x:xf[1] The attribute 'xfId' has invalid value '-2147483648'. The string '-2147483648' is not a valid 'UInt32' value." see below
            var spreadsheet = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(stream, false);

            DocumentFormat.OpenXml.Validation.OpenXmlValidator val = new DocumentFormat.OpenXml.Validation.OpenXmlValidator();

            var validationErrors = val.Validate(spreadsheet);
            if (validationErrors.Any())
            {
                foreach (var validationError in validationErrors)
                {
                    Assert.Fail(string.Format("{0} {1}", validationError.Path.XPath, validationError.Description));
                }
            }
Only the first call to CreateNamedStyle seems to have this effect subsequent named styles have correct positive integer xfId values.

comments

JanKallman wrote Dec 18, 2012 at 7:50 PM

I'll have a look at it

s_schwenker wrote Feb 1, 2013 at 3:58 PM

I had this same issue and thought I would help. I traced this down to cells that have a length greater than 32,767 which is the maximum length of a cell in the Excel format. I truncated the cells and everything worked fine again.

WenHaiXiong wrote Oct 15, 2013 at 3:03 PM

I fixed it, see TEXT .