public void RunTask(BackgroundWorker backgroundWorker)
{
try
{
int startingRow = _workbookController.ParseStartingRow(textBoxStartingRow.Text);
int numRows = _worksheet.UsedRange.Rows.Count;
int numCols = _worksheet.UsedRange.Columns.Count;
int numDataRows = _worksheet.UsedRange.Rows.Count - startingRow + 1;
double progress = 0;
double progressRowIncrementer = 100.0 / numDataRows;
List <ColumnAnalysis> columnsAnalysis = new List <ColumnAnalysis>();
// read the Excel file
for (int col = 1; !backgroundWorker.CancellationPending && col <= numCols; col++)
{
ColumnAnalysis ca = new ColumnAnalysis();
// the column address will appear in a format like $A:$A
ca.ColumnAddress = (string)_worksheet.Columns[col].Address;
ca.ColumnAddress = ca.ColumnAddress.Substring(1, ca.ColumnAddress.IndexOf(':') - 1);
// read the first row, which may be header text
object header = _worksheet.Cells[1, col].Value2;
if (header != null)
{
ca.HeaderText = header.ToString().Trim();
}
else
{
ca.HeaderText = String.Format("Column {0}", ca.ColumnAddress);
}
Excel.Range dataRange = _worksheet.Range[_worksheet.Cells[startingRow, col], _worksheet.Cells[numRows, col]];
object[,] data = dataRange.Value2;
if (data == null)
{
throw new Exception(Messages.ExcelNoData);
}
for (int row = 1; !backgroundWorker.CancellationPending && row <= numDataRows; row++)
{
object cell = data[row, 1];
if (cell != null)
{
ca.ValidValues++;
bool isNumeric = false;
double doubleValue = 0;
if (!ca.ContainsAlpha) // see if this value is a valid numeric
{
if (cell is double)
{
doubleValue = (double)cell;
isNumeric = true;
}
else if (cell is string)
{
isNumeric = Double.TryParse((string)cell, out doubleValue);
}
}
if (isNumeric)
{
if (ca.Values.Count < ColumnAnalysis.MaxValueSetValues && !ca.Values.Contains(doubleValue))
{
ca.Values.Add(doubleValue);
}
// assume a maximum of six digits after the decimal point
string formattedDouble = doubleValue.ToString("0.000000", CultureInfo.InvariantCulture);
formattedDouble = formattedDouble.TrimEnd('0');
int digitsBeforeDecimal = formattedDouble.IndexOf('.');
int digitsAfterDecimal = formattedDouble.Length - digitsBeforeDecimal - 1;
ca.DigitsBeforeDecimal = Math.Max(ca.DigitsBeforeDecimal, digitsBeforeDecimal);
ca.DigitsAfterDecimal = Math.Max(ca.DigitsAfterDecimal, digitsAfterDecimal);
ca.MaximumLength = Math.Max(ca.MaximumLength, digitsBeforeDecimal + digitsAfterDecimal + ((digitsAfterDecimal > 0) ? 1 : 0));
}
else
{
string sValue = (cell.ToString()).Trim();
ca.ContainsAlpha = true;
ca.MaximumLength = Math.Max(ca.MaximumLength, sValue.Length);
}
}
progress += progressRowIncrementer;
backgroundWorker.ReportProgress((int)progress);
}
if (ca.ValidValues != 0)
{
columnsAnalysis.Add(ca);
}
}
if (!backgroundWorker.CancellationPending)
{
_columnAnalysis = columnsAnalysis;
}
}
catch (Exception exception)
{
MessageBox.Show(String.Format(Messages.ExcelIOError, exception.Message));
}
}
private void CompareLists()
{
Task.Run(() =>
{
// perform list comparisons, this might take awhile.
int index = 0;
var results = new List <ComparisonResult>();
foreach (var list in LeftSharePointLists)
{
ComparisonResult result = new ComparisonResult
{
ListName = list.ListName,
ComparisonListName = RightSharePointLists[index].ListName,
ComparisonIndex = index
};
// extract out a list of each columns display name as an array of strings to compare.
var firstListColumns = list.ColumnDefinitions.Select(x => x.DisplayName);
var secondListColumns = RightSharePointLists[index].ColumnDefinitions.Select(x => x.DisplayName);
// We need to compare both ways incase of count differences
var firstListAnalysis = new ConcurrentBag <ColumnAnalysis>();
bool firstIdentical = true;
Parallel.ForEach(firstListColumns, item =>
{
ColumnAnalysis columnAnalysis = new ColumnAnalysis()
{
DisplayName = item
};
var exists = secondListColumns.Any(x => x == item);
if (exists)
{
columnAnalysis.Matched = true;
}
else
{
firstIdentical = false;
columnAnalysis.Matched = false;
}
firstListAnalysis.Add(columnAnalysis);
});
var secondListAnalysis = new ConcurrentBag <ColumnAnalysis>();
bool secondIdentical = true;
Parallel.ForEach(secondListColumns, item =>
{
ColumnAnalysis columnAnalysis = new ColumnAnalysis()
{
DisplayName = item
};
var exists = firstListColumns.Any(x => x == item);
if (exists)
{
columnAnalysis.Matched = true;
}
else
{
secondIdentical = false;
columnAnalysis.Matched = false;
}
secondListAnalysis.Add(columnAnalysis);
});
result.SecondListColumnAnalysis = secondListAnalysis;
result.FirstListColumnsAnalysis = firstListAnalysis;
result.Identical = firstIdentical && secondIdentical;
// let's do a view comparison too.
var viewsLeft = list.ViewDefinitions;
var viewsRight = RightSharePointLists[index].ViewDefinitions;
List <bool> matches = new List <bool>();
foreach (var view in viewsLeft)
{
foreach (var fieldRef in view.ViewFieldRefs)
{
var rightView = viewsRight.Find(x => x.ViewDisplayName == view.ViewDisplayName);
if (rightView == null)
{
break;
}
matches.Add(rightView.ViewFieldRefs.Any(x => x == fieldRef));
}
}
foreach (var view in viewsRight)
{
foreach (var fieldRef in view.ViewFieldRefs)
{
var leftView = viewsLeft.Find(x => x.ViewDisplayName == view.ViewDisplayName);
if (leftView == null)
{
break;
}
matches.Add(leftView.ViewFieldRefs.Any(x => x == fieldRef));
}
}
result.AreViewsIdentical = !matches.Any(x => x == false);
// collect our results and up the index count.
results.Add(result);
index++;
}
Dispatcher.Invoke(() =>
{
grdLoadingOverlay.Visibility = Visibility.Hidden;
// let's display our results
ComparisonResultsView comparisonResultsView = new ComparisonResultsView(results);
RootWindow.AddToMainContentView(comparisonResultsView);
});
});
}
public void RunTask(BackgroundWorker backgroundWorker)
{
try
{
int startingRow = _workbookController.ParseStartingRow(textBoxStartingRow.Text);
int numRows = _worksheet.UsedRange.Rows.Count;
int numCols = _worksheet.UsedRange.Columns.Count;
int numDataRows = _worksheet.UsedRange.Rows.Count - startingRow + 1;
double progress = 0;
double progressRowIncrementer = 100.0 / numDataRows;
List<ColumnAnalysis> columnsAnalysis = new List<ColumnAnalysis>();
// read the Excel file
for( int col = 1; !backgroundWorker.CancellationPending && col <= numCols; col++ )
{
ColumnAnalysis ca = new ColumnAnalysis();
// the column address will appear in a format like $A:$A
ca.ColumnAddress = (string)_worksheet.Columns[col].Address;
ca.ColumnAddress = ca.ColumnAddress.Substring(1,ca.ColumnAddress.IndexOf(':') - 1);
// read the first row, which may be header text
object header = _worksheet.Cells[1,col].Value2;
if( header != null )
ca.HeaderText = header.ToString().Trim();
else
ca.HeaderText = String.Format("Column {0}",ca.ColumnAddress);
Excel.Range dataRange = _worksheet.Range[_worksheet.Cells[startingRow,col],_worksheet.Cells[numRows,col]];
object[,] data = dataRange.Value2;
if( data == null )
throw new Exception(Messages.ExcelNoData);
for( int row = 1; !backgroundWorker.CancellationPending && row <= numDataRows; row++ )
{
object cell = data[row,1];
if( cell != null )
{
ca.ValidValues++;
bool isNumeric = false;
double doubleValue = 0;
if( !ca.ContainsAlpha ) // see if this value is a valid numeric
{
if( cell is double )
{
doubleValue = (double)cell;
isNumeric = true;
}
else if( cell is string )
isNumeric = Double.TryParse((string)cell,out doubleValue);
}
if( isNumeric )
{
if( ca.Values.Count < ColumnAnalysis.MaxValueSetValues && !ca.Values.Contains(doubleValue) )
ca.Values.Add(doubleValue);
// assume a maximum of six digits after the decimal point
string formattedDouble = doubleValue.ToString("0.000000",CultureInfo.InvariantCulture);
formattedDouble = formattedDouble.TrimEnd('0');
int digitsBeforeDecimal = formattedDouble.IndexOf('.');
int digitsAfterDecimal = formattedDouble.Length - digitsBeforeDecimal - 1;
ca.DigitsBeforeDecimal = Math.Max(ca.DigitsBeforeDecimal,digitsBeforeDecimal);
ca.DigitsAfterDecimal = Math.Max(ca.DigitsAfterDecimal,digitsAfterDecimal);
ca.MaximumLength = Math.Max(ca.MaximumLength,digitsBeforeDecimal + digitsAfterDecimal + ( ( digitsAfterDecimal > 0 ) ? 1 : 0 ));
}
else
{
string sValue = (cell.ToString()).Trim();
ca.ContainsAlpha = true;
ca.MaximumLength = Math.Max(ca.MaximumLength,sValue.Length);
}
}
progress += progressRowIncrementer;
backgroundWorker.ReportProgress((int)progress);
}
if( ca.ValidValues != 0 )
columnsAnalysis.Add(ca);
}
if( !backgroundWorker.CancellationPending )
_columnAnalysis = columnsAnalysis;
}
catch( Exception exception )
{
MessageBox.Show(String.Format(Messages.ExcelIOError,exception.Message));
}
}
