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 + ",");
else
stringBuilder.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 + ",");
else
stringBuilder.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函数。。。。
五、相关资料链接
检索 COM 类工厂中 CLSID 为 {00024500-0000-0000-C000-000000000046} 的组件时失败，原因是出现以下错误: 80070005
PS:8000401a的错误会在服务器上出现，不会在xp上出现，原因是交互式用户也没有Excel操作权限，这时候要给Excel手动制动一个有Excel操作权限用户，比如administrator。
好了，这就是我这次Excel学习的分享，谢谢大家耐着性子看完~
版权所有 © 2010 转载本站文章请注明： 转载自Log4D
原文链接: http://dddspace.com/2010/12/csharp-vba-excel.html
您可以随意地转载本站的文章，但是必须在醒目位置注明来源及本站链接，不可以将本站文章商业化使用，或者修改、转换或者以本作品为基础进行创作。