vb.net 没有互操作的 Excel 导出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18623957/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel export without Interop
提问by Alexander
I'm trying to accomplish export to Excel from a VB.NET (Windows Forms) application.
我正在尝试从 VB.NET（Windows 窗体）应用程序完成导出到 Excel。
Unfortunately, I can't use Office Interops because the application should work on every machine - even if there is no Excel installed.
不幸的是，我无法使用 Office Interops，因为该应用程序应该可以在每台机器上运行 - 即使没有安装 Excel。
I found the following sample on the Net:
我在网上找到了以下示例：
Public Sub ExportDatasetToExcel(ByVal ds As DataSet, Optional ByVal strHeader As String = "Save As")
'Proudly copied from:
'http://www.daniweb.com/software-development/vbnet/threads/368400/write-into-excel-using-oledb-connection#post1583200
Dim fileSave As New SaveFileDialog()
fileSave.Filter = "Excel 97-2003 Workbook (*.xls)|*.xls"
fileSave.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
fileSave.Title = strHeader
fileSave.ShowDialog()
Dim xlsFilePath As String = fileSave.FileName
If xlsFilePath = "" Then
Exit Sub
End If
System.IO.File.Copy(storagePath & "\" & "empty.xls", xlsFilePath)
Cursor.Current = Cursors.WaitCursor
Dim conn As New OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';" & "Extended Properties='Excel 8.0;HDR=YES;'", xlsFilePath))
conn.Open()
Dim strTableQ(ds.Tables.Count) As String
Dim i As Integer = 0
'making table query
For i = 0 To ds.Tables.Count - 1
strTableQ(i) = "CREATE TABLE [" & ds.Tables(i).TableName & "]("
Dim j As Integer = 0
For j = 0 To ds.Tables(i).Columns.Count - 1
Dim dCol As DataColumn
dCol = ds.Tables(i).Columns(j)
strTableQ(i) &= " [" & dCol.ColumnName & "] varchar(255) , "
Next
strTableQ(i) = strTableQ(i).Substring(0, strTableQ(i).Length - 2)
strTableQ(i) &= ")"
Dim cmd As New OleDb.OleDbCommand(strTableQ(i), conn)
cmd.ExecuteNonQuery()
Next
'making insert query
Dim strInsertQ(ds.Tables.Count - 1) As String
For i = 0 To ds.Tables.Count - 1
strInsertQ(i) = "Insert Into " & ds.Tables(i).TableName & " Values ("
For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
strInsertQ(i) &= "@" & ds.Tables(i).Columns(k).ColumnName & " , "
Next
strInsertQ(i) = strInsertQ(i).Substring(0, strInsertQ(i).Length - 2)
strInsertQ(i) &= ")"
Next
'Now inserting data
For i = 0 To ds.Tables.Count - 1
For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
Dim cmd As New OleDb.OleDbCommand(strInsertQ(i), conn)
For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
cmd.Parameters.AddWithValue("@" & ds.Tables(i).Columns(k).ColumnName.ToString(), ds.Tables(i).Rows(j)(k).ToString())
Next
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
Next
Next
conn.Close()
conn.Dispose()
Cursor.Current = Cursors.Default
End Sub
This code works and exports my dataset to an .xls file.
此代码有效并将我的数据集导出到 .xls 文件。
The problem: I can't open this file while my program is running. It seems my program is still having a handle on this file. I can see it whenever I use the Sysinternals Process Explorer. If I close my program, I can open this file without any problems.
问题：当我的程序运行时，我无法打开这个文件。看来我的程序仍然可以处理这个文件。每当我使用 Sysinternals Process Explorer 时，我都能看到它。如果我关闭我的程序，我可以毫无问题地打开这个文件。
I think I have to destroy some object or just close the file. Please could anyone help a noob to accomplish it?
我想我必须销毁一些对象或关闭文件。请问有人可以帮助菜鸟完成它吗？
采纳答案by Chris
I don't know if this is the problem, it could. You do not Dispose the OleDbCommandobjects. It's possible that it maintains a reference to the file. Try this:
不知道是不是这个问题，应该可以。您不处置OleDbCommand对象。它可能维护对文件的引用。尝试这个：
Public Sub ExportDatasetToExcel(ByVal ds As DataSet, Optional ByVal strHeader As String = "Save As")
'Proudly copied from:
'http://www.daniweb.com/software-development/vbnet/threads/368400/write-into-excel-using-oledb-connection#post1583200
Using fileSave As New SaveFileDialog()
fileSave.Filter = "Excel 97-2003 Workbook (*.xls)|*.xls"
fileSave.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
fileSave.Title = strHeader
If fileSave.ShowDialog() = Windows.Forms.DialogResult.OK Then
Dim xlsFilePath As String = fileSave.FileName
If xlsFilePath = "" Then Exit Sub
System.IO.File.Copy(storagePath & "\" & "empty.xls", xlsFilePath)
Cursor.Current = Cursors.WaitCursor
Using conn As New OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';" & "Extended Properties='Excel 8.0;HDR=YES;'", xlsFilePath))
conn.Open()
Dim strTableQ(ds.Tables.Count) As String
Dim i As Integer = 0
'making table query
For i = 0 To ds.Tables.Count - 1
strTableQ(i) = "CREATE TABLE [" & ds.Tables(i).TableName & "]("
Dim j As Integer = 0
For j = 0 To ds.Tables(i).Columns.Count - 1
Dim dCol As DataColumn
dCol = ds.Tables(i).Columns(j)
strTableQ(i) &= " [" & dCol.ColumnName & "] varchar(255) , "
Next
strTableQ(i) = strTableQ(i).Substring(0, strTableQ(i).Length - 2)
strTableQ(i) &= ")"
Using cmd As New OleDb.OleDbCommand(strTableQ(i), conn)
cmd.ExecuteNonQuery()
End Using
Next
'making insert query
Dim strInsertQ(ds.Tables.Count - 1) As String
For i = 0 To ds.Tables.Count - 1
strInsertQ(i) = "Insert Into " & ds.Tables(i).TableName & " Values ("
For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
strInsertQ(i) &= "@" & ds.Tables(i).Columns(k).ColumnName & " , "
Next
strInsertQ(i) = strInsertQ(i).Substring(0, strInsertQ(i).Length - 2)
strInsertQ(i) &= ")"
Next
'Now inserting data
For i = 0 To ds.Tables.Count - 1
For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
Using cmd As New OleDb.OleDbCommand(strInsertQ(i), conn)
For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
cmd.Parameters.AddWithValue("@" & ds.Tables(i).Columns(k).ColumnName.ToString(), ds.Tables(i).Rows(j)(k).ToString())
Next
cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
End Using
Next
Next
conn.Close()
Cursor.Current = Cursors.Default
End Using
End If
End Using
End Sub
Also note that a formthat you display with ShowDialog()method must be disposed too.
另请注意，form您使用ShowDialog()方法显示的a也必须被处理。
