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
Here you are an example of what I must obtain:
-->Sample Excel<--
The speed now is the last of my problem, becouse excel crashes before it finishing to generate my file.
Searching on internet I also found this microsoft article..
http://support.microsoft.com/kb/213904/en-us/
But I don't know a way to resolving my problem.
Thank you for any suggestion!
Marco