CODEWITHSUNDEEP
interopexcel-2007
ps.
ps.

Reputation: 4360

check if a sheet exists in excel

How do i check if a sheet exists in a excel using interop. I tried the following but it throws an COMException if not there.. Is there a better way of finding out than actually looking at the exception

    Worksheet sheet = null;
    Sheets worksheets = some;

    sheet = (Worksheet)worksheets.get_Item("sheetName");
    if(sheet!=null)
{
//do something
}

Edit:

Thanks for the input guys.

i wrote a function

private Dictionary<string, Worksheet> GetSheetsMap(Sheets worksheets)
{

    if (worksheets == null)
        throw new ArgumentNullException("worksheets");


    Dictionary<string, Worksheet> map = new Dictionary<string, Worksheet>(StringComparer.CurrentCultureIgnoreCase);
    foreach (Worksheet s in worksheets)
    {
        map.Add(s.Name, s);
    }

    return map;
}

And i use it as below

 Dictionary<string, Worksheet> sheetMap = GetSheetsMap(worksheets);
            Worksheet sheet = null;


            if (sheetMap.TryGetValue(ExtendedTemplateManager.BasicUserTemplate, out sheet))
            {
                //found it.
            }
else
{
// not
}

Upvotes: 6

Views: 23865

Answers (4)

Makatun
Makatun

Reputation: 1017

If you have worksheet and workbook objects them you can do parent check

if (sheet.Parent == workbook)

Upvotes: 0

pmartin
pmartin

Reputation: 2741

Here's a LINQ way to do it (and this method returns null if the sheet doesn't exist):

workbook.Worksheets.Cast<Worksheet>().FirstOrDefault(worksheet => worksheet.Name == worksheetName);

Upvotes: 3

brydgesk
brydgesk

Reputation: 864

Do you have a Workbook object? If so, you can iterate over the Workbook.Sheets array and check the Name property of each Sheet.

foreach (Sheet sheet in workbook.Sheets)
{
    if (sheet.Name.equals("sheetName"))
    {
        //do something
    }
}

Upvotes: 12

Hans Passant
Hans Passant

Reputation: 942267

First consider if an exception isn't actually appropriate. You expect a sheet with a certain name to be there. If it isn't, can you still meaningful continue running your program?

If you can, you can avoid the exception by iterating the sheets collection and look for a match on the Name property.

Upvotes: 3

Related Questions