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