如何比较两个不同的excel。使用apache POI和java的xls表

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表数据

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,我坚持锄读第二sheet1和比较。

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
    {

2 个解决方案

#1

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

读取单元格内容并将它们放在字符串或int类型中，并对book2执行相同的操作。现在比较两个表中保存数据的变量。如果您需要更多的参考资料，请阅读本教程

#2

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();
    }

}

#1