Hi,
I am using the Workbook.Load function to load up a large spreadsheet containing 100000 rows. This operation is taking a very long time and it appears that the entire contents of the sheet is being read into memory. Is there a more efficient means of achieving this?
Also, please find attached a spreadsheet which kills the Workbook object. A macro needs to be executed in the spreadsheet to generate content. There's a massive button to click on the first worksheet which executes the macro. The following errors are the result:
ContextSwitchDeadlock was detectedMessage: The CLR has been unable to transition from COM context 0x661f00 to COM context 0x662070 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.
And later:
An unhandled exception of type 'System.StackOverflowException' occurred in Infragistics3.Excel.v9.2.dll
Thanks in advance for any assistance provided.
There have been many bug fixes and performance enhancements to the loading logic. Try downloading the latest SR for 9.2 to see if your problem still occurs.
We are now using the latest build (Netadvantage 2010.3). The Workbook.Load() runs successfully but later on when I'm interrogating the spread sheet, I get a "Stack Overflow" exception. See the code below.
private void BuildExcelDataSource()
{
myWorkbook = Workbook.Load(myExcelFileName, true);
myWorkbook.DateSystem = DateSystem.From1900;
DataSet dataSource = new DataSet();
foreach (Worksheet worksheet in myWorkbook.Worksheets)
DataTable sheetTable = BuildDataTableForSheet(worksheet.Name);
if (sheetTable != null)
dataSource.Tables.Add(sheetTable);
}
myWorkbookDataSet = dataSource;
private DataTable BuildDataTableForSheet(string aWorkSheet)
DataTable dataTable = new DataTable(aWorkSheet);
// MIN/MAX Values used to frame the working size of the Excel data to be imported.
const int minCellColumn = 0;
const int minCellRow = 0;
int maxCellRow = Int32.MinValue;
int maxCellColumn = Int32.MinValue;
foreach (WorksheetRow row in myWorkbook.Worksheets[aWorkSheet].Rows)
// Exception happens here
CheckRowForDataGridInclusion(row, ref maxCellRow, ref maxCellColumn);
// More code here..
private void CheckRowForDataGridInclusion(WorksheetRow row, ref int previousMaxRowIndex, ref int previousMaxColumnIndex)
foreach (WorksheetCell cell in row.Cells)
if (cell.Value != null)
previousMaxRowIndex = Math.Max(previousMaxRowIndex, cell.RowIndex);
previousMaxColumnIndex = Math.Max(previousMaxColumnIndex, cell.ColumnIndex);