讀取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;        }    }}