Reading Excel files from C#

Question

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.

Answer 1

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 product page. You can try it yourself with the free, fully-functional evaluation.

Answer 2

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

Answer 3

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"),
                    });

Answer 4

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

Answer 5

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

Answer 6

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