search

nissl-lab / npoi

a .NET library that can read/write Office formats without Microsoft Office installed. No COM+, no interop.
Apache License 2.0
5.67k stars 1.42k forks source link

Reading Data Validation for sheet returns empty set if validation is a formula to another sheet #498

Open mdufrasne opened 3 years ago

mdufrasne commented 3 years ago

Reading Data Validation for sheet returns empty set if validation is a formula to another sheet.

Mainly, I have image

My code is as follows:

IWorkbook workbook = WorkbookFactory.Create(@"test.xlsx");
ISheet sheet = workbook.GetSheetAt(0);
var dvs = sheet.GetDataValidations();

At runtime, dvs is empty (count is 0). If I do the exact same thing within the same sheet, all works fine.

tonyqus commented 3 years ago

Please upload the Excel in order to help reproduce the issue

Algerator commented 3 years ago

Hi! Having the same issue here, data validation is not detected when it's a list referencing cells in another sheet. I attach a simple xlsx file showcasing the issue. external_data_validation_sample.xlsx

Thanks!

toraritte commented 9 months ago

Had the same issue just now, and it can be reproduced with @Algerator's sample workbook.

edit: For the record, this has been an open issue in POI since 2019 too.

edit-2: Tried to work around this using Microsoft's DocumentFormat.OpenXml package, but it has the same issue (see https://github.com/dotnet/Open-XML-SDK/issues/1613).

tonyqus commented 9 months ago

1231