working with microsoft excel

working with microsoft excel

和大多数的microsoft 的产品一样，excel通过COM借口提供了自动化excel的方法，本章只介绍一些实例代码，展示了如何使用QTP打开和处理excel 电子表格，要深度的开发excel的自动化功能，请参考市场上现存的的众多的excel的VBA/Macro编程书的其中一本

Excel自动化对象模型

下图显示了本章会用到的部分excel对象模型

Application 此主对象引用了excel应用程序本身

Workbooks 此对象引用当前打开的工作蒲的集合(也就是数据表文件)

Workbook 此对象引用了一个单独的工作蒲

Worksheets 此对象引用了所选择打开的的工作蒲中的的sheet集合

Worksheet 此对象引用所选择打开的工作蒲中的一个sheet

问题 23-1 如何启动和结束一个excel实例

'存储excel应用程序对象的变量

Dim xlApp

'创建一个新的excel应用程序实例，在默认的情况下，应用程序是以不可见的模式开始的，此会有助于在后台执行启动和初始化操作

Set xlApp=CreateObject("Excel.Application")

'使应用程序可见

xlApp.Visible=true

'结束刚刚启动的excel实例

xlApp.Quit

'销毁对象的引用

Set xlApp=nothing

问题 23-2 如何创建一个新的excel sheet，然后保存它

'excel 对象声明

Dim xlApp

Dim xlWorkBook

Dim xlWorkSheet

Set xlApp=CreateObject("Excel.Application")

'创建一个新的工作蒲,addadd方法返回一个对象，引用了刚刚创建的工作工作蒲，可以用来和工作蒲进行交互

Set xlWorkBook=xlApp.workbooks.add

'创建一个新的worksheet，此方法返回一个引用刚刚创建的workshet对象

Set xlWorkSheet=xlWorkBook.worksheets.add

'用一些文本更新A1，1,1单元格

xlWorkSheet.Cells(1,1)="a"

'如上的语句还可以写成 xlWorkBook.worksheet.cells(1,"A")

'保存这个新的工作蒲，之所以使用了SaveAs方法，是因为这个工作蒲还没有被保存过，在我们打开并且重新保存一个已经存在的工作蒲的种情况下，方法应该使用save

xlWorkBook.saveAs "c:\NewBook.xls"

'关闭workbook,并且结束excel实例

xlWorkBook.close

xlApp.quit

Set xlWorkBook=nothing

Set xlApp=nothing

注意：在剩下的例子中，为了简洁起见，我们不使用全部的代码，特别注意的是，我们会假设excel的调用

，结束和基本对象的创建

问题23-3，我们如何判定一个excel是否存在，如果不存在怎么去创建一个

首先我们使用filesystemobject去检查文件是否存在

'源文件

sSourceFile="C:\NewBook.xls"

Set fso=createobject("Scripting.FileSystemObject")

'检查文件是否存在

bFileExist=fso.fileExists(sSourceFile)

If bFileExist Then

'如果文件存在，打开它

set xlWorkbook=xlApp.workbooks.open(sSourceFile)

else

'如果文件不存在，那么创建一个新的

set xlWorkbook=xlApp.workbooks.add

End If

'检查是否打开了一个新的文件

If bfileExist Then

xlWorkbook.save

else

'当文件从来没有被保存过，使用SaveAs

xlWorkbook.saveas sSourceFile

End If

问题23-4 如何访问指定列里边的所有的非空行的值

有两种方法，一种方法是循环所所选择列的所有的单元格，直到遇到空白值就停止，第二种方法是在所选定列获取所有的非空值的个数

'方法1

'计算行数

I=1

While xlWorkSheet.cell(i,1)<>"" 

msgbox xlWorkSheet.cells(i,1)

Wend

'使用evalutate函数来确定有多少行有非空值

iRowCount=xlWorkSheet.evaluate("COUNTA(A:A)")

'在如上的语句中，A:A指定了在列A中所有的行数，（1:1）代表的是第一行，（A1:A100）意为列A中的第一到第100行

For i=0 iRowCount

msgbox xlworkShheet.cells(i,1)

Next

问题23-5 我们寻找一个文本字符串，并且更新此行中的其他单元格的值

考虑电子表格显示如图23-2

举例：我们说我们要更新Tarn的age值，下面是我们如何做到的

23-5

Dim xlApp

Dim xlWorkBook

Dim xlWorkSheet

Set xlApp=CreateObject("Excel.Application")

sSourceFile="C:\a.xls"

Set fso=createobject("Scripting.FileSystemObject")

bFileExist=fso.fileExists(sSourceFile)

If bFileExist Then

set xlWorkbook=xlApp.workbooks.open(sSourceFile)

set xlWorkSheet=xlWorkbook.worksheets(1)

'在A列里边寻找文本

set objfind=xlWorkSheet.range("A:A").find("cora")

If  objfind is nothing Then

msgbox "没有发现此行"

else

iRowNum=objfind.row

'更新age的值

xlWorkSheet.cells(iRowNum,2)=16

xlWorkbook.save

End If

End If

xlWorkBook.close

xlApp.quit

Set xlWorkBook=nothing

Set xlApp=nothing

23-6 我们如何使用excel就像使用QTP output DataTable

考虑电子表格显示如图23-2

我们假设我们已经打开了如上的文件，并且我们要为不同的行填充数据，但是我们又不能确定列的序号，即，name是定义在第一行，第二行或者其他行。我们首先要做的是计算出第一行的列标题，并且为每一个列标题随上与他相关的列数创建一个字典条目

‘23-6

systemutil.CloseProcessByName("EXCEL.EXE")

Dim xlApp

Dim xlWorkBook

Dim xlWorkSheet

Set xlApp=CreateObject("Excel.Application")

sSourceFile="C:\a.xls"

Set fso=createobject("Scripting.FileSystemObject")

bFileExist=fso.fileExists(sSourceFile)

If bFileExist Then

set xlWorkbook=xlApp.workbooks.open(sSourceFile)

set xlWorkSheet=xlWorkbook.worksheets(1)

'变量存储字典字典的index

 Dim oCols

 '创建一个字典对象，存储每一列的index

 Set oCols=CreateObject("Scripting.Dictionary")

 oCols.comparemode=vbTextCompare

 iColumn=1

 

' 循环第一行所有的列

 While xlWorkSheet.cells(1,iColumn)<>""

 '‘添加列标题作为key，列索引作为value

sTitle=xlWorkSheet.cells(1,iColumn)

oCols.Add sTitle,iColumn

iColumn=iColumn+1

 Wend

 ' 更新数据表两行

For iRow=2 to 3

'我们是从第二行开始循环的，是应为第一行是，标题行包含了列名

xlWorkSheet.cells(iRow,oCols("name"))="Name"&iRow

xlWorkSheet.cells(iRow,oCols("age"))=iRow+10

Next

 xlWorkbook.save

End If

xlWorkBook.close

xlApp.quit

Set xlWorkBook=nothing

Set xlApp=nothing

问题 23-7 我们如何选择一个文件去打开

？

转换VBA为VBSCRIPT

在excel里边我们可以录制宏，宏能够产生VBA代码

录制宏

Excel提供了录制宏的能能力，录制宏能够在应用程序中自动化不同的行为，这种内置在excel的能力，能够用来确定什么样的代码需在QTP写出来要以实现一个想要的操作，本节描述了如何执行宏录制然后转化这些步骤为QTP的vbscript代码

打开一个excel,with一个空的电子表格,go to 工具，宏录制，新建宏，然后按ok键开始录制一个宏

在第二行为name，age，和sex 输入一些值，然后选择所有的单元格，并且添加边框。选择标题行，并且给它一些背景色

按stop按钮结束录制

按住 alt+F11打开vba编辑器，浏览到模块文件，它包含了被录制的宏

如下是那个excel产生的宏

Sub Macro2()

' Macro2 Macro

'   Range("A2").Select

    ActiveCell.FormulaR1C1 = "cora"

    Range("B2").Select

    ActiveCell.FormulaR1C1 = "19"

    Range("C2").Select

    ActiveCell.FormulaR1C1 = "femal"

    Range("A1:C2").Select

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlInsideVertical)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlInsideHorizontal)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    Range("A1:C1").Select

    Range("C1").Activate

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 65535

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

End Sub

想在QTP中用vbscript来达到同样的东西，我们需要过滤掉我们不需要的东西，以及将vba转化为vbscript

考虑如下的vba代码行

‘选择范围A1：C1

Range("A1:C1").Select

因为vba代码一直在eccel内部执行，我们不需要指定一个worksheet对象，但是等效的QTP代码使用vbscript，需要显式的引用一个对象

xlWorkSheet.Range("A1:C1").Select

让我们转化如下的vba代码，以便他能够使用vbscript在QTP里边工作

Range("A1:C1").Select

        With Selection.Interior

        .Pattern = xlSolid

    End With

如上的代码使用了一个常量，xlSolid定义在excel里边。但是在QTP里边却不可用，所以需要我们自己定义，但是为了这样做，我们需要确定常量的值，有两种方法可以做到

方法1

在vba编辑器视图，按ctrl+G，发起一个即时窗口

在窗口内部输入“?xlSolid”并且按回车键显示常量值

这窗口会显示 我们现在在QTP中要使用的 常量值，

‘常量声明

Const xlSolid=1

方法二：在vba编辑器视图，按F2发起object browser，输入xlSolid搜索一个常量，如下

在最后一个vba代码片段中我们可以避开select，和with selection对象，在vbscpipt中直接引用我们要设定的对象，所以此vba代码转化为如下：

‘常量定义

Const xlSolid=1

xlWorksheet.Range("A1:C1").Select

  xlWorksheet.Range("A1:C1").Interior.ColorIndex=35

  xlWorksheet.Range("A1:C1").Interior.Pattern = xlSolid

End With

要使vba和vbscript的转换变得更加简单，我们需要更加的充分理解我们需要操作的对象，以及这些对象位于对象模型的什么位置

注意：我们可以使用excel object browser来看不类型的对象所支持的各种方法

如果我们去观察宏录制的 保存一个文件到指定位置的vba代码，他看起来会向这样

..

注意vba支持像这样传输参数，不需要考虑位置的顺序，使用“parametername：=pameter”语法，但是vbscript不支持这样的参数传输，所以我们要保证所有的所有的函数参数都是按照函数定义的指定顺序传输的，使用object browser审阅所选的函数定义，如saveAs函数定义如下

‘saveAs函数句法

Sub SaveAs([Filename], [FileFormat], [Password], [WriteResPassword], [ReadOnlyRecommended], [CreateBackup], [AccessMode As XlSaveAsAccessMode = xlNoChange], [ConflictResolution], [AddToMru], [TextCodepage], [TextVisualLayout], [Local])

这个vba语句能转换为vbscript，使用如下的简单语法，因为所有参数除第一个除外通常可以使用默认值

xlWorkBook.Saveas “C:\sheetExample.xls”

但是如果我们需要为sheet定义一个密码，我们应该使用如下的句法：

xlWorkBook.Saveas “C:\sheetExample.xls”,,”password”

注意我们没有为第二个参数提供一个值，所以它会使用一个默认的值

 

问题23-8 我们如何访问镶嵌在ie里边的excel电子表格

Internet explore 和explore能够呈现许多类型的文档，他可能是一个本地文件夹，共享目录，web URl，word文档等等，这两个程主要做的是呈现文档对象，文档对象的不同取决于所引用的URL的类型

对于浏览器显示一个网页我们可以使用如下的语句去访问document对象

Set objDoc = Browser("CreationTime:=0").object.document

但是浏览区如果显示一个内嵌的文档，如上的代码会导致QTP抛出一个“general run error”异常，要访问内嵌的文档对象，我们需要使用IE的COM接口，如下的函数通过window 句柄提供了对IECOM接口的访问，

‘函数获取通过句柄获取ie对象

Function GetIEObjectFromhwnd(ByVal hwnd)

Dim objShell,objShellWindows

Set objShell = CreateObject("Shell.Application")    '创建shelll object

Set objShellWindows = objShell.Windows  '获取所有打开的exploer窗口集合

‘检查集合对象是否存在

If (Not objShellWindows Is Nothing) Then

Dim objEnumItems

‘检查每个exploer窗口，并且比较句柄

For Each objEnumItems In objShellWindows

If objEnumItems.hwnd = hwnd Then

set GetIEObjectFromhwnd = objEnumItems   'returnthe objectwhose hwnd meet

  Exit For

Else

    Set GetIEObjectFromhwnd = Nothing                       'Noobject found

End If

Next

End If

  Set objShellWindows = Nothing

               Set objShell = Nothing

End Function

我们现可以使用如上给出的函数，获得显示电子表格的IE 窗口的COM接口

‘获得浏览器的句柄

hwnd =Browser("CreationTime:=0").GetROProperty("hwnd")

‘获得IE COM接口对象

Set ieBrowser = GetIEObjectFromhwnd(hwnd)

获得IE窗口的文档对象

Set objDocument = ieBrowser.document 

但是如果我们不知道此文档对象的实际内容，那我们如何去确定他的对象类型，我们只需要添加如下的代码，并且运行它，去观察他的输出

‘ 此会给出workbook

Msgbox typename(objDocument)

注意：为了获得如上的输出，需要在浏览器中打开内嵌的Excel sheet

使用typename函数，我们可以确定document是一个Excel应用程序的workbook对象，因此我们可以访问所有的工作蒲的属性和方法

‘从document对象中获得workbook

Set objWWorkbook = objDocument

msgbox typename(objWWorkbook)

Set objWorkbook=objDocument

Set objsheet1=objWorkbook.sheets(1)

objsheet1.cells(5,1)="Tarun Lalwani"

 msgbox objsheet1.cells(5,1)

如上的代码现在能够操作内嵌的Eccel电子表格，同样的操作可以用于计算和处理其他的内嵌document，像word，powPoint以及更多

新能提高

QTP调用ExcelObject有他的代价，因为，它是一个跨进程的通信，考虑如下的QTP代码

systemutil.CloseProcessByName("EXCEL.EXE")

Dim xlApp

Dim xlWorkBook

Dim xlWorkSheet

Set xlApp=CreateObject("Excel.Application")

sSourceFile="C:\a.xls"

Set fso=createobject("Scripting.FileSystemObject")

bFileExist=fso.fileExists(sSourceFile)

If bFileExist Then

set xlWorkbook=xlApp.workbooks.open(sSourceFile)

set xlWorkSheet=xlWorkbook.worksheets(1)

‘ 存储当前的timer

x=timer

For i=1 to 1000

For j=1 to 7

  xlWorkSheet.cells(i,j)  =i*j

Next

Next

   

    msgbox timer-x

 xlWorkbook.save

End If

xlWorkBook.close

xlApp.quit

Set xlWorkBook=nothing

Set xlApp=nothing

如上的代码在作者的机上花去了27秒，此代码的性能问题是我们做了7000此调用，每次只访问了一个电子表格单元格，如果我们减少调用的次数，我们可以提高运行时的性能，考虑如下的该进

x=timer

Dim iarray

ReDim iarray(6)

‘为下一行创建所有列值

For i=1 to 1000

For j=1 to 7

  iarray(j-1)  =i*j

Next

‘向下一行中所有单元格写上新值

 xlWorkSheet.range("A"&i&":G"&i)=iarray

Next

‘显示循环用掉的时间

  msgbox timer-x

在同一台机器上如上的代码的执行花去了不到4秒的时间，此增强的函数一行一行的更新sheet，它比一个个的写入单元格节省了大量的时间

通过一次性向电子表写入一列可以达到更进一步的性能优化，减少QTP对excle的调用到7次，不同的仅仅是把它赋给range之前我们需要倒置数组，使用如下的函数

'函数根据列索引获取列名

Function GetColumnName(byval index)

GetColumnName=chr(asc("A")+(index-1) mod 26)

index=(index-1)\26

If  index<>0Then

GetColumnName=chr(asc("A")+(index-1) mod 26)+GetColumnName

End If

End Function

x=timer

Dim iarray

ReDim iarray(1000)

'先对列进行循环，再对行进行循环

For i=1 to 7

For j=1 to 1000

  iarray(j-1)  =i*j

Next

colName=GetColumnName(i)

 xlWorkSheet.range(colName&"1:"&colName&j)=xlApp.transpose(iArray)

Next

'显示循环消耗的时间

    msgbox timer-x

这个改进的第二个方案仅仅花去了0.21秒去执行，仍然不够快？我们可以做出最后的性能改进，通过使用二维数数组，然后赋给电子表格，只需要访问一次

x=timer

Dim iarray

ReDim iarray(999,6)

‘构成一个二维数组

For i=1 to 1000

For j=1 to 7

  iarray(i-1,j-1)  =i*j

Next

Next

 

 xlWorkSheet.range("A"&"1:"&GetColumnName(j-1)&(i-1))=iArray

    msgbox timer-x

第三个和最后一个改进，仅仅用去了0.04秒的时间，大约比开始原先一个个的访问单元格的代码快了大约50倍

从excel中一次性读取数据

 Dim iarray

 '获取有值的范围

 iarray=xlWorkbook.UsedRange

 '获取 Array的维数

 rowCount=Ubound(iarray,1)

 colCount=Ubound(iarray,2)

 '循环数组

 For i=Lbound(iarray,1) to rowCount

 For j=Lbound(iarray,2) to colCount

'process iArray(i.j)

 Next

 Next

注意：

'' oCols.Add  xlWorkSheet.cells(1,iColumn),"haha"是不能这样写的，因为key中间有个逗号，QTP不能很好的识别出来。