/// <summary>
/// Excel操作类
/// </summary>
public class Excel
{
#region 私有属性
private _Application _oExcel = null; //Excel应用程序
private _Workbook _oBook = null; //Excel工作薄
private _Worksheet _oSheet = null; //Excel工作表
private bool _visible=false; //是否显示Excel程序
#endregion
#region 公共属性
public bool Visible
{
set { _oExcel.Visible = value; }
}
public object CurrentSheet
{
set
{
_oSheet = (_Worksheet)_oBook.Sheets[value];
_oSheet.Activate();
}
}
#endregion
#region Excel对象初始化
/// <summary>
/// 初始化
/// </summary>
private void Init()
{
GC.Collect();
_oExcel = new ApplicationClass();
}
#endregion
#region 构造函数
/// <summary>
/// 实例一个Excel对象
/// </summary>
public Excel()
{
Init();
}
/// <summary>
/// 实例一个Excel对象
/// </summary>
/// <param name="fileName">文件名称（物理路径）</param>
public Excel(string fileName)
{
Init();
_oBook = _oExcel.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_oSheet = (_Worksheet)_oBook.ActiveSheet;
}
#endregion
#region 打开Excel文件
/// <summary>
/// 打开一个指定的Excel文件
/// </summary>
/// <param name="fileName">文件名称（物理路径）</param>
public void Open(string fileName)
{
_oBook = _oExcel.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing,
Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
_oSheet = (_Worksheet)_oBook.ActiveSheet;
}
#endregion
#region 设置Excel值
/// <summary>
/// 设置Excel的单元格值
/// </summary>
/// <param name="x">行开始</param>
/// <param name="y">列开始</param>
/// <param name="value">值</param>
public void SetCellValue(int x, int y, object value)
{
_oSheet.Cells[x, y] = value;
}
/// <summary>
/// 设置Excel的行值
/// </summary>
/// <param name="startX">行开始</param>
/// <param name="startY">列开始</param>
/// <param name="values">值</param>
public void SetRowValue(int startX, int startY, object[] values)
{
for (int i = 0; i < values.Length; i++)
{
_oSheet.Cells[startX, startY + i] = values[i];
}
}
/// <summary>
/// 设置Excel的列值
/// </summary>
/// <param name="startX">行开始</param>
/// <param name="startY">列开始</param>
/// <param name="values">值</param>
public void SetColumnValue(int startX, int startY, object[] values)
{
for (int i = 0; i < values.Length; i++)
{
_oSheet.Cells[startX + i, startY] = values[i];
}
}
/// <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)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
_oSheet.Cells[startX + i, j + startY] = dt.Rows[i][j];
}
}
}
#endregion
#region 增加数据区模板行
/// <summary>
/// 增加数据区模板行
/// </summary>
/// <param name="tempRowIndex">行起始位置</param>
/// <param name="tempRowCount">增加的行数</param>
/// <returns>增加的行数</returns>
public int AddRow(int tempRowIndex, int tempRowCount)
{
for (int i = 0; i < tempRowCount; i++)
{
Range row = (Range)_oSheet.Rows[tempRowIndex, Type.Missing];
row.Select();
row.Copy(Type.Missing);
row.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
}
return tempRowCount;
}
/// <summary>
/// 增加数据区模板行
/// </summary>
/// <param name="tempRowIndex">行起始位置</param>
/// <returns>增加的行数</returns>
public int AddRow(int tempRowIndex)
{
Range row = (Range)_oSheet.Rows[tempRowIndex, Type.Missing];
row.Select();
row.Copy(Type.Missing);
row.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
return 1;
}
#endregion
#region 设置保护工作表
/// <summary>
/// 设置工作表为只读
/// </summary>
/// <param name="pwd">密码</param>
public void Protect(object pwd)
{
_oSheet.Protect(pwd, _oSheet.ProtectDrawingObjects,
true, _oSheet.ProtectScenarios, _oSheet.ProtectionMode,
_oSheet.Protection.AllowFormattingCells,
_oSheet.Protection.AllowFormattingColumns,
_oSheet.Protection.AllowFormattingRows,
_oSheet.Protection.AllowInsertingColumns,
_oSheet.Protection.AllowInsertingRows,
_oSheet.Protection.AllowInsertingHyperlinks,
_oSheet.Protection.AllowDeletingColumns,
_oSheet.Protection.AllowDeletingRows,
_oSheet.Protection.AllowSorting,
_oSheet.Protection.AllowFiltering,
_oSheet.Protection.AllowUsingPivotTables);
}
/// <summary>
/// 撤销工作表为只读
/// </summary>
/// <param name="pwd">密码</param>
public void Unprotect(object pwd)
{
_oSheet.Unprotect(pwd);
}
#endregion
#region 保存Excel
/// <summary>
/// 保存Excel文件到指定的路径（如果文件存在，则覆盖。）
/// </summary>
/// <param name="fileName">文件名称（物理路径）</param>
/// <returns></returns>
public bool SaveAs(string fileName)
{
try
{
//_oBook.SaveCopyAs(fileName);
_oExcel.DisplayAlerts = false;
_oBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_oExcel.DisplayAlerts = true;
return true;
}
catch (Exception ex)
{
return false;
}
}
#endregion
#region 关闭Excel的工作薄
/// <summary>
/// 关闭Excel的工作薄
/// </summary>
public void Close()
{
if (_oBook == null)
return;
_oBook.Close(Type.Missing, Type.Missing, Type.Missing);
_oBook = null;
}
#endregion
#region 关闭Excel应用程序进程
/// <summary>
/// 杀死Excel进程
/// </summary>
public void Kill()
{
if (_oBook != null)
_oBook.Close(Type.Missing, Type.Missing, Type.Missing);
if (_oExcel != null)
_oExcel.Quit();
_oSheet = null;
_oBook = null;
_oExcel = null;
GC.Collect();
}
#endregion
}
