.NET Slides line chart with a horizontal date axis

Aspose.Slides Product Family
1

I’m using .NET slides to create a line chart where the horizontal axis is a date range. When the powerpoint file is created the format of the axis labels is always plain text so I cannot apply min/max limitations or spacing.

In my example below the chart output may look correct at first as there are only 3 days which will each display as a category, but when I expand this to show a whole years worth of data on 1 chart and I want to group the category labels by month, it does nothing due to the fact this axis is a text type not a date type.
If I manually edit the created powerpoint file after creation, I can right click the chart, select edit data, highlight the column with my dates in and format the cells as Dates. This then gives me the axis format options I need, but is there a way to program this step using aspose?
I making an assumption here but maybe I need to do something with the workbook.GetCell function to not only write the data to the cell but also format it as a date? As the data parameter is of type object, I have also tried inserting DateTime’s instead of date strings but I couldn’t get this to work either.

Many thanks

Sample code:

</font></div><div><font face="Courier New" size="2">var chart = slide.Shapes.AddChart(ChartType.Line, x, y, width, height, false);</font></div><div><font face="Courier New" size="2">var workbook = chart.ChartData.ChartDataWorkbook;</font></div></div><div><font face="Courier New" size="2"><br></font></div><div><font face="Courier New" size="2">chart.ChartData.Categories.Add(workbook.GetCell(0, 1, 0, "01/01/2000"));</font></div><div><font face="Courier New" size="2">chart.ChartData.Categories.Add(workbook.GetCell(0, 2, 0, "02/01/2000"));</font></div><div><font face="Courier New" size="2">chart.ChartData.Categories.Add(workbook.GetCell(0, 3, 0, "03/01/2000"));</font></div><div><font face="Courier New" size="2">// TODO: Add more dates</font></div><div><font face="Courier New" size="2"><br></font></div><div><font face="Courier New" size="2">var series = chart.ChartData.Series.Add(workbook.GetCell(0, 0, 1, "SeriesA"), ChartType.Line);</font></div><div><font face="Courier New" size="2">series.DataPoints.AddDataPointForLineSeries(workbook.GetCell(0, 1, 1, 10));</font></div><div><font face="Courier New" size="2">series.DataPoints.AddDataPointForLineSeries(workbook.GetCell(0, 2, 1, 11));</font></div><div><font face="Courier New" size="2">series.DataPoints.AddDataPointForLineSeries(workbook.GetCell(0, 3, 1, 9));</font></div><div><font face="Courier New" size="2">// TODO: Add more data points</font></div><div><font face="Courier New" size="2"><br></font></div><div><div><font face="Courier New" size="2">// Format axis</font></div><div><font face="Courier New" size="2">chart.Axes.HorizontalAxis.MajorUnitScale = TimeUnitType.Months;</font></div><div><font face="Courier New" size="2">chart.Axes.HorizontalAxis.MajorUnit = 1;</font></div><div><font face="Courier New" size="2">chart.Axes.HorizontalAxis.BaseUnitScale = TimeUnitType.Days;</font></div></div><div><font face="Courier New" size="2">
2

Slight update:

I’ve got it a bit closer so I don’t have to format the cells, but I’m still one step away. By converting the DateTime to an OADate I can use NumberFormat to make it display correctly and powerpoint does recognise that it can be date. So the generated powerpoint file still has the axis type set to text initially, but if I manually change it to date then it works, so I just need to know how to program this?

</font></div><div><font face="Courier New" size="2">// adding categories</font></div><div><font face="Courier New" size="2">chart.ChartData.Categories.Add(workbook.GetCell(0, 1, 0, new DateTime(2000,1,1).ToOADate()));</font></div><div><font face="Courier New" size="2">... etc</font></div><div><font face="Courier New" size="2"><br></font></div><div><div><font face="Courier New" size="2">chart.Axes.HorizontalAxis.NumberFormat = "dd MM yyyy";</font></div><div><font face="Courier New" size="2">chart.Axes.HorizontalAxis.IsNumberFormatLinkedToSource = false;</font></div></div><div><font face="Courier New" size="2">
3

Hi Matthew,


Thanks for inquiring Aspose.Slides.

I have observed the requirement shared by you and it seems that you are looking for setting the date format as described in attached Desired.png. If this is the requirement then I have made some changes in your sample code which is shared below for your kind reference. I have also shared the generated presentation as well. One important point to mention here is that you can copy the desired date format using PowerPoint and set that in NumberFormat using Aspose.Slides as I have done. The Number format that you have set in your code is not a preset but a custom one. Therefore, it was getting set as custom. I hope this will be helpful.

public static void testChartLab()
{
Presentation pres = new Presentation();
ISlide slide = pres.Slides[0];
var chart = slide.Shapes.AddChart(ChartType.Line, 10, 10, 400, 400, false);
var workbook = chart.ChartData.ChartDataWorkbook;


// adding categories
chart.ChartData.Categories.Add(workbook.GetCell(0, 1, 0, new DateTime(2000,1,1).ToOADate()));
chart.ChartData.Categories.Add(workbook.GetCell(0, 2, 0, new DateTime(2000, 1, 2).ToOADate()));
chart.ChartData.Categories.Add(workbook.GetCell(0, 3, 0, new DateTime(2000, 1, 3).ToOADate()));

// chart.Axes.HorizontalAxis.NumberFormat = “dd MM yyyy”;
chart.Axes.HorizontalAxis.NumberFormat = “m/d/yyyy”;

chart.Axes.HorizontalAxis.IsNumberFormatLinkedToSource = false;

// TODO: Add more dates

var series = chart.ChartData.Series.Add(workbook.GetCell(0, 0, 1, “SeriesA”), ChartType.Line);
series.DataPoints.AddDataPointForLineSeries(workbook.GetCel
l(0, 1, 1, 10));
series.DataPoints.AddDataPointForLineSeries(workbook.GetCell(0, 2, 1, 11));
series.DataPoints.AddDataPointForLineSeries(workbook.GetCell(0, 3, 1, 9));
// TODO: Add more data points


// Format axis
chart.Axes.HorizontalAxis.MajorUnitScale = TimeUnitType.Months;
chart.Axes.HorizontalAxis.MajorUnit = 1;
chart.Axes.HorizontalAxis.BaseUnitScale = TimeUnitType.Days;

pres.Save(@“C:\Presentations\TestChart3.pptx”,SaveFormat.Pptx);
}

Pleas share, if I may help you further in this regard.

Many Thanks,
4

Hi Mudassir


Thankyou for your response. Sorry if I did not explain my issue well but its not the format of the date string that is my issue. This time I have added attachments for my problem which will hopefully be more helpful.

I also notice from your screenshot that I am using a different version of powerpoint to you so I hope this is not the issue but I am working with powerpoint 2013.

When I have lots of dates along the axis the default output can automatically space them out but the included date labels are not at nice intervals - see GeneratedChartProblem.png. (Please not I have also formatted them at an angle so they do not overlap)

What I want to achieve is to space the labels out by periods of months so that the included labels are always the first of the month - see ManuallyFixedProblem.png

The issue is that although you have the properties

chart.Axes.HorizontalAxis.MajorUnitScale = TimeUnitType.Months;
chart.Axes.HorizontalAxis.MajorUnit = 1;

until you change the axis format from text to date (highlighted by the red circles in the 2 png’s) these properties are ignored by powerpoint.

What I would like to know is if there is a way to make this change, or if I need to do anything different to the setup of my data if this is supposed to automatically realise its dealing with dates.

Many thanks
5

Hi Matthew,


Thank you for explaining.

I have observed your comments and worked over your requirements. I would like to share with you that the desired results can not be achieved unless Axis type is changed from “Text Axis” to “Date Axis” which is a missing feature in Aspose.Slides. I have logged a feature request with ticket ID SLIDESNET-36904 in our issue management system for further investigation and resolution of the issue. This thread has been linked with the issue so that you may be automatically notified as soon as the issue will be fixed.

We are sorry for you inconvenience,
6

Hi Muhammad


Thankyou for getting back to me on this and making a feature request. Having a date axis on a line graph is a key requirement for our use of your product.

For info for anyone else reading this thread we have had to implement a workaround where we clone a template chart from a basic powerpoint slide that already has the axis format set correctly. We can then build up the rest of the chart data and properties around it.

var templatePresentation = new Presentation(“LineChartWithDateAxisTemplate.pptx”);
var chartTemplate = templatePresentation.Slides[0].Shapes[0];
templatePresentation.Dispose();
var chart = (Chart)slide.Shapes.AddClone(chartTemplate, x, y, width, height);

By doing this we are taking a performance hit of nearly 2 seconds so I look forward to hearing if you resolve this issue in future.

Regards
7

Hi Matthew,


Thank you for sharing your findings.

I have observed your comments and like to request you to please share with us the files which you are using on your end and facing the issue, in a separate thread. Write down all the details about the issue and we will investigate it further to help you out.

Best Regards,
8

The issues you have found earlier (filed as SLIDESNET-36904) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.