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 (stackoverflow.com/questions/7099770/…)? — Daniel Daranas
– Daniel Daranas, Commented Aug 29, 2012 at 8: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
– Francesco Baruchelli, Commented Aug 29, 2012 at 10:40

Steve · Accepted Answer · 2012-08-29 10:27:10Z

3

Found a solution.

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

answered Aug 29, 2012 at 10:27

Steve

3,0823 gold badges24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:24:05Z

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, 2017 at 12:24

CommunityBot

11 silver badge

answered Aug 29, 2012 at 9:19

Pilgerstorfer Franz

8,3473 gold badges46 silver badges56 bronze badges

2 Comments

Steve Over a year ago

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.

Pilgerstorfer Franz Over a year ago

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 (<excel 2010 or not) programm should split values on multiple sheets or if xlVersion is 2010 give all values on one sheet?

Collectives™ on Stack Overflow

Writing a large 2d array to Excel

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related