InvalidOperationException - Range already starting from range B278:E278

[vivet]

When upgrading to EPPlus 6.2.0 i see this exception. I don't have the sample file, so I hope the exception trace is enough.

InvalidOperationException - Range already starting from range B278:E278
at OfficeOpenXml.Core.CellStore.RangeDictionary1.Add(Int32 fromRow, Int32 fromCol, Int32 toRow, Int32 toCol, T value) at OfficeOpenXml.DataValidation.ExcelDataValidationCollection.ReadDataValidations(XmlReader xr) at OfficeOpenXml.DataValidation.ExcelDataValidationCollection..ctor(XmlReader xr, ExcelWorksheet worksheet) at OfficeOpenXml.ExcelWorksheet.LoadDataValidations(XmlReader xr) at OfficeOpenXml.ExcelWorksheet.CreateXml() at OfficeOpenXml.ExcelWorksheet..ctor(XmlNamespaceManager ns, ExcelPackage excelPackage, String relID, Uri uriWorksheet, String sheetName, Int32 sheetID, Int32 positionID, Nullable1 hide)
at OfficeOpenXml.ExcelWorksheets..ctor(ExcelPackage pck, XmlNamespaceManager nsm, XmlNode topNode)
at OfficeOpenXml.ExcelWorkbook.get_Worksheets()
at OfficeOpenXml.ExcelWorkbook.GetDefinedNames()
at OfficeOpenXml.ExcelPackage.get_Workbook()

[OssianEPPlus]

That particular error implies that there is two overlapping dataValidations or ranges which aren't allowed in epplus or excel. Unfortunately the error is hard to reproduce for us without the .xlsx file. It's also hard to tell if its a bug or what kind of bug without it. It could be that recent updates are simply better at detecting there's something wrong with the xml.

If you can't access the particular sample file but are able to reproduce the issue in a separate file that would help.

[vivet]

Hi

I am investigating the excel file causing the issue.
I will get back...

[vivet]

Test.xlsx

I have attached a simple example.
To me it seems Excel is accepting it, so it seems it is supported.

[OssianEPPlus]

Hi and thank you! Ah now I see. So what's happening here is you've got two datavalidations referring to the same cell in the XML both of which are identical. So one is completely unnecesary. While technically it works as Excel does not crash Excel does not have full support for this.

A setup like this is unstable as Excel will try to apply both validations and occasionally apply one of them and occasionally the other, seemingly at random. I tried switching two list validations to lessThan and greaterThan 5 respectively and it applies both and returns the error message of the first validation even when its the second one being broken.

Because of this Epplus does not allow multiple dataValidations in the same cell and upon discovering it when reading the file throws an error as it is a file with a problem in it. In an update we are planning to instead delete the validation(s) referring to the same cell after the first as in most cases Excel defaults to the first validation in the xml.

How was this file generated? It shouldn't be possible to do this natively within excel. Anyway I removed the extra validation so here's that file back. It should be the same but readable by Epplus:
TestFix.xlsx

If you want to apply multiple data validations to one cell it is recommended to use a Custom Formula like in this tutorial for example

[vivet]

Okay, I see
Though I would argue that if Excel can open the file without any issues, EPPlus should as well.
The change is breaking all our customer's files, which is hard for us to change.

" In an update we are planning to instead delete the validation(s) referring to the same cell after the first as in most cases Excel defaults to the first validation in the xml"

When do you plan to release this update, cause it might be what we need?
At least think about that all users who have this problem in their Excel file, won't notice in Excel, but 6.2.0 will break the application.

[OssianEPPlus]

I definitely agree. Epplus should be able to open the file without any issues if Excel can which is why we'll be releasing that update at the beginning of next week and I apologise for the inconvinence.

Out of curiosity do you have any idea what's being used to generate the customer files having this issue? The state of the xml implies they weren't made in Excel itself. Knowing would help us take measures to avoid future issues.

[vivet]

hmmm.
It's coming from a different department, and the original might be 5-6 years old.

So sorry.

[OssianEPPlus]

Ah. Makes sense no worries.

[vivet]

Looking forward for the upcoming release

[vivet]

Seems fixed in 6.2.1