-1

HP UFT API Test - Saving Response/Checkpoint values

I was facing the same challenge and found the above thread but, I am trying to figure out the below.

How can I print different response values in a separate column in Excel.

I have 4 responses I was able to add the first one and it shows up properly in excel.

When I added the second response value it prints in either the same cell or if I put both response vaules in a seperate line it prints in a seperate cell but in one row and I need each response to print in it's seperate column.

ID | PersonName | Address | ZipCode

When you go to Write to File under property Tab there is only one field for Content so I can add muiltiple outputs under Expressions but, they all export in a same row.

I am working with Rest and Json UFT-API version 12.52

Any ideas?

UPDATE 1: @ManishChristian

Thanks Manish. I am not able to execute the script. my sheet is located on my desktop so I am assuming that there is no need to add the sql info or connect to database.

This is what my code looks like:

string price;
price = this.CodeActivity50.Input.Amount.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:\\Users/Batman/Desktop/sendAmount.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();

Error thrown:

The Microsoft Access database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1' is not a local object, check your network connection or contact the server administrator.

My sheet name is set to Sheet1

Community
  • 1
  • 1
Json Ulrich
  • 1
  • 2
  • You will need to use **`Custom Code`** to meet your requirement. This [**link**](https://www.joecolantonio.com/2011/12/15/service-test-11-20-how-to-write-web-service-response-to-excel/) will help get started. You can still use info from this link even though it tells about Service Test. – ManishChristian Jan 25 '17 at 14:37
  • My comment is too long so I am posting it below. – Json Ulrich Jan 26 '17 at 16:00
  • `"I am not able to execute the script."` Elaborate. – ManishChristian Jan 26 '17 at 18:40
  • Is there a way to export the data using custom code without having to use the sql code provided in the link. I am trying this but getting an error: string Store; Store = this.CodeActivity50.Input.Amount.ToString(); GetDataSource("C:\\Users/Batman/Desktop/sendAmount.xlsx").SetValue(2,amount, Stored); Error: The name 'amount' does not exist in the current context (CS0103) – Json Ulrich Jan 26 '17 at 18:53
  • First you have to pass `Input` variable with some value to `CustomCode` and than you'll be able to access it in your `CustomCode`. Please check my [answer](http://stackoverflow.com/a/30900339/1652222) on how you can pass and access variable. Same way you can pass multiple variables and access it in your code. – ManishChristian Jan 26 '17 at 19:26
  • I was able to follow the link instructions but, how do I export the response values to excel using that where each response value is parsed in a separate column. – Json Ulrich Jan 26 '17 at 20:14

1 Answers1

0

Here is SQL free way to write data to your Excel file using CustomCode in UFT.

Assumptions:

  • You have set all your four variables as Input parameter for your CustomCode. Here is a link on how to do that.
  • You have imported all the required contents in your CustomCode in order make this code work.


C# Code: 

using Excel = Microsoft.Office.Interop.Excel;
//  IMPORT OTHER REQURED CONTENTS

//  CHANGE THESE VARIABLES AS PER YOUR NEED
//  ALSO CHANGE WS_Method_Type, WS_Method_Name, sEnvironment, TestDataSet_Val  
//  FROM BELOW LINES AS PER YOUR INPUT VARIABLE NAMES
String wsMethodType     = this.CodeActivity16.Input.WS_Method_Type.ToString();
String wsMethodName     = this.CodeActivity16.Input.WS_Method_Name;
String env              = this.CodeActivity16.Input.sEnvironment;
String testDataSetVal   = this.CodeActivity16.Input.TestDataSet_Val.ToString();

String sheetName        = "Sheet1";
String srcFile          = @"PATH \ TO \ YOUR \ XLSX \ FILE";

Excel.Application xlApp = null;
Excel.Workbook wb       = null;
Excel.Worksheet worksheet = null;
Excel.Range excelCell   = null;

xlApp = new Excel.ApplicationClass();
//xlApp.Visible = true;     //  UN-COMMENT ME TO SEE EXCEL
if (xlApp == null)
{
    CodeActivity16.Report("Excel error","Excel could not be started");
}

//  OPENING EXCEL TO WRITE THE DATA     
wb = xlApp.Workbooks.Open(srcFile,
                                   0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                                   true, false, 0, true, false, false);
worksheet = (Excel.Worksheet)wb.Worksheets[sheetName];

int lastUsedRow = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell,Type.Missing).Row;

//  LET'S WRITE INPUT VARIABLE VALUES TO COLUMN A:D IN LAST ROW
//  CHANGE IT AS PER YOUR NEED
excelCell       = (Excel.Range)worksheet.get_Range("A" + lastUsedRow, "A" + lastUsedRow);
excelCell.Value = wsMethodType;

excelCell       = (Excel.Range)worksheet.get_Range("B" + lastUsedRow, "B" + lastUsedRow);
excelCell.Value = wsMethodName;

excelCell       = (Excel.Range)worksheet.get_Range("C" + lastUsedRow, "C" + lastUsedRow);
excelCell.Value = env;

excelCell       = (Excel.Range)worksheet.get_Range("D" + lastUsedRow, "D" + lastUsedRow);
excelCell.Value = testDataSetVal;

wb.Save();
xlApp.Workbooks.Close();
xlApp.Quit();


Note:

You need to add some error handling and clean-up code as this is not final code.

Community
  • 1
  • 1
ManishChristian
  • 3,759
  • 3
  • 22
  • 50