I'm creating a VSTO add-in that among other things is supposed to create a line chart for some annual data. This data contains datapoints on a weekly basis. I would like the horizontal axis to be grouped in months, illustrated here:
I can't however find anything in the VSTO documentations about whether this is possible or not. As far as I can tell a Series only takes a 1-dimensional array of values for the X-axis. Does anyone have any experience with this?
With help from the Microsoft Forums I figured out a solution (MSDN Thread).
For anyone else wondering how to do this, this is what I ended up with:
Chart chart = slide.Shapes.AddChart(XlChartType.xlLine).Chart;
Excel.Workbook wb = (Excel.Workbook) chart.ChartData.Workbook;
Excel.Worksheet ws = wb.Worksheets[1];
ws.ListObjects["Table1"].Resize(ws.Range["A1:C13"]);
ws.Range["B1"].Value = "X";
ws.Range["A2"].Value = "2015";
ws.Range["A3:A7"].ClearContents();
ws.Range["A8"].Value = "2016";
ws.Range["A9:A13"].ClearContents();
ws.Range["B2"].Value = "7/15/2015";
ws.Range["B3"].Value = "8/15/2015";
ws.Range["B2:B3"].AutoFill(Destination: ws.Range["B2:B13"]);
ws.Range["B2:B13"].NumberFormat = "d-mmm";
ws.Range["C1"].Value = "Y";
ws.Range["C2"].Value = "100";
ws.Range["C3"].Value = "150";
ws.Range["C2:C3"].AutoFill(Destination: ws.Range["C2:C13"]);
(chart.SeriesCollection(1) as Series).Delete();
Series series = chart.SeriesCollection(1) as Series;
series.Values = "=Sheet1!$C$2:$C$13";
series.XValues = "=Sheet1!$A$2:$B$13";
You're right, PowerPoint lacks this functionality.
The trick to creating a Chart with 2 series of x axis labels/ticks (multi-level category labels) is the layout of the data. You need to put the Years and Month values in different columns.
The problem is that while Excel supports Multi-level Category Labels, Powerpoint does not:
I was able to do it creating the chart in Excel and using the Clipboard object to copy it into PowerPoint.