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

 

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

2010-03-14 09:09 P.M.

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 } }