CODEWITHSUNDEEP
pythonexcelchartsopenpyxlxlsx
Asif Mahmud Shimon
Asif Mahmud Shimon

Reputation: 123

Export charts from excel to image

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) ?

Upvotes: 2

Views: 6313

Answers (2)

JLMelandri8
JLMelandri8

Reputation: 138

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

Upvotes: 3

Jeffrey Lim
Jeffrey Lim

Reputation: 52

I found this on this reading some documentation https://pypi.org/project/openpyxl-image-loader/

If you know where the images is you can do

from openpyxl_image_loader import SheetImageLoader

# Load your workbook and sheet as you want, for example
wb = load_workbook('path_to_file.xlsx')
sheet = wb['required_sheet']

# Put your sheet in the loader
image_loader = SheetImageLoader(sheet)

# And get image from specified cell
image = image_loader.get('A3')

# Image now is a Pillow image, so you can do the following
image.show()

# Ask if there's an image in a cell
if image_loader.image_in('A4):
    print("Got it!")

If you aren't sure where the image is in your workbook you can try iterating through the cells and until you find the image.

Upvotes: 0

Related Questions