Excel变换DataTable

2013-09-05

Excel转换DataTable/// summary/// 根据传入的Excel表输出Datatable/// /summary///param namestrE

Excel转换DataTable

/// <summary>        
        /// 根据传入的Excel表输出Datatable       
        /// </summary>       
        ///  <param name="strExcelFileName">Excel表路径及文件名称</param>       
        /// <param name="strTableName">数据表名</param>       
        ///  <returns>DataTable:mytable</returns>      
        public System.Data.DataTable LeadIn(string strExcelFileName, string strSheetName)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            string strExcelSql = string.Format("select * from [{0}$]", strSheetName);
            System.Data.DataSet ds = new DataSet();
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcelSql, strConn);
                adapter.Fill(ds, "mytable");
                return ds.Tables["mytable"];
            }
            catch (System.Data.OleDb.OleDbException e)
            {
                System.Console.WriteLine("OleDbException:{0}", e);
            }
            finally 
 
            {
                conn.Close();
            }
            ds.Tables.Add("mytable");
            return ds.Tables[0];
        }

2003版不会报错，但是07就会报错。怎么让这段代码适用2007版？
[解决办法]
应该是连接字符串的问题，给你个Excel连接字符串生产的函数参考上
static public string onGetExcelConnStr(E_ExcelVer Par_ExcelVer, string Par_XlsPath, bool Par_Hdr, bool Par_Imex)
        {
            string connStr = "";
            if (Par_ExcelVer == E_ExcelVer.Excel8)
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Par_XlsPath + ";" + "Extended Properties='Excel 8.0;HDR=" + (Par_Hdr ? "Yes" : "No") + ";IMEX=" + (Par_Imex ? "1" : "2") + "'";
            else if (Par_ExcelVer == E_ExcelVer.Excel12)
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Par_XlsPath + ";Extended Properties='Excel 12.0;HDR=" + (Par_Hdr ? "Yes" : "No") + ";IMEX=" + (Par_Imex ? "1" : "2") + "'";
            return connStr;
        }

注意： Excel2003 对应的 Excel 8.0,而Excel 2007及以上版本对应的是Excel 12.0

热点排行

C#

Excel变换DataTable