将Excel导入DataSet

来源：互联网发布：des加密c语言编辑：程序博客网时间：2024/04/27 23:29

将Excel导入DataSet

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;
   }
}