I needed to export 9000 rows from SQL to a xlsx file. I had no choice over the output format, because this was required by another system.
At first I tried automating a xlsx file directly, and outputing the data one cell at a time. This was very slow.
In the end, moved all the data into a CSV string, wrote this to the file system as a CSV file, then used Automation to open this file in Excel 2010, and save it as xlsx.
This was actually a lot harder than one might have expected, especially getting Excel to save in the right format, and to cleanup after itself.
Below is the code that finally worked. A simplified version of this would do the job, but I found all of this is required to cleanup Excel afterwards. Every part of Excel that is used must have its own variable assigned, so that .Net can do its garbage collection successfully, and to leave no orphaned processes running.
Dim Excel As New Microsoft.Office.Interop.Excel.Application
Dim WorkBooks1 As Microsoft.Office.Interop.Excel.Workbooks
Dim WorkBook1 As Microsoft.Office.Interop.Excel.Workbook
WorkBooks1 = Excel.Workbooks
WorkBook1 = WorkBooks1.Open(TempPath)
' XlFileFormat.xlWorkbookDefault = 51 - this is correct, although a lot of forums recommend
WorkBook1.SaveAs(Filename:=TempXLSXPath, FileFormat:=XlFileFormat.xlWorkbookDefault)
'CLEANUP
WorkBook1.Save() 'trying to find a way to get this to close without a prompt
WorkBook1.Close(False)
WorkBooks1.Close()
Excel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBook1)
System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBooks1)
System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
WorkBook1 = Nothing
WorkBooks1 = Nothing
Excel = Nothing
No comments:
Post a Comment