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