Excel在日常办公中应用非常广泛，这次我接到一个案子，需要往Excel中写入数据并能够打印出固定格式，前前后后大概花了将近2星期才搞定，现在做一些分享。
一、关于操作Excel的几种方式
我们导出Excel，大抵是有两种方法，一种是在服务器端用一些类库或者COM组件直接生成Excel成品，其二是在后台只写入数据，而不写入具体格式，等用户下载完Excel之后再在客户端上利用vba生成Excel成品。
1.1使用“自动化”功能-后台生成成品
微软把后台使用COM组件称之为“自动化”，其实它本身是不建议这种用法，在257757 号文章（服务器端 Office 自动化注意事项）也明文标出“Microsoft 目前建议不要从任何无人参与的、非交互式客户端应用程序或组件（包括 ASP、DCOM 和 NT Service）中进行 Microsoft Office 应用程序的“自动化”，也不为此提供支持，因为 Office 在这种环境中运行时可能会出现不稳定的现象并且/或者会死锁。”
这种方法的优点是给用户更简洁的展现，毕竟原生的Excel成品比半成品来更容易接受，而且可以避免宏安全问题，万一客户端禁止了宏，就只能看到丑陋的模板界面+一坨数据。
缺点是需要服务端支持，编写成本也比较高（VBA有时候可以直接录制）。服务端支持体现在需要安装一些微软或者第三方的类库。我当时采用的是Office类库，也就是在安装有Office的机器上面使用Interop.Excel.dll这个中间动态链接库进行操作，需要麻烦的安全设置，效率低不说还会扯出Excel无法关闭的Bug。
关于效率低我深有体会，我使用这种方法生成5页数据大约2m，客户等的急死，其根本原因是由于COM组件在调用时候，每一个Range这种对象都会产生一个借口请求。
1.2、使用ADO.net传输数据+VBA控制模板和数据
这方法的优缺点正好与上文相反，由于只是写入数据，即通过ADO.net的连接方式INSERT一堆数据到Excel文件的隐藏Sheet里面去（别跟我说你不知道Excel可以隐藏某个Sheet），所以速度后台速度极快。前台Excel文件虽然需要VBA编程支持，但是在理解Excel模型之后也不是很难的事情。
1.3
选择哪种方式，取决于你的需求，如果你在Java平台下面并且输出文件页面格式不复杂，我推荐第一种，如果是.net平台又或者要处理复杂的页面样式，就选用第二种吧（我前期使用第一种，后来因为效率问题和无法关闭Excel的问题，重写逻辑，选用第二种）。
关于Excel导出方案的选择，微软官方也是不建议使用第一种方案，甚至不提供技术支持。它推荐了一些方案，包括使用报表导出Excel或者ADO.net方式导出（即第二种），具体文章见如何使用 Visual C# 2005 或 Visual C# .NET 向 Excel 工作簿传输数据。
二、Office Excel文档模型
在写操作Excel代码之前，需要了解一下Excel的文档模型，才能想当然的把代码写出来。
简单说来，我们只要了解Application 、Workbook 、Worksheet 、Range这四种类型，如果需要操作图像的话，还需要多了解一种Chart。
Application就是Excel实例，不仅仅是一个Excel文件，而是整体的Excel程序（Office都是MDI文档体系）。
WorkBook就是实质意义上的某个Excel文件，你可以进行保存操作等等。
Worksheet是某个工作簿类型。
Range是我们打交道最多的，可以理解成一个区域快，也就是常见的”A2:B5″这种表示方式。
了解这几种之后，我们就可以下手操作了。更详细的微软官方文档，可以在Excel 对象模型概述找到。
三、使用C#操作Excel
我虽然不推荐第一种，但是毕竟是一种解决方案。
需要使用的命名空间为using Excel = Microsoft.Office.Interop.Excel;（使用别名简化一下）
另外需要项目引用Office的类库，如果不是项目形式而是网站形式，则需要手动编译对应Interop.Excel.dll到网站bin目录下面，我使用Excel2007编译出这个链接库，版本为1.6.0.0，需要的可以点击Interop.Excel.dll下载。
3.1 编译Interop.Excel.dll
编译的方法出自于“使用Office组件读取Excel，引用Microsoft.Office.Interop.Excel出现的问题”
进入你的visual studio的sdk下的bin目录，找到TlbImp.exe文件，如果没有，请用光盘安装此文件，详细说明请参照MSDN。命令行(cmd)进入bin目录，运行TlbImp /out:Interop.Excel.dll Office安装目录+Excel.exe此时很可能会报错：TlbImp error: Unable to locate input type library: ‘c:\program files\mcrosoft office\office\EXCEL.EXE’
此问题很有可能是TlbImp的bug，不支持空格式的路径；（具体原因不明）不要紧，将Excel.exe拷贝入bin目录，直接运行TlbImp /out:Interop.Excel.dll Excel.exe,提示“Type library imported to Interop.Excel.dll路径”
在bin目录下找到Interop.Excel.dll文件。在你的visual studio里将其引用即可。
3.2 封装的一个C#操作Excel类库
我把我之前封装的C#操作Excel类库分享一下
注：这个类的Dispose仍然没有解决Excel文件生成之后Excel进程无法正常关闭的问题，目前看来只有杀死进程方法才能起实质性作用。
其实我觉得这个类库除了让我熟悉一下Excel模型之外，并没派上实质的用场~
/// <summary>/// Excel帮助类/// Add by alswl 20101130/// </summary>public class ExcelHelper : IDisposable{ private Excel._Application excelApplication = null; public Excel._Workbook workbook = null; public Excel._Worksheet worksheet = null; private object missing = System.Reflection.Missing.Value; public ExcelHelper() { if (excelApplication == null) { excelApplication = new Excel.ApplicationClass(); } } /// <summary> /// ~ /// </summary> ~ExcelHelper() { if (excelApplication != null) excelApplication.Quit(); } /// <summary> /// 释放非托管资源 /// </summary> public void Dispose() { try { if (excelApplication != null) { if (!workbook.Saved) this.Close(false); excelApplication.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication); GC.Collect(System.GC.GetGeneration(worksheet)); GC.Collect(System.GC.GetGeneration(workbook)); GC.Collect(System.GC.GetGeneration(excelApplication)); if (excelApplication != null) { excelApplication = null; } GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); } } catch { } finally { GC.Collect(); } } /// <summary> /// 设置当前工作表 /// </summary> public int CurrentWorksheetIndex { set { if (value <= 0 || value > workbook.Worksheets.Count) throw new ArgumentException("索引超出范围"); else { object index = value; worksheet = workbook.Worksheets[index] as Excel._Worksheet; } } } /// <summary> /// 打开一个Excel工作薄 /// </summary> /// <param name="fileName"></param> public void OpenWorkbook(string fileName) { workbook = excelApplication.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); if (workbook.Worksheets.Count > 0) { int index = 1; worksheet = workbook.Worksheets[index] as Excel._Worksheet; } } /// <summary> /// 添加一个工作表 /// </summary> /// <param name="SheetName"></param> /// <returns></returns> public Excel.Worksheet AddSheet(string sheetName) { Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); worksheet.Name = sheetName; return worksheet; } /// <summary> /// 删除一个Sheet /// </summary> /// <param name="sheetName"></param> public void DeleteSheet(string sheetName) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; excelApplication.DisplayAlerts = false; sheet.Delete(); excelApplication.DisplayAlerts = true; } /// <summary> /// 保存数据 /// </summary> public void Save() { if (workbook != null) { workbook.Save(); } } /// <summary> /// 关闭文档 /// </summary> /// <param name="isSave"></param> public void Close(bool isSave) { object obj_Save = isSave; workbook.Close(obj_Save, missing, missing); } /// <summary> /// 设置当前工作表中某单元格的值 /// </summary> /// <param name="range"></param> /// <param name="value"></param> public void SetRangeValue(string range, object value) { SetRangeValue(worksheet.Name, range, value); } /// <summary> /// 设置工作表中某单元格的值 /// </summary> /// <param name="sheetName"></param> /// <param name="range"></param> /// <param name="value"></param> public void SetRangeValue(string sheetName, string range, object value) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; sheet.get_Range(range, missing).Value2 = value; } /// <summary> /// 删除某个Range，右侧左移 /// </summary> /// <param name="sheetName"></param> /// <param name="rangeStr"></param> public void DeleteRange(string sheetName, string rangeStr) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; sheet.get_Range(rangeStr, missing).Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft); } /// <summary> /// 删除某个Range，右侧左移 /// </summary> /// <param name="sheetName"></param> /// <param name="rangeStr"></param> public void DeleteRange(string rangeStr) { DeleteRange(worksheet.Name, rangeStr); } /// <summary> /// 合并单元格 /// </summary> /// <param name="sheetName"></param> /// <param name="range1"></param> /// <param name="range2"></param> public void Merge(string sheetName, string range1, string range2) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; Excel.Range range = worksheet.get_Range(range1, range2); range.Merge(true); } /// <summary> /// 合并单元格 /// </summary> /// <param name="range1"></param> /// <param name="range2"></param> public void Merge(string range1, string range2) { Merge(worksheet.Name, range1, range2); } /// <summary> /// 设置一个单元格的属性 /// </summary> /// <param name="sheetName"></param> /// <param name="range"></param> /// <param name="size">大小</param> /// <param name="name">字体</param> /// <param name="color">颜色</param> /// <param name="HorizontalAlignment">对齐方式</param> public void SetCellProperty(string sheetName, string rangeStr, double fontSize, string fontName, double height, Excel.Constants horizontalStyle) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; Excel.Range range = sheet.get_Range(rangeStr, missing); range.Font.Size = fontSize; range.Font.Name = fontName; //range.Font.Color = fontColor; range.RowHeight = height; range.HorizontalAlignment = horizontalStyle; } /// <summary> /// 设置一个单元格的属性 /// </summary> /// <param name="range"></param> /// <param name="fontSize"></param> /// <param name="fontName"></param> /// <param name="fontColor"></param> /// <param name="horizontalStyle"></param> public void SetCellProperty(string range, double fontSize, string fontName, double height, Excel.Constants horizontalStyle) { SetCellProperty(worksheet.Name, range, fontSize, fontName, height, horizontalStyle); } /// <summary> /// 设定Range的边框格式 /// </summary> /// <param name="rangeStart"></param> /// <param name="rangeEnd"></param> /// <param name="topStyle"></param> /// <param name="rightStyle"></param> /// <param name="bottomStyle"></param> /// <param name="leftStyle"></param> public void SetRangeBorderStyle(string sheetName, string rangeStr, Excel.XlLineStyle topStyle, Excel.XlLineStyle rightStyle, Excel.XlLineStyle bottomStyle, Excel.XlLineStyle leftStyle) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; Excel.Range range = sheet.get_Range(rangeStr, missing); range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = topStyle; range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = rightStyle; range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = bottomStyle; range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = leftStyle; } /// <summary> /// 设定Range的边框格式 /// </summary> /// <param name="rangeStr"></param> /// <param name="topStyle"></param> /// <param name="rightStyle"></param> /// <param name="bottomStyle"></param> /// <param name="leftStyle"></param> public void SetRangeBorderStyle(string rangeStr, Excel.XlLineStyle topStyle, Excel.XlLineStyle rightStyle, Excel.XlLineStyle bottomStyle, Excel.XlLineStyle leftStyle) { SetRangeBorderStyle(worksheet.Name, rangeStr, topStyle, rightStyle, bottomStyle, leftStyle); } /// <summary> /// 设定Range数字格式 /// </summary> /// <param name="sheetName"></param> /// <param name="rangeStr"></param> /// <param name="format"></param> public void SetRangeNumberFormat(string sheetName, string rangeStr, string format) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; Excel.Range range = sheet.get_Range(rangeStr, missing); range.NumberFormat = format;//0% } /// <summary> /// 设定Range数字格式 /// </summary> /// <param name="rangeStr"></param> /// <param name="format"></param> public void SetRangeNumberFormat(string rangeStr, string format) { SetRangeNumberFormat(worksheet.Name, rangeStr, format); } /// <summary> /// 将数据表格添加到Excel指定工作表的指定位置 /// </summary> /// <param name="dt"></param> /// <param name="ws"></param> /// <param name="startX"></param> /// <param name="startY"></param> public void AddTable(System.Data.DataTable dt, int startX, int startY) { for (int i = 0; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { worksheet.Cells[i + startX, j + startY] = dt.Rows[i][j]; } } } /// <summary> /// 加入分页符 /// </summary> /// <param name="rangeStr"></param> /// <param name="lineNumber"></param> public void AddPageBreak(string rangeStr, int lineNumber) { Excel.Range range = worksheet.get_Range("A" + lineNumber.ToString(), missing); worksheet.HPageBreaks.Add(range); } /// <summary> /// 加入分页符 /// </summary> /// <param name="lineNumber"></param> public void AddPageBreak(int lineNumber) { AddPageBreak(worksheet.Name, lineNumber); } /// <summary> /// 从当前工作表数据区域复制数据到另一个区域 /// </summary> /// <param name="sheetFromRange"></param> /// <param name="sheetToRange"></param> public void CopyRange2Range(string sheetFromRange, string sheetToRange) { CopyRange2Range(worksheet.Name, worksheet.Name, sheetFromRange, sheetToRange); } /// <summary> /// 从一个表的某个数据区域复制数据到另一个表的某个区域 /// </summary> /// <param name="sheetFromName"></param> /// <param name="sheetToName"></param> /// <param name="sheetFromRange"></param> /// <param name="sheetToRange"></param> public void CopyRange2Range(string sheetFromName, string sheetToName, string sheetFromRange, string sheetToRange) { Excel.Worksheet sheetFrom = workbook.Worksheets[sheetFromName] as Excel.Worksheet; Excel.Worksheet sheetTo = workbook.Worksheets[sheetToName] as Excel.Worksheet; sheetFrom.get_Range(sheetFromRange, missing).Copy(sheetTo.get_Range(sheetToRange, missing)); } /// <summary> /// 移动Range到另一个Range /// </summary> /// <param name="FromRange"></param> /// <param name="ToRange"></param> public void MoveRange2Range(string FromRange, string ToRange) { MoveRange2Range(worksheet.Name, FromRange, ToRange); } /// <summary> /// 移动Range到另一个Range /// </summary> /// <param name="sheetName"></param> /// <param name="FromRange"></param> /// <param name="ToRange"></param> public void MoveRange2Range(string sheetName, string FromRange, string ToRange) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; sheet.get_Range(FromRange, missing).Cut(sheet.get_Range(ToRange, missing)); } /// <summary> /// 重设当前工作目录的打印区域 /// </summary> public void ResetPrintArea() { SetPrintArea(worksheet.Name, ""); } /// <summary> /// 重设工作目录的打印区域 /// </summary> public void ResetPrintArea(string sheetName) { SetPrintArea(sheetName, ""); } /// <summary> /// 设定工作目录的打印区域 /// </summary> /// <param name="area"></param> public void SetPrintArea(string area) { SetPrintArea(worksheet.Name, area); } /// <summary> /// 设定工作目录的打印区域 /// </summary> /// <param name="sheetName"></param> /// <param name="area"></param> public void SetPrintArea(string sheetName, string area) { Excel.Worksheet sheet = workbook.Worksheets[sheetName] as Excel.Worksheet; sheet.PageSetup.PrintArea = area; } /// <summary> /// 将当前工作表中的表格数据复制到剪切板 /// </summary> public void Copy() { if (worksheet != null) { try { worksheet.UsedRange.Select(); } catch { } worksheet.UsedRange.Copy(missing); } }}
四、使用ADO.net+VBA操作Excel
我手头的这个案子在用第一种方法撰写一个星期之后遇到效率瓶颈，无奈改为第二种方法，由于对Excel的文档模型有了较全面的认识，几个主流函数也很清楚，所以写的速度很快。
这里我还是要佩服一下微软，曾经我对Office很不屑，后来看完《Word排版艺术》才开始认识Office，再之后深入PPT和Excel，更是感觉文档模型了不起。。。咳咳咳，扯远了~
4.1 使用ADO.net写入数据到Excel文件
废话不说了，上核心代码
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destFileName + ";Extended Properties=Excel 8.0;");objConn.Open();System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();objCmd.Connection = objConn;foreach (DataRow row in dt.Rows){StringBuilder stringBuilder = new StringBuilder();stringBuilder.AppendFormat("INSERT INTO [{0}$] (", sheetName);for (int i = 0; i < dt.Columns.Count; i++){if (i < dt.Columns.Count - 1)stringBuilder.Append(dt.Columns[i].ColumnName + ",");elsestringBuilder.Append(dt.Columns[i].ColumnName + ") VALUES (");}for (int i = 0; i < dt.Columns.Count; i++){if (i < dt.Columns.Count - 1)stringBuilder.Append("@" + dt.Columns[i].ColumnName + ",");elsestringBuilder.Append("@" + dt.Columns[i].ColumnName + ");");}objCmd.Parameters.Clear();for (int i = 0; i < dt.Columns.Count; i++){objCmd.Parameters.Add(dt.Columns[i].ColumnName, row[i]);}objCmd.CommandText = stringBuilder.ToString();objCmd.ExecuteNonQuery();}
注意点就是INSERT的表明就是Sheet的名字加上中括号和$符号。
4.2 使用VBA操作模板和数据
使用VBA操作模板和数据的过程是一个assign的过程，就是把数据和模板杂糅起来，类似于MVC中C控制M输出到V中，我建议针对不同类型的页面制作多个模板，而不要使用VBA进行各种样式修改操作，减少代码量。我这个案子中使用了8个不同类型模板，毕竟直接使用鼠标拖拽出一个模板比用VBA代码修改处一个模板简单的多。
Excel2007的VBA界面在“开发工具”-“Visual Basic”中打开，千万不要以为写VBA就是录制宏呀~另外附赠一个小技巧，在VBA编辑界面里面使用Ctrl+J可以自动补全，相当实用。
我把项目分成5个模块：Golbal 、Init 、Insert 、Finalize 、Utils，看名字就知道意思了，其中Utils包含一个中文大写数字转换的函数，供前面使用。
我们需要在Excel自动打开时候进行操作，需要使用函数Workbook_Open，另有Auto_Open，两个有不同之处，可以Google之。
启用代码如下：
'开启工作簿时候动作Private Sub Workbook_Open() MAIN_SHEET = "报价模板" For Each Sheet In Sheets If Sheet.Name = MAIN_SHEET Then Exit Sub End If Next If Sheets("OPTION").Range("B2").Value = "" Then '如果不存在DH行数，则退出 Exit Sub End If MsgBox ("初始化数据，请稍等片刻！") Call Run Call DeleteSheets 'Call ActiveWorkbook.Save MsgBox ("初始化数据完成，请保存Excel文件！")End Sub
核心代码的话。。。其实没什么核心代码，就是频繁的使用赋值和Copy函数。。。。
五、相关资料链接
C#操作Excel开发报表系列整理
检索 COM 类工厂中 CLSID 为 {00024500-0000-0000-C000-000000000046} 的组件时失败，原因是出现以下错误: 80070005
PS:8000401a的错误会在服务器上出现，不会在xp上出现，原因是交互式用户也没有Excel操作权限，这时候要给Excel手动制动一个有Excel操作权限用户，比如administrator。
Office编程在dot Net环境中总结(Excel篇)
好了，这就是我这次Excel学习的分享，谢谢大家耐着性子看完~
版权所有 © 2010 转载本站文章请注明：转载自Log4D原文链接:http://dddspace.com/2010/12/csharp-vba-excel.html您可以随意地转载本站的文章，但是必须在醒目位置注明来源及本站链接，不可以将本站文章商业化使用，或者修改、转换或者以本作品为基础进行创作。
下午某时，天气晴，我在某地，想念你。
