How to compare Two Excel Files - Java Code

package com.seth.excel;

import java.io.File;

import java.io.FileInputStream;

import java.util.Scanner;

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

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

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

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

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

public class CompareExcel {

    public static void main(String[] args) {

        try {

            // get input excel files

       

        Scanner reader = new Scanner(System.in);

        System.out.println("Enter File 1");

        String fileA = reader.nextLine();

        System.out.println("Enter File 2");

        String fileB = reader.nextLine();

        System.out.println("Enter WorkSheet Number to Compare (Starts from 0)");

        String SheetNumber = reader.nextLine();

        FileInputStream excellFile1 = new FileInputStream(new File(

                    fileA));

            FileInputStream excellFile2 = new FileInputStream(new File(

                    fileB));

            // Create Workbook instance holding reference to .xlsx file

            XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile1);

            XSSFWorkbook workbook2 = new XSSFWorkbook(excellFile2);

            // Get first/desired sheet from the workbook

            XSSFSheet sheet1 = workbook1.getSheetAt(Integer.parseInt(SheetNumber));

            XSSFSheet sheet2 = workbook2.getSheetAt(Integer.parseInt(SheetNumber));

            // Compare sheets

            if(compareTwoSheets(sheet1, sheet2)) {

                System.out.println("\n\nThe two excel sheets are Equal");

            } else {

                System.out.println("\n\nThe two excel sheets are Not Equal");

            }

            

            //close files

            excellFile1.close();

            excellFile2.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    

    // Compare Two Sheets

    public static boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) {

        int firstRow1 = sheet1.getFirstRowNum();

        int lastRow1 = sheet1.getLastRowNum();

        boolean equalSheets = true;

        for(int i=firstRow1; i <= lastRow1; i++) {

            

            System.out.println("\n\nComparing Row "+i);

            

            XSSFRow row1 = sheet1.getRow(i);

            XSSFRow row2 = sheet2.getRow(i);

            if(!compareTwoRows(row1, row2)) {

                equalSheets = false;

                System.out.println("Row "+i+" - Not Equal");

                //break;

            } else {

               // System.out.println("Row "+i+" - Equal");

            }

        }

        return equalSheets;

    }

    // Compare Two Rows

    public static boolean compareTwoRows(XSSFRow row1, XSSFRow row2) {

        if((row1 == null) && (row2 == null)) {

            return true;

        } else if((row1 == null) || (row2 == null)) {

            return false;

        }

        

        int firstCell1 = row1.getFirstCellNum();

        int lastCell1 = row1.getLastCellNum();

        boolean equalRows = true;

        

        // Compare all cells in a row

        for(int i=firstCell1; i <= lastCell1; i++) {

            XSSFCell cell1 = row1.getCell(i);

            XSSFCell cell2 = row2.getCell(i);

            if(!compareTwoCells(cell1, cell2)) {

                equalRows = false;

                System.err.println("       Cell "+i+" - Not Equal" +"; Value of Cell 1 is \"" +cell1 + "\" - Value of Cell 2 is \"" +cell2 + "\"");

                //break;

            } else {

                //System.out.println("       Cell "+i+" - Equal");

            }

        }

        return equalRows;

    }

    // Compare Two Cells

    public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) {

        if((cell1 == null) && (cell2 == null)) {

            return true;

        } else if((cell1 == null) || (cell2 == null)) {

            return false;

        }

        

        boolean equalCells = false;

        int type1 = cell1.getCellType();

        int type2 = cell2.getCellType();

        if (type1 == type2) {

            if (cell1.getCellStyle().equals(cell2.getCellStyle())) {

                // Compare cells based on its type

                switch (cell1.getCellType()) {

                case HSSFCell.CELL_TYPE_FORMULA:

                    if (cell1.getCellFormula().equals(cell2.getCellFormula())) {

                        equalCells = true;

                    }

                    break;

                case HSSFCell.CELL_TYPE_NUMERIC:

                    if (cell1.getNumericCellValue() == cell2

                            .getNumericCellValue()) {

                        equalCells = true;

                    }

                    break;

                case HSSFCell.CELL_TYPE_STRING:

                    if (cell1.getStringCellValue().equals(cell2

                            .getStringCellValue())) {

                        equalCells = true;

                    }

                    break;

                case HSSFCell.CELL_TYPE_BLANK:

                    if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {

                        equalCells = true;

                    }

                    break;

                case HSSFCell.CELL_TYPE_BOOLEAN:

                    if (cell1.getBooleanCellValue() == cell2

                            .getBooleanCellValue()) {

                        equalCells = true;

                    }

                    break;

                case HSSFCell.CELL_TYPE_ERROR:

                    if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {

                        equalCells = true;

                    }

                    break;

                default:

                    if (cell1.getStringCellValue().equals(

                            cell2.getStringCellValue())) {

                        equalCells = true;

                    }

                    break;

                }

            } else {

                return false;

            }

        } else {

            return false;

        }

        return equalCells;

    }

}

Create JPEG Image Graph from CSV File

import java.io.*;
import java.util.Scanner;
import java.util.StringTokenizer;

import org.jfree.chart.JFreeChart;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.category.DefaultCategoryDataset;

import com.opencsv.CSVReader;

public class LineChart
{


   public static void main( String[ ] args ) throws Exception
   {
  DefaultCategoryDataset line_chart_dataset = new DefaultCategoryDataset();
  String strFile = "F:\\Java\\Sample.csv";
  int  ColCount=getCols("F:\\Java\\Sample.csv");
 
  FileReader fr;
       fr = new FileReader("F:\\Java\\Sample.csv");
     
     
     
       String[] Headers = getHeaders("F:\\Java\\Sample.csv");
       System.out.println(Headers[0]);
 //Creation of DataSet Starts Here
       for (int k =0; k<ColCount;k++){
   

     CSVReader reader = new CSVReader(new FileReader(strFile));
   
     String [] nextLine;
     int lineNumber = 0;
     nextLine = reader.readNext();
   
   
 

     while ((nextLine = reader.readNext()) != null) {
       lineNumber++;
     
       line_chart_dataset.addValue( Double.parseDouble((nextLine[k])) , Headers[k] , "X" +lineNumber );
   

     }//End of While Loop
   
  }//End of For Loop - k
     
     
      // Chart Creation Starts here
      JFreeChart lineChartObject = ChartFactory.createLineChart(
         "Ececution Stats","Execution Number",
         "Value",
         line_chart_dataset,PlotOrientation.VERTICAL,
         true,true,false);

      int width = 1280; /* Width of the image */
      int height = 480; /* Height of the image */
      File lineChart = new File( "F:\\Java\\LineChart.jpeg" );
      ChartUtilities.saveChartAsJPEG(lineChart ,lineChartObject, width ,height);
   }
 
 
 
   public static int getCols(String xFileLocation) throws FileNotFoundException{

String InputLine = "";
int Rows = 0;
int Cols=0;
String[] InArray = null;

//String xFileLocation = "F:\\Java\\Sample.csv";
Scanner scanIn = new Scanner (new BufferedReader(new FileReader(xFileLocation)));
scanIn.useDelimiter(",");

while(scanIn.hasNextLine()){
InputLine = scanIn.nextLine();
InArray = InputLine.split(",");
Rows++;
Cols = InArray.length;

}

return Cols;
//System.out.println("Count of Rows is : " +Rows +" Count of Cols is "  + Cols);

}

public static int getRows(String xFileLocation) throws FileNotFoundException{

String InputLine = "";
int Rows = 0;
int Cols=0;
String[] InArray = null;

//String xFileLocation = "F:\\Java\\Sample.csv";
Scanner scanIn = new Scanner (new BufferedReader(new FileReader(xFileLocation)));
scanIn.useDelimiter(",");

while(scanIn.hasNextLine()){
InputLine = scanIn.nextLine();
InArray = InputLine.split(",");
Rows++;
Cols = InArray.length;

}

return Rows;
//System.out.println("Count of Rows is : " +Rows +" Count of Cols is "  + Cols);

}

public static String[] getHeaders(String xFileLocation) throws IOException{

FileReader fr;
    fr = new FileReader("F:\\Java\\Sample.csv");
   
   
    BufferedReader br = new BufferedReader(fr);
    String line = br.readLine();
    StringTokenizer st = new StringTokenizer(line, ",");
    String[] Headers = new String[getCols("F:\\Java\\Sample.csv")];
    int i = 0;
    final DefaultCategoryDataset dataset = new DefaultCategoryDataset( );
    //String xLabel = st.nextToken();
   // System.out.println(xLabel);
    while(st.hasMoreTokens()){
    Headers[i] = st.nextToken();
 
   // System.out.println(Headers[i]);
    i=i+1;
    }

return Headers;


}

 
}

Triggers in Oracle 11g -> Execution Order in Oracle 11g

Before Oracle 11g, more than trigger can be created on one table but Oracle doesn't guarantee the order of execution of trigger.  Oracle has introduced the feature in Oracle 11g to execute the trigger in any order as per requirements.

Let’s look the example:

Virtual Column in Oracle

Oracle has introduced new feature in version 11 ie. Virtual column. Virtual column values are not stored on disk. They are generated at runtime using their associated expression.

Lets have a look how to create a table with Virtual Column 

CREATE TABLE testvirtual
(
   id1   NUMBER (10)
 , id2   NUMBER (10)
 , id3   NUMBER (10) GENERATED ALWAYS AS (id1 + id2) VIRTUAL
);
Virtual keyword is optional.

Table Compression in Oracle 11g

Table compression was first introduced in Oracle 9i as a space saver feature only for data warehousing projects. In oracle 11g , this is acceptable as mainstream feature which is acceptable for OLTP Databases. This will result in saving storage space as this will compress the table, increased I/O performance and reduced memory use in the buffer cache. Definitely, this incurs CPU overhead because of compression.

Compression can be done at the tablespace, table or partition level . Options/Features which are available with Compression :

Regular Expression In Oracle

REGULAR EXPRESSION:

            A regular expression is a string or set of characters that describes the structure of some text .This expression helps to search a particular pattern on the line. It is composed of Literals and Metacharacters.

Drop columns from table in fastest way

Columns in the oracle table can have more than 1000 rows of data. If the user asked to drop such columns from the table, dropping the same will take significant amount of time.

By using “Set Unused Column”, it is possible to reduce the significant amount of time while dropping the column from the table.

The syntax used to drop the column from the table is:

Alter table Table Name drop column Column Name;

ORA-32332: Resolution

ORA-32332: Resolution

ERROR at line 1:

ORA-32332: cannot refresh materialized view "Materialized View Name"

as type evolution has occured

Resolution:

1)      Check if the materialized view is valid .

SELECT * from dba_objects where object_name =<view_name>

2)      Check if underneath compoennts are valid

3)      If Underneath all tables are there, check if any field has been added to the same.

If yes, get a complete refresh for materialized view.

In my case, one of the underneath view was invalid.

How to Use SYS_CONNECT_BY_PATH in Oracle

SYS_CONNECT_BY_PATH :

Syntax:

SYS_CONNECT_BY_PATH is a function which is used in hierarchical queries to get the path for the current node starting from Parent node.

SELECT SYS_CONNECT_BY_PATH ( column , char)

FROM table_name

START WITH <root_node>

CONNECt BY NOCYCLE PRIOR < child_node_col> = <Parent_node>

How to replace Special Characters in Oracle

How to replace Special Characters in Oracle

Use regexp_replace (column_name,'[]~!@#$%^&*|?]', NULL ) to replace Special character .

New Blog for Selenium Automation

Hello

Please refer to below blog for any assistance required in Selenium Automation. Infact, you can Request the topic you want me to write about and i would be more than happy to add a blog on the same.

http://automationtesters007.blogspot.com.au/

Happy Learning
The Software Professionals.

How to convert single column to multiple rows in Oracle

Example 1 ->  Take the below Example :

CREATE TABLE config_value

(

   Config_id    VARCHAR2(40)

 , config_code    VARCHAR2(100)

);

How to use 'Case' statement in Oracle

Case Statement

Syntax:

CASE [ expression ]

   WHEN condition_1 THEN result_1

   WHEN condition_2 THEN result_2

   ...

   WHEN condition_n THEN result_n

   ELSE result

END

Let’s take few scenarios to Implement Case Statement:

Sample Java Codes - Part 6

Code 34: Composition

Code a:


public class Test{
public static void main(String args[]){
Class3 Class3Object = new Class3(4,5,6);

}
}

Sample Java Codes - Part 5

Code 29: Time Class

Code a:


public class Test{
public static void main(String args[]){
Class2 Class2Object = new Class2();
System.out.println(Class2Object.toMilitary());
Class2Object.getTime(3, 59, 9);
System.out.println(Class2Object.toMilitary());

}
}

Sample Java Codes - Part 4

Code 13: Conditional Parameters

import java.util.Scanner;
public class Test{
public static void main(String args[]){
Scanner iage = new Scanner(System.in);
System.out.println("Hey ! Lets decide if you ae over or under a certain age");
int age = iage.nextInt();
System.out.println(age>50?"You ae over 50": "You ae under 50");
}
}

Test Planning Flow

Find below the flow diagram for Test Planning

Test Planning Flow

Defect management Flow

Defect management is essential part of Software Test Life Cycle (STLC), and it is essential to follow the Defect Management Flow. Here is the flow diagram for the same.

Defect Management Flow

Sample Java Codes - Part 3

Code 9: Many Methods & Instances

Code a:

import java.util.Scanner;
public class Test{
public static void main(String args[]){
Class2 Class2obj = new Class2();
Scanner input = new Scanner(System.in);
System.out.println("Enter your name");
String name = input.nextLine();
Class2obj.setName(name);
Class2obj.message();
}
}

Sample Java Codes- Part 2

Code 5: Switch case examples

import java.util.Scanner;

public class Test{
public static void main (String args[]){
System.out.println("Program to depict usage of switch case");
Scanner iage = new Scanner(System.in);
System.out.println("Enter age now:");
int age = iage.nextInt();
switch(age) {
case 1:
System.out.println("Kid can crawl");
break;
case 2:
System.out.println("Kid can walk");
break;
case 3:
System.out.println("Kid can talk");
break;
default :
System.out.println("Kid is a trouble! Send him School");
break;
}

}
}