Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 15. July 2010 12:14

Time to wrap this series on VSTO add-ins for Excel 2007. Now that we have a working application-level add-in, we want to deploy it on the user machine. There are two ways to do that: ClickOnce and Windows Installer. In this post, I will go over creating a basic installer using Windows installer with Visual Studio 2008. Very soon, we’ll have a VIP guest blogger who will tell you all you need to know about ClickOnce deployment and VSTO.

This post borrows heavily from the Microsoft white paper linked below, which is absolutely excellent. I mostly paraphrased it, focusing on the how and not the why. I strongly encourage you to go to the source and read it for more details:

Deploying a VSTO 3.0 Solution for Office 2007 System Using Windows Installer

The white paper comes with sample code, covering a few scenarios:

VSTO installer sample code 

Note: the following applies to Office 2007 projects. If your add-in needs to run on Excel 2003, you should follow this guidance instead: Deploying VSTO Solutions Using Windows Installer (Part 2 of 2)

Surgeon General Warning: prolonged reading of material pertaining to msi deployment can cause drowsiness or confusion; absolutely no risk whatsoever of euphoria is to be expected.

This post is not going to be sexy. My goal is to have a check-list of what to do to get your add-in to install correctly. The steps require no thinking, and are frankly rather boring. I find some steps pretty obscure, and recommend patience and soothing music; you may consider also having  some sacrificial offering ready to appease the Great Installer Voodoo deity (a nice chicken will usually do). 

Prepare the add-in

We will start from where we left off, with a working add-in (download the add-in here). Let’s first fill in the fields describing our assembly, by right-clicking on the project:

ClearLines.Anakin > Properties > Application > Assembly information:

AssemblyInfo

Next, let’s set the configuration to Release, so that we feed the optimized release version to the installer. Right-click on the Solution (not the add-in project), select Configuration Manager, and set ClearLines.Anakin to Release instead of Debug.

ReleaseMode

More...

by Mathias 26. May 2010 06:55

I finally got to reviewing and scrubbing the code for the part 2 of my Excel 2007 VSTO tutorial; you can download the code here. Next chapter, we will venture into the joys of deployment.

In the meanwhile, please feel free to let me know in the comments what you think, like and dislike, and how I can make this better!

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

Comments

Comment RSS