Worksheet get_Range throws exception

I'm using C# to manipulate an Excel worksheet. The following two pieces of code should work the same, but one works and the other throws an exception. I wonder why.

This works:

oRange = (Excel.Range)oSheet.get_Range("A1","F1");
oRange.EntireColumn.AutoFit();

This throws an exception:

oRange = (Excel.Range)oSheet.get_Range(oSheet.Cells[1, 1],oSheet.Cells[4,4]);
oRange.EntireColumn.AutoFit();

Exception:

RuntimeBinderException occurred. "object" does开发者_JAVA技巧 not contain a definition for 'get_Range'

The oSheet is instantiated as follows:

Excel.Worksheet oSheet = new Excel.Worksheet();

Am I supposed to instantiate both differently?

It looks like the exception is thrown from the oSheet.Cells[1, 1] and oSheet.Cells[4, 4] used as arguments to get_range.

Applying the following, no exception will be thrown:

Excel.Range c1 = oSheet.Cells[1, 1];
Excel.Range c2 = oSheet.Cells[4, 4];
oRange = (Excel.Range)oSheet.get_Range(c1, c2);
oRange.EntireColumn.AutoFit();

So, it might be related to the oSheet.get_Range functionality. It receives an object as an argument, therefore it might try to invoke a get_Range method on the arguments to receive the internal cell, and the up-cast from Range to object done by the compiler might hide the method call.

If you need the cells definition by row/column - try using the above approach.

Use the Worksheet Range property instead. For example, instead of

oRange = (Excel.Range)oSheet.get_Range(oSheet.Cells[1, 1],oSheet.Cells[4,4]);

use

oRange = (Excel.Range)oSheet.Range[oSheet.Cells[1, 1],oSheet.Cells[4,4]];

I was using the get_Range() method extensively when I leveraged off .NET 2. When I changed to .NET 4 Client Profile, I got this exception also. Replacing the get_Range() references with the Range property addressed this issue for me.

For everyone who have this problem, much better use explicit conversion, rather than create new variables.

oRange = (Excel.Range)oSheet.get_Range((Excel.Range)oSheet.Cells[1, 1], (Excel.Range)oSheet.Cells[4,4]);

I had upgraded my VS C# project from .Net Framework 3.5 to 4.5. Once I had upgraded successfully, I got this "object" does not contain a definition for 'get_Range' exception when trying to generate an excel report.

What I did is instead of using below code

range = xlSheet.get_Range(Cells[xlRow, xlCol], Cells[xlRow, xlCol]);

I rewrote to

range = xlSheet.Range[Cells[xlRow, xlCol], xlSheet.Cells[xlRow, xlCol]];

And it worked.

I tried the above answers on .NET 5 but they do not work anymore. Instead, following code works.

Excel.Range c1 = (Excel.Range)excelSheet.Cells[1, 1];
Excel.Range c2 = (Excel.Range)excelSheet.Cells[4, 4];
Excel.Range oRange = oSheet.get_Range(c1, c2);