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

display files and directories in Listview

Below is the C# code which displays all files and directories in listview control with their file size and creation date. If it is file then it also displays the extension of the file e.g. .txt, .jpg etc Below is the design view of the project: Listview to display files and directories with size and date created 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; using System.IO; namespace search_in_listview {     public partial class Form1 : Form     {         public Form1()         {             InitializeComponent();                   }         private void button1_Click(object sender, EventArgs ...
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