Search code examples
c#.net-coreexcel-interop

Excel IndexOutOfRange with .NET Core

Based on the example https://github.com/dotnet/samples/tree/master/core/extensions/ExcelDemo I can get Excel interop working in .NET Core 3.1 (yes, I realize Excel isn't exactly cross-platform but bear with me). The following code runs fine when targeting .NET Framework 4.7.2 and but on .NET Core the last line blows up with an IndexOutOfRangeException "Index was outside the bounds of the array."

Excel.Application excel;
Excel.Workbook workbook;
Excel.Worksheet sheet;
Excel.Range range;

// Start Excel and get Application object.
excel = new Excel.Application();
excel.Visible = true;

// Get a new workbook.
workbook = excel.Workbooks.Add(Missing.Value);
sheet = (Excel.Worksheet)workbook.ActiveSheet;

range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, 4]];  //this works fine with .NET Framework but not Core

The legacy code I'm attempting to port extensively sets variable ranges like this otherwise I would simply use absolute spreadsheet style indexing like "A1:D1" which seems to work fine in .NET Core. I've tried setting excel.ReferenceStyle = Excel.XlReferenceStyle.xlR1C1 as well as permutations of sheet.get_Range(,) instead of sheet.Range[,] but I still get the same out of bounds problem despite it being a valid range. Any ideas how to get the Range calls working in .NET Core?

Solution

  • The workaround is here, though I don't know why:

    you can call like below

    Excel.Range oCell1 = sheet.Cells[1, 1];
Excel.Range oCell2 = sheet.Cells[1, 4];
range = sheet.Range[oCell1, oCell2];

    or

    range = sheet.Range[(Excel.Range)sheet.Cells[1, 1], (Excel.Range)sheet.Cells[1, 4]];