Using Excel .xlsx files with Python! OpenPyXl Tutorial

what's up you guys welcome back to the channel in today's video i'm going to show you how to access and modify excel data in your python programs [Music] so to get started i have an excel sheet um it's just one sheet and i called it menu items and it's like a sample menu for a restaurant it's just four um dishes their prices and then whether or not they're vegetarian something like that the content of your excel sheet doesn't really matter but the location of it will so i've dropped mine into my project folder here so right where my main dot pi is for this python excel project i also have the menu excel file that's going to be important in a little bit when we actually access it just good to know okay so to get started the first thing anytime you're going to be using excel stuff inside of your python project make sure you have this open pi excel module installed if you don't then go to the terminal or command prompt or whatever on your computer and do pip install open pi excel and install the module real quick so once you do that you can do import open pi excel right at the beginning of your project just like that and then to access data we're going to use two basic things from that module so it'll be from open pi excel import and we need two things we need workbook and it's capital w workbook and then load underscore workbook and no caps in that one and so that's all we need to be able to do to just access our excel file so then the next thing is actually load in your workbook and to do that uh just pick a variable that makes sense to you i'll just call mine book but it's going to be equal to that load workbook that we just imported and then in quotations because it's a string the name of your file and so i'm using the pycharm ide and it does this nice thing where it'll auto populate with the names of files so like i can just type m and menu pops up as does main but make sure whatever you're using it's this xlsx file extension the xlsv that's like on older csv style workbooks is not going to work so make sure you have an xlx xlsx file and then if we want to view some specific data the next thing we want to do is just like we just put the specific book into a variable we want to put a specific sheet into a variable so if we want to take whatever's on top like whatever is the primary sheet it's just this sheet equals book.active and you can change this sheet and this book uh variable name if you want i like sheet and book it makes it pretty clear what we're looking at and um to get started i'll just print for you that sheet so you can see what's going on here so let let's run this and you can see down at the bottom hopefully i'll make that a little bigger for you it's it just displays worksheet menu items and now let's take a look at the sheet uh just to compare to what we just got so hopefully you can see i was trying to zoom in for you i just made everything invisible hopefully you can see i know it's kind of small text but the name of the sheet is menu items so it's great that we're accessing the sheet but we're not currently showing any of the actual data that's in this sheet so if you look at the kind of column in row names that's how each cell has an identification so like tortellini is in a2 and then the price of a burger is in b3 so um we're gonna access some specific cells just to show you what that looks like so to do that let's keep our print sheet statement in here but let's say we want to see what's in cell 82 so we'll do sheet a2 and then it's dot value if you want the stuff that's actually in the cell you have to use the dot value so let's run that okay and it gives us tortellini which is pretty cool so we're viewing data inside of our excel sheet okay so that's a great example of how to get information out of your excel sheet now let's do the next kind of basic operation would be which would be you want to modify data so you have that sheet a2 dot value next thing we'll do is we'll say that exact same thing let's say we want to change it from tortellini to spaghetti okay so just keeping it pasta we don't need to change anything else in the row but it was tortellini now we want to be spaghetti you can do this exact same way that you use to address the value you can overwrite it using the same exact statement and then you just say what you want it to be equal to and then the last thing is if i were to run this it wouldn't give us an error but if i open the excel sheet so it looks like it finished correctly but if i open the excel sheet it still says tortellini so it looks like what we're doing didn't work actually the program worked just like we told it to but there's this last piece you have to do which is you have to save the book so here's where if you've seen or modified text files it's probably familiar with you as well you can modify data but if you don't save it at the end you're not going to be overwriting any data and so you have your choice here after you modify things in your program you can save it to the exact same name as the file when you pulled it in so if i were to run this now and then it looks like it succeeded and i'm gonna pull up the program okay and you see we got spaghetti great um but let's say that you wanted to go back to tortellini um but only sometimes so you want that to be that in that cell but only for like seasonal you can make this seasonal menu.xlsx now if you run this okay we it it's going to create and i know it's a little small i don't have a good way of zooming in on the menu it's going to create a brand new excel file which if i open it up that one has tortellini right there but i still have my original menu preserved and it still says spaghetti because what we've done is we've edited the previous workbook and saved it in a new spot so you have this ability to create brand new excel files just by saving data with a new a new name so that's the basics of how to open data view data and modify and save data from excel using python if you enjoyed this and you want to see more excel python content just let me know about in the comments below i can for sure do a follow-up to this project where maybe we do some more advanced data manipulation and show the actual like practical uses of combining python in excel um so if you enjoyed this video please leave a like on the video subscribe to the channel it helps me out a ton and let me know what you want to see more of on the channel in the comments below as always thanks for watching and good luck with your code thanks bye

Channel: LeMaster Tech

Views: 39,971

Keywords: automating, coding, computer science, engineering, excel, excel python, learning to code, microsoft excel, openpyxl, programming, python, python automation, software, xlsx, xlsxwriter

Id: 718edSNvKLA

Length: 7min 23sec (443 seconds)

Published: Thu May 19 2022