使用OleDb，将Excel导入DataSet

本方法，将传入的Excel文件内所有的Sheet内的数据都填充入DataSet中。这是一个简单快捷的方法，不足之处是不适合带有格式复杂的Excel文件。

using System.Data;
using System.Data.OleDb;
using System.Data.Odbc;

public DataSet GetDataSetFromExcel(string filePath)
  {
   string strConn  = "Provider=Microsoft.Jet.OleDb.4.0;";
   strConn += "data source=" + filePath + ";";
   strConn += "Extended Properties=Excel 8.0;"; 
   //strConn += "HDR=Yes;IMEX=1";

   OleDbConnection objConn = new OleDbConnection(strConn);

   ArrayList SheetNameList = new ArrayList();
   
   try
   {
    objConn.Open();

    DataTable dtExcelSchema = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});
  
    string SheetName="";

    for(int i=0;i<dtExcelSchema.Rows.Count;i++)
    {
     SheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
     SheetNameList.Add( SheetName ); 
    }
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    objConn.Close();
   }

            DataSet dsExcel = new DataSet();

   try
   {
    string strSql = "";
    
    for(int i=0; i<SheetNameList.Count; i++)
    {
     //string strSql  ="select * From [Sheet1$e5:g23]";
     //string strSql  ="select [F1],[F2],[F3] From [Sheet2$3:100] ";  
     strSql = "select * from [" + (string)SheetNameList[i] + "]";
     OleDbDataAdapter oleExcelDataAdapter = new OleDbDataAdapter(strSql, objConn);
     DataTable dtExcel = new DataTable((string)SheetNameList[i]);
     oleExcelDataAdapter.Fill(dtExcel);
     dsExcel.Tables.Add(dtExcel);        
    }
    return dsExcel;
   }
   catch(Exception ex)
   {
    throw ex;
   }
  }