Charts from Excel to PowerPoint with Python

Question

I have an excel workbook that is created using an excellent "xlsxwriter" module. In this workbook, there about about 200 embedded charts. I am now trying to export all those charts into several power point presentations. Ideally, I want to preserve the original format and embedded data without linking to external excel work book.

I am sure there is a way to do this using VBA. But, I was wondering if there is a way to do this using Python. Is there a way to put xlsxwriter chart objects into powerpoints ?

I have looked at python-pptx and can't find anything about getting charts or data series from excel work book.

Any help is appreciated !

Litton

You can try OprnPyXl to read the chart from the Excel file, which I believe it supports, write to a file buffer and then add to PowerPoint. EDIT: 0penPyXl still does not support chart reading so I doubt this is possible in python. – Impious

try www.pptxbuilder.com – Equisetum

Answer 1

After spending hours of trying different things, I have found the solution to this problem. Hopefully,it will help someone save some time.The following code will copy all the charts from "workbook_with_charts.xlsx" to "Final_PowerPoint.pptx."

For some reason, that I am yet to understand, it works better when running this Python program from CMD terminal. It sometimes breaks down if you tried to run this several times, even though the first run is usually OK.

Another issue is that in the fifth line, if you make False using "presentation=PowerPoint.Presentations.Add(False)," it does not work with Microsoft Office 2013, even though both "True" and "False" will still work with Microsoft Office 2010.

It would be great if someone can clarify these about two issues.

# importing the necessary libraries
import win32com.client
from win32com.client import constants

PowerPoint=win32com.client.Dispatch("PowerPoint.Application")
Excel=win32com.client.Dispatch("Excel.Application")


presentation=PowerPoint.Presentations.Add(True)
workbook=Excel.Workbooks.Open(Filename="C:\\.........\\workbook_with_charts.xlsx",ReadOnly=1,UpdateLinks=False)

for ws in workbook.Worksheets:
    for chart in ws.ChartObjects():
    # Copying all the charts from excel
        chart.Activate()
        chart.Copy()  

        Slide=presentation.Slides.Add(presentation.Slides.Count+1,constants.ppLayoutBlank)
        Slide.Shapes.PasteSpecial(constants.ppPasteShape)

    # WE are going to make the title of slide the same chart title
    # This is optional 
    textbox=Slide.Shapes.AddTextbox(1,100,100,200,300)
    textbox.TextFrame.TextRange.Text=str(chart.Chart.ChartTitle.Text)

presentation.SaveAs("C:\\...........\\Final_PowerPoint.pptx")
presentation.Close()
workbook.Close()

print 'Charts Finished Copying to Powerpoint Presentation'

Excel.Quit()
PowerPoint.Quit()

Litton

I am hitting with AttributeError: ppLayoutBlank – Millionaire

@Litton Hi! This is a very neat example for basic use. Can you tell where did you find supporting material (help, guide, tutorials, etc.) where the code and its use like PowerPoint.Presentations.Add(), Slide.Shapes.PasteSpecial() or Slide.Shapes.AddTextbox() is explained? – Protuberancy

@Mike, Once you get the basic set up working to control excel and/or powerpoint file from python, additional complicated stuff essentially comes from VBA guide since we are essentially writing VBA code from more efficient code structure of Python... for example, learn.microsoft.com/en-us/office/vba/api/… or learn.microsoft.com/en-us/office/vba/api/excel.workbook – Litton

@Millionaire Hi, this code should solve your problem: from win32com.client import constants. – Protuberancy

Answer 2

The approach I'd be inclined toward with the current python-pptx version is to read the Excel sheets for their data and recreate the charts in python-pptx. That of course would require knowing what the chart formatting is, etc., so I could see why you might not want to do that.

Importing charts directly from Excel has been done in the past, see the pull request here on GitHub: https://github.com/scanny/python-pptx/pull/65

But it involved a large amount of surgery on python-pptx, and many versions back now, so at most it might be a good guide to what strategies might work. You'd need to want it pretty bad I suppose to go that route :)

Creatine

Hi Scanny, Thanks for your answer. I won't mind recreating charts with python-pptx even though I doubt python-pptx offers the same level of sophistication in terms of charting as "xlsxwriter." Also, I can't find any example of reading excel sheet data from python-pptx. Please share if you have any. – Litton

You would need to use another package to read the Excel data. I was thinking using xlsxwriter was an option, since it's imported by python-pptx anyway, but remember now it doesn't work for reading. It would have to be one of the other Excel packages I suppose, or perhaps exporting as CSV. – Creatine

Hi @Creatine are stacked charts, horizontal bar charts and area charts coming to python-pptx charting capabilities anytime soon ? – Phelps

@Phelps This is a separate question, perhaps best addressed on the python-pptx GitHub issues list. Short answer is most of these are in there now. – Creatine

Answer 3

I don't have enough reputation to comment but if you get the same issue as @R__raki__ then you can use the integer value defined by the VBA reference. For this case it would be 12.

So replace

Slide=presentation.Slides.Add(presentation.Slides.Count+1,constants.ppLayoutBlank)

with

Slide=presentation.Slides.Add(presentation.Slides.Count+1,12)

See here for more.

Gibbosity