Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Microsoft.Office.Interop.Excel very slow and crash after writing a
Hi everyone.
I need to write out an Excel file with three WorkSheet getting data from database. I have code that works fine (but very slow :(... ) for tables that are small.. But for tables size about 500 cells it causes excel crashing with the following error and stack trace: Microsoft Visual C++ Runtime Library: runtime Error. Abnormal program termination. in System.RuntimeType.ForwardCallToInvokeMember(Strin g memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) in Microsoft.Office.Interop.Excel.Range.set_ColumnWid th(Object ) in MotoroSensorLIST.Form1.function_components_eq_list (_Worksheet objSheet, Int32 id_funzione, String codice_funzione) in C:\Documents and Settings\marco.serioli\Documenti\Visual Studio 2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Fo rm1.cs:riga 1256 in MotoroSensorLIST.Form1.function_eq_list(_Worksheet objSheet) in C:\Documents and Settings\marco.serioli\Documenti\Visual Studio 2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Fo rm1.cs:riga 1080 in MotoroSensorLIST.Form1.eq_list(_Worksheet objSheet) in C:\Documents and Settings\marco.serioli\Documenti\Visual Studio 2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Fo rm1.cs:riga 827 in MotoroSensorLIST.Form1.eq_list_sheet(Int32 index) in C:\Documents and Settings\marco.serioli\Documenti\Visual Studio 2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Fo rm1.cs:riga 819 in MotoroSensorLIST.Form1.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\marco.serioli\Documenti\Visual Studio 2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Fo rm1.cs:riga 64 System.Object ForwardCallToInvokeMember(System.String, System.Reflection.BindingFlags, System.Object, Int32[], System.Runtime.Remoting.Proxies.MessageData ByRef) During debugging application I find that the exception is throw while setting alignment of a cell, but I suppose that it is non deterministic because the same instruction is working fine in the previous iteration. Here is the code that i am using .. ---------------------------------------------------- objApp = new Excel.Application(); objBooks = objApp.Workbooks; objBook = objBooks.Add(Missing.Value); objSheets = objBook.Worksheets; currentRow = 0; Excel.Range range; // populate DataTable function foreach (DataRow dr in function.Rows) { range = objSheet.get_Range("A" + currentRow, "A" + (currentRow+1)); range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders.Weight = Excel.XlBorderWeight.xlMedium; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; range.MergeCells = true; range.Font.Bold = true; range.Font.Size = 12; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.set_Value(Missing.Value, dr["Codice_funzione"]); range.Interior.ColorIndex = 15; range = objSheet.get_Range("B"+currentRow, "K"+ (currentRow+1)); range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders.Weight = Excel.XlBorderWeight.xlMedium; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; range.MergeCells = true; range.Font.Bold = true; range.Font.Size = 12; range.set_Value(Missing.Value, dr["Descrizione"].ToString().ToUpper()); range.Interior.ColorIndex = 15; range = objSheet.get_Range("L" + currentRow, "M" + (currentRow + 1)); range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders.Weight = Excel.XlBorderWeight.xlMedium; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; range.MergeCells = true; range.Font.Bold = true; range.Font.Size = 12; range.set_Value(Missing.Value, dr["Disegno"]); range.Interior.ColorIndex = 15; currentRow += 2; function_components_sensor_list( objSheet, int.Parse(dr["Id_funzione"].ToString()), dr["Codice_funzione"].ToString()); range = objSheet.get_Range("A" + currentRow, "M" + (currentRow + 1)); range.MergeCells = true; currentRow += 2; } ------------------------------------ ......and more similar code Thank you for any suggestion! Marco |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Microsoft.Office.Interop.Excel very slow and crash after writing a
I think i've found the problem. Too many styles applied at the cells.. Too many borders and background colors.. But I need this kind of cell's format. Does anybody know how to avoid this problem? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I think i've found the problem. Too many styles applied at thecells..
I have exactly the same problem: there is a significant delay setting a value in a cell which has some formatting, e.g. borders. If I remove the formatting performance is much better.
Did you find any solution to this problem in the end? On Friday, March 20, 2009 3:45 AM mseriol wrote: Hi everyone. I need to write out an Excel file with three WorkSheet getting data from database. I have code that works fine (but very slow :(... ) for tables that are small.. But for tables size about 500 cells it causes excel crashing with the following error and stack trace: Microsoft Visual C++ Runtime Library: runtime Error. Abnormal program termination. in System.RuntimeType.ForwardCallToInvokeMember(Strin g memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) in Microsoft.Office.Interop.Excel.Range.set_ColumnWid th(Object ) in MotoroSensorLIST.Form1.function_components_eq_list (_Worksheet objSheet, Int32 id_funzione, String codice_funzione) in C:\Documents and Settings\marco.serioli\Documenti\Visual Studio 2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Fo rm1.cs:riga 1256 in MotoroSensorLIST.Form1.function_eq_list(_Worksheet objSheet) in C:\Documents and Settings\marco.serioli\Documenti\Visual Studio 2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Fo rm1.cs:riga 1080 in MotoroSensorLIST.Form1.eq_list(_Worksheet objSheet) in C:\Documents and Settings\marco.serioli\Documenti\Visual Studio 2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Fo rm1.cs:riga 827 in MotoroSensorLIST.Form1.eq_list_sheet(Int32 index) in C:\Documents and Settings\marco.serioli\Documenti\Visual Studio 2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Fo rm1.cs:riga 819 in MotoroSensorLIST.Form1.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\marco.serioli\Documenti\Visual Studio 2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Fo rm1.cs:riga 64 System.Object ForwardCallToInvokeMember(System.String, System.Reflection.BindingFlags, System.Object, Int32[], System.Runtime.Remoting.Proxies.MessageData ByRef) During debugging application I find that the exception is throw while setting alignment of a cell, but I suppose that it is non deterministic because the same instruction is working fine in the previous iteration. Here is the code that i am using .. ---------------------------------------------------- objApp = new Excel.Application(); objBooks = objApp.Workbooks; objBook = objBooks.Add(Missing.Value); objSheets = objBook.Worksheets; currentRow = 0; Excel.Range range; // populate DataTable function foreach (DataRow dr in function.Rows) { range = objSheet.get_Range("A" + currentRow, "A" + (currentRow+1)); range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders.Weight = Excel.XlBorderWeight.xlMedium; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; range.MergeCells = true; range.Font.Bold = true; range.Font.Size = 12; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.set_Value(Missing.Value, dr["Codice_funzione"]); range.Interior.ColorIndex = 15; range = objSheet.get_Range("B"+currentRow, "K"+ (currentRow+1)); range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders.Weight = Excel.XlBorderWeight.xlMedium; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; range.MergeCells = true; range.Font.Bold = true; range.Font.Size = 12; range.set_Value(Missing.Value, dr["Descrizione"].ToString().ToUpper()); range.Interior.ColorIndex = 15; range = objSheet.get_Range("L" + currentRow, "M" + (currentRow + 1)); range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders.Weight = Excel.XlBorderWeight.xlMedium; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; range.MergeCells = true; range.Font.Bold = true; range.Font.Size = 12; range.set_Value(Missing.Value, dr["Disegno"]); range.Interior.ColorIndex = 15; currentRow += 2; function_components_sensor_list( objSheet, int.Parse(dr["Id_funzione"].ToString()), dr["Codice_funzione"].ToString()); range = objSheet.get_Range("A" + currentRow, "M" + (currentRow + 1)); range.MergeCells = true; currentRow += 2; } ------------------------------------ .....and more similar code Thank you for any suggestion! Marco On Friday, March 20, 2009 5:53 AM mseriol wrote: I think i've found the problem. Too many styles applied at the cells.. Too many borders and background colors.. But I need this kind of cell's format. Does anybody know how to avoid this problem? Submitted via EggHeadCafe Oracle Developer For Beginners http://www.eggheadcafe.com/training-...L-Samples.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Microsoft.Office.Interop.Excel when exporting from another program | Excel Discussion (Misc queries) | |||
Microsoft.Office.Interop.Excel Texttocolumns C# | Excel Programming | |||
Where can I find Microsoft.Office.Interop.Excel.dll? | Excel Programming | |||
COMException With Microsoft.Office.Interop.Excel | Excel Programming | |||
Microsoft.Office.Interop.Excel SaveAs error when clicking no or ca | Excel Programming |