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?

Although it is "pretty much useless", it would be interesting that you tell us what you see. Maybe you have this problem (http://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range)? — Daniel Daranas, Aug 29 '12 at 08:47
be aware that in older versions of excel (e.g. 2003), your sheets can't have more than 256 columns (the last one is IV) and 65536 rows, so they can't contain a 500*500 matrix without splitting it — Francesco Baruchelli, Aug 29 '12 at 10:40

score 3 · Accepted Answer · answered Aug 29 '12 at 10:27

3

Found a solution.

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

answered Aug 29 '12 at 10:27

Steve

2,950
3
21
32

score 2 · Answer 2 · edited May 23 '17 at 12:24

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();
}

edited May 23 '17 at 12:24

Community

1
1

answered Aug 29 '12 at 09:19

Pilgerstorfer Franz

8,303
3
41
54

This is pretty much the same as my code above, and this does work in normal circumstances. The issue is when the workbook opens in Compatibility Mode. – Steve Aug 29 '12 at 09:34
I am currently working on a new example. Pls confirm some assumptions: the programm shall open a file, according to the current version of file ( – Pilgerstorfer Franz Aug 29 '12 at 10:10

Writing a large 2d array to Excel

2 Answers2

Linked