Sagar Chaudhari
Reputation: 21
How compare 2 different excel.xls sheets using apache POI and java
Problem statement: I want to fetch data from two different websites , and write this data into workbook Sheet 1 and sheet 2 am looking for solution to perform excel comparison, looking for expert guidance .
=======================================================
Excel sheet data
===================================
solution I need mismatched from these two sheets using Apache POI and java.
================================================== Below is the code to read sheet 0 , i stuck with hoe to read second sheet1 and compare it.
import java.io.FileInputStream;
import jxl.Sheet; import jxl.Workbook;
public class MyCCD {
public static void oldTurnover() throws Exception{
//int turnoverRow = 0;
String TickerName = null;
int numOfTicker = 8;
String SummaryColumn = null;
float oldRevenue = 0;
String description = "Ticker";
Workbook wb = Workbook.getWorkbook(new FileInputStream("D:\\ssb.xls"));
Sheet sh = wb.getSheet(0);
int rows = sh.getRows();
int cols = sh.getColumns();
//to get the row of Ticker
for(int i=0;i<rows;i++){
//System.out.println(sh.getCell(0, i).getContents().toLowerCase());
if(sh.getCell(0, i).getContents().toLowerCase().matches("ticker")){
System.out.println("Ticker row:"+i);
//turnoverRow = i;
//Company Name
for(int j=i;j<i+numOfTicker;j++){
TickerName = sh.getCell(0, j+1).getContents();
System.out.println("-----------"+TickerName+"-------------");
for(int k=1;k<cols;k++){
//quarter
SummaryColumn = sh.getCell(k, i+1).getContents();
System.out.println("SummaryColumn: "+SummaryColumn);
//Estimated Revenue
oldRevenue = Float.parseFloat(sh.getCell(k, j+1).getContents());
float newRevenue = IHData(description, SummaryColumn);
if(oldRevenue != newRevenue){
System.out.println("SummaryColumn:"+SummaryColumn);
System.out.print ("SheetOne:"+oldRevenue);
System.out.print ("\t\t");
System.out.println("SheetTwo: "+newRevenue);
}
}
}
}
}
}
public static float IHData(String description, String SummaryColumn) throws Exception
{
Upvotes: 2
Views: 34670
Answers (3)
ruturaj tambe
Reputation: 1
This code compares two excel sheets and generates the third one. If the values are same it will retain the value from first excel sheet, but if the values are different it will show you two values in one block with the name of their respective excel sheet names. Hope this helps you.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Scanner;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;
import java.lang.Exception;
import java.lang.reflect.Array;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class CompareExcelSheet {
static ArrayList<Object> Excel1 = new ArrayList<Object>();
static ArrayList<Object> Excel2 = new ArrayList<Object>();
static ArrayList<String> Excel3 = new ArrayList<String>();
//String ExcelPath1="C:\\\\Users\\\\ruturaj.tambe\\\\Desktop\\\\Excel1.xlsx";
//String ExcelPath2="C:\\\\Users\\\\ruturaj.tambe\\\\Desktop\\\\Excel2.xlsx";
static int numberOfCells =0;
static int numberOfRows =0;
int numberOfRows2 =0;
int numberOfCells2 =0;
static int total=0;
static int error=0;
static int errorpercent=0;
static String ResultString1 = null;
static String ResultString2 = null;
static String Filename1 = null;
static String Filename2 = null;
static String Filename3 = null;
public static void main(String[] args) throws IOException {
Scanner sc= new Scanner(System.in);
System.out.println("Enter the Path of first file:- \n Eg:- C:\\Users\\ruturaj.tambe\\Desktop\\Excel1.xlsx");
String Filename1 = sc.nextLine();
System.out.println("Enter the Path of second file:- ");
String Filename2 = sc.nextLine();
System.out.println("Enter the name of Excel sheet to be generated:-");
String Filename3=sc.nextLine();
new CompareExcelSheet().readExcel(Filename1,Filename2,Filename3);
// new ReadExcelFinal().print(Excel1);
// new ReadExcelFinal().print(Excel2);
sc.close();
}
public void writeExcel(String Filename3) {
try {
XSSFWorkbook workbook3 = new XSSFWorkbook();
XSSFSheet sheet3 = workbook3.createSheet("Sheet3");
int q=0;
int rows=0;
System.out.println("\n\nCreating new excel sheet:");
for(int i=0;i<=numberOfRows;i++)
{
Row row = sheet3.createRow(rows++);
for(int j=0;j<numberOfCells;j++)
{
Cell cell = row.createCell(j);
cell.setCellValue(Excel3.get(q));
q++;
}
}
FileOutputStream out = new FileOutputStream(new File(Filename3+".xlsx"));
workbook3.write(out);
out.close();
workbook3.close();
new CompareExcelSheet().printString(Excel3);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void compareStore(ArrayList<Object> array1,ArrayList<Object> array2,String Filename3) throws IOException {
try {
int r1= array1.size();
System.out.println("Total numbers Values in first excel:"+r1);
int r2= array2.size();
System.out.println("Total numbers Values in second excel:"+r2);
int value=0;
for(int i=0;i<=numberOfRows;i++)
{
System.out.println("");
for(int j=1;j<=numberOfCells;j++)
{
String arrr1=(array1.get(value)).toString().trim();
String arrr2=(array2.get(value)).toString().trim();
if(arrr1.equals(arrr2)) {
Excel3.add(arrr1);
}else {
Excel3.add(" Not equal: "+ ResultString1 + ":- " +arrr1+" "+ ResultString2+":- "+arrr2);
error++;
}
value++;
total++;
}
}
System.out.println("\nNo of cells that did not match:- "+error);
System.out.println("Percent error in the sheets:- "+(error*100.00/total)+" %");
new CompareExcelSheet().writeExcel(Filename3);
}catch(Exception e) {
System.out.println(e);
}
}
public void print(ArrayList<Object> array) {
try {
int r1= array.size();
System.out.println("Total Number of elements:"+r1);
int s=0;
System.out.println("Printing the contents of excel sheet:");
for(int i=0;i<=numberOfRows;i++)
{
System.out.println("");
for(int j=1;j<=numberOfCells;j++)
{
System.out.print(array.get(s)+"\t\t\t");
s++;
}
}
}catch (Exception e)
{
e.printStackTrace();
}
}
public void printString(ArrayList<String> array) {
try {
int r1= array.size();
System.out.println("Size Of first array:"+r1);
int s=0;
System.out.println("Printing the contents of excel sheet:");
for(int i=0;i<=numberOfRows;i++)
{
System.out.println("");
for(int j=1;j<=numberOfCells;j++)
{
System.out.print(array.get(s)+"\t\t\t");
s++;
}
}
}catch (Exception e)
{
e.printStackTrace();
}
}
public void readExcel(String Filename1,String Filename2,String Filename3) {
try {
FileInputStream file1 = new FileInputStream(new File(Filename1));
FileInputStream file2 = new FileInputStream(new File(Filename2));
System.out.println(Filename1);
System.out.println(Filename2);
Pattern regex1 = Pattern.compile("([^\\\\/:*?\"<>|\r\n]+$)");
Matcher regexMatcher1 = regex1.matcher(Filename1);
if (regexMatcher1.find()) {
ResultString1 = regexMatcher1.group(1);
System.out.println(ResultString1);
}
Pattern regex2 = Pattern.compile("([^\\\\/:*?\"<>|\r\n]+$)");
Matcher regexMatcher2 = regex2.matcher(Filename2);
if (regexMatcher2.find()) {
ResultString2 = regexMatcher2.group(1);
System.out.println(ResultString2);
}
//Create Workbook instance holding reference to .xlsx file
final XSSFWorkbook workbook1 = new XSSFWorkbook(file1);
final XSSFWorkbook workbook2 = new XSSFWorkbook(file2);
//Get first/desired sheet from the workbook
final XSSFSheet sheet1 = workbook1.getSheetAt(0);
final XSSFSheet sheet2 = workbook2.getSheetAt(0);
//Iterate through each rows one by one
final Iterator<Row> rowIterator1 = sheet1.iterator();
final Iterator<Row> rowIterator2 = sheet2.iterator();
final Iterator<Row> rowIterator1_1 = sheet1.iterator();
final Iterator<Row> rowIterator2_1 = sheet2.iterator();
numberOfRows=sheet1.getLastRowNum();
if (rowIterator1_1.hasNext())
{
Row headerRow1 = (Row) rowIterator1_1.next(); //get the number of cells in the header row
numberOfCells = headerRow1.getPhysicalNumberOfCells();
}
System.out.println("Number of rows :"+numberOfRows);
System.out.println("Number of cells :"+numberOfCells);
numberOfRows2=sheet2.getLastRowNum();
if (rowIterator2_1.hasNext())
{
Row headerRow2 = (Row) rowIterator2_1.next(); //get the number of cells in the header row
numberOfCells2 = headerRow2.getPhysicalNumberOfCells();
}
System.out.println("Number of rows :"+numberOfRows2);
System.out.println("Number of cells :"+numberOfCells2);
if(numberOfRows==numberOfRows2 && numberOfCells==numberOfCells2) {
while (rowIterator1.hasNext() && rowIterator2.hasNext())
{
Row row1 = rowIterator1.next();
Row row2 = rowIterator2.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator1 = row1.cellIterator();
Iterator<Cell> cellIterator2 = row2.cellIterator();
while (cellIterator1.hasNext() && cellIterator2.hasNext())
{
Cell cell1 = cellIterator1.next();
Cell cell2= cellIterator2.next();
//Check the cell type and format accordingly
switch (cell1.getCellTypeEnum())
{
case NUMERIC:
// System.out.print(cell1.getNumericCellValue() + "\t\t");
Excel1.add(cell1.getNumericCellValue());
break;
case STRING:
// System.out.print(cell1.getStringCellValue() + "\t\t");
Excel1.add(cell1.getStringCellValue());
break;
}
switch (cell2.getCellTypeEnum())
{
case NUMERIC:
// System.out.print(cell2.getNumericCellValue() + "\t\t");
Excel2.add(cell2.getNumericCellValue());
break;
case STRING:
// System.out.print(cell2.getStringCellValue() + "\t\t");
Excel2.add(cell2.getStringCellValue());
break;
}
}
//System.out.println("");
}
System.out.println("\nRead Complete: Values from ExcelSheet 1 are stored in Excel1 and Values from ExcelSheet 2 are stored in Excel2 \n");
file1.close();
file2.close();
workbook1.close();
workbook2.close();
new CompareExcelSheet().compareStore(Excel1, Excel2,Filename3);
}else {
System.out.println("Rows and Columns do not match");
}
}catch (Exception e)
{
e.printStackTrace();
}
}
}
Upvotes: 0
user8132864
Reputation:
You can use a row iterator and cell iterator to iterate over rows and cells. This is just a basic example of how to do so. You can hence compare the respective cell content of the respective sheets.
public static void getRow(File file) {
try (FileInputStream fileInputStream = new FileInputStream(file);
Workbook workbook = StreamingReader.builder().bufferSize(4096).open(fileInputStream)) {
Sheet sheet1 = workbook.getSheetAt(0);
Sheet sheet2 = workbook.getSheetAt(1);
Iterator<Row> rowIterator1 = sheet1.iterator();
Iterator<Row> rowIterator2 = sheet2.iterator();
while (rowIterator1.hasNext() && rowIterator2.hasNext()) {
Row currentRow1 = rowIterator1.next();
Row currentRow2 = rowIterator2.next();
Iterator<Cell> cellIterartor1 = currentRow1.iterator();
Iterator<Cell> cellIterator2 = currentRow2.iterator();
while (cellIterartor1.hasNext() && cellIterator2.hasnext()) {
Cell currentCell1 = cellIterartor1.next();
Cell currentCell2 = cellIterartor2.next();
//logic to compare values
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
Upvotes: 1
BDRSuite
Reputation: 1612
Read your sheets data and compare the values.
First you need to read both sheets.
InputStream book1= new FileInputStream("book1.xlsx"));
XSSFWorkbook wb = new HSSFWorkbook(book1);
XSSFSheet sheet1 = myWorkBook.getSheetAt(0) // first sheet
Row row = sheet1.getRow(0); // first row
Cell cell = row.getCell(0);
String value = cell.getStringCellValue();// use a loop to read all the cells in the rows.
Read the cell contents and put them in String or int whatever type and do the same for book2. Now compare the variables holding data from both sheets. Also please read this tutorial if you require additional reference
Upvotes: 1
Related Questions
- compare 2 xlsx files and highlight differences using POI
- Apache POI : easy way to compare worksheets?
- Reading cell values from excel file using Apache POI
- Selenium Compare Web table with Excel data
- Why is my Apache POI code not reading excel worksheet?
- How to compare two excel sheets
- comparing data from one sheet and writing on another sheet in same workbookusing Apache POI and Java
- How to check exact style from two excel file using apache poi?
- Fetch the data from Excel using Apache poi using java
- Compare specific row or column of xls sheet using java and Apache POI