Java实现一对多匹配，需要在表二的变动日期中找到表一某人现职层/职等等于目标职务/职等的那个日期，更新到表一中

需求：Excel表格，sheet1和sheet2，sheet1中无重复，sheet2中多条重复，从sheet2从找出日期最早的匹配sheet1，更新sheet1。

sheet1 如下所示

sheet2 如下所示

需求：需要在表二的变动日期中找到表一某人现职层/职等等于目标职务/职等的那个日期，更新到表一中
代码如下：
sheet2对应的实体类User2

import java.util.Date;

public class User2  {
    private String id;
    private String name;
    private String number;
    private String targetGrade;
    private String targetJob;
    private Date changeDate;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public String getTargetGrade() {
        return targetGrade;
    }

    public void setTargetGrade(String targetGrade) {
        this.targetGrade = targetGrade;
    }

    public String getTargetJob() {
        return targetJob;
    }

    public void setTargetJob(String targetJob) {
        this.targetJob = targetJob;
    }

    public Date getChangeDate() {
        return changeDate;
    }

    public void setChangeDate(Date changeDate) {
        this.changeDate = changeDate;
    }

    @Override
    public String toString() {
        return "User2{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", number='" + number + '\'' +
                ", targetGrade='" + targetGrade + '\'' +
                ", targetJob='" + targetJob + '\'' +
                ", changeDate='" + changeDate + '\'' +
                '}';
    }
}

sheet1对应的实体类User3

public class User3 {
    private String name;
    private String number;
    private String job;
    private String department;
    private String post;
    private String nowJob;
    private String nowGrade;
    private String hireDate;
    private String dispatch;
    private String lastChange;
    private String remark;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public String getPost() {
        return post;
    }

    public void setPost(String post) {
        this.post = post;
    }

    public String getNowJob() {
        return nowJob;
    }

    public void setNowJob(String nowJob) {
        this.nowJob = nowJob;
    }

    public String getNowGrade() {
        return nowGrade;
    }

    public void setNowGrade(String nowGrade) {
        this.nowGrade = nowGrade;
    }

    public String getHireDate() {
        return hireDate;
    }

    public void setHireDate(String hireDate) {
        this.hireDate = hireDate;
    }

    public String getDispatch() {
        return dispatch;
    }

    public void setDispatch(String dispatch) {
        this.dispatch = dispatch;
    }

    public String getLastChange() {
        return lastChange;
    }

    public void setLastChange(String lastChange) {
        this.lastChange = lastChange;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    @Override
    public String toString() {
        return "User3{" +
                "name='" + name + '\'' +
                ", number='" + number + '\'' +
                ", job='" + job + '\'' +
                ", department='" + department + '\'' +
                ", post='" + post + '\'' +
                ", nowJob='" + nowJob + '\'' +
                ", nowGrade='" + nowGrade + '\'' +
                ", hireDate='" + hireDate + '\'' +
                ", dispatch='" + dispatch + '\'' +
                ", lastChange='" + lastChange + '\'' +
                ", remark='" + remark + '\'' +
                '}';
    }
}

日期比较类

import java.util.Comparator;

public class UserComparator implements Comparator<User2> {
    @Override
    public int compare(User2 o1, User2 o2) {
        if(o1.getChangeDate().before(o2.getChangeDate())){
            return -1;
        }
        return 1;
    }
}

业务具体实现类

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

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 一对多，取出多表中最早的匹配的数据，更新到一的日期里面
 * @author liangy
 */
public class ExcelQueryData {
    static String importPath = "D:\\1111.xlsx";
    static String exportPath = "D:\\aaa.xlsx";

    private static final List<String> KEYS = new ArrayList<>();

    private static final List<User2> INFO_LIST2 =new ArrayList<User2>();
    private static final List<User3> INFO_LIST3 =new ArrayList<User3>();
    private static final List<User> OUT_USER = new ArrayList<>();
    public static void readExcelByPoi1(){
        Map<String, List<String>> map =new HashMap<String,List<String>>();
        INFO_LIST2.clear();
        try{
            InputStream is =new FileInputStream(importPath);

            int index = importPath.lastIndexOf(".");
            String postfix = importPath.substring(index+1);

            Workbook workbook =null;
            if("xls".equals(postfix)){
                workbook =new HSSFWorkbook(is);
            }else if("xlsx".equals(postfix)){
                workbook =new XSSFWorkbook(is);
            }
            //获取第1张表
            Sheet sheet = workbook.getSheetAt(1);
            //总的行数
            int rows = sheet.getLastRowNum();
            System.out.println("总行数："+rows);
            //总的列数--->最后一列为null则有问题，读取不完整，将表头的数目作为总的列数，没有的则补为null
            int columns = sheet.getRow(0).getLastCellNum();
//            System.out.println("总列数："+columns);
            //先列后行
            for(int i =1; i <= rows; i++){
                Row row = sheet.getRow(i);
                if(null!= row && row.getFirstCellNum()==-1){//这一行是空行，不读取
                    continue;
                }
                List<String> contentList =new ArrayList<String>();
                for(int j =0; j < columns; j++){
                    if(row!=null && row.getCell(j)!=null){
                        contentList.add(ExcelUtils.getCellValue(row.getCell(j)));
                    }else{
                        contentList.add("");
                    }
                }
                map.put("StorageInfo"+i, contentList);
            }

            //遍历map集合，封装成bean
            for(Map.Entry<String,List<String>> entry : map.entrySet()){
                List<String> list = entry.getValue();
                User2 user =new User2();
                user.setId(list.get(0));
                user.setNumber(list.get(1));
                user.setName(list.get(2));
                user.setTargetGrade(list.get(3));
                user.setTargetJob(list.get(4));
                user.setChangeDate(stringToDate(list.get(5)));
                INFO_LIST2.add(user);
            }
            is.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public static Date stringToDate(String date) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        return sdf.parse(date);
    }

    public static void readExcelByPoi2(){
//        List<User> infoList =new ArrayList<User>();
        Map<String, List<String>> map =new HashMap<String,List<String>>();
        INFO_LIST3.clear();
        try{
            InputStream is =new FileInputStream(importPath);

            int index = importPath.lastIndexOf(".");
            String postfix = importPath.substring(index+1);

            Workbook workbook =null;
            if("xls".equals(postfix)){
                workbook =new HSSFWorkbook(is);
            }else if("xlsx".equals(postfix)){
                workbook =new XSSFWorkbook(is);
            }
            //获取第1张表
            Sheet sheet = workbook.getSheetAt(0);
            //总的行数
            int rows = sheet.getLastRowNum();
            System.out.println("总行数："+rows);
            //总的列数--->最后一列为null则有问题，读取不完整，将表头的数目作为总的列数，没有的则补为null
            int columns = sheet.getRow(0).getLastCellNum();
//            System.out.println("总列数："+columns);
            //先列后行
            for(int i =1; i <= rows; i++){
                Row row = sheet.getRow(i);
                if(null!= row && row.getFirstCellNum()==-1){//这一行是空行，不读取
                    continue;
                }
                List<String> contentList =new ArrayList<String>();
                for(int j =0; j < columns; j++){
                    if(row!=null && row.getCell(j)!=null){
                        contentList.add(ExcelUtils.getCellValue(row.getCell(j)));
                    }else{
                        contentList.add("");
                    }
                }
                map.put("StorageInfo"+i, contentList);
            }

            //遍历map集合，封装成bean
            for(Map.Entry<String,List<String>> entry : map.entrySet()){
                List<String> list = entry.getValue();
                User3 user =new User3();
                user.setNumber(list.get(0));
                user.setName(list.get(1));
                user.setJob(list.get(2));
                user.setDepartment(list.get(3));
                user.setPost(list.get(4));
                user.setNowJob(list.get(5));
                user.setNowGrade(list.get(6));
                user.setHireDate(list.get(7));
                user.setDispatch(list.get(8));
                user.setLastChange(list.get(9));
                user.setRemark(list.get(10));

                INFO_LIST3.add(user);
            }
            is.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    static List<User2> usersList = new ArrayList<User2>();
    public static void checkDate(){
        for(User3 user3 : INFO_LIST3){
            for(User2 user2 : INFO_LIST2) {
                if(user3.getName().equals(user2.getName()) && user3.getNumber().equals(user3.getNumber()) &&
                    user3.getNowGrade().equals(user2.getTargetGrade()) ){
                    usersList.add(user2);

                }
            }
            INFO_LIST2.removeAll(usersList);
            System.out.println("usersList"+usersList);

            if(usersList.size()>0){
                usersList.sort(new UserComparator());
                System.out.println(usersList);
                User2 targetUser = usersList.get(0);
                user3.setLastChange(dateToString(targetUser.getChangeDate()));
            }
            usersList.clear();
        }
    }

    public static String dateToString(Date date){
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        return simpleDateFormat.format(date);
    }

    public static void exportExcel(List<User3> userList) throws IOException {
        //1. 创建一个Excel表格
        XSSFWorkbook workbook = new XSSFWorkbook();
        //2. 创建工作表
        XSSFSheet sheet = workbook.createSheet("工作表一");
        for(int i=0;i<userList.size();i++){
            User3 user  = userList.get(i);
            //3. 创建行
            XSSFRow row1 = sheet.createRow(i);
            //4. 创建单元格赋值
            XSSFCell cell1 = row1.createCell(0);
            cell1.setCellValue(user.getNumber());
            XSSFCell cell2 = row1.createCell(1);
            cell2.setCellValue(user.getName());
            XSSFCell cell3 = row1.createCell(2);
            cell3.setCellValue(user.getJob());
            XSSFCell cell4 = row1.createCell(3);
            cell4.setCellValue(user.getDepartment());
            XSSFCell cell5 = row1.createCell(4);
            cell5.setCellValue(user.getPost());
            XSSFCell cell6 = row1.createCell(5);
            cell6.setCellValue(user.getNowJob());
            XSSFCell cell7 = row1.createCell(6);
            cell7.setCellValue(user.getNowGrade());
            XSSFCell cell8 = row1.createCell(7);
            cell8.setCellValue(user.getHireDate());
            XSSFCell cell9 = row1.createCell(8);
            cell9.setCellValue(user.getDispatch());
            XSSFCell cell10 = row1.createCell(9);
            cell10.setCellValue(user.getLastChange());
            XSSFCell cell11 = row1.createCell(10);
            cell11.setCellValue(user.getRemark());
        }
        //5. 通过输出流将对象下载到磁盘
        FileOutputStream fileOutputStream = new FileOutputStream(exportPath);
        workbook.write(fileOutputStream);
        //刷新输出流
        fileOutputStream.flush();
        //6. 释放资源
        fileOutputStream.close();
        workbook.close();
    }

    public static void main(String[] args) throws IOException {
        long start = System.currentTimeMillis();
        readExcelByPoi1();
        System.out.println(INFO_LIST2);
        readExcelByPoi2();
        System.out.println(INFO_LIST3);
        checkDate();
//        System.out.println(outUser);
        exportExcel(INFO_LIST3);
        long end = System.currentTimeMillis();
        long duration = (end - start)/1000;
        System.out.println("总用时："+duration+" 秒");
    }
}

输出结果如下：