VFP 学习、开发漫谈 (24)
在VFP中,将查询结果输出至 EXCEL 或 WORD 代替报表打印,这样的应用越来越普遍。在我设计的程序中,除非用户有明确的要求,并且打印输出的格式相对固定,如:纸张、版面布局等,我才设计报表,否则一概输出至 Excel。另外,为了方便用户随时导出记录,在我制作的表格类中添加了一个将表格内容导出至 Excel表的右键菜单。有朋友可能会问:VFP不是有“Copy To <文件> XL5”命令吗?VFP确实可以将DBF文件直接输出至Excel格式,但是它有局限性:一是无法控制输出格式;二是最多只能输出至Excel 5.0格式(使用XL5选项),若使用XLS选项,则输出至Excel 2.0格式。用高版本的Excel只能在兼容方式下打开该文件,不支持某些操作和格式。因此,若要输出用户满意的Excel报表,就必须学会在VFP下熟练掌握VBA。
将查询结果输出至Excel,大体上可分为以下几个步骤:
1. 将查询结果保存在一个临时表中,如:curResult。为了方便导出,应尽可能将临时表的字段名直接设置为Excel的列标题。总之,准备工作要做足,临时表中的数据就是最终要导出的数据。
2. 创建Excel对象
3. 添加工作簿,相当于新建一个文件
4. 输出表头及标题
5. 输出明细行
6. 对工作表的行高、列宽、表格线、字体、页面布局等格式进行设置
7. 显示Excel窗口
下面,着重介绍一下有关创建Excel对象及后续处理的过程。
● 首先,要建立一个Excel对象。为此,我编写了一个创建Excel对象的自定义函数:NewExcel()
程序代码:
* 功能:创建 Excel 对象---------------------------------------------------------------- * 参数:toExcel:创建的 Excel 对象,按址传递 * 返回:逻辑值,.t.:创建成功,.f.:创建失败 FUNCTION NewExcel(toExcel) WAIT Window '正在创建 Excel 对象,请稍侯……' NOWAIT LOCAL lErr TRY toExcel = CREATEOBJECT('Excel.Application') toExcel.DisplayAlerts = .f. CATCH WAIT CLEAR MESSAGEBOX('你的微机上可能没有安装 Excel,无法创建 Excel 对象!',16,'提示') lErr = .t. ENDTRY WAIT CLEAR RETURN !lErr ENDFUNC
比如:要创建一个Excel对象,并将其引用保存到变量oExcel中,可调用如下命令:
程序代码:
* 创建Excel对象,并将引用保存到oExcel变量 LOCAL oExcel IF !NewExcel(@oExcel) Return ENDIF
下面对NewExcel()函数中的语句解释一下。
□ 第一条语句是一个Wait Window语句,告诉用户系统正在创建Excel对象。因为创建Excel对象需要较长时间,因此在执行耗时较长的语句时,应该给出提示。
□ 系统将创建Excel对象的语句放在一个错误处理结构中。这是因为若用户微机上没有安装Excel,会产生一个创建对象失败的系统错误。
□ 再往下是创建Excel对象的核心语句:toExcel = CREATEOBJECT('Excel.Application'),创建Excel对象并将指针引用保存到变量toExcel。
□ 下面的语句是:toExcel.DisplayAlerts = .f.,这条语句设置Excel不显示警告提示。主要作用是:当使用VBA或API函数关闭Excel窗口时,不弹出提示保存对话框。
□ 在 CATCH 语句内部,除了显示错误提示外,还添加了一行 lErr = .t.。大家要记住2点:第一点是:在使用 LOCAL 语句定义变量lErr时,已经为lErr赋初值.f.;第二点是:当 CATCH 捕获到错误时,不能在 CATCH 内部直接执行 Return,因此,本函数用一个变量 lErr 来跟踪是否出现错误。另外,按照结构化编程的要求,一个函数应尽可能只保留一个出口。
□ 当正确创建了Excel对象时,返回.t.,否则返回.f.
● 下面说一下添加工作簿。
若要创建一个空白的工作簿,执行 oExcel.WorkBooks.Add 即可。但是,这里我要介绍一下另外一项技巧,即:使用一个Excel文件作为模板来创建工作簿。
我们在导出数据时,不但要使导出的Excel表格美观,还要导的快,而设置表头、单元格格式和页面布局需要消耗大量的时间。因此,若预先将表头、单元格格式以及页面布局等信息设置好,并保存到一个Excel文件中,再以该文件为模板导出查询结果,将大大加快导出速度。我公司是一家电力设备生产企业,而国网公司每2个月组织一次全国性的设备招投标活动,要求每个企业在10天之内完成400多份报价单。在我开发的报价系统中,最初是通过为每个单元格赋值的方式导出的,导出每份报价单约需2-3分钟,你可以想象一下,导出400多份报价单需要多长时间。后来,经过优化,每份报价单的导出时间缩短为10秒钟左右,大大提高了效率。
下图是我预先建立的库存盘点表文件 Stock.xls,在该文件中设置好了表头、表格标题以及页面布局,导出时直接从A5单元格导入表记录即可。
以导出库存盘点表为例,用模板建立工作簿的语句是:oExcel.WorkBooks.Add(LOCFILE(Stock.xls))。
有用户可能会问:为什么要将 Stock.xls 放在 LOCFILE()函数中呢?这是因为 WorkBooks.Add要求模板文件必须带完整路径。我们可以将 Stock.xls 放在系统默认路径或搜索路径下,然后再使用 LOCFILE()函数将文件名转换为带绝对路径的文件名。
● 导出明细行的语句就很简单了。下面是一段示例:
程序代码:
SELECT curResult && 选定查询结果表 nRow = 5 && 从第 5 行开始导入 SCAN FOR i = 1 TO FCOUNT() && 导出字段 oExcel.Cells(nRow,i).Value = EVAL(Field(i)) NEXT nRow = nRow + 1 && 行号加 1 ENDSCAN
上述方法虽然简单,但是效率比较低。若要加快导出速度,还得另辟蹊径。下面是我采取的思路:先将查询结果复制为一个 Fox2X 的低版本 DBF 文件,然后使用 Excel打开该文件,将所有记录行复制到最终的 Excel窗口,最后再关闭 Fox2X窗口。这样操作肯定要比逐条记录、逐个字段写入Excel单元格要快得多。
程序代码:
WITH oExcel .WorkBooks.Add(LOCFILE('Stock.xls')) && 以 Stock.xls为模板建立工作簿 oBookOld = .ActiveWindow && 记下 Excel 窗口 cTmpFile = ADDBS(SYS(2023)) + SYS(2015) && 临时文件名(临时文件夹+文件名) COPY TO (cTmpFile) TYPE Fox2x && 复制到兼容版本 .WorkBooks.Open(cTmpFile) && 用 Excel 打开表 oBookNew = .ActiveWindow && 记下 DBF 窗口 .Rows('2:'+TRAN(_Tally+1)).Copy && 复制所有数据行,不含标题 oBookOld.Activate && 返回 Excel 窗口 .Range('A5').PasteSpecial() && 从细节行位置开始粘贴 oBookNew.Activate && 返回 DBF 窗口 .Application.CutCopyMode = .f. && 取消选择区域,清除粘贴板上的数据 .ActiveWindow.Close(.F.) && 关闭 DBF 窗口 DELETE FILE (cTmpFile + '.dbf') && 删除临时表 .oBookOld.Activate && 返回 Excel 窗口 ENDWITH
● 一般情况下,我们都是在后台处理 DBF 到 Excel的,在处理过程中 Excel对象窗口是不显示的,等处理完成后才显示,即:oExcel.Visible = .t.。这就带来一个问题:如果在导出过程中出现了系统错误怎么办?因为后台的Excel窗口没有显示出来,怎么才能关闭它呢?若不关闭隐藏的Excel窗口,则每个Excel进程占用30M左右的内存,这样的无效进程多了,会严重影响系统的性能,所以必须关闭它。一种方法是,打开“任务管理器”,选择进程中的“EXCEL”,然后单击“结束进程”。但更完善的方法是:将整个导出过程放在一个错误处理结构中,若出现错误,则自动关闭隐藏的Excel窗口。如下所示:
程序代码:
* 定义 API 函数,用于关闭 Excel 窗口 DECLARE INTEGER SendMessage IN Win32API INTEGER HWND,INTEGER Msg, Short WParam,INTEGER LPARAM TRY * 导出处理 …… CATCH MESSAGEBOX('导出至 Excel 失败!',48,'提示') oExcel.ActiveWindow.Close(.f.) && 关闭 Excel 活动窗口,但并未退出 Excel 应用程序 nHwnd = oExcel.Hwnd && 记下窗口句柄 oExcel = NULL && 清除 Excel 对象引用,当Excel对象的引用存在时,无法结束Excel进程 SendMessage(nHwnd,16,0,0) && 关闭 Excel 应用程序窗口 ENDTRY
若导出过程中出现系统错误,先关闭当前工作簿窗口,记下 Excel应用程序窗口的句柄,然后清空 Excel对象变量,最后再使用API函数 SendMessage()关闭 Excel窗口。
● 在设置 Excel页面布局时,如:纸张方向、页边距等,必须首先判断系统是否安装了打印机,若在没有安装打印机的微机上使用VBA修改页面布局,会出现系统错误。下面,是我的处理方案:
程序代码:
DIMENSION aPRN(1) nNumPrint = APRINTERS(aPRN) && 获取系统安装的打印机 IF nNumPrint > 0 && 只有安装了打印机时,才进行页面设置 WITH oExcel.ActiveSheet.PageSetup .Orientation = 2 && 纸张方向:横向 .PrintTitleRows = '$1:$4' && 标题行 .CenterHorizontally = .t. && 页面水平居中 .LeftMargin = 1/0.035 && 左边距 .RightMargin = 1/0.035 && 右边距 .PrintGridLines = .f. && 不打印表格线 .CenterFooter = '第 &P 页,共 &N 页' && 设置页脚 ENDWITH ENDIF
● 经常看到有用户发贴询问如何设置行间距、如何合并单元格等问题。这些问题都可以通过录制一个宏,然后再通过查看宏代码来解决。比如:如何合并单元格问题,可以这样做:(以 Excel 2007为例)
1. 确保在工具面板上显示了“开发工具”选项卡
2. 切换到“开发工具”选项卡,单击“录制宏”
3. 选择单元格A1:B1,切换到“开始”选项卡,从“合并后居中”列表中选择“合并单元格”
4. 切换回“开发工具”选项卡,单击“停止录制”按钮,再单击“宏”,从宏列表中选择“Macro1”,单击“编辑”,可以查看到我们录制的 VBA 代码
在 VFP 中,可以转换为如下语句:
oExcel.Range("A1:B1").Select && 选定 A1:B1 单元格 oExcel.Selection.Merge && 合并选定区域
当然,也可以将两条语句合并成一条语句:oExcel.Range("A1:B1").Merge
在录制的 VBA 代码中,通常有很多 VBA 常量,但在 VFP 中使用时,必须将这些常量转换为常数。那么如何知道这些常量代表的常数值呢?方法是:在录制的 VBA 窗口中,按下 F8,然后将鼠标指针移动到常量上,系统自动显示其代表的常数。
微软确实太厉害了,它的软件可以做到融会贯通,你中有我,我中有你,在方便用户使用的同时,也给其他竞争对手设置了很高的门槛。近日,公司在做正版化工作时,从节约资金方面考虑,有人想选择 WPS,但经过测试,制表与编辑文档都没有问题,就是在 VBA 输出上仍然无法代替 WORD 和 EXCEL。
● WORD 与 EXCEL 大部分功能是相通的,但也有一些功能不同。比如,创建的 Excel对象有 Hwnd 窗口句柄属性,但 WORD 对象就没有。那么在导出过程中若出现错误,如何关闭隐藏的WORD窗口呢?我采用如下处理方法:
首先,在创建 WORD 对象时,将窗口标题设为一个特殊的字符串,如:“!@#?”。下面是我编写的创建 WORD 对象的函数 NewWord():
程序代码:
FUNCTION NewWord(toWord) = WaitWindow('正在创建 Word 对象,请稍侯……') LOCAL lErr TRY toWord = CREATEOBJECT('Word.Application') toWord.DisplayAlerts = .f. toWord.Caption = '!@#?' && 赋予特殊标题,便于使用 FindWindow()获取窗口句柄 CATCH WAIT CLEAR MESSAGEBOX('你的微机上可能没有安装 Word,无法创建 Word 对象!',16,'提示') lErr = .t. ENDTRY WAIT CLEAR RETURN !lErr ENDFUNC
若导出过程中出现错误,则使用 API 函数 FindWindow()来获取 WORD 窗口句柄,然后再调用 API 函数 SendMessage()来关闭隐藏的 WORD 窗口。若没有出现错误,则将标题替换为“Microsoft Word”,并显示 WORD 窗口。如下所示:
程序代码:
TRY * 导出处理 …… oWord.Visible = .t. && 若前面的导出处理无错误,则显示 Word 窗口 oWord.DisplayAlerts = .t. && 显示警告提示 oWord.Caption = 'Microsoft Word' && 恢复 Word 应用程序窗口的标题 CATCH WAIT CLEAR MESSAGEBOX('导出 WORD 文件失败!',48,'提示') cStr = oWord.Caption && Word 应用程序窗口标题:“!@#?” IF oWord.Windows.Count > 0 && 文档子窗口标题,如:“文档1”,完整的标题是:<文档标题> - <应用程序标题> cStr = oWord.ActiveWindow.Caption + ' - ' + cStr ENDIF nHwnd = FindWindow(0,cStr) && 根据窗口标题获取窗口句柄 IF nHwnd > 0 && 获取成功,则关闭 WORD 窗口 oWord = NULL SendMessage(nHwnd,16,0,0) ELSE && 获取失败,则显示 WORD 窗口 oWord.Visible = .t. ENDIF ENDTRY
WORD 窗口的标题与 EXCEL 窗口的标题类似,均采用“<文档名> - <应用程序名>”格式,如:“文档1 - Microsoft Word”,oWord.Caption 返回的是应用程序的标题,如:“Microsoft Word”(在创建WORD对象时,我们将其改成了“!@#?”),而 oWord.ActiveWindow.Caption 返回的是活动文档窗口的标题,如:“文档1”,理解了这一点,就可以读懂上面的代码了。
[ 本帖最后由 liuxingang28 于 2014-6-25 07:57 编辑 ]