1

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?

user3685427
  • 211
  • 2
  • 8
  • As a note, to make things easier on yourself it's recommended to use a plugin when it comes to interop since documentation and intellisense for it are sparse. Both LINQtoExcel and EPPlus are amazing and efficient plugins that I can't do without when it comes to working with Excel in C# – Ben Black Jun 09 '14 at 18:23

4 Answers4

4

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".

Ben Black
  • 3,751
  • 2
  • 25
  • 43
  • Thanks! This was a great help. – user3685427 Jun 17 '14 at 12:14
  • I apologize for the late response, but I'm trying to figure out how use EPPlus. I installed the package using the NuGet Package command line. I stated `using OpenOfficeXml;`at the top yet I'm still getting errors on your line `return ws.Names.Where(n ....`. Intellisense does not like the "Where" and will not provide me any fixes. Any help? – Sanya Jan 11 '17 at 14:54
  • If it doesn't have a reference to `.Where()` then that probably means you don't have the proper `using` statement. Off the top of my head I think that requires `using System.Linq` – Ben Black Jan 11 '17 at 16:22
  • I'm pretty sure you can just get the names directly off the Workbook rather than iterating through every sheet: https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.names. Haven't tested it yet though. – Colm Bhandal Sep 09 '19 at 12:01
0

are you looking for something like this? In this solution I put them in a dictionary

Dictionary<string, Worksheet> dictionary = new Dictionary<string, Worksheet>();
foreach ( Worksheet worksheet in ws.Worksheets )
{
   dictionary.Add( worksheet.Name, worksheet );
}

update

Sheet1.Range("A3:A7")<-- get range

wb is of type Workbook

Dictionary<string, Range> dictionary = new Dictionary<string, Range>();
foreach (Worksheet sheet in wb.Sheets) {
    foreach (Range range in sheet.Names) {
        dictionary.Add( range.Name, Range );
        //use here range var
        var cells= range.Cells;
     }
}

refer here to see which property you want

faby
  • 7,394
  • 3
  • 27
  • 44
  • `Sheet1.Range("A3:A7")` – faby Jun 09 '14 at 18:07
  • 1
    What? This lists the names of the worksheets, such as Sheet1, Sheet2, Sheet3, what he wants is a list of named ranges within the Worksheet itself, which means he would need to use the `Names` member, not `Name` member. – Ben Black Jun 09 '14 at 18:09
  • Also, how do you plan to iterate through the Sheets collection of the Excel app? You need to iterate through the Sheets collection of a Workbook, not the Excel Application itself. – Ben Black Jun 09 '14 at 18:21
  • if you post some code and explain me better the problem I'll try to help you in a better way. – faby Jun 09 '14 at 18:24
  • I don't have a problem, I already answered the question above, I was merely correcting your code. You can't iterate through the `Sheets` collection of the `Excel.Application`, try it and see what happens. – Ben Black Jun 09 '14 at 18:25
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/55338/discussion-between-faby-and-ben-black). – faby Jun 09 '14 at 18:34
  • Thanks! I used both yours and Ben's suggestions, and it has worked extremely well. – user3685427 Jun 17 '14 at 12:15
0

This alternative to Ben Black's answer worked for me :

foreach (Excel.Worksheet sheet in wb.Sheets) {
    foreach (Excel.Name name in sheet.Names) {        
        var value = sheet.get_Range(name.NameLocal).Value;//get value of named range
        var formula = sheet.get_Range(name.NameLocal).Formula;//get Formula of named range
 }
}
David Watts
  • 2,249
  • 22
  • 33
no_doubt
  • 11
  • 2
0
        int row = 3;

        var wb = Globals.ThisAddIn.Application.ActiveWorkbook;
        ws.Range["A6"].Value = wb.Names.Count;

        foreach (Name rangeName in wb.Names)
        {
            Range c = ws.Cells[row++, 3];
            c.Value = rangeName.NameLocal;
        }
        var ws = Globals.ThisAddIn.Application.ActiveSheet;
        ws.Range["A7"].Value = ws.Range["MyRangeName"].Value;
Joe B
  • 692
  • 8
  • 18