155 lines
6.5 KiB
C#
155 lines
6.5 KiB
C#
// Copyright (c) 2022- schick Informatik
|
|
// Description: Compares excel files and highlights changes
|
|
//
|
|
|
|
using System;
|
|
using System.IO;
|
|
using ClosedXML.Excel;
|
|
|
|
namespace ENI2.Excel
|
|
{
|
|
|
|
/// <summary>
|
|
/// Diese Klasse beinhaltet den von Anmeldungen unabhängigen Vergleich von Excel Sheets. Damit
|
|
/// können dann Updates von "außen" einfacher abgearbeitet werden. Zellen und sheets werden bei
|
|
/// Aktualisierung bunt eingefärbt
|
|
/// </summary>
|
|
public static class ExcelComparer
|
|
{
|
|
private static readonly XLColor diffColor = XLColor.FromArgb(150, 150, 255); // blue
|
|
|
|
private static bool GetSheetRange(IXLWorksheet sheet, out int lastUsedRow, out int lastUsedColumn)
|
|
{
|
|
try
|
|
{
|
|
var usedRange = sheet.RangeUsed();
|
|
if (usedRange != null)
|
|
{
|
|
lastUsedRow = usedRange.RowCount();
|
|
lastUsedColumn = usedRange.ColumnCount();
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
lastUsedColumn = 0;
|
|
lastUsedRow = 0;
|
|
return false;
|
|
}
|
|
}
|
|
catch (Exception)
|
|
{
|
|
lastUsedColumn = 0;
|
|
lastUsedRow = 0;
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public static string Compare(string sourcePath, string targetPath, string comparisonFileName, out string errorMessage)
|
|
{
|
|
errorMessage = "";
|
|
int counter = 0;
|
|
|
|
try
|
|
{
|
|
File.Copy(targetPath, comparisonFileName, true);
|
|
|
|
using (var sourceWorkbook = new XLWorkbook(sourcePath))
|
|
using (var comparisonWorkbook = new XLWorkbook(comparisonFileName))
|
|
{
|
|
// Es werden Zellen der "used range" miteinander verglichen
|
|
foreach (var sourceSheet in sourceWorkbook.Worksheets)
|
|
{
|
|
IXLWorksheet targetSheet = null;
|
|
foreach (var sheet in comparisonWorkbook.Worksheets)
|
|
{
|
|
if (sourceSheet.Name.Equals(sheet.Name))
|
|
{
|
|
targetSheet = sheet;
|
|
break;
|
|
}
|
|
}
|
|
|
|
if (targetSheet == null) continue;
|
|
System.Diagnostics.Trace.WriteLine(string.Format("Processing sheet {0}", targetSheet.Name));
|
|
|
|
if (GetSheetRange(sourceSheet, out int sourceRows, out int sourceCols) && GetSheetRange(targetSheet, out int targetRows, out int targetCols))
|
|
{
|
|
int maxRows = Math.Max(sourceRows, targetRows);
|
|
int maxCols = Math.Max(sourceCols, targetCols);
|
|
|
|
for (int rowidx = 1; rowidx <= maxRows; rowidx++)
|
|
{
|
|
for (int colidx = 1; colidx <= maxCols; colidx++)
|
|
{
|
|
string sourceText = null;
|
|
string targetText = null;
|
|
|
|
// Get source cell value
|
|
if (rowidx <= sourceRows && colidx <= sourceCols)
|
|
{
|
|
var sourceCell = sourceSheet.Cell(rowidx, colidx);
|
|
if (!sourceCell.IsEmpty())
|
|
{
|
|
sourceText = sourceCell.GetString();
|
|
}
|
|
}
|
|
|
|
// Get target cell value
|
|
if (rowidx <= targetRows && colidx <= targetCols)
|
|
{
|
|
var targetCell = targetSheet.Cell(rowidx, colidx);
|
|
if (!targetCell.IsEmpty())
|
|
{
|
|
targetText = targetCell.GetString();
|
|
}
|
|
}
|
|
|
|
if (sourceText == null)
|
|
{
|
|
if (targetText != null)
|
|
{
|
|
var cellToHighlight = targetSheet.Cell(rowidx, colidx);
|
|
cellToHighlight.Style.Fill.BackgroundColor = diffColor;
|
|
counter++;
|
|
}
|
|
}
|
|
else if (targetText == null)
|
|
{
|
|
if (sourceText != null)
|
|
{
|
|
var cellToHighlight = targetSheet.Cell(rowidx, colidx);
|
|
cellToHighlight.Style.Fill.BackgroundColor = diffColor;
|
|
counter++;
|
|
}
|
|
}
|
|
else if ((sourceText != null) && (targetText != null))
|
|
{
|
|
if (!sourceText.Equals(targetText))
|
|
{
|
|
var cellToHighlight = targetSheet.Cell(rowidx, colidx);
|
|
cellToHighlight.Style.Fill.BackgroundColor = diffColor;
|
|
counter++;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
errorMessage = "failed to get sheet ranges";
|
|
}
|
|
}
|
|
|
|
comparisonWorkbook.SaveAs(comparisonFileName);
|
|
}
|
|
errorMessage = string.Format("{0} differences found", counter);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
errorMessage = ex.Message;
|
|
}
|
|
|
|
return comparisonFileName;
|
|
}
|
|
}
|
|
} |