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.
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") |