Excel 自动化教程

[amnesiac]

导言：本文最初翻译自 Basic Ahk_L COM Tutorial for Excel（作者 Mickers），后面根据 Excel 脚本编写系列（很棒的系列教程）重新改写，曾使用标题 Excel 自动化第一阶发表于中文论坛。本教程将介绍通过 COM 自动化 Microsoft Office Excel，其中对最基础的命令进行了说明，不论您对 Excel/VBA 是否熟悉，在学习本文后都能轻松的入门。之前曾发过一篇通过 ADO 操作电子表格文件的文章，大家可参照学习。
创建、连接和终止 Excel 实例
我们从最简单的脚本开始介绍，这个脚本创建一个 Excel 实例，并向其中添加一个新的工作簿：

Code: Select all

objExcel := ComObjCreate("Excel.Application")
objExcel.Workbooks.Add

执行这段脚本后，为什么没有出现 Excel 程序呢？前面的脚本是起了作用，您也确实创建了 Excel 的新的实例。如果您仍在怀疑，请打开任务管理器并查看其中的进程，应该能在里面找到 Excel.exe 进程。实际情况是这样：默认情况下，在任何时候使用脚本创建 Office 应用程序的实例，该应用程序都将在屏幕上不可见的窗口中运行。Excel 其实存在于后台，所以您无法看到它。当 Excel 在不可见的窗口中运行时，您唯一的损失就是失去了通过键盘键入内容，从而使应用程序响应击键操作这一功能，而这正是默认行为起作用的地方。假设您正在运行一个脚本程序，该脚本程序将使用 Excel 创建一个报表，我们还假设在脚本运行期间，Excel 始终处于可见状态。用户 (甚至您本人) 可能会无意间按下键盘上的某一个按键，从而毁掉整个报表或者意外地通过关闭 Excel 以致不仅毁掉报表，而且使脚本崩溃的目的（因为脚本会尝试向已经不存在的 Excel 实例发送命令）。以不可见的方式运行 Excel 就可以避免这类问题的发生。

不过在执行本文中的代码时，为了查看代码执行的效果，这里必须让它显示出来：

Code: Select all

objExcel.Visible := True

创建新的 Excel 实例常常需要等一些时间，有时我们不想创建（例如在学习本文需要测试其中的代码）而只想使用当前已有的 Excel 实例，那么可以这么做：

Code: Select all

objExcel := ComObjActive("Excel.Application")    ; 获取当前活动的 Excel 实例的句柄

要让这个 Excel 实例退出，只需简单的执行 quit 命令：

Code: Select all

objExcel.Quit

打开电子表格
我们已经会创建 Excel 实例，现在看看如何打开电子表格。在 Excel 对象模型（有关详细信息，请参阅 MSDN 中的 Excel Object Model Overview）中，电子表格包含在 Workbooks 对象中。要打开电子表格，我们需要创建一个 Workbooks 集合的实例，然后使用 Open 方法打开电子表格。听起来很复杂，但具体到能够创建 Excel 实例的 ComObjCreate 的调用，只需要写以下区区两行代码：

Code: Select all

objExcel := ComObjCreate("Excel.Application")
objWorkbook := objExcel.Workbooks.Open("C:\test.xls")

当然有一点是很明显的，那就是如果在 C:\ 文件夹中并不存在名为 test.xls 的文件，那么脚本就不会起作用。如果您的计算机中没有安装 Excel，那么脚本也不会起作用（如果您认为仅仅通过运行这个脚本就可以避免购买 Office，那么很抱歉，让您失望了）。

有没有打开电子表格的其他的编程方法？有。实际上，我们这个专题中的许多操作都可以用其他的方法完成。但出于时间和版面的考虑，我们将以最容易被初学者接受的方法来编写 Excel 脚本。如果您希望了解能够完成相同任务的其他的方法，请查看Excel 帮助中的 Excel 对象模型文档。
保存文件
直接保存到当前文件（注：如果是新建的工作簿调用Save方法时会以创建时默认的工作簿名称保存在用户文档目录下）：

Code: Select all

objExcel.Workbook.Save()

另存为其他文件：

Code: Select all

objExcel.ActiveWorkbook.SaveAs("C:\test.xls")

将数据添加到电子表格中
首先我们简单地引用一个单元格，然后相应地设置值。下面将在第一行第一列输入“AutoHotkey”：

Code: Select all

objExcel.Cells(1, 1).Value := "AutoHotkey"

从电子表格读取数据
现在我们把刚才存入的数据读取并显示出来：

Code: Select all

strCell := objExcel.Cells(1, 1).Value
MsgBox, % strCell

如果您想要添加其他的数据，我们只需要多引用几个单元格并且设置合适的值就可以了。
格式设置
在脚本中设置单元格的格式和手动设置单元格一样的简单，在下面将设置单元格 A1 的格式：

Code: Select all

objExcel.Cells(1, 1).Font.Bold := TRUE    ; 将文本设为黑体
objExcel.Cells(1, 1).Font.Size := 24    ; 将字体大小设为 24
objExcel.Cells(1, 1).Font.ColorIndex := 3    ; 将字体颜色设为红色

这段代码是非常简单的，如果想要将文本设为斜体该怎么办？可以使用下面这行代码：

Code: Select all

objExcel.Cells(1, 1).Font.Italic := TRUE

如果想要使用 Times New Roman 字体该怎么办？可以使用下面这行代码：

Code: Select all

objExcel.Cells(1, 1).Font.Name := "Times New Roman"

要将单元格的背景颜色设置为褐色，可以使用下面的代码：

Code: Select all

objExcel.Cells(1, 1).Interior.ColorIndex := 9

注：遗憾的是，我们没有时间全面介绍您在处理时会使用的许多格式设置选项，您需要去参考 Excel 帮助。
使用范围
在许多时候我们需要对多个单元格，例如同一行的某些单元格或整列单元格，这时需要使用范围。虽然有几种不同的方法指示范围中包含的单元格，但是它们有一点是共同的：它们都需要您创建 Range 对象的实例，然后指定哪些单元格是该范围的一部分。例如，下面是一些创建范围的常用方法。
要创建包含单个单元格的范围：

Code: Select all

objRange2 := objExcel.Range("A1")

要创建包含整个列的范围：

Code: Select all

objRange := objExcel.ActiveCell.EntireColumn

正如您所期望的，有相似的命令来创建包含整个行的范围：

Code: Select all

objRange := objExcel.ActiveCell.EntireRow

如果您想要选择的行或列不同于带有活动单元格的行或列怎么办？没问题。使用所需的行或列中的一个单元格来创建范围，然后使用 Activate 方法来使其成为活动单元格。此时，设置代表整个行或列的范围。例如，下面这段代码使单元格 E5 成为活动单元格，然后通过选择整个行来创建包含第 5 行中的所有单元格的范围：

Code: Select all

objRange := objExcel.Range("E5")
objRange.Activate
objRange := objExcel.ActiveCell.EntireRow

要创建包含一组单元格的范围：

Code: Select all

objRange := objExcel.Range("A1:C10")

注意，您在这里做的是指定起点 (A1) 和终点 (C10)。Excel 会自动选择这两个点之间的所有单元，并把它们放在范围之中。

要创建包含所有数据的范围：

Code: Select all

objCell := objExcel.Range("A1").SpecialCells(11)

在这个例子中，11 是表示包含数据的电子表格中最后的单元格的参数。这个命令所创建的范围从单元格 A1 开始一直延伸到所有包含数据的单元格。

注：在 Excel 中可以使用许多灵活的方式指定范围，然而在某些要求比较高难以直接使用范围时，这时可以考虑循环：

Code: Select all

; 这里在 A1-I1 单元格中依次存入数字 1-9
while, (A_Index < 10)
{
    strCell := Chr(A_Index + 64) . "1"
    objExcel.Range(strCell).value := A_Index
}

经过适当的变化，可以用于许多较特殊的情况，例如隔行读取数据等。
数据排序
您还可以对 Excel 中的内容进行排序：

Code: Select all

objRange2 := objExcel.Range("A1")

您必须按范围对 Excel 中的数据进行排序。因而，您需要创建一个范围，它包含您想要按其进行排序的列的第一个单元格。因为我们想要按列 A 进行排序，所以我们创建的范围包含单个单元格：A1。

Code: Select all

objRange.Sort(objRange2, ComObjMissing(), ComObjMissing(), ComObjMissing(), ComObjMissing(), ComObjMissing(), ComObjMissing(), 1)

这种 Sort 方法看起来很疯狂（这么多 ComObjMissing()），但这是因为我们仅仅按单列进行排序。当您在 Excel 中对一些内容进行排序时，您必须依次指定所有的排序参数；如果您不使用参数，则将其保留为默认值（ComObjMissing() 表示该可选参数的默认值）。其中参数的含义请参阅 Excel 帮助。
一个完整的脚本
现在我们把前面的大部分操作合并到一个完整的脚本中，在其中我们将进行下列操作：

1. 更改带标签的单元格 (1,1) 的背景颜色和字体颜色；
2. 创建包含我们正在使用的五个单元格的范围 (A1:A5) 并更改字体大小；
3. 创建包含带有四个物理学家名字的单元 (A2:A5) 的范围并更改背景颜色；
4. 选择列 A 并使用 Autofit() 方法来重新设置列的大小，以便所有的文本都适合；
5. 创建只包含 A1 的范围并对列 A 进行排序。

脚本如下：

Code: Select all

objExcel := ComObjCreate("Excel.Application")
objExcel.Visible := True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value := "Name"
objExcel.Cells(1, 1).Font.Bold := TRUE
objExcel.Cells(1, 1).Interior.ColorIndex := 30
objExcel.Cells(1, 1).Font.ColorIndex := 2
objExcel.Cells(2, 1).Value := "Schr?dinger"
objExcel.Cells(3, 1).Value := "Heisenberg"
objExcel.Cells(4, 1).Value := "Bohr"
objExcel.Cells(5, 1).Value := "Einstein"
objRange := objExcel.Range("A1","A5")
objRange.Font.Size := 14
objRange := objExcel.Range("A2","A5")
objRange.Interior.ColorIndex := 36
objRange := objExcel.ActiveCell.EntireColumn
objRange.AutoFit()
objRange2 := objExcel.Range("A1")
objRange.Sort(objRange2, ComObjMissing(), ComObjMissing(), ComObjMissing(), ComObjMissing(), ComObjMissing(), ComObjMissing(), 1)

如果希望查看执行每行脚本时 Excel 中发生的变化（尽管前面已经解释了它们的功能，不过看看效果印象会更深刻，假如您之前没有执行过代码的话），那么可以在 SciTE4AutoHotkey 中使用单步执行的方法。
就这些吗？
基础教程到这里就结束了，不管您信不信，您可以在系统管理脚本中使用的所有奇妙的方法，我们都还没有接触到。例如，我们还没有讨论使用 Excel 来创建图表或图形的可能性。如果您想对 Excel 进行深入的学习以执行更多更高级的操作，可以将下面两种方法结合起来：

• 通过 VBA 帮助简单的熟悉 Excel 中的数据对象模型，并学习其中的例子；
• 通过 Excel 的宏功能录制手动进行的操作，并查看相应的 VBA 代码，把它们转换成 AutoHotkey 中的代码是很简单的。

并且，多实践、多思考、多总结，我想您很快就能成为其中的高手。在学习时主要的参考资料是 Excel 开发人员参考 （程序自带），由于某些时候可能安装精简版的去除了这个，可以到 MSDN 上查看，这里提供一个 Excel 2003 VBA 参考的下载链接。

此外，在官方论坛和中文论坛可以找到许多在脚本中操作 Excel 的实用的例子，例如：用AHK_L原生的com处理excel实例

[AAAHHHKKK]

感谢楼主的分享，
对选定范围复制，剪切，粘贴有具体例子吗？