public static ExcelWorkbook Create(string path, ExcelSheetReadConfig config)
{
if (Path.GetExtension(path) == ".csv")
{
return(CreateFromCsv(path, config));
}
if (Path.GetExtension(path) == ".tsv")
{
return(CreateFromTsv(path, config));
}
var srcWb = WorkbookFactory.Create(path);
var wb = new ExcelWorkbook();
for (int i = 0; i < srcWb.NumberOfSheets; i++)
{
var srcSheet = srcWb.GetSheetAt(i);
wb.Sheets.Add(srcSheet.SheetName, ExcelSheet.Create(srcSheet, config));
}
return(wb);
}
public static ExcelWorkbook Create(string path, ExcelSheetReadConfig config, bool enableEdit = false)
{
if (Path.GetExtension(path) == ".csv")
{
return(CreateFromCsv(path, config));
}
if (Path.GetExtension(path) == ".tsv")
{
return(CreateFromTsv(path, config));
}
var wb = new ExcelWorkbook();
FileAccess access = enableEdit == true ? FileAccess.ReadWrite : FileAccess.Read;
if (Path.GetExtension(path) == ".xlsm" || Path.GetExtension(path) == ".xlsx" || Path.GetExtension(path) == ".tmp")
{
FileStream stream = new FileStream(path, FileMode.OpenOrCreate, access, FileShare.ReadWrite);
wb.srcWb = new XSSFWorkbook(stream);
}
else
{
wb.srcWb = WorkbookFactory.Create(path);
}
//wb.srcWb.Close();
wb.FilePath = path;
for (int i = 0; i < wb.srcWb.NumberOfSheets; i++)
{
var srcSheet = wb.srcWb.GetSheetAt(i);
wb.Sheets.Add(srcSheet.SheetName, ExcelSheet.Create(srcSheet, config));
}
return(wb);
}
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 ExcelSheetDiff(ExcelSheet src, ExcelSheet dst)
{
Rows = new SortedDictionary <int, ExcelRowDiff>();
_srcSheet = src;
_dstSheet = dst;
}
public ExcelSheetDiff(ExcelSheet srcSheet, ExcelSheet dstSheet)
{
SrcSheet = srcSheet;
DstSheet = dstSheet;
Rows = new SortedDictionary <int, ExcelRowDiff>();
}