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
|