操作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();
   }
  }
 }
}

posted on 2006-05-26 21:41  Yang-S  阅读(216)  评论(0编辑  收藏  举报