一、新建项目Vlook项目
using MiniExcelLibs;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.IO;
namespace Vlook
{
internal class Program
{
static void Main(string[] args)
{
var dir = AppContext.BaseDirectory;
Console.WriteLine("请输入对比的Excel源:");
var fileName1 = Console.ReadLine();
var path1 = Path.Combine(dir, fileName1);
Console.WriteLine("请输入对比的Exce2源:");
var fileName2 = Console.ReadLine();
var path2 = Path.Combine(dir, fileName2);
var rows1 = MiniExcel.Query(path1);
var rows2 = MiniExcel.Query(path2);
var equalColumns = new List<string>();
Console.WriteLine("请输关联列,输入0退出:");
while (true)
{
var column = Console.ReadLine();
if (column.ToString().ToLower() == "0")
{
break;
}
equalColumns.Add(column);
}
var compareColumns = new List<string>();
Console.WriteLine("请输对比的列,输入0退出:");
while (true)
{
var column = Console.ReadLine();
if (column.ToString().ToLower() == "0")
{
break;
}
compareColumns.Add(column);
}
var moreDatas = new List<object>();
var lessDatas = new List<object>();
var diffDatas = new List<object>();
var sameDatas = new List<object>();
var tuple = CompareCore(rows1, rows2, equalColumns, compareColumns);
moreDatas.AddRange(tuple.moreDatas);
diffDatas.AddRange(tuple.diffDatas);
sameDatas.AddRange(tuple.sameDatas);
var tuple2 = CompareCore(rows2, rows1, equalColumns, compareColumns);
lessDatas.AddRange(tuple2.moreDatas);
var compareResultPath= Path.Combine(dir, DateTime.Now.ToString("yyyy-MM-dd HH_mm_ss").ToString()+"_对比结果.xlsx");
var sheets = new Dictionary<string, object>
{
["moreDatas"] = moreDatas,
["lessDatas"] = lessDatas,
["diffDatas"] = diffDatas,
["sameDatas"] = sameDatas
};
MiniExcel.SaveAs(compareResultPath, sheets);
Console.WriteLine("Done");
}
private static (List<object> moreDatas, List<object> diffDatas, List<object> sameDatas) CompareCore(IEnumerable<dynamic> rows1, IEnumerable<dynamic> rows2, List<string> equalColumns, List<string> compareColumns)
{
var moreDatas = new List<object>();
var diffDatas = new List<object>();
var sameDatas = new List<object>();
var i = 0;
foreach (var row1 in rows1)
{
if (i == 0)
{
i++;
continue;
}
var equalColumnValue1 = GetEqualColumnValue(row1, equalColumns);
var exist = false;
dynamic row2Temp = null;
var j = 0;
foreach (var row2 in rows2)
{
if (j == 0)
{
j++;
continue;
}
var equalColumnValue2 = GetEqualColumnValue(row2, equalColumns);
if (equalColumnValue1 == equalColumnValue2)
{
exist = true;
row2Temp = row2;
break;
}
j++;
}
if (exist)
{
var compareColumnValue1 = GetCompareColumnValue(row1, compareColumns);
var compareColumnValue2 = GetCompareColumnValue(row2Temp, compareColumns);
if (compareColumnValue1 == compareColumnValue2)
{
sameDatas.Add(row1);
}
else
{
diffDatas.Add(row1);
}
}
else
{
moreDatas.Add(row1);
}
i++;
}
return ( moreDatas, diffDatas,sameDatas);
}
private static string GetEqualColumnValue(dynamic row, List<string> equalColumns)
{
var str = "";
foreach (var equalColumn in equalColumns)
{
str += ((IDictionary<string, object>)row)[equalColumn];
}
return str;
}
private static string GetCompareColumnValue(dynamic row, List<string> compareColumns)
{
var str = "";
foreach (var compareColumn in compareColumns)
{
str += ((IDictionary<string, object>)row)[compareColumn];
}
return str;
}
}
}
二、首先我们准备两个Products1.xlsx和Products2.xlsx,并给出差异
Products1.xlsx
Products2.xlsx
执行程序: