vb.net 从 Excel 文件中读取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2118061/
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
Reading values from an Excel File
提问by reggie
I want to get a value from 12 excel sheet. is there any way that i get the values without opening the excel sheet? I am using vb.net. Please post an example code, if there is a way to read values without opening the excel file. thanks
我想从 12 个 excel 表中获取一个值。有什么方法可以在不打开excel表的情况下获取值？我正在使用 vb.net。请发布示例代码，如果有一种方法可以在不打开 excel 文件的情况下读取值。谢谢
采纳答案by Mike Dinescu
You can't read the values without opening the Excel file at all. But you may read the values without having to open Excel.
您根本无法在不打开 Excel 文件的情况下读取这些值。但您无需打开 Excel 即可读取这些值。
If the file is saved in the xml format it's going to be easier. If not, the easiest method is to still use Excel but use Office Automation to do it. The hard way is to create an excel file parser - quite hard on the non-open xml excel format (pre Office 2003) - hard but still possible.
如果文件以 xml 格式保存，它会更容易。如果没有，最简单的方法是仍然使用 Excel，但使用 Office 自动化来完成。困难的方法是创建一个 excel 文件解析器 - 在非开放的 xml excel 格式（Office 2003 之前）上非常困难 - 很难但仍然可能。
However, it is quite impossible to read from an excel spreadsheet without opening the file at all..
但是，根本不可能在不打开文件的情况下从 Excel 电子表格中读取数据。
Here's a snippet of code you could use to open a spreadsheet from VB.NET, by leveraging Office Automation (it still opens the file, an relies on Excel automation dlls, but doesn't require opening Excel):
下面是一段代码，您可以使用 Office 自动化从 VB.NET 打开电子表格（它仍然打开文件，依赖于 Excel 自动化 dll，但不需要打开 Excel）：
DISCLAIMER
免责声明
The following code is not intended to be used as is, but merely it is a sample to guide the reader to their own solution which should be thoroughly tested.
以下代码不打算按原样使用，而只是一个示例，可引导读者找到自己的解决方案，应该对其进行彻底测试。
' The code below requires you to add references to Office Interop assemblies
' into your VB.NET project (if you don't know how to do that search Google)
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open("<YOUR EXCEL SPREADSHEET FILE HERE")
xlWorkSheet = xlWorkBook.Worksheets("sheet1")
range = xlWorkSheet.UsedRange
For rCnt = 1 To range.Rows.Count
For cCnt = 1 To range.Columns.Count
Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
' Obj.value now contains the value in the cell..
Next
Next
回答by Alexander Gro?
You can use ADO.NET to read values from an Excel sheet. For more information on the connection string, see http://www.connectionstrings.com/excel-2007
您可以使用 ADO.NET 从 Excel 工作表中读取值。有关连接字符串的更多信息，请参阅http://www.connectionstrings.com/excel-2007
<connectionStrings>
<add name="Default"
connectionString='Microsoft.ACE.OLEDB.12.0;Data Source=c:\your\folder\file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";'
providerName="System.Data.OleDb" />
</connectionStrings>
You can then use a standard System.Data.OleDb.OleDbConnectionto read values from the data source. For example, consider an Excel file that has a sheet named Users, with two columns, UserNameand Age.
然后，您可以使用标准System.Data.OleDb.OleDbConnection从数据源读取值。例如，考虑一个 Excel 文件，它有一个名为 的工作表Users，有两列，UserName和Age.
using System.Data;
using System.Data.Common;
public int UserExists(string userName, int age)
{
var provider = ConfigurationManager.ConnectionStrings["Default"].ProviderName;
var factory = DbProviderFactories.GetFactory(provider);
var connectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
using (var connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand command = connection.CreateCommand())
{
DbParameter userNameParameter = factory.CreateParameter();
userNameParameter.ParameterName = "@UserName";
userNameParameter.DbType = DbType.String;
userNameParameter.Direction = ParameterDirection.Input;
userNameParameter.IsNullable = false;
userNameParameter.Value = userName;
DbParameter ageParameter = factory.CreateParameter();
ageParameter.ParameterName = "@Age";
ageParameter.DbType = DbType.Int32;
ageParameter.Direction = ParameterDirection.Input;
ageParameter.IsNullable = false;
ageParameter.Value = age;
command.CommandText = "SELECT COUNT(*) FROM [Users$] WHERE UserName=@UserName AND Age=@Age";
command.Parameters.Add(userNameParameter);
command.Parameters.Add(ageParameter);
connection.Open();
int usersExits = (int) command.ExecuteScalar();
return usersExits == 1;
}
}
}
回答by ketan italiya
use this code for that,
为此使用此代码，
DimobjEXCELCon As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=EXCLE_FILE_PATH;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
ExcelConnection.Open()
Dim objQuery As String = "SELECT * FROM [Sheet1$]" 'get values from sheet1, here you can change your sheet name
Dim objCMD As OleDbCommand = New OleDbCommand(objQuery,objEXCELCon)
Dim objDR As OleDbDataReader
Dim SQLconn As New SqlConnection()
Dim szCON As String = "Connection string for database"
SQLconn.ConnectionString = szCON
SQLconn.Open()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLConn)
bulkCopy.DestinationTableName = "TableToWriteToInSQLSERVER"
Try
objDR = objCMD.ExecuteReader
bulCopy.WriteToServer(objDR)
objDR.Close()
SQLConn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
回答by Jose V
One way is to create an excel application object and set visible = false, then open the excel. I don't know if you are looking for something increase speed or just to avoid having the user see the open and close excel files. I've used this and it works. I'm thinking about using the ADO connections; I've used this with access and they work great, and excel can be used as a database; I just don't know what happens if some of these files don't have the database style array (fields on top, values going down)??
一种方法是创建一个excel应用程序对象并设置visible = false，然后打开excel。我不知道您是在寻找提高速度的方法还是只是为了避免让用户看到打开和关闭的 excel 文件。我用过这个并且它有效。我正在考虑使用 ADO 连接；我已经在 access 中使用了它，它们运行良好，而且 excel 可以用作数据库；我只是不知道如果其中一些文件没有数据库样式数组（顶部的字段，值下降）会发生什么？？
回答by TLiebe
I don't know of any way to get a value from an Excel spreadsheet without actually opening it but you can access the spreadsheet without having Office installed if that is the problem you are having. Have a look at using the Office primary interop assemblies (see here).
我不知道有什么方法可以在不实际打开 Excel 电子表格的情况下从它获取值，但是如果这是您遇到的问题，您可以在不安装 Office 的情况下访问该电子表格。查看使用 Office 主互操作程序集（请参阅此处）。
