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

# 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

转自：

http://hi.baidu.com/jinjie1118/blog/item/2ae592e9719b253bb90e2df0.html