Using Excel .xlsx files with Python! OpenPyXl Tutorial
HTML-код
- Опубликовано: 26 янв 2025
- This tutorial shows you the basics of installing the OpenPyXl module, how to access data and information out of an Excel file, and then how to read the data, modify the data, and save changes!
This is a beginner introduction to using excel with python, if you want to see more advanced operations and automation, let me know in the comments!
Check out my personal channel for more fun science content!
/ @peterlemaster8310
Thanks for watching and good luck with your code!
I struggled for more than a month with openpyxl but you helped me in less than 10 minutes! Thanks you so much
Hey you are very very welcome!! Thank you for watching and for the super nice comment 😁
Programming
Thanks a lot. I appreciate that you explain the things beginners wouldn't know intuitively.
Thanks for watching! I try to not assume anyone watching has already done any advanced stuff!!
Thank you that was a perfect tutorial - short precise and no nonsense - Awesome job
Thanks so much for watching and for the nice comment!! I try to be useful without wasting peoples time 😊
That was very easy. Thankyou LeMaster!
You’re welcome thank you for watching!
Good to see your subscriber numbers going up, you were under 400 not so long ago, now almost at 1,000! And thanks for taking up my suggestion of zooming in a bit 👍🏻
Thanks Dale! That was a great suggestion so I really appreciate it! And yeah free code camp .org posted my best maker project to their channel and I think alot of folks found me through that which is exciting!
Great video! Tahnks for this clear and brief basics of openpyxl
You’re super welcome thank you for watching!!
Thanks by mistake i found a right video,and now i know how to import file of excel in python
Thanks for watching glad you found it!
Thanks! Very well explained. Please think about a video on doing changes to many cells at once. For example, change all instances of correllation to correlation.
Interesting! It could be good to do a follow up video with some more advanced functionality built in! Plus I believe new excel supports some python scripting directly!
Thanks for the great lecture. Pls I will like to see more data manipulations using excel and python.
Thanks for watching!! I’ll add more python and excel projects to my to do list!!
Thanks a lot, very informative. wanna know some python program making a local desktop web page accessing read and write to excel database file
Great vid! Straight to the point
Glad you liked it thank you for watching!
Thanks but how do I import the excel file to pycharm? For example the menu file needs to be first added to pycharm right? I'm using a community version of pycharm I'm not able to find an option to add any excel files to pycharm
Hi, so you can just copy/paste or drag/drop it into the same folder you’re working in in pycharm. Or you can import the os module and navigate to where in your computers folder structure it lives!
how to extact values from a column and append the same into an array for plotting
Nice and neat. Well done
Thank you! Thanks for watching :)
from openpyxl import Workbook, load_workbook
book = load_workbook("menu_items.xlsx")
sheet = book.active #primary sheet in excel
sheet['A2'].value = "Food"
book.save("food_items.xlsx")
Thanks for sharing!
This wasn't half bad. Thank you!
Hey that comment wasn’t half mean, you’re welcome thanks for watching! 😊
Hi, if I wanted to show (on a table format) the content of every row but just for specific columns, how could I do it?
Hi, do you mean in python because you can just hide columns in excel if you only want some columns displayed?
Thank you so much for the amazing tutorial, is their a way to acces the information while the file is opened or being modified while the code is being executed
This method will actually work while the file is open but it’s not really meant to constantly update while a file is also actively being worked on in excel! You’d need a pretty frequently updated while loop to achieve something like that!
very good tutorial! could you please make a video about reading tables into .doc files?
Hi! Do you mean using python to read data from an excel file and then write it to a word file? I think word already handles excel data pretty well natively!?
why must the excel book be closed in order for the code to work? is it possible that it can be kept opened while running it?
Hi, you can leave it open while it is running but you want to make sure to close the book before finishing your program! If you leave an excel book open but your python file finishes then changes will not be saved!
Really enjoyed this explanation. Thanks :)
You’re very welcome thank you for watching and for the nice comment!
Super helpful - thanks!
You’re very welcome thank you for watching!
Thank you very much for this tutorial
You’re very welcome, thanks for watching!!
how can i connect with you .. i want to get some tutorials from you , this script you wrote is always want i wanted to do .... i want to edit some config data in an excel file and let the script save another file for me , so i can use the newly saved file to run my configuration
Hi, you can DM me on Instagram, or TikTok or email if you have specific requests you don’t just want to send in a RUclips comment!
Very helpful. Thank you and good luck. I subscribed
Thanks so much for watching and the nice comment, and welcome to the channel!
Hi, from your video , i can see you have a lot of experience on VBA. Will there be a way of optimizing VBA functions to go more faster? I've been implementing macros that take a LOT of time while running. What can I do to optimize the code and will computing this on Python could accelerate procedures?
Hi - speed in modern programming languages is all based on how optimized your code is. Try to stop in for loops or while loops as soon as you find what you’re looking for using things like break or return, avoid if chains and nested loops if there are other things available, and most of all try to only grab as small a sized dataset to work with as you can initially
Drum Machine with Python and Pygame - Full Project Course
I am a new subscriber I just subscribed here right now after seeing this video
Thanks friend! Welcome!
@@lemastertech
I hope to see projects video like that 😸
what environment do you use ?
Pycharm! It’s free from jetbrains.org
@@lemastertech came across to your channel since I have a dtr calculator project now I learned a lot and it's exciting 😁😃😃
Hello please do you run a class that one can join ?
Hi I am working on a few courses but none are completed or ready yet. Are you more interested in programming or engineering?
Watched it, good to know. Could you make a video covering properties and the descriptor protocol in python? I never got to understand them.
Hey that’s a good idea! Might be a little advanced for some of my viewers but I think it could be a useful concept on a future video! If you need any quick tips on it, this is a pretty good summary too! docs.python.org/3/howto/descriptor.html
whenever I try to run the code it tells me that openpyxl isn't installed, I installed it using cmd but I'm running the code using IDLE, could that be why it isn't working?
edit: nevermind, I uninstalled/reinstalled and it started working
Good fix!!
Thats Cool
Thanks for watching!!
how did you save that excel sheet in pycharm?
You can do a file.open with the write instruction and it will create a csv with that name in the same project folder! Goodluck!
helped a tone man
Rad!
Thanks a lot!
You’re super welcome thanks for watching!
I am getting an unicode error, any suggestions? please
That usually means there is a character in the excel doc that python can’t recognize, the error code usually throws what row and cell the error occcurs in
Thanks a lot, awesome and very practical video
could recommend more videos (yours or others) how to automate work in Excel and other MS office apps
is Python effective to automate daily boring tasks on the computer? more recommends much appreciated
Cheers 🙂
That’s a good idea for a future video!! Thanks for watching and leaving that suggestion!
I’m looking at a way that I can create a very interactive rota using python so maybe a tutorial on how to design a rota/scheduler in excel but coding in python rather than having to use VBA
That would be a cool idea! I don’t have much experience in rotas myself so I’ll have to do a bit of research, what type of features and functions would you want to see??
@@lemastertech maybe a skill based rota so people are selected depending on their skills.
Add, modify and delete someone
Dropdown selections depending on their skills, so they only appear in lists they have skills for.
Date picker pop up to select dates for the schedule
Print/export buttons to print out or export to pdf
Email or WhatsApp buttons to send reminders. Also the option to have the reminder go as a calendar reminder on their mobile device.
how do u put the menu.xlsx file there like
That is just like saving a file to a folder location!
Can we work with password protected files?
Yes you can manually input the password but it’s honestly probably easier to save off a copy that is not protected, work with it that way, then copy it back into the master. Python also just added an actual excel plug in you can install via excel!
Can i use it in vs code or no
Yes the IDE you use shouldn’t matter
Can this code be used while the excel is open?
I believe it throws an error or overwrites data if you do have it open so probably not best to have it open!
For some reason the library expects the file to be a zipfile on my linux system, even though i specified the filename with xlsx at the end :(
Hi! I am sorry I actually have not personally used this module on linux before, but you might also need the filestream module since linux handles all doc types differently! But I honestly am not sure what the specific fix for linux would be - they have decent online documentation for the openpyxl module though! Goodluck!
Remember that an xlsx file IS a zip file. Just changing a file name isn't enough
Can you do a full course on how to make word chain game with use of sql database for dictionary and also how can we make bot that plays this game and also a bot that conducts this game? Also banned letter etc modes
That would be interesting! Thank you for the idea, I will have to read up a bit on word chain games since there are alot of different types! And sql as a database is a good idea that would be an awesome tutorial I will working on a project for that!
Thanks I want to learn sumifs through phython please guide me
Hi I think the best thing to do if you want to use python to do the same style of calculation as the excel sum if would just be to use a for loop with an if statement in python that checks for your conditional and then add that number to your running total if it satisfies your condition and ignore it and move on otherwise!
More Excel with Python
Please upload more project on python I need it please. 😭😭😭
I will my friend!! Good luck!
Can you do video on how to make bots on telegram using python please?
Hi! I can look into this, I don’t make bots and always find them a little annoying so I’m not sure, but it might be a cool project! I’ll read about it