java - 如何比较两个Excel表格

Question

我有两个 Excel 文件，我需要比较它们。

主要情况是用户下载文件，进行一些更改，然后将其上传回来。之后系统对它们进行比较并显示差异报告。

我使用 Apache POI，并尝试了解如何跟踪某些用户操作。例如。用户重命名一行中的所有单元格，然后将其向上移动。如何跟踪这些变化？

问题是，如果我将新文件与他的最后一个副本进行比较，看起来用户删除了一行并创建了另一行，而不是重命名和移动它。

我了解到 Excel 可以跟踪用户 changes并可以创建更改日志，但我在 POI 中没有找到任何提及此功能的信息。这是解决这个问题的一种想法，但我认为这不是一个好的想法。

那么处理这些用户操作的最佳方式是什么？

Answer 1

在文件中，我仅比较第一列，删除此行

if (cell1.getColumnIndex() == 0)

尝试按照我们的要求修改

public class ReadWriteXLImple {
    static Boolean check = false;

    public static void main(String args[]) throws IOException {

        try {

            ArrayList arr1 = new ArrayList();
            ArrayList arr2 = new ArrayList();
            ArrayList arr3 = new ArrayList();

            FileInputStream file1 = new FileInputStream(new File(
                    "src\\file\\Book1.xls"));

            FileInputStream file2 = new FileInputStream(new File(
                    "src\\file\\Book2.xls"));

            // Get the workbook instance for XLS file
            HSSFWorkbook workbook1 = new HSSFWorkbook(file1);
            HSSFWorkbook workbook2 = new HSSFWorkbook(file2);

            // Get first sheet from the workbook
            HSSFSheet sheet1 = workbook1.getSheetAt(0);
            HSSFSheet sheet2 = workbook2.getSheetAt(0);

            // Compare sheets

            // Get iterator to all the rows in current sheet1
            Iterator<Row> rowItera`enter code here`tor1 = sheet1.iterator();`enter code here`
            Iterator<Row> rowIterator2 = sheet2.iterator();

            while (rowIterator1.hasNext()) {
                Row row = rowIterator1.next();
                // For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    // This is for read only one column from excel
                    if (cell.getColumnIndex() == 0) {
                        // Check the cell type and format accordingly
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue());
                            arr1.add(cell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            arr1.add(cell.getStringCellValue());
                            System.out.print(cell.getStringCellValue());
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            arr1.add(cell.getBooleanCellValue());
                            System.out.print(cell.getBooleanCellValue());
                            break;
                        }

                    }

                }

                System.out.println(" ");
            }

            file1.close();

            System.out.println("-----------------------------------");
            // For retrive the second excel data
            while (rowIterator2.hasNext()) {
                Row row1 = rowIterator2.next();
                // For each row, iterate through all the columns
                Iterator<Cell> cellIterator1 = row1.cellIterator();

                while (cellIterator1.hasNext()) {

                    Cell cell1 = cellIterator1.next();
                    // Check the cell type and format accordingly

                    // This is for read only one column from excel
                    if (cell1.getColumnIndex() == 0) {
                        switch (cell1.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            arr2.add(cell1.getNumericCellValue());
                            System.out.print(cell1.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            arr2.add(cell1.getStringCellValue());
                            System.out.print(cell1.getStringCellValue());
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            arr2.add(cell1.getBooleanCellValue());
                            System.out.print(cell1.getBooleanCellValue());
                            break;

                        }

                    }
                    // this continue is for
                    // continue;
                }

                System.out.println("");
            }

            System.out.println("book1.xls -- " + arr1.size());
            System.out.println("book1.xls -- " + arr2.size());

            // compare two arrays
            for (Object process : arr1) {
                if (!arr2.contains(process)) {
                    arr3.add(process);
                }
            }
            System.out.println("arr3 list values - = - = + " + arr3);
            writeStudentsListToExcel(arr3);

            // closing the files
            file1.close();
            file2.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    // write into new file excel

    private static void writeStudentsListToExcel(ArrayList arr3) {

        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(
                    "D:/Test/output/result.xls");

            HSSFWorkbook workBook = new HSSFWorkbook();
            HSSFSheet spreadSheet = workBook.createSheet("email");
            HSSFRow row;
            HSSFCell cell;
            // System.out.println("array size is :: "+minusArray.size());
            int cellnumber = 0;
            for (int i1 = 0; i1 < arr3.size(); i1++) {
                row = spreadSheet.createRow(i1);
                cell = row.createCell(cellnumber);
                // System.out.print(cell.getCellStyle());
                cell.setCellValue(arr3.get(i1).toString().trim());
            }
            workBook.write(fos);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        catch (IOException e) {
            e.printStackTrace();
        }

    }

    // end -write into new file
}