forked from chris1610/pbpython
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcreate_ppt.py
More file actions
139 lines (121 loc) · 5 KB
/
create_ppt.py
File metadata and controls
139 lines (121 loc) · 5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
"""
See http://pbpython.com/creating-powerpoint.html for details on this script
Requires https://python-pptx.readthedocs.org/en/latest/index.html
Example program showing how to read in Excel, process with pandas and
output to a PowerPoint file.
"""
from __future__ import print_function
from pptx import Presentation
from pptx.util import Inches
import argparse
import pandas as pd
import numpy as np
from datetime import date
import matplotlib.pyplot as plt
import seaborn as sns
def df_to_table(slide, df, left, top, width, height, colnames=None):
"""Converts a Pandas DataFrame to a PowerPoint table on the given
Slide of a PowerPoint presentation.
The table is a standard Powerpoint table, and can easily be modified with the Powerpoint tools,
for example: resizing columns, changing formatting etc.
Arguments:
- slide: slide object from the python-pptx library containing the slide on which you want the table to appear
- df: Pandas DataFrame with the data
Optional arguments:
- colnames
https://github.com/robintw/PandasToPowerpoint/blob/master/PandasToPowerpoint.py
"""
rows, cols = df.shape
res = slide.shapes.add_table(rows + 1, cols, left, top, width, height)
if colnames is None:
colnames = list(df.columns)
# Insert the column names
for col_index, col_name in enumerate(colnames):
# Column names can be tuples
if not isinstance(col_name, str):
col_name = " ".join(col_name)
res.table.cell(0, col_index).text = col_name
m = df.as_matrix()
for row in range(rows):
for col in range(cols):
val = m[row, col]
text = str(val)
res.table.cell(row + 1, col).text = text
def parse_args():
""" Setup the input and output arguments for the script
Return the parsed input and output files
"""
parser = argparse.ArgumentParser(description='Create ppt report')
parser.add_argument('infile',
type=argparse.FileType('r'),
help='Powerpoint file used as the template')
parser.add_argument('report',
type=argparse.FileType('r'),
help='Excel file containing the raw report data')
parser.add_argument('outfile',
type=argparse.FileType('w'),
help='Output powerpoint report file')
return parser.parse_args()
def create_pivot(df, index_list=["Manager", "Rep", "Product"],
value_list=["Price", "Quantity"]):
"""
Take a DataFrame and create a pivot table
Return it as a DataFrame pivot table
"""
table = pd.pivot_table(df, index=index_list,
values=value_list,
aggfunc=[np.sum, np.mean], fill_value=0)
return table
def create_chart(df, filename):
""" Create a simple bar chart saved to the filename based on the dataframe
passed to the function
"""
df['total'] = df['Quantity'] * df['Price']
final_plot = df.groupby('Name')['total'].sum().order().plot(kind='barh')
fig = final_plot.get_figure()
fig.set_size_inches(6, 4.5)
fig.savefig(filename, bbox_inches='tight', dpi=600)
def create_ppt(input, output, report_data, chart):
""" Take the input powerpoint file and use it as the template for the output
file.
"""
prs = Presentation(input)
# Use the output from analyze_ppt to understand which layouts and placeholders
# to use
# Create a title slide first
title_slide_layout = prs.slide_layouts[0]
slide = prs.slides.add_slide(title_slide_layout)
title = slide.shapes.title
subtitle = slide.placeholders[1]
title.text = "Quarterly Report"
subtitle.text = "Generated on {:%m-%d-%Y}".format(date.today())
# Create the summary graph
graph_slide_layout = prs.slide_layouts[8]
slide = prs.slides.add_slide(graph_slide_layout)
title = slide.shapes.title
title.text = "Sales by account"
placeholder = slide.placeholders[1]
pic = placeholder.insert_picture(chart)
subtitle = slide.placeholders[2]
subtitle.text = "Results consistent with last quarter"
# Create a slide for each manager
for manager in report_data.index.get_level_values(0).unique():
#print(report_data.xs(manager, level=0).reset_index())
slide = prs.slides.add_slide(prs.slide_layouts[2])
title = slide.shapes.title
title.text = "Report for {}".format(manager)
top = Inches(1.5)
left = Inches(0.25)
width = Inches(9.25)
height = Inches(5.0)
# Flatten the pivot table by resetting the index
# Create a table on the slide
df_to_table(slide, report_data.xs(manager, level=0).reset_index(),
left, top, width, height)
prs.save(output)
if __name__ == "__main__":
args = parse_args()
df = pd.read_excel(args.report.name)
report_data = create_pivot(df)
create_chart(df, "report-image.png")
create_ppt(args.infile.name, args.outfile.name, report_data, "report-image.png")