Connect Python To Excel - Python and Excel With OpenPyXL #4

okay in this video we're going to write our first program and sort of try and connect to this file so i've got this file and i've called it hello.xlsx uh it's just a basic thing we've got names and favorite colors we've got a list of names here and a list of favorite colors here now i saved this in the same directory where we've been working out throughout this whole course so if we head back over to our terminal and let me clear the screen and type in ls to list the stuff in our directory you can see we've got our hello.pi file that we did in the last video and we've got this hello.xlsx excel spreadsheet file also so okay and just notice it's important that it's in this directory now it doesn't have to be and i'll talk about that in a second but this is where we want it right now so let me go ahead and clear this screen and let's head over to our sublime text editor and we're in our hello dot py file that we haven't really done anything yet so the first thing we need to do is let our python program know that we want to use open pi excel and to do that we import open pi excel into our code and to do that we come up to the top and we go from open pi excel dot workbook we want to import workbook and this is a capital w so i should mention whenever we're working with a excel spreadsheet there are basically two things we need to sort of keep an eye on workbooks and worksheets now if we head back over to our excel sheet here this whole thing is like a workbook right so think of excel as the workbook right inside of each workbook instead of each file there's different worksheets and those are always listed down here so i can do this now we have sheet2 and sheet1 sheet2 sheet1 so sheet1 has all of our stuff in it but sheet2 still exists so these are worksheets right so excel is the workbook these things are the worksheets so that's just sort of the terminology that python expects to see and that's why that's why we've now imported workbook this will allow us to import an excel workbook into our program so we also want to load an existing workbook so this will allow us to create our own excel workbooks we also want to load other ones so like i said the beginning of this video i've created an excel spreadsheet we want to be able to load that into this file so to do that we go from open pi excel we want to import load underscore workbook and these two lines look a little different don't sort of get hung up on that just sort of understand that you need these two lines of code almost in everything that we're going to do from here on out so almost every time we create a new python program and we want to do excel stuff with we're going to put these two lines of code at the top so first let's create a workbook object and python is an object-oriented programming language so we create objects and we do things with them and so i'm just going to call this wb short for workbook and then we just set it equal to a workbook object right and you'll notice this is basically this up here so we've created a workbook we've named it wb now we can do stuff to it later on by referencing this wb variable right and this will become more apparent what i'm talking about with this stuff later on so okay that's pretty good now we need to create a worksheet or better yet an active worksheet and to do that we just call we create a new variable i'm going to call it worksheet i'm going to call it ws short for worksheet and we're going to set that to wb dot active and this wb is this wb up here so we're saying hey from this workbook make this variable sort of the active worksheet and in this case that's going to be this thing once we load our worksheet because it's the active one it's the first one it's the one that opens when you open excel it's the one that you know is shown it's the active worksheet right so okay we've designated that now let's load existing spreadsheet so to do that we're going to call on our wb and we want to set it now equal to load underscore workbook and then just the name of our spreadsheet and remember i called it hello dot xlsx now because we saved it into the same directory that this whole thing is saved in we can just reference it as hello.xlsx if you save this in a different directory you would have to specify that here so you'd be like something like c forward slash my files forward slash actually forward slash there we go so this is an absolute path it is the absolute location on your computer of this file if it was in the my files directory this is a relative path and this is just relative to whatever directory you're currently in we're in our excel dash python directory this file is saved in that directory so we can just reference it like this so okay so we've now sort of created our first program we've loaded the excel spreadsheet into it and now we're ready to go i think this video is getting a little bit long so in the next video we'll actually print out something from the spreadsheet onto our terminal and that'll be in the next video

Channel: Codemy.com

Views: 43,890

Keywords: python openpyxl, python openpyxl load workbook, python openpyxl active worksheet, python openpyxl excel, python connect to excel, python connect to excel with openpyxl, python load workbook, python openpyxl Workbook instance, codemy.com, john elder, john elder openpyxl, Python Openpyxl tutorial #4, python openpyxl tutorial, python spreadsheets, python excel spreadsheets, spreadsheets with python

Id: qDpq2_TTmvk

Length: 5min 44sec (344 seconds)

Published: Tue Apr 13 2021