公司产品最近需要实现一个功能,将内存中对象列表内保存的数据输出为Excel格式,为了满足各种Excel导出需求,现实现了一个Excel操作的基础类,然后通过继承这个基础类实现特定的操作,详细如下:
一:目的
将内存中对象保存的数据导出为模板格式的Excel文件,用户指定存储路径,导出图片格式以及单位格式。
二:实现手段
通过OFFICE自动化实现,需要使用到Microsoft.Office.Interop.Excel动态链接库
三:实现类设计
1.ObjToExcel基类,实现IDisposable接口清理资源
基类实现对Excel的基本操作,包括
(1) 启动Excel进程,指定进程是否可见
(2) 结束Excel进程并清理所有资源
(3) 向工作表集合中添加、删除工作表
(4) 新增、删除、选择工作表
(5) 打开指定路径的工作表
(6) 新增、删除工作簿(按名称或编号)
(7) 重名工作簿(根据工作簿所在工作表的位置编号)
(8) 选择工作簿内指定位置的单元格集合
(9) 为指定的单元格集合赋值(二维数组)
(10) 将工作表保存至用户指定的位置
该类将来需要扩展将图片导入到指定单元格区域的方法
变量 | |
protected Excel.Application app; | Excel进程 |
protected Excel.Workbooks oWBS | 工作表集合 |
protected Excel.Workbook oWB; | 工作表 |
protected Excel.Worksheet oWS | 工作簿 |
protected Excel.Range oRange; | 工作簿单元格区域 |
protected readonly object missing = System.Reflection.Missing.Value; | 参数missing |
构造函数 | |
protected Object2Excel() | 默认构造函数,启动Excel进程不显示 |
protected Object2Excel(bool visible) | 用户指定启动Excel进程是否显示 |
方法 | |
protected void AddWorkBook() | 向工作表集合中添加工作表 |
protected void RemoveWorkBook(int index) | 向工作表集合中删除工作表 |
protected WorkBook GetWorkBook(int index) | 在工作表集合中选择指定编号工作表 |
protected WorkBook OpenExcel(string excelPath) | 打开指定磁盘位置的Excel文件 |
protected void CreatWorkBook(string sheet1Name,string sheet2Name,string sheet3Name) | 向工作表集合中添加指定工作簿名称的工作表,如制定空字符串,名称为默认名称 |
protected void AddWorkSheet(string sheetName) | 添加工作簿 |
protected void DelWorkSheet(int index) | 删除工作簿 |
protected void ReName(string sheetName,int index) | 重命名工作簿 |
protected Range SetRange(Int32 marginTop, Int32 marginLeft,Int32 rowsCount, Int32 columnsCount) | 指定距离顶部和左边单元格边距的单元格为Range起始位置,根据行数和列数指定Range的区域大小 |
protected Range SetRange(string beginCell,string endCell) | 指定起始单元格和结束单元格获得Range的区域大小 |
protected void ArrayToExcel(int workSheetIndex, object[,] ,Range oRange) | 指定保存数据的WorkSheet编号,将用户的二维数组导出到指定的Range对象 |
protected void Save(string filePath) | 将Excel保存至指定路径 |
具体实现代码:
using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
namespace LoadMaster.IO.ObjToExcel
{
public class Object2Excel : IDisposable
{
#region 声明私有变量
protected Excel.Application app;
protected Excel.Workbooks oWBS;
protected Excel.Workbook oWB;
protected Excel.Worksheet oWS;
protected Excel.Range oRange;
protected readonly object missing = System.Reflection.Missing.Value;
#endregion
#region 构造函数
public Object2Excel()
{
app = new Excel.Application();
app.Visible = false;
app.ScreenUpdating = false;
oWBS = app.Workbooks;
}
/// <summary>
/// 启动一个Excel进程,并指定该进程是否可见
/// </summary>
/// <param name="visible">指定进程是否可见</param>
public Object2Excel(bool visible)
{
app = new Excel.Application();
app.Visible = visible;
app.ScreenUpdating = visible;
oWBS = app.Workbooks;
}
#endregion
#region 基本操作方法
protected void AddWorkBook()
{
app.Workbooks.Add(Type.Missing);
oWB = app.ActiveWorkbook;
}
/// <summary>
/// 移除指定位置的WorkBook
/// </summary>
/// <param name="index"></param>
protected void RemoveWorkBook(int index)
{
try
{
app.Workbooks.Item[index].Close();
}
catch
{
Console.WriteLine("在指定位置没有找到WorkBook");
}
}
/// <summary>
/// 查找指定位置的WorkBook
/// </summary>
/// <param name="index"></param>
/// <returns></returns>
protected void GetWorkBook(int index)
{
try
{
oWB = app.Workbooks.Item[index];
oWB = app.ActiveWorkbook;
}
catch
{
Console.WriteLine("在指定位置没有找到WorkBook");
}
}
/// <summary>
/// 打开现有的Excel文档
/// </summary>
/// <param name="excelPath">指定现有Excel文档的路径</param>
protected void OpenExcel(string excelPath)
{
if (app == null)
{
app = new Excel.Application();
app.Visible = false;
app.ScreenUpdating = false;
oWBS = app.Workbooks;
}
try
{
oWB = oWBS.Open(excelPath, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
}
catch
{
Console.WriteLine("文件路径不存在");
}
}
/// <summary>
/// 新增一个WorkBook,默认有三个WorkSheet
/// </summary>
/// <param name="sheet1Name">第一个WorkSheet的名称</param>
/// <param name="sheet2Name">第二个WorkSheet的名称</param>
/// <param name="sheet3Name">第三个WorkSheet的名称</param>
protected void CreatWorkBook(string sheet1Name, string sheet2Name, string sheet3Name)
{
oWB = app.Workbooks.Add(Type.Missing);
oWB = app.ActiveWorkbook;
if (sheet1Name != "")
((Excel.Worksheet)oWB.Worksheets.Item[1]).Name = sheet1Name;
if (sheet2Name != "")
((Excel.Worksheet)oWB.Worksheets.Item[2]).Name = sheet2Name;
if (sheet3Name != "")
((Excel.Worksheet)oWB.Worksheets.Item[3]).Name = sheet3Name;
}
/// <summary>
/// 在指定WorkBook中新增一个WorkSheet
/// </summary>
/// <param name="sheetName">新增WorkSheet的名称</param>
protected void AddWorkSheet(string sheetName)
{
if (oWB == null)
{
Console.WriteLine("没有打开工作表");
return;
}
oWB.Worksheets.Add(missing, missing, missing, missing);
}
/// <summary>
/// 删除指定WorkBook中的WorkSheet
/// </summary>
/// <param name="index">需要删除的WorkSheet的编号</param>
protected void DelWorkSheet(int index)
{
if (oWB == null)
{
Console.WriteLine("没有打开工作表");
return;
}
if (oWB.Worksheets.Count < index)
{
Console.WriteLine("没有指定编号的WorkSheet");
return;
}
app.DisplayAlerts = false;
((Excel.Worksheet)oWB.Worksheets[index]).Delete();
app.DisplayAlerts = true;
}
/// <summary>
/// 更改指定编号的WorkSheet名称
/// </summary>
/// <param name="sheetName">更改后的名称</param>
/// <param name="index">WorkSheet编号</param>
protected void ReName(string sheetName, int index)
{
if (oWB == null)
{
Console.WriteLine("没有打开工作表");
return;
}
try
{
((Excel.Worksheet)oWB.Worksheets[index]).Name = sheetName;
}
catch
{
Console.WriteLine("指定位置不存在WorkSheet");
}
}
/// <summary>
/// 根据WorkSheet编号获取WorkSheet对象
/// </summary>
/// <param name="index"></param>
protected void GetWorkSheet(int index)
{
if (oWB == null)
{
Console.WriteLine("没有打开工作表");
return;
}
if (oWB.Worksheets.Count < index)
{
Console.WriteLine("指定的编号超出工作表的所有工作簿");
return;
}
oWS = (Excel.Worksheet)oWB.Worksheets.get_Item(index);
}
/// <summary>
/// 选中Range区域
/// </summary>
/// <param name="marginTop">该区域距离上边距的单元格数量</param>
/// <param name="marginLeft">该区域距离左边距的单元格数量</param>
/// <param name="rowsCount">该区域的总行数</param>
/// <param name="columnsCount">该区域的总列数</param>
/// <returns></returns>
protected void SetRange(Int32 marginTop, Int32 marginLeft, Int32 rowsCount, Int32 columnsCount)
{
if (oWB == null)
{
Console.WriteLine("没有打开工作表");
}
if (oWS == null)
{
Console.WriteLine("没有活动的WorkSheet");
}
if (marginTop + rowsCount > 65536)
{
Console.WriteLine("顶部边距超出最大值");
}
if (marginLeft + columnsCount > 256)
{
Console.WriteLine("左边距超出最大值");
}
oRange = (Excel.Range)oWS.Cells[marginTop, marginLeft]; //起始位置
oRange = oRange.get_Resize(rowsCount, columnsCount); //计算Range的总区域大小
}
protected void SetRange(int workSheetIndex, Int32 marginTop, Int32 marginLeft, Int32 rowsCount, Int32 columnsCount)
{
if (oWB == null)
{
Console.WriteLine("没有打开工作表");
}
if (oWS == null)
{
oWS = (Excel.Worksheet)oWB.Worksheets.Item[workSheetIndex];
}
if (marginTop + rowsCount > 65536)
{
Console.WriteLine("顶部边距超出最大值");
}
if (marginLeft + columnsCount > 256)
{
Console.WriteLine("左边距超出最大值");
}
oRange = (Excel.Range)oWS.Cells[marginTop, marginLeft]; //起始位置
oRange = oRange.get_Resize(rowsCount, columnsCount); //计算Range的总区域大小
}
/// <summary>
/// 指定Range区域
/// </summary>
/// <param name="beginCell">该区域的起始单元格编号</param>
/// <param name="endCell">该区域的结束单元格编号</param>
/// <returns></returns>
protected void SetRange(string beginCell, string endCell)
{
try
{
oRange = oRange.get_Range(beginCell, endCell); //计算Range的总区域大小
}
catch
{
Console.WriteLine("单元格表示格式不正确");
}
}
/// <summary>
/// 将二维数组的值赋给指定的Range区域
/// </summary>
/// <param name="workSheetIndex">指定WorkSheet的编号</param>
/// <param name="arr">二维数组</param>
/// <param name="ran">指定的Range区域</param>
protected void ArrayToExcel(int workSheetIndex, object[,] arr, Excel.Range ran)
{
if (oWB == null)
{
Console.WriteLine("没有打开WorkBook");
return;
}
if (oWB.Worksheets.Count < workSheetIndex)
{
Console.WriteLine("WorkBook中的WorkSheet个数少于" + workSheetIndex.ToString() + "个");
return;
}
oWS = (Excel.Worksheet)oWB.Worksheets.get_Item(workSheetIndex);
ran.NumberFormat = "@";
ran.Value2 = arr;
}
protected void ArrayToExcel(object[,] oArr)
{
oRange.Value2 = oArr;
}
/// <summary>
/// 保存Excel文档
/// </summary>
/// <param name="filePath">指定保存文档的路径</param>
protected void Save(string filePath)
{
try
{
if (oWB == null)
{
Console.WriteLine("没有打开WorkBook");
return;
}
oWB.SaveAs(filePath);
}
catch (Exception e)
{
throw e;
}
finally
{
Dispose();
}
}
/// <summary>
/// 注销资源,已经在保存Excel中实现,禁止在外部调用该方法
/// </summary>
public void Dispose()
{
if (app != null)
app.Quit();
if (oRange != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oRange);
oRange = null;
}
if (oWS != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWS);
oWS = null;
}
if (oWB != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
oWB = null;
}
if (oWBS != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWBS);
oWBS = null;
}
if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();
}
protected void ArrayToExcel(string filePath, int workSheetIndex, Int32 rangeMarginTop, Int32 rangeMarginLeft, Int32 rangeRowsCount, Int32 rangeColumnsCount, object[,] oArr)
{
OpenExcel(filePath);
GetWorkBook(workSheetIndex);
SetRange(rangeMarginTop, rangeMarginLeft, rangeRowsCount, rangeColumnsCount);
oRange.Value2 = oArr;
}
protected void ArrayToExcel(int workSheetIndex, Int32 rangeMarginTop, Int32 rangeMarginLeft, Int32 rangeRowsCount, Int32 rangeColumnsCount, object[,] oArr)
{
try
{
oWS = (Excel.Worksheet)oWB.Worksheets.get_Item(workSheetIndex);
SetRange(rangeMarginTop, rangeMarginLeft, rangeRowsCount, rangeColumnsCount);
oRange.Value2 = oArr;
}
catch
{
Dispose();
}
}
#endregion
}
}