C#操作Excel的类以及其使用举例说明

1前言
这两天由于某种需要，研究了一下.NET中对Excel的互操作，之前主要是直接通过第三方的组件等方式来操作的Excel，这次仔细的研究了一下，对常用的Excel操作需求做了一个简单的总结，比如创建Excel，设置单元格样式，合并单元格，导入内存中的DataTable，插入图片、图表等。在此基础上借助于Microsoft.Office.Interop.Excel对这些操作进行了封装最终形成了ExcelHandler类。本文主要对自己封装的这个类进行简单的举例说明，关于此类的完整代码参见第三部分。
注意：对于命名空间Microsoft.Office.Interop.Excel，使用之前需要引用COM：Microsoft Office 11.0 Object Library（office 2003 ）或者Microsoft Office 12.0 Object Library（office 2007 ），如果引用列表中没有，需要自行添加 X:Program Files/Microsoft Office/OFFICE[11|12]/EXCEL.EXE的引用。如果已经安装VSTO(Visual Studio Tools For Office)，亦可直接添加对X:/Program Files/Microsoft Visual Studio [9.0|8.0]/Visual Studio Tools for Office/PIA/Office[11|12]/Microsoft.Office.Interop.Excel.dll的引用。

2ExcelHandler类举例说明
为了展示该类的使用方法，新建了一个WinForm的测试项目进行测试，项目名称可自定，添加对ExcelHandler的dll的引用。所有测试代码均放在窗体的一个按钮单击事件的处理方法中。

2.1创建Excel文件

 /// <summary>
/// 测试ExcelHandler类
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonTest_Click(object sender, EventArgs e)
{
string excelFilePath = string.Format("{0}Excel-{1}.xls", AppDomain.CurrentDomain.BaseDirectory, DateTime.Now.ToString("yyyyMMddHHmmss"));

using (ExcelHandler handler = new ExcelHandler(excelFilePath, true))
{
handler.OpenOrCreate();
MessageBox.Show("创建Excel成功！");
handler.Save();
MessageBox.Show(string.Format("保存Excel成功！Excel路径:{0}", excelFilePath));
} 
}
点击按钮执行后，可以看到在Debug目录下多了一个Excel文件

此文件的完整路径为:F:/My Projects/Tanging.DataVisualization/Tanging.DataVisualization/bin/Debug/Excel-20100314181502.xls。
注意：之后的例子的Excel的文件的路径需要为你自己创建的Excel的相应路径。
下面将举例说明对此Excel文件进行操作。

2.2创建自己的工作表Worksheet
 private void buttonTest_Click(object sender, EventArgs e)
{
string excelFilePath = string.Format("{0}Excel-20100314181502.xls", AppDomain.CurrentDomain.BaseDirectory);

using (ExcelHandler handler = new ExcelHandler(excelFilePath, false))//设置第二个参数为false表示直接打开现有的Excel文档
{
handler.OpenOrCreate();
//创建一个Worksheet
Worksheet sheet = handler.AddWorksheet("TestSheet");
//删除除TestSheet之外的其余Worksheet
handler.DeleteWorksheetExcept(sheet);
handler.Save();
}
}
再次打开创建的Excel，可以看到新建的Worksheet

2.3单元格、Range等的操作

下面设置A1到E5的单元格样式，并且设置单元格值等
private void buttonTest_Click(object sender, EventArgs e)
{
string excelFilePath = string.Format("{0}Excel-20100314181502.xls", AppDomain.CurrentDomain.BaseDirectory);

using (ExcelHandler handler = new ExcelHandler(excelFilePath, false))//设置第二个参数为false表示直接打开现有的Excel文档
{
handler.OpenOrCreate();
//获得Worksheet对象
Worksheet sheet = handler.GetWorksheet("TestSheet");
//A1-E5
Range range = handler.GetRange(sheet, 1, 1, 5, 5);
handler.SetRangeFormat(range);

handler.SetCellValue(sheet, 1, 1, "测试");
handler.SetCellValue(sheet, 2, 1, "测试2");

range.Font.Bold = true;//加粗

handler.Save();
}
}
效果如下：

 

2.4导入DataTable

代码如下：
private void buttonTest_Click(object sender, EventArgs e)
{
string excelFilePath = string.Format("{0}Excel-20100314181502.xls", AppDomain.CurrentDomain.BaseDirectory);

using (ExcelHandler handler = new ExcelHandler(excelFilePath, false))//设置第二个参数为false表示直接打开现有的Excel文档
{
handler.OpenOrCreate();
//获得Worksheet对象
Worksheet sheet = handler.GetWorksheet("TestSheet");
//A1-E5
Range range = handler.GetRange(sheet, 1, 1, 5, 5);
handler.SetRangeFormat(range);
range.Font.Bold = true;

System.Data.DataTable table = new System.Data.DataTable();
table.Columns.AddRange(new DataColumn[] { new DataColumn("测试列1"), new DataColumn("测试列2"), new DataColumn("测试列3") });
Random random = new Random(20);
for (int i = 0; i < 10; i++)
{
table.Rows.Add(random.Next(10000), random.Next(10000), random.Next(10000));
}

//从第6行第一列导入数据
handler.ImportDataTable(sheet, "测试导入表格", true, new string[] { "测试列1", "测试列2", "测试列3" }, 6, 1, table);

handler.Save();
}
}

结果如下图：

2.5 插入图片
下面插入一张图片到Excel的第一行第五列，代码如下：
private void buttonTest_Click(object sender, EventArgs e)
{
string excelFilePath = string.Format("{0}Excel-20100314181502.xls", AppDomain.CurrentDomain.BaseDirectory);

using (ExcelHandler handler = new ExcelHandler(excelFilePath, false))//设置第二个参数为false表示直接打开现有的Excel文档
{
handler.OpenOrCreate();
//获得Worksheet对象
Worksheet sheet = handler.GetWorksheet("TestSheet");

//图片地址
string filePath = string.Format("{0}test.png", AppDomain.CurrentDomain.BaseDirectory);

//从第1行第5列插入图片
Picture pic = handler.AddImage(sheet, filePath, 1, 5);


handler.Save();
}
}
效果如下图：

3ExcelHandler 类完整源码
/* ***********************************************
* Author:          Raymond Tang
* Email:           china.raymond@hotmail.com
* Blog:               http://blog.tanging.net
* Created Time:    2010-3-14 9:59:43
* Class:           Tanging.Interop.Excel.ExcelHandler
* ***********************************************/

using System;
using System.IO;
using System.Reflection;
using System.Text;
using Xls = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Collections.Generic;
using System.Runtime.InteropServices;

namespace Tanging.Interop.Excel
{
/// <summary>
/// Excel处理类
/// </summary>
/// <remarks>可以用于创建Excel，操作工作表，设置单元格样式对齐方式等，导入内存、数据库中的数据表，插入图片到Excel等</remarks>
public sealed class ExcelHandler : IDisposable
{
#region [构造函数]

/// <summary>
/// ExcelHandler的构造函数
/// </summary>
/// <param name="fileName">Excel文件名，绝对路径</param>
public ExcelHandler(string fileName)
: this(fileName, false)
{

}

/// <summary>
/// 创建ExcelHandler对象，指定文件名以及是否创建新的Excel文件
/// </summary>
/// <param name="fileName">Excel文件名，绝对路径</param>
/// <param name="createNew">是否创建新的Excel文件</param>
public ExcelHandler(string fileName, bool createNew)
{
this.FileName = fileName;
this.mCreateNew = createNew;
}

#endregion

#region [字段和属性]

private static readonly object missing = Missing.Value;

private string mFileName;
/// <summary>
/// Excel文件名
/// </summary>
public string FileName
{
get { return mFileName; }
set { mFileName = value; }
}
/// <summary>
/// 是否新建Excel文件
/// </summary>
private bool mCreateNew;

private Xls.Application mApp;
/// <summary>
/// 当前Excel应用程序
/// </summary>
public Xls.Application App
{
get { return mApp; }
set { mApp = value; }
}

private Xls.Workbooks mAllWorkbooks;
/// <summary>
/// 当前Excel应用程序所打开的所有Excel工作簿
/// </summary>
public Xls.Workbooks AllWorkbooks
{
get { return mAllWorkbooks; }
set { mAllWorkbooks = value; }
}

private Xls.Workbook mCurrentWorkbook;
/// <summary>
/// 当前Excel工作簿
/// </summary>
public Xls.Workbook CurrentWorkbook
{
get { return mCurrentWorkbook; }
set { mCurrentWorkbook = value; }
}

private Xls.Worksheets mAllWorksheets;
/// <summary>
/// 当前Excel工作簿内的所有Sheet
/// </summary>
public Xls.Worksheets AllWorksheets
{
get { return mAllWorksheets; }
set { mAllWorksheets = value; }
}

private Xls.Worksheet mCurrentWorksheet;
/// <summary>
/// 当前Excel中激活的Sheet
/// </summary>
public Xls.Worksheet CurrentWorksheet
{
get { return mCurrentWorksheet; }
set { mCurrentWorksheet = value; }
}

#endregion

#region [初始化操作，打开或者创建文件]
/// <summary>
/// 初始化，如果不创建新文件直接打开，否则创建新文件
/// </summary>
public void OpenOrCreate()
{
this.App = new Xls.ApplicationClass();
this.AllWorkbooks = this.App.Workbooks;

if (!this.mCreateNew)//直接打开
{
if (!File.Exists(this.FileName))
{
throw new FileNotFoundException("找不到指定的Excel文件，请检查路径是否正确！", this.FileName);
}

this.CurrentWorkbook = this.AllWorkbooks.Open(this.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Xls.XlPlatform.xlWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

}
else//创建新文件
{
if (File.Exists(this.FileName))
{
File.Delete(this.FileName);
}
this.CurrentWorkbook = this.AllWorkbooks.Add(Type.Missing);

}

this.AllWorksheets = this.CurrentWorkbook.Worksheets as Xls.Worksheets;
this.CurrentWorksheet = this.CurrentWorkbook.ActiveSheet as Xls.Worksheet;
this.App.DisplayAlerts = false;
this.App.Visible = false;
}
#endregion

#region [Excel Sheet相关操作等]

/// <summary>
/// 根据工作表名获取Excel工作表对象的引用
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public Xls.Worksheet GetWorksheet(string sheetName)
{
return this.CurrentWorkbook.Sheets[sheetName] as Xls.Worksheet;
}

/// <summary>
/// 根据工作表索引获取Excel工作表对象的引用
/// </summary>
/// <param name="index"></param>
/// <returns></returns>
public Xls.Worksheet GetWorksheet(int index)
{
return this.CurrentWorkbook.Sheets.get_Item(index) as Xls.Worksheet;
}

/// <summary>
/// 给当前工作簿添加工作表并返回的方法重载,添加工作表后不使其激活
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public Xls.Worksheet AddWorksheet(string sheetName)
{
return this.AddWorksheet(sheetName, false);
}

/// <summary>
/// 给当前工作簿添加工作表并返回
/// </summary>
/// <param name="sheetName">工作表名</param>
/// <param name="activated">创建后是否使其激活</param>
/// <returns></returns>
public Xls.Worksheet AddWorksheet(string sheetName, bool activated)
{
Xls.Worksheet sheet = this.CurrentWorkbook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing) as Xls.Worksheet;
sheet.Name = sheetName;
if (activated)
{
sheet.Activate();
}
return sheet;
}

/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="sheet">工作表对象</param>
/// <param name="newName">工作表新名称</param>
/// <returns></returns>
public Xls.Worksheet RenameWorksheet(Xls.Worksheet sheet, string newName)
{
sheet.Name = newName;
return sheet;
}

/// <summary>
/// 重命名工作表
/// </summary>
/// <param name="oldName">原名称</param>
/// <param name="newName">新名称</param>
/// <returns></returns>
public Xls.Worksheet RenameWorksheet(string oldName, string newName)
{
Xls.Worksheet sheet = this.GetWorksheet(oldName);
return this.RenameWorksheet(sheet, newName);
}

/// <summary>
/// 删除工作表
/// </summary>
/// <param name="sheetName">工作表名</param>
public void DeleteWorksheet(string sheetName)
{
if (this.CurrentWorkbook.Worksheets.Count <= 1)
{
throw new InvalidOperationException("工作簿至少需要一个可视化的工作表！");
}
this.GetWorksheet(sheetName).Delete();
}

/// <summary>
/// 删除除参数sheet指定外的其余工作表
/// </summary>
/// <param name="sheet"></param>
public void DeleteWorksheetExcept(Xls.Worksheet sheet)
{
foreach (Xls.Worksheet ws in this.CurrentWorkbook.Worksheets)
{
if (sheet != ws)
{
ws.Delete();
}
}
}


#endregion

#region [单元格，Range相关操作]

/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="rowNumber">单元格行号</param>
/// <param name="columnNumber">单元格列号</param>
/// <param name="value">单元格值</param>
public void SetCellValue(Xls.Worksheet sheet, int rowNumber, int columnNumber, object value)
{
sheet.Cells[rowNumber, columnNumber] = value;
}

/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="rowNumber1">第一个单元格行号</param>
/// <param name="columnNumber1">第一个单元格列号</param>
/// <param name="rowNumber2">结束单元格行号</param>
/// <param name="columnNumber2">结束单元格列号</param>
public void MergeCells(Xls.Worksheet sheet, int rowNumber1, int columnNumber1, int rowNumber2, int columnNumber2)
{
Xls.Range range = this.GetRange(sheet, rowNumber1, columnNumber1, rowNumber2, columnNumber2);
range.Merge(Type.Missing);
}

/// <summary>
/// 获取Range对象
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="rowNumber1">第一个单元格行号</param>
/// <param name="columnNumber1">第一个单元格列号</param>
/// <param name="rowNumber2">结束单元格行号</param>
/// <param name="columnNumber2">结束单元格列号</param>
/// <returns></returns>
public Xls.Range GetRange(Xls.Worksheet sheet, int rowNumber1, int columnNumber1, int rowNumber2, int columnNumber2)
{
return sheet.get_Range(sheet.Cells[rowNumber1, columnNumber1], sheet.Cells[rowNumber2, columnNumber2]);
}
#endregion

#region [设置单元格、Range的样式、对齐方式自动换行等]

/// <summary>
/// 自动调整，设置自动换行以及自动调整列宽
/// </summary>
/// <param name="range"></param>
public void AutoAdjustment(Xls.Range range)
{
range.WrapText = true;
range.AutoFit();
}

/// <summary>
/// 设置Range的单元格样式
/// </summary>
/// <remarks>将各项值设置为默认值</remarks>
/// <param name="range"></param>
public void SetRangeFormat(Xls.Range range)
{
this.SetRangeFormat(range, 11, Xls.Constants.xlAutomatic, Xls.Constants.xlColor1, Xls.Constants.xlLeft);
}

/// <summary>
/// 设置Range的单元格样式
/// </summary>
/// <remarks>将各项值设置为默认值</remarks>
/// <param name="sheet"></param>
/// <param name="rowNumber1"></param>
/// <param name="columnNumber1"></param>
/// <param name="rowNumber2"></param>
/// <param name="columNumber2"></param>
public void SetRangeFormat(Xls.Worksheet sheet, int rowNumber1, int columnNumber1, int rowNumber2, int columNumber2)
{
this.SetRangeFormat(sheet, rowNumber1, columnNumber1, rowNumber2, columNumber2, 11, Xls.Constants.xlAutomatic);
}

/// <summary>
/// 设置Range的单元格样式
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowNumber1">第一个单元格行号</param>
/// <param name="columnNumber1">第一个单元格列号</param>
/// <param name="rowNumber2">结束单元格行号</param>
/// <param name="columnNumber2">结束单元格列号</param>
/// <param name="fontSize"></param>
/// <param name="fontName"></param>
public void SetRangeFormat(Xls.Worksheet sheet, int rowNumber1, int columnNumber1, int rowNumber2, int columNumber2, object fontSize, object fontName)
{
this.SetRangeFormat(this.GetRange(sheet, rowNumber1, columnNumber1, rowNumber2, columNumber2), fontSize, fontName, Xls.Constants.xlColor1, Xls.Constants.xlLeft);
}

/// <summary>
/// 设置Range的单元格样式
/// </summary>
/// <param name="range">Range对象</param>
/// <param name="fontSize">字体大小</param>
/// <param name="fontName">字体名称</param>
/// <param name="color">字体颜色</param>
/// <param name="horizontalAlignment">水平对齐方式</param>
public void SetRangeFormat(Xls.Range range, object fontSize, object fontName, Xls.Constants color, Xls.Constants horizontalAlignment)
{
range.Font.Color = color;
range.Font.Size = fontSize;
range.Font.Name = fontName;
range.HorizontalAlignment = horizontalAlignment;
}




#endregion

#region [导入内存中的DataTable]


/// <summary>
/// 导入内存中的数据表到Excel中
/// </summary>
/// <remarks>直接导入到工作表的最起始部分</remarks>
/// <param name="sheet"></param>
/// <param name="headerTitle"></param>
/// <param name="showTitle"></param>
/// <param name="headers"></param>
/// <param name="table"></param>
public void ImportDataTable(Xls.Worksheet sheet, string headerTitle, bool showTitle, object[] headers, DataTable table)
{
this.ImportDataTable(sheet, headerTitle, showTitle, headers, 1, 1, table);
}

/// <summary>
/// 导入内存中的数据表到Excel中
/// </summary>
/// <remarks>直接导入到工作表的最起始部分，且不显示标题行</remarks>
/// <param name="sheet"></param>
/// <param name="headers"></param>
/// <param name="table"></param>
public void ImportDataTable(Xls.Worksheet sheet, object[] headers, DataTable table)
{
this.ImportDataTable(sheet, null, false, headers, table);

}

/// <summary>
/// 导入内存中的数据表到Excel中
/// </summary>
/// <remarks>标题行每一列与DataTable标题一致</remarks>
/// <param name="sheet"></param>
/// <param name="table"></param>
public void ImportDataTable(Xls.Worksheet sheet, DataTable table)
{
List<string> headers = new List<string>();
foreach (DataColumn column in table.Columns)
{
headers.Add(column.Caption);
}
this.ImportDataTable(sheet, headers.ToArray(), table);
}


/// <summary>
/// 导入内存中的数据表到Excel中
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="headerTitle">表格标题</param>
/// <param name="showTitle">是否显示表格标题行</param>
/// <param name="headers">表格每一列的标题</param>
/// <param name="rowNumber">插入表格的起始行号</param>
/// <param name="columnNumber">插入表格的起始列号</param>
/// <param name="table">内存中的数据表</param>
public void ImportDataTable(Xls.Worksheet sheet, string headerTitle, bool showTitle, object[] headers, int rowNumber, int columnNumber, DataTable table)
{
int columns = table.Columns.Count;
int rows = table.Rows.Count;

int titleRowIndex = rowNumber;
int headerRowIndex = rowNumber;
Xls.Range titleRange = null;

if (showTitle)
{
headerRowIndex++;
//添加标题行，并设置样式
titleRange = this.GetRange(sheet, rowNumber, columnNumber, rowNumber, columnNumber + columns - 1);
titleRange.Merge(missing);

this.SetRangeFormat(titleRange, 16, Xls.Constants.xlAutomatic, Xls.Constants.xlColor1, Xls.Constants.xlCenter);
titleRange.Value2 = headerTitle;
}

//添加表头
int m = 0;
foreach (object header in headers)
{
this.SetCellValue(sheet, headerRowIndex, columnNumber + m, header);
m++;
}

//添加每一行的数据
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
sheet.Cells[headerRowIndex + i + 1, j + columnNumber] = table.Rows[i][j];
}
}

}

#endregion

#region [插入图片到Excel中的相关方法]
/// <summary>
/// 插入图片
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="imageFilePath">图片的绝对路径</param>
/// <param name="rowNumber">单元格行号</param>
/// <param name="columnNumber">单元格列号</param>
/// <returns></returns>
public Xls.Picture AddImage(Xls.Worksheet sheet, string imageFilePath, int rowNumber, int columnNumber)
{
Xls.Range range = this.GetRange(sheet, rowNumber, columnNumber, rowNumber, columnNumber);
range.Select();
Xls.Pictures pics = sheet.Pictures(missing) as Xls.Pictures;
Xls.Picture pic = pics.Insert(imageFilePath, missing);
pic.Left = (double)range.Left;
pic.Top = (double)range.Top;
return pic;
}

/// <summary>
/// 插入图片
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="imageFilePath">图片的绝对路径</param>
/// <param name="rowNumber">单元格行号</param>
/// <param name="columnNumber">单元格列号</param>
/// <param name="width">图片的宽度</param>
/// <param name="height">图片的高度</param>
/// <returns></returns>
public Xls.Picture AddImage(Xls.Worksheet sheet, string imageFilePath, int rowNumber, int columnNumber, double width, double height)
{
Xls.Picture pic = this.AddImage(sheet, imageFilePath, rowNumber, columnNumber);
pic.Width = width;
pic.Height = height;
return pic;
}

/ <summary>
/ 插入图片
/ </summary>
/ <remarks>从流中读取图片</remarks>
/ <param name="sheet"></param>
/ <param name="imageStream"></param>
/ <param name="x"></param>
/ <param name="y"></param>
/ <param name="width"></param>
/ <param name="height"></param>
/ <returns></returns>
//public Xls.Picture AddImage(Xls.Worksheet sheet, Stream imageStream, int x, int y, double width, double height)
//{

//}

#endregion

#region [保存Excel]

/// <summary>
/// 保存Excel
/// </summary>
public void Save()
{
if (this.mCreateNew)
{
this.SaveAs(this.FileName);
}
else
{
this.CurrentWorkbook.Save();
}

//this.SaveAs(this.FileName);
}

/// <summary>
/// 保存Excel
/// </summary>
/// <param name="filePath">文件的绝对路径</param>
public void SaveAs(string filePath)
{
this.CurrentWorkbook.SaveAs(filePath, Xls.XlFileFormat.xlWorkbookNormal, missing, missing, missing,
missing, Xls.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
}

#endregion


#region [公共帮助方法]
/// <summary>
/// 更新Uri
/// </summary>
public string UpdateUri
{
get
{
return "http://hi.baidu.com/1987raymond";
}
}

#endregion

#region [IDisposable 成员]
/// <summary>
/// 对象销毁时执行的操作
/// </summary>
public void Dispose()
{

//this.CurrentWorkbook.Close(true, this.FileName, missing);
Marshal.FinalReleaseComObject(this.CurrentWorkbook);
this.CurrentWorkbook = null;

this.App.Quit();
Marshal.FinalReleaseComObject(this.App);
this.App = null;

System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}

#endregion
}
}