Writing a large 2d array to Excel

Question

I'm looking to write a large 2d array to an Excel worksheet using C#. If the array is 500 x 500, the code that I would use to write this is as follows:

 var startCell = Worksheet.Cells[1, 1];
 var endCell = Worksheet.Cells[500, 500];
 var writeRange = (Excel.Range)Worksheet.Cells[startCell, endCell;
 writeRange.Value = myArray;

I get an exception on this line:

var endCell = Worksheet.Cells[500, 500];

As anybody who has used C# and Excel via COM can testify, the error message received is pretty much useless. I think that the issue is that the underlying data structure used for the worksheet is not of sufficient size to index cell 500,500 when I first create the sheet.

Does anybody know how to achieve the desired result? I'm hoping that there is a simple way to re-size the underlying data structure before creating the Range.

Thanks.

Edit: Error message is:

{"Exception from HRESULT: 0x800A03EC"}

With and excel error code of -2146827284.

Update: The link supplied in the comments below alluded to an issue with opening the Excel sheet in compatibility mode. This does seem to be the problem. If I save the document in .xlsx or .xlsm format before running my code, this seems to work. My issue is that I cannot expect my users to do this each time. Is there a programmitcal way of achieving this? Would it simply be a case of opening the file, checking the extension and then saving it in the new format if needed?

Answer 1

Found a solution.

Instead of using Worksheet.Cells[x, x], use Worksheet.get_range(x, x) instead.

Answer 2

I just wrote a small example that worked for me. Originally found at SO this answer. I had to adapt this answer as in my Interop assembly (Excel 14 Object Library) there is no more method Worksheet.get_Range(.., ..)

var startCell = ws.Cells[1, 1];
int row = 500, col = 500;
var endCell = ws.Cells[row, col];
try
{
    // access range by Property and cells indicating start and end           
    var writeRange = ws.Range[startCell, endCell];
    writeRange.Value = myArray;
}
catch (COMException ex)
{
    Debug.WriteLine(ex.Message);
    Debugger.Break();
}