C#操纵Excel文件

OGeek|极客世界-中国程序员成长平台 › 门户 › 编程› Node.js›Node.js教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

转自:http://blog.sina.com.cn/u/46335684010008b7

WinFormLiLang
{
/// <summary>
/// 创建者: Eddy
/// 联系QQ: 927129
/// 创建时间: 07.1.3 04:17:00
/// 类功能: Excel操纵
/// 类通用接口: 无
/// 命名规则: 标准命名
/// 外加引用: WriteLog log类
/// </summary>
public class ClsExcelAlt
{
  //oleDbConnection
  protected System.Data.OleDb.OleDbConnection OdCon;
  //oda
  protected System.Data.OleDb.OleDbDataAdapter oda;
  //oc
  protected System.Data.OleDb.OleDbCommand oc;
  /// <summary>
  /// 构造:)
  /// </summary>
  public ClsExcelAlt()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }
  /// <summary>
  /// Excel表导出一个Table
  /// </summary>
  /// <param name="strPath">ExcelPath</param>
  /// <param name="strSheetName">工作簿名称</param>
  /// <param name="strConn">连接字符串(与此方法无关,但必须得过来一个字符串,为了触发SqlCommand进行Excel的操作)</param>
  /// <returns>Table</returns>
  public System.Data.DataTable ExcelToTable(string strPath,string strSheetName,string strConn)
  {
   try
   {
    System.Data.DataTable tempDt=new System.Data.DataTable();
    OdCon=new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "TempExcelAlt.mdb");
    OdCon.Open();
    oda=new System.Data.OleDb.OleDbDataAdapter("select top 65535 * from [Excel 8.0;database=" + strPath + "].["+strSheetName+"$]",OdCon);
    //oda=new System.Data.OleDb.OleDbDataAdapter("select top 65535 * from [Excel 8.0;database=" + strPath + "].["+strSheetName+"$]",OdCon);
    oda.Fill(tempDt);
    return tempDt;
   }
   catch(Exception ex)
   {
    WriteLog.WriteError("ClsExcelAlt中ExcelToTable方法错误!错误原因:"+ex.Message.ToString());
   }
   finally
   {
    oda.Dispose();
   }
   return new System.Data.DataTable();
  }
  /// <summary>
  /// 得到一个Datatable,并把它写到Excel里
  /// </summary>
  /// <param name="strPath">ExcelPath</param>
  /// <param name="strSheetName">工作簿名称</param>
  /// <param name="strConn">连接字符串(与此方法无关,但必须得过来一个字符串,为了触发SqlCommand进行Excel的操作)</param>
  public void TableToExcel(string strPath,string strSheetName,string strConn)
  {
   try
   {
    OdCon=new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "TempExcelAlt.mdb");
    OdCon.Open();
    oc=new System.Data.OleDb.OleDbCommand();
    oc.Connection=OdCon;
    oc.CommandText="select top 65535 * into [Excel 8.0;database=" + strPath + "].["+strSheetName+"] from TempTable";
    int a=oc.ExecuteNonQuery();
   }
   catch(Exception ex)
   {
    WriteLog.WriteError("ClsExcelAlt中TableToExcel方法错误!错误原因:"+ex.Message.ToString());
   }
   finally
   {
    oc.Dispose();
    OdCon.Close();
   }
  }
}
}