/// <summary>
/// Table Intersection
/// </summary>
/// <param name="target">The other excel file</param>
/// <returns></returns>
public ExcelModel Intersect(ExcelModel target)
{
DataTable dt = new DataTable("ResultSet");
foreach (var item in Columns)
{
dt.Columns.Add(item);
}
var sourceRows =
sourceData.Select()
.Select(dr =>
dr.ItemArray
.Select(x => x.ToString())
.ToArray())
.ToList();
var targetRows =
target.sourceData.Select()
.Select(dr =>
dr.ItemArray
.Select(x => x.ToString())
.ToArray())
.ToList();
foreach (var sourceItem in sourceRows)
{
foreach (var targetItem in targetRows)
{
if (targetItem[ColIndex] == sourceItem[ColIndex])
{
bool isMatch = true;
for (int i = 0; i < sourceItem.Length; i++)
{
if (sourceItem[i] != targetItem[i])
{
isMatch = false;
break;
}
}
if (isMatch)
{
dt.Rows.Add(sourceItem);
}
break;
}
}
}
return(new ExcelModel(dt));
}
/// <summary>
/// Table difference i.e Excel Difference
/// </summary>
/// <param name="target"> the other excel file</param>
/// <returns></returns>
public ExcelModel Difference(ExcelModel target)
{
DataTable dt = new DataTable("ResultSet");
foreach (var item in Columns)
{
if (item != Columns[ColIndex])
{
dt.Columns.Add("Source_" + item.ToString());
dt.Columns.Add("Target_" + item.ToString());
}
else
{
dt.Columns.Add(item);
}
}
var sourceRows =
sourceData.Select()
.Select(dr =>
dr.ItemArray
.Select(x => x.ToString())
.ToArray())
.ToList();
var targetRows =
target.sourceData.Select()
.Select(dr =>
dr.ItemArray
.Select(x => x.ToString())
.ToArray())
.ToList();
foreach (var sourceItem in sourceRows)
{
foreach (var targetItem in targetRows)
{
if (targetItem[ColIndex] == sourceItem[ColIndex])
{
bool isMatch = true;
for (int i = 0; i < sourceItem.Length; i++)
{
if (i > targetItem.Length || sourceItem[i] != targetItem[i])
{
isMatch = false;
break;
}
}
if (!isMatch)
{
List <string> itemArray = new List <string>();
itemArray.Add(sourceItem[ColIndex]);
for (int i = 0; i < sourceItem.Length; i++)
{
if (i == ColIndex)
{
continue;
}
itemArray.Add(sourceItem[i]);
itemArray.Add(targetItem[i]);
}
dt.Rows.Add(itemArray.ToArray());
}
break;
}
}
}
return(new ExcelModel(dt));
}