Retrieve of different values from excel sheet

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Retrieve of different values from excel sheet

Preetha Devi
Hi Team,

Kindly help me for the below scenario using TestNG and DataProvider.

Scenario:Pick the user and his ticket
Cond: whose ticket status is open and order by very latest ticket of the user followed by old ticket to the user

Below is the test data.

sl no User Ticket Raised Date Ticket Status
1 1 Loginissue 07-07-2018 Open 
2 2 id issue 06-07-2018 Closed
3 3 project issue 06-07-2018 Closed
4 3 project issue 07-07-2018 Open 
5 5 project issue 05-07-2018 Closed
6 3 id issue 05-07-2018 Open
7 2 project issue 07-07-2018 Closed
8 3 id issue 05-07-2018 Open 
9 4 project issue 07-07-2018 Closed
10 3 Loginissue 07-07-2018 Closed
11 6 Loginissue 06-07-2018 Closed
12 3 id issue 07-07-2018 Open 

--
You received this message because you are subscribed to the Google Groups "testng-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/testng-users.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Retrieve of different values from excel sheet

Krishnan Mahadevan

Can you please show us what you have as code so far and where you need help ?

 

Thanks & Regards

Krishnan Mahadevan

 

"All the desirable things in life are either illegal, expensive, fattening or in love with someone else!"

My Scribblings @ http://wakened-cognition.blogspot.com/

My Technical Scribbings @ http://rationaleemotions.wordpress.com/

 

From: <[hidden email]> on behalf of Preetha Devi <[hidden email]>
Reply-To: <[hidden email]>
Date: Tuesday, July 10, 2018 at 4:08 AM
To: testng-users <[hidden email]>
Subject: [testng-users] Retrieve of different values from excel sheet

 

Hi Team,

 

Kindly help me for the below scenario using TestNG and DataProvider.

 

Scenario:Pick the user and his ticket

Cond: whose ticket status is open and order by very latest ticket of the user followed by old ticket to the user

 

Below is the test data.

 

sl no

User

Ticket

Raised Date

Ticket Status

1

1

Loginissue

07-07-2018

Open 

2

2

id issue

06-07-2018

Closed

3

3

project issue

06-07-2018

Closed

4

3

project issue

07-07-2018

Open 

5

5

project issue

05-07-2018

Closed

6

3

id issue

05-07-2018

Open

7

2

project issue

07-07-2018

Closed

8

3

id issue

05-07-2018

Open 

9

4

project issue

07-07-2018

Closed

10

3

Loginissue

07-07-2018

Closed

11

6

Loginissue

06-07-2018

Closed

12

3

id issue

07-07-2018

Open 

--
You received this message because you are subscribed to the Google Groups "testng-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/testng-users.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "testng-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/testng-users.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Retrieve of different values from excel sheet

TotalQA
In reply to this post by Preetha Devi
Hi Preetha,

Please refer to the complete scenario of reading the Data from xls and passing to @Test via Data provider.



If you need to read the data from XLSX please go through the below link:


Let me know if you are still having doubts.

Regards,





On Tuesday, July 10, 2018 at 4:08:20 AM UTC+5:30, Preetha Devi wrote:
Hi Team,

Kindly help me for the below scenario using TestNG and DataProvider.

Scenario:Pick the user and his ticket
Cond: whose ticket status is open and order by very latest ticket of the user followed by old ticket to the user

Below is the test data.

sl no User Ticket Raised Date Ticket Status
1 1 Loginissue 07-07-2018 Open 
2 2 id issue 06-07-2018 Closed
3 3 project issue 06-07-2018 Closed
4 3 project issue 07-07-2018 Open 
5 5 project issue 05-07-2018 Closed
6 3 id issue 05-07-2018 Open
7 2 project issue 07-07-2018 Closed
8 3 id issue 05-07-2018 Open 
9 4 project issue 07-07-2018 Closed
10 3 Loginissue 07-07-2018 Closed
11 6 Loginissue 06-07-2018 Closed
12 3 id issue 07-07-2018 Open 

--
You received this message because you are subscribed to the Google Groups "testng-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/testng-users.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Retrieve of different values from excel sheet

Vimal Raj
In reply to this post by Preetha Devi
Hi Preetha,

Before performing any actions on your data, let's wrap each row of data into an object, say for example, each row is a User object. (Create a User pojo class with getters and setters if required)

Let's solve each conditions first.

order by very latest ticket of the user

You could use Java 8's lambdas and sorting features to perform this. Refer: Lamdas and Sorting
So you have to use the Comparator to compare the User Ids. Now you have all the user ids compared, the sort operation is not yet done.

followed by old ticket to the user

Now you have to compare the Reserved dates in a reverse order (latest ticket first, then the old ticket). So you will have to chain the comparator using thenComparing using reversed.

Now we are ready to perform the sorting. You will have to use Java 8's stream and perform sorting using the comparator that you have built, refer sorted method from Stream api.

Now you have the Stream which has the data sorted in a required manner.

whose ticket status is open

Now you have to apply the filter to remove the closed tickets using filter method (build a predicate to check the status == open) and then make it as a List.

That's all you have to do. Hope this helps!

Thanks
Vimalraj


On Tue, 10 Jul 2018 at 04:08, Preetha Devi <[hidden email]> wrote:
Hi Team,

Kindly help me for the below scenario using TestNG and DataProvider.

Scenario:Pick the user and his ticket
Cond: whose ticket status is open and order by very latest ticket of the user followed by old ticket to the user

Below is the test data.

sl no User Ticket Raised Date Ticket Status
1 1 Loginissue 07-07-2018 Open 
2 2 id issue 06-07-2018 Closed
3 3 project issue 06-07-2018 Closed
4 3 project issue 07-07-2018 Open 
5 5 project issue 05-07-2018 Closed
6 3 id issue 05-07-2018 Open
7 2 project issue 07-07-2018 Closed
8 3 id issue 05-07-2018 Open 
9 4 project issue 07-07-2018 Closed
10 3 Loginissue 07-07-2018 Closed
11 6 Loginissue 06-07-2018 Closed
12 3 id issue 07-07-2018 Open 

--
You received this message because you are subscribed to the Google Groups "testng-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/testng-users.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "testng-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/testng-users.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Retrieve of different values from excel sheet

akash kansal
In reply to this post by Preetha Devi
Hi,

Try This

@DataProvider
public Object[][] insert(Method m)
{

System.out.println("Data Provider Executing method name:-"+m.getName());
Class<? extends Object> className = m.getDeclaringClass();
/*System.out.println(className.getSimpleName());
Method[] meth =className.getDeclaredMethods();
for (Method method : meth) {
System.out.println(method.getName());
}*/
ExcelReader excel = new ExcelReader(System.getProperty("user.dir")+"//src//test//resources//excel//"+className.getSimpleName()+".xlsx");
String sheetname ="Sheet1";
int rows = excel.getRowCount(sheetname);
int column = excel.getColumnCount(sheetname);
Map<String,List<Map<String,String>>> allData = new LinkedHashMap<>();
for(int i=1;i<=rows;i++)
{
HashMap<String,String> map = new HashMap<>();
for(int j=1;j<column;j++)
{
//System.out.println(excel.getCellData(0, j,sheetname)+" : "+excel.getCellData(i, j,sheetname));
map.put(excel.getCellData(0, j,sheetname),excel.getCellData(i, j,sheetname));
}
String methodName = excel.getCellData(i, 0,sheetname);
if(allData.get(methodName) == null) {
allData.put(methodName, new ArrayList<Map<String,String>>());
}
allData.get(methodName).add(map);
}

if(!allData.containsKey(m.getName()))                         Change this as per your condition your closed test case will gets skipped
{
Reporter.log("Test start onTestStart:- "+m.getName());
Test test = m.getAnnotation(Test.class);
ExtentTestManager.startTest(className.getSimpleName()+"."+m.getName().toUpperCase(),test.description());
ExtentTestManager.getTest().assignCategory(className.getSimpleName());
ExtentTestManager.getTest().log(LogStatus.INFO,"Test Going to be skipped",m.getName());
throw new SkipException("Test Case Skipped as not present in "+className.getSimpleName()+".xlsx"+" Excel");
}
Object [][] arr = new Object [allData.get(m.getName()).size()][1];

for(int i=0;i<arr.length;i++) //No. of Rows should be -1 as starting to fetch value from Second row.
{
arr[i][0] = allData.get(m.getName()).get(i);
System.out.println(i+": "+arr[i][0].toString());
}
return arr;
}




Test Parameters
@Test
public void two(HashMap<String,String> table)
{
}








Second Classs-----------------------------------------------------------------

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReader {

public String path;
public FileInputStream fis = null;
public FileOutputStream fos = null;
public XSSFWorkbook workbook = null;
public XSSFSheet sheet = null;
public XSSFRow row = null;
public XSSFCell cell = null;

public ExcelReader(String path)
{
this.path =path;
try {
fis = new FileInputStream(new File(path));
workbook = new XSSFWorkbook(fis);
//int sheetSize = workbook.getNumberOfSheets();
//sheet = workbook.getSheetAt(0);
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}

public int getRowCount(String sheetname)
{
if(!sheetname.equals("") || !sheetname.equals(null))
{
int index = workbook.getSheetIndex(sheetname);
if(index == -1)
{
return 0;
}
else
{
sheet = workbook.getSheetAt(index);
int number = sheet.getLastRowNum();
return number;
}
}
else
{
sheet = workbook.getSheetAt(0);
int number = sheet.getLastRowNum();
return number;
}
}

public int getColumnCount(String sheetname)
{
if(!sheetname.equals("") || !sheetname.equals(null))
{
int index = workbook.getSheetIndex(sheetname);
if(index == -1)
{
return 0;
}
else
{
sheet = workbook.getSheetAt(index);
int number = sheet.getRow(0).getLastCellNum();
return number;
}
}
else
{
sheet = workbook.getSheetAt(0);
int number = sheet.getRow(0).getLastCellNum();
return number;
}
}

@SuppressWarnings("deprecation")
public String getCellData(int rowNo,int colNo,String sheetName)
{
int index = workbook.getSheetIndex(sheetName);
if(index == -1)
{
return "Sheet Not found";
}
sheet = workbook.getSheetAt(index);
row = sheet.getRow(rowNo);
//System.out.println("row no.:- "+rowNo);
//System.out.println("colNo:- "+colNo);
cell = row.getCell(colNo);
if(cell == null)
{
System.out.println("Column not exist");
return "No Value in column";
}
else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC || cell.getCellType()==Cell.CELL_TYPE_FORMULA ){

String cellText  = String.valueOf(cell.getNumericCellValue());
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// format in form of M/D/YY
double d = cell.getNumericCellValue();

Calendar cal =Calendar.getInstance();
cal.setTime(HSSFDateUtil.getJavaDate(d));
cellText =
(String.valueOf(cal.get(Calendar.YEAR))).substring(2);
cellText = cal.get(Calendar.MONTH)+1 + "/" +
cal.get(Calendar.DAY_OF_MONTH) + "/" +
cellText;
}
System.out.println("Date type");
return cellText;
}
else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
{
System.out.println("No Value in Cell");
return "";
}
else
{
//System.out.println(cell.getStringCellValue());
return cell.getStringCellValue();
}
}
}


On Sat, Jul 7, 2018 at 11:42 PM, Preetha Devi <[hidden email]> wrote:
Hi Team,

Kindly help me for the below scenario using TestNG and DataProvider.

Scenario:Pick the user and his ticket
Cond: whose ticket status is open and order by very latest ticket of the user followed by old ticket to the user

Below is the test data.

sl no User Ticket Raised Date Ticket Status
1 1 Loginissue 07-07-2018 Open 
2 2 id issue 06-07-2018 Closed
3 3 project issue 06-07-2018 Closed
4 3 project issue 07-07-2018 Open 
5 5 project issue 05-07-2018 Closed
6 3 id issue 05-07-2018 Open
7 2 project issue 07-07-2018 Closed
8 3 id issue 05-07-2018 Open 
9 4 project issue 07-07-2018 Closed
10 3 Loginissue 07-07-2018 Closed
11 6 Loginissue 06-07-2018 Closed
12 3 id issue 07-07-2018 Open 

--
You received this message because you are subscribed to the Google Groups "testng-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/testng-users.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "testng-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/testng-users.
For more options, visit https://groups.google.com/d/optout.