Sunday, May 1, 2016

Selenium - Useful Excel Methods

import java.io.File;
import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.HashMap;

import java.util.Iterator;

import java.util.Map;

import java.util.Map.Entry;


import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
 
import org.apache.poi.ss.usermodel.Cell;

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

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

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

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

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

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

/**
* Class : DataTable
* Description: Methods to deal with an excel file similar to QTP Datatable methods
* Developed By: Baba Fakruddin D
* Developed on: 01/05/2016
* */
 

/** Below class extends PublicVariables Class which has few public variables definitions - If you guys face errors after copy and paste this class, Please create those public variables in your class*/



class DataTable{
    public static String File = null;
    public String xlDataFileName;
    public String xlSheetName;
    public static Map tcRowData = new HashMap();
    private String path;
    public FileInputStream  inputStream = null;
    public FileOutputStream outputStream = null;
    public String hname, value;
    public int i, j;
    public File file;
    public XSSFWorkbook workBook;
    public XSSFSheet sheet;
   
    public DataTable(String path) throws IOException{
        this.path = path;
        this.file = new File(path);
        try{
            inputStream = new FileInputStream(path);
            workBook = new XSSFWorkbook(inputStream);
            sheet = workBook.getSheetAt(0);
            inputStream.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
   
    public DataTable(String path, String sheetName){
        this.path = path;
        this.file = new File(path);
        try{
            inputStream = new FileInputStream(path);
            workBook = new XSSFWorkbook(inputStream);
            sheet = workBook.getSheet(sheetName);
            inputStream.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
   
    /*public DataTable(File file, XSSFWorkbook workBook){
        this.file = file;
        this.workBook = workBook;
        System.out.println("For Creating object");
    }*/
   
    public DataTable(File file, XSSFWorkbook workBook, XSSFSheet sheet){
        this.file = file;
        this.workBook = workBook;
        this.sheet = sheet;
    }
   
    public int getRowCount(String sheetName){
        int index = workBook.getSheetIndex(sheetName);
        if(index==-1){
            return -1;
        }else{
            sheet = workBook.getSheetAt(index);
            int number = sheet.getLastRowNum()+1;
            return number;
        }
    }
   
    /**
     * Function: setFile
     * Description: Set the excel file based on input filePath
     * @author Baba
     * @category Generic
     * @param filePath
     * @return void
     * Tested: 22/05/2016 - working as expected
     * */
    public void setFile(String filePath) throws FileNotFoundException{
        inputStream = null;
        workBook = null;
        file = null;
        try {
            file = new File(filePath);
            inputStream = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            System.out.println ("File not found in the specified path.");
            e.printStackTrace();
        }
        try {
            workBook = new XSSFWorkbook(inputStream);
            inputStream.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
   
    /**
     * Function: createWorkBook
     * Description: Creates an excel work book in filepath with "Sheet1" sheet as sent as parameter
     * @author Baba
     * @param filePath
     * @return XSSFWorkbook
     * @category Generic
     * Tested: 22/05/2016 - working as expected
     * */
    public XSSFWorkbook createWorkBook(String filePath) throws IOException{
        inputStream = null;
        workBook = null;
        sheet = null;
        file = null;
        file = new File(filePath);
        if(file.exists()){
            inputStream = new FileInputStream(file);
        }else{
            workBook = new XSSFWorkbook();
            outputStream = new FileOutputStream(file);
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            inputStream = new FileInputStream(file);
        }
       
        workBook = new XSSFWorkbook(inputStream);
        sheet = workBook.createSheet();
//        DataTableMethods.createSheet("Sheet1");
//        sheet = workBook.createSheet("Sheet1");
       
        inputStream.close();
        outputStream = new FileOutputStream(file);
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();
       
        if(file.exists()){
            return workBook;
        }else{
            return null;
        }
    }
   
    /**
     * Function: createWorkBook
     * Description: Creates an excel workbook and in filepath and sheetName as sent as parameter
     * @author Baba
     * @param filePath, sheetName
     * @return XSSFWorkbook
     * @category Generic
     * Tested: 22/05/2016 - working as expected
     * */
    public XSSFWorkbook createWorkBook(String filePath, String sheetName) throws IOException{
        inputStream = null;
        workBook = null;
        sheet = null;
        file = new File(filePath);
        if(file.exists()){
            inputStream = new FileInputStream(file);
        }else{
            workBook = new XSSFWorkbook();
            outputStream = new FileOutputStream(file);
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            inputStream = new FileInputStream(file);
        }
       
        workBook = new XSSFWorkbook(inputStream);
       
        DataTable dts = new DataTable(file, workBook, sheet);
        dts.createSheet(sheetName);
       
//        DataTableMethods.createSheet(sheetName);
//        sheet = workBook.createSheet("Sheet1");
       
        inputStream.close();
        outputStream = new FileOutputStream(file);
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();
       
        if(file.exists()){
            return workBook;
        }else{
            return null;
        }
    }
   
    /**
     * Function: createSheet
     * Description: Creates excel sheet in workbook which set as global, use setfile to set one excel file
     * @author Baba
     * @param sheetName
     * @return Sheet
     * @category Generic
     * Tested: 22/05/2016 - working as expected
     * */
    public Sheet createSheet(String sheetName) throws IOException{
        int shtCnt = workBook.getNumberOfSheets();
        Boolean blnFlag;
        if(shtCnt>0){
            for (int i=0; i                blnFlag = true;
                for (int j=0;j                    if (workBook.getSheetName(j).equalsIgnoreCase(sheetName)){
                        blnFlag = false;
                        break;
                    }
                }
                if (blnFlag){
                    sheet = workBook.createSheet(sheetName);
                    break;
                }else{
                    break;
                }
            }
        }else if(shtCnt==0){
            sheet = workBook.createSheet(sheetName);
        }
       
        sheet = workBook.getSheet(sheetName);

        Row row = sheet.createRow(0);  
        row.createCell(0);
       
        try {
            if (inputStream!=null){
                inputStream.close();
            }
            outputStream = new FileOutputStream(file);
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
       
        return sheet;
    }
   
    /**
     * Function: saveAs
     * Description: Save an excel workBook as excel file in the path sent as parameter
     * @author Baba
     * @param sheetName
     * @return Sheet
     * @throws IOException
     * @category Generic
     * Tested: 22/05/2016 - working as expected
     * */
    public void saveAs(String filePath) throws IOException{
        outputStream = new FileOutputStream(new File(filePath));
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
   
    public void closeWorkBook(){
        try {
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
   
    public Sheet setSheet(String sheetName){
        sheet = workBook.getSheet(sheetName);
        return sheet;
    }
   
    public String getSheetName(){
        return sheet.getSheetName();
    }
   
    public void setFileAndSheet(String filePath, String sheetName) throws IOException{
        /*inputStream = null;
        workBook = null;
        sheet = null;
        DataTable dts = new DataTable(filePath);
        dts.setFile(filePath);
        dts.setSheet(sheetName);*/
        try{
            inputStream = new FileInputStream(filePath);
            workBook = new XSSFWorkbook(inputStream);
            sheet = workBook.getSheet(sheetName);
            File file = new File(filePath);
            DataTable dts = new DataTable(file, workBook, sheet);
            inputStream.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
   
    public String value(int row, int column){
        row = row-1;
        column = column-1;
        Cell cellValue = sheet.getRow(row).getCell(column);
        if(cellValue!=null){
            return cellValue.toString();
        }else{
            System.out.println("No Value contains in Row/Column : "+ row +"/"+column);
            return null;
        }
    }
   
    public int getRowNumber(String cellValue, int column){
        int i=0;
        Iterator rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
//            System.out.println(row.toString());
           
            Cell cell = row.getCell(column-1);
            if (cell!=null){
                if(cell.toString().equalsIgnoreCase(cellValue)){
                    return i+1;
                }
            }
           
            i = i+1;
        }
        return -1;
    }
   
    public Row getRow(String cellValue, int column){
        Iterator rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Cell cell = row.getCell(column-1);
            if(cell!=null){
                if(cell.toString().equalsIgnoreCase(cellValue)){
                    return row;
                }
            }
        }
        return null;
    }
   
    public Row getRow(int row){
        int lastRowNo = sheet.getLastRowNum();
        if (row<=lastRowNo){
            return sheet.getRow(row-1);
        }else{
            return null;
        }
    }
   
    public Row getRow(String rowCellText){
        DataTable dts = new DataTable(file, workBook, sheet);
        int rowNum = dts.getRowNumber(rowCellText);
        return dts.getRow(rowNum);
    }
   
    public int getRowNumber(String tCID){
        int i=0;
        Iterator rowIterator = sheet.iterator();
        while(rowIterator.hasNext()){
            Row row = rowIterator.next();
            Cell cell = row.getCell(0);
            if(cell.toString().equalsIgnoreCase(tCID)){
                return i+1;
            }
            i = i+1;
        }
        return -1;
    }
   
    public int getColumnNumber(String cellValue, int rowNumber){
        int i=0;
        rowNumber = rowNumber-1;
        Iterator colIterator = sheet.getRow(rowNumber).cellIterator();
        while(colIterator.hasNext()){
            Cell cell = colIterator.next();
            if (cell.getStringCellValue().toString().equalsIgnoreCase(cellValue)){
                return i+1;
            }
            i = i+1;
        }
        return -1;
    }
   
    public Cell getColumn(String cellValue, int rowNumber){
        int i=0;
        rowNumber = rowNumber-1;
        Iterator colIterator = sheet.getRow(rowNumber).cellIterator();
        while(colIterator.hasNext()){
            Cell cell = colIterator.next();
            if (cell.getStringCellValue().toString().equalsIgnoreCase(cellValue)){
                return cell;
            }
            i = i+1;
        }
        return null;
    }
   
    public int getRowCount(){
        return sheet.getLastRowNum()+1;
    }
   
    public int getColumnCount(int row, String sheetName){
        DataTable dts = new DataTable(file, workBook, sheet);
        XSSFSheet sheet = (XSSFSheet) dts.setSheet(sheetName);
        return sheet.getRow(row-1).getLastCellNum();
    }
   
    public int getColumnCount(String sheetName){
        DataTable dts = new DataTable(file, workBook, sheet);
        XSSFSheet sheet = (XSSFSheet) dts.setSheet(sheetName);
        return sheet.getRow(0).getLastCellNum();
    }
   
    public int getColumnCount(int row){
//        DataTable dts = new DataTable(file, workBook, sheet);
        return sheet.getRow(row-1).getLastCellNum();
    }
   
    public int getColumnCount(){
//        DataTable dts = new DataTable(file, workBook, sheet);
        return sheet.getRow(0).getLastCellNum();
    }
   
    public int getSheetCount(){
        return workBook.getNumberOfSheets();
    }
   
    public int getSheetNumber(String sheetName){
        int i, shtCnt;
        shtCnt = getSheetCount();
        for (i=1; i<=shtCnt; i++){
            if(workBook.getSheetAt(i-1).getSheetName().toString().equalsIgnoreCase(sheetName)){
                return i;
            }
        }
        return -1;
    }
   
    public int getColumnNumber(String columnName){
        int i = 0;
        Row row = sheet.getRow(0);
        Iterator colIterator = row.cellIterator();
        while (colIterator.hasNext()){
            Cell cell = colIterator.next();
            if(cell.getStringCellValue().toString().equalsIgnoreCase(columnName)){
                return i+1;
            }
            i = i+1;
        }
        return -1;
    }
   
    public Row addNewRow(int rowNo) throws IOException{
        boolean op = false;
        Row row = sheet.createRow(rowNo-1);
        Cell cell = row.createCell(0);
        cell.setCellValue("New Row");
        if (row!=null){
            op = true;
        };
       
        /*if (inputStream!=null){
            inputStream.close();
        }
        outputStream = new FileOutputStream(file);
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();*/
        if (op){
            return row;
        }else{
            return null;
        }
       
    }
   
    public XSSFCell addNewColumn() throws IOException{
        boolean op=false;
        DataTable dts = new DataTable(file, workBook, sheet);
        int cc = dts.getColumnCount();
        XSSFCell cell = null;
        if (cc!=-1){
            cell = sheet.getRow(0).createCell(cc);
        }
        if(cell!=null){
            op = true;
        }
        if (inputStream!=null){
            inputStream.close();
        }
        outputStream = new FileOutputStream(file);
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();
       
        if(op){
            return cell;
        }else{
            return null;
        }
    }
   
    public XSSFCell addNewColumn(int row) throws IOException{
        boolean op = false;
        DataTable dts = new DataTable(file, workBook, sheet);
        int cc = dts.getColumnCount();
        XSSFCell cell = null;
        if(cc==-1){
            cell = sheet.getRow(row).createCell(cc+1);
        }else{
            cell = sheet.getRow(row).createCell(cc);
        }
        if(cell!=null){
            op = true;
        }
        if (inputStream!=null){
            inputStream.close();
        }
        outputStream = new FileOutputStream(file);
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();
        if(op){
            return cell;
        }else{
            return null;
        }
    }
   
    public void setCellData(int row, int column, String cellValue) throws IOException{
//        inputStream = new FileInputStream(file)
        row = row-1;
        column = column-1;
        DataTable dts = new DataTable(file, workBook, sheet);
        Row actRow = sheet.getRow(row);
        if(actRow == null){
            System.out.println("Added New Row in the Sheet and Set Cell Value");
            actRow = dts.addNewRow(row+1);
        }
       
        Cell actCell = actRow.getCell(column);
//        if(actCell==null){
//            actCell = DataTableMethods.addNewColumn(row);
//        }
//       
        if(actCell==null){
            try{
                actCell = actRow.createCell(column);
            }catch(Exception e){
                e.printStackTrace();
                System.out.println(column + "column doesn't exist. exiting method");
                return;
            }
        }
       
        actCell.setCellType(actCell.CELL_TYPE_STRING);
        actCell.setCellValue(cellValue);
       
        if (inputStream!=null){
            inputStream.close();
        }
        outputStream = new FileOutputStream(file);
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
   

    public boolean setCellData(int row, String column, String cellValue) throws IOException{
        row = row-1;
        DataTable dts = new DataTable(file, workBook, sheet);
        int colNum = dts.getColumnNumber(column);
        if (colNum!=-1){
            colNum = colNum-1;
        }else{
            System.out.println("Column : "+ column + " doesn't exist");
            return false;
        }
       
       
        Row actRow = sheet.getRow(row);
        if(actRow==null){
            System.out.println("Added New Row in the Sheet and Set Cell Value");
            actRow = dts.addNewRow(row);
        }
       
        Cell actCell = actRow.getCell(colNum);
//        if(actCell==null){
//            actCell = actRow.createCell(colNum);
//        }
       
        if(actCell==null){
            try{
                actCell=actRow.createCell(colNum);
            }catch(Exception e){
                e.printStackTrace();
                System.out.println(column + " column doesn't exist");
                return false;
            }
        }
       
        actCell.setCellType(Cell.CELL_TYPE_STRING);
        actCell.setCellValue(cellValue);
       
        if (inputStream!=null){
            inputStream.close();
        }
        outputStream = new FileOutputStream(file);
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();
        return true;
    }
   
    public void setCellData(String rowCellText, int column, int trgtColumn, String cellValue) throws IOException{
        DataTable dts = new DataTable(file, workBook, sheet);
        Row actRow = dts.getRow(rowCellText, column);
       
        if(actRow==null){
            System.out.println("No such Cell value exists : "+rowCellText + " in column : "+ column);
            return;
        }
       
        Cell actCell = actRow.getCell(column-1);
        if(actCell==null){
            System.out.println("No such Cell value exists : "+rowCellText + " in column : "+ column);
            return;
//            actCell = actRow.createCell(trgtColumn-1);
        }
       
        Cell trgtCell = actRow.getCell(trgtColumn-1);
        if(trgtCell==null){
            try{
                trgtCell = actRow.createCell(trgtColumn-1);
            }catch(Exception e){
                e.printStackTrace();
                System.out.println("column doesn't exist");
                return;
            }
           
//            trgtCell.setCellType(Cell.CELL_TYPE_STRING);
            trgtCell.setCellValue(cellValue);
           
        }else{
            trgtCell.setCellValue(cellValue);
        }
       
        if (inputStream!=null){
            inputStream.close();
        }
        outputStream = new FileOutputStream(file);
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();
//        return true;
    }
      
    public void setRowData(int row, Map rowData) throws IOException{
        DataTable dts = new DataTable(file, workBook, sheet);
        Row actRow = dts.getRow(row);
        Cell cell = null;
        Iterator entries = rowData.entrySet().iterator();
        while(entries.hasNext()){
            Entry thisEntry = (Entry)entries.next();
            Object key = thisEntry.getKey();
            Object value = thisEntry.getValue();
            int colNum = dts.getColumnNumber(key.toString());
            if(colNum!=-1){
                cell = actRow.getCell(colNum);
                if(cell==null){
                    cell = actRow.createCell(colNum);
                    cell.setCellValue(value.toString());
                }else{
                    cell.setCellValue(value.toString());
                }
//                actRow.getCell(colNum).setCellValue(value.toString());
//                actRow.getCell(colNum).setCellValue("Hello");
            }else{
                System.out.println("No such column exists in table");
            }
        }
        if (inputStream!=null){
            inputStream.close();
        }
        outputStream = new FileOutputStream(file);
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
   
    public void setRowData(String rowCellText, Map rowData) throws IOException{
        DataTable dts = new DataTable(file, workBook, sheet);
//        inputStream = new FileInputStream(file);
//        int rowNo = DataTableMethods.getRowNumber(rowCellText);
        Row actRow = dts.getRow(rowCellText);
       
        Cell cell = null;
        /*cell = actRow.getCell(0);
       
        cell = actRow.getCell(4);
        if(cell==null){
            cell = actRow.createCell(4);
            cell.setCellValue("India");
        }
        cell = actRow.getCell(5);*/
       
        Iterator> entries = rowData.entrySet().iterator();
        while(entries.hasNext()){
            Entry thisEntry = (Entry) entries.next();
            Object key = thisEntry.getKey();
            Object value = thisEntry.getValue();
            int colNum = dts.getColumnNumber(key.toString());
            if(colNum!=-1){
                dts.setCellData(rowCellText, 1, colNum, value.toString());
                cell = actRow.getCell(colNum);
                /*if(cell==null){
                    cell = actRow.createCell(colNum);
                    cell.setCellValue(value.toString());
                }else{
                    cell.setCellValue(value.toString());
                }*/
//                actRow.getCell(colNum).setCellValue(value.toString());
                cell=null;
            }else{
                System.out.println("No Column exists with name : "+ key.toString());
            }
        }
        if (inputStream!=null){
            inputStream.close();
        }
        outputStream = new FileOutputStream(file);
        workBook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
}


A good threat is worth a thousand tests" - Boris Beizer

No comments:

There was an error in this gadget