How can I save an excel chart as an image using python?

Question

I am currently trying to use the win32com python library to open an excel file which contains a chart, and save that chart as an image in the same directory.

I have tried the code below:

import win32com.client as win32
from win32com.client import Dispatch
import os


xlApp = win32.gencache.EnsureDispatch('Excel.Application')

# Open the workbook with the correct path
workbook = xlApp.Workbooks.Open("C:\\Users\\Owner\\PycharmProjects\\venv\\automaticexcelgrapherv4\\saveImageTest.xlsx")
xlApp.Sheets("Sheet1").Select()
xlApp.Visible = True

xlSheet1 = workbook.Sheets(1)

#Ensure to save any work before running script
xlApp.DisplayAlerts = False

i = 0
for chart in xlSheet1.ChartObjects():

    chart.CopyPicture()
    #Create new temporary sheet
    xlApp.ActiveWorkbook.Sheets.Add(After=xlApp.ActiveWorkbook.Sheets(1)).Name="temp_sheet" + str(i)
    temp_sheet = xlApp.ActiveSheet

    #Add chart object to new sheet.
    cht = xlApp.ActiveSheet.ChartObjects().Add(0,0,800, 600)
    #Paste copied chart into new object
    cht.Chart.Paste()
    #Export image
    #IMP: The next line exports the png image to the new sheet, however I would like to save it in the directory instead
    cht.Chart.Export("chart" + str(i) + ".png")
    i = i+1

xlApp.ActiveWorkbook.Close()
#Restore default behaviour
xlApp.DisplayAlerts = True

This creates a new sheet inside the excel file and puts the .png image of the chart inside it. However, I do not know how to then save that image in the directory.

score 2 · Accepted Answer · answered Apr 09 '21 at 15:05

Following up with the answer from @nernac. Your code can save all the excel objects into images with minor modification:

import win32com.client
import PIL
from PIL import ImageGrab, Image
import os
import sys

inputExcelFilePath = "C:\\Users\\Owner\\PycharmProjects\\venv\\automaticexcelgrapherv4\\saveImageTest.xlsx"
outputPNGImagePath = "C:\\Users\\Owner\\PycharmProjects\\venv\\automaticexcelgrapherv4\\"

# This function extracts a graph from the input excel file and saves it into the specified PNG image path (overwrites the given PNG image)
def saveExcelGraphAsPNG(inputExcelFilePath, outputPNGImagePath):
    # Open the excel application using win32com
    o = win32com.client.Dispatch("Excel.Application")
    # Disable alerts and visibility to the user
    o.Visible = 0
    o.DisplayAlerts = 0
    # Open workbook
    wb = o.Workbooks.Open(inputExcelFilePath)

    # Extract first sheet
    sheet = o.Sheets(1)
    for n, shape in enumerate(sheet.Shapes):
        # Save shape to clipboard, then save what is in the clipboard to the file
        shape.Copy()
        image = ImageGrab.grabclipboard()
        length_x, width_y = image.size
        size = int(factor * length_x), int(factor * width_y)
        image_resize = image.resize(size, Image.ANTIALIAS)
        # Saves the image into the existing png file (overwriting) TODO ***** Have try except?
        outputPNGImage = outputPNGImagePath + str(n) + '.jpeg'
        image_resize.save(outputPNGImage, 'JPEG', quality=95, dpi=(300, 300))
        pass
    pass

    wb.Close(True)
    o.Quit()

saveExcelGraphAsPNG(inputExcelFilePath, outputPNGImagePath)

I also added lines to adjust the size and resolution of images. This worked on Python 3.7.

```size = int(factor * length_x), int(factor * width_y)``` factor value? — THAVASI.T, Dec 05 '22 at 11:54

score 1 · Answer 2 · answered May 07 '20 at 21:47

Found some code that did something similar, and after a bit of fixing it worked:

import win32com.client
import PIL
from PIL import ImageGrab, Image
import os
import sys

inputExcelFilePath = "C:\\Users\\Owner\\PycharmProjects\\venv\\automaticexcelgrapherv4\\saveImageTest.xlsx"
outputPNGImagePath = "C:\\Users\\Owner\\PycharmProjects\\venv\\automaticexcelgrapherv4\\PreviewGraphAutomaticExcelGrapher.png"

# This function extracts a graph from the input excel file and saves it into the specified PNG image path (overwrites the given PNG image)
def saveExcelGraphAsPNG(inputExcelFilePath, outputPNGImagePath):
    # Open the excel application using win32com
    o = win32com.client.Dispatch("Excel.Application")
    # Disable alerts and visibility to the user
    o.Visible = 0
    o.DisplayAlerts = 0
    # Open workbook
    wb = o.Workbooks.Open(inputExcelFilePath)

    # Extract first sheet
    sheet = o.Sheets(1)
    for n, shape in enumerate(sheet.Shapes):
        # Save shape to clipboard, then save what is in the clipboard to the file
        shape.Copy()
        image = ImageGrab.grabclipboard()
        # Saves the image into the existing png file (overwriting) TODO ***** Have try except?
        image.save(outputPNGImagePath, 'png')
        pass
    pass

    wb.Close(True)
    o.Quit()

saveExcelGraphAsPNG(inputExcelFilePath, outputPNGImagePath)

The function takes in as input the path to an excel file containing a graph (or multiple ones, in which case it would select the last one) and the path to an existing PNG image which it then overrides to put the graph inside.

score 0 · Answer 3 · edited May 07 '20 at 13:35

0

After Export you can try to store with:

images = {}
with open(chartOne.png', 'rb') as x:
    image = x.read()
    images['MyImages'] = image

edited May 07 '20 at 13:35

Dave Challis

3,525
2
37
65

answered May 07 '20 at 12:49

HarishM-AIS

26
3

When I run this it says there is no such file in directory: 'chartOne.png'. Does this code read from an image in the directory? Because I would like it to copy the image from the excel file and save it into the directory. – nernac May 07 '20 at 13:53
chartOne.png - I have given as sample it will be like : your Image name : "chart" + str(i) + ".png" – HarishM-AIS May 07 '20 at 14:17

How can I save an excel chart as an image using python?

3 Answers3