如何使用 apache POI 和 java 比较 2 个不同的 excel.xls 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27401694/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-11 04:17:55 来源:igfitidea点击:
How compare 2 different excel.xls sheets using apache POI and java
提问by Sagar Chaudhari
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 .
问题陈述：我想从两个不同的网站获取数据，并将这些数据写入工作簿表 1 和表 2 正在寻找执行 excel 比较的解决方案，寻求专家指导。
=======================================================
================================================== ======
Excel sheet data
Excel工作表数据
===================================
====================================
solution I need mismatched from these two sheets using Apache POI and java.
解决方案我需要使用 Apache POI 和 java 从这两个工作表中不匹配。
================================================== Below is the code to read sheet 0 , i stuck with hoe to read second sheet1 and compare it.
==================================================下面是读取工作表 0 的代码，我坚持用锄头读取第二张工作表并进行比较。
import java.io.FileInputStream;
导入 java.io.FileInputStream;
import jxl.Sheet; import jxl.Workbook;
进口 jxl.Sheet; 导入 jxl.Workbook;
public class MyCCD {
公共类 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
{
回答by vembutech
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 tutorialif you require additional reference
读取单元格内容并将它们放入 String 或 int 任何类型，并对 book2 执行相同操作。现在比较保存两个工作表中数据的变量。如果您需要其他参考，也请阅读本教程
回答by Dilinie Seimon
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();
}
}
回答by ruturaj tambe
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.
此代码比较两个 Excel 工作表并生成第三个。如果值相同，它将保留第一个 Excel 工作表中的值，但如果值不同，它将在一个块中显示两个值，并显示它们各自的 Excel 工作表名称。希望这对你有帮助。
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();
}
}
}
}
