Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding large amount of data to range get excetion:HRESULT:0x800A03
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(Strin g memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) at Microsoft.Office.Interop.Excel.Range.set_Value2(Ob ject ) 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(); |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding large amount of data to range get excetion:HRESULT:0x800A03
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reformating large amount of data | Excel Discussion (Misc queries) | |||
Grouping large amount of data by day-of-week | Excel Discussion (Misc queries) | |||
Changing large amount of Data | Excel Discussion (Misc queries) | |||
Large amount of data for plotting | Charts and Charting in Excel | |||
CopyFromRecordset with large amount of data | Excel Programming |