How to get Table/Cells data based on Defined Name/Defined name Range in Excel using OpenXML & C#

I've One Excel file with multiple sheets. In one sheet I've 2-3 tables like data.
Each table data having one defined name.
Now I want to get the respective table data based on the defined name. I'm new to openXML can anyone suggest any Ideas..?

Example:
In the Image I'm having 3 table like data, each one having one defined name. If we select any of the defined name it'll select the respective data.

I'm trying to achieve this. Based on the define name how can I get the data.

using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath, false))
        {
            WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
            Sheets theSheets = workbookPart.Workbook.Sheets;
            foreach (Sheet item in theSheets)
            {
                if (item.Name.Value == "Sheet1")
                {
                    var definedNames = GetDefinedNames(filePath);
                    foreach(KeyValuePair<string, string> dn in definedNames)
                    {
                        Console.WriteLine(dn.Value);
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(item.Id.Value);
                    Worksheet workSheet = worksheetPart.Worksheet;
                    SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                    IEnumerable<Row> rows = sheetData.Descendants<Row>();
                    //this will include header row...
                    foreach (Cell cell in rows.ElementAt(0))
                    {
                        if (!table.Columns.Contains(GetCellValue(spreadSheetDocument, cell)))
                        {
                            table.Columns.Add(GetCellValue(spreadSheetDocument, cell));
                        }
                    }
                    // this will include cell data
                    foreach (Row row in rows)
                    {
                        DataRow tempRow = table.NewRow();
                        for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                        {
                            tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                        }
                        table.Rows.Add(tempRow);
                    }
                }
            }
        }
        table.Rows.RemoveAt(0);

By using below method I'm getting defined names but, How can I get the data which is tagged to those defined names:

public static Dictionary<string, string> GetDefinedNames(string fileName)
    {
        var returnValue = new Dictionary<string, string>();
        using (SpreadsheetDocument document =
            SpreadsheetDocument.Open(fileName, false))
        {
            WorkbookPart wbPart = document.WorkbookPart;
            DefinedNames definedNames = wbPart.Workbook.DefinedNames;
            if(definedNames != null)
            {
                foreach(DefinedName dn in definedNames)
                {
                    if (!returnValue.ContainsKey(dn.Name.Value))
                    {
                        returnValue.Add(dn.Name.Value, dn.Text);
                    }
                }
            }
        }
        return returnValue;
    }

OpenXML does not provide functionality for that. You might be better off using some higher level API like ClosedXML etc.

If you insist on using OpenXML, here is what you would need to do:

The DefinedName's Text property contains the cell range in the form "SHEETNAME!$A$1:$C$3". Note that it can contain multiple ranges, separated by commas. Also, the name of the sheet can be surrounded by square brackets.
Parsing that string gives you the left-upper and the right-bottom adresses of the cells in the ranges.

Then you need functionality to get cells by their adresses. OpenXML does not provide that either. You already have the code for reading cell values. The CellReference property of the Cell contains the adress of the cell ("A1") - now you need check if the adress is within the range from the DefinedName.

Instead of using the OpenXML SDK use a high-level library like EPPlus or ClosedXML. The OpenXML SDK is too low-level as it follows the XML structure closely.

In EPPlus you can read the contents of a named table with just sheet.Tables["myTable"],for example:

using(var package = new ExcelPackage(file))
{
    var sheet = package.Workbook.Worksheets["Tables"];

    var myTable = sheet.Tables["myTable"];   
    var range=myTable.Range;
    ...

    package.Save();
}

EPPlus offers methods to extract the data into a DataTable or List<T>, save it to CSV, JSON or HTML.

var table=sheet.Tables["myTable"];
var items=table.ToCollection<TestDTO>();