Search code examples
pythonexcelpowerpointpython-pptx

Python pptx - Editing excel spreadsheet table (no linked table or outside workbook required)

In PowerPoint, I have Inserted an Excel Spreadsheet table, but the "table" only seems to be a graphicframe without a shape type and returns false when I check for a table or chart:

from pptx import Presentation
prs = Presentation('../Template.pptx')
slide = prs.slides[7]
table = slide.shapes[6]
print(table)
>>><pptx.shapes.graphfrm.GraphicFrame object at 0x063BDEB0>
print(table.shape_type)
>>>None
print(table.has_table)
>>>False
print(table.has_chart)
>>>False

Any clue how I can actually edit the data inside my table variable?

To recreate this issue, create a slide and add a table through the ribbon Insert > Table > Excel Spreadsheet. Then you can modify my script above to call the proper presentation/slide/shape.

I will add that when I create a table through Insert > Table > Insert Table... or use table = slide.shapes.add_table(3,3,x,y,cx,cy) then table.has_table is true. But if the table is created this way, I can't make use of key Excel features like data types and conditional formatting. You can see in the image below, the table formatted with Currency and conditional formatting is much more readable than the one without.

table comparison screenshot

Solution

  • An embedded Excel "table" is not the same sort of thing as a PowerPoint table shape.

    The short answer is that there is no API support for editing an embedded Excel range in python-pptx.

    There are five shape types in PowerPoint: autoshape (regular square, circle, etc.), connector (lines), picture, group-shape, and graphic-frame. A graphic-frame is a "container" for a non-native-PowerPoint shape type, like chart, table, smart-art, and embedded Office object (Excel range in this case).

    So the fact that the reported shape type is GraphicFrame only narrows it down so far, and not far enough in this case to distinguish between a table and embedded Excel range.

    The GraphicFrame.has_table property is faithfully doing its job in reporting that the graphic frame does not contain a DrawingML table, although the thing it does contain certainly has a tabular appearance.

    If you want to work with an embedded Excel range you'd be on your own to manipulate the XML from the GraphicFrame down using lxml calls. I expect the graphic-frame XML contains a reference to an embedded Excel "file" which could perhaps be opened with openpyxl to make changes to it. Still, a pretty advanced bit of coding, mostly complicated by having to reverse-engineer the embedded MS Office object XML semantics and navigating the package structure to obtain the bytes of the embedded Excel "file".

    The chart code in python-pptx does something similar, because each chart has an embedded Excel file in the pptx file that provides the chart data, so that might be somewhere to look for inspiration if you were going to undertake the challenge.