把Excel轉換成DataTable，Excel2003+

在資料處理的時候，我們會Excel（包含2003、2007、2010等）轉換成DataTable，以便進一步操作

1、怎麼訪問Excel檔案呢？我們可以通過OLEDB介面訪問，如下：

        private string GetConStr(string ExcelPath)
        {
            string path = ExcelPath;
            if (!File.Exists(path))
                return null;
string str2 = Path.GetExtension(path).ToLower();
            if ((str2 != ".xls") && (str2 != ".xlsx"))
                 return null;
            string str3 = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + path + "; Extended Properties=Excel 8.0";
            if (str2 == ".xlsx")
                str3 = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" + path + "; Extended Properties=Excel 12.0";
            return str3;
        }

2、讀取Excel的資料到DataTable

        public DataTable ExcelToDataTable(string ExcelPath)
        {
            return ExcelToDataTable(ExcelPath, null);
        }

        public DataTable ExcelToDataTable(string ExcelPath, string SheetName)
        {
            string conStr = GetConStr(ExcelPath);
            if (string.IsNullOrEmpty(conStr))
                return null;
            OleDbConnection connection = new OleDbConnection(conStr);
            connection.Open();
            if (string.IsNullOrEmpty(SheetName))
                  SheetName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString(); 
            else if (!SheetName.Contains("$"))
                  SheetName = SheetName + "$";
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + SheetName + "]", conStr);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, "[" + SheetName + "$]");
            connection.Close();
            return dataSet.Tables[0];
        }