The Boring Python Office Talk
Baerisch, Stefan
We will have a quick tour of the many ways Python gives us to handle DOCX, XLSX, PPTX, and PDF and automate some boring office tasks. Many things are more interesting than office file formats like DOCX, XLSX, PPTX, and PDF. Still, while working with office formats does not seem to be the most fun, it is useful. But we can do better than just useful. With the Python and some great libraries, it is possible to have Python do much of the work you would have to do otherwise: Create and modify PDF files. Create Powerpoints presentations from scripts. Create Excel files, from simple tables to charts and reports. Combine information in Word documents. In this talk, we will have a look at a usual working day for Bob and Ann, two fictional office works. Both Bob and Ann work office jobs, but while Bob does all of his work by hand, but Ann knows Python. We will look at different tasks that Bob wants to do, such as preparing an Excel report, building a Powerpoint presentation, or rearranging a PDF. Then, we will look how Ann use Python and some exciting libraries to automate these task. During the talk, we will use Bob and Ann to consider different task related to office file formats. We will then look at the Python libraries that are available. Then, using this library, we will see how an otherwise boring task can be automated. The goal of the task is to showcase the libraries to Python offers to work with standard office formats and provide you with a starting point for your own office automation. After this talk, you will know how to automate at least some of your daily office tasks. You may also be bored because Python is doing so much of your work for you. If you know basic Python programming, you will be right at home. There will be some use of Pandas, but it is not required.
 Thank you everybody. So, well, I am impressed. So it is Friday on the conference. It is after the coffee break and you actually came as volunteers to talk that it's boring. Now there may be some confusion in the restroom if you want to have a nap, it's over there. It was the one day before, at one door before. Yeah, why is a boring office talk? So well, truth be told, a couple of years ago I switched to the dark side. So I'm mostly do project management nowadays. And as you know, well, there's a lot of excellent PowerPoint involved. And so I'm on my little quest to automate that. So I looked for available modules because there are always, well, a couple of modules available in Python. And in this space there are quite a lot. And what I would like to do is first of all, well, motivate you while boring things can be interesting. And then to give you a short introduction on what's available and how it could look like, hoping that you will then start to do your own, well, boring stuff with these modules and maybe with some insight from this talk. So motivation, some things should be boring. I like boring. So I don't have to think at all about the, well, equipment in this conference center. I don't have to think about the plumbing at all. It just works. Because when I have to think about the plumbing, there's a problem. So, and I would like to have the same situation for, well, office files, because they are, to a certain extent, the plumbing in most organizations that I know, they get sent around, somebody looks at them, annotates them. So, well, a way to communicate. And I want to make this boring. I don't want to think, okay, I'll have to copy and paste a lot of files, a lot of images, a lot of pictures, a lot of PowerPoint slides at Friday 8 p.m. just before, just before I hopefully can go into the weekend. That's something that we should do automatically. Which allows me to introduce my three characters. They're totally, well, archetypes. So we have the boss. The boss, as always, wants an Excel file, hopefully with nice numbers, but, well, in doubt, an Excel file with the right numbers will have to do. And we have, well, this little guy here. So he's working in office. He's the only one with a tie here. And he mostly works copy, pasting, combining, doing Excel files. So we'll learn what he does later. And he has one co-worker. And his co-worker is a Python-knowing pragmatist. So maybe he studied physics, learned about Python, now started a job. And while he knows all the files that has to be produced, she knows how to automate it. And he knows that he wants the files. And again, what do I hope to accomplish with this talk? I'll have to warn you, the code on these slides, if you compare it with what you've seen in this conference, it's not pretty. It's API code. And it's API code that is pretty closely aligned to how the file formats work. So you will see lots of calls that, well, enter some data, manipulate some data, do highlighting. This is not something that is really exciting. It's just useful, well, as the talk applied, it's boring. So what you should get out of this talk is module names. There are a lot of different modules that are available. And a rough understanding, how much effort you would need to invest to, say, have a nicely highlighted Excel file, have a PowerPoint with a chart, have a PDF file that looks like your PowerPoint file. And with that, you can maybe just convince people at your workplace to let you play with pandas because you can do calculations in a repeatable way and then create some, let's say, business-conformant files from that, that your boss can then take and edit around. So ultimately, what I wanted to do is when you maybe sit down on Monday, when you talk to somebody and say, okay, I could send you an Excel file. And whether it's a CSV file, you know, you always have to think on how to really open that. An Excel file is something that you know. And I can even do a PowerPoint for you. And that, once you have automated it, will be easy with Python. All the code, which is not much, is on GitHub. I will also, there's also the slides on GitHub right now. As soon as I have figured out how to upload the slides for the conference, you'll also have some there. So you can take this and hopefully, well, find something interesting. Another word about the philosophy about how I do things for this talk. I've learned a lot about good and best practices in using Python. This is, I tend to follow it, especially when I start more or less a quick and dirty approach. So if you think about automating things in your work, you may be, if you have a software engineering background, you may be tempted to think, okay, I will need a full testing framework. I will need to have this in Docker. And it should be a web application. And there are some extra parameters. And for things like that, I would advise against this. What you're trying to do is just save you some work to get started, especially, this is in Ubitalk, if you haven't done any large Python and programs before, I think this is a very good way to get started because you can do small things and then you can check whether they are correct. And this is the, well, basic idea. So what I do, for example, is just to, with exactly these APIs, is just combine some time sheets. So we have multiple consultants working on a project, each have their time sheets, I basically copy them together and see whether the numbers fit and then put, I'll put them in a different format. It is not especially challenging. It's I think 50 lines of code. The good thing is I don't have to think about what I've done every month. I just want the same script and things are pretty much okay. So what do we want to do? We have just three tasks in this talk. First of all, we have multiple Excel files. The Excel files here are stand-ins for any data you might accomplish. It might be a CSV file. You may read something from a database. You may have your little web crawler that checks pricing information on the Internet. So you have multiple data sources that you want to combine. That's fine. Then you want to transform them a little bit and write them out to some other Excel file. So far so good. You can do all this with the, let's say, Pandas standard API. And then you want to make it somewhat prettier, some highlighting, some specific Excel features that you or your organization might like. Then we go to the multi more presentation focused stuff. We built some PowerPoints. Once we have the PowerPoints, we will turn those into PDFs. That's it. The interesting thing about this particular task is that there are many, many good tools available. So a personal favorite, I think of all its Pandas. It reads almost everything. It does almost all kinds of calculations and it exports also quite a lot. It has a nice Excel export, for example. Then you have with Excel write out open pixel to API are two modules that are very good at working with Excel files. This one, hence the name, can only write them. This is the one that we are going to use here. Mostly for, let's say, taste reasons. I like the API slightly better. Open pixel can also read Excel files. So you can read something in, change just a few numbers, write it out again. For PDFs, we have PDF read write, which we just used to combine some PDFs. There is a gigantic number of PDFs library available for Python, depending on what you want to do. So you'll probably need to invest some time. What is quite nice, but quite big, if you wanted to do custom reporting is rapid lab. Again, something you won't see here, but just to give you a point of this is, well, if you want to do PDFs with Python and want them to be more complex than you want to see here, that's fine. I will use LepreOffice, basically in headless mode, to do some file conversations. And then we have Python PowerPoint, PBTX, which, as the name suggests, does PBTX files. Okay, the overall structure is, if you look at the GitHub repo, it's just the main method. So we read data, we do the extra format, then we do some nice output, then we output the PowerPoints, and then we do the PDFs. So if you check out the code, that should basically all work. It should have a couple of files, if not, let me know, in which case, most likely something went wrong with the share. Okay, what is our task? These are extremely made up and boring files. So the idea is we have some project team, this project team has expenses for materials, they want to build a mass station, and they have the working times. And well, everybody has to be paid, so they have some expense per hour. So these are three files that you got, maybe from your accounting department, maybe from your project team themselves. And now you want to combine them and do some reporting, because you want to know what actually my expenses. So first of all, you work with pandas to load in all these files. So you have three data frames, and well, we're not really much to see here. So we have these Excel files, each of these Excel files only has one worksheet. Okay, now you have three data. Then you want to do something that you would normally done by copy pasting an Excel. So if you work with Excel, well, you can do it. Who loves pivot tables in Excel? Okay, that's for people who uses pivot tables and have some rumbling. Okay, so I will assume from that that you would enjoy to use Python slightly more. Well, there is practically pivot. So what we do is the same thing that we most likely do in Excel file. If you can't follow this, I don't think that you should be concerned. Just know that everything that you could do in Excel, you can also do in pandas. And thankfully, especially from the MPI data people, there are a lot of very good, you know, pandas tutorials available. So I just walk you through what we are doing here so that you see the AP is quite, well, beginner-friendly. And the documentation is excellent. So whatever you want to do, I think you can Google yourself to success. Okay, so what we basically want to do is we want to combine these two files. So we update, we just start from some, starting at some end time. We calculate overall cost by rate. So we combine the rate and the hours per update table. This is basically this part here. Then we add some extra column just to say what cost type that would be, and then we combine them. So this is, I think, a pretty standard copy and paste job. And as you can see, it's not so much code. So if you wrote this not for demonstration purposes, it's most likely the same code up here into that copy here. So maybe 10 lines of code. So that's nice. We have the calculation. And the calculation is now copy and paste proof. If you have to do this multiple times, you can do it multiple times. You can even do it on a server. I think it's nicer than doing manual. So this is when we end up with, so we have different cost positions. Where do they come from? Is it working time? Or are these expensive? Who is responsible for it? We have, well, two projects, team dinner and mass colony. So it's two rather, let's say, ambitious things. And we want to output this to an Excel file. So this is almost the un-pundit standard we have doing it. So in pandas, you can do two things. You can either go to Excel and write out a single Excel file, or you can initiate your own Excel writer. And the Excel writer will make it possible to have slightly more control. Particularly here, you will create, well, different worksheets. So these three worksheets all going to the writer. And you can give them a cheat name. And now you have, well, some control over your Excel. But it's basically still all data. So I think it's not really management-friendly quite now. So first of all, what we want to do is we want to have some introductory sheet into our Excel. We want a company logo, a version number, and maybe some formatting. And to do this, you take a workbook object. You can get the workbook object from the writer object. And the workbook represents, well, basically your Excel sheet. And you can then define with Excel writer some formats. And there's a large number of different properties that you may want to use. In this particular case, I just want to say I want to sing in blue and slightly bold. This ends up here. So I have now created a new format and now I've write it. This 0, 0 means row 0 and line 0. The API also has these Excel-specific coordinates. So if you want to go A1, is it where you can also do this? I'll write my text. And bold in this case is this format object. So I'll write this out. I can also go unformatted. So this over-cost thing is here. And I can play around a little bit with the column width. So setColumn takes which column it should apply to. So first column to first column. We could also go multiple columns. And it says which width do I want to have in terms of characters. And I'll write this out. So I have my first formatting. And I can include an image. Well, and include typos, but that's another thing. OK, so what we have done so far, we have output an Excel straight from Pandas. And we have created a new sheet in this Excel. OK. But, well, what do we want to do if we need slightly more control? So for example, if my data is not in a Pandas dataset, or if I have some other data that I want Mike to add in a arbitrary position. So you have two methods. We see the first one here. Well, you have multiple, but you can write whole rows into the system. So what we do here is just we take our columns, add them here. So we have a header. And then we add the raw information in the different rows. So now I have control over how it updates. So if you have something in a dict or wherever you get it from, you don't need to, well, assume that you do an export just from Pandas. Now, this still looked rather drab. So what we want to add up is something a little bit nicer. So what you see here is more manual control over what you are doing. So first of all, we're building a worksheet. And then we add two formats this time. And you see the different properties that are available. Think bold. We already used. We have different font sizes and different colors. Then we want to write the column information out. Again, we apply one of these font information, one of these styles. And then the next thing that we want to do is to change the column widths again. So this happens here. And then we want to write out our data. And this time we do it really data element by data element. So we don't use the raw function, but just go over all our data. And what we want to accomplish is we want to change everything that is higher than the 75th percentile in the data in red. So we just use pendants to get this particular value. And then we go over F we row in our data. We look which column we are because the first column is data. That is a number. The rest is text. And if we are above the percentile value, we just apply red bold. So it's the other style that we defined. Then it looks like this. So it gets slightly more interesting. So you don't have these desert of numbers anymore, but you can hide that number that have specific interest. Now, those of you who like Excel too much, like me, might notice, okay, but there are conditional formats in Excel. So I could just, if I do this by hand, I could do all that in Excel. And in terms of highlighting, I could maybe do it as a table. That's also possible. So you can just say, okay, I want specific number formats like Excel wants them and define those. And I can define part of my Excel sheet as a table. So, five. Okay, so in order to do that, you just go for this data. And then what you can also do is say, I want conditional highlighting. Conditional highlighting, if you can own Excel, you work, you can define custom rules. And there are quite a lot of complicated properties that you can use, but my old time favorite is just this, three color scale. So it basically gives you a traffic light. And you can apply those again to a specific part in your spreadsheet. In this case, it's the same part that we used for the table. And you have a nice colorful Excel sheet. Okay. We'll have to speed up a little bit. So I'll just be quick. You can also create charts. Charts are the most ugly part in the API, mostly because of this part here. Because what you do is just here, you add all the data to the table. You need the data on the Excel sheet in order to create a chart. Excel needs to find them somewhere. And then you create two series. Each series has a name. And in this case, it could be, well, the expense information has a category. That's the person who is responsible for the price. And, well, once you have this, you can just create a chart. You could also go for mudplot lib at this moment. So it's say, we already saw earlier that we could use pictures. Yes, you could. The advantage of this one is that it is an Excel chart. So it's a digital. You can change it. OK, so much for Excel. Now we'll just go over the PowerPoint thing. The PowerPoint module, as I think, is wonderful. So basically, everything you can do by hand, you can do with a module. For Excel, there are still some limitations. So I didn't find a way to do pivot tables in the form of the Excel.expect them. So the thing about PowerPoint is one difference. You should probably prepare some template that you want to change, because you have a lot of formating information that is not necessarily required for the Excel file. And then you can just go and fill out placeholders and write out, well, ugly tables. In this case, on purpose, that you see that I did something myself. So I said, I'm not a designer. And same thing. You can add pictures to it. You can add tables to it. Here you will see that we use this module pandas to PowerPoint. It's not pip installable, but you'll find it on GitHub. It's a couple of hundred lines. And again, you can add charts. It's basically, well, the RP is close to it. There are a lot of magic numbers in here. And you're probably best off starting to copy the code and then change it. So it's not the nicest code. But it is useful. So I want, again, get a native PowerPoint object. One thing I didn't figure out so far is how to take the charts out of my Excel into my PowerPoint, like I would do with copy and pasting. Anybody have the hint there that would be helpful? OK, now we have also the PowerPoint. If you want to change this into PDF, you need something that's able to render PowerPoints. I didn't find a pattern module to do that. Luckily, there's LibreOffice, and LibreOffice has a command line mode. So what we can do is just automate the command line mode. Some of you may be familiar with that process. So what we do here is just build a command line, look for the executable, add some options. These are the ones that are interesting, and then, well, execute it. And at least on the Mac, the version that comes out of the PowerPoint, and the version that isn't the PDF, look 90% the same. So that's quite practical. What you can also do, this PDF ReadWide, is just read an existing PDF file. So let's say you have a title page, an outro, and some pages that you build. So you just read those in. And then you create a PDF writer. Then you can just say, take this page, add it to the output, take this page, add it to the output, and many things more. OK. And that's basically all we've done. So we have now seen that it is possible to create nice-looking Excel files. Some people like this. We have seen that it is possible to create PowerPoint slides. So my personal dream scenario is that in some kind of the future, I will be able to create gun charts from Excel files. That would be great. So you're working on that. And you can even create PDFs for archiving or whatever you want to do from those files. So there are some things that you can look into and learn more. Especially if you are new with Python, this book is nice. It's nicely written. And the HTML version is free. If you want to have a deep dive into the wonderful world of PDFs, then you could also do this on the Web App Lab page. I think this is the sign that I should shut up now. And the documentation for each of these modules is excellent. I mean, we're excellent. So the people who did those modules, it's almost heroic. It's not really the nicest work to do that, but they did it really well. OK. And that is it. Thank you. Yeah. Honestly, honestly, no. So I think what you'd like to do via com. I think there's also now a product. I think it's Excel Wings where you basically embed Python into Excel. Those are also available for the Mac. And the reason that I did this, the way that I did it, I just wanted to have a script that runs without opening Excel because it, for me, makes it debugging so much easier. Pivot tables will be preserved in OpenPixel. So if you have existing complex Excel files with lots of pivot tables and you just want to change the data, use OpenPixel. Yes. There are some things that you have to keep in mind. So it changes the file. This, for example, means that if you have some computations in the file that Excel would trigger automatically once you have, for example, included a pivot table, you will need to trigger in Excel again. So you cannot assume that everything that would happen in Excel will also happen if you create the file outside of Excel. That makes things well. You can. Yeah. You can. The question is, does Excel do it? So with OpenPixel, yeah, you can change the data. You can force the pivot tables to update. And you should be able to copy and paste an Excel chart into pivot, into PowerPoint. Okay. In this case, we can talk about this because this would be extremely interesting. It's not directly possible, but you should be able to take the spreadsheet drawing, dump the XML, and attach it into a PowerPoint file. It's not that difficult. Okay. Yeah, that's slightly lower level than, well, I went with this. If you want to work with templates, it should all be doable. Okay. I'm the OpenPixel maintainer, so I know this stuff. Thank you. We have time for one more question. Sorry. Someone here had a question. It was basically also the same question about how to interact with an open instance of Excel PowerPoint. And yes, there is this Excel wings, which does this for Excel, and you always can do it via com, but that's quite cumbersome. I'm not aware of anything which can do the same with PowerPoint, for example. And if someone knows something, just talk to me. Have you had any experience with a word in templates? And if you put text in it, if the text is holding the template styles and also the talk I'm having, have you some experience with it that works well with Python? Sorry. Hardly did any work or anything with Word files? Okay. I want to thank again, Stefan, for a good talk.
 Thank you everybody.  So, well, I am impressed.  So it is Friday on the conference.  It is after the coffee break and you actually came as volunteers to talk that it's boring.  Now there may be some confusion in the restroom if you want to have a nap, it's over there.  It was the one day before, at one door before.  Yeah, why is a boring office talk?  So well, truth be told, a couple of years ago I switched to the dark side.  So I'm mostly do project management nowadays.  And as you know, well, there's a lot of excellent PowerPoint involved.  And so I'm on my little quest to automate that.  So I looked for available modules because there are always, well, a couple of modules  available in Python.  And in this space there are quite a lot.  And what I would like to do is first of all, well, motivate you while boring things can  be interesting.  And then to give you a short introduction on what's available and how it could look  like, hoping that you will then start to do your own, well, boring stuff with these modules  and maybe with some insight from this talk.  So motivation, some things should be boring.  I like boring.  So I don't have to think at all about the, well, equipment in this conference center.  I don't have to think about the plumbing at all.  It just works.  Because when I have to think about the plumbing, there's a problem.  So, and I would like to have the same situation for, well, office files, because they are,  to a certain extent, the plumbing in most organizations that I know, they get sent around,  somebody looks at them, annotates them.  So, well, a way to communicate.  And I want to make this boring.  I don't want to think, okay, I'll have to copy and paste a lot of files, a lot of images,  a lot of pictures, a lot of PowerPoint slides at Friday 8 p.m. just before, just before  I hopefully can go into the weekend.  That's something that we should do automatically.  Which allows me to introduce my three characters.  They're totally, well, archetypes.  So we have the boss.  The boss, as always, wants an Excel file, hopefully with nice numbers, but, well, in  doubt, an Excel file with the right numbers will have to do.  And we have, well, this little guy here.  So he's working in office.  He's the only one with a tie here.  And he mostly works copy, pasting, combining, doing Excel files.  So we'll learn what he does later.  And he has one co-worker.  And his co-worker is a Python-knowing pragmatist.  So maybe he studied physics, learned about Python, now started a job.  And while he knows all the files that has to be produced, she knows how to automate  it.  And he knows that he wants the files.  And again, what do I hope to accomplish with this talk?  I'll have to warn you, the code on these slides, if you compare it with what you've seen in  this conference, it's not pretty.  It's API code.  And it's API code that is pretty closely aligned to how the file formats work.  So you will see lots of calls that, well, enter some data, manipulate some data, do  highlighting.  This is not something that is really exciting.  It's just useful, well, as the talk applied, it's boring.  So what you should get out of this talk is module names.  There are a lot of different modules that are available.  And a rough understanding, how much effort you would need to invest to, say, have a nicely  highlighted Excel file, have a PowerPoint with a chart, have a PDF file that looks like  your PowerPoint file.  And with that, you can maybe just convince people at your workplace to let you play with  pandas because you can do calculations in a repeatable way and then create some, let's  say, business-conformant files from that, that your boss can then take and edit around.  So ultimately, what I wanted to do is when you maybe sit down on Monday, when you talk  to somebody and say, okay, I could send you an Excel file.  And whether it's a CSV file, you know, you always have to think on how to really open  that.  An Excel file is something that you know.  And I can even do a PowerPoint for you.  And that, once you have automated it, will be easy with Python.  All the code, which is not much, is on GitHub.  I will also, there's also the slides on GitHub right now.  As soon as I have figured out how to upload the slides for the conference, you'll also  have some there.  So you can take this and hopefully, well, find something interesting.  Another word about the philosophy about how I do things for this talk.  I've learned a lot about good and best practices in using Python.  This is, I tend to follow it, especially when I start more or less a quick and dirty approach.  So if you think about automating things in your work, you may be, if you have a software  engineering background, you may be tempted to think, okay, I will need a full testing  framework.  I will need to have this in Docker.  And it should be a web application.  And there are some extra parameters.  And for things like that, I would advise against this.  What you're trying to do is just save you some work to get started, especially, this  is in Ubitalk, if you haven't done any large Python and programs before, I think this is  a very good way to get started because you can do small things and then you can check  whether they are correct.  And this is the, well, basic idea.  So what I do, for example, is just to, with exactly these APIs, is just combine some time  sheets.  So we have multiple consultants working on a project, each have their time sheets, I  basically copy them together and see whether the numbers fit and then put, I'll put them  in a different format.  It is not especially challenging.  It's I think 50 lines of code.  The good thing is I don't have to think about what I've done every month.  I just want the same script and things are pretty much okay.  So what do we want to do?  We have just three tasks in this talk.  First of all, we have multiple Excel files.  The Excel files here are stand-ins for any data you might accomplish.  It might be a CSV file.  You may read something from a database.  You may have your little web crawler that checks pricing information on the Internet.  So you have multiple data sources that you want to combine.  That's fine.  Then you want to transform them a little bit and write them out to some other Excel file.  So far so good.  You can do all this with the, let's say, Pandas standard API.  And then you want to make it somewhat prettier, some highlighting, some specific Excel features  that you or your organization might like.  Then we go to the multi more presentation focused stuff.  We built some PowerPoints.  Once we have the PowerPoints, we will turn those into PDFs.  That's it.  The interesting thing about this particular task is that there are many, many good tools  available.  So a personal favorite, I think of all its Pandas.  It reads almost everything.  It does almost all kinds of calculations and it exports also quite a lot.  It has a nice Excel export, for example.  Then you have with Excel write out open pixel to API are two modules that are very good  at working with Excel files.  This one, hence the name, can only write them.  This is the one that we are going to use here.  Mostly for, let's say, taste reasons.  I like the API slightly better.  Open pixel can also read Excel files.  So you can read something in, change just a few numbers, write it out again.  For PDFs, we have PDF read write, which we just used to combine some PDFs.  There is a gigantic number of PDFs library available for Python, depending on what you  want to do.  So you'll probably need to invest some time.  What is quite nice, but quite big, if you wanted to do custom reporting is rapid lab.  Again, something you won't see here, but just to give you a point of this is, well, if you  want to do PDFs with Python and want them to be more complex than you want to see here,  that's fine.  I will use LepreOffice, basically in headless mode, to do some file conversations.  And then we have Python PowerPoint, PBTX, which, as the name suggests, does PBTX files.  Okay, the overall structure is, if you look at the GitHub repo, it's just the main method.  So we read data, we do the extra format, then we do some nice output, then we output the  PowerPoints, and then we do the PDFs.  So if you check out the code, that should basically all work.  It should have a couple of files, if not, let me know, in which case, most likely something  went wrong with the share.  Okay, what is our task?  These are extremely made up and boring files.  So the idea is we have some project team, this project team has expenses for materials,  they want to build a mass station, and they have the working times.  And well, everybody has to be paid, so they have some expense per hour.  So these are three files that you got, maybe from your accounting department, maybe from  your project team themselves.  And now you want to combine them and do some reporting, because you want to know what actually  my expenses.  So first of all, you work with pandas to load in all these files.  So you have three data frames, and well, we're not really much to see here.  So we have these Excel files, each of these Excel files only has one worksheet.  Okay, now you have three data.  Then you want to do something that you would normally done by copy pasting an Excel.  So if you work with Excel, well, you can do it.  Who loves pivot tables in Excel?  Okay, that's for people who uses pivot tables and have some rumbling.  Okay, so I will assume from that that you would enjoy to use Python slightly more.  Well, there is practically pivot.  So what we do is the same thing that we most likely do in Excel file.  If you can't follow this, I don't think that you should be concerned.  Just know that everything that you could do in Excel, you can also do in pandas.  And thankfully, especially from the MPI data people, there are a lot of very good, you  know, pandas tutorials available.  So I just walk you through what we are doing here so that you see the AP is quite, well,  beginner-friendly.  And the documentation is excellent.  So whatever you want to do, I think you can Google yourself to success.  Okay, so what we basically want to do is we want to combine these two files.  So we update, we just start from some, starting at some end time.  We calculate overall cost by rate.  So we combine the rate and the hours per update table.  This is basically this part here.  Then we add some extra column just to say what cost type that would be, and then we  combine them.  So this is, I think, a pretty standard copy and paste job.  And as you can see, it's not so much code.  So if you wrote this not for demonstration purposes, it's most likely the same code up  here into that copy here.  So maybe 10 lines of code.  So that's nice.  We have the calculation.  And the calculation is now copy and paste proof.  If you have to do this multiple times, you can do it multiple times.  You can even do it on a server.  I think it's nicer than doing manual.  So this is when we end up with, so we have different cost positions.  Where do they come from?  Is it working time?  Or are these expensive?  Who is responsible for it?  We have, well, two projects, team dinner and mass colony.  So it's two rather, let's say, ambitious things.  And we want to output this to an Excel file.  So this is almost the un-pundit standard we have doing it.  So in pandas, you can do two things.  You can either go to Excel and write out a single Excel file, or you can initiate your  own Excel writer.  And the Excel writer will make it possible to have slightly more control.  Particularly here, you will create, well, different worksheets.  So these three worksheets all going to the writer.  And you can give them a cheat name.  And now you have, well, some control over your Excel.  But it's basically still all data.  So I think it's not really management-friendly quite now.  So first of all, what we want to do is we want to have some introductory sheet into  our Excel.  We want a company logo, a version number, and maybe some formatting.  And to do this, you take a workbook object.  You can get the workbook object from the writer object.  And the workbook represents, well, basically your Excel sheet.  And you can then define with Excel writer some formats.  And there's a large number of different properties that you may want to use.  In this particular case, I just want to say I want to sing in blue and slightly bold.  This ends up here.  So I have now created a new format and now I've write it.  This 0, 0 means row 0 and line 0.  The API also has these Excel-specific coordinates.  So if you want to go A1, is it where you can also do this?  I'll write my text.  And bold in this case is this format object.  So I'll write this out.  I can also go unformatted.  So this over-cost thing is here.  And I can play around a little bit with the column width.  So setColumn takes which column it should apply to.  So first column to first column.  We could also go multiple columns.  And it says which width do I want to have in terms of characters.  And I'll write this out.  So I have my first formatting.  And I can include an image.  Well, and include typos, but that's another thing.  OK, so what we have done so far, we have output an Excel straight from Pandas.  And we have created a new sheet in this Excel.  OK.  But, well, what do we want to do if we need slightly more control?  So for example, if my data is not in a Pandas dataset,  or if I have some other data that I want Mike to add in a arbitrary position.  So you have two methods.  We see the first one here.  Well, you have multiple, but you can write whole rows into the system.  So what we do here is just we take our columns, add them here.  So we have a header.  And then we add the raw information in the different rows.  So now I have control over how it updates.  So if you have something in a dict or wherever you get it from,  you don't need to, well, assume that you do an export just from Pandas.  Now, this still looked rather drab.  So what we want to add up is something a little bit nicer.  So what you see here is more manual control over what you are doing.  So first of all, we're building a worksheet.  And then we add two formats this time.  And you see the different properties that are available.  Think bold.  We already used.  We have different font sizes and different colors.  Then we want to write the column information out.  Again, we apply one of these font information, one of these styles.  And then the next thing that we want to do is to change the column widths again.  So this happens here.  And then we want to write out our data.  And this time we do it really data element by data element.  So we don't use the raw function, but just go over all our data.  And what we want to accomplish is we want to change everything that is  higher than the 75th percentile in the data in red.  So we just use pendants to get this particular value.  And then we go over F we row in our data.  We look which column we are because the first column is data.  That is a number.  The rest is text.  And if we are above the percentile value, we just apply red bold.  So it's the other style that we defined.  Then it looks like this.  So it gets slightly more interesting.  So you don't have these desert of numbers anymore, but  you can hide that number that have specific interest.  Now, those of you who like Excel too much, like me, might notice, okay,  but there are conditional formats in Excel.  So I could just, if I do this by hand, I could do all that in Excel.  And in terms of highlighting, I could maybe do it as a table.  That's also possible.  So you can just say, okay, I want specific number formats like Excel wants them and  define those.  And I can define part of my Excel sheet as a table.  So, five.  Okay, so in order to do that, you just go for this data.  And then what you can also do is say, I want conditional highlighting.  Conditional highlighting, if you can own Excel, you work, you can define custom rules.  And there are quite a lot of complicated properties that you can use, but  my old time favorite is just this, three color scale.  So it basically gives you a traffic light.  And you can apply those again to a specific part in your spreadsheet.  In this case, it's the same part that we used for the table.  And you have a nice colorful Excel sheet.  Okay.  We'll have to speed up a little bit.  So I'll just be quick.  You can also create charts.  Charts are the most ugly part in the API, mostly because of this part here.  Because what you do is just here, you add all the data to the table.  You need the data on the Excel sheet in order to create a chart.  Excel needs to find them somewhere.  And then you create two series.  Each series has a name.  And in this case, it could be, well, the expense information has a category.  That's the person who is responsible for the price.  And, well, once you have this, you can just create a chart.  You could also go for mudplot lib at this moment.  So it's say, we already saw earlier that we could use pictures.  Yes, you could.  The advantage of this one is that it is an Excel chart.  So it's a digital.  You can change it.  OK, so much for Excel.  Now we'll just go over the PowerPoint thing.  The PowerPoint module, as I think, is wonderful.  So basically, everything you can do by hand, you can do with a module.  For Excel, there are still some limitations.  So I didn't find a way to do pivot tables in the form of the Excel.expect them.  So the thing about PowerPoint is one difference.  You should probably prepare some template that you want to change,  because you have a lot of formating information that is not necessarily  required for the Excel file.  And then you can just go and fill out placeholders and write out, well,  ugly tables.  In this case, on purpose, that you see that I did something myself.  So I said, I'm not a designer.  And same thing.  You can add pictures to it.  You can add tables to it.  Here you will see that we use this module pandas to PowerPoint.  It's not pip installable, but you'll find it on GitHub.  It's a couple of hundred lines.  And again, you can add charts.  It's basically, well, the RP is close to it.  There are a lot of magic numbers in here.  And you're probably best off starting to copy the code and then change it.  So it's not the nicest code.  But it is useful.  So I want, again, get a native PowerPoint object.  One thing I didn't figure out so far is how to take the charts out of my Excel  into my PowerPoint, like I would do with copy and pasting.  Anybody have the hint there that would be helpful?  OK, now we have also the PowerPoint.  If you want to change this into PDF,  you need something that's able to render PowerPoints.  I didn't find a pattern module to do that.  Luckily, there's LibreOffice, and LibreOffice has a command line mode.  So what we can do is just automate the command line mode.  Some of you may be familiar with that process.  So what we do here is just build a command line,  look for the executable, add some options.  These are the ones that are interesting, and then, well, execute it.  And at least on the Mac, the version that comes out of the PowerPoint,  and the version that isn't the PDF, look 90% the same.  So that's quite practical.  What you can also do, this PDF ReadWide, is just read an existing PDF file.  So let's say you have a title page, an outro, and some pages that you build.  So you just read those in.  And then you create a PDF writer.  Then you can just say, take this page, add it to the output,  take this page, add it to the output, and many things more.  OK.  And that's basically all we've done.  So we have now seen that it is possible to create nice-looking Excel files.  Some people like this.  We have seen that it is possible to create PowerPoint slides.  So my personal dream scenario is that in some kind of the future,  I will be able to create gun charts from Excel files.  That would be great.  So you're working on that.  And you can even create PDFs for archiving or whatever you want to do  from those files.  So there are some things that you can look into and learn more.  Especially if you are new with Python, this book is nice.  It's nicely written.  And the HTML version is free.  If you want to have a deep dive into the wonderful world of PDFs,  then you could also do this on the Web App Lab page.  I think this is the sign that I should shut up now.  And the documentation for each of these modules is excellent.  I mean, we're excellent.  So the people who did those modules, it's almost heroic.  It's not really the nicest work to do that, but they did it really well.  OK.  And that is it.  Thank you.  Yeah.  Honestly, honestly, no.  So I think what you'd like to do via com.  I think there's also now a product.  I think it's Excel Wings where you basically embed Python into Excel.  Those are also available for the Mac.  And the reason that I did this, the way that I did it, I just wanted to have a script that  runs without opening Excel because it, for me, makes it debugging so much easier.  Pivot tables will be preserved in OpenPixel.  So if you have existing complex Excel files with lots of pivot tables and you just want  to change the data, use OpenPixel.  Yes.  There are some things that you have to keep in mind.  So it changes the file.  This, for example, means that if you have some computations in the file that Excel would  trigger automatically once you have, for example, included a pivot table, you will need to trigger  in Excel again.  So you cannot assume that everything that would happen in Excel will also happen if you  create the file outside of Excel.  That makes things well.  You can.  Yeah.  You can.  The question is, does Excel do it?  So with OpenPixel, yeah, you can change the data.  You can force the pivot tables to update.  And you should be able to copy and paste an Excel chart into pivot, into PowerPoint.  Okay.  In this case, we can talk about this because this would be extremely interesting.  It's not directly possible, but you should be able to take the spreadsheet drawing, dump  the XML, and attach it into a PowerPoint file.  It's not that difficult.  Okay.  Yeah, that's slightly lower level than, well, I went with this.  If you want to work with templates, it should all be doable.  Okay.  I'm the OpenPixel maintainer, so I know this stuff.  Thank you.  We have time for one more question.  Sorry.  Someone here had a question.  It was basically also the same question about how to interact with an open instance of Excel  PowerPoint.  And yes, there is this Excel wings, which does this for Excel, and you always can do it via  com, but that's quite cumbersome.  I'm not aware of anything which can do the same with PowerPoint, for example.  And if someone knows something, just talk to me.  Have you had any experience with a word in templates?  And if you put text in it, if the text is holding the template styles and also the talk  I'm having, have you some experience with it that works well with Python?  Sorry.  Hardly did any work or anything with Word files?  Okay.  I want to thank again, Stefan, for a good talk.
Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_0_59.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_60_131.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_132_1655.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_1656_2927.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_2928_4283.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_4284_7247.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_7248_9491.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_9492_11099.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_11100_13583.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_13584_14387.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_14388_15359.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_15360_15815.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_15816_19163.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_19164_20747.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_20748_23543.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_23544_24611.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_24612_26927.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_26928_27635.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_27636_29027.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_29028_29147.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_29148_30059.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_30060_30431.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_30432_30947.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_30948_31643.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_31644_31751.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_31752_32039.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_32040_32459.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_32460_32855.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_32856_33263.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_33264_33971.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_33972_34631.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_34632_35411.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_35412_35771.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_35772_35819.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_35820_35867.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_35868_36347.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_36348_42407.png Stefan_Baerisch_-_The_Boring_Python_Office_Talk_Automate_Powerpoint__Excel__and_PDF_with_Pyt_42408_42511.png
Thank you everybody. So, well, I am impressed. So it is Friday on the conference. It is after the coffee break and you actually came as volunteers to talk that it's boring. Now there may be some confusion in the restroom if you want to have a nap, it's over there. It was the one day before, at one door before. Yeah, why is a boring office talk? So well, truth be told, a couple of years ago I switched to the dark side. So I'm mostly do project management nowadays. And as you know, well, there's a lot of excellent PowerPoint involved. And so I'm on my little quest to automate that. So I looked for available modules because there are always, well, a couple of modules available in Python. And in this space there are quite a lot. And what I would like to do is first of all, well, motivate you while boring things can be interesting. And then to give you a short introduction on what's available and how it could look like, hoping that you will then start to do your own, well, boring stuff with these modules and maybe with some insight from this talk. So motivation, some things should be boring. I like boring. So I don't have to think at all about the, well, equipment in this conference center. I don't have to think about the plumbing at all. It just works. Because when I have to think about the plumbing, there's a problem. So, and I would like to have the same situation for, well, office files, because they are, to a certain extent, the plumbing in most organizations that I know, they get sent around, somebody looks at them, annotates them. So, well, a way to communicate. And I want to make this boring. I don't want to think, okay, I'll have to copy and paste a lot of files, a lot of images, a lot of pictures, a lot of PowerPoint slides at Friday 8 p.m. just before, just before I hopefully can go into the weekend. That's something that we should do automatically. Which allows me to introduce my three characters. They're totally, well, archetypes. So we have the boss. The boss, as always, wants an Excel file, hopefully with nice numbers, but, well, in doubt, an Excel file with the right numbers will have to do. And we have, well, this little guy here. So he's working in office. He's the only one with a tie here. And he mostly works copy, pasting, combining, doing Excel files. So we'll learn what he does later. And he has one co-worker. And his co-worker is a Python-knowing pragmatist. So maybe he studied physics, learned about Python, now started a job. And while he knows all the files that has to be produced, she knows how to automate it. And he knows that he wants the files. And again, what do I hope to accomplish with this talk? I'll have to warn you, the code on these slides, if you compare it with what you've seen in this conference, it's not pretty. It's API code. And it's API code that is pretty closely aligned to how the file formats work. So you will see lots of calls that, well, enter some data, manipulate some data, do highlighting. This is not something that is really exciting. It's just useful, well, as the talk applied, it's boring. So what you should get out of this talk is module names. There are a lot of different modules that are available. And a rough understanding, how much effort you would need to invest to, say, have a nicely highlighted Excel file, have a PowerPoint with a chart, have a PDF file that looks like your PowerPoint file. And with that, you can maybe just convince people at your workplace to let you play with pandas because you can do calculations in a repeatable way and then create some, let's say, business-conformant files from that, that your boss can then take and edit around. So ultimately, what I wanted to do is when you maybe sit down on Monday, when you talk to somebody and say, okay, I could send you an Excel file. And whether it's a CSV file, you know, you always have to think on how to really open that. An Excel file is something that you know. And I can even do a PowerPoint for you. And that, once you have automated it, will be easy with Python. All the code, which is not much, is on GitHub. I will also, there's also the slides on GitHub right now. As soon as I have figured out how to upload the slides for the conference, you'll also have some there. So you can take this and hopefully, well, find something interesting. Another word about the philosophy about how I do things for this talk. I've learned a lot about good and best practices in using Python. This is, I tend to follow it, especially when I start more or less a quick and dirty approach. So if you think about automating things in your work, you may be, if you have a software engineering background, you may be tempted to think, okay, I will need a full testing framework. I will need to have this in Docker. And it should be a web application. And there are some extra parameters. And for things like that, I would advise against this. What you're trying to do is just save you some work to get started, especially, this is in Ubitalk, if you haven't done any large Python and programs before, I think this is a very good way to get started because you can do small things and then you can check whether they are correct. And this is the, well, basic idea. So what I do, for example, is just to, with exactly these APIs, is just combine some time sheets. So we have multiple consultants working on a project, each have their time sheets, I basically copy them together and see whether the numbers fit and then put, I'll put them in a different format. It is not especially challenging. It's I think 50 lines of code. The good thing is I don't have to think about what I've done every month. I just want the same script and things are pretty much okay. So what do we want to do? We have just three tasks in this talk. First of all, we have multiple Excel files. The Excel files here are stand-ins for any data you might accomplish. It might be a CSV file. You may read something from a database. You may have your little web crawler that checks pricing information on the Internet. So you have multiple data sources that you want to combine. That's fine. Then you want to transform them a little bit and write them out to some other Excel file. So far so good. You can do all this with the, let's say, Pandas standard API. And then you want to make it somewhat prettier, some highlighting, some specific Excel features that you or your organization might like. Then we go to the multi more presentation focused stuff. We built some PowerPoints. Once we have the PowerPoints, we will turn those into PDFs. That's it. The interesting thing about this particular task is that there are many, many good tools available. So a personal favorite, I think of all its Pandas. It reads almost everything. It does almost all kinds of calculations and it exports also quite a lot. It has a nice Excel export, for example. Then you have with Excel write out open pixel to API are two modules that are very good at working with Excel files. This one, hence the name, can only write them. This is the one that we are going to use here. Mostly for, let's say, taste reasons. I like the API slightly better. Open pixel can also read Excel files. So you can read something in, change just a few numbers, write it out again. For PDFs, we have PDF read write, which we just used to combine some PDFs. There is a gigantic number of PDFs library available for Python, depending on what you want to do. So you'll probably need to invest some time. What is quite nice, but quite big, if you wanted to do custom reporting is rapid lab. Again, something you won't see here, but just to give you a point of this is, well, if you want to do PDFs with Python and want them to be more complex than you want to see here, that's fine. I will use LepreOffice, basically in headless mode, to do some file conversations. And then we have Python PowerPoint, PBTX, which, as the name suggests, does PBTX files. Okay, the overall structure is, if you look at the GitHub repo, it's just the main method. So we read data, we do the extra format, then we do some nice output, then we output the PowerPoints, and then we do the PDFs. So if you check out the code, that should basically all work. It should have a couple of files, if not, let me know, in which case, most likely something went wrong with the share. Okay, what is our task? These are extremely made up and boring files. So the idea is we have some project team, this project team has expenses for materials, they want to build a mass station, and they have the working times. And well, everybody has to be paid, so they have some expense per hour. So these are three files that you got, maybe from your accounting department, maybe from your project team themselves. And now you want to combine them and do some reporting, because you want to know what actually my expenses. So first of all, you work with pandas to load in all these files. So you have three data frames, and well, we're not really much to see here. So we have these Excel files, each of these Excel files only has one worksheet. Okay, now you have three data. Then you want to do something that you would normally done by copy pasting an Excel. So if you work with Excel, well, you can do it. Who loves pivot tables in Excel? Okay, that's for people who uses pivot tables and have some rumbling. Okay, so I will assume from that that you would enjoy to use Python slightly more. Well, there is practically pivot. So what we do is the same thing that we most likely do in Excel file. If you can't follow this, I don't think that you should be concerned. Just know that everything that you could do in Excel, you can also do in pandas. And thankfully, especially from the MPI data people, there are a lot of very good, you know, pandas tutorials available. So I just walk you through what we are doing here so that you see the AP is quite, well, beginner-friendly. And the documentation is excellent. So whatever you want to do, I think you can Google yourself to success. Okay, so what we basically want to do is we want to combine these two files. So we update, we just start from some, starting at some end time. We calculate overall cost by rate. So we combine the rate and the hours per update table. This is basically this part here. Then we add some extra column just to say what cost type that would be, and then we combine them. So this is, I think, a pretty standard copy and paste job. And as you can see, it's not so much code. So if you wrote this not for demonstration purposes, it's most likely the same code up here into that copy here. So maybe 10 lines of code. So that's nice. We have the calculation. And the calculation is now copy and paste proof. If you have to do this multiple times, you can do it multiple times. You can even do it on a server. I think it's nicer than doing manual. So this is when we end up with, so we have different cost positions. Where do they come from? Is it working time? Or are these expensive? Who is responsible for it? We have, well, two projects, team dinner and mass colony. So it's two rather, let's say, ambitious things. And we want to output this to an Excel file. So this is almost the un-pundit standard we have doing it. So in pandas, you can do two things. You can either go to Excel and write out a single Excel file, or you can initiate your own Excel writer. And the Excel writer will make it possible to have slightly more control. Particularly here, you will create, well, different worksheets. So these three worksheets all going to the writer. And you can give them a cheat name. And now you have, well, some control over your Excel. But it's basically still all data. So I think it's not really management-friendly quite now. So first of all, what we want to do is we want to have some introductory sheet into our Excel. We want a company logo, a version number, and maybe some formatting. And to do this, you take a workbook object. You can get the workbook object from the writer object. And the workbook represents, well, basically your Excel sheet. And you can then define with Excel writer some formats. And there's a large number of different properties that you may want to use. In this particular case, I just want to say I want to sing in blue and slightly bold. This ends up here. So I have now created a new format and now I've write it. This 0, 0 means row 0 and line 0. The API also has these Excel-specific coordinates. So if you want to go A1, is it where you can also do this? I'll write my text. And bold in this case is this format object. So I'll write this out. I can also go unformatted. So this over-cost thing is here. And I can play around a little bit with the column width. So setColumn takes which column it should apply to. So first column to first column. We could also go multiple columns. And it says which width do I want to have in terms of characters. And I'll write this out. So I have my first formatting. And I can include an image. Well, and include typos, but that's another thing. OK, so what we have done so far, we have output an Excel straight from Pandas. And we have created a new sheet in this Excel. OK. But, well, what do we want to do if we need slightly more control? So for example, if my data is not in a Pandas dataset, or if I have some other data that I want Mike to add in a arbitrary position. So you have two methods. We see the first one here. Well, you have multiple, but you can write whole rows into the system. So what we do here is just we take our columns, add them here. So we have a header. And then we add the raw information in the different rows. So now I have control over how it updates. So if you have something in a dict or wherever you get it from, you don't need to, well, assume that you do an export just from Pandas. Now, this still looked rather drab. So what we want to add up is something a little bit nicer. So what you see here is more manual control over what you are doing. So first of all, we're building a worksheet. And then we add two formats this time. And you see the different properties that are available. Think bold. We already used. We have different font sizes and different colors. Then we want to write the column information out. Again, we apply one of these font information, one of these styles. And then the next thing that we want to do is to change the column widths again. So this happens here. And then we want to write out our data. And this time we do it really data element by data element. So we don't use the raw function, but just go over all our data. And what we want to accomplish is we want to change everything that is higher than the 75th percentile in the data in red. So we just use pendants to get this particular value. And then we go over F we row in our data. We look which column we are because the first column is data. That is a number. The rest is text. And if we are above the percentile value, we just apply red bold. So it's the other style that we defined. Then it looks like this. So it gets slightly more interesting. So you don't have these desert of numbers anymore, but you can hide that number that have specific interest. Now, those of you who like Excel too much, like me, might notice, okay, but there are conditional formats in Excel. So I could just, if I do this by hand, I could do all that in Excel. And in terms of highlighting, I could maybe do it as a table. That's also possible. So you can just say, okay, I want specific number formats like Excel wants them and define those. And I can define part of my Excel sheet as a table. So, five. Okay, so in order to do that, you just go for this data. And then what you can also do is say, I want conditional highlighting. Conditional highlighting, if you can own Excel, you work, you can define custom rules. And there are quite a lot of complicated properties that you can use, but my old time favorite is just this, three color scale. So it basically gives you a traffic light. And you can apply those again to a specific part in your spreadsheet. In this case, it's the same part that we used for the table. And you have a nice colorful Excel sheet. Okay. We'll have to speed up a little bit. So I'll just be quick. You can also create charts. Charts are the most ugly part in the API, mostly because of this part here. Because what you do is just here, you add all the data to the table. You need the data on the Excel sheet in order to create a chart. Excel needs to find them somewhere. And then you create two series. Each series has a name. And in this case, it could be, well, the expense information has a category. That's the person who is responsible for the price. And, well, once you have this, you can just create a chart. You could also go for mudplot lib at this moment. So it's say, we already saw earlier that we could use pictures. Yes, you could. The advantage of this one is that it is an Excel chart. So it's a digital. You can change it. OK, so much for Excel. Now we'll just go over the PowerPoint thing. The PowerPoint module, as I think, is wonderful. So basically, everything you can do by hand, you can do with a module. For Excel, there are still some limitations. So I didn't find a way to do pivot tables in the form of the Excel.expect them. So the thing about PowerPoint is one difference. You should probably prepare some template that you want to change, because you have a lot of formating information that is not necessarily required for the Excel file. And then you can just go and fill out placeholders and write out, well, ugly tables. In this case, on purpose, that you see that I did something myself. So I said, I'm not a designer. And same thing. You can add pictures to it. You can add tables to it. Here you will see that we use this module pandas to PowerPoint. It's not pip installable, but you'll find it on GitHub. It's a couple of hundred lines. And again, you can add charts. It's basically, well, the RP is close to it. There are a lot of magic numbers in here. And you're probably best off starting to copy the code and then change it. So it's not the nicest code. But it is useful. So I want, again, get a native PowerPoint object. One thing I didn't figure out so far is how to take the charts out of my Excel into my PowerPoint, like I would do with copy and pasting. Anybody have the hint there that would be helpful? OK, now we have also the PowerPoint. If you want to change this into PDF, you need something that's able to render PowerPoints. I didn't find a pattern module to do that. Luckily, there's LibreOffice, and LibreOffice has a command line mode. So what we can do is just automate the command line mode. Some of you may be familiar with that process. So what we do here is just build a command line, look for the executable, add some options. These are the ones that are interesting, and then, well, execute it. And at least on the Mac, the version that comes out of the PowerPoint, and the version that isn't the PDF, look 90% the same. So that's quite practical. What you can also do, this PDF ReadWide, is just read an existing PDF file. So let's say you have a title page, an outro, and some pages that you build. So you just read those in. And then you create a PDF writer. Then you can just say, take this page, add it to the output, take this page, add it to the output, and many things more. OK. And that's basically all we've done. So we have now seen that it is possible to create nice-looking Excel files. Some people like this. We have seen that it is possible to create PowerPoint slides. So my personal dream scenario is that in some kind of the future, I will be able to create gun charts from Excel files. That would be great. So you're working on that. And you can even create PDFs for archiving or whatever you want to do from those files. So there are some things that you can look into and learn more. Especially if you are new with Python, this book is nice. It's nicely written. And the HTML version is free. If you want to have a deep dive into the wonderful world of PDFs, then you could also do this on the Web App Lab page. I think this is the sign that I should shut up now. And the documentation for each of these modules is excellent. I mean, we're excellent. So the people who did those modules, it's almost heroic. It's not really the nicest work to do that, but they did it really well. OK. And that is it. Thank you. Yeah. Honestly, honestly, no. So I think what you'd like to do via com. I think there's also now a product. I think it's Excel Wings where you basically embed Python into Excel. Those are also available for the Mac. And the reason that I did this, the way that I did it, I just wanted to have a script that runs without opening Excel because it, for me, makes it debugging so much easier. Pivot tables will be preserved in OpenPixel. So if you have existing complex Excel files with lots of pivot tables and you just want to change the data, use OpenPixel. Yes. There are some things that you have to keep in mind. So it changes the file. This, for example, means that if you have some computations in the file that Excel would trigger automatically once you have, for example, included a pivot table, you will need to trigger in Excel again. So you cannot assume that everything that would happen in Excel will also happen if you create the file outside of Excel. That makes things well. You can. Yeah. You can. The question is, does Excel do it? So with OpenPixel, yeah, you can change the data. You can force the pivot tables to update. And you should be able to copy and paste an Excel chart into pivot, into PowerPoint. Okay. In this case, we can talk about this because this would be extremely interesting. It's not directly possible, but you should be able to take the spreadsheet drawing, dump the XML, and attach it into a PowerPoint file. It's not that difficult. Okay. Yeah, that's slightly lower level than, well, I went with this. If you want to work with templates, it should all be doable. Okay. I'm the OpenPixel maintainer, so I know this stuff. Thank you. We have time for one more question. Sorry. Someone here had a question. It was basically also the same question about how to interact with an open instance of Excel PowerPoint. And yes, there is this Excel wings, which does this for Excel, and you always can do it via com, but that's quite cumbersome. I'm not aware of anything which can do the same with PowerPoint, for example. And if someone knows something, just talk to me. Have you had any experience with a word in templates? And if you put text in it, if the text is holding the template styles and also the talk I'm having, have you some experience with it that works well with Python? Sorry. Hardly did any work or anything with Word files? Okay. I want to thank again, Stefan, for a good talk.
to talk that it's boring. Now there may be some boring things can be interesting. And then to then start to do your own, well, boring stuff an Excel file with the right numbers will have to automate it. And he knows that he wants the closely aligned to how the file formats work. So just useful, well, as the talk applied, it's is module names. There are a lot of different file, have a PowerPoint with a chart, have a PDF business-conformant files from that, that your Excel file is something that you know. And I can have automated it, will be easy with Python. All automating things in your work, you may be, if the, let's say, Pandas standard API. And then you you or your organization might like. Then we go task is that there are many, many good tools at working with Excel files. This one, hence the give you a point of this is, well, if you want to do PDFs with Python and want them to be more structure is, if you look at the GitHub repo, with pandas to load in all these files. So you you want to do something that you would normally have doing it. So in pandas, you can do two possible to have slightly more control. that you may want to use. In this particular we're building a worksheet. And then we add two properties that are available. Think bold. We be quick. You can also create charts. Charts are so much for Excel. Now we'll just go over the can do by hand, you can do with a module. For because you have a lot of formating information that is not necessarily required for the Excel can add tables to it. Here you will see that we nicest code. But it is useful. So I want, again, from Excel files. That would be great. So you're The question is, does Excel do it? So with well, I went with this. If you want to work with with it that works well with Python? Sorry.