Reading values from an Excel File

Question

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

Answer 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.

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.

However, it is quite impossible to read from an excel spreadsheet without opening the file at all..

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):

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

Answer 2

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).

Answer 3

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

<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.

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;
        }
    }
}