Reading Excel files from C#

C# Problem Overview

Is there a free or open source library to read Excel files (.xls) directly from a C# program?

It does not need to be too fancy, just to select a worksheet and read the data as strings. So far, I've been using Export to Unicode text function of Excel, and parsing the resulting (tab-delimited) file, but I'd like to eliminate the manual step.

C# Solutions

Solution 1 - C#

var fileName = string.Format("{0}\\fileNameHere", Directory.GetCurrentDirectory());
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [workSheetNameHere$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds, "anyNameHere");

DataTable data = ds.Tables["anyNameHere"];

This is what I usually use. It is a little different because I usually stick a AsEnumerable() at the edit of the tables:

var data = ds.Tables["anyNameHere"].AsEnumerable();

as this lets me use LINQ to search and build structs from the fields.

var query = data.Where(x => x.Field<string>("phoneNumber") != string.Empty).Select(x =>
				new MyContact
					{
						firstName= x.Field<string>("First Name"),
						lastName = x.Field<string>("Last Name"),
						phoneNumber =x.Field<string>("Phone Number"),
					});

Solution 2 - C#

If it is just simple data contained in the Excel file you can read the data via ADO.NET. See the connection strings listed here:

http://www.connectionstrings.com/?carrier=excel2007 or http://www.connectionstrings.com/?carrier=excel

-Ryan

Update: then you can just read the worksheet via something like select * from [Sheet1$]

Solution 3 - C#

The ADO.NET approach is quick and easy, but it has a few quirks which you should be aware of, especially regarding how DataTypes are handled.

This excellent article will help you avoid some common pitfalls: http://blog.lab49.com/archives/196

Solution 4 - C#

This is what I used for Excel 2003:

Dictionary<string, string> props = new Dictionary<string, string>();
props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
props["Data Source"] = repFile;
props["Extended Properties"] = "Excel 8.0";

StringBuilder sb = new StringBuilder();
foreach (KeyValuePair<string, string> prop in props)
{
    sb.Append(prop.Key);
    sb.Append('=');
    sb.Append(prop.Value);
    sb.Append(';');
}
string properties = sb.ToString();

using (OleDbConnection conn = new OleDbConnection(properties))
{
    conn.Open();
    DataSet ds = new DataSet();
    string columns = String.Join(",", columnNames.ToArray());
    using (OleDbDataAdapter da = new OleDbDataAdapter(
        "SELECT " + columns + " FROM [" + worksheet + "$]", conn))
    {
        DataTable dt = new DataTable(tableName);
        da.Fill(dt);
        ds.Tables.Add(dt);
    }
}

Solution 5 - C#

How about Excel Data Reader?

http://exceldatareader.codeplex.com/

I've used in it anger, in a production environment, to pull large amounts of data from a variety of Excel files into SQL Server Compact. It works very well and it's rather robust.

Solution 6 - C#

Here's some code I wrote in C# using .NET 1.1 a few years ago. Not sure if this would be exactly what you need (and may not be my best code :)).

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

namespace ExportExcelToAccess
{
	/// <summary>
	/// Summary description for ExcelHelper.
	/// </summary>
	public sealed class ExcelHelper
	{
		private const string CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FILENAME>;Extended Properties=\"Excel 8.0;HDR=Yes;\";";

		public static DataTable GetDataTableFromExcelFile(string fullFileName, ref string sheetName)
		{
			OleDbConnection objConnection = new OleDbConnection();
			objConnection = new OleDbConnection(CONNECTION_STRING.Replace("<FILENAME>", fullFileName));
			DataSet dsImport = new DataSet();

			try
			{
				objConnection.Open();
			
				DataTable dtSchema = objConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

				if( (null == dtSchema) || ( dtSchema.Rows.Count <= 0 ) )
				{
					//raise exception if needed
				}
				
				if( (null != sheetName) && (0 != sheetName.Length))
				{
					if( !CheckIfSheetNameExists(sheetName, dtSchema) )
					{
						//raise exception if needed
					}
				}
				else
				{
					//Reading the first sheet name from the Excel file.
					sheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
				}
				
				new OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", objConnection ).Fill(dsImport);
			}
			catch (Exception)
			{
				//raise exception if needed
			}
			finally
			{
				// Clean up.
				if(objConnection != null)
				{
					objConnection.Close();
					objConnection.Dispose();
				}
			}

			
			return dsImport.Tables[0];
			#region Commented code for importing data from CSV file.
			//				string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + System.IO.Path.GetDirectoryName(fullFileName) +";" +"Extended Properties=\"Text;HDR=YES;FMT=Delimited\"";
			//
			//				System.Data.OleDb.OleDbConnection conText = new System.Data.OleDb.OleDbConnection(strConnectionString);
			//				new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + System.IO.Path.GetFileName(fullFileName).Replace(".", "#"), conText).Fill(dsImport);
			//				return dsImport.Tables[0];

			#endregion
		}

		/// <summary>
		/// This method checks if the user entered sheetName exists in the Schema Table
		/// </summary>
		/// <param name="sheetName">Sheet name to be verified</param>
		/// <param name="dtSchema">schema table </param>
		private static bool CheckIfSheetNameExists(string sheetName, DataTable dtSchema)
		{
			foreach(DataRow dataRow in dtSchema.Rows)
			{
				if( sheetName == dataRow["TABLE_NAME"].ToString() )
				{
					return true;
				}	
			}
			return false;
		}
	}
}

Solution 7 - C#

Koogra is an open-source component written in C# that reads and writes Excel files.

Solution 8 - C#

While you did specifically ask for .xls, implying the older file formats, for the OpenXML formats (e.g. xlsx) I highly recommend the OpenXML SDK (http://msdn.microsoft.com/en-us/library/bb448854.aspx)

Solution 9 - C#

I did a lot of reading from Excel files in C# a while ago, and we used two approaches:

The COM API, where you access Excel's objects directly and manipulate them through methods and properties
The ODBC driver that allows to use Excel like a database.

The latter approach was much faster: reading a big table with 20 columns and 200 lines would take 30 seconds via COM, and half a second via ODBC. So I would recommend the database approach if all you need is the data.

Cheers,

Carl

Solution 10 - C#

ExcelMapper is an open source tool (http://code.google.com/p/excelmapper/) that can be used to read Excel worksheets as Strongly Typed Objects. It supports both xls and xlsx formats.

Solution 11 - C#

I want to show a simple method to read xls/xlsx file with .NET. I hope that the following will be helpful for you.

private DataTable ReadExcelToTable(string path)

{
 //Connection String

 string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";  
 //the same name 
 //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + //";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; 

 using(OleDbConnection conn = new OleDbConnection(connstring))
 {
    conn.Open();
    //Get All Sheets Name
    DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"Table"});  

    //Get the First Sheet Name
    string firstSheetName = sheetsName.Rows[0][2].ToString(); 

    //Query String 
    string sql = string.Format("SELECT * FROM [{0}]",firstSheetName); 
    OleDbDataAdapter ada =new OleDbDataAdapter(sql,connstring);
    DataSet set = new DataSet();
    ada.Fill(set);
    return set.Tables[0];   

}
}

Code is from article: http://www.c-sharpcorner.com/uploadfile/d2dcfc/read-excel-file-with-net/. You can get more details from it.

Solution 12 - C#

Not free, but with the latest Office there's a very nice automation .Net API. (there has been an API for a long while but was nasty COM) You can do everything you want / need in code all while the Office app remains a hidden background process.

Solution 13 - C#

Forgive me if I am off-base here, but isn't this what the Office PIA's are for?

Solution 14 - C#

Lately, partly to get better at LINQ.... I've been using Excel's automation API to save the file as XML Spreadsheet and then get process that file using LINQ to XML.

Solution 15 - C#

http://www.spreadsheetgear.com/">SpreadsheetGear for .NET is an Excel compatible spreadsheet component for .NET. You can see what our customers say about performance on the right hand side of our http://www.spreadsheetgear.com/products/spreadsheetgear.net.aspx">product page. You can try it yourself with the free, fully-functional https://www.spreadsheetgear.com/downloads/register.aspx">evaluation</a>;.

Solution 16 - C#

SmartXLS is another excel spreadsheet component which support most features of excel Charts,formulas engines, and can read/write the excel2007 openxml format.

Solution 17 - C#

The .NET component Excel Reader .NET may satisfy your requirement. It's good enought for reading XLSX and XLS files. So try it from:

> http://www.devtriogroup.com/ExcelReader

Solution 18 - C#

I recommend the FileHelpers Library which is a free and easy to use .NET library to import/export data from EXCEL, fixed length or delimited records in files, strings or streams + More.

The Excel Data Link Documentation Section http://filehelpers.sourceforge.net/example_exceldatalink.html

Solution 19 - C#

You can try using this open source solution that makes dealing with Excel a lot more cleaner.

http://excelwrapperdotnet.codeplex.com/

Solution 20 - C#

SpreadsheetGear is awesome. Yes it's an expense, but compared to twiddling with these other solutions, it's worth the cost. It is fast, reliable, very comprehensive, and I have to say after using this product in my fulltime software job for over a year and a half, their customer support is fantastic!

Solution 21 - C#

The solution that we used, needed to:

Allow Reading/Writing of Excel produced files
Be Fast in performance (not like using COMs)
Be MS Office Independent (needed to be usable without clients having MS Office installed)
Be Free or Open Source (but actively developed)

There are several choices, but we found NPoi (.NET port of Java's long existing Poi open source project) to be the best: http://npoi.codeplex.com/

It also allows working with .doc and .ppt file formats

Solution 22 - C#

If it's just tabular data. I would recommend file data helpers by Marcos Melli which can be downloaded here.

Solution 23 - C#

Late to the party, but I'm a fan of LinqToExcel

Solution 24 - C#

you could write an excel spreadsheet that loads a given excel spreadsheet and saves it as csv (rather than doing it manually).

then you could automate that from c#.

and once its in csv, the c# program can grok that.

(also, if someone asks you to program in excel, it's best to pretend you don't know how)

(edit: ah yes, rob and ryan are both right)

Solution 25 - C#

I know that people have been making an Excel "extension" for this purpose.
You more or less make a button in Excel that says "Export to Program X", and then export and send off the data in a format the program can read.

<http://msdn.microsoft.com/en-us/library/ms186213.aspx> should be a good place to start.

Good luck

Solution 26 - C#

Just did a quick demo project that required managing some excel files. The .NET component from GemBox software was adequate for my needs. It has a free version with a few limitations.

http://www.gemboxsoftware.com/GBSpreadsheet.htm

Solution 27 - C#

Excel Package is an open-source (GPL) component for reading/writing Excel 2007 files. I used it on a small project, and the API is straightforward. Works with XLSX only (Excel 200&), not with XLS.

The source code also seems well-organized and easy to get around (if you need to expand functionality or fix minor issues as I did).

At first, I tried the ADO.Net (Excel connection string) approach, but it was fraught with nasty hacks -- for instance if second row contains a number, it will return ints for all fields in the column below and quietly drop any data that doesn't fit.

Solution 28 - C#

Take.io Spreadsheet will do this work for you, and at no charge. Just take a look at this.

Solution 29 - C#

We use ClosedXML in rather large systems.

Free
Easy to install
Straight forward coding
Very responsive support
Developer team is extremly open to new suggestions. Often new features and bug fixes are implemented within the same week

Solution 30 - C#

I just used ExcelLibrary to load an .xls spreadsheet into a DataSet. Worked great for me.

Solution 31 - C#

Excel Data Reader is the way to go!

It´s Open Source, at http://exceldatareader.codeplex.com/ and actively developed.

We been using it for reading Tabular (and sometimes not so tabular) worksheets for a couple of years now (In a financial application).

Works like a charm to read unit test data from human-readable sheets.

Just avoid the feature of trying to return DateTime's, as, for Excel, DateTime's are just double numbers.

Solution 32 - C#

If you have multiple tables in the same worksheet you can give each table an object name and read the table using the OleDb method as shown here: http://vbktech.wordpress.com/2011/05/10/c-net-reading-and-writing-to-multiple-tables-in-the-same-microsoft-excel-worksheet/

Content Type	Original Author	Original Content on Stackoverflow
Question	dbkk	View Question on Stackoverflow
Solution 1 - C#	Robin Robinson	View Answer on Stackoverflow
Solution 2 - C#	Ryan Farley	View Answer on Stackoverflow
Solution 3 - C#	Ian Nelson	View Answer on Stackoverflow
Solution 4 - C#	Dmitry Shechtman	View Answer on Stackoverflow
Solution 5 - C#	Michał Pawłowski	View Answer on Stackoverflow
Solution 6 - C#	hitec	View Answer on Stackoverflow
Solution 7 - C#	Rune Grimstad	View Answer on Stackoverflow
Solution 8 - C#	Hafthor	View Answer on Stackoverflow
Solution 9 - C#	Carl Seleborg	View Answer on Stackoverflow
Solution 10 - C#	anonymous	View Answer on Stackoverflow
Solution 11 - C#	Lizzy	View Answer on Stackoverflow
Solution 12 - C#	xanadont	View Answer on Stackoverflow
Solution 13 - C#	Rob Cooper	View Answer on Stackoverflow
Solution 14 - C#	kenny	View Answer on Stackoverflow
Solution 15 - C#	Joe Erickson	View Answer on Stackoverflow
Solution 16 - C#	liya	View Answer on Stackoverflow
Solution 17 - C#	Bonnie Cornell	View Answer on Stackoverflow
Solution 18 - C#	Jason Von Ruden	View Answer on Stackoverflow
Solution 19 - C#	user289261	View Answer on Stackoverflow
Solution 20 - C#	John R	View Answer on Stackoverflow
Solution 21 - C#	Marcel Toth	View Answer on Stackoverflow
Solution 22 - C#	cless	View Answer on Stackoverflow
Solution 23 - C#	DeeDee	View Answer on Stackoverflow
Solution 24 - C#	Leon Bambrick	View Answer on Stackoverflow
Solution 25 - C#	Lars Mæhlum	View Answer on Stackoverflow
Solution 26 - C#	Christian Hagelid	View Answer on Stackoverflow
Solution 27 - C#	dbkk	View Answer on Stackoverflow
Solution 28 - C#	Balena	View Answer on Stackoverflow
Solution 29 - C#	Doctor Rudolf	View Answer on Stackoverflow
Solution 30 - C#	Hafthor	View Answer on Stackoverflow
Solution 31 - C#	JP Negri	View Answer on Stackoverflow
Solution 32 - C#	VBK	View Answer on Stackoverflow