Category axis datetime format

[Supermonster]

When category cells are of DateTime format - how can I set category axis to the same format as well? It seems like axis supports just numbers...


Thanks, Anton

[harry.support]

Hi Anton,
Thanks for your inquiry.
You can set chart.PrimaryCategoryAxis.NumberFormat = "yyyy-MM-dd" A full demo below is for you:

Code: Select all

using Spire.Xls;
using Spire.Xls.Charts;
using System;

namespace MySupport2
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();

            //Initailize worksheet
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Chart data";
            sheet.GridLinesVisible = false;

            //Writes chart data
            CreateChartData(sheet);
            //Add a new  chart worsheet to workbook
            Chart chart = sheet.Charts.Add();

            //Set region of chart data
            chart.DataRange = sheet.Range["A1:C5"];
            chart.SeriesDataFromRange = false;

            //Set position of chart
            chart.LeftColumn = 1;
            chart.TopRow = 6;
            chart.RightColumn = 11;
            chart.BottomRow = 29;

            chart.ChartType = ExcelChartType.Area;

            //Chart title
            chart.ChartTitle = "Sales market by date";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 12;
            chart.PrimaryCategoryAxis.NumberFormat = "yyyy-MM-dd";

            chart.PrimaryCategoryAxis.Title = "Country";
            chart.PrimaryCategoryAxis.Font.IsBold = true;
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

            chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
            chart.PrimaryValueAxis.MinValue = 1000;
            chart.PrimaryValueAxis.TitleArea.IsBold = true;
     
            foreach (ChartSerie cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            chart.Legend.Position = LegendPositionType.Top;
            workbook.Worksheets[0].AutoFitColumn(1);

            workbook.SaveToFile("Sample.xls");
            System.Diagnostics.Process.Start("Sample.xls");
        }

        private static void CreateChartData(Worksheet sheet)
        {
            //// Date
            //sheet.Range["A1"].Value = "Date";
            //sheet.Range["A2"].Value= "2011-5-6";
            //sheet.Range["A3"].Value= "2011-6-5";
            //sheet.Range["A4"].Value="2011-7-9";
            //sheet.Range["A5"].Value = "2011-9-5";

            //Date
            sheet.Range["A1"].Value = "Date";
            sheet.Range["A2"].DateTimeValue = Convert.ToDateTime("2011-5-6");
            sheet.Range["A3"].DateTimeValue = Convert.ToDateTime("2011-6-5");
            sheet.Range["A4"].DateTimeValue = Convert.ToDateTime("2011-7-9");
            sheet.Range["A5"].DateTimeValue = Convert.ToDateTime("2011-9-5");
            sheet.Range["A1:A5"].Style.NumberFormat = "yyyy-MM-dd";
   

            //Jun
            sheet.Range["B1"].Value = "Jun";
            sheet.Range["B2"].NumberValue = 6000;
            sheet.Range["B3"].NumberValue = 8000;
            sheet.Range["B4"].NumberValue = 9000;
            sheet.Range["B5"].NumberValue = 8500;

            //Jun
            sheet.Range["C1"].Value = "Aug";
            sheet.Range["C2"].NumberValue = 3000;
            sheet.Range["C3"].NumberValue = 2000;
            sheet.Range["C4"].NumberValue = 2300;
            sheet.Range["C5"].NumberValue = 4200;

            //Style
            sheet.Range["A1:C1"].Style.Font.IsBold = true;
            sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";
        }
    }
}