Reading Excel files from C#

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.


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

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$]


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

链接地址: http://www.djcxy.com/p/35684.html

上一篇: 使用excel.interop替换excel中的单元格值

下一篇: 从C#读取Excel文件