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:

string price;
price = this.CodeActivity12.Input.joePrice.ToString();
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\\STDEMO.xlsx';Extended Properties=Excel 12.0;Persist Security Info=False");
MyConnection.Open();
myCommand.Connection = MyConnection;
sql = "INSERT into [Sheet1$] (COLUMN1) values('" + price + "')";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
MyConnection.Close();

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

Tagged as: Excel, HP Service Test