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?

Solution 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

Solution 2

CSharpJExcel for reading Excel 97-2003 files (XLS), ExcelPackage for reading Excel 2007/2010 files (Office Open XML format, XLSX), and ExcelDataReader that seems to have the ability to handle both formats

Good luck!

Solution 3

Save the Excel file to CSV, and read the resulting file with C# using a CSV reader library like FileHelpers.

Solution 4

I don't have a machine available to test this but it should work. First you will probably need to install the either the 2007 Office System Driver: Data Connectivity Components or the Microsoft Access Database Engine 2010 Redistributable. Then try the following code, note you will need to change the name of the Sheet in the Select statement below to match sheetname in your excel file:

using System.Data;
using System.Data.OleDb;

namespace Data_Migration_Process_Creator
{
    class Class1
    {
        private DataTable GetDataTable(string sql, string connectionString)
        {
            DataTable dt = null;

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    using (OleDbDataReader rdr = cmd.ExecuteReader())
                    {
                        dt.Load(rdr);
                        return dt;
                    }
                }
            }
        }

        private void GetExcel()
        {
            string fullPathToExcel = "<Path to Excel file>"; //ie C:\Temp\YourExcel.xls
            string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=yes'", fullPathToExcel);
            DataTable dt = GetDataTable("SELECT * from [SheetName$]", connString);

            foreach (DataRow dr in dt.Rows)
            {
                //Do what you need to do with your data here
            }
        }
    }
}

Note: I don't have an environment to test this in (One with Office installed) so I can't say if it will work in your environment or not but I don't see why it shouldn't work.

Solution 5

Convert the excel file to .csv file (comma separated value file) and now you can easily be able to read it.

View more solutions

Comments

[Tim]

TextFieldParser Class in Microsoft.VisualBasic.FileIO assembly is included with .NET and is very good with CSV files, IMO.

[Mark Kram]

Here is something I have not heard of before: code.google.com/p/linqtoexcel

[Michael Silver]

ExcelDataReader appears to be the most recently maintained. It's also available via NuGet. I've had much luck with it.

[Manvinder]

For any future users, give a try to ExcelDataReader. It is a very good option especially if you build your application with Platform Target 'AnyCpu'. Otherwise the pain of installing x86 or x64 on client machines is a big pain.

[Pavan]

Linq to Excel helps a lot. thanks

[Bengi Besçeli]

I get an exception with the code. What is Sheet1$ ?

[Steve]

It is the name of a worksheet in your excel file. Each sheet is considered as a different table with the name of the sheet and with a $ suffix. Of course, if you change the sheet name you need to change the tablename (sheet1$) used above

[atheaos]

Saving to CSV will result in the file having formatted values, rather than the underlying values (for example, rounded to two decimal places when the full precision is 6). This could be a problem depending on your data needs.

[bjan]

My Excel file contains a merged cell having multiline text. This approach reads only part of the text i.e. upto middle of the 7th line. Could you please help me?

[Steve]

Post a new question please, in this way you will get a lot more attention to you specific problem, than asking here on a 3 years old question/answer. And remember the 255 chars limit....

[nick]

Sorry to hijack this thread...How to import this excelreader folder in my projecct?? I am new to c# ..please help

[daniele3004]

@Steve it's possible using a sheet index in query select * from [0] ???

[Steve]

I doubt it. Following a FROM there must be a string with the name of the table. Changing this convention to introduce an index of an array seems to be rather inconsistent. However you can try to get the array of sheet (tables) using the GetSchema method of connection and then index that array

[wearybands]

Function_Library.DatabaseFunctions This line give me the following error The object does not contain definition for 'DatabaseFunctions'

[Mark Kram]

@wearybands I just fixed my sample sorry it took me so long to respond.

[RBT]

If you're facing problem to read cells which contain numbers then connection string need to be modified as suggested in this post - OleDbDataReader How to read number types?

[Fractal]

Why isn't anyone mentioning any of the libraries mentioned in this link? nugetmusthaves.com/Tag/Excel

[Steve]

Perhaps because this is a 6 years old answer. Things change, and in any case using Jet OleDb 4.0 doesn't require any other library to distribute, just the framework

[Hazel Patton]

JExcelApi seems to be abandoned and I'm unable to find the version for C# (I found only Java). Also, Chrome is showing security warning for that ExcelPackage link. Anyway, here is fast and efficient Excel reader that I ended up using, you can find it on NuGet.

[Chris HG]

For a 2020 solution can I shamelessly plug ExcelToEnumerable. We've benchmarked it and it's the fastest solution if you only need to support .xlsx files. Disclaimer I'm the author of ExcelToEnumerable.

[Your Dad]

for Excel 2007 and above, please use below conection string else you will get multiple misguiding errors. conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';";

[Vikas Lalwani]

OleDb is good option but it is better to use EPPlus or Interop Take a look at this link Read excel file in C# (.XLSX or .XLS using OLEDB / EPPlus or Interop)