导入excel，xls,xlsx



/// <summary>
        /// 文件.xlsx，获得xls内的DataTable
        /// </summary>
        /// <param name="filenameurl">文件保存地址</param>
        /// <param name="table">文件名称</param>
        /// <param name="IsXls">文件后缀名</param>
        /// <returns></returns>
        public DataSet ExecleDs(string filenameurl, string table, string IsXls)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
                //strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";

                //备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
                //      "IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。
                // string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Server.MapPath("ExcelFiles/Mydata2007.xlsx") + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                //DataTable FromExcel = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                动态获得表的名称
                //string tbname = FromExcel.Rows[4].ToString();
                DataSet ds = new DataSet();
                string strsql = "select * from [新增$]";
                OleDbDataAdapter odda = new OleDbDataAdapter(strsql, conn);
                odda.Fill(ds, table);
                return ds;
            }
            catch (Exception ee)
            {
                //Log.WriteErorrLog(ee);
                LogBLL.debug(ee.ToString());
                // Log.WriteLog("ERROR", ee.ToString());
                return null;
            }
        }
        /// <summary>
        /// 文件.xls，获得xls内的DataTable
        /// </summary>
        /// <param name="filenameurl">文件保存地址</param>
        /// <param name="table">文件名称</param>
        /// <param name="IsXls">文件后缀名</param>
        /// <returns></returns>
        public DataSet ExecleDs1(string filenameurl, string table, string IsXls)
        {
            try
            {
                string strConn;
                //strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
                //strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
                strConn = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + filenameurl + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                //备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
                //      "IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。
                // string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Server.MapPath("ExcelFiles/Mydata2007.xlsx") + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
                OleDbConnection conn = new OleDbConnection(strConn);

                conn.Open();
                // DataTable FromExcel = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                动态获得表的名称
                //  string tbname = FromExcel.Rows[4].ToString();
                DataSet ds = new DataSet();
                string strsql = "select * from [新增$]";
                OleDbDataAdapter odda = new OleDbDataAdapter(strsql, conn);
                odda.Fill(ds, table);
                return ds;
            }
            catch (Exception ee)
            {
                //Log.WriteErorrLog(ee);
                LogBLL.debug(ee.ToString());
                //Log.WriteLog("ERROR", ee.ToString());
                return null;
            }
        }