c sharp 操作office软件相关.doc

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/OFFICE11|12/EXCEL.EXE的引用。如果已经安装VSTO(Visual Studio Tools For Office)，亦可直接添加对X:Program FilesMicrosoft Visual Studio 9.0|8.0Visual Studio Tools for OfficePIAOffice11|12Microsoft.Office.Interop.Excel.dll的引用。2ExcelHandler类举例说明为了展示该类的使用方法，新建了一个WinForm的测试项目进行测试，项目名称可自定，添加对ExcelHandler的dll的引用。所有测试代码均放在窗体的一个按钮单击事件的处理方法中。2.1创建Excel文件/ / 测试ExcelHandler类/ / / private void buttonTest_Click(object sender, EventArgs e)string excelFilePath = string.Format(0Excel-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 ProjectsTanging.DataVisualizationTanging.DataVisualizationbinDebugExcel-20100314181502.xls。注意：之后的例子的Excel的文件的路径需要为你自己创建的Excel的相应路径。下面将举例说明对此Excel文件进行操作。2.2创建自己的工作表Worksheetprivate void buttonTest_Click(object sender, EventArgs e)string excelFilePath = string.Format(0Excel-20100314181502.xls, AppDomain.CurrentDomain.BaseDirectory);using (ExcelHandler handler = new ExcelHandler(excelFilePath, false)/设置第二个参数为false表示直接打开现有的Excel文档handler.OpenOrCreate();/创建一个WorksheetWorksheet sheet = handler.AddWorksheet(TestSheet);/删除除TestSheet之外的其余Worksheethandler.DeleteWorksheetExcept(sheet);handler.Save();再次打开创建的Excel，可以看到新建的Worksheet2.3单元格、Range等的操作下面设置A1到E5的单元格样式，并且设置单元格值等private void buttonTest_Click(object sender, EventArgs e)string excelFilePath = string.Format(0Excel-20100314181502.xls, AppDomain.CurrentDomain.BaseDirectory);using (ExcelHandler handler = new ExcelHandler(excelFilePath, false)/设置第二个参数为false表示直接打开现有的Excel文档handler.OpenOrCreate();/获得Worksheet对象Worksheet sheet = handler.GetWorksheet(TestSheet);/A1-E5Range 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(0Excel-20100314181502.xls, AppDomain.CurrentDomain.BaseDirectory);using (ExcelHandler handler = new ExcelHandler(excelFilePath, false)/设置第二个参数为false表示直接打开现有的Excel文档handler.OpenOrCreate();/获得Worksheet对象Worksheet sheet = handler.GetWorksheet(TestSheet);/A1-E5Range 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(0Excel-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(0test.png, AppDomain.CurrentDomain.BaseDirectory);/从第1行第5列插入图片Picture pic = handler.AddImage(sheet, filePath, 1, 5);handler.Save();效果如下图：3ExcelHandler 类完整源码/* * Author: Raymond Tang * Email: * Blog: * 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/ / Excel处理类/ / 可以用于创建Excel，操作工作表，设置单元格样式对齐方式等，导入内存、数据库中的数据表，插入图片到Excel等public sealed class ExcelHandler : IDisposable#region 构造函数/ / ExcelHandler的构造函数/ / Excel文件名，绝对路径public ExcelHandler(string fileName): this(fileName, false)/ / 创建ExcelHandler对象，指定文件名以及是否创建新的Excel文件/ / Excel文件名，绝对路径/ 是否创建新的Excel文件public ExcelHandler(string fileName, bool createNew)this.FileName = fileName;this.mCreateNew = createNew;#endregion#region 字段和属性private static readonly object missing = Missing.Value;private string mFileName;/ / Excel文件名/ public string FileNameget return mFileName; set mFileName = value; / / 是否新建Excel文件/ private bool mCreateNew;private Xls.Application mApp;/ / 当前Excel应用程序/ public Xls.Application Appget return mApp; set mApp = value; private Xls.Workbooks mAllWorkbooks;/ / 当前Excel应用程序所打开的所有Excel工作簿/ public Xls.Workbooks AllWorkbooksget return mAllWorkbooks; set mAllWorkbooks = value; private Xls.Workbook mCurrentWorkbook;/ / 当前Excel工作簿/ public Xls.Workbook CurrentWorkbookget return mCurrentWorkbook; set mCurrentWorkbook = value; private Xls.Worksheets mAllWorksheets;/ / 当前Excel工作簿内的所有Sheet/ public Xls.Worksheets AllWorksheetsget return mAllWorksheets; set mAllWorksheets = value; private Xls.Worksheet mCurrentWorksheet;/ / 当前Excel中激活的Sheet/ public Xls.Worksheet CurrentWorksheetget return mCurrentWorksheet; set mCurrentWorksheet = value; #endregion#region 初始化操作，打开或者创建文件/ / 初始化，如果不创建新文件直接打开，否则创建新文件/ 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相关操作等/ / 根据工作表名获取Excel工作表对象的引用/ / / public Xls.Worksheet GetWorksheet(string sheetName)return this.CurrentWorkbook.SheetssheetName as Xls.Worksheet;/ / 根据工作表索引获取Excel工作表对象的引用/ / / public Xls.Worksheet GetWorksheet(int index)return this.CurrentWorkbook.Sheets.get_Item(index) as Xls.Worksheet;/ / 给当前工作簿添加工作表并返回的方法重载,添加工作表后不使其激活/ / / public Xls.Worksheet AddWorksheet(string sheetName)return this.AddWorksheet(sheetName, false);/ / 给当前工作簿添加工作表并返回/ / 工作表名/ 创建后是否使其激活/ 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;/ / 重命名工作表/ / 工作表对象/ 工作表新名称/ public Xls.Worksheet RenameWorksheet(Xls.Worksheet sheet, string newName)sheet.Name = newName;return sheet;/ / 重命名工作表/ / 原名称/ 新名称/ public Xls.Worksheet RenameWorksheet(string oldName, string newName)Xls.Worksheet sheet = this.GetWorksheet(oldName);return this.RenameWorksheet(sheet, newName);/ / 删除工作表/ / 工作表名public void DeleteWorksheet(string sheetName)if (this.CurrentWorkbook.Worksheets.Count = 1)throw new InvalidOperationException(工作簿至少需要一个可视化的工作表！);this.GetWorksheet(sheetName).Delete();/ / 删除除参数sheet指定外的其余工作表/ / public void DeleteWorksheetExcept(Xls.Worksheet sheet)foreach (Xls.Worksheet ws in this.CurrentWorkbook.Worksheets)if (sheet != ws)ws.Delete();#endregion#region 单元格，Range相关操作/ / 设置单元格的值/ / 工作表/ 单元格行号/ 单元格列号/ 单元格值public void SetCellValue(Xls.Worksheet sheet, int rowNumber, int columnNumber, object value)sheet.CellsrowNumber, columnNumber = value;/ / 合并单元格/ / 工作表/ 第一个单元格行号/ 第一个单元格列号/ 结束单元格行号/ 结束单元格列号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);/ / 获取Range对象/ / 工作表/ 第一个单元格行号/ 第一个单元格列号/ 结束单元格行号/ 结束单元格列号/ public Xls.Range GetRange(Xls.Worksheet sheet, int rowNumber1, int columnNumber1, int rowNumber2, int columnNumber2)return sheet.get_Range(sheet.CellsrowNumber1, columnNumber1, sheet.CellsrowNumber2, columnNumber2);#endregion#region 设置单元格、Range的样式、对齐方式自动换行等/ / 自动调整，设置自动换行以及自动调整列宽/ / public void AutoAdjustment(Xls.Range range)range.WrapText = true;range.AutoFit();/ / 设置Range的单元格样式/ / 将各项值设置为默认值/ public void SetRangeFormat(Xls.Range range)this.SetRangeFormat(range, 11, Xls.Constants.xlAutomatic, Xls.Constants.xlColor1, Xls.Constants.xlLeft);/ / 设置Range的单元格样式/ / 将各项值设置为默认值/ / / / / 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);/ / 设置Range的单元格样式/ / / 第一个单元格行号/ 第一个单元格列号/ 结束单元格行号/ 结束单元格列号/ / 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);/ / 设置Range的单元格样式/ / Range对象/ 字体大小/ 字体名称/ 字体颜色/ 水平对齐方式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/ / 导入内存中的数据表到Excel中/ / 直接导入到工作表的最起始部分/ / / / / public void ImportDataTable(Xls.Worksheet sheet, string headerTitle, bool showTitle, object headers, DataTable table)this.ImportDataTable(sheet, headerTitle, showTitle, headers, 1, 1, table);/ / 导入内存中的数据表到Excel中/ / 直接导入到工作表的最起始部分，且不显示标题行/ / / public void ImportDataTable(Xls.Worksheet sheet, object headers, DataTable table)this.ImportDataTable(sheet, null, false, headers, table);/ / 导入内存中的数据表到Excel中/ / 标题行每一列与DataTable标题一致/ / public void ImportDataTable(Xls.Worksheet sheet, DataTable table)List headers = new List();foreach (DataColumn column in table.Columns)headers.Add(column.Caption);this.ImportDataTable(sheet, headers.ToArray(), table);/ / 导入内存中的数据表到Excel中/ / 工作表/ 表格标题/ 是否显示表格标题行/ 表格每一列的标题/ 插入表格的起始行号/ 插入表格的起始列号/ 内存中的数据表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.CellsheaderRowIndex + i + 1, j + columnNumber = table.Rowsij;#endregion#region 插入图片到Excel中的相关方法/ / 插入图片/ / 工作表/ 图片的绝对路径/ 单元格行号/ 单元格列号/ 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;/ / 插入图片/ / 工作表/ 图片的绝对路径/ 单元格行号/ 单元格列号/ 图片的宽度/ 图片的高度/ 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, columnNumbe