vba 如何比较两张excel表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28544569/
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
How to compare two excel sheets
提问by Ishikawa Yoshi
I have two excel files and I need to compare them.
我有两个 excel 文件，我需要比较它们。
Main case is that the user downloads the file, makes some changes, and uploads it back. After that the system compare them and show a discrepancy report.
主要情况是用户下载文件，进行一些更改，然后将其上传回来。之后系统会比较它们并显示差异报告。
I use Apache POI, and I try to understand how track some user actions. E.g. user rename all the cells in a row, then moves it up. How to track these changes?
我使用 Apache POI，并尝试了解如何跟踪某些用户操作。例如，用户重命名一行中的所有单元格，然后将其向上移动。如何追踪这些变化？
The problem is if I compare the new file with his last copy it looks like the user deleted one row and created another, instead of renaming and moving it.
问题是，如果我将新文件与他的最后一个副本进行比较，看起来用户删除了一行并创建了另一行，而不是重命名和移动它。
I have read that Excel can track user changesand can create change logs but I don't find any mentions of this feature in POI. This is one idea how to solve this problem, but I think it's not a good one.
我已经读到 Excel 可以跟踪用户更改并可以创建更改日志，但我在 POI 中没有找到任何提及此功能的内容。这是如何解决这个问题的一种想法，但我认为这不是一个好主意。
So what is the best way to handle these user actions?
那么处理这些用户操作的最佳方法是什么？
回答by chandrashekar.n
In the file I am comparing only first column remove this line
在我只比较第一列的文件中删除这一行
if (cell1.getColumnIndex() == 0)
try to modification as per our requirement
尝试根据我们的要求进行修改
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
}
回答by Oedhel Setren
I don't know how to do this via Apache, but I know under the review tab in Excel there is an option to track changes, but it needs to be enabled first. Its a robust feature that tells you who, when, and what. It would require you to use the excel sheet (which can also be set to share mode) for review of these changes, but you would only need the one sheet versus an old copy and a new copy.
我不知道如何通过 Apache 执行此操作，但我知道在 Excel 的审阅选项卡下有一个跟踪更改的选项，但需要先启用它。它是一个强大的功能，可以告诉您是谁、何时以及什么。它需要您使用 Excel 工作表（也可以设置为共享模式）来查看这些更改，但您只需要一张工作表而不是旧副本和新副本。
