private static void DiffCells(
IEnumerable <DiffResult <ExcelRow> > results, ExcelSheetDiff sheetDiff, Dictionary <int, ExcelColumnStatus> columnStatusMap)
{
foreach (var result in results)
{
switch (result.Status)
{
case DiffStatus.Equal:
DiffCellsCaseEqual(result, sheetDiff, columnStatusMap);
break;
case DiffStatus.Modified:
DiffCellsCaseEqual(result, sheetDiff, columnStatusMap);
break;
case DiffStatus.Deleted:
DiffCellsCaseDeleted(result, sheetDiff, columnStatusMap);
break;
case DiffStatus.Inserted:
DiffCellsCaseInserted(result, sheetDiff, columnStatusMap);
break;
}
}
}
private static void DiffCells(
IEnumerable <DiffResult <ExcelRow> > results, ExcelSheetDiff sheetDiff, Dictionary <int, ExcelColumnStatus> columnStatusMap)
{
foreach (var result in results)
{
switch (result.Status)
{
case DiffStatus.Equal:
try
{
DiffCellsCaseEqual(result, sheetDiff, columnStatusMap);
}
catch (Exception ex)
{
string a = ex.StackTrace;
}
break;
case DiffStatus.Modified:
try
{
DiffCellsCaseEqual(result, sheetDiff, columnStatusMap);
}
catch (Exception ex)
{
string b = ex.StackTrace;
}
break;
case DiffStatus.Deleted:
try
{
DiffCellsCaseDeleted(result, sheetDiff, columnStatusMap);
}
catch (Exception ex)
{
string c = ex.StackTrace;
}
break;
case DiffStatus.Inserted:
try
{
DiffCellsCaseInserted(result, sheetDiff, columnStatusMap);
}
catch (Exception ex)
{
string d = ex.StackTrace;
}
break;
}
}
}
private static void DiffCellsCaseInserted(
DiffResult <ExcelRow> result, ExcelSheetDiff sheetDiff, Dictionary <int, ExcelColumnStatus> columnStatusMap)
{
var row = sheetDiff.CreateRow();
var columnIndex = 0;
foreach (var cell2 in result.Obj2.Cells)
{
var cell1 = new ExcelCell(string.Empty, cell2.OriginalColumnIndex, cell2.OriginalRowIndex);
row.CreateCell(cell1, cell2, columnIndex, ExcelCellStatus.Added);
columnIndex++;
}
}
private static void DiffCellsCaseEqual(
DiffResult <ExcelRow> result, ExcelSheetDiff sheetDiff, Dictionary <int, ExcelColumnStatus> columnStatusMap)
{
var row = sheetDiff.CreateRow();
var equalizedCells = EqualizeColumnCount(result.Obj1.Cells, result.Obj2.Cells, columnStatusMap);
var columnIndex = 0;
foreach (var pair in equalizedCells)
{
var srcCell = pair.Item1;
var dstCell = pair.Item2;
if (srcCell != null && dstCell != null)
{
var status = srcCell.Value.Equals(dstCell.Value) ? ExcelCellStatus.None : ExcelCellStatus.Modified;
if (columnStatusMap[columnIndex] == ExcelColumnStatus.Deleted)
{
status = ExcelCellStatus.Removed;
}
else if (columnStatusMap[columnIndex] == ExcelColumnStatus.Inserted)
{
status = ExcelCellStatus.Added;
}
row.CreateCell(srcCell, dstCell, columnIndex, status);
}
else if (srcCell != null && dstCell == null)
{
dstCell = new ExcelCell(string.Empty, srcCell.OriginalColumnIndex, srcCell.OriginalColumnIndex);
row.CreateCell(srcCell, dstCell, columnIndex, ExcelCellStatus.Removed);
}
else if (srcCell == null && dstCell != null)
{
srcCell = new ExcelCell(string.Empty, dstCell.OriginalColumnIndex, dstCell.OriginalColumnIndex);
row.CreateCell(srcCell, dstCell, columnIndex, ExcelCellStatus.Added);
}
else
{
srcCell = new ExcelCell(string.Empty, 0, 0);
dstCell = new ExcelCell(string.Empty, 0, 0);
row.CreateCell(srcCell, dstCell, columnIndex, ExcelCellStatus.None);
}
columnIndex++;
}
}
public static ExcelSheetDiff Diff(ExcelSheet src, ExcelSheet dst, ExcelSheetDiffConfig config)
{
var srcColumns = src.CreateColumns();
var dstColumns = dst.CreateColumns();
var columnStatusMap = CreateColumnStatusMap(srcColumns, dstColumns, config);
var option = new DiffOption <ExcelRow>();
option.EqualityComparer =
new RowComparer(new HashSet <int>(columnStatusMap.Where(i => i.Value != ExcelColumnStatus.None).Select(i => i.Key)));
// 这里实际上计算的是有没有插入新列
foreach (var row in src.Rows.Values)
{
var shifted = new List <ExcelCell>();
var index = 0;
var queue = new Queue <ExcelCell>(row.Cells);
while (queue.Any())
{
if (columnStatusMap[index] == ExcelColumnStatus.Inserted)
{
shifted.Add(new ExcelCell(string.Empty, 0, 0));
}
else
{
shifted.Add(queue.Dequeue());
}
index++;
}
row.UpdateCells(shifted);
}
foreach (var row in dst.Rows.Values)
{
var shifted = new List <ExcelCell>();
var index = 0;
var queue = new Queue <ExcelCell>(row.Cells);
while (queue.Any())
{
if (columnStatusMap[index] == ExcelColumnStatus.Deleted)
{
shifted.Add(new ExcelCell(string.Empty, 0, 0));
}
else
{
shifted.Add(queue.Dequeue());
}
index++;
}
row.UpdateCells(shifted);
}
var r = DiffUtil.Diff(src.Rows.Values, dst.Rows.Values, option);
r = DiffUtil.Order(r, DiffOrderType.LazyDeleteFirst);
var resultArray = DiffUtil.OptimizeCaseDeletedFirst(r).ToArray();
if (resultArray.Length > 10000)
{
var count = 0;
var indices = Enumerable.Range(0, 100).ToList();
foreach (var result in resultArray)
{
if (result.Status != DiffStatus.Equal)
{
indices.AddRange(Enumerable.Range(Math.Max(0, count - 100), 200));
}
count++;
}
indices = indices.Distinct().ToList();
resultArray = indices.Where(i => i < resultArray.Length).Select(i => resultArray[i]).ToArray();
}
var sheetDiff = new ExcelSheetDiff(src, dst);
DiffCells(resultArray, sheetDiff, columnStatusMap);
return(sheetDiff);
}
// public void DumpByCreate()
// {
// // 尝试直接通过封装的数据来创建出原始 excel 表，比较冒险，可能有缺少的内容导致写入的 excel 错误
// // 但是这样写入成功之后，界面操作修改都会变得简单
// var wb = new XSSFWorkbook();
//
// foreach (var sheetName in SheetNames)
// {
// var table = wb.CreateSheet(sheetName);
//
// var sheetWrap = Sheets[sheetName];
//
// foreach (var rowWrap in sheetWrap.Rows)
// {
// var row = table.CreateRow(rowWrap.Key);
//
// // if (row is null)
// // {
// // break;
// // }
//
// var i = 0;
// foreach (var cellWrap in rowWrap.Value.Cells)
// {
// var cell = row.CreateCell(i);
// // var cell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
//
// XSSFCellStyle cellstyle = wb.CreateCellStyle() as XSSFCellStyle;
// if (cellWrap.OriginalCellStyle != null)
// {
// cellstyle.CloneStyleFrom(cellWrap.OriginalCellStyle);
// }
//
// cell.CellStyle = cellstyle;
// cell.SetCellValue(cellWrap.Value);
// i++;
// }
// }
// }
//
// using (FileStream stream = new FileStream(@"D:/test.xlsx", FileMode.Create, FileAccess.Write))
// {
// wb.Write(stream);
// }
// }
public void Dump(string sheetName, ExcelSheetDiff sheetDiff, bool isLeft)
{
var workbook = rawWorkbook;
var table = workbook.GetSheet(sheetName);
var tableModified = false;
// 添加需要的行，让原始表格和diff行数保持一致
foreach (KeyValuePair <int, ExcelRowDiff> sheetDiffRow in sheetDiff.Rows)
{
if (sheetDiffRow.Value.IsRemoved() && !isLeft)
{
if (sheetDiffRow.Key <= table.LastRowNum)
{
Debug.Print("ShiftAddRight : " + sheetDiffRow.ToString());
table.ShiftRows(sheetDiffRow.Key, table.LastRowNum, 1);
}
table.CreateRow(sheetDiffRow.Key);
}
if (sheetDiffRow.Value.IsAdded() && isLeft)
{
if (sheetDiffRow.Key <= table.LastRowNum)
{
Debug.Print("ShiftAddLeft : " + sheetDiffRow.ToString());
table.ShiftRows(sheetDiffRow.Key, table.LastRowNum, 1);
}
table.CreateRow(sheetDiffRow.Key);
}
}
// 逐行比对修改
foreach (var rowDiff in sheetDiff.Rows)
{
if (rowDiff.Value.LeftEmpty())
{
continue;
}
if (rowDiff.Value.RightEmpty())
{
continue;
}
if (!rowDiff.Value.NeedMerge())
{
continue;
}
var rawRow = table.GetRow(rowDiff.Key) ?? table.CreateRow(rowDiff.Key);
foreach (var cellDiff in rowDiff.Value.Cells)
{
var rawCell = rawRow.GetCell(cellDiff.Key, MissingCellPolicy.CREATE_NULL_AS_BLANK);
ExcelCell targetWrap = null;
if (isLeft)
{
if (cellDiff.Value.MergeStatus == ExcelCellMergeStatus.UseRight)
{
targetWrap = cellDiff.Value.DstCell;
}
}
else
{
if (cellDiff.Value.MergeStatus == ExcelCellMergeStatus.UseLeft)
{
targetWrap = cellDiff.Value.SrcCell;
}
}
if (targetWrap != null)
{
if (targetWrap.RawCell != null)
{
var style = workbook.CreateCellStyle();
style.CloneStyleFrom(targetWrap.RawCell.CellStyle);
rawCell.CellStyle = style;
rawCell.SetCellType(targetWrap.RawCell.CellType);
switch (targetWrap.RawCell.CellType)
{
case CellType.Unknown:
break;
case CellType.Numeric:
rawCell.SetCellValue(targetWrap.RawCell.NumericCellValue);
break;
case CellType.String:
rawCell.SetCellValue(targetWrap.RawCell.StringCellValue);
break;
case CellType.Formula:
rawCell.SetCellValue(targetWrap.RawCell.CellFormula);
break;
case CellType.Blank:
break;
case CellType.Boolean:
rawCell.SetCellValue(targetWrap.RawCell.BooleanCellValue);
break;
case CellType.Error:
break;
default:
rawCell.SetCellValue(targetWrap.Value);
break;
}
}
tableModified = true;
}
}
}
int index = 0;
if (isLeft)
{
foreach (var rowDiff in sheetDiff.Rows)
{
if (rowDiff.Value.LeftEmpty())
{
Debug.Print("ShiftBackLeft: " + rowDiff.ToString());
if (index + 1 < table.LastRowNum)
{
table.ShiftRows(index + 1, table.LastRowNum, -1);
}
}
else
{
index++;
}
}
}
else
{
foreach (var rowDiff in sheetDiff.Rows)
{
if (rowDiff.Value.RightEmpty())
{
Debug.Print("ShiftBackRight: " + rowDiff.ToString());
if (index + 1 < table.LastRowNum)
{
table.ShiftRows(index + 1, table.LastRowNum, -1);
}
}
else
{
index++;
}
}
}
if (tableModified)
{
using (FileStream stream = new FileStream(rawFilePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
}
}
