首页 > Tech > QTP finishing the operation function excel

QTP finishing the operation function excel

2010-12-02 来源：本站原创分类：Tech 人气：125

************************************************** ****
'Function: read a value in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: Excel to read the name in the Sheet
'IntRow: read what their data
'IntCol: which column to read the data
'For example: "E: \ a.xls", "Sheet1", 2,3
'Return: the value of taking into
'************************************************* *****

function getOneValue (strFilePath, strSheetName, intRow, intCol)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet

'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)

'Get excel in value, and returns
getOneValue = ExcelSheet.Cells (intRow, intCol)

'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
end function

'************************************************* *****
'Sub: to write a data in excel
'Input parameter:
'StrExcelSheetName: Excel to write the name in the Sheet
'IntRow: where to write data line
'IntCol: where to write a column
'StrValue: Value to write
'For example: "E: \ a.xls", "Sheet1", 2,3, "111"
'Return:
'************************************************* *****

sub setOneValue (strFilePath, strSheetName, intRow, intCol, strValue)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet

'Set value
ExcelSheet.cells (intRow, intCol). Value = strValue

'Write complete, save the EXCEL
ExcelApp.DisplayAlerts = False
ExcelApp.Save

'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet

end sub

'************************************************* *****
'Function: read the value of a column in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: Excel to read the name in the Sheet
'IntCol: Read a column of data which
'For example: "E: \ a.xls", "Sheet1", 2
'Return: the value of taking into
'************************************************* *****

function getColValues (strFilePath, strSheetName, intCol)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, intRowscount, arrValues ()

'There are a few lines in excel
intRowscount = ExcelBook.ActiveSheet.UsedRange.Rows.Count

'Get value excel
Redim Preserve arrValues (intRowscount-1)
For i = 1 to intRowscount
arrValues (i-1) = ExcelSheet.Cells (i, intCol)
Next

'Return value
getColValues = arrValues

'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet

end Function

'************************************************* *****
'Sub: Write the value of a column in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: to write the name of Sheet
'IntCol: Write a column of data which
'IntFromrow: where the line began to write
'ArrValue: write value (array)
'For example: "E: \ a.xls", "Sheet1", 2,2, arrRes
'Return:
'************************************************* *****
Sub setColValues (strFilePath, strSheetName, intCol, intFromRow, arrValue)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, intRowscount
Dim intArrColumnsCount, intColumnsCount

'ExcelSheet.activate
'Get the size of the array
intArrColumnsCount = UBound (arrValue)

'Where the last line of writing
intRowCount = intFromRow + intArrColumnsCount

'Set value
For i = intFromRow To intRowCount
ExcelSheet.cells (i, intCol). Value = arrValue (i-intFromRow)
Next

'Write complete, save the EXCEL
ExcelApp.DisplayAlerts = False
ExcelApp.Save

'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
End Sub

'************************************************* *****
'Function: read the value of a row in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: Excel to read the name in the Sheet
'IntRow: read what their data
'For example: "E: \ a.xls", "Sheet1", 1
'Return: the value of taking into
'************************************************* *****

function getRowValues (strFilePath, strSheetName, intRow)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, intColumnsCount, arrValues ()

'There are several columns in excel
intColumnsCount = ExcelBook.ActiveSheet.UsedRange.Columns.count

'Get value excel
Redim Preserve arrValues (intColumnsCount -1)
For i = 1 to intColumnsCount
arrValues (i-1) = ExcelSheet.Cells (intRow, i)
Next

'Return value
getRowValues = arrValues

'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet

end Function

'************************************************* *****
'Sub: Write the value of a row in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: to write the name of Sheet
'IntRow: write a line of data which
'IntFromCol: where to start writing the column
'ArrValue: write value (array)
'For example: "E: \ a.xls", "Sheet1", 5,2
'Return:
'************************************************* *****

Sub setRowValues (strFilePath, strSheetName, intRow, intFromCol, arrValue)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, intColcount
Dim intArrColumnsCount, intColumnsCount

'Get the size of the array
intArrColumnsCount = UBound (arrValue)

'Last wrote which column
intColcount = intFromCol + intArrColumnsCount

'Set value
For i = intFromCol To intColcount
ExcelSheet.cells (intRow, i). Value = arrValue (i-intFromCol)
Next

'Write complete, save the EXCEL
ExcelApp.DisplayAlerts = False
ExcelApp.Save

'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet

End Sub

'************************************************* *****
'Function: read all the values in Excel
'Input parameter:
'StrFilePath: Save Excel file path
'StrExcelSheetName: Excel to read the name in the Sheet
'For example: "E: \ a.xls", "Sheet1"
'Return: the value of taking into
'************************************************* *****

function getAllValues (strFilePath, strSheetName)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, intRowscount, intColumnsCount, arrGetCellValue ()

'There are several columns in excel
intRowscount = ExcelBook.ActiveSheet.UsedRange.Rows.Count
intColumnsCount = ExcelBook.ActiveSheet.UsedRange.Columns.count

'Get value excel
Redim Preserve arrGetCellValue (intRowscount-1, intColumnsCount-1)
For i = 1 To intRowscount
For j = 1 to intColumnsCount
arrGetCellValue (i-1, j-1) = ExcelSheet.Cells (i, j)
Next
Next

'Return value
getAllValues = arrGetCellValue

'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet

end Function

'************************************************* *****
'Function: read the value of the first occurrence of a line number
'Input parameter:
'StrFilePath: Excel file path
'StrSheetName: Excel to read the name in the Sheet
'Value: The value of the first occurrence of
'For example: "E: \ a.xls", "Sheet1", "root"
'Return: line number
'************************************************* *****

Function getRowByValue (strFilePath, strSheetName, Value)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet
Dim rowcount, colcount

'There are a few lines to obtain EXCEL table, columns
rowcount = ExcelBook.ActiveSheet.UsedRange.Rows.Count
colcount = ExcelBook.ActiveSheet.UsedRange.Columns.Count

'From the line to start the cycle
For i = 1 To rowcount
For j = 1 To colcount
'To find the desired value to determine whether
If ExcelSheet.cells (i, j) = Value Then

'Return value
getRowByValue = i

'If this value is found, exit loop
Exit for
End If
Next

'If this value is found, exit loop
If getRowByValue <> "" Then
Exit For
End If
Next

'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet

End Function

'************************************************* *****
'Function: Read the first occurrence of a value column number
'Input parameter:
'StrFilePath: Excel file path
'StrSheetName: Excel to read the name in the Sheet
'Value: The value of the first occurrence of
'For example: "E: \ a.xls", "Sheet1", "root"
'Return: line number
'************************************************* *****
Function getColByValue (strFilePath, strSheetName, Value)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet
Dim rowcount, colcount

'There are a few lines to obtain EXCEL table, columns
rowcount = ExcelBook.ActiveSheet.UsedRange.Rows.Count
colcount = ExcelBook.ActiveSheet.UsedRange.Columns.Count

'From the line to start the cycle
For i = 1 To rowcount
For j = 1 To colcount
'To find the desired value to determine whether
If ExcelSheet.cells (i, j) = Value Then

'Return value
getColByValue = j

'If this value is found, exit loop
Exit for
End If
Next

'If this value is found, exit loop
If getColByValue <> "" Then
Exit For
End If
Next

'Close Excel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet

End Function

'************************************************* *****
'Function: initial data, access to test data
'Input parameter:
'StrFilePath: Excel test data file path
'StrSheetName: Excel to read the name in the Sheet
'ColNumber: identifier located list
'Flag: whether to implement identifier
'ParmNumbers: the number of test parameters

'For example: "D: \ test.xls", "login", 1, "x", 4
'Return: test data (two-dimensional array)
'The first column in each test data row number in excel, so that the results of the write-back
'************************************************* *****

Function getTestdata (strFilePath, strSheetName, colNumber, flag, parmNumbers)
'Define variables
Dim ExcelApp, ExcelBook, ExcelSheet, rowcount, colcount, array (), arra (), k

'There are a few lines to obtain EXCEL table, columns
rowcount = ExcelBook.ActiveSheet.UsedRange.Rows.Count
colcount = ExcelBook.ActiveSheet.UsedRange.Columns.Count

'The data to determine which rows need to perform, there is one-dimensional array
m = 0
For i = 1 To rowcount
If ExcelSheet.cells (i, colNumber) = flag Then
ReDim PreServe arra (m)
arra (m) = i
m = m +1
End If
Next

'Re-definition of the second weft array, the first column of data stored for each test line number, and the parameters of the test data
ReDim PreServe array (m-1, parmNumbers)

For i = 0 To m-1
array (i, 0) = arra (i)
For j = 1 To parmNumbers
array (i, j) = ExcelSheet.cells (arra (i), j + colNumber)
Next
Next

'Return value
getTestdata = array

'Close Ecxel
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet

End Function

'************************************************* *****
'Sub: According to the results of filtering the test data is written
'Input parameter:
'StrFilePath: Excel test data file path
'StrSheetName: Excel to read the name in the Sheet
'ArrData: an array of test data storage
'StrColName: results of the column names
'ArrResult: an array of storage of test results
'For example: "D: \ 1.xls", "sheet1", arrData, "actualResult", arrResult
'Return:
'************************************************* *****
Sub setResultByArrdata (strFilePath, strSheetName, arrData, resultColname, arrResult)
Dim ExcelApp, ExcelBook, ExcelSheet, notNullNumber, intCol
'Create the EXCEL program, open the workbook, set the current active sheet
Set ExcelApp = CreateObject ("Excel.Application")
Set ExcelBook = ExcelApp.WorkBooks.Open (strFilePath)
Set ExcelSheet = ExcelBook.WorkSheets (strSheetName)

'There are a few lines to obtain EXCEL table, columns
rowcount = ExcelBook.ActiveSheet.UsedRange.Rows.Count
colcount = ExcelBook.ActiveSheet.UsedRange.Columns.Count

intCol = getColByValue (strFilePath, strSheetName, resultColname)
'Statistics the number of rows where the column is not empty
notNullNumber = 0

For i = 1 To rowcount
If ExcelSheet.cells (i, intCol )<>"" Then
notNullNumber = notNullNumber +1
End If
Next

If notNullNumber = 1 Then
For i = 0 To UBound (arrResult)
ExcelSheet.cells (arrData (i, 0), intCol). Value = arrResult (i)
Next
Else
For i = 0 To UBound (arrResult)
ExcelSheet.cells (arrData (i, 0), colcount +1). Value = arrResult (i)
Next
End If

ExcelApp.DisplayAlerts = false
ExcelApp.Save
closeExcelSheet ExcelBook, ExcelApp, ExcelSheet
End Sub

'************************************************* *****
'Sub: Close Excel
'Input parameter:
'ExcelBook: Open the Excel
'ExcelApp: Create the Excel object
'ExcelSheet: currently active form
'For example: ExcelBook, ExcelApp, ExcelSheet
'Return:
'************************************************* *****
Sub closeExcelSheet (ExcelBook, ExcelApp, ExcelSheet)

ExcelBook.Close
ExcelApp.Quit
Set ExcelApp = Nothing
Set ExcelBook = Nothing
Set ExcelSheet = Nothing

End Sub

标签： variables xls cells excel application sheet1 input parameter

QTP finishing the operation function excel 2010-12-02

************************************************** **** 'Function: read a value in Excel 'Input parameter: 'StrFilePath: Save Excel file path 'StrExcelSheetName: Excel to read the name in the Sheet 'IntRow: read what their data 'IntCol: which column
jxl operation generated excel 2010-03-29

MS spreadsheet (Excel) is an important member of Office is to preserve a common format for statistical data. As office documents, is bound to involve the exchange of electronic documents, Excel is a very common in the enterprise file formats, print,
jacob operation word excel 2010-03-29

Project development process, the needs are related to a variety of documents are converted to HTML or Web page easy to display format, will now be finishing implementations are as follows: First, understand the Jacob First look at the concept, JACOB
After the operation c # excel how to kill the excel process 2010-03-03

Transfer Kill () method can be, some time is. Net code private void Kill(Microsoft.Office.Interop.Excel.Application _eApp) { IntPtr t = new IntPtr(_eApp.Hwnd); int k = 0; GetWindowThreadProcessId(t, out k); // This process is the only flag k System.D
EXCEL file using JAVA operation (JAVA EXCEL API) 2010-04-22

JAVA EXCEL API Introduction Java Excel is an open source project through which Java developers can read Excel document, create a new Excel file, update an existing Excel file. Non-Windows operating system using the API can also be pure Java applicati
js operation on excel 2010-07-12

Fill in the ActiveXObject using JavaScript and set the Excel format var XLObj = new ActiveXObject ("Excel.Application"); var xlBook = XLObj.Workbooks.Add; / / Add a workbook var ExcelSheet = xlBook.Worksheets (1); / / Create a worksheet 2. Save
Java date and time operation function code list 2010-04-03

/** Date Class * @date * @version 1.0 */ import java.util.*; import java.text.*; import java.util.Calendar; public class VeDate { /** * Get current time * * @return Return time type yyyy-MM-dd HH:mm:ss */ public static Date getNowDate() { Date curren
js floating-point addition and subtraction, multiplication and division operation function 2010-04-27

javascript Budget results in errors, two floating-point numbers that budget when more obvious . This function returns a more precise results . /** * The addition * @param arg1 * @param arg2 * @return */ function accAdd(arg1,arg2){ var r1,r2,m; try{ r
Spring finishing 8 - initialization function and the disappearance of the use of function 2010-05-12

Configuration file: <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"> <beans> <!-- Configure the init
Oracle function Daquan (string function, mathematical function, the date function, logic operation function, other functions) 2010-05-12

SQL in single-record function 1.ASCII Returns the character corresponding to the specified decimal number; SQL> select ascii ('A') A, ascii ('a') a, ascii ('0 ') zero, ascii (' ') space from dual; AA ZERO SPACE --------- --------- --------- ---------
Common method of operation of Excel 2010-10-23

using System; using System.Collections.Generic; using System.Text; using Excel = Microsoft.Office.Interop.Excel; using System.Diagnostics; using System.Windows.Forms; namespace OfficeManager { public class ExcelClass : IDisposable { private Excel._Ap
java time operation function summary 2010-11-26

Frequently used time and date class, so the date will be commonly used methods and properties are summarized below, we find easily 1. Calculate the maximum number of days a month Calendar time = Calendar.getInstance (); time.clear (); time.set (Calen
VBA Excel documents python operation 2011-01-15

Through the python's win32com interface component called Microsoft's office in order to achieve the read and write operations on the excel. This method is only practical in the windows platform, and the need to install Microsoft Office Excel. I use t
java code to write excel and text document import and export [change] 2010-03-29

Example 1 -----" using jxl package to complete the excel import and export JXL package provides a JAVA environment, method of operation of EXCEL files, EXCEL files can read and write operations. Overall, the use of this package is very simple, becaus
With Java there is no good method to generate large amounts of data in Excel 2010-03-19

The earliest Published time :2009-01-13 Online Excel file generated using the Java method, I am most popular POI project, but today from the afternoon until the evening of this part-time (around January 13, 2009 0:54:00) in the read data after numero
win32 API function Daquan 2010-03-19

1. API of the network function WNetAddConnection the same network resources to create a permanent connection WNetAddConnection2 create a connection with a network of resources WNetAddConnection3 create a connection with a network of resources WNetCan
Asp.Net in Word, Excel and other office com component operating rights issue 2010-05-06

Recently opened a web page written in the original run, the emergence of operational authority Excel problem, specifically, is to call the following piece of code with Excel operation of such statement, Application curExcelApp = new ApplicationClass
IE to print the contents of the function frame 2010-06-30

Here are two examples, if deployed to TOMCAT in, you can run index.jsp <%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transiti
Javascript Operation Cookie 2010-07-06

Cookie is to maintain client state solutions, in most server-side languages are available for Cookie direct manipulation of the function, the client, we can use javascript to achieve the operation of the Cookie. Here are a few ways javascript cookie
Operation of the registry using Python 2010-07-12

Associated with the registry operation function can be divided into open registry, close the registry, read the key value, c add the key value, add items and delete items and other types. 1. Open the Registry Before operating the registry, you must o

QTP finishing the operation function excel

热门文章

apache server platform with lighttpd.nginx analysis of the three WEB

Spring order 5 - module assembly

zk-based annotation of the type conversion

JDK J2SE Java SE development process

Android Intent Daquan updates to the API Level8 (change)

SIP Presence-related ...

[Reserved] Learning Dual table

oracle function to use

iPhone下的.NET开发环境 MonoTouch

解析php开发中的中文编码问题

小编推荐

最新更新