领导每天让比较两个Excel中的内容，为了节省工作效率多摸鱼，就写了个java接口，通过上传两个文件 进行代码比较得到详细的比较结果(这个需要自己根据日志二开)
目前只实现了比较功能
话不多说直接上代码，具体看注释
package com.yxy.aob.util;import cn.hutool.core.collection.CollUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;/*** @Description: excel对比* @Author: Hyz* @Date: 2024/10/14 11:33* @Version:1.0*/
@Slf4j
public class CompareTwoExcels {private final static String MSG_PREFIX = "【source】与【target】中";/*** 文件1流*/private InputStream sourceExcelFile;/*** 文件2流*/private InputStream targetExcelFile;/*** 是否模糊 true为精准匹配 false为模糊匹配*/private boolean isPerfectMatch;/*** 差集*/private List<String> differential;/*** 错误行*/private List<String> errorMsg;/*** 匹配行*/private List<String> successMsg;/*** 构造方法** @param sourceExcelFile 文件1* @param targetExcelFile 文件2* @param isPerfectMatch 表示是否需要完全匹配*/public CompareTwoExcels(InputStream sourceExcelFile, InputStream targetExcelFile, boolean isPerfectMatch) {this.sourceExcelFile = sourceExcelFile;this.targetExcelFile = targetExcelFile;this.isPerfectMatch = isPerfectMatch;this.differential = new ArrayList<>();this.errorMsg = new ArrayList<>();this.successMsg = new ArrayList<>();}public static void main(String[] args) throws IOException {try (InputStream sourceInputStream = Files.newInputStream(Paths.get("C:\\Users\\Administrator\\Desktop\\1.xlsx"));InputStream targetInputStream = Files.newInputStream(Paths.get("C:\\Users\\Administrator\\Desktop\\1 - 副本.xlsx"))) {// 初始化识别器CompareTwoExcels compareTwoExcels = new CompareTwoExcels(sourceInputStream, targetInputStream, false);// 执行识别方法 需要指定对应的sheet页 下标0开始boolean result = compareTwoExcels.comparedExcels(0, 0);log.info("result为：" + result);} catch (Exception e) {e.printStackTrace();}}/*** 获取初始化文件的sheet数量** @param type 1 表示sourceExcelFile 2 表示targetExcelFile*/private int getSheetNum(Integer type) {log.info("sourceExcelSheetNum为：" + this.sourceExcelFile);try (Workbook workbook = WorkbookFactory.create(type == 1 ? this.sourceExcelFile : this.targetExcelFile)) {return workbook.getNumberOfSheets();} catch (IOException e) {return 0;}}/*** 比较两个excel** @param sourceExcelSheetNum sheet 从0开始* @param targetExcelSheetNum sheet 从0开始*/private boolean comparedExcels(int sourceExcelSheetNum, int targetExcelSheetNum) {log.info("------------------------读取Excel1数据------------------------");List<List<String>> sourceList = this.readExcel(sourceExcelSheetNum, this.sourceExcelFile, "source");log.info("------------------------读取Excel2数据------------------------");List<List<String>> targetList = this.readExcel(targetExcelSheetNum, this.targetExcelFile, "target");if (CollUtil.isEmpty(sourceList) || CollUtil.isEmpty(targetList)) {log.error("sourceList为空!!!");return false;}// 行数不一致直接返回falseif (sourceList.size() != targetList.size()) {log.error("sourceList与targetList长度不一致");return false;}log.info("------------------------开始匹配：【{}】------------------------", isPerfectMatch ? "精准匹配" : "模糊匹配");boolean result = true;try {// 模糊匹配和精准匹配区分if (!isPerfectMatch) {// 模糊匹配 即两份excel的每一行不定for (int i = 1; i <= sourceList.size(); i++) {log.info("↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ 第【{}】行 ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓", i);// 表格1的第i行List<String> list1 = sourceList.get(i - 1);List<String> list2 = targetList.get(i - 1);// 判断1、两个表格的同一行是否相同 (即表格1的第1行和表格2的第1行hashCode是否一致)if (list1.hashCode() != list2.hashCode()) {log.info("------{}第【{}】行匹配结果------UNKNOWN", MSG_PREFIX, i);// 判断2、 需要比较表格1的这一行在表格二中是否存在if (targetList.contains(list1)) {int i1 = targetList.indexOf(list1);log.info("---------详情：【source】中第【{}】行与【target】中第【{}】行匹配（跨行）", i, i1 + 1);this.successMsg.add("【source】中第【" + i + "】行与【target】中第【" + (i1 + 1) + "】行匹配（跨行）");// List<String> cells = targetList.get(targetList.indexOf(list1));
// for (int j = 1; j <= list1.size(); j++) {
// if (cells.contains(list1.get(j - 1))) {
// log.info("---------详情：第【{}】行,第【{}】列 匹配", i, j);
// this.successMsg.add(MSG_PREFIX + "第【" + i + "】行,第【" + j + "】列 匹配");
// } else {
// log.info("---------详情：第【{}】行,第【{}】列 不匹配", i, j);
// this.errorMsg.add(MSG_PREFIX + "第【" + i + "】行,第【" + j + "】列 不匹配");
// result = false;
// }
// }} else {log.info("---------详情：【source】中第【{}】行在【target】中不存在", i);this.errorMsg.add(MSG_PREFIX + "第【" + i + "】行均不存在");result = false;}} else {log.info("------{}第【{}】行匹配结果------SUCCESS", MSG_PREFIX, i);this.successMsg.add(MSG_PREFIX + "第【" + i + "】行匹配");}}} else {// 精准匹配 即两份excel的每一行每一列格式相同for (int i = 1; i <= sourceList.size(); i++) {// 表格1的第i行List<String> list1 = sourceList.get(i - 1);// 表格2的第i行List<String> list2 = targetList.get(i - 1);// 精准匹配 即两份excel的每一行每一列都完全匹配// list2.equals(list1)或list1.hashCode() == list2.hashCode()if (list1.hashCode() == list2.hashCode()) {log.info("------第【{}】行, 匹配------", i);this.successMsg.add(MSG_PREFIX + "第【" + i + "】行, 匹配");} else {log.info("------第【{}】行, 不匹配------", i);this.errorMsg.add(MSG_PREFIX + "第【" + i + "】行, 不匹配");result = false;}}}} catch (IndexOutOfBoundsException ex) {log.error("两个表格可能存在行数不一致，导致sourceList与targetList长度不一致！");result = false;} finally {log.info("------------------------比较结束------------------------");}return result;}/*** 读取excel 封装成集合* 该程序需要传入一份excel 和excel的列数 行数由程序自动检测* 注意：该方法统计的行数是默认第一行为title 不纳入统计的** @return excel的集合*/private List<List<String>> readExcel(int sheetNum, InputStream inputStream, String type) {List<List<String>> list = new ArrayList<>();// 建需要读取的excel文件写入streamtry (Workbook workbook = WorkbookFactory.create(inputStream)) {// 指向sheet下标为0的sheet 即第一个sheet 也可以按在sheet的名称来寻找Sheet sheet = workbook.getSheetAt(sheetNum);// 获取sheet1中的总行数int rowTotalCount = sheet.getLastRowNum();//获取总列数int columnCount = 0;for (int i = 0; i <= rowTotalCount; i++) {// 获取第i列的row对象Row row = sheet.getRow(i);ArrayList<String> listRow = new ArrayList<>();int physicalNumberOfCells = row.getPhysicalNumberOfCells();if (physicalNumberOfCells >= columnCount) {columnCount = physicalNumberOfCells;}for (int j = 0; j < physicalNumberOfCells; j++) {//下列步骤为判断cell读取到的数据是否为null 如果不做处理 程序会报错String cell = null;//如果未null则加上""组装成非null的字符串if (row.getCell(j) == null) {cell = row.getCell(j) + "";listRow.add(cell);//如果读取到额cell不为null 则直接加入 listRow集合} else {cell = row.getCell(j).toString();listRow.add(cell);}}list.add(listRow);}log.info("Excel【{}】中【{}】页签共计：{}行，{}列（列为最大列）", type, sheet.getSheetName(), rowTotalCount + 1, columnCount);} catch (IOException e) {log.error("读取excel sheet{}失败: {}", sheetNum, e.getMessage());}return list;}/*** ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓私有属性方法↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓*/public List<String> getDifferential() {return differential;}private void setDifferential(List<String> differential) {this.differential = differential;}public List<String> getErrorMsg() {return errorMsg;}private void setErrorMsg(List<String> errorMsg) {this.errorMsg = errorMsg;}public List<String> getSuccessMsg() {return successMsg;}private void setSuccessMsg(List<String> successMsg) {this.successMsg = successMsg;}
}
