Friday, 26 August 2016

How to get row count,column count in Excel useful for selenium

In this post I am going to explain how can we get row count,column count in excel file with the help of Java using Apache POI jar libraries.
 Download Apache POI jar libraries in following link  and configure to your project.
 Download Apache POI jar

Sample code:
import org.apache.poi.xssf.usermodel.*;
import java.io.*;

public class Xls_Reader
{
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 static String sActionKeyword=null;


//Constructor for path configuration
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 number of  rows 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 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();
}
        // 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;
}
//usage
public static void main(String[] args){

Xls_Reader xls = new Xls_Reader("C:/Users/Sudharsan/Desktop/TestData.xlsx");
System.out.println(xls.isSheetExist("Login"));
System.out.println(xls.getRowCount("Login"));
System.out.println(xls.getColumnCount("Login"));


}

2 comments:

  1. Thank you so much for sharing this worth able content with us. The concept taken here will be useful for my future programs and i will surely implement them in my study. Keep blogging article like this.
    selenium training in bangalore|

    ReplyDelete
  2. I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.
    "Selenium Training in Marathahalli"

    ReplyDelete