读取Excel数据

Excel2007的连接字串与Excel2003的不同。

DataTable tbSheetName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
用来获得sheet的名字

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace ExcelDataReader
{
    public class ExcelDataReader
    {
        private static string BuildConnectionString(string filepath)
        {
            if (!File.Exists(filepath))
            {
                throw new FileNotFoundException("Cannot find file" + filepath);
            }

            switch (Path.GetExtension(filepath))
            {
                case ".xls":
                    return "Provider=Microsoft.Jet.OleDb.4.0;Data Source='" + filepath + "';Extended Properties='Excel 8.0;HDR=YES'";
                case ".xlsx":
                    return "Provider=Microsoft.Ace.OleDb.12.0;Data Source='" + filepath + "';Extended Properties='Excel 12.0;HDR=YES'";
                default:
                    throw new NotSupportedException("The file is not a valid excel file");
            }
        }

        public static DataSet Read(string filepath)
        {
            DataSet dataset = new DataSet();

            string connect = BuildConnectionString(filepath);

            string query = string.Empty;

            using (OleDbConnection connection = new OleDbConnection(connect))
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                DataTable tbSheetName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (tbSheetName != null && tbSheetName.Rows.Count > 0)
                {
                    foreach (DataRow row in tbSheetName.Rows)
                    {
                        string sheetname = row["TABLE_NAME"].ToString();

                        if (sheetname.EndsWith("$"))
                        {
                            sheetname = sheetname.TrimEnd('$');
                        }

                        query = string.Format("Select * from [{0}$]", sheetname);

                        using (OleDbDataAdapter adp = new OleDbDataAdapter(query, connection))
                        {
                            DataTable table = new DataTable(sheetname);
                            adp.Fill(table);

                            dataset.Tables.Add(table);
                        }
                    }
                }
            }

            return dataset;
        }
    }
}