导入Excel方式汇总--第一种

第一种：利用oledb方式，把Excel当做数据库进行读取。

 

/// <summary>

        /// 根据路径读取Excel放入DataTable里
        /// </summary>
        /// <param name="path"></param>
        /// <param name="error"></param>
        /// <returns></returns>
        public DataTable ExcelToDS(string path,out string error)
        {
            string fileType = System.IO.Path.GetExtension(path);
            string fileType1 = path.Substring(path.LastIndexOf('.'));


            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
            if (fileType == ".xls")
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            else
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
            DataSet ds = null;
            try
            {
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;


                //得到所有sheet的名字
                DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); 
                //得到第一个sheet的名字
                string firstSheetName = sheetsName.Rows[0][2].ToString();


                strExcel = "select * from [sheet1$]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                myCommand.Fill(ds, "table1");
                error = "";
            }
            catch(Exception ex)
            {
                error = ex.Message;
            }
            return ds.Tables[0];
        }