how to specify number of worksheets to create while doing late binding in c#?

Question

I need to specify number of worksheets to be created using late binding. Here is my code & exception says invalid index:

oSheets = oWB.GetType()
    .InvokeMember("Worksheets", BindingFlags.GetProperty, null, oWB, new object[] { 7 });

Below code works and creating 3 worksheets,

oSheets = oWB.GetType()
    .InvokeMember("Worksheets", BindingFlags.GetProperty, null, oWB, null );

Let me know what I need to do to create worksheets with a specified number.

Answer 1

The reason you are getting an exception is that you are invoking a property and trying to pass a parameter to it. Excel by default creates 3 worksheets which is why the second line works. If you want to add more worksheets, you need to call the Workbook.Worksheets.Add() method and specify the count property. You can continue to use reflection as you asked but it's much easier to write (and read back) if you use the dynamic type:

dynamic oWB = GetWorkbook();
dynamic oSheets = oWB.Worksheets();

//We now have 3 worksheets, and now we will add 4 more
oSheets.Add(null, null, 4, xlSheetType.xlWorksheet);

And the enumeration if needed:

public enum xlSheetType
{
    xlWorksheet = -4167
}