StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The GC.Collect makes not much sense where you placed it, if anything you should call it <em>after you return</em> from <code>converToExcel</code>. Also you may need to wait for finalizers to run. Personally I think Hans' answer is the way to go, but I know from personal experience writing office addins in C# that sometimes its necessary to do manual reference counting, in particular when you need to be compatible with older office versions. (There are many documented problems, in particular when handling events from office, which can only be reliably solved by manual reference counting. Also some COM libraries don't like at all when released in the wrong order by GC, but thats not the case with office.)</p> <p>So on to the actual problem in your code: there are three intermediate COM objects not released here:</p> <ul> <li><code>xlWorkBook.Sheets</code> returns a collection of type <code>Excel.Sheets</code></li> <li><code>xlWorkSheet.Columns</code> returns a COM object of type <code>Excel.Range</code></li> <li><code>xlWorkSheet.Cells</code> also returns an <code>Excel.Range</code> object</li> </ul> <p>Besides this, if Marshal.ReleaseComObject throws an exception you did something wrong in your manual reference counting, therefore I wouldn't wrap it in an exception handler. When doing manual reference counting you must release every COM object once for every time it crosses the COM->NET boundary, meaning the <code>Excel.Range</code> objects need to be released in every iteration of the loop.</p> <p>Here's code which properly terminates Excel for me:</p> <pre><code>Imports Microsoft.Office.Interop Imports System.Runtime.InteropServices Private Sub converToExcel(fileLoc As String, ds As DataSet) Dim xlApp As New Excel.Application Dim xlWorkBooks As Excel.Workbooks = xlApp.Workbooks Dim xlWorkBook As Excel.Workbook = xlWorkBooks.Add(System.Reflection.Missing.Value) Dim xlWorkSheets As Excel.Sheets = xlWorkBook.Sheets ' accessing the sheet by index because name is localized and your code will fail in non-english office versions Dim xlWorkSheet As Excel.Worksheet = xlWorkSheets(1) For i = 0 To ds.Tables(0).Rows.Count - 1 For j = 0 To ds.Tables(0).Columns.Count - 1 ' couldn't this be moved outside the loop? Dim xlColumns As Excel.Range = xlWorkSheet.Columns xlColumns.NumberFormat = "@" Marshal.ReleaseComObject(xlColumns) Dim xlCells As Excel.Range = xlWorkSheet.Cells xlCells(i + 1, j + 1) = ds.Tables(0).Rows(i).Item(j).ToString() Marshal.ReleaseComObject(xlCells) Next Next xlWorkSheet.SaveAs(fileLoc) 'xlWorkBook.Close() -- not really necessary xlApp.Quit() Marshal.ReleaseComObject(xlWorkSheet) Marshal.ReleaseComObject(xlWorkSheets) Marshal.ReleaseComObject(xlWorkBook) Marshal.ReleaseComObject(xlWorkBooks) Marshal.ReleaseComObject(xlApp) End Sub </code></pre> <p>If you want to be extra careful you'd want to handle exceptions from the office API and call ReleaseComObject inside finally-clauses. It can be helpful to define a generic wrapper and write using-clauses instead of try-finally (make the wrapper a structure not a class so you don't allocate the wrappers on the heap).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload