Adding large amount of data to excel range generates excetion:HRESULT: 0x800A03EC

Question

I have made some code that can take som data from a .NET dataset and put it
into an excell sheet. This works perfectly when i have e.g. 1000 rows of
information. My problem is that based on som mete data, i create a four
column and 24000+ row data for an excel range. when i try to assign the data
to the range, i get the following exception:
Exception from HRESULT: 0x800A03EC
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Microsoft.Office.Interop.Excel.Range.set_Value2(Object )

the code that produces the error is:

private void InsertData(DataTable table, _Worksheet concreteWorkSheet)
{
 // the worksheet starting at cell A2.
 int numOfRows = table.Rows.Count;
 int numOfColums = table.Columns.Count;
 object[,] objData = new Object[numOfRows, numOfColums];

 for (int row = 0; row < numOfRows; row++)
 {
for (int column = 0; column < numOfColums; column++)
{
 objData[row, column] = table.Rows[row][column];
}

 }
 excellRange = concreteWorkSheet.get_Range("A2", excelNullValue);
 excellRange = excellRange.get_Resize(numOfRows, numOfColums);
 excellRange.Value2 = objData;
}

 

********************The operation calling the method is the
following******************
Application excelApplication;
excelApplication = new Application();
Workbooks excellWorkBooks;
excellWorkBooks = excelApplication.Workbooks;
_Workbook concreteWorkbook;
concreteWorkbook = excellWorkBooks.Add(excelNullValue);
Sheets excelWorkSheets;
excelWorkSheets = concreteWorkbook.Worksheets;
if (data.Tables.Count > excelWorkSheets.Count)
{
 int numberOfMissingSheets = data.Tables.Count - excelWorkSheets.Count;
 for (int i = 0; i < numberOfMissingSheets; i++)
 {
concreteWorkbook.Worksheets.Add(excelNullValue, excelNullValue,
excelNullValue, excelNullValue);
 }
}
for (int i = 1; i <= excelWorkSheets.Count && i <= data.Tables.Count;
i++)
{
 _Worksheet concreteWorkSheet;
 concreteWorkSheet = (_Worksheet)(excelWorkSheets.get_Item(i));

 DataTable table = data.Tables[i - 1];
 CreateHeaders(table, concreteWorkSheet);
 InsertData(table, concreteWorkSheet);
}

if (!Path.IsPathRooted(excelFileName))
{
 excelFileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
Path.GetFileName(excelFileName));
}

// Save the Workbook and quit Excel.
if (File.Exists(excelFileName)) File.Delete(excelFileName);
concreteWorkbook.SaveAs(excelFileName, excelNullValue, excelNullValue,
excelNullValue, excelNullValue, excelNullValue,
XlSaveAsAccessMode.xlNoChange,
XlSaveConflictResolution.xlLocalSessionChanges, excelNullValue,
excelNullValue, excelNullValue, excelNullValue);
concreteWorkbook.Close(false, excelNullValue, excelNullValue);
excelApplication.Quit();

Answer 1

Hi Morton

1. This forum is for discussing the VSTO technology, not for Office automation (which is what you're doing, since you're instantiating the application). The correct place to get help on problems revolving around the Office object model is in the newsgroup interface; you'll find a list of newsgroups, by topic, in the "Please Read First" message at the top of this forum.

2. As you're automating Excel, we have no way of knowing which version is involved. The version of Excel affects, for example, the number of rows available in a worksheet. And how well it can handle a data dump of this magnitude.

My advice is to first ask in the excel.programming newsgroup if there's a general limitation for dumping 24000+ x 4 cell's worth of data into a range. You may be dealing with a general limitation, or this may be a problem with the .Net-COM interface - but that's where you have to start tracking it down.

You may also want to investigate putting the data into an ADO (not .NET) or DAO Recordset object, then use the CopyFromRecordset method.

 

Answer 2

If you're using Microsoft Excel 2003 and any field within your DataTable contains a string longer than 911 characters, you may be hitting a known issue: http://support.microsoft.com/kb/818808

-Phil

 

Answer 3

none of the data/strings in my excel sheet is that long. I found that instead of inserting 24000+ rows in one operation, i split the operation and tried with a smaller amount of data. This resulted in the following behavior:

 

inserting the data at 1000 rows at a time, the exception occured after some 3000+ inserted rows.

inserting the data at 200 rows at a time, the exception occured after some 2300+ rows inserted.

inserting the data at 150 rows at a time, the exception occured after some 1900+ rows inserted.

inserting the data at 140 rows at a time, the exception occured after some 1900+ rows inserted.

inserting the data at 127 or 128 rows at a time, the exception did not occur.

 

So it could look like there is som kind of 7bit (127 or 128 with offset 1) constraint/error, that when adding large amount of data makes Excel throw exceptions. I managed to create a workaround, but i cant figure out why it was made this way in the first place.

I have only tried inserting one specific dataset, so it could also be something with the total amount of bytes inserted at a time, and a coincidence with the 127/128 limit.

 

Im not going to investigate it any further though.

Answer 4

Hi Cindy,

Just a quick question.

If you were trying to do this with Visual C# express 2008 would it still work or do you need VSTO. In other words do you need the full blown edition of visual c#?

Thanks
Ade