Spreadsheet gear Chart's x axis date is appearing as whole number

Question

I am generating an output using spreadsheet gear which contains chart as output and its XAxis has date and y axis has numbers to plot the chart. At the end, application will copies the source workbook sheets to new workbook's spreadsheet. While doing this, the date values in the chart's XAxis is not appearing as date (MM/dd/yy) in the sheet which i have copied (i.e. in new workbook's sheet), instead, i am getting it as some number (such as 39174, 39175, 39176. Could this be a julian date?). Even though i am formatting the Date column to a Date format (MM/dd/yy), but still it is not referred as date in the graph. I have cross checked this by right clicking on a cell (in Date column) and choosing 'format cells' option. It is showing that it has used custom formatting (as mm/dd/yy) for it. Also, i have coded to update the links while copying it to a new sheet, but still it is not working.

Please advise to resolve this issue.

Thanks

Answer 1

You can either set the format of the chart labels using the ITickLabels interface or set the property of the axis to Linked to source and then set the format of the range that is the data source:

Here is an example of how to set chart formatting

Here is the detail of the properties of ITickLabels

This is an example of setting the label format

chart.Axes[AxisType.Value].TickLabels.NumberFormat = "0.00";

EDIT

This is the other method where you format the cell range and then link the chart axis format to this range:

Using the SpreadsheetGear basic chart example as a template:

// Declare the data range    
SpreadsheetGear.IRange dataRange = worksheet.Cells["A2:A13"];

// Set the data range format
dataRange.NumberFormat = "0.0";

The chart is set up as follows

double left = windowInfo.ColumnToPoints(2.0);
double top = windowInfo.RowToPoints(1.0);
double right = windowInfo.ColumnToPoints(9.0);
double bottom = windowInfo.RowToPoints(16.0);
SpreadsheetGear.Charts.IChart chart =
    worksheet.Shapes.AddChart(left, top, right - left, bottom - top).Chart;

// Set the chart's source data range, plotting series in columns.
chart.SetSourceData(dataRange, SpreadsheetGear.Charts.RowCol.Columns);

// Set the chart type.
chart.ChartType = SpreadsheetGear.Charts.ChartType.Area;

// Set the axis label format to the data range
chart.Axes[AxisType.Value].TickLabels.NumberFormatLinked = true;

To explicitly set the chart axis range you replace the last line with something like this:

chart.Axes[AxisType.Value].TickLabels.NumberFormat = "0.00";