.net中操作Excel的方法

一、引用Excel 库（EXCEL9.OLB）或者添加引用命名空间，在添加引用=>com组件里面找 Microsoft Execl组件 添加进来就可以了。。
二、在需要操作的页面上建立应用 m_objExcel = new Excel.ApplicationClass(); 通过此应用就可以对Excel表格进行各种操作

但有一个前提是必须在Web.config配置文件中加用户验证：
<identity impersonate="true" userName="" password=""></identity>

这个得需要服务器的用户名及密码，而我们一般不可能拥有服务器的用户名及密码，那这该怎么办，能不能不用这个验证

------------------------------------------------------------------------------------------------------------------------------

操作EXCEL类

using System;
using System.Data;
using System.Reflection;
using System.IO;

namespace TIMS.WEB.PublicClass.ReportForms
{
/// <summary>
/// ExcelReportForms 的摘要说明。
/// </summary>
public class ExcelRF
{
  private Excel.Application m_oExcelApp = null;
  private Excel.Workbooks   m_oBooks = null;
  private Excel.Workbook    m_oBook = null;
  private Excel.Worksheet   m_oSheet = null;
  private Excel.Range       m_oRange = null;
  private Excel.Range       m_oCopyRange = null;
  private string m_FilePath = "";
  //--------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 构造函数，加载Excel模版文件；
  /// </summary>
  /// <param> FilePath是指定要打开的Excel文件路径 </param>
  public ExcelRF(string FilePath)
  { 
   try
   {
    m_FilePath = FilePath;
    m_oExcelApp = new Excel.ApplicationClass();  
    m_oBooks = m_oExcelApp.Workbooks;
    m_oBook = ((Excel.Workbook)m_oBooks.Open(m_FilePath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value));
    m_oSheet = (Excel.Worksheet)m_oBook.ActiveSheet;

    this.m_oExcelApp.Visible =false;
    this.m_oExcelApp.DisplayAlerts =false;
   }
   catch(Exception e)
   {
    CloseReportForms();
    new Exception(e.Message);
   }
  
  }
  //--------------------------------------------------------------------------------------------------------
  //作用 设置活动工作簿
  //--------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 设置活动工作簿
  /// </summary>
  /// <param name="index">要设置为活动工作簿的索引值</param>
  /// <returns>函数返回true,则设置活动工作簿成功,否则失败.</returns>
 
  public bool SetActiveSheet(int index)
  {
   bool bResult = false;
   try
   {
   m_oSheet = (Excel.Worksheet)m_oBook.Sheets[index];
   }
   catch(Exception e)
   {
    CloseReportForms();
    new Exception(e.Message);
   }

   return(bResult);
  }
  //--------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 向Excel写入数据
  /// </summary>
  /// <param>RowIndex是要写入数据单元格行的索引，ColumnIndex是要写入数据单元格列的索引，
  /// Value是要写入该单元格的数据值.</param>
  /// <returns>函数返回true,则写入数据成功,否则失败.</returns>
  public bool WriteValue(int RowIndex, int ColumnIndex, string Value)
  {
   bool bResult = false;
   try
   {
    m_oRange = (Excel.Range)m_oSheet.Cells[RowIndex, ColumnIndex];
    m_oRange.Value2 = Value;
    m_oRange = null;
    bResult = true;
   }
   catch(Exception e)
   {
    CloseReportForms();
    new Exception(e.Message);
   }

   return(bResult);
  }
  //--------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 向Excel写入数据
  /// </summary>
  /// <param>StartLocation是要写入区域的左上角单元格位置，
  /// EndLocation是要写入区域的右下角单元格位置，Value是要写入指定区域所有单元格的数据值</param>
  /// <returns>如果函数返回true,则写入数据成功,否则失败.</returns>
  public bool WriteValue(string StartLocation, string EndLocation, string Value)
  {
   bool bResult = false;

   try
   {
    m_oRange = m_oSheet.get_Range(StartLocation, EndLocation);
    m_oRange.Value2 = Value;
    m_oRange = null;
    bResult = true;
   }
   catch(Exception e)
   {
    CloseReportForms();
    new Exception(e.Message);
   }

   return(bResult);
  }
  //--------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 拷贝单元格
  /// </summary>
  /// <param>SorStart是复制区域的左上角单元格位置,SorEnd是复制区域的右下角单元格位置,
  /// DesStart是粘贴区域的左上角单元格位置,DesEnd是粘贴区域的右下角单元格位置</param>
  /// <returns>函数返回true,则拷贝数据成功,否则失败</returns>
  public bool CopyCells(string SorStart, string SorEnd, string DesStart, string DesEnd)
  {
   bool bResult=false;
   try
   {
    m_oCopyRange = m_oSheet.get_Range(SorStart, SorEnd);
    m_oRange= m_oSheet.get_Range(DesStart, DesEnd);
    m_oCopyRange.Copy(m_oRange);

    m_oCopyRange = null;
    m_oRange = null;
    bResult = true;
   }
   catch(Exception e)
   {
    CloseReportForms();
    new Exception(e.Message);
   }

   return(bResult);
  }
  //--------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 向Excel插入行
  /// </summary>
  /// <param>RowIndex是指要插入所在行的索引位置，插入后其原有行下移，RowNum是要插入行的个数</param>
  /// <returns>函数返回true,则插入数据成功,否则失败.</returns>
  public bool InserRow(int RowIndex,int RowNum)
  { 
   bool bResult=false;
   if((RowIndex<=0)||(RowNum<=0))
   {
    return(bResult);
        
   }
   try
   {
    m_oRange = (Excel.Range)m_oSheet.Rows[RowIndex,Missing.Value];
    for(int i=0;i<RowNum;i++)
    {
     m_oRange.Insert(Excel.XlDirection.xlDown,Missing.Value);
    }
    m_oRange = null;
    bResult=true;
   }
   catch(Exception e)
   {
    CloseReportForms();
    new Exception(e.Message);
   }
   return(bResult);
  }
  //--------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 保存Excel文件
  /// </summary>
  /// <param>SavePath是指要存储的路径和文件名</param>
  /// <returns>函数返回true,则存储文件成功,否则失败.</returns>
  public bool SaveExcel(string SvaePath)
  {
   bool bResult = false;

   try
   {   
    m_oSheet.SaveAs(SvaePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value);  
    bResult = true;
   }
   catch(Exception e)
   {
    CloseReportForms();
    new Exception(e.Message);
   }

   return(bResult);
  }
  //--------------------------------------------------------------------------------------------------------
  /// <summary>
  /// 关闭Excel文件，释放对象
  /// </summary>
  /// <param></param>
  public void CloseReportForms()
  {
   try
   {
    m_oBooks = null;
    m_oBook = null;
    m_oSheet = null;
    m_oRange = null;
    if(m_oExcelApp != null)
    {
     m_oExcelApp.Quit();
     m_oExcelApp = null;
    }
   }
   finally
   {
    GC.Collect();
    GC.WaitForPendingFinalizers();
   }
  }
}
}

 

 

 

 

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

该文章转载自德仔工作室：http://www.dezai.cn/Article_Show.asp?ArticleID=23476&ArticlePage=2

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/liusen5555/archive/2008/03/19/2197407.aspx