Using Python PPTX, creating a new chart slide from a template.
After slide creation when Edit Chart Data is selected from the right-click menu, the formatting spoils on the slide and is lost.
The only way to get it back is to exit PPT and lose any changes.
from numpy.core.numeric import False_
from pptx import Presentation
from pptx.chart.data import CategoryChartData, XyDataPoint
from pptx.enum.chart import XL_CHART_TYPE, XL_TICK_LABEL_POSITION
from pptx.util import Inches
from pptx.dml.color import RGBColor
from pptx.enum.chart import XL_LABEL_POSITION
from pptx.enum.chart import XL_TICK_MARK
from pptx.util import Pt
from pptx.enum.chart import XL_LEGEND_POSITION
from pptx.chart.data import ChartData
#from pptx.chart.data import SeriesData
from pptx.enum.chart import XL_MARKER_STYLE
from pptx.enum.text import PP_ALIGN
from pptx.enum.shapes import MSO_CONNECTOR
from pptx.enum.text import MSO_ANCHOR
from pptx.oxml.xmlchemy import OxmlElement
from xml.sax import ContentHandler, parse
import pandas as pd
import numpy as np
import os
import glob
from collections import deque
import time
start = time.time()
class ExcelHandler(ContentHandler):
def __init__(self):
self.chars = [ ]
self.cells = [ ]
self.rows = [ ]
self.tables = [ ]
def characters(self, content):
self.chars.append(content)
def startElement(self, name, atts):
if name=="Cell":
self.chars = [ ]
elif name=="Row":
self.cells=[ ]
elif name=="Table":
self.rows = [ ]
def endElement(self, name):
if name=="Cell":
self.cells.append(''.join(self.chars))
elif name=="Row":
self.rows.append(self.cells)
elif name=="Table":
self.tables.append(self.rows)
baseDirVar = os.path.abspath(os.path.dirname(__file__)) + "/"
#create new presentation
prs = Presentation(baseDirVar+'horiz_stack_template.pptx')
Files = []
Formats =[]
for dataList in glob.glob(baseDirVar+'*_data.xml'):
Files.append(dataList)
for formatList in glob.glob(baseDirVar+'*_format.xml'):
Formats.append(formatList)
for x in Files:
excelHandler = ExcelHandler()
parse(x, excelHandler)
df_data = pd.DataFrame(excelHandler.tables[0])
for y in Formats:
excelHandler = ExcelHandler()
parse(y, excelHandler)
df_format = pd.DataFrame(excelHandler.tables[0])
ab = pd.DataFrame([df_format.loc[1,:][1:]]) # makes the labels into its own df with 1 row
stack_ab = ab.stack() #stack the row into a column for the labels.
de = df_data.drop(index=1, axis = 0)
ef = de.drop(index=0, axis = 0)
barchart = prs.slide_layouts[-1]
slide2 = prs.slides.add_slide(barchart)
l,t,w,h = Inches(2),Inches(0),Inches(2.5),Inches(1)
txBox_3 = slide2.shapes.add_textbox(l,t,w,h)
tf3 = txBox_3.text_frame
tf3.text = str("some title text").lower()
p = tf3.paragraphs[0].font.size=Pt(44)
p = tf3.paragraphs[0].font.name='Frutiger (Headings)'
p = tf3.paragraphs[0].alignment = PP_ALIGN.LEFT
p = tf3.paragraphs[0].vertical_anchor = MSO_ANCHOR.TOP
chart_data = ChartData()
mplist=df_data[0][2:].tolist()
chart_data.categories = mplist
data_1 = df_data[1][2:]
data_2 = df_data[2][2:]
chart_data.add_series(mplist[0], data_1, '0%') #a data series needs a lable, but its not used here and overwritten
chart_data.add_series(mplist[1], data_2, '0%')
x, y, cx, cy = Inches(2), Inches(2), Inches(9.75), Inches(5.25)
graphic_frame = slide2.shapes.add_chart(XL_CHART_TYPE.BAR_STACKED_100, x, y, cx, cy, chart_data)
chart = graphic_frame.chart
chart.plots[0].vary_by_categories = False
#this controls the legend
chart.has_legend = False
#legend = chart.legend
#legend.font.size = Pt(10)
#legend.font.name='Frutiger (Headings)'
#legend.include_in_layout = False
#legend.position = XL_LEGEND_POSITION.RIGHT
#add colors to specific bars in series using series indexer
plot = chart.plots[0]
series = plot.series[0]
fill = series.format.fill
chart.has_title = True
chart.has_textframe = True
chart.chart_title.has_text_frame = True
chart.chart_title.text_frame.text = str(df_format[1][1])+" vs. "+str(df_format[2][1]) #read in the labels from format for chart title
chart.chart_title.text_frame.paragraphs[0].font.size = Pt(18)
chart.chart_title.text_frame.paragraphs[0].font.name = 'Frutiger (Headings)'
series = plot.series[0]
fill = series.format.fill
fill.solid()
fill.fore_color.rgb = RGBColor.from_string('007096') #set bar colors
series = plot.series[1]
fill = series.format.fill
fill.solid()
fill.fore_color.rgb = RGBColor.from_string('052748')
plot = chart.plots[0]
plot.has_data_labels = True
data_labels = plot.data_labels
data_labels.font.size = Pt(10)
data_labels.font.name ='Frutiger (Body)'
data_labels.font.color.rgb = RGBColor(255,255,255) #set white font on bars
data_labels.number_format = '0.00%'
data_labels.position = XL_LABEL_POSITION.CENTER
category_axis = chart.category_axis
category_axis.has_major_gridlines = False
category_axis.has_minor_gridlines = False
category_axis.minor_tick_mark = XL_TICK_MARK.OUTSIDE
category_axis.tick_labels.font.italic = False
category_axis.tick_labels.number_format = '0.0%'
category_axis.tick_labels.font.size = Pt(12)
category_axis.tick_label_position = XL_TICK_LABEL_POSITION.LOW
value_axis = chart.value_axis
value_axis.has_major_gridlines = True
value_axis.has_minor_gridlines = False
value_axis.minor_tick_mark = XL_TICK_MARK.NONE
value_axis.tick_label_position = XL_TICK_LABEL_POSITION.LOW
value_axis.minor_unit = 0.1 #set pips to only every 10%
value_axis.minor_tick_mark = XL_TICK_MARK.OUTSIDE
value_axis.tick_labels.number_format = '0.0%'
value_axis.tick_labels.font.italic = False
value_axis.tick_labels.font.size = Pt(12)
end=time.time()
print((end - start)/60)
prs.save(baseDirVar+'test.pptx')
data reads from Excel XML file pair hence the class at the top data file format:
I cannot tell if there is some fundamental python-pptx feature I am missing that pairs the data with the PPT and Excel layer properly?
When I convert the PPT file to a ZIP I can see the data matches in zip/ppt/embeddings/Microsoft_Excel_Sheet1.xlsx
This is the problem chart bars and data disappear from slide
