Skip to main content

Excel operations in JAVA

 import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.Calendar;


import org.apache.poi.common.usermodel.HyperlinkType;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.CellType;

import org.apache.poi.ss.usermodel.FillPatternType;

import org.apache.poi.ss.usermodel.IndexedColors;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFCreationHelper;

import org.apache.poi.xssf.usermodel.XSSFFont;

import org.apache.poi.xssf.usermodel.XSSFHyperlink;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;



public class ExcelReader {

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 ExcelReader(String path) {

this.path=path;

try {

fis = new FileInputStream(path);

workbook = new XSSFWorkbook(fis);

sheet = workbook.getSheetAt(0);

fis.close();

} catch (Exception e) {

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 "";

if(cell.getCellType()==CellType.STRING)

  return cell.getStringCellValue();

else if(cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA ){

  

  String cellText  = String.valueOf(cell.getNumericCellValue());

  if (HSSFDateUtil.isCellDateFormatted(cell)) {

           

  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;

           

          


         }


  

  

  return cellText;

  }else if(cell.getCellType()==CellType.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()==CellType.STRING)

  return cell.getStringCellValue();

  else if(cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.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;

           

         


         }


  

  

  return cellText;

  }else if(cell.getCellType()==CellType.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.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){

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++){

if(row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(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.setCellValue(data);

    XSSFCreationHelper createHelper = workbook.getCreationHelper();


    //cell style for hyperlinks

    

    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(HyperlinkType.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){

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.HSSFColorPredefined.GREY_40_PERCENT.getIndex());

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

sheet=workbook.getSheetAt(index);

row = sheet.getRow(0);

if (row == null)

row = sheet.createRow(0);

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.HSSFColorPredefined.GREY_40_PERCENT.getIndex());

XSSFCreationHelper createHelper = workbook.getCreationHelper();

style.setFillPattern(FillPatternType.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){

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

   

    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{

ExcelReader datatable = null;


datatable = new ExcelReader("C:\\CM3.0\\app\\test\\Framework\\AutomationBvt\\src\\config\\xlfiles\\Controller.xlsx");

for(int col=0 ;col< datatable.getColumnCount("TC5"); col++){

System.out.println(datatable.getCellData("TC5", col, 1));

}

}

}

Labels:
Location: Pune, Maharashtra, India

Comments

Post a Comment

Popular posts from this blog

Add, remove, search an item in listview in C#

Below is the C# code which will help you to add, remove and search operations on listview control in C#. Below is the design view of the project: Below is the source code of the project: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace Treeview_control_demo {     public partial class Form2 : Form     {         public Form2()         {             InitializeComponent();             listView1.View = View.Details;                   }         private void button1_Click(object sender, EventArgs e)         {             if (textBox1.Text.Trim().Length == 0)...
Post a Comment
Read more

Add worklog in Jira using Python

 Below is the Python code to add the worklog in Jira. You need to install a request library for this. Here is the code: import requests from requests.auth import HTTPBasicAuth import json url = "https://your jira address here/rest/api/2/issue/ticket_number/worklog" auth = HTTPBasicAuth("username", "jira access token") headers = {     "Accept": "application/json",     "Content-Type": "application/json" } payload = json.dumps({     "comment": {         "content": [             {                 "content": [                     {                         "text": "This is for QA Testing",                         "type": "text"                     } ...
Post a Comment
Read more

Some GUI examples in Python using customtkinter

 Some GUI examples in Python using customtkinter import customtkinter import os from PIL import Image class ScrollableCheckBoxFrame(customtkinter.CTkScrollableFrame):     def __init__(self, master, item_list, command=None, **kwargs):         super().__init__(master, **kwargs)         self.command = command         self.checkbox_list = []         for i, item in enumerate(item_list):             self.add_item(item)     def add_item(self, item):         checkbox = customtkinter.CTkCheckBox(self, text=item)         if self.command is not None:             checkbox.configure(command=self.command)         checkbox.grid(row=len(self.checkbox_list), column=0, pady=(0, 10))         self.checkbox_list.append(checkbox)     def remove_item(self, it...
Post a Comment
Read more