Read And Write Excel Files In Python Using Openpyxl In PyCharm- Excel Styling and formatting Python

hey hyphen this is mukesh youtube once again from hyphen notification.com today in this lecture i will show you how you can work with the excel files in python so this video will consist of two parts first of all we will talk about how to read the excel files and then we'll talk about how to write the excel files so in the reading part i will show you multiple ways so first of all we'll load the workbook then we'll read one record then we will see how to read the multiple records as well and i will show you three different ways to read the excel data so depends on the requirement you can go with any of the way because at the end of the day we are just going to read the excel file right in the second part i will show you how you can write the excel file so while writing we will also discuss in case if you want to change the formatting let's say you want to change the cell to red green or some other color depends on the status or depends on the requirement you can customize that particular set so it's going to be little lengthy video but trust me after this video you will have clear idea how to read and write excel files in python okay so let me show you step by step in case guys if you're new to this channel then make sure you subscribe this channel and let's continue okay so now let's see how do we work with excel files in python so whether it comes to reading or writing we can do both the things in python okay so let me show you the different libraries which exist in python in order to work with excel okay so just go to google and search for python excel library okay so the moment you hit enter you will see a site called python hyphen excel dot org the moment you click on it you will see we have a different libraries or i was a package is available which will help you to read and write excel file okay so you can see we have a list of packages that we can use so i can go ahead and use open pi excel so as you can see this is going to help us to read and write excel 2010 files in short dot xlsx file okay and you can see we also have few other packages so few packages will only allow you to read and few packages only allow you to write but open pi excel will help you to read and write both with excel file with dot x extension x extension so you can see now we have a download link we have a documentation and we have a bit bucket link as well so the moment you come here you can see first of all we need to install open pi excel okay so the moment you install this package you can start reading and writing uh excel files you can see it supports all these extensions so right now i'm going to deal with excellence x extension and you can see they also have given the sample code how you can read and write the excel sheet it's very very easy you just need to import the workbook and you can start reading the data and you can start writing the data and finally you can save the file very easy i will show you in few minutes and this is their official documentation again introduction followed by mailing list and sample code and the company documentation and this is the you know the complete version history and this is their bitbucket link so right now i will quickly take few examples and i will show you how you can use it so first of all we need to install this open pi excel so either you can copy from here or you can just go ahead and click on terminal and just type eip install okay and just type this open pi excel so open pi excel hit enter it will take a few seconds and it will install this package in our local system so you can see it is installing uh some packages so it is installing this one open pixel along with that it also installed a couple of other packages which it requires okay now we are done with the package and in order to show you the examples you can see i already have this excelis x file so for the demo i have created this separately so if i show you this excel right click click on explorer so you can see i got this folder and this is the excel sheet so let me show you first of all the extension extension is dot xlsx and if i simply open this excel it has multiple data so you can see this is my sheet one which is names which has three columns name address number and i have two records okay so first row includes name address and number and these are dummy data you can put any data just to get multiple varieties i have taken a string and integers i have another sheet where i have taken a string and i have taken k numbers let me take this number as floating numbers let me put let's say dot zero one or zero five doesn't matter same thing for another number i will make it let's say dot one zero so we have enough varieties of data and sheet three is blank we don't have any data but still we have three sheets so let me show you how you can read it so let's open pycharm and let's create our first python program in order to read excel so i will say read excel so let's import pi excel so i will say open pi excel and just type open pixel dot and you can see now we have multiple functions available right so the moment you come down here you can see we have one function called load workbook which is simply asking you the file name right so the moment you hit enter in the double quotes you need to provide the file name okay so file name you can see right now this is our file or this is our python file and this is my dot xlsx file which is our sheet so i can directly pass data dot xlsx in case if we have this data file into some other directory you can simply provide the path for example let's say that i have one more excel in this desktop so if i want to pass this particular file i can go ahead and simply paste the path here okay so i just need to use one forward slash or two backward slash so i will use two backward slash here both the data dot xlis x file are same i just copy paste it so whether you go in this format since we have the same excel in the same direct directory i can remove it okay but doesn't matter in case if you have any other location go ahead with that now once you load this workbook it is going to return you one object okay so that i will store into wb object and in case if you want to see what is this so just type type of wb okay and run this program and you will see it will return you the object type so you can see it's a separate class right which is nothing but workbook so workbook is a class that we are going to use now fine now let me simply show you a couple of things so this is the workbook right now if i want to see number of sheets which we have so if i simply say sheet names so it is going to return me return me number of sheets so if i simply store into a variable called sheets okay and if i simply print this just run this program again and you can see we have total three sheets right so one sheet is name second is marks and third one is sheet three which is this perfect so now you can see it is started reading the data and now it's up to us what kind of data we need so right now i showed you one which is sheet names which is returning you actually a list you can see this brackets right now let's talk about how we can focus on a particular sheet and how we can get a particular data fine and in case if you want to see which is the current active sheet so what you can do just type wb dot and just call active and just put dot and just capture the title so it will give you the current active sheet which you have so this is the complete data file right now i'm just requesting give me the active sheet so the active sheet which is currently opened is names which is this sheet okay which is in active mode fine now let's say i want to read particular records so first row if you see this is actually a heading so let me make it bold so if you want a particular sheet what you can do just type wb and in the bracket you need to mention which sheet you're looking for okay so right now i'm looking for name sheet which is this one so i will just copy this name sheet okay so now it will return me sheet object so let me store it into sh1 okay and if you just want to see again the type of sh1 just type of sh1 and you will see this is also a separate class so this sh1 is object of worksheet class clear now this sh one is going to deal with this particular sheet okay so first we load the workbook then we load this particular sheet now let's say i want this data or let's say i want this data so it's very easy so i will show you the different ways to access a record from a particular cell okay so for example you can see this one right uh let's say i want to capture my name so if you just see here this is a2 right which is a is your column and two is the row so if i just want the data from a2 i can just type sh1 okay and in the bracket in the double quotes of single quotes i can simply say b2 and i will simply say dot value that's all and it should give me the data okay now right now program so you can see i got this data which is india okay so right now i just mentioned b2 guys sorry by mistake so b2 is this india it is mentioning it is printing india fine and i will just comment this i don't need this right now so it is actually printing the active sheet which is names so i just commented out so we got india okay and yeah here we go so right now if you don't want to write into multiple statement what you can do so right now what we are doing here first going to a sheet then we are going to a particular cell then we are calling this dot value right so if you want into a single statement this is how you can do just type sh1 sorry just type wb which is workbook object then provide the sheet which is names in our case then i can directly say that i want record of uh a2 okay dot value and this should again give you data two i'm just giving a different variable name and just print data too so this time it should give me mukesh so data2 is a2 is mukesh in this case right very easy it is just you need to get this workbook object and then you can just refer which sheet you want which cell you want and you can directly access clear so instead of storing to variable you can directly use it you can just say print and you can start getting the data in this way so this is one way to access the data okay i will just give you different ways so depends on your requirement which one you like you can go ahead with that maybe i will just give you this as option one right second option which is quite convenient because in this case you need to remember or you just need to focus which column a1 b2 c3 sometimes it is confusing so if you don't want to go in this format so let's say we already have the sheet right which is sh1 so let's say if i just say sh1 which is focusing on sheet1 i can directly call a method here called cell okay and here i just provide row and column okay so just main thing here guys this is not the index it is actual row and column number so let's say you want to get this data which is bangalore so you can see one two three so this is row three column so you can see one two i want this data so i will just mention two and again i will just say dot value and it should give me the data so again if i directly want to print so just run this and you can see i got data as bangalore so so 3 is not the index this is actually which row and which column so let's say if i try to read this again i just want another record so i will just get this data this time okay since we have seen a string now let me talk about this integer so again this is 3 3 right so i will just mention 3 3 and the moment i run this i should get the value as 60 and here we go i'm not storing into a variable guys i'm just printing it but in case if you want to use this data into some activity or in some other operation store into a variable and then use it clear now let's consider that i want to read this particular sheet in this sheet again i have two columns one is name and number i want to access this particular record so right now sh1 which we are using it belongs to this particular sheet which is namesheet so if i want another sheet again i can do that i will just say wb and i will simply provide marks which is nothing but the sheet name so now if i want a particular record i will say ssh 2 dot and now i will call cell now which one we want so this is the second row guys 2 column is 1 so two one two one then i will simply say dot value and now i should get the data so i will simply print this okay let's run this and this time it should give me the data from the different sheet which is from marks and you can see we got the data which is john if i want this data which belongs to third row second column it should give 91.1 so i will just again do the same thing i will copy this and i will just mention this is row 3 and column s2 this time i should get the data and you can see we got the data right so this was uh sheet 2 sheet 2 was referring to marks so if i open the sheet 2 marks this one when i say two one second row uh sorry row and column so when i say row two column one which is giving john which is this and when i say three two third row column two which is 91.1 right now this is second way of reading the data i will show you one more way how you can read it now for this we have already discussed one concept called keyword argument okay so i will show you this way so don't get confused it's totally up to you which one you want you can go with any other option okay so go with option one option two or option three now let's use this sh2 which is focusing on marks again so sh2 dot again we are going to call this cell now if you remember we discussed about the keyword arguments so now we are going to par uh pass the keywords as well okay so right now we don't know whether this is row and column so this is like the syntax which i'm telling but what if you want to exactly pass row and column so now you can pass the keywords as well so if i just show you that uh just type ro so you can see we have a keyword called row so instead of passing bl blind you can just say i am referring to row number two comma and you just mention the column column also you can mention two so when you say second row second column which is this one right second row and second column which is this 90.05 now if you just call dot value you will get the exact data this is the same thing that we have seen and now if you simply call a print function you will get the data so this time you should get 90.05 okay so let's quickly check three different ways so option one you can simply pass like a1 a2 b1 2 depends on which row column you want to read second option you can pass row column again but with cell and here you just need to point x row and column third way is using keyword argument you can pass row and column now there will be no confusion that you are passing what is this 2 1 is a row and column so here it's pretty straightforward that we are referring to second row second column okay i would prefer this way but it's totally up to you because at the end of the day we are reading the data now if you are uh little curious about what exactly is happening behind the scenes so let me show you so we have seen when you call wp wb is nothing but object of workbook class right then we have seen sh1 sh1 was the worksheet but what about this so let's say i just want to show you what exactly it will return so the moment you say sh2 dot cell we are providing row and column so let's say i just want to store into a variable called c and let me just print type of this c what exactly is the type the moment i run my program you will see the type so it says it belongs to a cell so there is a dedicated cell class in open pi excel so this c is nothing but object of cell class great so now let's do one thing let me show you a few additional things i hope you are enjoying this session if yes then let me know in the comment section now let me show you one interesting uh method which actually is replicated now but it was available in the previous version so if you're using previous versions of open pixel you will be able to use it but since we're using the latest one so we will not be we still can use it but you will get the deprecated warning in the console okay so let's say i'm using this wb object right so if i just show you wb and the moment i put dot and let me call one method called get sheet by name so as the name says you just provide which sheet you want to read you just provide the name and it will return you the sheet object so let's say i just want to read name sheet let me add one more record here i will say python and let's say address i will say usa and number let's say 99 let me read this particular record so when i say get sheet by name sheet name is name so in double quotes or single quotes i can provide names then i need which specific cell right so i will say dot cell and now i can provide which row and columns i will say row let's say row is four fourth row when it comes to column column i will put one and i will just say dot value so i should get the result so in this case it should return me the value as python right so let's run this and just focus on the last record it says none okay maybe i have not saved this let me save this file and let's run it once again okay and you can see it is giving me python now if you just see this console you might see one warning okay it is still giving you the data but you will keep getting this kind of a warning it says deprecated warning call to deprecated function get sheet by name use wb bracket sheet name that we have already seen okay so in case if you don't want this depleted warning continue with the earlier thing which we have discussed okay so this is like part one of the video where we discussed how to read the individual row and individual record okay now let me show you what if you want to read all the data from this particular sheet okay whether it's the number of rows number of columns you want to run a follow for that we can just run a for loop and we can read all data so let me show you one thing uh what i will do i will create another file so that we should not get confused i will just create a copy and i will say read excel 2 dot py file and let me simply delete everything ok because anyways we have a backup so i will just say that i want to read uh first of all which sheet i will just refer the sheet called names okay this will return my sheet object i will say s h1 now first of all i need to just get number of rows so how do i get the rows so just say sh1 dot okay max row so just say dot max underscore row similarly if you want the column just say sh1 dot max underscore column okay if you just want to print just print how many rows and how many columns just right click run and you can see it says you have four rows which is correct and three columns now why we need this data because i want to run a for loop so i can just remove the spring because now we don't need i can simply run a for loop and this for loop is going to return me number of data so if you see guys i just need to run two loops right one loop will which will keep on incrementing the number of rows and second will keep on incrementing number of columns so let me write my first for loop where i will just use one variable i so guys i know i need to run my for loop till the number of rows right so i will just call a range function in the range function i will say start from one and go till the number of rows okay now there's a small catch i will tell you what now this outer loop will run four times right because number of rows are four and inner loop should run three times because we have three columns so for this also we need accounts i will say for j in range and here i will say 1 and i just need till the number of columns right and finally i will just mention what value we need so as of now our sheet one is like sh1 is ready i will just call dot cell i will just pass i which is number like row then j which is column and dot value okay just run this program you will not get the expected output then i will tell you why just run this program okay so now if you just see it says name address number so name address is coming but you can see this number column is missing similarly i should get mukesh india otwani bangalore and python usa so if you just see i'm getting mukesh india which is fine or 20 bangalore which is fine but i'm missing my this record is work so what is happening now if you have followed my earlier videos then we discussed that this is going to not include the last number which means if number of rows are 4 so it will run from 1 2 3 it will ignore 4. similarly if columns are 3 so it will go from 1 to 2 right so i will just say plus 1 and plus 1 here as well now if you just run this program now we'll get the proper data and now you can see all the columns are coming so three columns name address number which is name address number let's see the first record mukesh india 50 which is this and similarly if i just check second is o 20 bangalore 60 and the last python usa 99 right how easy it is it is just you need to understand how to run this for loops range function that we discussed and this is what we discussed today i hope it is clear now so let me show you one thing let's say uh this is my excel okay and i want to add three records here so one record let's say i just want to add pi test then i want to add some dummy address and some dummy number so now is it possible to read and writes at the same time yes so what i would do now so i will take the same sheet because i just want to add the same record in the same sheet actually so i will say sh1 okay now i will just mention which cell i want to focus so let's say i want to focus on this particular cell which is five fifth row and one so i will say row which row uh the row should be let's say fifth which column i just want to focus on which column let's say one and which value okay so you can also mention which value so let's say the value should be here pi test okay i can mention this in double quotes or single quotes now i just want to add one more record so row will remain same column i will change to 2 and here i will say let's see uk at last i just want to add one more so this time i will say column equal to 3 and value let's say i just want to add any value value let's say 88.88 okay so i'm just adding three cells row is same so fifth row but i'm just changing the columns and once you are done now we need to call save so you can see we have a save method which is simply asking you give me the file name like and you can simply say give me the exact path where you want to save this file now this is a small catch if you just give the same path or the same name it is going to overwrite but if you just give another file let's say i can say this is report dot xls x so what it will do it is going to create a new excel sheet which will have the old data plus it will also have the new data and it is going to create a new sheet for us called report.xlsx okay just see here right now we don't have any report.xlsx the moment i run this okay you can see we got this xls dot x file so let me show you i will just go to uh show in explorer this is our package so let's open this and finally open this report.xlsx file and here we go so you can see guys still we have the old data that we had earlier marks sheet but in the name sheet it now added a new record or new row i will say with three records you can see how easy it is to work with open pi excel just you need to provide row column pass the values and you can work now this is when you already a file is opened you're reading and you're writing in the same file okay now i will show you one more example let's say you have a requirement you want to create uh excel from the scratch okay so in that case how you will do so let me show you that as well so guys just uh give me five more minutes and we are done so i will say write excel demo dot py file and i will simply remove everything so now let's do one thing just type from open by excel and now we are going to import workbook directly okay and now we are going to create one excel sheet from the scratch so first of all i will say workbook i will create object of workbook class so uh just say wb now we got the workbook instance and the moment i type wb dot active it will give you the active sheet right now the question should come into my mind that we have not given any path right but when we call active what it will return so guys when you create a new excel sheet okay so let's say i just just want to close all the files okay let's close this as well the moment i open any excel file so you can see by default we have sheet right and by default the active sheet is sheet1 so if i just say give me the active sheet so if i say wb dot active dot title okay so it should give me the active sheet and if i just want to see the sheets which is available if i just say wb.sheet names it should give me number of sheets as well so okay so by default we have only one sheet which is the active sheet perfect so now if i just say wb in the bracket if i just mention this sheet and now if i want to let's say add any value so now in the latest excel or the excel that we are going to create uh first of all i want to set the title okay so i will just say title and here i will give report of automation some title which is nothing but sheet name so this is going to change the sheet name to this so it will change to report of automation okay so finally now we need sheet object so that now we can start adding the record so i will say wb dot active sheet okay so we will use this reference now so i will store this into sh1 now i will say sh1 now i want to add records right so i will say a1 so in the a1 i just want to set a value i will say value equal to let's say first of all name fine then i will just take b1 and c1 so i will say b1 and c1 here i will say name and here i will just say status okay let's have only two columns fine now again i just want to enter some values a to b two a three b three and so on so i will just take the same sheet i will just take a two b two and let me add the name let's say python and let's say i just want to keep this as active again i will just give a3 b3 and here i will just mention java and here also i will make this as active finally i just want to save this records right i will just say wb that is the main workbook object i will just call a method called save file name i just gave final report dot xlsx and the moment i run this you will see it will not print anything because we are not printing as such but the moment i open my current working directory which is this one right you can see we got final report.xlsx and if i simply show you this you will get this report and if you open this can you see we have the data and the main part is the sheet sheet name is report of automation it's just a dummy name and dummy data but you can have any productive data here and guys if you want let's say on the desktop let's say uh i want this final report on the desktop so i can provide that path here i can say go to my c drive users folder again users go to desktop and the final report which it should generate it should generate at this particular directory i will say data extracted okay so the moment i run this once your program is done just go to desktop and you can see we got the final excel the moment you open this and here we go again guys it's very easy in order to read and write okay um if i show you a few more things then it will make your concept very clear okay i hope it is clear if yes then let me know in the comment section if no then also let me know in the comment section now if i show you one real time scenario where you need to format the cells as well okay so suppose i have a situation where i just want the result that if it is an active state it should give me uh okay if it is active it should give me a green color in case if it is inactive okay use case can be anything guys i'm just giving you some scenarios if it is inactive it should generate or give me the final output or that particular cell should be in red color in that case what we can do let's see so i will not save this let's go to python once again now definitely guys in order to um fill this data with some style or with some data you need to use some other classes as well right so for this let me just show you how you can do that just open another tab okay and search for styling merged uh cell open pi excel okay you will get their official documentation so you can see here working with style so right now we have to use pattern fill okay so pattern fill there's another class where you can provide what kind of pattern you want to provide so you can provide the color you can also provide border and everything so you can see these are the options which are available right so again if you have time if you want to do some additional stuff please go ahead and read this documentation i will quickly show you how you can fill this data okay so same example i'm taking if it is active then i will make it green if it is inactive i will make it as red color so let's say first of all we need the color code okay let's see the color so again i will open a new tab and i will just say green color code okay just say green color code you will get a html color code okay so let's say for this one let's see this is the green which i want so i can get this color okay this value i will copy this and i will just let's say i will paste it here somewhere on top this is for green and let's say if i just want the status in red for red color let's say this is the color i want so this is the code i will just copy this okay and this i will paste here that this is for red now let's do one thing once this is active i just want to fill this with this particular color so what you can do first of all you just mentioned sh1 okay and i just want to focus on b2 right now and now i want to fill this okay so the moment i say i just want to fill it so you need to provide a separate class now okay which is called pattern fill which i showed you just now here right we need to use this particular class called pattern fill so let's import this as well so how do we import pattern fill it's very straight forward just say oh from open pi excel dot styles okay and then you can say import pattern fill okay so you can see it's available now come down here so here in the pattern fill we need to provide some arguments in our case first of all i just need to provide uh that i if you see guys here the pattern type by default is none right so here i will just make it a solid second thing you can see it is asking you a color right foreground color now here is the main deal i will just say i will provide the foreground color and the color which i want for green is this one perfect similarly if it is inactive okay in that case i just want to fill this with red color so again i will just copy this and i will just make it as b3 pattern feels solid for red color i will just use this particular one and we are done okay it sounds little complicated but just not it is just you need to import pattern fill and if you see we need to provide first of all the pattern type okay just show let me show you so you can see pattern type and then foreground color now let me right click and let me give this as new so that you can see it's a new excel let me right click and run once again and let's go to the red top and it should generate the excel okay so where's the excel let me just refresh okay maybe let me run it once again okay so we got a small issue let's see what exactly it is okay so it says sorry guys i made a small mistake it says value must be dark down medium gray solid and all of this so i did a small spelling mistake it should be solid right now let's run it once again and let's go to the desktop and this time it should give me the excel okay again it is giving me some issue here okay so maybe i have not saved this file guys okay maybe i did a mistake here as well sorry sorry yeah it's done now let's run it once again and let's see now this is done and we got this another sheet if you see the sheet name is data extracted new and here we go sorry so this is how you can format the cells again guys go with the documentation you can do a lot you can merge the cell you can fill background color fog and chloe you can do so many things okay so i hope you enjoyed this session this is all about reading and writing excel sheet so once you move it we will be exporting multiple data into excel sheet so this is going to help you a lot so guys if you are still watching this video till then it means you are liking this if yes then please share with your friends and if case if you are new to this channel then make sure to subscribe and i will see in the next video have a nice day bye

Channel: Mukesh otwani

Views: 168,773

Keywords: read excel in python, write excel in python, write excel in python openpyxl, read write excel in python, excel read and write in python, excel formatting in python, conditional formatting in excel using python, python tutorials, python for beginners

Id: nsKNPHJ9iPc

Length: 42min 48sec (2568 seconds)

Published: Tue Oct 27 2020