从Excel文件中读取值

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文件的情况下读取值，请发布示例代码。谢谢

5 个解决方案

#1

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 Automation来执行此操作。困难的方法是创建一个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):

这里有一段代码可以用来从VB.NET打开电子表格，利用Office Automation（它仍然打开文件，依赖于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

#2

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.OleDbConnection to read values from the data source. For example, consider an Excel file that has a sheet named Users, with two columns, UserName and 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;
        }
    }
}

#3

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主互操作程序集（请参阅此处）。

#4

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

#5

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连接;我已经将它用于访问，它们工作得很好，excel可以用作数据库;我只是不知道如果其中一些文件没有数据库样式数组会发生什么（字段在顶部，值会下降）？

#1