Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
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 1. April 2010 08:08

My step-by-step Excel 2007 VSTO add-in tutorial has been growing quite a bit, and Dennis Wallentin, in his great wisdom, suggested that a table of contents would be a useful addition. Here it is – I will update this page as I keep adding to the tutorial.

Part 1: displaying worksheets in a TreeView within a Custom Task Pane

This first part focuses on using a WPF TreeView hosted in a Custom Task Pane to display all the workbooks and worksheets that are currently open, and refreshing the contents based on the events exposed by Excel.

1. Getting Started
2. Using the Custom Task Pane
3. Using the Ribbon
4. Adding a WPF control
5. Displaying open workbooks & worksheets in a TreeView
6. Using Excel events to update the TreeView
7. Wrapping up part 1

Code sample for part 1

Part 2: finding differences between worksheets

This second part focuses on reading the contents of worksheets to identify their differences, and on building a WPF user interface in the Task Pane, using the MVVM pattern, to navigate between the differences and highlight them. 

8. Binding to Commands to navigate to Cells
9. Reading the values and formulas from worksheets
10. Use a WPF converter to display differences

Code sample for part 2

Part 3: deployment

11. Creating a basic msi installer

To be continued...

by Mathias 27. March 2010 11:18

Previous episodes

  1. Getting Started
  2. Using the Custom Task Pane
  3. Using the Ribbon
  4. Adding a WPF control
  5. Displaying open workbooks & worksheets in a TreeView
  6. Using Excel events to update the TreeView

Today is the day, we will finally close “chapter one” of these series, with some minor improvements of the tree view display of open workbooks and worksheets. The final result of our work looks like this, with a TreeView displaying all open workbooks and worksheets, refreshing its contents (quasi) automatically, and with some home-made icons just for kicks.

FullTreeView

Rather than a systematic walk-through, I will just explain the changes I implemented to the code base, which I have now posted on a dedicated Wiki page.

Download Anakin project code

More...

by Mathias 17. March 2010 10:10

Previous episodes

  1. Getting Started
  2. Using the Custom Task Pane
  3. Using the Ribbon
  4. Adding a WPF control
  5. Displaying open workbooks & worksheets in a TreeView

It’s time to wrap-up the first part of this tutorial, and hook up our tree view to Excel events, to update the contents of the tree when the user changes what is open.

We need to capture the following: we need to update the TreeView when the user

  • Opens, creates or closes a workbook
  • Adds or deletes a worksheet

Added Worksheets and Workbooks

Let’s start with adding a worksheet to a workbook. Excel exposes that event, through the Workbook.NewSheet event. We want the WorkbookViewModel to take care of his children, so we modify the constructor the following way:

internal WorkbookViewModel(Excel.Workbook workbook)
{
   this.workbook = workbook;
   workbook.NewSheet += new Excel.WorkbookEvents_NewSheetEventHandler(workbook_NewSheet);
   // no change here, code stays the same
}

void workbook_NewSheet(object newSheet)
{
   var worksheet = newSheet as Excel.Worksheet;
   if (worksheet != null)
   {
      var worksheetViewModel = new WorksheetViewModel(worksheet);
      this.worksheetViewModels.Add(worksheetViewModel);
   }
}

Creating event handlers can be a bit tedious; fortunately, Visual Studio simplifies the task quite a bit. When you type workbook.NewSheet +=, you should see a tooltip appear, which “suggests” an event handler. Type Tab, and Tab again – Visual Studio will create for you the empty event handler, with the right arguments and types, where you can now insert the logic of what should happen when the event is triggered.

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