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); } } }