筛选两个Excel文件里的数据互相存在否

1.应用背景介绍

对比两个不同Excel文件里面的身份证号码，输出匹配结果。【尝试了一次用汉字来给变量命名，非常不错，自己写了个小例子，留做备份用。】

2.maven工程依赖配置

<dependencies>

    <!-- Java POI 操作Excel(读取/写入) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.0.1</version>
    </dependency>
    
    <!-- Java POI 操作Excel(读取/写入) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.0.1</version>
    </dependency>
    
    <!-- lombok-1.16.4 -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.16.4</version>
        <scope>provided</scope>
    </dependency>
    
</dependencies>

3.两个对比文件的javaBean结构

import org.apache.poi.ss.usermodel.Row;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@ToString
public class Employee {

    private String 编号;
    private String 姓名;
    private String 身份证;
    private String 慢病名;
    private String 家庭住址;
    private String 医院名;

    /**
     * Excel结构对象化
     * @param r 行
     * @return 结构对象
     */
    public static Employee fromRow(Row r) {
        Employee e = new Employee();
        e.set编号(r.getCell(0).toString());
        e.set姓名(r.getCell(1).toString());
        e.set身份证(r.getCell(2).toString());
        e.set慢病名(r.getCell(3).toString());
        e.set家庭住址(r.getCell(4).toString());
        e.set医院名(r.getCell(5).toString());
        return e;
    }
}

4.对比Excel用的工具类

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashMap;

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 org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;

/**
 * Excel文件处理用工具类
 */
public class ExcelUtils {

    /**
     * 入力的文件转换为MAP
     * @param sheetid sheetID
     * @param filePath 文件路径
     * @return 文件map
     * @throws Exception throws
     */
    public static HashMap<String, Employee> readWorkBook(int sheetid, String filePath) throws Exception {
        HashMap<String, Employee> map = new HashMap<String, Employee>();
        InputStream inp = new FileInputStream(filePath);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(sheetid);
        for (Row row : sheet) {
            Employee employee = Employee.fromRow(row);
            map.put(employee.get身份证(), employee);
        }
        inp.close();
        return map;
    }

    /**
     * 创建对比后输出的Excel文件的行
     * @param employee 行的字段
     * @param sheet sheet页
     * @param rowCount 第几个单元格
     * @return 行
     */
    public static XSSFRow creatCell(Employee employee, XSSFSheet sheet, int rowCount) {
        XSSFRow row = sheet.createRow(rowCount);
        row.createCell(0).setCellValue(employee.get编号());
        row.createCell(1).setCellValue(employee.get姓名());
        row.createCell(2).setCellValue(employee.get身份证());
        row.createCell(3).setCellValue(employee.get慢病名());
        row.createCell(4).setCellValue(employee.get家庭住址());
        row.createCell(5).setCellValue(employee.get医院名());
        return row;
    }
}

5.测试

import java.io.FileOutputStream;
import java.util.HashMap;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import dto.Employee;
import dto.ExcelUtils;

public class TestClass {

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

        // 对比用Excel文件所在路径
        String excelFileName1 = "C:\\Users\\xxx\\Desktop\\程序\\汇总.xlsx";
        String excelFileName2 = "C:\\Users\\xxx\\Desktop\\程序\\全部.xlsx";

        // 对比结果文件路径
        String excelFileName3 = "C:\\Users\\xxx\\Desktop\\程序\\对比.xlsx";

        // 取得对比文件的Map结构
        HashMap<String, Employee> map1 = ExcelUtils.readWorkBook(0, excelFileName1);
        HashMap<String, Employee> map2 = ExcelUtils.readWorkBook(0, excelFileName2);

        //创建工作薄对象
        @SuppressWarnings("resource")
        XSSFWorkbook workbook = new XSSFWorkbook();

        //新创建的xls需要新创建新的工作簿，offine默认创建的时候会默认生成三个sheet
        XSSFSheet sheet1 = workbook.createSheet("全部里不存在");

        //新创建的xls需要新创建新的工作簿，offine默认创建的时候会默认生成三个sheet
        XSSFSheet sheet2 = workbook.createSheet("全部里存在");

        // 对比结果的作成
        int rowCount = 0;
        int rowCount2 = 0;
        for (String key : map1.keySet()) {
            if (map2.get(key) == null) {
                ExcelUtils.creatCell(map1.get(key), sheet1, rowCount);
                rowCount++;
            } else {
                ExcelUtils.creatCell(map1.get(key), sheet2, rowCount2);
                rowCount2++;
            }
        }
        // 输出对比后的文件
        FileOutputStream out = new FileOutputStream(excelFileName3);
        workbook.write(out);
        out.close();
        System.out.println("createWorkBook success");
    }
}