Archive for the ‘VSTO’ Category

Deploying C# Setup.exe with .Net 3.5 Redistributable

March 4, 2010

Its annoying for end-users when they have to download the .Net 3.5 Framework via Microsofts site when installing your applications:( So why not deploy your app with the .Net framework included!

Here are the steps to add a Setup project to your VS2008 application packaged with a .Net 3.5 installer.
1. Create a new C# .Net 3.5 project (in my case a Visual C#/Windows/Windows Forms Application).

2. Right click the project title (not the solution title), and go to Properties | Publish | Prerequisites.

  • You will see that Windows Installer and .Net 3.5 are selected as default.
  • Now, check ‘Download prerequisites from same location as my application‘.
  • This avoids the end-user have the massive .Net 3.5 Framework internet based download.
  • Make sure the ‘Create setup program to install prerequisites’ at the top of the window is checked.

3. Right click on the Solution title and go to Add | New Project | Other Project Types | Setup & Deployment. Choose Setup Wizard and then OK.

  • In the setup wizard click Next, choose the 1st option in (VS 2008), ‘Create a setup for a windows application’.
  • Next, choose the project outputs. For this example ‘Primary output from myApp’ will suffice.
  • Next, add a readme file if you want (I did not want), and click Next or Finish.

4. Right click the Setup project title (Setup3pt5 in my case, see pic below), go to Properties | Prerequisites.

  • Check ‘Download prerequisites from same location as my application’.

Setup Prerequisites

Steps 5 – 10….. (more…)


Automatically create Getter Setter in Visual Studio

January 5, 2010

private string directory;

This is your member variable. Highlight and right click on ‘directory’, then choose Refactor/Encapsulate Field.

Produces the following code automatically:

public string Directory
get { return directory; }
set { directory = value; }

Reading a File to a String in C#, including Tabs and Newlines etc.

January 5, 2010

StreamReader sr = new StreamReader(fileName);
fileAsString= sr.ReadToEnd();

This is very handy if you want to compare a string that is to be written to a file, with a file that already exists.
Used it for some unit testing.

Closing Excel in C#: always one instance left in task manager

January 5, 2010

There are loads of forum posts on trying to close Excel from within C#. In my case, when opening a number of workbooks, no matter how I tried, there was always one Excel instance left open in the Task Manager, even if I specificially used .quit or any other of the 10’s of methods I tried. This is how I finally solved the problem:

With just one excelApp instance:
Excel.Application excelApp = new Excel.Application();

Search through the Excel files and open the ones you want:

workbook = excelApp.Workbooks.Open(name, false, true, missing etc);

Make sure to close the workbook when finished with it:
//end the workbook session, checking if has been assigned
//a workbook object successfully first
if (workbook != null)
workbook.Close(false, Type.Missing, Type.Missing);

Finally, Kill the excel app. I dont use the word Kill lightly, because it was a
very annoying problem:


Done and dusted.

IntereString: String Vs string, == or .Equals, @, Stringbuilder

November 19, 2009

A lot of my C# development time involves working with and manipulating strings (a task common to most programmers). Here are some short answers to a few issues/questions I came accross:

String vs string
Lower case string is simply an alias for System.String in the .NET Framework.

== or .Equals
A rule of thumb is that for almost all reference types e.g. objects, use Equals when you want to test equality. == compares whether two references (i.e. object instances) refer to the same object.
Primitive types can be compared for equality using ==, but in the case of strings, the variables on each side of the operators must be of type string.
Have a look at the following code and output, taken from Jon Skeet’s blog.

using System;

public class Test
	static void Main()
        // Create two equal but distinct strings
        string a = new string(new char[] {'h', 'e', 'l', 'l', 'o'});
        string b = new string(new char[] {'h', 'e', 'l', 'l', 'o'});
        Console.WriteLine (a==b);
        Console.WriteLine (a.Equals(b));
        // Now let's see what happens with the same tests but
        // with variables of type object
        object c = a;
        object d = b;
        Console.WriteLine (c==d);
        Console.WriteLine (c.Equals(d));
The results are: 


@ before a string
Using @ before a string means the escape sequences are not processed. This making it easier to write fully qualified path names for example.
@”c:\Docs\Source\a.txt” // rather than “c:\\Docs\\Source\\a.txt”

String objects are immutable, and so editing them, using Replace for example, results in a entirely new string being created, with new memory allocation. The original string is left intact in memory.
The StringBuilder class is designed for situations when one needs to work with a single string and make an arbitrary number of iterative changes to it. New memory allocation is not needed.

Hope this helps, cos it helped me!

C# to Excel

November 17, 2009

There are a number of ways to move data between C# and Excel. I recently came
accross the following tutorials.

The first using an ODBC connection to populate a DataSet.

The second using an OleDb connection without having to use the Excel COM Object.

A more complete list of ‘Excel to C#’ methods is available from the microsoft support site:

  • Transfer data cell by cell.
  • Transfer data in an array to a range of cells.
  • Transfer data in an ADO recordset to a range of cells by using the CopyFromRecordset method.
  • Create a QueryTable object on an Excel worksheet that contains the result of a query on an ODBC or OLEDB data source.
  • Transfer data to the clipboard, and then paste the clipboard contents into an Excel worksheet
  • Unit Testing in C# –

    November 11, 2009

    I was using NUnit, but it had to be run from the command line or
    as an external .exe.

    Just installed and its much handier. Your tests
    can be run by simply right clicking the project and going to Run Tests.

    Code Snippets in Visual Studio

    November 11, 2009

    Handy for getting at reusable code. Shortcut key in VS2008, Ctrl + k then Ctrl + x.

    Heres a good editor for creating your own snippets:

    Named Ranges – research paper at EuSpRIG 2009

    September 11, 2009

    Co authored a paper recently, “An Exploratory Analysis of the Impact of Named Ranges on the Debugging Performance of Novice Users” ( which presents evidence that might call into question the unsupported contention that Named Ranges are a good idea.

    The paper is discussed in an industrial context by the good people at Financial Mechanics:

    Reading Named Ranges in Excel using C#

    September 1, 2009

    Couldnt find any good examples of reading Named Ranges from a workbook, but eventually figured it out. Here some of the code.

    private Excel.Workbook m_workbook;
    object missing = Type.Missing;
            public void testNamedRangeFind()
                m_workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
                int i = m_workbook.Names.Count;
                string address = "";
                string sheetName = "";
                if (i != 0)
                    foreach (Excel.Name name in m_workbook.Names)
                        string value = name.Value;
                        //Sheet and Cell e.g. =Sheet1!$A$1 or =#REF!#REF! if refers to nothing
                        string linkName = name.Name;
                        //gives the name of the link e.g. sales
                        if (value != "=#REF!#REF!")
                            address = name.RefersToRange.Cells.get_Address(true, true, Excel.XlReferenceStyle.xlA1, missing, missing);
                            sheetName = name.RefersToRange.Cells.Worksheet.Name;
                        Debug.WriteLine("" + value + ", " + linkName + " ," + address + ", " + sheetName);

    The following references are included:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    using System.Diagnostics;
    using System.Windows.Forms;
    using System.IO;