Automation Testing

UFT API How to Write Web Service Response to Excel

By Test Guild
  • Share:
Join the Guild for FREE

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

9 Ways AI Improves Software Testing

Posted on 07/02/2024

I recently co-hosted a webinar, with Blinq.io to delve into the impact of ...

Strategic Test Environment Setup For Automation Success

Posted on 06/18/2024

Understanding the importance of test environment setup Test environment setup is crucial for ...

Top 18 Visual Testing Tools for Testers (2024 Guide)

Posted on 06/10/2024

When you're a web developer, making sure that your site looks great – ...

NEW WEBINAR ALERT: Using AI to Simplify & Speed Up ETL TestingI --  Register Now!