Automation Testing

UFT API How to Write Web Service Response to Excel

By Test Guild
  • Share:
Join the Guild for FREE
UFT API Cover

The other day I received an e-mail from my blog-reading friend Samar. He asked:

“I've created a local Excel table with one column (e.g Column1). How can I set a column programmatically?”

Rather than sending him a long e-mail reply, I've decided to answer in the form of a blog post, which follows below.

How to Programmatically Write to Excel 2010

1. First, start the HP Flight sample application and enter the end point http://localhost:24240/HPFlights_SOAP?WSDL to import the flight web services into Service Test

(For step-by-step instructions, be sure to check out my post, HP Service Test 11 – How to Test a Web Services Video).

2. Drag the CreateFlightOrder operation onto the main canvas area.

3. Drag a Custom Code activity onto the main canvas area.

4. Under the Custom Code's “Property Sheet,” create an Input value named joePrice.

5. Click on the “Event” icon and create a default handler for the ExecuteEvent.

6. Enter the following code under your onExecuteEvent under the TestUserCode.cs section:

7. Run the script. You should now have a price value in your Excel file:

How to Use Service Test's Built-in Database Commands to Write to an Excel File

1. Follow steps 1 through 3 (above).

2. Drag a Concatenate String operation onto the main canvas area.

3. Under the ‘Concatenate Strings' “Property Sheet,” enter the following under the Prefix “Property”:

INSERT into [Sheet1$] (COLUMN1) values (‘

For the Suffix, click on the “link to data source,” and point the TotalPrice value from the CreateFlightOrder operation:

4. Drag another Concatenate String operation onto the main canvas area.

For the Prefix, click on the “link to data source,” and point to the previous Concatenate String's Result output.

For the Suffix, enter ‘).

5. Drag an Open Connection operation onto the main canvas area.

6. Under the Open Connection “Property Sheet,” enter the following under the Connection String:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\\STDEMO.xlsx';Extended Properties=Excel 12.0;Persist Security Info=False

(FYI – IF you do not have the ACE ODBC driver you should be able to get it by installing http://www.microsoft.com/download/en/details.aspx?id=13255

7. Drag an Execute Command operation onto the main canvas area.

For the Connection property, point to the Open Connection's “Connection Output” property.

For the Command Property, point to the previous Concatenation String's “Results” property.

8. Run the script. You should now have a price value in your Excel file.

Hope this post helps you to excel with Excel and HP Services Test 11.20

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

SafeTest: Next Generation Testing Framework from Netflix

Posted on 03/26/2024

When you think of Netflix the last thing you probably think about is ...

Top Free Automation Tools for Testing Desktop Applications (2024)

Posted on 03/24/2024

While many testers only focus on browser automation there is still a need ...

Bridging the Gap Between Manual and Automated Testing

Posted on 03/13/2024

There are some testers who say there’s no such thing as manual and ...