How to read data from excel file using C #

Question

My application should read data from an excel file. I use .Net and C # for development. I can not install MS-office in the system. Because of this, my application cannot read the excel file and throws an error when loading the dll for excel.

How can I access the excel file in my application on a system where ms is not installed?

Answer 1

It is possible to use OleDB and use Excel worksheets, such as data 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 uses the Microsoft.Jet.OleDb.4.0 provider to open and read an Excel file. However, if the file is of the xlsx type (from Excel 2007 and later), 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; . Please note that there are two versions of this component: one for 32-bit and one for 64-bit. Choose the appropriate version for your application bits and Office (if any). There are many quirks for this driver to work correctly for your application. See this question for example .

Of course, you do not need Office installed on the target machine.

Although this approach has some advantages, I think you should pay special attention to the link that the comment in your question on reading Excel files from C # reports. There are some problems regarding the correct interpretation of data types, and when the length of the data present in one excel cell is longer than 255 characters

Answer 2

CSharpJExcel for reading Excel 97-2003 files (xls): http://sourceforge.net/projects/jexcelapi/

and ExcelPackage for reading Excel 2007/2010 files (Office Open XML format, xlsx): http://excelpackage.codeplex.com/

and ExcelDataReader, which seems to be able to handle both formats: https://github.com/ExcelDataReader/ExcelDataReader

Good luck

Answer 3

I do not have a testing machine, but it should work. First, you probably need to install either the "Office 2007 System Driver: Data Connectivity Components" or the Redistributable Microsoft Access Database Engine 2010 . Then try the following code, note that you will need to change the sheet name in the Select statement below to match the sheet name 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 for checking this in (one with Office installed), so I can’t say whether it will work in your environment or not, but I don’t understand why it should not work.

Answer 4

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

Answer 5

Convert the excell file to a .csv file - a comma-separated value file and you can easily read it.