I am working on a problem where I need to ascertain whether a specific DefinedName in a workbook is referenced within a particular worksheet. I am currently using the OpenXML library to facilitate this.

Here is my current approach:

Firstly, I construct a HashSet containing all calculations within the worksheet. This is achieved through the following method:

private HashSet<string> BuildAllCalculationsSet(SpreadsheetDocument document)
{
    HashSet<string> allCalculations = new HashSet<string>();

    // Iterate over all worksheets
    foreach (var worksheetPart in document.WorkbookPart.WorksheetParts)
    {
        var cellsWithFormulas = worksheetPart.Worksheet.Descendants<Cell>().Where(c => c.CellFormula != null);

        // Iterate over all cells with formulas
        foreach (var cell in cellsWithFormulas)
        {
            // Add the cell s formula to the set of all calculations
            allCalculations.Add(cell.CellFormula.Text);
        }
    }

    return allCalculations;
}

Subsequently, I employ this HashSet to determine if a specific DefinedName is referenced within the worksheet:

private bool IsNamedRangeReferenced(HashSet<string> allCalculations, string namedRange)
{
    // Check if the named range is referenced in any calculation
    return allCalculations.Any(calculation => calculation.Contains(namedRange));
}

This method is utilized as follows:

if (IsNamedRangeReferenced(allCalculations, namedRange.Name.Value))
{
    isReferenced = true;
    break;
}

Now, I am contemplating incorporating recursion and Graphs with Depth First Search algo to also account for scenarios where a DefinedName might be referenced by another DefinedName which in turn is referenced in the worksheet s calculations. However, before proceeding with this approach, I am curious to explore if there might be a more efficient solution.

Given that some of the documents can contain more than 30,000 DefinedNames, it is crucial to strike a balance between performance and reliability. I am open to solutions using either OpenXML, which I m currently employing, or ClosedXML.

Any advice or guidance would be greatly appreciated!

Thank you in advance.