毕业设计（论文）-格式化EXCEL表格的工具的设计与测试.doc

格式化格式化 excelexcel 表格的工具的设计与测试表格的工具的设计与测试 摘要摘要 excel 是一个在各个领域中被广泛应用的电子表格软件。通过它，我们可 以很好的组织管理数据，对数据进行统计分析等。在实际应用中，我们发现经 常需要根据某种特定的条件，特殊显示部分数据。虽然这种功能可以通过 excel 宏来实现，但每次编写类似的代码使人感觉跟不方便。excel 同其它 office 组件一样，提供了通过插件进行功能扩展的可能。通过编写和安装插件， 我们可以几乎任意地扩展 excel 的功能，为我们的工作提供极大的便利。本文 详细介绍了一个 excel 条件格式化软件的设计和实现。该软件使用 com 组件技 术，根据 office 插件的接口要求，设计了一个 excel 的插件。该插件在 excel 应用程序的窗口中增加一个工具栏，并在其上创建了一个按键，用于打开格式 化设置窗口。软件可以对用户输入的、用字符串表达的判断条件进行计算，并 根据计算结果，将满足条件的单元数据设置为用户选择的特殊格式（字体、颜 色、字号、修饰等） 关键词：关键词：excel 插件；com 组件；接口 the design and implementation of the conditionally formatting tool for excel abstract excel is an electronic sheet software which is extensively used in various fields. by using it, we can organize and manage data, statistically analysis data easily. in practical applications, we need it usually need to specially show some data under certain conditions. this function can be achieved by using excel macros. however，such a method is inconvenient in writing similar codes in each time. excel, as long as other microsoft office softwares, povides a standard plug-in interface, also called add-in, to allow users to expand its functionalities. by writing addins for excel, we can almost unlimitedly to expand its functionality and makes our work more easily and effectively. this paper introduces the design and implementation of an excel conditional formatting addin, which is implemented by the com component technique according to the interface requirements of excel. the conditional formatting addin adds a new toolbar into excel, on which there is a button to be used to open the format setting dialog. the software can calculate the results of the condition expressions inputted by the user in the form of string, and according to the result, it sets the format of the cells matching the use-defined condition to special formats (font, color, font size and decoration) that was inputted by the user. key words: excel addin; com component; interface 目目 录录 论文总页数：23 页 1 引言 1 2 com 组件的实现技术 1 2.1 com 组件技术.1 2.2 接口 2 2.3 插件技术 3 2.4 excel 对象模型.4 2.4.1 excel 对象模型的类以及之间的关系.4 2.4.2 使用 excel.5 2.4.3 引用 application 对象 5 2.4.4 application 对象常用的属性、方法.6 2.4.5 使用工作薄 6 2.4.6 使用工作表 8 2.5 vb 编写组件基本介绍9 2.5.2 在 vb 中编写 com 组件 .9 3 excel 条件格式化工具的具体实现 .12 3.1 插件接口方法 .12 3.2 excel插件的实现.13 3.2.1 插件实现的功能13 3.2.2 具体功能实现13 参考文献.21 第 1 页 共 23 页 excelexcel 条件格式化工具的设计与实现条件格式化工具的设计与实现 1 1 引言引言 我们生活的这个世界是丰富多彩的，几乎所有的知识都来自于视觉。也许 无法记住一连串的数字，以及它们之间的关系和趋势。但是可以很轻松地记住 一幅图画或者一个曲线。excel 就具有许多高级的制图功能，同时使用起来也 非常方便。它不仅可以用来制作电子表格、完成许多复杂的数据运算，还可以 进行数据的分析和预测。excel 文档是实际工作学习中最为常用的文档格式之一， 为了增强 word、excel 等软件的自动化能力，人们开发了各种提高办公效率的 软件，它们大多数实用、专业性强。为了避免用户做大量重复性的工作，提高 office 办公效率，达到提高其实用功能的目的，可根据具体工作内容要求，编 写出最具本地化、个性化、最合适的软件。 2 2 comcom 组件的实现技术组件的实现技术 2.12.1 comcom 组件技术组件技术 com 是微软公司为了计算机工业的软件生产更加符合人类的行为方式开发 的一种新的软件开发技术。在 com 构架下，人们可以开发出各种各样的功能专 一的组件，然后将它们按照需要组合起来，构成复杂的应用系统。由此带来的 好处是多方面的：可以将系统中的组件用新的替换掉，以便随时进行系统的升 级和定制；可以在多个应用系统中重复利用同一个组件；可以方便的将应用系 统扩展到网络环境下；com 与语言，平台无关的特性使所有的程序员均可充分 发挥自己的才智与专长编写组件模块。 com 是开发软件组件的一种方法。组件实际上是一些小的二进制可执行程序， 它们可以给应用程序，操作系统以及其他组件提供服务。开发自定义的 com 组 件就如同开发动态的，面向对象的 api。多个 com 对象可以连接起来形成应用 程序或组件系统。并且组件可以在运行时刻，在不被重新链接或编译应用程序 的情况下被卸下或替换掉。microsoft 的许多技术，如 activex, directx 以及 ole 等都是基于 com 而建立起来的。并且 microsoft 的开发人员也大量使用 com 组件来定制他们的应用程序及操作系统。com 所含的概念并不止是在 microsoft windows 操作系统下才有效。com 并不是一个大的 api，它实际上象结构化编程 及面向对象编程方法那样，也是一种编程方法。在任何一种操作系统中，开发 人员均可以遵循“com 方法” 。一个应用程序通常使由单个的二进制文件组成的。 第 2 页 共 23 页 当编译器生成应用程序之后，在对下一个版本重新编译并发行新生成的版本之 前，应用程序一般不会发生任何变化。操作系统，硬件及客户需求的改变都必 须等到整个应用程序被重新生成。目前这种状况已经发生变化。开发人员开始 将单个的应用程序分隔成单独多个独立的部分，也既组件。这种做法的好处是 可以随着技术的不断发展而用新的组件取代以有的组件。此时的应用程序可以 随新组件不断取代旧的组件而渐趋完善。而且利用已有的组件，用户还可以快 速的建立全新的应用。 传统的做法是将应用程序分割成文件，模块或类，然后将它们编译并链接成一 个单模应用程序。它与组件建立应用程序的过程（称为组件构架）有很大的不 同。一个组件同一个微型应用程序类似，即都是已经编译链接好并可以使用的 二进制代码，应用程序就是由多个这样的组件打包而得到的。单模应用程序只 有一个二进制代码模块。自定义组件可以在运行时刻同其他的组件连接起来以 构成某个应用程序。在需要对应用程序进行修改或改进时，只需要将构成此应 用程序的组件中的某个用新的版本替换掉即可。 com 即组件对象模型，是关于如何建立组件以及如何通过组件建立应用程序 的一个规范，说明了如何可动态交替更新组件。使用组件的优点：组件架构的 一个优点就是应用可以随时间的流逝而发展进化。除此之外，使用组件还有一 些可以使对以有应用的升级更加方便和灵活的优点，如应用的定制，组件库以 及分布式组件等。使用组件的种种优点直接来源于可以将它们动态的插入或卸 出应用。为了实现这种功能，所有的组件必须满足两个条件：第一，组件必须 动态链接；第二，它们必须隐藏（或封装）其内部实现细节。动态链接对于组 件而言是一个至关重要的要求，而消息隐藏则是动态链接的一个必要条件。 2.22.2 接口接口 由于组件向外部隐藏了其内部的细节，因此客户要使用组件时就必须通过 一定的机制，也就是说要通过一定的方法来实现客户与组件之间的通信，这就 需要接口。所谓接口就是组件对外暴露的、向外部客户提供服务的“连接点” 。 外部的客户见不到组件内部的细节，它所能看到的只是接口，客户也是通过接 口来获取组件提供的服务。这有点像 osi 网络协议分层模型，每一层就像一个 组件，它内部的实现细节对于其他层是不可见的；而每一层通过“服务接入点” 向其上层提供服务，这就像这里所说的接口。一般来说，接口总是固定的，也 是公开的。组件的开发人员要实现这些接口，而客户则通过接口获得服务。正 是接口的这种固定和公开，才使得组件和客户能够在不了解对方的情况下达成 一致。 第 3 页 共 23 页 接口提供了两个不同对象间的一种连接。实际上计算机程序是通过一组函 数而连接起来的。这组函数实际上就定义了程序中不同部分的接口。dll 的接 口就是它所输出的那些函数。com 中的接口也涉及到一组由组件实现并提供给 客户使用的函数。对于 com 来说，接口是一个包含一个函数指针数组的内存 结构。第一个数组包含的是一个由组件所实现的函数的地址。对于 com 而言， 接口就是此内存结构，其它东西均是一个 com 并不关心的实现细节。 接口的作用：在 com 中接口就是一切。对于客户来说，一个组件就是一 个接口集。客户只能通过接口才能同 com 组件打交道。从整体上讲，客户对 于一个组件可以说是知之甚少的。在某些情况下，客户甚至不必知道一个组件 所提供的所有接口。 可复用应用程序架构：说组件仅仅只是接口的实现细节当然有点言过其实。 不管怎么说，一个未被实现的接口实际上什么也不能完成。但是组件可从应用 程序中删除并可用另外一个组合来取代之。只要新的组件支持同组件相同的接 口，那么整个应用程序将仍然能够工作。单个的组件并不能对整个应用程序产 生决定的作用。相反，用以连接组件的接口将对整个应用程序产生决定性的作 用。只要接口保持不变，那么组件可以任意地更换。 接口同木板房中的大梁非常类似。这些大梁决定了整个房屋的结构。同样 可以将应用程序所用的组件替换掉，这样应用程序的行为将会发生变化，但从 结构上讲，整个应用程序并没有发生任何变化。使用组件来构造应用程序的最 大的优点在于可以复用应用程序的结构。如果接口设计得好的话，将可以得到 可复用极高的结构。当然使用接口除了可以设计出可复用的结构外，还有其它 若干优点。 com 接口的其它优点：接口使得客户可以用同样的方式来处理不同的组件。 这种能力就被称作是多态。这些优点是通过接口将某个特定的行为封闭起来而 获得的。 2.32.3 插件技术插件技术 插件是一类特殊的组件。它的目的不是为一般应用程序使用，而是专为特 定的应用程序使用。插件一般在宿主应用程序上添加控制项，如菜单、工具按 键等。插件响应宿主程序中对这些项目的操作，并对宿主程序中的数据进行特 定操作。 插件是一种遵循一定规范的应用程序接口编写出来的程序。插件是一类特 殊的组件。它的目的不是为一般应用程序使用，而是专为特定的应用程序使用。 第 4 页 共 23 页 插件的本质是在不修改程序主体的情况下对软件功能进行加强，当插件的接口 被公开时，任何公司或个人都可以自己制作插件来解决一些操作上的不便或增 加一些功能。插件还可以支持多人合作开发，不同的功能可以由不同的人来完 成，而且由于不同插件之间互不影响，方便程序的调试和纠错。插件一般在宿 主应用程序上添加控制项，如菜单、工具按键等。插件响应宿主程序中对这些 项目的操作，并对宿主程序中的数据进行特定操作。插件类型从广义的范围来 看，插件有以下三种类型： 1类似批命令的简单插件。事实上这种插件的自由度非常低。运行这种插 件后，会一步步要求用户进行选择/输入，最后根据用户的输入来执行一系列事 先定义好的操作。这种插件一般是文本文件。功能比较单一，可扩展性极小。 优点是插件做起来非常方便，即使是对程序设计了解不多的人也可以制作。 2使用一种特殊的脚本语言来实现的插件。这种插件比较难写，需要软件 开发者自己制作一个程序解释内核。比如微软惹了很多麻烦的宏就是这种类型 的。有一套著名的 office 辅助工具就是完全用 office 内置的 vbscript 写成的。 这种方法的优点在于无需使用其它工具来制作插件，软件本身就可以实现，普 遍出现于各种办公自动化软件中。 3利用已有的程序开发环境来制作插件。例如 photoshop 等软件使用的方 法。使用这种方法的软件在程序主体中建立了多个自定义的接口，使插件能够 自由访问程序中的各种资源。这种插件的优势在于自由度极大，可以无限发挥 插件开发者的创意，这种插件是狭义范围的插件，也是真正意义上的插件。而 这种插件机制的编写相对复杂，对于插件接口之间的协调比较困难。 2.42.4 excelexcel 对象模型对象模型 .1 excelexcel 对象模型的类以及之间的关系对象模型的类以及之间的关系 excel 组件中定义了大量的类。下图显示了我们在软件开发中使用到的一些 主要的类和它们之间的关系。 第 5 页 共 23 页 application workbook * 1 worksheet *1 chart * 1 range * 1 font * 1 1 * 1* 1 * 1 * 1 * 图 1 excel 对象模型片断 .2 使用使用 excelexcel 在 vb 应用程序中使用 excel，实质是将 excel 作为一个外部对象来引用， 由 excel 对象模型提供能从 vb 应用程序内部来程序化操纵的对象以及相关的属 性、方法和事件。为了能从 vb 应用程序中访问 excel 丰富的内部资源，使 excel 应用程序运行得更快，需要在 vb 工程中添加对 excel 类型库的引用。具 体步骤如下： a）从 vb6“工程“菜单中选择“引用“； b） 在“引用“对话框中选择 excel 类型库：“microsoft excel 11.0 object library“； c）单击左边小方框，使之出现“符号； d）按“确定“退出。 注：要想在 vb 应用程序中调用 excel，你的计算机系统中必须安装 excel。 .3 引用引用 applicationapplication 对象对象 application 对象是 excel 对象模型的顶层，表示整个 excel 应用程序。 在 vb 应用程序中调用 excel，就是使用 application 对象的属性、方法和事件。 为此，首先要声明对象变量： dim vbexcel as object 第 6 页 共 23 页 或直接声明为 excel 对象： dim vbexcel as excel.application 在声明对象变量之后，可用 createobject 函数或 getobject 函数给变量赋值。 前者创建一个新的 excel 实例，后者取得当前打开的 excel 实例。两个函数均 返回对 excel 的 application 对象引用。 a）用 createobject 函数生成新的对象引用： set vbexcel=createobject (“excel.application“) 字符串“excel.application“是提供 excel 应用程序的编程 id。 b）用 getobject 函数打开已存在的对象引用： set appexcel=getobject(“samp.xls“, “excel.application“) 上面语句打开文件 samp.xls。 .4 applicationapplication 对象常用的属性、方法对象常用的属性、方法 visible 属性取 true 或 false，表明 excel 应用程序是否可见。left,top 属性 excel 窗口的位置； height, width 属性 excel 窗口的大小； windowstate 属性 指定窗口的状态，取：ximaximized(最大化)x1 minimized(最小化)x1 normal(缺省)。quit 方法 退出 microsoft excel；calculate 方法 重新计算所有打开的工作簿、工作表或单元格。 evaluate 方法 求值数学表达式并返回结果。 示例 1：求值数学表达式： dim vbexcel as object set vbexcel=createobject (“excel.application“) x=vbexcel. evaluate (“3+5*(cos (1/log (99. 9)“) 在 vb 应用程序中使用 excel 应用程序，就是通过 application 对象的属 性、方法来获得其它 excel 对象，如工作簿、工作表、单元等待。下面分类给 出其中常用的属性和方法。 第 7 页 共 23 页 .5 使用工作薄使用工作薄 workbook 对象代表 excel 应用程序中当前打开的一个工作簿，包含在 workbooks 集合中。可以通过 workbooks 集合或表示当前活动工作簿的 active workbook 对象访问 workbook 对象。 常用的方法有： add 方法 创建新的空白工作簿，并将其添加到集合中。 open 方法 打开工作簿。 activate 方法 激活工作簿，使指定工作簿变为活动工作簿，以便作为 active workbook 对象使用。 save 方法 按当前路径和名称保存现有工作簿(如是首次保存，则将其保存到缺 省名称中，如 book1.xls)。 saveas 方法 首次保存工作簿或用另一名称保存工作簿。 close 方法 关闭工作簿。 printout 方法 打印工作簿，语法为： printout (from, to, copies, preview, printer, tofile, collate) 可选参数： from：打印的起始页号。如省略将从起始位置开始打印。 to：打印的终止页号。如省略将打印至最后一页。 copies：要打印的份数。如省略将只打印一份。 preview：如果为 true 则 excel 打印指定对象之前进行打印预览。如果为 false，或省略则立即打印该对象。 printer：设置活动打印机的名称。 tofile：如果为 true 则打印输出到文件。 collate：如果为 true 则逐份打印每份副本。 下面语句将活动工作簿的 2 到 5 页打印 3 份： activeworkbook.printout from：=2 to 5 copies：=3 示例 2：生成、保存、关闭工作簿 第 8 页 共 23 页 dim vbexcel as excel.application set vbexcel= createobject(“excel.application“) with vbexcel .workbooks.add with activeworkbook .save as“c： temp output.xls“ .close end with quit end with .6 使用工作表使用工作表 sheets 集合表示工作簿中所有的工作表。可以通过 sheets 集合来访问、 激活、增加、更名和删除工作表。一个 worksheet 对象代表一个工作表。 worksheets 属性 返回 sheets 集合。 name 属性 工作表更名。 add 方法 创建新工作表并将其添加到工作簿中。 select 方法 选择工作表。 copy 方法 复制工作表。 move 方法 将指定工作表移到工作簿的另一位置。 delete 方法 删除指定工作表。 printout 方法 打印工作表 示例：将 c 盘工作簿中的工作表复制到 a 盘工作簿中 dim vbexcel as excel.application set vbexcel=createobject(“excel.application“) with vbexcel .workbooks.open “c：tempoutput.xls“ .workbooks.open“a：output1.xls“ .workbooks(“output.xls“).sheets (“sales“).copy .workbooks(“output1.xls) .workbooks(“output1.xls“).save .workbooks(“output.xls“).close .workbooks(“outputi.xls“).close 第 9 页 共 23 页 .quit end with 使用单元范围对象（range）： range 对象代表工作表的某一单元格、某一行、某一列、某一选定区域或者某 一三维区域。 range 属性 range (arg)其中 arg 为 a1-样式符号，表示单个单元格或单元格 区域。range 对象的主要属性和方法包括： cells 属性 cells (row, col )(其中 row 为行号，col 为列号)表示单 个单元格。 columnwidth 属性 指定区域中所有列的列宽。 rowl3eight 属性 指定区域中所有行的行宽。 value 属性 指定区域中所有单元格的值(缺省属性)。 formula 属性 指定单元格的公式，由 a1-样式引用。 select 方法 选择范围。 copy 方法 将范围的内容复制到剪贴板。 c1earcontents 方法 清除范围的内容。 delete 方法 删除指定单元范围。 以上简要介绍了 excel 对象模型中部分对象及其属性和方法，更详细的信 息可参阅 excel 2000 帮助中的“microsoft excel visual basic 参考“一节的 内容。实际上，microsoft office 家族的 word,powerpoint, access 和 project 等应用程序都可以在 vb 应用程序中调用，其原理和步骤完全相同，只 是其对象模型有所不同而已。 2.52.5 vbvb 编写组件基本介绍编写组件基本介绍 .2 在在 vbvb 中编写中编写 comcom 组件组件 1、新建 activex dll 工程 第 10 页 共 23 页 图 2 activex dll 工程 注意：该类型工程缺省添加的不是窗体，而是类模块。 2、修改工程属性 菜单：工程属性 图 3 菜单：工程属性 其中，工程名称就是组件的名称。 3、修改类模块的性质 第 11 页 共 23 页 图 4 模块的性质 说明：其中的 instancing 是组件对象的实例化方式。 5-multiuse：可以创建多个实例。 1-private: 私有对象。不能在外部程序中创建它的实例，但公开其接口。 4、定义对象的属性和方法 与一般 vb 类相同。如添加以下代码，定义了该对象缺省接口的 hello 方法： public sub hello(smsg as string) msgbox smsg end sub 如果定义为 private，则不是接口的方法，而是私有对象中的方法，对外不公 开。 5、编译生成 com 组件 菜单：文件生成 6、调试和使用该 com 组件 （1） 在当前工程组中添加一个标准 exe 的 vb 工程。 菜单：文件添加工程。 第 12 页 共 23 页 （2） 在新工程中引用前面的 activex dll 工程。 com 组件工程的名称将出现在组件选择对话框的列表中 图 5 引用工程 （3）将新工程设置为启动工程。 图 6 启动工程 （4）在新工程中添加代码，创建组件对象，调用其 hello 方法。 private sub command1_click()dim myvbobj as myvbcom.vbcomobject set myvbobj = new myvbcom.vbcomobject myvbobj.hello “hello world.“ 第 13 页 共 23 页 end sub （5）设置断点，运行。 3 3 excelexcel 条件格式化工具的具体实现条件格式化工具的具体实现 3.13.1 插件接口方法插件接口方法 3.23.2 excelexcel 插件的实现插件的实现 .1 插件实现的功能插件实现的功能 .2 具体功能实现具体功能实现 设计器设计器 图 7 设计器 窗体的实现窗体的实现 第 14 页 共 23 页 图 8 窗体 设置字体按钮： 图 9 设置字体按钮 选择字体 private sub cmdsetfont_click() 选择一个临时单元 dim rng as range set rng = mexcelapp.selection 保存其原有字体设置 dim size, italic, underline, strikethrough, bold, color, style, name size = rng.font.size italic = rng.font.italic underline = rng.font.underline strikethrough = rng.font.strikethrough bold = rng.font.bold color = rng.font.color style = rng.font.fontstyle 第 15 页 共 23 页 name = rng.f 使用 excel 的标准对话框设置其字体 mexcelapp.dialogs(xldialogfontproperties).show 保存其字体设置 mfontsize = rng.font.size mfontitalic = rng.font.italic mfontunderline = rng.font.underline mfontstrikethrough = rng.font.strikethrough mfontbold = rng.font.bold mfontcolor = rng.font.color mfontstyle = rng.font.fontstyle mfontname = rng.f 恢复其原有字体设置 rng.font.size = size rng.font.italic = italic rng.font.underline = underline rng.font.strikethrough = strikethrough rng.font.bold = bold rng.font.color = color rng.font.fontstyle = style rng.f = name end sub 单元填充按钮： 图 10 单元填充按钮 private sub cmdbackground_click() 选择一个临时单元 dim rng as range set rng = mexcelapp.selection 保留原有设置 dim clrindex, pclrindex, p, pclr 第 16 页 共 23 页 p = rng.interior.pattern clrindex = rng.interior.colorindex pclr = rng.interior.patterncolor pclrindex = rng.interior.patterncolorindex 设置新的填充模式 mexcelapp.dialogs(xldialogpatterns).show 保存设置 mpattern = rng.interior.pattern mcolorindex = rng.interior.colorindex mpatterncolor = rng.interior.patterncolor mpatterncolorindex = rng.interior.patterncolorindex 恢复临时单元以前设置 rng.interior.pattern = p rng.interior.colorindex = clrindex rng.interior.patterncolor = pclr rng.interior.patterncolorindex = pclrindex end sub 保存单元格式设置的变量 private sub cmdcancel_click() unload me end sub private sub cmdformat_click() formatselectedcells trim(txtformula) msgbox “格式化完成！“ unload me end sub 类模块设计：类模块设计： （类模块设计中包括“段”类和公式类） 1 段类 表示公式的一部分, 可能是字符串, 也可能是一个相对的位置. 职责: 将相对位置转换为当前的绝对位置 根据当前单元相对于公式原始位置的偏移, 计算绝对位置 2 公式类 第 17 页 共 23 页 职责: 1. 将公式字符串转换为“段“对象(见: csegment), 以表示其中的相对单元位置 2. 根据提供的当前行列号, 得到当前的公式字符串. option explicit 公式的原始位置 private mabsrow as long private mabscol as long 组成公式的段 private msegments as collection 设置公式字符串 public sub setformula(byval sformula as string, byval absrow as long, byval abscol as long) 这个公式字符串有很多信息类型、范围 释放原有的段集 set msegments = nothing 定义新的段集 set msegments = new collection mabsrow = absrow mabscol = abscol 解析公式(查找其中的相对单元位置: 以开头的单元) 主要是从上面的 公式字符串里提取我们需要的 相对位置的信息 dim i as integer, schar as string, sseg as string dim blninbrace as boolean 标志: 当前字符位置是否在引号中 dim blngettingrelativecell as boolean 标志: 正在提取相对单元位置 dim seg as csegment for i = 1 to len(sformula) schar = mid(sformula, i, 1) select case schar case “, “ 如果是引号 blninbrace = not blninbrace 是否在引号内标志取反 case “ 可能是相对单元位置 if not blninbrace then 第 18 页 共 23 页 不在引号嵌套中, 则确定是一个相对