Ejemplo n.º 1
0
        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;
                }
            }
        }
Ejemplo n.º 2
0
        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;
                }
            }
        }
Ejemplo n.º 3
0
        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++;
            }
        }
Ejemplo n.º 4
0
        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++;
            }
        }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
        // 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);
                }
            }
        }