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

Fails to load Workbook (code bug)

Sep 28, 2011 at 6:32 PM

I tried opening a workbook using ClosedXML however it failed because of what looks like a coding bug (I am using the most recent Nuget package, but the bug seems to exist in the current source code as well).

I get a NullReferenceException from XLWorkbook.ApplyStyle:

The error appears at this line (which appears to be XLWorkbook_Load.cs line 1075:

uint borderId = cellFormat.BorderId.Value;

The excel file I am working with has a style.xml part where one of the <xf /> nodes in the document does not have a borderId. I believe this property needs to be accessed in a safer manner where we check for nulls. Unfortunately the document I'm using is confidential and can't be posted but I am sure this is the line that fails.

(Strangely enough it turns out that BorderId is null for me here, but changing the source to do the following checks still fail with a null reference exception:

var borderId = cellFormat.BorderId;
if(borderId != null) {
    if(borderId.HasValue) {
)

Coordinator
Sep 28, 2011 at 6:50 PM

.HasValue is a little funky in OpenXML land. The following change should work:

            if (cellFormat.BorderId != null)
            {
                uint borderId = cellFormat.BorderId.Value;
                var border = (Border) borders.ElementAt((Int32) borderId);
                if (border != null)
                {
                    ...
                }
            }

Could you please try to recreate the error by creating a worksheet with a bordered cell then stripping the border from the XML?

Thanks,

Sep 28, 2011 at 7:12 PM

I'll try and see if I can do this when I have some time in a bit, but wanted to let you know in addition to checking BorderId for null here when loading I also had to change the following method to consider nulls when saving:

        private static bool CellFormatsAreEqual(CellFormat f, StyleInfo styleInfo)
        {
            return
                f.BorderId != null && styleInfo.BorderId == f.BorderId
                && styleInfo.FillId == f.FillId
                && styleInfo.FontId == f.FontId
                && styleInfo.NumberFormatId == f.NumberFormatId
                && f.ApplyNumberFormat != null && f.ApplyNumberFormat == false
                && f.ApplyAlignment != null && f.ApplyAlignment == false
                && f.ApplyFill != null && f.ApplyFill == ApplyFill(styleInfo)
                && f.ApplyBorder != null && f.ApplyBorder == ApplyBorder(styleInfo)
                && AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment)
                && ProtectionsAreEqual(f.Protection, styleInfo.Style.Protection)
                ;
        }

That change coupled with this change allowed me to open and save:
UInt32Value borderId = cellFormat.BorderId;
            if (borderId != null) {
                if (borderId.HasValue) {
                    var border = (Border) borders.ElementAt((Int32) borderId.Value);
                    if (border != null) {

I'll see about producing a shareable the example as soon as I can.

Sep 28, 2011 at 7:39 PM
Edited Sep 28, 2011 at 7:45 PM

Ok I was able to reproduce the issue just by removing the borderId from one of the <xf /> elements in the style.xml part; unfortunately, I have no idea how to attach files on these boards. If you do the same, you should be able to reproduce. Note that in your test you will want to do a read and a write.

 

Edit:

Here is a link to the document up on google docs:

 

Coordinator
Sep 29, 2011 at 4:15 AM

Thanks for the feedback. I added the changes.