Microsoft.Office.Interop.Excel very slow and crash after writing a

M

mserioli

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(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)

in Microsoft.Office.Interop.Excel.Range.set_ColumnWidth(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\Form1.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\Form1.cs:riga 1080

in MotoroSensorLIST.Form1.eq_list(_Worksheet objSheet) in C:\Documents and
Settings\marco.serioli\Documenti\Visual Studio
2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Form1.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\Form1.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\Form1.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;
}
 
M

mserioli

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?
 
G

Gary Watson

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?
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(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)

in Microsoft.Office.Interop.Excel.Range.set_ColumnWidth(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\Form1.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\Form1.cs:riga 1080

in MotoroSensorLIST.Form1.eq_list(_Worksheet objSheet) in C:\Documents and
Settings\marco.serioli\Documenti\Visual Studio
2008\Projects\MotoroSensorLIST\MotoroSensorLIST\Form1.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\Form1.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\Form1.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?
Click to expand...
Submitted via EggHeadCafe
Oracle Developer For Beginners
http://www.eggheadcafe.com/training-topic-area/Oracle-SQL-Developer/2/Oracle-SQL-Samples.aspx
Click to expand...
Click to expand...
 
Top