excel 导入（改进版）

 

 //import excel by filepath and sheetname
        public DataTable ExcelDataSource(string filepath, string sheetname)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\";";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
            DataTable dt = new DataTable();
            oada.Fill(dt);
            return dt;
        }


        //import first sheet of excel by filepath
        public DataTable ExcelDataSource(string filepath)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            int count = ExcelSheetName(filepath).Count - 1;
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + ExcelSheetName(filepath)[count] + "]", strConn);
            DataTable dt = new DataTable();
            oada.Fill(dt);
            return dt;
        }

        //get excel's sheet name list
        public ArrayList ExcelSheetName(string filepath)
        {
            ArrayList al = new ArrayList();
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;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;
        }

 

注意：excelsheetname获得的表名是按照字母排序的