How to Load Excel in Python. Openpyxl Tutorial #1

hello everyone in this series i will show you how to automate axial tasks in python using open pi excel module open by excel module is a external python module that will allow us to automate many of the excel tasks that we usually need to do manually now open pi excel doesn't come pre-installed with python so first we need to install that package to install that run pip install open i excel in your command prompt or terminal and hit and enter command this will install this uh in your computer for me it's already installed so it says requirements are already satisfied now to begin with uh first i will show you my excel sheet that i have created an example actually here i have named balance.xls and it has two sheet sheet1 and score sheet1 has name of the customer and their balances in score actual sheet i have name and this code so in this first demo i'm not going to show you any anything complicated but just explain you how to load that excel file in the python to do that let's go back to our python sheet so here we first need to import open pi excel in our actual c to do that we can type import open pi excel and this will import uh open pi actual module in my python file now if this module is not installed in your computer or for this particular environment it will throw an error so to solve the issue go back to previous step and run the peep install open by excel command to install it now i have installed it successfully so it doesn't show me any error now first thing we need to do is load our excel sheet to do that we need to create an workbook object now here i am calling it wb it stands for workbook but again this is just in variable you can name it anything you like but i'm using the standard naming convention so when somebody else read my code they understand to load an excel file we have to use load workbook method load workbook method we can pass multiple parameter one of them is a file name and there are a few mores now other parameter are optional here and we don't need to pass it right now we will use those in future module but for now we will just focus on this file name now here you will need to pass absolute file path you can also pass a relative file path there's nothing won't work but it has to be true now in my case i have stored actual sheet in my python file in a same folder so i can directly pass the file name here without passing anything else but if you have stored your excel file and python file in a two different folder or two different location make sure to always pass the absolute file name in here now if this is correct we can run this python file and if it doesn't throw any error that means it's loaded but let me intentionally use file name that doesn't exist now if i run my python file it will throw an error that no search file or directory exist this error meaning file name you passed here is incorrect so you will need to make sure to either pass here absolute file name or relative file name but it must be true and it must exist in your laptop now let's correct this error and i'll name it balance.xls and just to make sure it doesn't have any other error i'll rerun it and did not see any error that means it worked now first thing we need to understand is how to interact with different worksheet in the excel file to print all sheet names we can use wb wb which is our workbook object that we created here and we can simply pass file names let me add a print statement here so we can see that in our console now let me rerun the python file here you can see we have to see it in my excel file sheet1 and score so let's go back to our excel sheet and see if those two exist and it does here is my seat one and here is a score now we can perform many operation almost all the operations that we can manually do in excel we can automate that using this open pi excel module but before that we need to select particular sheet name so to select that we can simply say wb and then use square bracket and pass here a seat name so we have two sheets seat one and score i wanted to select the scoresheet so i'll just have to pass wb slash code i can see that in another variable uh let's call it w as basically i'm naming it worksheet the short form of worksheet and then let's print that worksheet now if i read in my file it says brain doesn't exist oops i made an error here so let me correct that and let's rerun our command now it will say worksheet score so that means worksheet score is selected and i can perform any other operation i like if i want to perform operation on sheet1 i just simply have to either create a another sheet object so i'll say ws1 wb and pass here sheet1 and let's print that as well ws1 and rerun our command now here we have like two sheet object printed worksheet score worksheet sheet one now this ws is for scoresheet and ws1 for sheet1 so whatever operation i run on this ws it will go to the score sheet now this is for selecting a particular actual sheet now let's look at how we can add a new actual sheet in there so let me just comment this out and to create a new worksheet we can we can use create sheet method and here we will need to pass a sheet name i'll call this new sheet and save it so with this method we can create a new sheet let's run the python command again and no error so i'm expecting that new sheet will be created now let's close and reopen the file and we did not see so here we still see sheet1 and score the reason for this is we have sent in command to create a new sheet but we did not save it so this is very important point to understand whenever we wanted to make a change in a actual sheet we always need to run w plus save command without this command even though you ran your command to make a change but it will not be implemented until and unless you save that now there are two option here first we can save this as a brand new file or we can override the existing file now it will not overwrite all the data okay a very important thing to understand if you use the same file name it will simply add the changes that you have specified in your python file so here i don't want to create a new sheet but want to make a change in a seam balance excel file so here i'll just simply copy this file name that i pass in my first line and add that here now if i run my python command again i don't see any error so let's go back to our excel file and open it and there you go here you will see that new sheet that name we pass here that has been created now we have other options as well in creating method we can pass the indexes to specify where exactly we want to create that new sheet so i'll just rename something in here i'll say let new sheet1 new underscore sheet 1 but i want to create the seat in the first place to do that i can simply pass the index so in this hint bubble you can see create c take two inputs one is a title that you already passed and second is index now index is an optional if you did not pass any index it will by default save it as a last seat but if you want to save it at particular location you will need to pass an index to save it as a first place you can simply pass zero but if you wanna save it as a second place pass it one and so on let's create a new sheet one at first place so i'll pass zero and again we need to save our workbook object now if i rerun my python file again no error let's go back to our excel sheet and you will see we have this new sheet one that is created in first place because we pass this index of zero we will end our first demo here in next demo we will start working with excel values and cell and we will see how we can extract values from the excel file thank you

Channel: Python Bits

Views: 40,225

Keywords: learn python, python programming, python, python for beginners, read excel in python, write excel in python, write excel in python openpyxl, read write excel in python, excel read and write in python, Python How To Read Microsoft Excel Files With Openpyxl, python tutorial python basics python for beginners, openpyxl tutorials, read excel files in python, Python How To Read Microsoft Excel Files, Openpyxl, microsoft excel python, excel python

Id: FpgdFkup3ew

Length: 10min 8sec (608 seconds)

Published: Tue Dec 29 2020