Study Selenium
A Site for Selenium Professionals
Friday, 3 January 2014
Read/write excel file in Java
Read/write excel file in Java
package datatable;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
//import org.apache.poi.hssf.usermodel.HSSFHyperlink;
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 colName,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(colName))
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);
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;
}
Back to Selenium Interview Questions and Answers
Posted by ruchi goyal at 1/03/2014 01:49:00 pm
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: excelreader in java, program excel, Read/write excel file in Java
8 comments:
1.
teresa10 March 2014 09:35
thanks Ruchi.. you are a real life saver :)
Reply
Replies
1.
Anonymous2 April 2014 23:26
Thanks
2.
Anonymous2 April 2014 23:26
Thanks
3.
Anonymous22 May 2014 13:19
hiii
Reply
2.
Anonymous22 May 2014 13:18
hiii
Reply
3.
Srikool26 May 2014 16:19
Which is best for writing a file from selenium web driver..poi is there..jxl is
there..heard few using ant to write excel file...im confused which one is best to write
the result.
Reply
4.
Srikool26 May 2014 16:23
what is the difference in using poi jar file,jxl jar file or some other method to write the
result file..which method is best.
Reply
5.
Anonymous10 September 2014 18:01
hi am new to selenium webdriver how to check all the sublinks of the websites from
excel sheet and i have error while taking another URL for link text method
Reply
Load more...
Newer Post Older Post Home
Subscribe to: Post Comments (Atom)
About Me
ruchi goyal
View my complete profile
Search This Blog
Total Pageviews
473920
Labels
Alert box in selenium
array unique elements
AutoIT
autoit example
AutoIt Scripts
AutoIT scripts to file handling
basic java questions
broken image in selenium
comparing pixels
countuniquewords from file
Database
Database setup using Java
Databse Connection using Java
Download file using Selenium
excelreader in java
extract unique elements from array
factorial
fibonacci
File dialog in selenium
file handling in selenium
file screenshot in java
File Upload in Selenium
findelement and findelements
Flex Testing using selenium.Selenium Flex Automation
Flex Webdriver
Handling System Window
image compare in java
image comparison in java
itextpdf
java factorial program
java interview
Java interview questions
java program swapnumbers
java program to calculate factorial
java program to calculate palindrome
java program to countwords
Java Program to get unique elements from array
java program to print Fibonacci
java program to read xml file
java question-answers
java read xmll file
java series
matrix java program
maTrix Transposal
or.xml
palindrome after n
palindrome program
pdfsearch
program excel
Read/write excel file in Java
refresh page using selenium webdriver
refresh using selenium
run test multiple times
run test parallely
screenshot in selenium
scroll in webriver
scroll web element in selenium
search in pdf using itext
search in pdf using java
selendroid
selenium 2.0
Selenium Connectivity
Selenium Database
Selenium Flash testing.Flex Webdriver
selenium grid interview questions
selenium ide questions
selenium image comparison
selenium interview
selenium interview questions and answers
selenium mobile automation
selenium questions
Selenium SQL
Selenium Testing Using Flex Webdriver
Selenium web driver
Selenium web driver 2.0
selenium webdriver find broken images
set execution order
swap numbers java program
swap without using thord variable
testng interview
testng parameter passing
testng priority
transpose matrix
web driver
window pop-up
words and theiroccurence
xml file java program
YouTube Testing using Java
zip files in java
zip java files
zipreport in java
Blog Archive
2014 (39)
o January (39)
Java program to read xml file-Object-Repository.xm...
difference between single and double slash in Xpat...
Selenium Mobile Automation
exceptions in selenium and code to resolve it
How to ZIP files in Selenium
set the execution order for TestNg Testcases.
How to take the screen shots in seelnium 2.0
Difference between find element () and findelement...
What is actions class in web driver
How to switch between frames
What are the different assertions in SIDE
How to highlight an object with selenium and java
TestNG vs. Junit
How to work with radio button in web driver
Differences between QTP and selenium
How to pass parameters from testng.xml into test c...
How to get the name of browser using Web Driver
How to handle autocomplete box in web driver
How to find broken images in a page using Selenium...
How to refresh a page without using context click
hierarchy of TestNG annotations
How to run tests in multiple browser parallel
How to scroll web element
difference b/w implicitly Wait and Explicit wait
difference between thread. Sleep () and selenium. ...
Why we refer Firefox driver to the web driver inhe...
Best way to locate element in selenium
Difference between Selenium RC and Selenium Web dr...
Difference between Absolute path & Relative path
Selenium Hybrid Framework using Selenium WebDriver...
program to transpose a Matrix.
program to fetch unique elements from Array.
Java program to print Palindrome number after numb...
program to reverse a String in Java.
java program for factorial of a given number.
Write a java program for swapping of two numbers
java program to count number of unique words separ...
Read/write excel file in Java
Java program to print Fibonacci series.
2013 (8)
Popular Posts
100 Selenium Interview Questions
Difference between Selenium RC and Selenium Web driver
Difference between Absolute path & Relative path
Selenium Hybrid Framework using Selenium WebDriver, POI,testNg , Log4J
Best way to locate element in selenium
difference b/w implicitly Wait and Explicit wait
Basic Core Java Interview Questions
difference between thread. Sleep () and selenium. Set Speed ("2000")
How to scroll web element
Why we refer Firefox driver to the web driver inheritance
Picture Window template. Powered by Blogger.
