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.

Answer 1

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.

Answer 2

After Export you can try to store with:

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

Answer 3

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.