Adding large amount of data to range get excetion:HRESULT:0x800A03

[Morten Herman Jensen]

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

 

[Morten Herman Jensen]

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 might 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.