Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Error copying range with ConditionalFormat (ColorScale) to new cell. Throws NullReferenceException #1526

Open
2 of 3 tasks
jafin opened this issue Sep 21, 2020 · 1 comment · May be fixed by #1527
Open
2 of 3 tasks

Error copying range with ConditionalFormat (ColorScale) to new cell. Throws NullReferenceException #1526

jafin opened this issue Sep 21, 2020 · 1 comment · May be fixed by #1527

Comments

@jafin
Copy link

jafin commented Sep 21, 2020

Do you want to request a feature or report a bug?

  • Bug

Did you test against the latest CI build?

  • Yes

Version of ClosedXML

0.95.3 - abe7cc0

What is the current behaviour?

Error copying range with ConditionalFormat (ColorScale) to new cell.
There is an error in XLConditionalFormat when the ConditionalFormat values are read and the value is null (which appears expected for certain conditionalFormat rules)

What is the expected behavior or new feature?

Copying the cell range should complete without error.

Is this a regression from the previous version?

No

Reproducibility

Execute the following test, it will throw an exception

Code to reproduce problem:

     [Test]
        public void CopyConditionalFormatColorScaleInRange()
        {
            var ws = new XLWorkbook().Worksheets.Add("Sheet");

            ws.Row(1).Cell(1).AddConditionalFormat()
                .ColorScale()
                .LowestValue(XLColor.Teal)
                .HighestValue(XLColor.Orange);

            ws.Cell(5, 2).Value = ws.Range(1, 1, 1, 5);

            Assert.AreEqual(2, ws.ConditionalFormats.Count()); //existing format + copied.
            Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B5:B5").ConditionalFormatType == XLConditionalFormatType.ColorScale);
        }

Will throw an exception

System.NullReferenceException : Object reference not set to an instance of an object.
   at ClosedXML.Excel.XLFormula..ctor(XLFormula defaultFormula) in D:\ClosedXML\ClosedXML\Excel\Misc\XLFormula.cs:line 15
   at ClosedXML.Excel.XLConditionalFormat.<CopyFrom>b__97_0(KeyValuePair`2 kp) in D:\ClosedXML\ClosedXML\Excel\ConditionalFormats\XLConditionalFormat.cs:line 263
   at ClosedXML.Excel.EnumerableExtensions.ForEach[T](IEnumerable`1 source, Action`1 action) in D:\ClosedXML\ClosedXML\Extensions\EnumerableExtensions.cs:line 12
   at ClosedXML.Excel.XLConditionalFormat.CopyFrom(IXLConditionalFormat other) in D:\ClosedXML\ClosedXML\Excel\ConditionalFormats\XLConditionalFormat.cs:line 263
   at ClosedXML.Excel.XLCell.CopyConditionalFormatsFrom(XLRangeBase fromRange) in D:\ClosedXML\ClosedXML\Excel\Cells\XLCell.cs:line 2258
   at ClosedXML.Excel.XLCell.SetRange(Object rangeObject) in D:\ClosedXML\ClosedXML\Excel\Cells\XLCell.cs:line 2197
   at ClosedXML.Excel.XLCell.set_Value(Object value) in D:\ClosedXML\ClosedXML\Excel\Cells\XLCell.cs:line 685
   at ClosedXML_Tests.CopyingRangesTests.CopyConditionalFormatColorScaleInRange() in D:\ClosedXML\ClosedXML_Tests\Excel\Ranges\CopyingRangesTests.cs:line 149

Workaround

In the XLConditionalFormat.cs Line 262, guarding for a null Value resolves the crash.
ie

-            other.Values.ForEach(kp => Values.Add(kp.Key, new XLFormula(kp.Value)));
+            other.Values.Where(x=>x.Value != null).ForEach(kp => Values.Add(kp.Key, new XLFormula(kp.Value)));
  • I attached a sample spreadsheet. (You can drag files on to this issue)
@igitur igitur linked a pull request Sep 21, 2020 that will close this issue
Open
@GeorgeAdamon
Copy link

GeorgeAdamon commented Apr 18, 2024 •
edited

The issue still persists on versions 102.2, 103.0 beta and 104.0 preview.

Simply trying to add an existing Worksheet (with 2-Color gradient scale conditional formatting) to a new Workbook object,
FinalWorkbook.AddWorksheet(s);

generates an error almost identical to the one above.

System.NullReferenceException: Object reference not set to an instance of an object.
   at ClosedXML.Excel.XLFormula..ctor(XLFormula defaultFormula)
   at ClosedXML.Excel.XLConditionalFormat.<CopyFrom>b__97_0(KeyValuePair`2 kp)
   at ClosedXML.Excel.EnumerableExtensions.ForEach[T](IEnumerable`1 source, Action`1 action)
   at ClosedXML.Excel.XLConditionalFormat.CopyFrom(IXLConditionalFormat other)
   at ClosedXML.Excel.XLCell.CopyConditionalFormatsFrom(XLRangeBase fromRange)
   at ClosedXML.Excel.XLCell.CopyFrom(IXLRangeBase rangeObject)
   at ClosedXML.Excel.XLRangeBase.CopyTo(XLCell target)
   at ClosedXML.Excel.XLRangeBase.CopyTo(IXLRangeBase target)
   at ClosedXML.Excel.XLColumn.CopyTo(IXLColumn column)
   at ClosedXML.Excel.XLWorksheet.<>c__DisplayClass156_0.<CopyTo>b__0(KeyValuePair`2 kp)
   at ClosedXML.Excel.EnumerableExtensions.ForEach[T](IEnumerable`1 source, Action`1 action)
   at ClosedXML.Excel.XLWorksheet.CopyTo(XLWorkbook workbook, String newSheetName, Int32 position)
   at ClosedXML.Excel.XLWorksheet.CopyTo(XLWorkbook workbook, String newSheetName)
   at ClosedXML.Excel.XLWorkbook.AddWorksheet(IXLWorksheet worksheet)
   at MyProject.MainWindow.<>c__DisplayClass20_0.<GenerateDistributables_Click>b__0()

The Conditional Formatting rules don't do anything fancy, all of their ranges are totally valid values. The only thing that seems to make the problem go away is to remove the conditional formatting altogether.

The error seems to happen in this line:

ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs

Line 268 in 41dafa6

other.Values.ForEach(kp => Values.Add(kp.Key, new XLFormula(kp.Value)));

The workaround above by @jafin doesn't look like it has made it to the (current) official releases.

If you think this deserves a new issue, happy to move it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
2 participants
@jafin @GeorgeAdamon