Complete Excel Library

Using this excel library we can perform following manipulations on excel sheet.

• 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)

Complete Excel Library:

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: