将Excel中所有Sheet的数据导入一个DataSet

将Excel中所有Sheet的数据导入一个DataSet

2008-06-11 11:40

# region 将Excel中所有Sheet的数据导入一个DataSet /**/ /// <summary> /// 将Excel中所有Sheet的数据导入一个DataSet /// </summary> /// <returns></returns> public static DataSet CreateDataSource( string strPath) {                 try      {                   bool m_DelRow = false;                 string strConn = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + strPath + "; Extended Properties = 'Excel 8.0;HDR = Yes;IMEX = 1'";      OleDbConnection conn = new OleDbConnection(strConn);      conn.Open();                      System.Data.DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"Table"});                 //=======删除文件中的打印区域============================================================                     for(int k = 0;k < dtSheetName.Rows.Count;k++)     {         if(dtSheetName.Rows[k]["TABLE_NAME"].ToString().EndsWith("Print_Area"))         {                 dtSheetName.Rows[k].Delete();          }                           }      dtSheetName.AcceptChanges(); //========================================================================                     string strSql = string.Empty;          DataSet myDataSet = new DataSet();                 //===========================================================================                     for(int i = 0 ;i < dtSheetName.Rows.Count;i++)     {//用多个表来填充myDataSet           strSql = "Select * from [" + dtSheetName.Rows[i]["TABLE_NAME"].ToString() + "]";                             OleDbDataAdapter myCommand = new OleDbDataAdapter(strSql,conn);                        myCommand.Fill(myDataSet,dtSheetName.Rows[i]["TABLE_NAME"].ToString());                       } //==========删除空表=========================================     //     for(int h =0;h < myDataSet.Tables.Count;h++) //     { //        if(myDataSet.Tables[h].Rows.Count == 0) //        { //         myDataSet.Tables[h]. //        } //     }     //删除文件中的全为空的行     for(int j = 0;j < myDataSet.Tables.Count;j++)     {         for(int i = 0;i < myDataSet.Tables[j].Rows.Count;i++)         {            for(int k = 0; k < myDataSet.Tables[j].Columns.Count;k++)            {             if(myDataSet.Tables[j].Rows[i][k].ToString() != string.Empty)             {                   m_DelRow = true;              }                                     }           if(!m_DelRow)           {              myDataSet.Tables[j].Rows[i].Delete();                                    }            m_DelRow = false;              }        }       myDataSet.AcceptChanges();   //================================================================================            m_DelRow = false;           return myDataSet;        }       catch(Exception err)       {             Console.WriteLine(err.Message + System.Environment.NewLine +   err.StackTrace);                        return null     ;        } } #endregion