Introduction to OpenpyXL Library [ Python and Microsoft Excel ]

[Music] hey guys hope you're all doing well long time i haven't made any tutorials or courses that's because i've been very busy lately at work but here we go again with a new crash course for open pi excel before we get started just don't forget to hit that subscribe button and hit the like and share this video science has proven that sharing is caring alright without further ado let's get started you all must have worked with excel at some time in your life and must have felt the need for automating some repetitive or tedious tasks don't worry in this course we're going to learn about how to work with excel using python or automating excel using python we will be covering this with the help of the open pi excel module open pi excel is a python library that provides various methods to interact with excel files using python it allows operations like reading writing arithmetic operations plotting graphs and many other excel operations this is the pipe.org website and here is the open pi excel module page you get a little bit introduction here about the security and if you want to read the documentation you can go here and read everything about open pi excel this module naturally doesn't come built in with python it's actually a third party library so to install the module globally on your system you can go ahead and type pip install open by excel however what i recommend is creating a virtual environment always whenever you want to create a project create a virtual environment that way all of your dependencies and libraries will be isolated from your global system will be only in that folder or only in that project so what i do is i activate the ppnv using ppnv shell all right but um if you don't have that you can install it via pip install vpnv and once you do this you can activate the ppnv using ppnb shell as i showed you and then you can go ahead and say ppnv install open pi excel all right so this is the way to install open pi excel of course if you prefer to install it globally you can say pip install open pi excel or if you are a mac or linux user you can say pip3 install open by excel alright so let's go ahead directly and start our project so know just where we are all right let me just go to the desktop and let's create a folder i will call it open pi excel course okay and let's open that with visual studio code so let me actually open the integrated terminal and let's go ahead and activate our virtual environment ppnv shell okay good now let me install open pi excel module using ppnv install open by excel command okay perfect let me just close this and the first thing that i want to show you is how to read from a spreadsheet so to do that let's go ahead and create an excel file a workbook excel microsoft excel worksheet okay and we'll call it net sales let's open that and here i will have name employee number maybe sales volume by a thousand pieces and sales value in in dollar okay just adjust this all right and let's have some random names mina ahmed dina nancy and joel all right you can can do anything you want really you don't have to stick to that pieces 200 150 400 234 549 sales value 1000 all right uh that's all right okay so we have our net sales table here for our sales reps with the volume and sales value in dollar right just like that so we're going to work on that file we're going to read values from that excel worksheet okay we have sheet 1 in that workbook let's get back to our code and let me have here a file i'll call it netsales.pi and here we'll have our code so the first thing you want to do as you might have guessed correctly is to import the openpi excel module so we're going to open pi to import open pi excel this is the first thing that naturally you will need to do right because it has all the characteristics the attributes and methods that we will need in our in our application or our program the next thing that i want to do is to give the location of the file or to give the path of the file so i'm going to create a variable called path and the name of the excel file we said netsales oh we didn't actually just put it here inside okay so the path of that excel file will be net sales dot xl oops xlsx okay so this is the path of the file so to open the workbook we will need to read the excel file that you have to open the spreadsheet we have a method to do that called load workload okay so let me actually create another variable i will call it wb underscore opt or workbook object and this is equal to the open pi excel library or module dot load underscore workbook and you can see immediately the suggestion to use that method and we will pass inside our path this is the active sheet right so in order to use the active sheet there is an attribute called active okay so i will use or i will create sheet underscore obj which is worksheet object and this is actually going to take the workbook object oops workbook opt dot active now i have selected this activated worksheet okay this line is telling me hey python i want to choose that activated worksheet in that workbook object which is this one next what i want to do is actually to choose this cell a1 and this is actually takes two parameters the column and the row or the row and the column respectively so row one column one which is a1 okay so i want to choose cell a1 to do that in python is very very easy so let's create a third variable called cell underscore object and this is equal to sheet obj dot a method called cell or object why i said why i missed just the j so the cell obj is actually the cell inside that activated worksheet okay so the method where we're going to use is called cell and it takes two parameters or two inputs right the row and the column row is equal to one because we want row one column one which is cell a1 as we said and also column equal to one and if we're going to print that cell opt dot value let's actually delete this one python netsales dot pi we will get name which is exactly the value of cell a1 and as you can imagine if you repeat the same thing while changing the row will be the same if i want to just print employee sales volume and sales value row will be always 1 but column will change to 3 and 4. so let's change that two three and four save let's execute that one more time and let's repeat that three times one two and three save execute again and there we go we have name employee number sales volume and sales value all right so this is how to read from a single cell in a worksheet all right now we have seen how to read from single cell in a worksheet let's go ahead and read from multiple cells let's get back to our excel file and here if i want to read from multiple cells like a1 b1 c1 for example we can use the values inside the for loop to get the value of the desired row or column or any cell depending upon the situation of course and to do that we can get the count of the total rows and total columns respectively so we can use these values actually inside a for loop to iterate in that range in order to get the value of the desired row or column or whatever cell you want to get the value of alright so let's go ahead and see how to get the value of the first column and the first row for example okay so i've added just this doc string here and this one here so reading from single cell reading from multiple cells and don't worry if you're watching this over your phone or your tablet and you're not coding along with me don't worry i will leave the link to github for the code or the source code so you can use it later as a reference alright so first of all let's actually get the value of maximum rows and columns so here simply i'm going to declare a variable called row and i will take not show sheet obj and i'm going to access that property called max underscore row the same thing goes for column and instead of max row we have max column and if you want to print the total rows and columns we can do that simply by printing that value okay so let's go ahead and run that and here we have the total rows six total columns four let me just um comment these lines out okay so we have total rows and total columns okay which match exactly what we have we have six rows and four columns okay pretty simple pretty straightforward now what if i want to print the value of the first column so we can loop over the values of that column right so let's try to do that let me just have print statement here i'll say value of first column and here we will have a for loop so we'll say for i in range one as first parameter then row plus one all right so i want range one and row plus one so adding row plus one row plus one row plus one until the loop finishes okay next i will have cell obj and in this case this is going to be equal to sheet underscore obj dot cell method and the cell method takes two parameters as we saw a row and a column and we want the initiation so the iterator will be equal to that row and the column and the column is equal to one all right and then we will print the value of that so cell um cell underscore oops cell underscore object dot value all right let's try that out let me just clear the terminal all right so value of first column we have name mina ahmadinen joel which exactly is in the first column great let's apply the same ideology and return the values of the second row which will start from a2 okay so let's do that let's have the same print statement but instead column we'll say row all right now we will loop over the columns right so we'll say for i in range again one oops in range like that and here column plus one what i want to do i want again to create a cell object variable and don't worry this is um this is just private for that for loop so it's not public it won't affect anything with the same name um next what i want to do is to take the same thing sheet of dot cell and again i need a row and a column so in this case rho will be equal to 2 because we want the second row and column oops if i can type that column will be equal to the iterator it's exactly the opposite what we did here the row which was the iterator now i want to iterate over the columns so i will set it to the iterator the i okay and then we will print cell underscore obj dot value and then i will just put them on one line and i will separate them by a space like that okay let's try that out and there we go value of first row not first row actually this is this first row yeah it's the first row correct mina the employee number two hundred and one thousand dollars okay now we have seen how to read a single cell multiple cells or a range let's see how we can create and write to a spreadsheet so i have a new folder and i'm going to create test.pi file and uh let's just not forget to activate the virtual environment okay perfect let me just close the terminal now i want to import a class called workbook so from open pi excel i will import workbook and w in the workbook is capitalized okay and if you will hover over workbook um nothing is not accessed that's odd okay there you go so this is the class if you want to we just you want to read more you can just click it and read the details of that class but really we don't need to do that we're going just to instantiate an object or instantiate a class by creating an object right so we'll call it workbook equal to workbook domain class and then i will use my object or this variable dot save method and save is going as you guessed it save the excel file or the outcome or that newly created file in the same folder where your python file is so the file name here we will call it let's call it test.xlsx all right let's actually run that we need the one with the active environment and say python test.pi have you noticed that it has created test excel file in the same folder okay it's that easy but what if we want to add data right we need to add some data in that excel file so i'm going to import open pi excel just i like to organize the code this way all dependencies are above and then the rest of the code comes below then i need my workbook object so openpi excel dot workbook then i want to select that activated sheet right you remember when we did that before wb dot active and then i want to write different values on different cells so for that i will create two different variables okay let's say for instance x1 so x1 will be equal to sheet dot cell and i need row and column so if i want it in cell a1 then i will choose the first row and the first column and i want to define a value or write a value to that cell so x1 dot value and in form of string we can say hello and i can take both of them and i will change just the variable name x1 to x2 and similarly for the value and i want it to be in the same row but on the second column and instead of hello we'll say world like that okay let's go ahead and run that file and we'll need actually to open it from outside you can see here that nothing actually happened we didn't write anything and the reason for that is that we didn't save the file so we actually have written stuff in our code but we didn't save it it's similar to what you do control s in your excel file to save what you have written so let's do that here wb dot save and we're going to save on the same file we're going to overwrite that file anyway it's empty now i've saved the python file and i'm going to run the program one more time let's open that file again and there you go we have hello world perfect let's close that let me show you another thing so instead of doing so actually instead of doing cell and using row and column you can directly use or choose the name of the sheet that you want to enter whatever you want to enter so let's have x3 is equal to sheet let's choose a2 alright let's choose a2 and b2 sheet and i want a2 like that and the value of a2 we'll have the word happy for example okay and we can do the same thing we can choose both we can change the name of the variable and we need b2 now and we can say days happy days all right um it's always the save always should be at the bottom so we'll save that okay let's go ahead and open our accept file again and there you go instead of instead of choosing row column and passing all of that you just can choose the cell name okay a2 b2 whatever and you will see that every time you try to write to a spreadsheet the existing data gets overwritten and the file is saved as a new file so if i will delete all of that and i will just put in a1 a number and i will save when i will open it i won't find all of that i won't find only number in e1 and why is that because the workbook class always creates a new workbook file object okay so to write to an existing workbook you must open the file or load that file and there is a method for that called load workbook okay so let me actually show you so here if i want to add anything let's say x5 okay and x5 here will be in c2 for instance and let's give that x5 a value of 150 a number for example after importing the open pi excel let's directly import that file i will say workbook or wb open pi excel dot method called load workbook and that method will take that file that i want to open or to load so what did i do not buy xlsx that's why the terminal yelled at me it has yelled at me again because the file actually is opened so can do anything let's run that again it's good to see these errors just for you to know what's going on so you can keep the excel file open while adding and saving and python won't work freely now so let's open the test excel file and exactly what we needed to see in c2 we have the value of 150 all right so that's what load workbook does it loads the file it writes what you want to write and then it saves it and just ready for you to open it and work with it of course this is a very simple example and this is very simple tutorial actually this is a very beginner friendly tutorial for anyone who is just who knows a little bit of python a little bit of excel just to know the possibilities and how the two worlds can work together okay let's also try to add some stuff so let me actually create a tuple called data and that data tuple will have two tuples um the first one if i can type that the first double will have one oops the first tuple will have one two and three just simple integers and the second double will have four five and six so i want to iterate over that data tuple so say for row in data so i will say sheet dot append and i will pass the row inside here okay let's save that let's clear the terminal and as you can see here a3 and a4 one two three four five six respectively all right sweet in the next and final part of the tutorial i'm going to show you how to do some arithmetic operations on an excel spreadsheet what if we want to do some arithmetic a very simple arithmetic operation like sum of few numbers for example so let me create a file here we'll call it sum dot pi and i will do the same steps again import open pi excel again workbook is equal to open pi excel workbook and i will need from openpi excel open pi excel to if i can type at all today import workbook okay good and i want to select my sheet to be activated in the workbook so sheet is equal to workbook.active right and now what if i want to write different values in different cells let's say from a1 to a5 different values different numbers and i want to add them or get the addition or the sum of these values so let's do that very quickly sheet a1 equal to 100 and i did that four more times so 8 2 3 4 and five two three four and five okay and then in cell a7 i want the sum of that so we'll say sheet a7 is going to be equal to the formula that you typically write it in excel okay so what you write you write equal to sum and you select the cells that you want to add so here the same thing but i will wrap this formula in single quotes equal to some capital and i will say a1 from a1 to a5 and i will close the parenthesis okay and don't forget to save your work wb or workbook dot save and we're actually going to write and create in the same time okay so i'm going to call it sum.xlsx save and the moment that i will run that file sum dot pi i should see here sum.xlsx there you go created okay so let's go ahead and open that and there you go the numbers and then the formula you will see here you'll find that we have actually written an excel formula in python code so if you can do simple stuff like that in excel you can imagine that you can do much much powerful stuff using openpixel module alright so that's it for this tutorial guys i hope this was useful for you if you want more advanced tutorial please let me know in the comments section below and i will see you in the next videos until then stay safe and be well take it easy guys

Channel: Bek Brace

Views: 26,428

Keywords:

Id: hFDrWvDOYFA

Length: 34min 8sec (2048 seconds)

Published: Sat Feb 26 2022