The Boring Python Office Talk Europython 2018 Edinburgh, Stefan - PowerPoint PPT Presentation

The Boring Python Office Talk Europython 2018 Edinburgh, Stefan Baerisch � 1

Motivation Some things should be boring � 2 all images used as part of FreePics subscription

The Challenge ! •Excel, Powerpoint, Word and PDF are everywhere •They are useful, but can be a lot of effort to produce by hand •Python has modules to automate a lot of document creation � 3

What you should get from this Talk This Talk Your Part Your Some Tasks Awareness of Pointers Great great Python Sample Modules Modules Code Some Work https://github.com/stbaercom/europython2018_boring � 4

What you should get from this Talk I have no time ! Perfectionist We’ll start simple Pragmatist � 5

Things we want Python to do. Combine Information from 1 multiple existing Excel Files Add the table and chart from the result 2 file to a Power Point Presentation Create a PDF for Archiving 3 � 6

Modules Used pandas XlsxWriter openpyxl Can also read / Used to combine Used to create the change Excel Files. inputs and produce Excel with charts, See Github pivot table etc. reportlab pdfrw Can create custom Used to combine PDFs. Not really In headless mode. PDF files used Can “print” to PDF python-pptx python-docx Used to change and Create PPTX files PandasToPowerpoint So many You do not need much modules… from each to begin with � 7

Overall Program Flow 1 2 3 This is an example. You do not need pandas, you can use just Python. You can create text-heavy PPTXs without tables and charts, but with nice images… � 8

Excel and Python project_expenses.xlsx project_rates.xlsx 1. Read 3 Files common table 2. Do some cleanup project_hours.xlsx 3. Build a pivot and a Chart � 9

Loading the Input Files Now, this is rather boring… � 10

Data Transformation and Pivots � 11

Simple Export to Excel � 12

Adding an Introduction Sheet � 13

Writing Data to the Excel "by Hand" � 14

Custom Formats by Hand (1/2) � 15

Custom Formats by Hand (2/2) � 16

Tables & Conditional Formats (1/2) � 17

Tables & Conditional Formats (2/2) Boring, but colorful � 18

Creating Charts (1/2) � 19

Creating Charts (2/2) � 20

So much for Excel We do have your Excel Files And from now on, you can have many for files… Almost perfect. Even with the charts and conditional formatting I like � 21

Powerpoints with PPTX The slide master is ugly on purpose. Really. � 22

Adding Text Boxes and Graphics � 23

Adding Table Data to a Slide � 24

Adding Charts to a Slide (1/2) � 25

Adding Charts to a Slide (2/2) � 26

So much for PPTX And now of have some Powerpoints. They even use the new And we did not Company Master even get into Shapes… Ugly but useful. And for once, even in time. I still need PDFs to send to OtherBoss � 27

PPTX to PDF with Libreoffice CLI � 28

Combining PDF Files � 29

So, what have we done Things we did Task Build a formated Excel Table with Data from Pandas 1 Applied formats, conditional formats, and tables with filters Created charts Created a PPTX based on an existing 2 template Created tables and charts in a PPTX Transformed a PPTX into a PDF via Libreo ffi ce 3 Combined multiple PDFs into a single file 30 �

Where to learn more Books Automate the Boring Stuff with Python, by Al Sweigart. Free to read under Creative Commons: https://automatetheboringstuff.com/ ReportLab - PDF Processing with Python, Michael Driscoll, Leanpub https://leanpub.com/reportlab Documentation https://xlsxwriter.readthedocs.io/ https://openpyxl.readthedocs.io/en/stable/ https://python-pptx.readthedocs.io/en/latest/ � 31

The End? https://github.com/stbaercom/europython2018_boring Bye!! � 32