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)); } }