How to read data from excel file using c#

Question

My application needs to read data from an excel file. I am using .Net and c# for development. I cannot install MS office in the system. Because of that the my application fails to read excel file and throws an error while loading the dll for excel.

How can i access excel file in my application in a system where ms office is not installed?

Answer 1

There is the option to use OleDB and use the Excel sheets like datatables in a database...

Just an example.....

string con =
  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;" + 
  @"Extended Properties='Excel 8.0;HDR=Yes;'";    
using(OleDbConnection connection = new OleDbConnection(con))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection); 
    using(OleDbDataReader dr = command.ExecuteReader())
    {
         while(dr.Read())
         {
             var row1Col0 = dr[0];
             Console.WriteLine(row1Col0);
         }
    }
}

This example use the Microsoft.Jet.OleDb.4.0 provider to open and read the Excel file. However, if the file is of type xlsx (from Excel 2007 and later), then you need to download the Microsoft Access Database Engine components and install it on the target machine.

The provider is called Microsoft.ACE.OLEDB.12.0;. Pay attention to the fact that there are two versions of this component, one for 32bit and one for 64bit. Choose the appropriate one for the bitness of your application and what Office version is installed (if any). There are a lot of quirks to have that driver correctly working for your application. See this question for example.

Of course you don't need Office installed on the target machine.

While this approach has some merits, I think you should pay particular attention to the link signaled by a comment in your question Reading excel files from C#. There are some problems regarding the correct interpretation of the data types and when the length of data, present in a single excel cell, is longer than 255 characters