Python: Creating AreaChart3D in a recursive way

Viewed 24

My target is to create some AreaChart3D plots in an automatically way. Precisely, for example I have the following picture:

enter image description here

This table is automatically outputed by a tool. I can have only one graph, maybe 2 graphs or even 100 graphs (does not matter so much), it is important every time I will have this kind of behavior with Location, Speed, and some times inside.

Now, I would like to have in the second sheet(ws2_obj) 4 graphs or maybe 2 graphs depends how many graphs will be outputed by the tool.

If I would have had a fixed number of graph it would have been easier. Because this graphs are not fixed i have to cover the entire sheet and I do not know how to do it.

from openpyxl.chart import (
    AreaChart3D,
    Reference,
)
wb_obj = xl.load_workbook('Plots.xlsx')
ws_obj = wb_obj.active
ws2_obj = wb_obj.create_sheet("Graphs")
c1 = AreaChart3D()
c1.legend = None
c1.style = 15    
cats = Reference(ws_obj, min_col=1, min_row=7, max_row=200)
data = Reference(ws_obj, min_col=2, min_row=6, max_col=8, max_row=200)
c1.add_data(data, titles_from_data=True)
c1.set_categories(cats)
ws2_obj.add_chart(c1, "A1")
wb_obj.save("Plots.xlsx")

The Code above produces only one graph, but how should I proceed to create 2 or 4 or 100 graphs?

1 Answers

You can create a list of the series data (position where the data series starts). The list has 1 element per series. Iterate the list creating a chart for each and ensure you have some means to place the chart in a unique position.
Example code with comments below.

import openpyxl as xl
from openpyxl.chart import (
    AreaChart3D,
    Reference,
)


def create_chart(tl, maxr, hdr, x_ax):
    """
    Creates a standard Area 3D Chart
    """
    cht = AreaChart3D()
    cht.legend = None
    cht.style = 15

    cht.title = hdr + " Chart"
    cht.x_axis.title = x_ax
    cht.y_axis.title = 'Something'  # Some text for the y axis

    data = Reference(ws_obj, min_col=tl[0], min_row=tl[1], max_col=tl[0]+1, max_row=maxr-1)
    cht.add_data(data, titles_from_data=True)

    return cht


## Sheet constants
chart_header = 'Speed'  # It is assumed this is located in a merged cell
x_axis_header = 'Location'
series_topleft_header = 25

## Load Workbook and Sheet of Excel with data series
wb_obj = xl.load_workbook('Plots.xlsx')
ws_obj = wb_obj.active

## Get the total used rows in the sheet (end of the series table)
maxrows = ws_obj.max_row

speed_row = ''
speed_col_start = ''
speed_col_end = ''
speed_col_letter = ''

## Get a list of Merged cell in the sheet these contain the Headers for position referencing
merge_list = [m.coord for m in ws_obj.merged_cells.ranges]

## Search for the row with Header name 'Speed' to use as reference for series data postioning
for merge_element in ws_obj.merged_cells:
    merge_cell_val = merge_element.start_cell.internal_value
    if merge_cell_val.lower() == chart_header.lower():
        speed_row = merge_element.max_row
        speed_col_start = merge_element.min_col
        speed_col_end = merge_element.max_col
        speed_col_letter = merge_element.start_cell.column_letter

series_header_row = speed_row + 1
series1_start = speed_col_letter + str(series_header_row+1)

"""
Obtain the location of the top left cell where the series data exists
This searches the row below the header (containing the text 'Speed') for the first
series header (i.e. 25 in the example) and adds each position to the series_postion_list
"""
series_position_list = []
for row in ws_obj.iter_rows(min_row=series_header_row,
                              max_row=series_header_row,
                              min_col=speed_col_start,
                              max_col=speed_col_end):
    for cell in row:
        if cell.value == series_topleft_header:
            series_position_list.append([cell.column, series_header_row])

## Create the Charts
""" 
With the series_position_list indicating the top left cell of the series data
and the number of rows in the series determined be the maxrows - 1. This data
can be passed to the create_chart function to create the chart.

Charts are placed below the series data table from Column A with two charts
per row. First row for chart location is 2 rows below the series table.
"""
chart_start_row = maxrows + 2
chart_col = 'A'

"""
The series_position_list is used to create 1 chart per series
The chart creation function takes the top left coordinate and max rows along
with Chart header name and x axis header name
"""
for enum, top_left in enumerate(series_position_list, 1):
    chart_obj = create_chart(top_left,
                             maxrows,
                             chart_header + ' ' + str(enum),
                             x_axis_header)

    ## This sets the position the chart will be placed. Based on standard size
    ## of plot area the charts are 16 rows and 10 columns apart
    if enum == 1:
        pass
    elif enum % 2 == 1:
        chart_col = 'A'
        chart_start_row += 16
    else:
        chart_col = 'J'

    ## Adds chart to the Excel sheet
    print(f"Adding chart {chart_header + ' ' + str(enum)} to Excel:")
    print(f"Series Data Start; Row:{str(top_left[1]+1)} Column:{top_left[0]}")
    ws_obj.add_chart(chart_obj, chart_col + str(chart_start_row))

    print("--------------\n")

wb_obj.save("Plots.xlsx")

Output for example data provided

Related