Date Axis
Possible Usage Scenarios
When you create a chart from worksheet data that uses dates, and the dates are plotted along the horizontal (category) axis in the chart, Aspose.cells automatically changes the category axis to a date (time-scale) axis. A date axis displays dates in chronological order at specific intervals or base units, such as the number of days, months, or years, even if the dates on the worksheet are not in sequential order or in the same base units. By default, Aspose.cells determines the base units for the date axis based on the smallest difference between any two dates in the worksheet data. For example, if you have data for stock prices where the smallest difference between dates is seven days, Excel sets the base unit to days, but you can change the base unit to months or years if you want to see the performance of the stock over a longer period of time.
Handle Date Axis like Microsoft Excel
Please see the following sample code that create a new Excel file and put values of the chart in the first worksheet. Then we add a chart and set the type of the Axis to TimeScale and then set the base units to Days.
Sample Code
// Create an instance of Workbook | |
Workbook workbook = new Workbook(); | |
// Get the first worksheet | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Add the sample values to cells | |
worksheet.Cells["A1"].PutValue("Date"); | |
// 14 means datetime format | |
Style style = worksheet.Cells.Style; | |
style.Number = 14; | |
// Put values to cells for creating chart | |
worksheet.Cells["A2"].SetStyle(style); | |
worksheet.Cells["A2"].PutValue(new DateTime(2022, 6, 26)); | |
worksheet.Cells["A3"].SetStyle(style); | |
worksheet.Cells["A3"].PutValue(new DateTime(2022, 5, 22)); | |
worksheet.Cells["A4"].SetStyle(style); | |
worksheet.Cells["A4"].PutValue(new DateTime(2022, 8, 3)); | |
worksheet.Cells["B1"].PutValue("Price"); | |
worksheet.Cells["B2"].PutValue(40); | |
worksheet.Cells["B3"].PutValue(50); | |
worksheet.Cells["B4"].PutValue(60); | |
// Adda chart to the worksheet | |
int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 9, 6, 21, 13); | |
// Access the instance of the newly added chart | |
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex]; | |
// Add SeriesCollection (chart data source) to the chart ranging from "A1" cell to "B4" | |
chart.SetChartDataRange("A1:B4", true); | |
// Set the Axis type to Date time | |
chart.CategoryAxis.CategoryType = CategoryType.TimeScale; | |
// Set the base unit for CategoryAxis to days | |
chart.CategoryAxis.BaseUnitScale = TimeUnit.Days; | |
// Set the direction for the axis text to be vertical | |
chart.CategoryAxis.TickLabels.DirectionType = ChartTextDirectionType.Vertical; | |
// Fill the PlotArea area with nothing | |
chart.PlotArea.Area.FillFormat.FillType = FillType.None; | |
// Set max value of Y axis. | |
chart.ValueAxis.MaxValue = 70; | |
// Set major unit. | |
chart.ValueAxis.MajorUnit = 10; | |
// Save the file | |
workbook.Save("DateAxis.xlsx"); |