OLEDB打开Excel文件的实现方法

classclsoledb

  {

    OleDbConnection connection;

    publicvoidOpenConnection(stringxlsFils)

    {

      if(!File.Exists(xlsFils))

      {

        MessageBox.Show("文件"+ xlsFils +"不存在","提示");

        return;

      }

      stringconn ="Provider = Microsoft.Jet.OLEDB.4.0;Data Source ="+ xlsFils +";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'";

      connection =newOleDbConnection(conn);

      try

      {

        connection.Open();

      }

      catch(OleDbException ex)

      {

        if(ex.ErrorCode == -2147467259)

        {

          connection.ConnectionString ="Provider = Microsoft.Ace.OLEDB.12.0;Data Source ="+ xlsFils +";Extended Properties='Excel 12.0;HDR=NO;IMEX=1;'";

          connection.Open();

        }

      }

    }

    publicDataTable Select()

    {

      DataTable dt =newDataTable();

      stringSql ="select * from [$A1:R65536]";

      OleDbDataAdapter mycommand =newOleDbDataAdapter(Sql, connection);

      mycommand.Fill(dt);

      if(dt.Rows.Count > 0)

      {

        DataRow dr = dt.Rows[0];

        for(intcol = 0; col < dt.Columns.Count; col++)

        {

          dt.Columns[col].ColumnName = dr[col].ToString();

        }

        dt.Rows[0].Delete();

        dt.AcceptChanges();

      }

      returndt;

    }

  }