Data validations with formula to another sheet won't get returned

[toraritte]

Describe the bug
if data validation is a formula to another sheet, then it won't get returned.

Screenshots

(Image taken from NPOI issue #498.)

To Reproduce
Using a minimal XLSX file external_data_validation_sample.xlsx (again, taken from NPOI issue #498) and the following F# code in dotnet fsi:

#r "nuget: DocumentFormat.OpenXml, 3.0.0";;

open DocumentFormat.OpenXml
open DocumentFormat.OpenXml.Packaging
open DocumentFormat.OpenXml.Spreadsheet

let GetDataValidationsByIndex (filePath: string) (sheetIndex: int) =
    use spreadsheet = SpreadsheetDocument.Open(filePath, false)
    let workbookPart = spreadsheet.WorkbookPart
    let sheets = workbookPart.Workbook.Descendants<Sheet>() |> Seq.toArray

    if sheetIndex >= 0 && sheetIndex < sheets.Length then
        let sheet = sheets.[sheetIndex]
        let worksheetPart = workbookPart.GetPartById(sheet.Id) :?> WorksheetPart
        let dataValidations = worksheetPart.Worksheet.Descendants<DataValidations>()

        let validations =
            dataValidations
            |> Seq.collect (fun dv ->
                dv.Descendants<DataValidation>()
                |> Seq.map (fun validation ->
                    // Extract data validation details as needed
                    validation
                ))
            |> Seq.toList

        Some validations
    else
        printfn "Sheet index '%d' out of range" sheetIndex
        None
;;

GetDataValidationsByIndex "external_data_validation_sample.xlsx" 0 |> Option.get |> List.map (fun v -> v.PromptTitle);;
GetDataValidationsByIndex "external_data_validation_sample.xlsx" 1 |> Option.get |> List.map (fun v -> v.PromptTitle);;

Observed behavior
Both queries above will return empty lists.

Expected behavior
Expected to return data validations with formulas to other sheets (e.g., =Sheet2!$B$2:$B$5). (Tested the above function on an internal XLSX file as well, and all validations get returned, except the ones that refer to sheets in their formulas.)

Desktop (please complete the following information):

• OS: Windows, Mac
• .NET Target: .NET Core 8.0
• DocumentFormat.OpenXml Version: 3.0.0

[tomjebo]

@toraritte Sorry this is coming so late. I took a look at the code and recreated this in C#, finding that the formula based dataValidation elements are x14 (Office2010) extensions and therefore considered different types from the original Office2007 x:dataValidation. You need to scope them differently when using the framework. Here is an example of using the new dataValidation elements in C#:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;


var spd = SpreadsheetDocument.Open(args[0], false);
var wb = spd.WorkbookPart;

if (wb is null)
{
    Console.WriteLine("No workbook part!");
    throw new ArgumentException();
}

var sheets = wb.Workbook.Sheets;

if ((sheets is null) || (sheets.Descendants<Sheet>().Count<Sheet>() == 0))
{
    Console.WriteLine("No sheets collection in workbook!");
    throw new ArgumentException();
}

foreach (var sheet in sheets.Descendants<Sheet>())
{
    var worksheetpart = wb.GetPartById(sheet.Id);
    if ((worksheetpart is null) || (worksheetpart.RootElement is null))
    {
        Console.WriteLine("corrupt worksheet part!");
        throw new ArgumentException();
    }

    var dvs = worksheetpart.RootElement.Descendants<DocumentFormat.OpenXml.Office2010.Excel.DataValidation>();
    foreach (DocumentFormat.OpenXml.Office2010.Excel.DataValidation dv in dvs)
    {
        Console.WriteLine("Type of validation: {0}", dv.Type);
        if ((dv.DataValidationForumla1 is not null) && (dv.DataValidationForumla1.Formula is not null))
        {
            Console.WriteLine("Formula: {0}", dv.DataValidationForumla1.Formula.Text);
        }
        if ((dv.DataValidationForumla2 is not null) && (dv.DataValidationForumla2.Formula is not null))
        {
            Console.WriteLine("Formula: {0}", dv.DataValidationForumla2.Formula.Text);
        }
    }
}

Hopefully, I've understood the nature of your problem and this will help. Otherwise, please clarify what you need.