Free Support Forum - aspose.com

Custom date format on X axis

Hi,

I’m using Aspose Cells for Java, and I’m trying to have a custom date format on X axis of my chart. This works fine with a chart of type ChartType.SCATTER (the date format used for the cells containing the dates is used for the chart), but it doesn’t for charts of type ChartType.LINE . Is it normal ? How can I do this ?
Below is my code:

// Adding a chart to the worksheet
Chart chart = charts.addChart(ChartType.LINE,0,0,15,5);

chart.setLegendShown(this.isShowLegend());
if(chart.isLegendShown()){
chart.getLegend().setPosition(LegendPositionType.BOTTOM);
}
// Adding NSeries (chart data source)
NSeries series = chart.getNSeries();
//data
String dataRange=topLeftCell.getName()+":"+bottomRightCell.getName();
series.add(dataRange, true);
//X axis label
String xAxisLabel=firstHeaderRow.getName()+":"+lastHeaderRow.getName();
series.setCategoryData(xAxisLabel);

if(log.isDebugEnabled()){
log.debug("data used to draw graph: "+dataRange);
log.debug(“data used for X-axis : “+xAxisLabel);
}

//data “names”
Row headerRow=sheet.getCells().getRow(2);
for(int i=1; i <= bnrEntityNames.size();i++){
String serieName=headerRow.getCell(i).getStringValue();
if(log.isDebugEnabled()){
log.debug(“setting serie name: serie n°”+(i-1)+”=”+serieName);
}
series.get(i-1).setName(serieName);
}

CategoryAxis categoryAxis = chart.getCategoryAxis();
categoryAxis.setCategoryType(CategoryType.TIME_SCALE);

Thanks in advance


Vincent

Hi Vincent,

Yes I have checked in MS Excel and I think it's normal for a line chart, the time axis scale automatically set to some "....1900" year date, Is not it?

Aspose.Cells for Java works in the same way as MS Excel. If you find anything different in MS Excel, kindly create your desired chart with MS Excel and post the excel file here, we will check it soon.

Thank you.

Not exactly… On a line chart, it is possible to change the format of the axis’ data (in the “number” tab in Excel). The date format I have is mm/dd/yy , while I would like to have mm/yyyy . In the attached file, you’ll see that I have the correct date format in the cells, but not on the axis.
Basically, what I would like to do is to set the Style of the axis data, jsut like it’s possible on cells.

Regards

Hi,

Thanks for providing us the template file.

Now we are clear about the issue. Yes, it looks like a bug in Aspose.Cells for Java after an initial test, we will figure out the issue soon.

Thank you.

OK, thanks

Hi Vincent,

For the initial date format when create a chart, Aspose.Cells acts same with MS Excel, that is, for a SCATTER chart it has the same style with the source data, for a LINE chart it does not. To set the date format on axis, please add a line in your code for categoryAxis, such as following:

CategoryAxis categoryAxis = chart.getCategoryAxis();
categoryAxis.setCategoryType(CategoryType.TIME_SCALE);
categoryAxis.setNumber(17);

Currently Aspose.Cells for Java only support setNumber() to set the format, for the valid value for setNumber, please see the javadoc of Style.setNumber(). For customed format such as "mm-yyyy", we will look into it and support it in our later versions.

thanks, it will be sufficient for the moment. But indeed, it would make more sense if we had to use the Style object, just like on cells.