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 TIME_SCALE and then set the base units to Days.

todo:image_alt_text

Sample Code

from aspose.cells import Workbook
from aspose.cells.charts import CategoryType, ChartTextDirectionType, ChartType, TimeUnit
from aspose.cells.drawing import FillType
from datetime import datetime
# Create an instance of Workbook
workbook = Workbook()
# Get the first worksheet
worksheet = workbook.worksheets[0]
# Add the sample values to cells
worksheet.cells.get("A1").put_value("Date")
# 14 means datetime format
style = worksheet.cells.style
style.number = 14
# Put values to cells for creating chart
worksheet.cells.get("A2").set_style(style)
worksheet.cells.get("A2").put_value(datetime(2022, 6, 26))
worksheet.cells.get("A3").set_style(style)
worksheet.cells.get("A3").put_value(datetime(2022, 5, 22))
worksheet.cells.get("A4").set_style(style)
worksheet.cells.get("A4").put_value(datetime(2022, 8, 3))
worksheet.cells.get("B1").put_value("Price")
worksheet.cells.get("B2").put_value(40)
worksheet.cells.get("B3").put_value(50)
worksheet.cells.get("B4").put_value(60)
# Adda chart to the worksheet
chartIndex = worksheet.charts.add(ChartType.COLUMN, 9, 6, 21, 13)
# Access the instance of the newly added chart
chart = worksheet.charts[chartIndex]
# Add SeriesCollection (chart data source) to the chart ranging from "A1" cell to "B4"
chart.set_chart_data_range("A1:B4", True)
# Set the Axis type to Date time
chart.category_axis.category_type = CategoryType.TIME_SCALE
# Set the base unit for CategoryAxis to days
chart.category_axis.base_unit_scale = TimeUnit.DAYS
# Set the direction for the axis text to be vertical
chart.category_axis.tick_labels.direction_type = ChartTextDirectionType.VERTICAL
# Fill the PlotArea area with nothing
chart.plot_area.area.fill_format.fill_type = FillType.NONE
# Set max value of Y axis.
chart.value_axis.max_value = 70
# Set major unit.
chart.value_axis.major_unit = 10.0
# Save the file
workbook.save("DateAxis.xlsx")