/********************************************************
* Code Base of C#
* Excel report develop tools
* Create by huang pinghua 2008/12/28, all rights reserved.
* *****************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.IO;
namespace ExcelX.ExcelLib
{
/// <summary>
/// Excel 操作代理
/// </summary>
public class ExcelAgent
{
private ApplicationClass _app = null;
private _Workbook _wb = null;
private _Worksheet _ws = null;
private string _filePath = "";
private int _shIndex = 0; // 1 based index
public event EventHandler ExcelExceptionOccured;
/// <summary>
/// 当前Sheet
/// </summary>
public int SheetIndex { get { return this._shIndex; } }
/// <summary>
/// 当前文件名
/// </summary>
public string FileName { get { return this._filePath; } }
#region private operations
/// <summary>
/// 打开App
/// </summary>
private void OpenApp()
{
this._app = new ApplicationClass();
this._app.Visible = false;
}
/// <summary>
/// 释放资源
/// </summary>
/// <param name="o"></param>
private void ReleaseCom(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);//强制释放一个对象
}
catch { }
finally
{
o = null;
}
}
/// <summary>
/// 检查App
/// </summary>
private bool CheckApp()
{
if (this._app == null)
{
if (this.ExcelExceptionOccured != null)
{
this.ExcelExceptionOccured(this, new ErrorEventArgs(new Exception("Application对象未初始化")));
}
return false;
}
return true;
}
/// <summary>
/// 检查Book
/// </summary>
private bool CheckWorkBook()
{
if (this._wb == null)
{
if (this.ExcelExceptionOccured != null)
{
this.ExcelExceptionOccured(this, new ErrorEventArgs(new Exception("Workbook对象未初始化")));
}
return false;
}
return true;
}
/// <summary>
/// 检查Sheet
/// </summary>
private bool CheckSheet()
{
if (this._ws == null)
{
if (this.ExcelExceptionOccured != null)
{
this.ExcelExceptionOccured(this, new ErrorEventArgs(new Exception("Worksheet对象未初始化")));
}
return false;
}
return true;
}
#endregion
#region basic operation
/// <summary>
/// 打开文件
/// </summary>
/// <param name="filePath"></param>
public void Open(string filePath)
{
if (!File.Exists(filePath))
{
//打开工作簿
Workbook mybook = (Workbook)this._app.Workbooks.Add(1); // 1表示只建一个表
//保存
mybook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
// Check Application
if (!this.CheckApp()) return;
// Open workbook
this._filePath = filePath;
this._wb = this._app.Workbooks._Open(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);
// set default sheet
this.SetCurrentSheet(1);
}
public void Creat(string filePath)
{
if(!File.Exists(filePath) )
{
ApplicationClass my=new ApplicationClass();
my.Visible=false;
object objMissing=System.Reflection.Missing.Value;
//打开工作簿
Workbook mybook = (Workbook)my.Workbooks.Add(1); // 1表示只建一个表
//保存
mybook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
}
/// <summary>
/// 自动打开Excel对象
/// </summary>
public ExcelAgent()
{
this.OpenApp();
}
/// <summary>
/// 打开excel文件
/// </summary>
/// <param name="filePath"></param>
public ExcelAgent(string filePath)
{
this.OpenApp();
this.Open(filePath);
}
/// <summary>
/// 保存当前文档
/// </summary>
public void Save()
{
// check workbook
if (!this.CheckWorkBook()) return;
// save the book
this._wb.Save();
}
/// <summary>
/// 另存当前文档
/// </summary>
/// <param name="filePath"></param>
public void Save(string filePath)
{
// check workbook
if (!this.CheckWorkBook()) return;
// save work book
this._filePath = filePath;
bool b = this._app.DisplayAlerts;
this._app.DisplayAlerts = false;
// save work book
this._wb.SaveAs(this._filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
this._app.DisplayAlerts = b;
}
/// <summary>
/// 关闭当前操作
/// </summary>
public void Close()
{
if (this._app == null) return;
if (this._wb != null)
{
this._wb.Close(false, Missing.Value, Missing.Value);
ReleaseCom(this._wb);
this._wb = null;
}
this._app.Quit();
ReleaseCom(this._app);
this._app = null;
}
/// <summary>
/// 设置当前工作Sheet（序号：从1记起）
/// </summary>
/// <param name="sheetIndex"></param>
public void SetCurrentSheet(int sheetIndex)
{
// check workbook
if (!this.CheckWorkBook()) return;
// set sheet object
this._shIndex = sheetIndex;
this._ws = (_Worksheet)this._wb.Worksheets[sheetIndex];
}
/// <summary>
/// 设置当前工作Sheet（序号：从1记起）
/// </summary>
/// <param name="sheetIndex"></param>
public void SetCurrentSheet(string SheetName)
{
// check workbook
if (!this.CheckWorkBook()) return;
// set sheet object
this._ws = (_Worksheet)this._wb.Worksheets[SheetName];
this._shIndex = this._ws.Index;
}
/// <summary>
/// 删除一个工作表
/// </summary>
/// <param name="SheetName"></param>
public void DeleteSheet()
{
// check workbook
if (!this.CheckSheet()) return;
this._ws.Delete();
}
/// <summary>
/// 改名
/// </summary>
/// <param name="newName"></param>
public void RenameSheet(string newName)
{
// check workbook
if (!this.CheckSheet()) return;
this._ws.Name = newName;
}
/// <summary>
/// 创建Sheet
/// </summary>
/// <param name="newName"></param>
public void CreateSheet(string newName)
{
// check workbook
if (!this.CheckWorkBook()) return;
this._wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
/// <summary>
/// 获取数量
/// </summary>
/// <returns></returns>
public int GetSheetCount()
{
// check workbook
if (!this.CheckWorkBook()) return -1;
return this._wb.Worksheets.Count;
}
#endregion
#region sheet operation
/// <summary>
/// 设置单元值
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="value"></param>
public void SetCellValue(int x, int y, object value)
{
if (!this.CheckSheet()) return;
this._ws.Cells[x, y] = value;
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="x1"></param>
/// <param name="y1"></param>
/// <param name="x2"></param>
/// <param name="y2"></param>
public void UniteCells(int x1, int y1, int x2, int y2)
{
if (!this.CheckSheet()) return;
this._ws.get_Range(this._ws.Cells[x1, y1], this._ws.Cells[x2, y2]).Merge(Type.Missing);
}
/// <summary>
/// 将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
/// </summary>
/// <param name="dt"></param>
/// <param name="startX"></param>
/// <param name="startY"></param>
public void InsertTable(System.Data.DataTable dt, int startX, int startY)
{
if (!this.CheckSheet()) return;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
this._ws.Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
}
}
}
/// <summary>
/// 获取单元格值
/// </summary>
/// <param name="cellName"></param>
/// <returns></returns>
public object GetCellValue(string cellName)
{
if (!this.CheckSheet()) return null;
Range range = this._ws.get_Range(cellName, Type.Missing);
return range.Value2;
}
/// <summary>
/// 获取单元格值
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <returns></returns>
public object GetCellValue(int row, int col)
{
if (!this.CheckSheet()) return null;
try
{
Range range = (Range)this._ws.Cells[row, col];
return range.Value2;
}
catch
{
return "";
}
}
public string GetStringValue(string cellName)
{
object val = this.GetCellValue(cellName);
string result = "";
if (val != null) result = val.ToString();
return result;
}
public string GetStringValue(int row, int col)
{
object val = this.GetCellValue(row, col);
string result = "";
if (val != null) result = val.ToString();
return result;
}
public double GetDoubleValue(string cellName)
{
object val = this.GetCellValue(cellName);
string result = "";
if (val != null) result = val.ToString();
double number = 0d;
if (double.TryParse(result, out number))
{
number = double.Parse(result);
}
else
{
number = 0d;
}
return number;
}
public double GetDoubleValue(int row, int col)
{
object val = this.GetCellValue(row, col);
string result = "";
if (val != null) result = val.ToString();
double number = 0d;
if (double.TryParse(result, out number))
{
number = double.Parse(result);
}
else
{
number = 0d;
}
return number;
}
#endregion
}
}
ExcelAgent
