Export charts from excel to image

Question

I have been using openpyxl to work with xlsx files on both linux and windows platform. But at this point i need to read a workbook and export a chart from a worksheet as image. I went through the documentation, but couldn't find any way to accomplish that.

Question: What can i use or do to export a chart as image using python (preferably openpyxl) ?

Answer 1

I was working with openpyxl and I had the same question.

I searched in the documentation but I didn't get anything. After a long browsing I found a solution in Youtube. The only problem is that we have to add another library call "win32com" to get the charts and then save them as images.

--- SOLUTION ---

import openpyxl
from openpyxl.chart import BarChart, Reference
from win32com.client import Dispatch

#I used Pathlib to get the absolute path of the workspace.
import pathlib

workbook_file_name = str(pathlib.Path().resolve()) + r"\barChart.xlsx"

#With this function I created an example xlsx.
def create_xlsx():
    wb = openpyxl.Workbook()

    sheet = wb.active

    for i in range(10):
        sheet.append([i])

    values = Reference(sheet, min_col=1, min_row=1,
                       max_col=1, max_row=10)

    chart = BarChart()

    chart.add_data(values)

    chart.title = " BAR-CHART "

    chart.x_axis.title = " X_AXIS "

    chart.y_axis.title = " Y_AXIS "

    sheet.add_chart(chart, "E2")

    wb.save(workbook_file_name)

#--- HERE IS THE SOLUTION! ---
def export_image():
    app = Dispatch("Excel.Application")
    # It's important to use the absolute path, it won't work with a relative one.
    workbook = app.Workbooks.Open(Filename=workbook_file_name)

    app.DisplayAlerts = False

    for i, sheet in enumerate(workbook.Worksheets):
        for chartObject in sheet.ChartObjects():
            print(sheet.Name + ':' + chartObject.Name)
            # It's important to use the absolute path, it won't work with a relative one.
            chartObject.Chart.Export(str(pathlib.Path().resolve()) + "\chart" + str(i+1) + ".png")

    workbook.Close(SaveChanges=False, Filename=workbook_file_name)

def main():
    create_xlsx()
    export_image()

if __name__ == "__main__":
    main()

--- MODULES VERSIONS ---

openpyxl==3.0.10

pywin32==304

--- REFERENCES ---

HERE'S THE LINK OF THE VIDEO