VBS之excel操作集锦

上一篇 / 下一篇  2016-07-08 15:34:03 / 个人分类:QTP自动化测试

   最近一直在写一些关于读写Excel的脚本,最开始每次去读写excel文件,效率特别低,而且每次在释放excel进程的时候都有可能释放不完全,导致写入的时候老是会有意外弹框,即便是屏蔽掉弹框,但所有的进程一直存在,会导致数据写入存在问题。后来优化脚本,一次性把excel中的数据读取到内存中,先把结果先写到内存中,然后再一次性把内存中的数据写入excel中,不仅大大提高了写入效率,而且也解决了进程释放不掉的问题。现在把VBS操作excel的函数总结如下:

Rem ===============================================================
Rem 功能:读取数据驱动Excel表中的数据,并以二维数组的方式返回
Rem  
Rem 参数说明:数据驱动Excel文件的路径,要读取Sheet的名称
Rem 
Rem 说明:此函数的返回值为一个数组,是将Excel内容转换的数组
===============================================================

Function DataDirveReadExcel(ExcelFilePath,SheetName)
Rem定义变量
Dim ExApp
Dim ExBook
Dim ExSheet
Dim ArrayRange

Rem 关掉存在的Excel进程
Rem KillProcess("excel.exe")

Rem创建所使用到的Excel对象
Set ExApp = CreateObject("Excel.Application")
Set ExBook = ExApp.Workbooks.Open(ExcelFilePath,,True)
Set ExSheet = ExBook.Worksheets(SheetName).UsedRange

Rem 将Excel表中的数据转换成二维数组的形式
ArrayRange = ExSheet.Value

Rem 返回二维数组
DataDirveReadExcel = ArrayRange

Rem关闭工作表,释放对象
ExBook.Close
ExApp.Quit

Rem 释放创建的对象
Set ExSheet = Nothing
Set ExBook = Nothing
Set ExApp = Nothing

End Function


===============================================================
Rem 功能:将指定Excel中指定Sheet的指定单元格的值返回。
Rem  
Rem 参数说明:1、Excel文档路径    2、SheetName    3、X坐标    4、Y坐标
Rem 
Rem=============================================================

Function ReturnCellsValue(FilePath,SheetName,x,y)

Rem 定义变量
Dim ExArray

Rem 将指定的Excel的某个Sheet以二维数组返回
ExArray = DataDirveReadExcel(FilePath,SheetName)

Rem 将指定单元格的值返回
ReturnCellsValue = ExArray(x,y)

End Function


===============================================================
Rem 功能:求出Excel表中指定Sheet的记录总数
Rem  
Rem 参数说明:Excel文件的路径,要读取Sheet的名称
Rem 
Rem 说明:此函数的返回值为记录条数
===============================================================

Function ExcelRecordCount(ExcelFilePath,SheetName)

Rem定义变量,个别变量赋初值
Dim ExApp
Dim ExBook
Dim ExSheet
Dim RecordCount

Rem 关掉存在的Excel进程
Rem Call KillProcess("excel.exe")

Rem创建所使用到的Excel对象
Set ExApp = CreateObject("Excel.Application")
Set ExBook = ExApp.Workbooks.Open(ExcelFilePath,,True)
Set ExSheet = ExBook.Worksheets(SheetName)

Rem 获取记录总数
RecordCount = ExSheet.UsedRange.Rows.count

Rem 返回记录数
ExcelRecordCount = RecordCount 

Rem关闭工作表,释放对象
ExBook.Close
ExApp.Quit

Rem 释放创建的对象
Set ExSheet = Nothing
Set ExBook = Nothing
Set ExApp = Nothing

End Function

Rem ===============================================================
Rem 功能:写数据到Excel中
Rem  
Rem 参数说明:1、文件地址,表格名,指定行,列,写入数据
Rem 
Rem 
Rem 备注:
Rem 
Rem ===============================================================
Function WriteToExcelFile(FilePath,SheetName,x,y,Content)
    
    Rem 打开表格
    Set xlsobj=createobject("excel.application")
    Set xlsbook=xlsobj.Workbooks.Open(FilePath)
    Set xlssheet=xlsbook.Sheets(SheetName)
    
    Rem 写信息
    xlssheet.cells(x,y)=Content
    
    On Error Resume Next
    
    Rem 屏蔽对话框
    xlsobj.DisplayAlerts = False
    
    Rem 保存文件
    xlsbook.Save
    
    Rem 保存文件
    xlsbook.Close
    
    xlsobj.Quit

    Rem 释放创建的对象
    Set xlssheet = Nothing
    Set xlsbook = Nothing
    Set xlsobj = Nothing
    
End Function

Rem ===============================================================
Rem 功能:复制一个表格中的指定数据列到另一个表格中
Rem  
Rem 参数说明:1、文件地址1、2,表格名,指定列,写入数据
Rem 
Rem 
Rem 备注:Column为“B:D”
Rem 
Rem ===============================================================
Function CopyColumnToExcelFile(ResultFilePath1,ResultFilePath2,SheetName,Column)
    
    Rem 定义变量
    Dim oExcel
    Dim oExcel1
    Dim oExcel2
    
    Rem 创建对象
    Set oExcel = CreateObject("Excel.Application")
    oExcel.visible=False
    Set oExcel1 = oExcel.Workbooks.Open(ResultFilePath2,true
    Set oExcel2 = oExcel.Workbooks.Open(ResultFilePath1,false)
    oExcel1.Sheets(SheetName).Activate
    oExcel2.Sheets(SheetName).Activate
    
    Rem 复制B:D三列
    oExcel1.ActiveSheet.Range(Column).Copy 
    oExcel2.ActiveSheet.Range(Column).PasteSpecial
    
    Rem 清空剪切版
    Call ClearClipBoard()
    
    oExcel1.close
    Set oExcel1 = Nothing
    oExcel2.Save
    oExcel2.close
    Set oExcel2 = Nothing
    oExcel.quit
    
End Function


TAG: Excel excel

 

评分:0

我来说两句

Open Toolbar