Pierre-Emmanuel Dautreppe

We have seen here in a previous post how to set some values in cell. We'll see now how to format the cells.

The examples shown here are quite simple but as all - or so - properties methods take only objects and that the MSDN gives only sparingly the real type to use.

Note that for all the following examples and to focus on the different types we can use, I have defined two aliases :

How to set some basic properties :

How to set the width on a column :

Note that here, the width is given in points.

How to set the formatting display on a cell :

How to find the correct formatting cell ? Well you can see in Excel directly in the cell format dialog editor. In the "custom" tab you will see some examples of the string you can give.

On the personal VSTO project I currently work on, I was first working on sheets I had added at design time. I was thinking it would be simpler to be able to generate the worksheets at runtime depending of my needs.

To add a sheet, we can just do something like :

However we can note the following things

• The worksheets added at design time are Microsoft.Office.Tools.Excel.Worksheet
• The worksheets added at runtime are Microsoft.Office.Interop.Excel.Worksheet
• The add method :
  • expects only optional arguments
  • its two first arguments (Before and After) must be of type Interop.Excel.Worksheet
  • it will return an Interop.Excel.Worksheet
• As far as I have seen - after googling quite a lot - there is no way to convert a Tools.Excel.Worksheet to a Interop.Excel.Worksheet

However there are some way (not given or advised by the MSDN) to convert a Interop.Excel.Worksheet to a Tools.Excel.Worksheet (search GetExtendedWorksheet in google).

Having these two types and no built in conversion is driving me crazy... I think for now I will continue working with sheets at design time. If you have any advice, leave a comment !

Second article of the serie. Now I need to control a chart and to create some series.

Note that in my case, I have added a graph (empty) on a page, to be sure of the position and size (even if we can also control that by code). As a consequence, I will control the graph Globals.MySheet.MyGraph and I will store it in a variable chart.

How to add the source data of a graph ?

There is many way to add the source data of a graph. The simplest ? to use the SetSourceData method.

Here it's a little bit tricky to understand. In the first case, Excel will construct one serie to the graph. It will also try to extract the title from the data. How ? well it will probably check the type of the data to see, but it's a bit "magic". In the second case, it will construct two different series.

How to manipulate the series ?

The "simplest" and the most powerful way is to work directly with the series. You will note here the use of "System.Type.Missing". Indeed this is an optional parameter. When giving a string or an int, it will return a single Series. To get the wole collection, we have to omit this parameter.

How to manipulate the axis ? 

Since a few days, I'm working in Visual Studio 2008 and VSTO to update an Excel application.

As I have a mighty hard time to understand everything and find some information in the very lacunar documentation (See in MSDN), I have decided to so a serie of post, related to what I learn and discover in this domain.

How to access to your sheets ?

• By simply using the "this" keyword when working in a sheet
• By using the "Globals" class
  • Globals.Sheet1
  • Globals.Sheet2

How to set a value in a single cell ?

• Select the cell you want and set a value

How to set the same value in mutliple cells ?

• Select a range...
  • Example 1

    Excel.Range range = Globals.Sheet1.Range["D10", "E11"];

  • Example 2

    Excel.Range cell1 = (Excel.Range)Globals.Sheet1.Cells[10, 10];

    Excel.Range cell2 = (Excel.Range)Globals.Sheet1.Cells[12, 12];

    Excel.Range range = Globals.Sheet1.Range[cell1, cell2];

• ...and set

  range.Value2 = "Same value";

How to set a formula in a cell ?

Note that to set it in mutliple cell, you can first get a range as specified above.

How works the last example ? It's pretty simple in fact :

• R means "the same row"
• R[3] means the current row + 3 (if we are at row 10, so it will be row 13)
• R[-2] means the current row - 2 (if we are at row 10, so it will be row 8)
• C means "the current column"
• C[3] means the current column + 3 (if we are at column 10, so it will be column 13)
• C[-2] means the current column - 2 (if we are at column 10, so it will be column 8)

It was possible under Visual Source Safe, but not under TFS 2005.

It has been added in TFS 2008. How to use it ?

tf destroy itemspec

tf destroy $/MyTeamproject/MyItemToDestroy