vb.net 将 Excel 工作表导入 datagridview - 是否需要关闭工作簿才能使用 OleDB？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17573476/
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
Import Excel sheet into datagridview - does workbook need to be closed to use OleDB?
提问by Eric J
I'm trying to import an Excel sheet into datagridview, but I'm getting hit with an error: "database or object is read-only." However, the workbook I'm referencing does not have the read-only attribute applied. That being said, the workbook I'm connecting to is already open if my application is running, so I suspect this is the reason I'm getting hit with this error. The workbook is open, thus appears to the system as read-only when trying to fill the dataset.
我正在尝试将 Excel 工作表导入 datagridview，但遇到错误：“数据库或对象是只读的。” 但是，我引用的工作簿没有应用只读属性。话虽如此，如果我的应用程序正在运行，我要连接的工作簿已经打开，所以我怀疑这就是我遇到此错误的原因。工作簿处于打开状态，因此在尝试填充数据集时系统显示为只读。
Am I right in this assumption? Is their a way to import an Excel sheet into datagridview using the OleDB connection if the workbook I'm connecting to is open? If not, is there any other way to populate this datagridview without having to do a massive loop through my sheet? My code is as follows:
我的这个假设是对的吗？如果我连接到的工作簿处于打开状态，他们是否可以使用 OleDB 连接将 Excel 工作表导入到 datagridview 中？如果没有，有没有其他方法可以填充这个 datagridview 而不必在我的工作表中进行大量循环？我的代码如下：
Try
'connect to Excel data source and set gridview equal to dataset (entire sheet should be visible in gridview)
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & StatVar.workbookName & ";Extended Properties=""Excel 12.0;HDR=YES;Readonly=False"";" 'may need to use different MS provider and lower OLEDB for .xls files (Microsoft.Jet.OLEDB4.0...Excel 8.0) kind of sketchy though
MyConnection = New System.Data.OleDb.OleDbConnection(connstring) 'create a new data connection to Excel data source
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Budget$]", MyConnection) 'query the sheet - select all data
MyCommand.TableMappings.Add("Table", "Table") 'map data selection as table
StatVar.DtSet1 = New System.Data.DataSet 'create new data set
MyCommand.Fill(StatVar.DtSet1) 'fill data set with table
Form14.DataGridView1.DataSource = StatVar.DtSet1.Tables(0) 'populate gridview with data set table
MyConnection.Close()
Form14.ShowDialog()
Catch exc As Exception
MessageBox.Show("There was a problem loading this database. Please contact an administrator if the problem continues." & vbNewLine & vbNewLine & "Error: " & exc.Message)
End Try
I'm getting hit with the error here:
我在这里遇到错误：
MyCommand.Fill(StatVar.DtSet1)
采纳答案by D_Bester
Did you try changing the connstring; set ReadOnly=True
您是否尝试过更改连接字符串；设置只读=真
If that doesn't work, you can make a copy of the workbook and then query that copy. If you do that it gets you the workbook as it was last saved. If that is a problem, you can do a save on the open workbook before copying.
如果这不起作用，您可以制作工作簿的副本，然后查询该副本。如果您这样做，它会为您提供上次保存的工作簿。如果这是一个问题，您可以在复制之前对打开的工作簿进行保存。
Or better, simply ask the user to close the workbook before running the code.
或者更好的是，在运行代码之前简单地要求用户关闭工作簿。
回答by Eric J
Try
Cursor = Cursors.WaitCursor
'can't populate gridview with an open file using OLEDB - need to export the Budget sheet to a new file, close it, connect with OLEDB, then delete the temp Budget.xls file just created
'copy Budget sheet to new workbook, delete unneeded columns, save file as Budget.xls, close workbook
Dim filenm = "W:\TOM\ERIC\Budget Temp\Budget.xls"
StatVar.xlApp.Sheets("Budget").Copy()
StatVar.xlApp.ActiveWorkbook.Sheets("Budget").Columns("C:DY").Delete()
StatVar.xlApp.ActiveWorkbook.SaveAs("W:\TOM\ERIC\Budget Temp\Budget.xls")
StatVar.xlApp.ActiveWorkbook.Close(True)
'connect to Excel data source and set gridview equal to dataset (entire sheet should be visible in gridview)
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & filenm & ";Extended Properties=""Excel 12.0;HDR=YES;Readonly=False"";" 'may need to use different MS provider and lower OLEDB for .xls files (Microsoft.Jet.OLEDB4.0...Excel 8.0) kind of sketchy though
MyConnection = New System.Data.OleDb.OleDbConnection(connstring) 'create a new data connection to Excel data source
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Budget$A:F]", MyConnection) 'query the sheet - select all data in columns A:F
MyCommand.TableMappings.Add("Table", "Table") 'map data selection as table
StatVar.DtSet1 = New System.Data.DataSet 'create new data set
MyCommand.Fill(StatVar.DtSet1) 'fill data set with table
Form14.DataGridView1.DataSource = StatVar.DtSet1.Tables(0) 'populate gridview with data set table
MyConnection.Close()
'delete temporary Budget.xls file created at beginning of procedure and show Budget Codes form
File.Delete(filenm)
Form14.TxtBoxAutoCode.Text = StatVar.xlApp.Sheets("Budget").Range("EU2").Text
Form14.ShowDialog()
Catch exc As Exception
MessageBox.Show("There was a problem loading this database. Please contact an administrator if the problem continues." & vbNewLine & vbNewLine & "Error: " & exc.Message)
Finally
Cursor = Cursors.Default
End Try
