Retrieve a dictionary of all named ranges in a spreadsheet document

OpenXML Excel

 string FilePath = @"..\..\..\..\Sample Files\";

string FileName = FilePath + "Retrieve a dictionary of all named ranges.xlsx";

Dictionary<String, String> ranges = GetDefinedNames(FileName);

public static Dictionary<String, String> GetDefinedNames(String fileName)

{

    // Given a workbook name, return a dictionary of defined names.

    // The pairs include the range name and a string representing the range.

    var returnValue = new Dictionary<String, String>();

    // Open the spreadsheet document for read-only access.

    using (SpreadsheetDocument document =

        SpreadsheetDocument.Open(fileName, false))

    {

        // Retrieve a reference to the workbook part.

        var wbPart = document.WorkbookPart;

        // Retrieve a reference to the defined names collection.

        DefinedNames definedNames = wbPart.Workbook.DefinedNames;

        // If there are defined names, add them to the dictionary.

        if (definedNames != null)

        {

            foreach (DefinedName dn in definedNames)

                returnValue.Add(dn.Name.Value, dn.Text);

        }

    }

    return returnValue;

}

Aspose.Cells

 string FilePath = @"..\..\..\..\Sample Files\";

string FileName = FilePath + "Retrieve a dictionary of all named ranges.xlsx";

Dictionary<String, String> ranges = GetDefinedNames(FileName);

public static Dictionary<String, String> GetDefinedNames(String fileName)

{

    // Given a workbook name, return a dictionary of defined names.

    // The pairs include the range name and a string representing the range.

    var returnValue = new Dictionary<String, String>();



    // Open a SpreadsheetDocument based on a filepath.

    Workbook workbook = new Workbook(fileName);



    //Getting all named ranges

    Range[] range = workbook.Worksheets.GetNamedRanges();



    // If there are items in Ranges, add them to the dictionary.

    if (range != null)

    {

        foreach (Range rn in range)

            returnValue.Add(rn.Name, rn.Value.ToString());

    }

    return returnValue;

}

Download Running Code Example

• GitHub

Sample Code

• GitHub