Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 6. May 2010 09:27

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:

 DifferenceDisplay

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...

by Mathias 26. April 2010 08:32

Today we will write code that reads the contents (values & formulas) of the two selected worksheets, generates a list of their differences, and feeds it to the view that displays the comparison. The code isn’t particularly complicated, and uses mostly ideas which have been seen in the previous posts, so rather than break this into multiple small posts, I chose to bite the bullet and get done with a large chunk, all at once. I hope you survive it and bear with me – promise, we are almost there!

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

Extracting the comparison from the comparison ViewModel

In the previous installment, I created a stub to provide a “canned” list of differences to the ComparisonViewModel, so that we would have something to display. It’s time to replace that stub, and generate a real comparison between 2 worksheets. We could add the required code inside the existing classes; however, we will likely have a good amount of logic, so to avoid clutter, and to keep our design tight, we will extract that responsibility into its own class, the WorksheetsComparer.

First, let’s create a stub for the class, in the Comparison folder; its key method will be FindDifferences, and we will temporarily move the “fake” comparison that was in the ComparisonViewModel into that method:

public class WorksheetsComparer
{
   public static List<Difference> FindDifferences(Excel.Worksheet firstSheet, Excel.Worksheet secondSheet)
   {
      // Temporary code
      var difference1 = new Difference() { Row = 3, Column = 3 };
      var difference2 = new Difference() { Row = 3, Column = 5 };
      var difference3 = new Difference() { Row = 5, Column = 8 };
      
      var differences = new List<Difference>();
      differences.Add(difference1);
      differences.Add(difference2);
      differences.Add(difference3);

      return differences;
   }
}

Now we need to hook it up to the add-in. We will add a button to the AnakinView, which, when clicked, will call WorksheetsComparer.FindDifferences, and pass the result to the ComparisonViewModel. Let’s first plug a button in the AnakinView, and bind it to a Command on the ViewModel, GenerateComparison:

<StackPanel Margin="5">
  <Comparison:ComparisonView x:Name="ComparisonView" />
  <Button Command="{Binding GenerateComparison}" 
          Content="Compare" Width="75" Height="25" Margin="5"/>

Following the same approach as in the previous post, we implement the command in the View Model (only added code is displayed):

public class AnakinViewModel
{
   private ICommand generateComparison;

   public ICommand GenerateComparison
   {
      get
      {
         if (this.generateComparison == null)
         {
            this.generateComparison = new RelayCommand(GenerateComparisonExecute);
         }
         return this.generateComparison;
      }
   }

   private void GenerateComparisonExecute(object target)
   {
      var differences = WorksheetsComparer.FindDifferences(null, null);
      this.comparisonViewModel.SetDifferences(differences);
   }
}

More...

by Mathias 16. April 2010 11:03

I am pretty excited that the new Silverlight Toolkit now supports Stacked Series. If you develop business applications, at some point or another, you will have to produce charts. A while back I looked into the WPF / Silverlight toolkit, which offers charting capabilities, and I really liked it, because its design supports mvvm-style databinding pretty well. There was one major drawback, though: it didn’t include any stacked series. The point of reference for most business users is Excel charts, and this left a large chunk of the standard charts out. Problem solved with the last release: the stacked histogram and some of his friends are in! The Excel “Outdoors Bar” type is still not included, but I don’t think anyone will complain. I can’t wait to play with this release.

by Mathias 14. April 2010 12:02

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...

by Mathias 8. March 2010 12:50

Previous episodes

  1. Getting Started
  2. Using the Custom Task Pane
  3. Using the Ribbon
  4. Adding a WPF control

The shell of our control is ready – today we will fill the TreeView with all the open workbooks and worksheets. We will use a common design pattern in WPF: we will create objects that act as intermediary between the user interface and the domain objects. This approach is know as MVVM (Model-View-ViewModel) in WPF, and is a variation on the classic Model-View-Presenter pattern – the main difference being that MVVM relies heavily on the data binding capabilities of WPF.

As usual, Josh Smith has some great material on how to use the WPF TreeView, which is highly recommended reading – and was a life-saver in figuring out how things work.

In a first step, we will fill in the TreeView with fake data, and once the UI “works”, we will hook up the objects to retrieve real data from Excel.

To quote Josh Smith, “the WinForms TreeView control is not really providing a “view” of a tree: it is the tree”, whereas “the TreeView in our WPF programs to literally provide a view of a tree”, to which we want to bind. In our case, the tree we want to represent is that Excel has a collection of Workbook objects, which each has a collection of Worksheet objects. Let’s build that structure.

More...

Comments

Comment RSS