Using this excel library we can perform following manipulations on excel sheet.
Created Sample Test Class to call the methods from above excel library and perform the tasks.
Please refer below YouTube video for more details:
- How to get total row count - int getRowCount(String sheetName)
- How to read the value - String getCellData(String sheetName,String colName,int rowNum), String getCellData(String sheetName,int colNum,int rowNum).
- How to set the value - boolean setCellData(String sheetName,String colName,int rowNum, String data)
- How to add a New work sheet - boolean addSheet(String sheetname)
- How to remove a work sheet - boolean removeSheet(String sheetName)
- How to add a column - boolean addColumn(String sheetName,String colName)
- How to remove a column - boolean removeColumn(String sheetName, int colNum)
- How to check if a sheet is exist or not - boolean isSheetExist(String sheetName)
- How to get total column count - int getColumnCount(String sheetName)
- How to get cell row number - int getCellRowNum(String sheetName,String colName,String cellValue)
package com.utility;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Calendar;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class NewExcelLibrary {
public static String path = System.getProperty("user.dir") + "/TestData/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 NewExcelLibrary() {
// 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();
}
}
public NewExcelLibrary(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;
}
}
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().name().equals("STRING"))
return cell.getStringCellValue();
else if(cell.getCellType().name().equals("NUMERIC") || cell.getCellType().name().equals("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().name().equals("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().name().equals("STRING"))
return cell.getStringCellValue();
else if(cell.getCellType().name().equals("NUMERIC") || cell.getCellType().name().equals("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().name().equals("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 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();
}
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;
}
}
Created Sample Test Class to call the methods from above excel library and perform the tasks.
import org.testng.annotations.Test;
import com.utility.NewExcelLibrary;
public class ExcelTest {
NewExcelLibrary obj= new NewExcelLibrary("D:\\Workspace_Eclipse\\ReadExcel\\TestData\\TestData.xlsx");
@Test(priority = 1)
public void testCase1() {
int totalRows=obj.getRowCount("Employee");
System.out.println("Total rows: "+totalRows);
}
@Test(priority = 2)
public void testCase2() {
String salary=obj.getCellData("Employee", "Salary", 4);
System.out.println("salary is: "+salary);
}
@Test(priority = 3)
public void testCase3() {
String rating=obj.getCellData("Employee", 4, 2);
System.out.println("rating is: "+rating);
int a=1;
Double sum= Double.valueOf(rating)+a;
System.out.println("The rating is now: "+sum);
}
@Test(priority = 4)
public void testCase4() {
obj.setCellData("Employee", "ID", 2, "105");
}
}
Please refer below YouTube video for more details: