How do I write to separate column in Excel using UFT API

0
0

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

  • You must to post comments
0
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.

  • You must to post comments
Showing 1 result
Your Answer
Post as a guest by filling out the fields below or if you already have an account.
Name*
E-mail*
Website