Reports Not getting Generated and also ending up with Error.

Ganesh,

The way data driven framework is made has not changed. Yes Zoho.com has changed.

I will be updating the videos but first I am working on Cucumber these days. After updating cucumber, I will be doing that.

I do not see any screenshot

Hi,

I have attached again. Please have a look and help to resolve it. Also I have a doubt that currently we can fetch data from excel sheet using Apache Poi / still from Xls_Reader. 

Also please find my XLS_Reader file. I believe getting error because of that.

package com.qtpselenium.zoho.project.util;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.util.Calendar;

public class Xls_Reader {
public static String filename = System.getProperty("user.dir")+"\\src\\config\\testcases\\TestData.xlsx";
public String path;
public FileInputStream fis = null;
public FileOutputStream fileOut =null;
private XSSFWorkbook workbook = null;
private XSSFSheet sheet = null;
private XSSFRow row =null;
private XSSFCell cell = null;

public Xls_Reader(String path) {

this.path=path;
try {
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheetAt(0);
fis.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
// returns the row count in a sheet
public int getRowCount(String sheetName){
int index = workbook.getSheetIndex(sheetName);
if(index==-1)
return 0;
else{
sheet = workbook.getSheetAt(index);
int number=sheet.getLastRowNum()+1;
return number;
}

}

// returns the data from a cell
public String getCellData(String sheetName,String colName,int rowNum){
try{
if(rowNum <=0)
return "";

int index = workbook.getSheetIndex(sheetName);
int col_Num=-1;
if(index==-1)
return "";

sheet = workbook.getSheetAt(index);
row=sheet.getRow(0);
for(int i=0;i<row.getLastCellNum();i++){
//System.out.println(row.getCell(i).getStringCellValue().trim());
if(row.getCell(i).getStringCellValue().trim().equals(colName.trim()))
col_Num=i;
}
if(col_Num==-1)
return "";

sheet = workbook.getSheetAt(index);
row = sheet.getRow(rowNum-1);
if(row==null)
return "";
cell = row.getCell(col_Num);

if(cell==null)
return "";
//System.out.println(cell.getCellType());
if(cell.getCellType()==Cell.CELL_TYPE_STRING)
return cell.getStringCellValue();
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.DAY_OF_MONTH) + "/" +
cal.get(Calendar.MONTH)+1 + "/" +
cellText;

//System.out.println(cellText);

}
return cellText;
}else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
return "";
else
return String.valueOf(cell.getBooleanCellValue());

}
catch(Exception e){

e.printStackTrace();
return "row "+rowNum+" or column "+colName +" does not exist in xls";
}
}

// returns the data from a cell
public String getCellData(String sheetName,int colNum,int rowNum){
try{
if(rowNum <=0)
return "";

int index = workbook.getSheetIndex(sheetName);

if(index==-1)
return "";


sheet = workbook.getSheetAt(index);
row = sheet.getRow(rowNum-1);
if(row==null)
return "";
cell = row.getCell(colNum);
if(cell==null)
return "";

if(cell.getCellType()==Cell.CELL_TYPE_STRING)
return cell.getStringCellValue();
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(cellText);

}

return cellText;
}else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
return "";
else
return String.valueOf(cell.getBooleanCellValue());
}
catch(Exception e){

e.printStackTrace();
return "row "+rowNum+" or column "+colNum +" does not exist in xls";
}
}

// returns true if data is set successfully else false
public boolean setCellData(String sheetName,String string,int rowNum, String data){
try{
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);

if(rowNum<=0)
return false;

int index = workbook.getSheetIndex(sheetName);
int colNum=-1;
if(index==-1)
return false;


sheet = workbook.getSheetAt(index);

row=sheet.getRow(0);
for(int i=0;i<row.getLastCellNum();i++){
//System.out.println(row.getCell(i).getStringCellValue().trim());
if(row.getCell(i).getStringCellValue().trim().equals(string))
colNum=i;
}
if(colNum==-1)
return false;

sheet.autoSizeColumn(colNum);
row = sheet.getRow(rowNum-1);
if (row == null)
row = sheet.createRow(rowNum-1);

cell = row.getCell(colNum);
if (cell == null)
cell = row.createCell(colNum);

// cell style
CellStyle cs = workbook.createCellStyle();
cs.setWrapText(true);
cell.setCellStyle(cs);
cell.setCellValue(data);

fileOut = new FileOutputStream(path);

workbook.write(fileOut);

fileOut.close();

}
catch(Exception e){
e.printStackTrace();
return false;
}
return true;
}


// returns true if data is set successfully else false
public boolean setCellData(String sheetName,String colName,int rowNum, String data,String url){
//System.out.println("setCellData setCellData******************");
try{
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);

if(rowNum<=0)
return false;

int index = workbook.getSheetIndex(sheetName);
int colNum=-1;
if(index==-1)
return false;


sheet = workbook.getSheetAt(index);
//System.out.println("A");
row=sheet.getRow(0);
for(int i=0;i<row.getLastCellNum();i++){
//System.out.println(row.getCell(i).getStringCellValue().trim());
if(row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName))
colNum=i;
}

if(colNum==-1)
return false;
sheet.autoSizeColumn(colNum); //ashish
row = sheet.getRow(rowNum-1);
if (row == null)
row = sheet.createRow(rowNum-1);

cell = row.getCell(colNum);
if (cell == null)
cell = row.createCell(colNum);

cell.setCellValue(data);
XSSFCreationHelper createHelper = workbook.getCreationHelper();

//cell style for hyperlinks
//by default hypelrinks are blue and underlined
CellStyle hlink_style = workbook.createCellStyle();
XSSFFont hlink_font = workbook.createFont();
hlink_font.setUnderline(XSSFFont.U_SINGLE);
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hlink_style.setFont(hlink_font);
//hlink_style.setWrapText(true);

XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
link.setAddress(url);
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);

fileOut = new FileOutputStream(path);
workbook.write(fileOut);

fileOut.close();

}
catch(Exception e){
e.printStackTrace();
return false;
}
return true;
}



// returns true if sheet is created successfully else false
public boolean addSheet(String sheetname){

FileOutputStream fileOut;
try {
workbook.createSheet(sheetname);
fileOut = new FileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}

// returns true if sheet is removed successfully else false if sheet does not exist
public boolean removeSheet(String sheetName){
int index = workbook.getSheetIndex(sheetName);
if(index==-1)
return false;

FileOutputStream fileOut;
try {
workbook.removeSheetAt(index);
fileOut = new FileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
// returns true if column is created successfully
public boolean addColumn(String sheetName,String colName){
//System.out.println("**************addColumn*********************");

try{
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);
int index = workbook.getSheetIndex(sheetName);
if(index==-1)
return false;

XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

sheet=workbook.getSheetAt(index);

row = sheet.getRow(0);
if (row == null)
row = sheet.createRow(0);

//cell = row.getCell();
//if (cell == null)
//System.out.println(row.getLastCellNum());
if(row.getLastCellNum() == -1)
cell = row.createCell(0);
else
cell = row.createCell(row.getLastCellNum());

cell.setCellValue(colName);
cell.setCellStyle(style);

fileOut = new FileOutputStream(path);
workbook.write(fileOut);
fileOut.close();

}catch(Exception e){
e.printStackTrace();
return false;
}

return true;


}
// removes a column and all the contents
public boolean removeColumn(String sheetName, int colNum) {
try{
if(!isSheetExist(sheetName))
return false;
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);
sheet=workbook.getSheet(sheetName);
XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
@SuppressWarnings("unused")
XSSFCreationHelper createHelper = workbook.getCreationHelper();
style.setFillPattern(HSSFCellStyle.NO_FILL);



for(int i =0;i<getRowCount(sheetName);i++){
row=sheet.getRow(i);
if(row!=null){
cell=row.getCell(colNum);
if(cell!=null){
cell.setCellStyle(style);
row.removeCell(cell);
}
}
}
fileOut = new FileOutputStream(path);
workbook.write(fileOut);
fileOut.close();
}
catch(Exception e){
e.printStackTrace();
return false;
}
return true;

}
// find whether sheets exists
public boolean isSheetExist(String sheetName){
int index = workbook.getSheetIndex(sheetName);
if(index==-1){
index=workbook.getSheetIndex(sheetName.toUpperCase());
if(index==-1)
return false;
else
return true;
}
else
return true;
}

// returns number of columns in a sheet
public int getColumnCount(String sheetName){
// check if sheet exists
if(!isSheetExist(sheetName))
return -1;

sheet = workbook.getSheet(sheetName);
row = sheet.getRow(0);

if(row==null)
return -1;

return row.getLastCellNum();



}
//String sheetName, String testCaseName,String keyword ,String URL,String message
public boolean addHyperLink(String sheetName,String screenShotColName,String testCaseName,int index,String url,String message){
//System.out.println("ADDING addHyperLink******************");

url=url.replace('\\', '/');
if(!isSheetExist(sheetName))
return false;

sheet = workbook.getSheet(sheetName);

for(int i=2;i<=getRowCount(sheetName);i++){
if(getCellData(sheetName, 0, i).equalsIgnoreCase(testCaseName)){
//System.out.println("**caught "+(i+index));
setCellData(sheetName, screenShotColName, i+index, message,url);
break;
}
}

return true;
}
public int getCellRowNum(String sheetName,String colName,String cellValue){

for(int i=2;i<=getRowCount(sheetName);i++){
if(getCellData(sheetName,colName , i).equalsIgnoreCase(cellValue)){
return i;
}
}
return -1;

}

// to run this on stand alone
public static void main(String arg[]) throws IOException{

//System.out.println(filename);
Xls_Reader datatable = null;

datatable = new Xls_Reader("H:\\Student_Selenium_Workspaces\\Framework_Weekend\\src\\Framework_XL_Files\\Controller.xlsx");
for(int col=0 ;col< datatable.getColumnCount("TC5"); col++){
System.out.println(datatable.getCellData("TC5", col, 1));
}
}


}

Plz zip your project and send it to me

Hi Ashish,

Please find the attached Zip file of my project. 

attached

Hi Ashish,

Please reply.

xls_path = D:\\temp\\Datas.xlsx

This is in properties file

Is this correct?

Yes Its Right. I have created Datas excel in D drive inside temp folder.

Hi Ashish,

Your prompt reply will be more helpful.

Sorry for late reply

Is sheet name LoginTest

Plz pass your xls file

Hi,

Please find the attached sheet.

Ok

There are 2 issues:

1) Name of your properties file is projectconfiguration.properties but in init function you declared wrong name - projectconfig.properties

FileInputStream fs = new FileInputStream(System.getProperty("user.dir")+"//src//test//resources//projectconfig.properties");

2) I your data sheet.. dont mention TestA... metiion Login Test