Today’s post will be much lighter than the previous episode: we will display detailed information about the differences that have been found on the ComparisonView control. To do this, we will bind properties of the Difference to the control, and use a WPF Value Converter to dynamically format the control.

You can find the complete list of episodes, and a link to the source code of the Excel VSTO add-in tutorial here.

Displaying how the cells differ

The add-in tracks cells where either the values or the formulas are different. We will simply display “side-by-side” the values and formulas of the cell that is being compared in the ComparisonView control:

To do this we need to provide a way to access the value and formula of each side of the comparison, so first we add the following properties to the Difference class:

public class Difference
{
   public string OriginalValue
   {
      get; set;
   }

   public string OtherValue
   {
      get; set;
   }

   public string OriginalFormula
   {
      get; set;
   }

   public string OtherFormula
   {
      get; set;
   }

More...

You can find the complete list of episodes, and a link to the source code of the Excel VSTO add-in tutorial here.

In the second part of this series, we will generate a comparison between two open spreadsheets, and create a user interface to navigate between the differences, and reconcile them if need be.

For today, let’s assume that the add-in has already figured out what the differences are, and build first a user interface that allows the user to navigate to the cells that have differences. In later posts, we will focus on actually generating these differences.

To achieve this, I will leverage the Goto method of Excel. Application.Goto(object reference, object scroll) will navigate to the range defined by reference. If scroll is set to true, it will force the window to scroll so that the range is the upper-left corner, otherwise it will scroll only if necessary.

This is a good starting point for design. A spreadsheet comparison will be a collection of differences, and each difference should map to a cell. One approach is to store the row and column of each difference, so that from a Difference object, we can retrieve the corresponding range, and go to it:

private void NavigateToDifference(Difference difference)
{
   var row = difference.Row;
   var column = difference.Column;
   var activeSheet = (Excel.Worksheet)this.excel.ActiveSheet;
   var differenceLocation = activeSheet.Cells[row, column];
   this.excel.Goto(differenceLocation, Type.Missing);
}

More...