判断EXCEL是否有该表

Question

有一个EXCEL,其中有几千个SHEET.
我的SQLSERVER数据库中有个表ABC,里面记录了该EXCEL的几千的表名.
在我将那几千个SHEET导入SQLSERVER的时候,想先判断一下ABC里的表名是否在EXCEL的几千个SHEET中是否真的存在.
该怎么去判断?
以下是代码
while (myDataReader.Read())
            {
                urlstr =  myDataReader["url"].ToString();
                urlstr = urlstr.Replace("'", "");
                uid = myDataReader["id"].ToString();                sheetcolshu = getSHEETcolsum(this.ExcelFileName.Text.ToString(), urlstr);
                chazhi = fubiaotablesum - sheetcolshu;                switch (chazhi)
                #region //获取补充字段，返回tiaojian
                {
                    case 1:
                        tiaojian = "," + uid;
                        break;
                    case 2:
                        tiaojian = ",0," + uid;
                        break;
                   }
                #endregion                #region //判断是否存在该表                                #endregion
                comm2 = "insert  into " + fubiaotablename + " select *" + tiaojian + " from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=" + ExcelFileName.Text.ToString().Trim() + ";Extended properties=Excel 8.0')...[" + urlstr + "$]";
                comm = new SqlCommand(comm2, myConnection);
                comm.ExecuteNonQuery();
   
            }

Answer 1

/// <summary>   
       /// 获取EXCEL的表 表名字列    
       /// </summary>   
       /// <param name="p_ExcelFile">Excel文件</param>   
       /// <returns>数据表</returns>   
       public static DataTable GetExcelTableName(string p_ExcelFile)   
       {   
           try  
           {   
               if (System.IO.File.Exists(p_ExcelFile))   
               {   
                   OleDbConnection _ExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + p_ExcelFile);   
                   _ExcelConn.Open();   
                   DataTable _Table = _ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);   
                   _ExcelConn.Close();   
                   return _Table;   
               }   
               return null;   
           }   
           catch  
           {   
               return null;   
           }   
       }  
 

Answer 2

少个参数SHEET参数,我是要判断XLS文件里是否存在某个SHEET.
函数最好能返回BOOL型的.

Answer 3

参阅一下GetOleDbSchemaTable的功能即可

Answer 4

if(dataSet.Tables.Contains("")) 
{}
或使用select 表名=name,类型=case xtype when 'S' then '系统表' else '用户表' end from sysobjects 
where xtype in('U','S')

Answer 5

 
几千个表用dbo不代现实吧。
用excel的office组件　，如
 (workBook.Sheets[i] as Excel.WorkSeet).Name
这个效率和成本都能保证

Answer 6

顶一下.函数最好能返回BOOL型的.

Answer 7

搞定了,谢谢大家!public bool CheckExcel(string path, string sheetName)
        {
            bool returnValue;
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
            DataSet myDataSet = new DataSet();
            try
            {
                using (OleDbConnection myOleDbConnection = new OleDbConnection(strConn))
                {
                    myOleDbConnection.Open();
                    string strExcel = " select top 1 * from [" + sheetName + "$]";
                    using (OleDbCommand myOleDbCommand = new OleDbCommand(strExcel, myOleDbConnection))
                    {
                        myOleDbCommand.ExecuteNonQuery();
                        using (OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter(myOleDbCommand))
                        {
                            myOleDbDataAdapter.Fill(myDataSet);
                        }
                    }
                }
                returnValue = true;
            }
            catch (Exception)
            {                returnValue = false;
            }            return returnValue;
        }