本文介绍了Excel导出不带Interop的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试从VB.NET（Windows Forms）应用程序导出到Excel。
不幸的是，我不能使用Office Interops，因为应用程序应该在每台机器上工作 - 即使没有安装Excel。
我在网上找到以下示例：
公开Sub ExportDatasetToExcel（ByVal ds As DataSet，可选ByVal strHeader As String =另存为）
'自豪地复制：
' 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工作簿（* .xls）| * .xls
fileSave.InitialDirectory = Environment.GetFolderPath（Environment.SpecialFolder.Desktop）
fileSave。 Title = strHeader
fileSave.ShowDialog（）
Dim xlsFilePath As String = fileSave.FileName
如果xlsFilePath =然后
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}';&扩展属性='Excel 8.0; HDR = YES;'，xlsFilePath））
conn.Open（）
Dim strTableQ（ds.Tables.Count）As String
Dim i As Integer = 0
'make 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新的OleDb.OleDbCommand（strTableQ（i），conn）
cmd.ExecuteNonQuery（）
下一个
'进行插入查询
Dim strInsertQ .Tables.Count - 1）As String
For i = 0 To ds.Tables.Count - 1
strInsertQ（i）=Insert Into& ds.Tables（i）.TableName& 值（
对于k As Integer = 0到ds.Tables（i）.Columns.Count - 1
strInsertQ（i）& =@& ds.Tables（i）。列（k）.ColumnName&，
Next
strInsertQ（i）= strInsertQ（i）.Substring（0，strInsertQ（i）.Length - 2）
strInsertQ ）
下一个
'现在插入数据
For i = 0 To ds.Tables.Count - 1
对于j As Integer = 0 to ds.Tables（i）.Rows.Count - 1
Dim cmd As New OleDb.OleDbCommand（strInsertQ（i），conn）
对于k As Integer = 0到ds.Tables（i）。 Columns.Count - 1
cmd.Parameters.AddWithValue（@& ds.Tables（i）.Columns（k）.ColumnName.ToString（），ds.Tables（i）.Rows（j）（ k）.ToString（））
下一个
cmd.ExecuteNonQuery（）
cmd.Parameters.Clear（）
下一个
下一个
conn.Close ）
conn.Dispose（）
Cursor.Current = Cursors.Default
End Sub
此代码可以将我的数据集导出到.xls文件。
问题：我的程序运行时无法打开此文件。看来我的程序仍然有一个这个文件的句柄。每当我使用Sysinternals Process Explorer时，我都可以看到它。如果我关闭我的程序，我可以打开这个文件没有任何问题。
我想我必须摧毁一些对象或者只是关闭文件。请问有人可以帮助noob来完成吗？
解决方案
我不知道这是否是问题。你不要处理 OleDbCommand 对象。它可能保留对该文件的引用。尝试这样：
Public Sub ExportDatasetToExcel（ByVal ds As DataSet，可选ByVal strHeader As String =另存为）
'自豪地复制自：
'http://www.daniweb.com/software-development/vbnet/threads/368400/write-into-excel-using-oledb-connection#post1583200
使用fileSave作为新的SaveFileDialog（）
fileSave.Filter =Excel 97-2003工作簿（* .xls）| * .xls
fileSave.InitialDirectory = Environment.GetFolderPath（Environment .SpecialFolder.Desktop）
fileSave.Title = strHeader
如果fileSave.ShowDialog（）= Windows.Forms.DialogResult.OK然后
Dim xlsFilePath As String = fileSave.FileName
如果xlsFilePath =然后退出Sub
System.IO.File.Copy（storagePath&\&empty.xls，xlsFilePath）
Cursor.Current = Cursors.WaitCursor
使用conn作为新的OleDb.OleDbConnection（String.Fo RMAT（提供者= 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
'make 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 ）& =）
使用cmd作为新的OleDb.OleDbCommand （strTableQ（i），conn）
cmd.ExecuteNonQuery（）
结束使用
下一个
'使插入查询
Dim strInsertQ（ds.Tables .Count - 1）As String
For i = 0 To ds.Tables.Count - 1
strInsertQ（i）=Insert Into& ds.Tables（i）.TableName& 值（
对于k As Integer = 0到ds.Tables（i）.Columns.Count - 1
strInsertQ（i）& =@& ds.Tables（i）。列（k）.ColumnName&，
Next
strInsertQ（i）= strInsertQ（i）.Substring（0，strInsertQ（i）.Length - 2）
strInsertQ ）
下一个
'现在插入数据
For i = 0 To ds.Tables.Count - 1
对于j As Integer = 0 ds.Tables（i）.Rows.Count - 1
使用cmd作为新的OleDb.OleDbCommand（strInsertQ（i），conn）
对于k As Integer = 0到ds.Tables（i）。 Columns.Count - 1
cmd.Parameters.AddWithValue（@& ds.Tables（i）.Columns（k）.ColumnName.ToString（），ds.Tables（i）.Rows（j）（ k）.ToString（））
下一个
cmd.ExecuteNonQuer y（）
cmd.Parameters.Clear（）
结束使用
下一个
下一个
conn.Close（）
Cursor.Current = Cursors.Default
结束使用
如果
结束使用
End Sub
另请注意，您显示为 ShowDialog（）方法的表单也必须处理。
I'm trying to accomplish export to Excel from a VB.NET (Windows Forms) application.
Unfortunately, I can't use Office Interops because the application should work on every machine - even if there is no Excel installed.
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.
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.
I think I have to destroy some object or just close the file. Please could anyone help a noob to accomplish it?
解决方案
I don't know if this is the problem, it could. You do not Dispose the OleDbCommand objects. It's possible that it maintains a reference to the file. Try this:
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 form that you display with ShowDialog() method must be disposed too.
这篇关于Excel导出不带Interop的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！