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"}

5 Key Reasons to Choose Private Cloud for Mobile App Testing

Posted on 02/16/2024

Remote work has fixed a lot, but it's made testing mobile apps a ...

Top 12 Automation Testing Trends Shaping 2024

Posted on 01/02/2024

As we enter 2024, what automation testing trends will shape the future? Each ...

The Best Open Source API Testing Tools for 2024

Posted on 01/01/2024

Here is my list of the best open source API testing tools for ...