比较2个excel文件的内容

I have 2 excel files and i wanted to compare the contents and highlight the differences. For example:

我有2个excel文件，我想比较内容并突出显示差异。例如：

first file...

第一档......

name|age
abc|123
def|456
second file...
name|age
abc|123
def|456
ghi|789 - this being the differece

is there any third party libraries to do this? or what would be the best way to do it?

有没有第三方图书馆这样做？或者最好的方法是什么？

3 个解决方案

#1

Like DaDaDom said Apache POI is what you are looking for. You can download it from this page. Mind that POI project is not fully independent and you may need to download some extra libraries. Follow the instructions on Apache POI website. This is how you use it:

像DaDaDom一样，Apache POI就是你要找的。您可以从此页面下载它。请注意，POI项目并非完全独立，您可能需要下载一些额外的库。按照Apache POI网站上的说明进行操作。这是你如何使用它：

InputStream myxls = new FileInputStream("workbook.xls"));
HSSFWorkbook wb = new HSSFWorkbook(myxls); // for *.xlsx use XSSFWorkbook

If it's a new file you might need to create sheet before proceeding, but in this case the files are already created.

如果它是一个新文件，您可能需要在继续之前创建工作表，但在这种情况下，文件已经创建。

HSSFSheet sheet = wb.getSheetAt(0);       // first sheet
HSSFRow row     = sheet.getRow(0);        // first row
HSSFCell cell   = row.getCell((short)0);  // first cell

To get value from the cell use:

要从单元格中获取价值：

String value = cell.getStringCellValue();

However if the type stored in cell is numeric you would get an error. In case of numbers use:

但是，如果存储在单元格中的类型是数字，则会出错。如果使用数字：

Int value = cell.getCellValue();

This is a method I wrote to deal with different cell data types:

这是我编写的用于处理不同单元格数据类型的方法：

public String getValue(int x, int y){
    Row row = this.activeSheet.getRow(y);
    if(row==null) return "";
    Cell cell = row.getCell(x);
    if(cell==null) return "";
    int type = cell.getCellType();
    switch(type){
    case 0:
        return cell.getNumericCellValue() + "";
    case 1:
        return cell.getStringCellValue();
    case 2:
        return cell.getCellFormula();
    case 3:
        return "";
    case 4:
        return cell.getBooleanCellValue() + "";
    case 5:
        return cell.getErrorCellValue() + "";
    default:
        return "";
    }
}

I hope this quick introduction into Apache POI will help you with your project :)

我希望这个对Apache POI的快速介绍将帮助您完成项目:)

#2

From this question, my answer partially duplicated below.

从这个问题来看，我的答案在下面部分重复。

My project simple-excel which provides a bunch of Hamcrest Matchers and wraps up Apache POI's syntax.

我的项目simple-excel提供了一堆Hamcrest Matchers并包含了Apache POI的语法。

When you do something like the following,

当您执行以下操作时，

assertThat(actual, WorkbookMatcher.sameWorkbook(expected));

You'd see, for example,

你会看到，例如，

java.lang.AssertionError:
Expected: entire workbook to be equal
     but: cell at "C14" contained <"bananas"> expected <nothing>,
          cell at "C15" contained <"1,850,000 EUR"> expected <"1,850,000.00 EUR">,
          cell at "D16" contained <nothing> expected <"Tue Sep 04 06:30:00">
    at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)

Read a blog post about it

阅读有关它的博客文章

#3

I would use epplus to load both documents into datatables and then iterate over them to find differences. Depending on how you want to highlight differences, you could simply color the cells with epplus and save them back to the files..

我会使用epplus将两个文档加载到数据表中，然后迭代它们以找到差异。根据您想要突出显示差异的方式，您可以使用epplus为单元格着色并将其保存回文件中。

#1

InputStream myxls = new FileInputStream("workbook.xls"));
HSSFWorkbook wb = new HSSFWorkbook(myxls); // for *.xlsx use XSSFWorkbook