筛选两个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");
}
}