pythonexcelmatplotlibopenpyxl

Can we access and replace a image in a xlsx

I am writing a script for automation of a process which requires updating an Excel file and then plotting a graph based on some data present in this Excel file and then insert the graph in the same Excel file. I have used openpyxl for reading and writing the Excel file and then used matplotlib for drawing a graph for data and then inserted the graph to the same Excel file. The data in the Excel file is being updated once or twice a week. Every time the data is updated, I need to plot an updated graph and insert the graph in the Excel file. Right now my script is updating the values in the Excel file Automatically and plotting the graph for the updated data but when I insert the graph it does not overwrite the previous graph: It every time appends the graph above the previous graph because of which the size of the Excel file will keep on increasing.

Right now, the code that I am using for plotting and inserting the graph in Excel file is:

fig = plt.figure(figsize=(8,4)) 
PLT = fig.add_axes([0.04, 0.08, 0.79, 0.8]) 

plt.xlabel("WORKING WEEK (WW)",fontsize=7)
plt.ylabel("UTILIZATION [%]",fontsize=7)
plt.title("PATCH UTILIZATION",fontsize=9)
#PLT.subplots_adjust(right=0.8)
for i in range(len(p)):
    PLT.plot(x,p[i],label = '%s'%row[0],marker="s",markersize=2)
PLT.legend(bbox_to_anchor=(1.21,1),borderaxespad=0,prop={'size':6})
PLT.tick_params(axis='both', which='major', labelsize=4)
plt.savefig("myplot.png",dpi=160)
wb=load_workbook('Excel.xlsm',read_only=False,keep_vba=True)
ws=wb['Patch Util']
img = openpyxl.drawing.image.Image("myplot.png")
img.anchor='D50'
ws.add_image(img)
wb.save('Excel.xlsm')

"x" and "p" are two lists (p is list of lists) which are containing data and will be updated when the data in the Excel file is updated.

What I want is to plot a graph and insert it once. Now whenever the data is updated I want to access the same graph from the Excel file, plot it for updated data and re-inserting it in the same Excel file, instead of inserting a new graph every time at the top of the previous graph, so that the size of the Excel file remains the same.

It will be great help if anyone can help me with this

Solution

  • Comment: No..I am using 2.5.6 version and in my case every graph and chart is retained

    Show me the output of the following: 

    from openpyxl import load_workbook

wb = load_workbook(<your file path>)
ws = wb.worksheets[<your sheet index>]

for image in ws._images:
    print("_id:{}, img.path:{}".format(image._id, image.path))

    Comment: the output i got is- _id:1, img.path:/xl/media/image1.png

    Question: Can we access and replace a image in a xlsx

    You can do it replacing the Image object in ws._images. For the first time , you have to initalise the ref data, doing it as usual using ws.add_image(...). If an image exists len(ws._images) == 1 you can replace it with a new Image object.
    For example:

    if len(ws._images) == 0:
    # Initalise the `ref` data, do ws.add_image(...)   
    img = openpyxl.drawing.image.Image("myplot.png")
    img.anchor='D50'
    ws.add_image(img)

elif len(ws._images) == 1:
    # Replace the first image do **only** the following:
    ws._images[0] = openpyxl.drawing.image.Image("myplot.png")
    # Update the default anchor `A1` to your needs
    ws._images[0].anchor='D50'
else:
    raise(ValueError, "Found more than 1 Image!")

    Note: You are using a class private property, this could result in unexpected side effect.

    Working with openpyxl Version 2.5.6