Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Chart with multi-level labels on x-axis

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:

enter image description 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?

like image 289
Frederik Hansen Avatar asked Sep 29 '17 13:09

Frederik Hansen


People also ask

How do I add multiple labels to x-axis?

Add an axis title for a secondary axis For example, in a line chart, click one of the lines in the chart, and all the data marker of that data series become selected. Click Add Chart Element > Axis Titles > and select between Secondary Horizontal or Second Vertical.

Can you have multiple data labels in Excel chart?

On the Layout tab, in the Labels group, click Data Labels, and then click the option that you want. For additional data label options, click More Data Label Options, click Label Options if it's not selected, and then select the options that you want.


1 Answers

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";
like image 156
Frederik Hansen Avatar answered Sep 21 '22 12:09

Frederik Hansen