Automated Excel Bot in Python

Key Takeaways

what is going on guys welcome back in today's video we're going to learn how to build a simple automated Excel bot in Python so let us get right into it [Music] all right so we're going to automate Excel processes and python today and we're going to build an actual automated bot which is different than just using python to work with EXO files I have made a video on this already on my channel as well uh today we're not gonna just parse Excel files create Excel files and work with them in Python we're going to actually control the actual Excel application so we're going to use something called com which is a mechanism in Windows that provides us with the interface that is language independent that we can use to Target the EXO functionality and we're going to automate we're going to control the actual Excel application using python to do whatever we want to do which is different than just using python as an EXO file parsing machine or manipulation machine we're going to actually control the Microsoft axle software in today's video and for this on Windows we don't even need to have any external libraries this works out of the box we can just use the core python modules we can start by saying import OS and import win32 com which is the module that we need to use here dot client and we're going to give this an alias of win32 this is what we're going to use and we can start with a very simple thing we can just Target the Excel application create a simple worksheet and then just save it without any content without any action and for that what we do is we say Excel equals win32 dot dispatch and here we pass a string Excel Dot application this targets the axle application and then we can also set a parameter called visible to actually see what we're doing so you can automate the Excel processes without seeing the results and without seeing the progress but if you want to see the application what it's actually doing you want to say Excel dot visible with a capital V equals true and once you have that you can say workbook is equal to excel workbooks dot add with a capital A and here we then just say workbook dot uh safe as as you can see all these methods all these functions start with capital letters which is not typical in Python but this is the com interface so those are the methods here um and we now save this just as a file now one thing that you need to keep in mind here is that if you just provide something like my file.xlsx this will not be saved in your current python directory now here I have one from the test run that I did before but it's not going to be saved here in the directory that you're working in it's going to be saved in the directory that Excel is working in so if you just do this it is usually going to save the file in the documents folder of your user if you want to store this in your python directory if you want to save it into your python directory you need to say OS dot path dot join and then you're going to pass this year as the second parameter and before that you say OS dot get current working directory so get CWD and this essentially now takes the directory that you're currently in with the python script and then appends to it the file name and then you save it there so in this case in the current directory that we're working in right now so if I run this now you can see that Excel opens up down here now actually I had it open already so let me just restart this let me delete this now if I run this you can see Excel opens up down below we have this file nothing changed yet but we can see my file.xlsx is saved so the actual application was run and we we open it up we save the file and that's it now we can do basically everything that we can do with the axle software by using that interface so we can use the functionality of the axle software I'm not going to cover every single bit of functionality that we have here I'm going to cover quite a bit here but still if you want to have a functionality that I didn't cover you can look up the com documentation of the Excel application and then you can see that basically you can control the whole software using Python and using these com interfaces and what we can do now for example is we can say sheet1 is going to be the sheet the first sheet that we have down here so if we open up the file you can see we have sheet1 down here so we can say that we want to get that sheet by saying workbook dot worksheets and then want to have sheet1 and then we can change the name of this sheet for example we can say sheet1 dot name is equal to um to do list let's let's make it to-do list in this video and then we can also format a little bit we can say for example give me the columns how many columns are we going to use let's say we're going to use four columns and uh I want to have a certain width of those columns so I want to say the column width of those columns has to be at least or has to be exactly 30 and then I have more space for text so what I can do here is I can say sheet1 range and I can say a to d which are columns A B C D and I can say dot column width is going to be equal to 30. and if I now run this in this case you will see first of all it says we have this file already if I allow it to replace the file everything will work as expected if I say no let me show you this is going to give me an exception so if I say no I'm not able to do the save as operation which basically terminates my script if I run this again now let me just close these windows here let me just close them and if I run this again here and I say this time yes you are allowed to replace it then everything works as expected you can see this is now now called to-do list and we have the column width of 30 here for those four columns now the problem is of course I didn't save afterwards so if I now just say don't save and I open up the file the changes are not in the file so it still has the name sheet1 because we didn't save uh the progress we didn't do this one again uh but yeah this is some basic formatting let's just change the value uh the values of the individual cells now this is quite easy basically we just get the cells of the sheet by saying cells is equal to sheet 1 dot cells with a Capital C and then we can use that uh in we can use that like an array but not exactly because we're not using square brackets we're using uh we're calling the cells actually so we're we're actually saying cells and then we're calling cells with these parentheses and then we specify one a for example and we say that the value of this cell is going to be equal to the task so the to do task and then I can do the same thing here for a b c d I can say B not V sorry B C and D and then I can say here okay this is not the task this is the description and then I have uh this is the we can say this is done this is the status of of the task and then we say time needed either expected or in the past how long it took and what we can also do is we can format these cells we can say cells a 1A and then dot font dot bold is going to be equal to true and this basically makes uh the content of the cell bold and we can change this here to B to C and to D and then basically we get the result um there you go so let's also go ahead and add some values let's say here's cells and of course this can be automated with a loop if we have lists of values and all that but we're going to do it now manually quite simply here uh dot value will be something like dinner and then we're going to copy this here to B C D so dinner cook dinner is the description then we have done no and we have um the time needed I don't know 40 minutes or something like that or let's let's just go with 40 and let's maybe keep this empty here and only add an X if we have it done already now we can copy this we can paste it down here and we can change this two to a three and then we can change the tasks here clean room clean your room or in this case my room since this is a to-do list let's say this is done already it took 20 minutes or something because it was already quite clean and let's maybe add two more of those um which we're going to have for and then actually let's just keep it at four so actually three let's say this is now work out go to the gym this is not done and this will take on 120 minutes because yeah why not now let's just go ahead and see what this produces in the Excel program we can open this up we can say yes I want to replace it and then you can see it fills up the actual content right before our eyes we can see we have the task the description the done status the time needed and we have the values in here you can see we have the Bold font so this actually worked and it was controlled by python especially because the file already existed you can see since we have the the possibility to pause here and and nothing has yet happened if I click on yes you can see how it does everything on the Fly here now let's say we want to have the sum of the time needed down here as a formula this is quite simple this is the cell 5D or D5 so what we have to do is we have to just say cells 5D dot value is equal to and then sum actually equals some uh the simple Excel formula essentially This was um D2 up until D4 and if I run this now this gives me the sum down here 180 you can see the formula as seen here so this is the most basic stuff right this is nothing too complicated um and there are a bunch of different things that you can do like that you can use um fancy functions you can use formatting of All Sorts one thing that I want to show you in this video in particular because this is a little bit different is adding charge so actually taking the data that we have here and turning it into uh into actual graphs this is something quite interesting I think for most of the stuff you can just look up the documentation and I don't need to show every single feature off the API of the interface here but the charts are a little bit special so I want to show them here uh we're going to just create a simple chart I'm going to call it CH and I'm going to say sheet 1 dot shapes dot add chart dot select so this is the chart and now what we can do so basically select means we're creating the chart and we're selecting it because in the next step we're going to talk about the active chart and the active chart is the one we select so this is why we use the select function and here we say now Excel DOT active chart dot set Source data so in Excel let me show you manually how this is done now we don't have values here but let's say we have two three four five I take these values here I go to uh what was it was it insert there you go I pick a chart type like this one here and this already has this as the source right so I can um I'm not very good at XO to be honest but I think I can change select the data here and here I specify what data is the chart representing right and we do the same thing now in Python by saying the source data that we want to plot is exactly the data that we have in here D2 up until D4 just the time it takes for it doesn't really make a lot of sense to plot it in this case but we're just going to do it here for the sake of the demonstration so the source is going to be sheet1 dot range and the range is as I said D2 up until D4 and the important thing is here now if we just do it like that let me just let me just finish uh the thing um or actually let's do it right now the problem is that you have two types of plotting um plotting the graph you can do it column wise or row wise um and essentially if you don't specify plot by equals two if you just leave it at the default value you're not going to get what you expect you would get so you don't get what you get automatically if I select D2 to D4 in EXO and I create a new graph it's going to actually do what we do here in Python if I say plot by equals two the default is I think one and it doesn't do what we want to do so this is just a different way of interpreting the data and you need to pass it here in order to get the plant that we desire maybe I can show you that in a second here if we don't do it uh what it's going to look like but before that let me show you here that we can use different charts obviously because up until now we have not specified what kind of chart we want to do do we want to have a pie chart do we want to have a a simple line chart hardware and essentially what you want to do here is you want to go to the documentation to the Excel chart type I can link I can I can put the link in description down below and what you do is you say chart type equals and then you specify the number and I have the link here actually so maybe I can open this up in the browser let me see if I can do that there you go opens it up on my second screen so here we have the Excel chart type enumeration from Exo and if I scroll here you can see this in the middle is the value this is the name and this is the description and right now what we're going to plot is uh 64 or 63 yeah stacked line is what we're going to plot here um so this is the chart type that we choose we can just say 63 and this is going to be a stacked line chart this is what we want to plot in this case so what I do then is I'm going to say here's save as so that we have the actual result again OS path join uh OS get current or current working directory and then my file Dot xlsx and then I can run this here and say yes and say Yes again and in this case you can see we have the line chart so this is the Stacked line chart which is based on these three values so you can see that those are the values that the chart is using um let me show you what happens if we don't specify plot by equals two because this is actually something that you don't want to happen because what this is going to do is as far as I remember this is going to to interpret the individual numbers as individual Series so we have one series another series and another series and they don't have anything to plot right so you wanna swap the way it is interpreted by saying plot by equals two plotted by equals two and I think that I actually have a link to the documentation for this one as well you can see here uh plot by one is data Series in a column and plot by two is data Series in a row and this is the one that we're choosing here in this case so this is the documentation again if I forget to put it in the description down below first of all let me know in the comments so that I remember and maybe I'm going to put it afterwards into the comments uh into the into the description otherwise if for some reason I'm not reliable and you don't find the link in description you can just go to learn Microsoft learn.microsoft.com whatever language slash office vwa Visual Basic API Excel and then whatever or you can just Google com API Excel so this is the full documentation essentially now what we can also do here let's go back to this um chart type here I want to show you also the pie chart and as far as I have it here in my code this is the number five so XL Pi is the number five so we can just go ahead here and say okay this is a pie chart um and the rest actually stays the same so this should already be enough here um replace replace and this makes a little bit more sense in this case because the time needed is something that you probably want to plot in a pie chart because what how do you interpret the lines it doesn't really make a lot of sense because it's not a Time series but here you can see which task takes how much time and you can of course customize it with a legend and all that but essentially this is how you build a simple Axel bottom python you just take the API you take the functionality that you want to automate um some functionality is quite simple like filling up cells with formulas um this one is a little bit different you have to set the source data in the right way you have to choose a chart type but everything is well documented and documented in the API documentation and I think this is a little bit at least if you're on Windows I prefer this way of automating excel than just working with EXO files and python because you're using using the actual Excel API which is I think more convenient so that's it for today's video I hope you enjoyed it and hope you learned something if so let me know by hitting a like button and leaving a comment in the comment section down below and of course don't forget to subscribe to this Channel and hit the notification Bell to not miss a single future video for free other than that thank you much for watching see you next video and bye foreign [Music]