VB.NET 快速向Excel写入大量数据
把数据以字符串的形式保存,不同列之前以Tab符隔开,不同行之间用回车换行符号:
思路是把所有数据在内存中以字符串的形式写入到WINDOWS的粘贴板中,然后一次性粘贴到Excel中。
先添加引用“Microsoft.Office.Interop.Excel”
'先添加引用“Microsoft.Office.Interop.Excel” Private Sub SaveToXls(StrData As String, StrFileName As String) If String.IsNullOrEmpty(StrData) Or String.IsNullOrEmpty(StrFileName) Then Return Else Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application If IsNothing(xlApp) Then MessageBox.Show("无法创建Excel对象,可能您的系统未安装Excel") End If xlApp.DefaultFilePath = "" xlApp.DisplayAlerts = True xlApp.SheetsInNewWorkbook = 1 Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(True) Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = xlBook.Worksheets.Add() System.Windows.Forms.Clipboard.SetDataObject(StrData) xlSheet.Paste() xlBook.SaveAs(StrFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing) '以下代码是结束Excel进程在系统中的占用,否则只用close和Quit,.NET框架并不能实时结束Excel进程。 xlBook.Close() System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet) System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook) xlSheet = Nothing xlBook = Nothing xlApp.Quit() System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) xlApp = Nothing GC.Collect() End If End Sub