How to get an existing range name from an Excel worksheet using c# Excel.Interop?

Question

I am working with a very large worksheet that has many named ranges. I expect that the worksheet will be modified in the future, so I cannot interact with cells using their cell number.

How can I get an existing name (or list of the existing names) from the worksheet? Alternatively, how can I tell my C# program that a certain name already exists, and to utilize it?

Answer 1

You'll want to use Excel.Worksheet.Names

foreach (Excel.Worksheet sheet in wb.Sheets) {
    foreach (Excel.Range range in sheet.Names) {
        // this is the named ranges
     }
}

Source

If you were to use EPPlus, you could do this (very simple)

    static void Main(string[] args) {
        using (FileStream stream = new FileStream(@"C:\Users\bblack\Test\TestWorksheet.xlsx", FileMode.Append))  {
            using (ExcelPackage xl = new ExcelPackage(stream)) {
                // xl by default contains one workbook;
                bool test;
                foreach (ExcelWorksheet sheet in xl.Workbook.Worksheets) {
                    test = NamedRangeExists("NamedRange", sheet);
                }
            }
        }
    }

    static bool NamedRangeExists(string name, ExcelWorksheet ws) {
        return ws.Names.Where(n => n.Name == name).Count() > 0;
    }

That would open up the specified file, iterate through the sheets contained in the workbook and check for a named range within them called "NamedRange".