将EXCEL做为数据库的方式操作EXCEL

将EXCEL做为数据库的方式操作EXCEL
using System;
using System.Data;
using System.Data.OleDb;

namespace Entity.DataBase
{
 /// <summary>
 /// ClsExcel 的摘要说明。
 /// </summary>
 public class ClsExcel
 {

  OleDbConnection ExcelCon = null;
  //---------------------------------------------------------------------------------------------------------------------------
  public ClsExcel(string strExcelFilePath)
  {
   Connection(strExcelFilePath);
  }
  //---------------------------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 建立与EXCEL的链接
  /// </summary>
  /// <param name="strExcelFilePath"></param>
  public void Connection(string strExcelFilePath)
  {
   try
   {
    string ConnectionStr = "";           //链接字符串

    if(ExcelCon != null)
    {
     Close();
    }

    ConnectionStr ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelFilePath + ";Extended Properties=Excel 8.0;";
    ExcelCon = new OleDbConnection(ConnectionStr);
    ExcelCon.Open();
   }
   catch(Exception e)
   {
    string strError = e.Message;
   }
  }
  //----------------------------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 返回Connection对象
  /// </summary>
  /// <returns>OleDbConnection：为null时，表示返回失败；</returns>
  public OleDbConnection GetConnection()
  {   
   return(ExcelCon);
  }
  //----------------------------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 返回Command对象
  /// </summary>
  /// <param name="SQL">SQL语句</param>
  /// <returns>OleDbCommand：为null时，表示返回失败；</returns>
  public OleDbCommand GetCommand(string SQL)
  {
 
   try
   {
    OleDbCommand  OleCom=new OleDbCommand(SQL, ExcelCon);
    
    return(OleCom);
   }
   catch(Exception e)
   {
    string strError = e.Message;
    return(null);
   }
  }
  //----------------------------------------------------------------------------------------------------------------------------
  /// <summary>
  ///返回DataAdapter对象
  /// </summary>
  /// <param name="SQL">SQL语句</param>
  /// <returns>OleDbDataAdapter：为null时，表示返回失败；</returns>
  public OleDbDataAdapter GetDataAdapter(string SQL)
  {
   OleDbDataAdapter adp = null;
   try
   {
    adp= new OleDbDataAdapter(SQL, ExcelCon);
    return(adp);
   }
   catch(Exception e)
   {
    string strError = e.Message;
    adp = null;
    return(adp);
   }

  }
  //----------------------------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 执行SQL插入、更新、删除操作
  /// </summary>
  /// <param name="SQL">SQL语句</param>
  /// <returns>bool：true成功；false失败；</returns>
  public bool Execute(string SQL)
  {
   OleDbCommand comm;
   comm = GetCommand(SQL);
   try
   {
    comm.ExecuteNonQuery();
    comm.Dispose();    //使用完后，及时将该对象释放，如果不释放的话，生成过多的COMMAND对象会导致ORCALE报错；
    comm = null;
    return(true);
   }
   catch(Exception e)
   {
    comm = null;
    string strError = e.Message;
    return(false);
   }
  }
  //----------------------------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 取得DataSet对象
  /// </summary>
  /// <param name="SQL">SQL语句</param>
  /// <param name="DataTableName">填充在DataSet中的表名称</param>
  /// <returns>DataSet</returns>
  public DataSet GetDataSet(string SQL, string DataTableName)
  {
   OleDbDataAdapter Adpt = null;
   DataSet DS = new DataSet() ;

   try
   {
    Adpt = GetDataAdapter(SQL);
    Adpt.Fill(DS, DataTableName);
   }
   catch(Exception e)
   {
    string strError = e.Message;
    DS = null;
   }   

   return (DS);
  }
  //----------------------------------------------------------------------------------------------------------------------------

  /// <summary>
  /// 取得DataSet对象
  /// </summary>
  /// <param name="SQL">SQL语句</param>
  /// <returns>DataSet</returns>
  public DataSet GetDataSet(string SQL)
  {
   OleDbDataAdapter Adpt = null;
   DataSet DS = new DataSet() ;
   string DataTableName = "Table1";

   try
   {
    Adpt = GetDataAdapter(SQL);
    Adpt.Fill(DS, DataTableName);
   }
   catch(Exception e)
   {
    string strError = e.Message;
    DS = null;
   }   

   return (DS);
  }
  //----------------------------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 关闭数据库对象
  /// </summary>
  public void Close()
  {
   try
   {
    if(ExcelCon != null)
    {
     ExcelCon.Close ();
     ExcelCon.Dispose ();
     ExcelCon = null;
    }
   }
   catch
   {
    ExcelCon = null;
   }
  }
  //----------------------------------------------------------------------------------------------------------------------------
 }
}