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;