excel檔案匯入（支援2003、2007格式）

#region Excel操作 /// <summary> /// 出入一個excel檔案，獲取檔案的sheetname /// </summary> /// <param name="filepath">excel檔案</param> /// <returns></returns> public static ArrayList GetExcelSheetName(string filepath,string type) { ArrayList al = new ArrayList(); string strConn; if (type == "xls") { strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'"; } else { strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;IMEX=1'"; } OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataTable sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); conn.Close(); foreach (DataRow dr in sheetNames.Rows) { al.Add(dr[2]); } return al; } /// <summary> /// 將excel檔案資料轉換為dataset /// </summary> /// <param name="filepath">excel檔案</param> /// <param name="sheetname">sheetname</param> /// <returns></returns> public static DataSet FillExcelToDataSet(string filepath, string sheetname,string type) { string strConn; if (type == "xls") { strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'"; } else { strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;IMEX=1'"; } OleDbConnection conn = new OleDbConnection(strConn); OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn); DataSet ds = new DataSet(); oada.Fill(ds); return ds; } #endregion

注：