If you dynamically add worksheets, you may need to delete them also. How to do that ? Simple. You can write a simple function as :
public static void DeleteWorksheets(IExcel.Sheets worksheets, params string[] excludedNames)
{
for ( int i = worksheets.Count; i > 0; i-- )
{
IExcel.Worksheet sheet = worksheets[i] as IExcel.Worksheet;
if ( !excludedNames.Contains(sheet.Name) )
sheet.Delete();
}
}
How to use it ? Well if you are in a sheet code and that you want to delete all the sheets except the current one, you can do :
ExcelUtils.DeleteWorksheets(Globals.ThisWorkbook.Worksheets, this.Name);
Almost good. You will simply notice that each time Excel wants to delete a sheet, you will get a popup asking to confirm the deletion. How to bypass it ? Let's modify our function as follows :
public static void DeleteWorksheets(IExcel.Sheets worksheets, params string[] excludedNames)
{
bool displayAlerts = worksheets.Application.DisplayAlerts;
try
{
worksheets.Application.DisplayAlerts = false;
for ( int i = worksheets.Count; i > 0; i-- )
{
IExcel.Worksheet sheet = worksheets[i] as IExcel.Worksheet;
if ( !excludedNames.Contains(sheet.Name) )
sheet.Delete();
}
}
finally
{
worksheets.Application.DisplayAlerts = displayAlerts;
}
}
And here we are !
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 :
using TExcel = Microsoft.Office.Tools.Excel;
using IExcel = Microsoft.Office.Interop.Excel;
How to set some basic properties :
cell.Font.Bold = true;
cell.HorizontalAlignment = IExcel.XlHAlign.xlHAlignCenter;
cell.VerticalAlignment = IExcel.XlVAlign.xlVAlignCenter;
cell.WrapText = true;
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.
cell.NumberFormat = "0.00";
cell.NumberFormat = "#.##0.00";
cell.NumberFormat = "dd/MM/yyyy";
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 :
Globals.ThisWorkbook.Worksheets.Add(System.Type.Missing, System.Type.Missing, 1, XlSheetType.xlWorksheet);
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.
Microsoft.Office.Tools.Excel.Chart chart = Globals.MySheet.MyChart;
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.
chart.SetSourceData(sheet.Range["B1", "B4"], XlRowCol.xlColumns);
chart.SetSourceData(sheet.Range["B1", "C4"], XlRowCol.xlColumns);
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.
SeriesCollection series = chart.SeriesCollection(System.Type.Missing) as SeriesCollection;
Series serie = series.Add(sheet.Range["B2", "B4"], XlRowCol.xlColumns, false, false, false);
serie.XValues = sheet.Range["A2", "A4"];
serie.Name = "Name of my first serie";
serie = series.Add(sheet.Range["C2", "C4"], XlRowCol.xlColumns, false, false, false);
serie.XValues = sheet.Range["A2", "A4"];
serie.AxisGroup = XlAxisGroup.xlSecondary;
serie.Name = "Name of my second serie";
How to manipulate the axis ?
Excel.Axis axis = (Excel.Axis)chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "My left axis title";
axis.AxisTitle.Top = chart.PlotArea.Top;
axis = (Excel.Axis)chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlSecondary);
axis.HasTitle = true;
axis.AxisTitle.Text = "My right axis title";
axis.AxisTitle.Top = chart.PlotArea.Top;
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
Globals.Sheet1.Cells[10, 10] = "Hello world";
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.
Globals.Sheet1.Range["A15", "A15"].Formula = "=15";
Globals.Sheet1.Range["A16", "A16"].Formula = "=15*2";
Globals.Sheet1.Range["A17", "A17"].Formula = "=R[-2]C+R[-1]C";
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)