public void AddCell(ExcelCell newCell)
{
for (int i = 0; i < Cells.Count; i++)
{
if (Cells[i].OriginalColumnIndex == newCell.OriginalColumnIndex)
{
Cells[i].SetValue(newCell.Value);
return;
}
}
if (Cells.Count <= newCell.OriginalColumnIndex)
{
for (int i = 0; i < newCell.OriginalColumnIndex + 1; i++)
{
if (i >= Cells.Count)
{
Cells.Add(new ExcelCell(string.Empty, i, newCell.OriginalRowIndex));
}
if (i == newCell.OriginalColumnIndex)
{
Cells[i].SetValue(newCell.Value);
return;
}
}
}
}
public ExcelCellDiff CreateCell(ExcelCell src, ExcelCell dst, int columnIndex, ExcelCellStatus status)
{
var cell = new ExcelCellDiff(columnIndex, Index, src, dst, status);
Cells.Add(cell.ColumnIndex, cell);
return(cell);
}
public ExcelCellDiff(int columnIndex, int rowIndex, ExcelCell src, ExcelCell dst, ExcelCellStatus status)
{
ColumnIndex = columnIndex;
RowIndex = rowIndex;
SrcCell = src;
DstCell = dst;
Status = status;
}
public void ReplaceCell(int row, int column, ExcelCell cell)
{
if (Rows.Count < row)
{
return;
}
Rows[row].AddCell(cell);
}
public void ReplaceCell(int row, int column, ExcelCell cell)
{
if (Rows.Count < row)
{
return;
}
foreach (var _cell in Rows[row].Cells)
{
if (_cell.Value.ColumnIndex == column)
{
_cell.Value.DstCell.SetValue(cell.Value);
return;
}
}
}
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++;
}
}
private static IEnumerable <Tuple <ExcelCell, ExcelCell> > EqualizeColumnCount(
IEnumerable <ExcelCell> srcCells, IEnumerable <ExcelCell> dstCells, Dictionary <int, ExcelColumnStatus> columnStausMap)
{
var srcQueue = new Queue <ExcelCell>(srcCells);
var dstQueue = new Queue <ExcelCell>(dstCells);
foreach (var status in columnStausMap)
{
ExcelCell src = null;
ExcelCell dst = null;
if (srcQueue.Any())
{
src = srcQueue.Dequeue();
}
if (dstQueue.Any())
{
dst = dstQueue.Dequeue();
}
yield return(Tuple.Create(src, dst));
}
}
// 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);
}
}
}