I have recently written a web application that allow people to obtain the Excel Report. Anyway, I use Microsoft.Office.Interop.Excel to implement this. But the speed of generating Excel is not good. It takes me more than one minutes to obtain the report. So, I suspect the reason why the speed is low is many com objects are created every generating report. The following is some of my source:
Is anybody familiar with using this kind of com object? How can I see how many com objects are created when the report is generating? Any suggestion and solutions?
VB.NET:
Dim PagTA As New udv_desp_pag_expense_reportTableAdapter
Dim PagTable As Mapa.udv_desp_pag_expense_reportDataTable = PagTA.GetData(StartDate, EndDate)
For i As Integer = 0 To PagTable.Rows.Count - 1
If Not (oCells.Find(PagTable.Rows(i)(0).ToString()) Is Nothing) Then
oCells(oCells.Find(PagTable.Rows(i)(0).ToString()).Row, 9) = PagTable.Rows(i)(1).ToString()
End If
Next
oBook.SaveAs(Server.MapPath("Result.xls"))
oBook.Close()
oBooks.Close()
oExcel.Quit()
ReleaseComObject(oCells) : ReleaseComObject(DespesaSheet)
ReleaseComObject(oSheets) : ReleaseComObject(oBook)
ReleaseComObject(oBooks) : ReleaseComObject(oExcel)
ReleaseComObject(ReceitaSheet)
oExcel = Nothing : oBooks = Nothing : oBook = Nothing
oSheets = Nothing : DespesaSheet = Nothing : oCells = Nothing
ReceitaSheet = Nothing
System.GC.Collect()
Is anybody familiar with using this kind of com object? How can I see how many com objects are created when the report is generating? Any suggestion and solutions?