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.
How to write the output of a database querry to an XML File?
Thulasi » Hi Thulasi- have you tried using ST 11.20’s db activity with the XML\String to XML activity to get the functionality you are looking for? If you use the Database\Select Data activity and point the String To XML’s Source String parameter to Select Data’s results does it work for you? Are you having issues with this approach? Let me know. Cheers~Joe
Hi Joe,
I tried above steps. Test has passed successfully but nothing updated in excel sheet. Please help what else should i check.
Here is my code:
string price;
price = this.CodeActivity5.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=’C:\\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();
STUSER » Hi – what version of Excel are you using? I took the code you sent and was able to run it in ST. It did write the price value to the STDEMO spreadsheet. What happens if you create a new script with just a custom code activity and hard code a value for joePrice does it work? Let me know. Cheers~Joe
@Joe, No need of your time now. I was able to send output to spreadsheet . Thanks for the code
Joe,
I am facing issues with this.I teried the same code.I donot get any error but, I dinot see EXCEL updated.Please help
abc » What version of Excel are you using?
Excel 2003.I tried to save the file in Excel 2007 also.
abc » I will update this post but this code is specifically for Excel 2010. If you are using 2007 you cant try to change the Extended Properties=Excel 12.0 to Extended Properties=Excel 8.0. For 2003 you would have to change the Provider to = Provider=Microsoft.Jet.OLEDB.4.0. Sorry for the confusion. Hope this helps~Cheers Joe
How do I create default handler in step 6.Please explain with example.
what was the issue?
Joe,
I followed all the steps mentioned for this, however, I do not see excel updated.Could you please guibe me on what might be the issue.
Hi, we have encountered an issue with respect to Data Source using ‘Excel’.
Can you please let us know how to iterate a single test flow for multiple Rows in an Excel through Service Test. Let us know the configuration to be made in order to achieve this task.
Kavitha » Hi Kavitha – you should be able to do this by specifying a For loop at the Test Flow Level. For example in your ST test click on the ‘Test Flow’. The Test Flow should appear in your Property Sheet. Click on the Input button (Step Icon). Under the Input section click on the ‘For’ Loop radio button and specify the number of iterations that you want to run. Hope I’m understanding your issue correctly. Please let me know if this works for you. Cheers~Joe
Hi Joe,
Thanks for your quick reply, your information helped me to resolve the issue.
Hi Joe,
I may need your help again. I have similar issue, but I would like import results into local table with in Service test.
For example, I have created some test to add two numbers from local table. I have added three columns (column a, b and c) in local table and I have filled column ‘a’ and column ‘b’ with values and left column ‘c’ empty. My test will add values from columns ‘a’ and ‘b’ with in a loop for all rows in local table.
I would like to get the result into column ‘c’ ..
is there any way to do that please?
Many Thanks…
Hi Joe,
First, I want to thank about giving all this information, because in HP Service Test Help file many things are not clear and on internet also it is very difficult to find these answers, I think you are only the right guy.
My question is : I am getting multi-element response lick for example If I pass book title as input value to “searchbookbyTitle” operation then some time I will be getting 1 book information and sometime more than one (now this is dynamic) but response element name are same (response element with same name but in array parent element)
Eg :
In Select Link Source window : Output section :
InventoryItem[1]
Author
Description
Id
Isbn
InventoryItme[2]
Author
Description
Id
Isbn
So when I used above procedure to write response to excel it is writing only first array “InventoryItem[1]” values now I can select other InventoryIteme[2] elements and add them to “Expression” box in “Select Link Source window” but it will throw error
(and practically we shouldn’t select each array element because it may be 1000 element also – and we can’t predict also the number of these multi-element response)
so maybe I am not sure there is option through which I can write multi-element values to excel file, there is “TestFlow” node in “Available Steps” section of Select Link Source window with two child Properties (“Current iteration number” and “Number of iteration”)
but I am not able to use this option and may be it is not for this purpose, I tried to in HP Service Test help file and on other sits available on internet but didn’t got any solution
can you please tell me solution.
Hi Joe,
I used above method to write in excel but in my case some time I am getting multi element value response for example operation taking input as book name and giving output of available books in stock with book isbn number, author information. So there may be possibility that I pass C++ book title then I may getting two book information or more in one response in that case how I can store entire multi element response in different row of excel. (eg if input C++ and out put info is for 3 book then store in 3 different rows of excel)
is there any way to do that please?
thanks.
Hi Joe/All,
We are planning to use UFT 11.5 for web service automation, is there any framework or approach similar to qtp automation ? Is there a way to implement data driven framework for hp st ? Please share the details.
Thanks in advance.
– Rakhi
Hi Joe,
How do I read Response Header information? I need to pass this information to next service on fly. Can you please help on this please. Thanks – Vijay
Hi Joe, We are working on UFT 11.5 currently. We have some clarifications. Like a “query id” that gets generated for test flow in service test we do not see one getting generated for UFT. due to this we are unable to export value to a data in data table.
Query in Service Test:
sql = “UPDATE [ZipCodeDetails$] SET CITY='” + city + “‘,ST='” + region + “‘,Country='” + country + “‘ WHERE TC_ID='”+this.Query1.GetValue(“TC_ID”).ToString()+”‘”;
this.query1 that we have used here am not sure how to handle in UFT. Please guide me.
Hi Joe,
As in the below query I need to update an excel row for a specific condition
say Update [Sheet1$] set tcstatus = ‘pass’ where tcid=’tc1′;
i tried this, but its not updating the cell. can you please help me on this
string strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” +”Data Source=”+ Path +”;”+”Extended Properties=Excel 8.0;”;
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = “”;
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel=”select * from [sheet1$]”;
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds,”table1″);
return ds;
Hello Joel ,
We are planning to implement Web Service Automation Framework using Functional approach .
Can you share across a few sample frame works please ?
Kumar » Hi Kumar – sorry for my late reply. To get header information you can use custom code. In the web service’s STServiceCallActivity you can add this.StServiceCallActivity.HttpResponseHeaders to capture response info
Instead of writing the response to the local machine.how can I write the response to the spreadsheet that is stored in QC in Test resources. How can I do that? How can I get the path of the spread sheet in the Test resources in QC?
Hi Joe
Can you respond to my query please? How to write response to an excel that is stored in QC? In the custom code – under Data source I’ve given the path of my excel sheet that is in QC like this “[QualityCenter\\Resources] Resources\BB\data\DEMO.xlsx” doesnt work. How can I give the path of a file that is stored in Test resouces folder? Please help
Thanks
Sri » You should be able to use OTA similar to the example I showed in my post “How to save a file to a QC resource using QTP with OTA”
Thanks Joe. But I was thinking to write webservice response from HP service test 11.2 directly to QC- resource module. Is there a way I can do that WITHOUT using QTP?
Hi Joe,
Thanks for sharing the above information. I was able to use it in my UFT API Test version 11.52 with a linked Excel data option.
After saving the UFT API Test in ALM, other users of my team are unable to open the API Test in ALM when they tried opening them in their UFT. It seemed, when I included the absolute path of the linked Excel file, it prevents any user from adding the API Test into their solution.
Is there a way to fix this? Any suggestion is much appreciated.
-Jose
Hi Joe,
We have a regression suite developed using HPST 9.5 and dont have cleint approval to migrate to the newer version (11.2 or 11.5) for those scripts. Could you please help for the below:
Problem:
Currently, we are manually capturing the test results by copy pasting into a notepad from the report generated.
Needed solution: is there a way we can redirect the results from the report generated o notepad?
Hi – its been awhile since I used ST 9.5 – since its built on LoadRunners Vugen can’t you use C to write to a file. Something like this:
//Function to write to an output file
int LogFile(char *buffer)
{
int fd1;
//change the output file location if needed
char *filename = “c:\\temp\\MyOutput.log”;
//check if there is a fopen error.
//If fopen is ok, will append to the output file. Else, return an error and exit
if((fd1 = fopen(filename, “a”)) == NULL)
{
lr_error_message(“ERROR: Failed to open file for appending: %s”, filename);
return -1;
}
//append to outputfile
fprintf(fd1, “%s”, buffer);
//close file
fclose(fd1);
}
Hi Joe,
i want to connect to db through HP ST.not through the code.directly using the Data Source Option available in HP ST.
Could you please help me out how to connect
-Shanmukha
Hi Joe,
My question is similar to Sri’s above. How can you write a value to a referenced DataTable in ST/API 11.52(3) within the custom code. The way it works in a GUI script does not seem to work here (DataTable(“column_header”, dtLocalSheet);)
I’ve tried it two ways:
var = DataTable(“column_header”, dtLocalSheet);
string var = DataTable.ReferenceEquals(var, dtLocalSheet);
Any ideas?
as always Joe , you are a genius ! love your TestTalk and good solutions here.
Thanks!
Hi Joe,
While I am importing excel data file from local drive using the line of code GetDataSource(“InputData!Sheet2″).Import(new ExcelfileImportArgs(@”C:\InputData.xls”,”Sheet2″,True);
getting error message that “A data source with specified name does not exist.” and if I am adding it through data source wizard it is working fine.
Can you please look into it and help me out?
Thanks in advance for your help and support!
Regards,
Chandra
How to add muiltiple response data to excel datasheet using write to file function from the toolbox menu -> File -> Write to File.
I was able to add one item of response under Write to File method in the field contain but I need to store muiltiple items and there is only 1 Contain option.
Thanks,
Avaya
Hi Joe,
Your website is a good one.It has helped me during many difficult situation. Thanks a lot.
I have a question.I want to parameterize the web service request with data from excel sheet. Can you help me to parameterize the request using custom coding?
Currently I am linking each field manually to data source . I have a request with more than 300 fields.So it is taking a lot of time.
Thanks in advance.
Regards,
Anusha