Using Excel .xlsx files with Python! OpenPyXl Tutorial
ฝัง
- เผยแพร่เมื่อ 18 พ.ค. 2022
- 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! - วิทยาศาสตร์และเทคโนโลยี
![Introduction to OpenpyXL Library [ Python and Microsoft Excel ]](http://i.ytimg.com/vi/hFDrWvDOYFA/mqdefault.jpg)
![[TH] VCT Pacific Stage 2 - Playoffs Day 2 // PRX vs GEN | DRX vs TLN](http://i.ytimg.com/vi/gmrSk-_ahlA/mqdefault.jpg)
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 😁
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!!
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!
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 😊
Great video! Tahnks for this clear and brief basics of openpyxl
You’re super welcome thank you for watching!!
Super helpful - thanks!
You’re very welcome thank you for watching!
Thanks a lot!
You’re super welcome thanks for watching!
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!!
Thank you very much for this tutorial
You’re very welcome, thanks for watching!!
Really enjoyed this explanation. Thanks :)
You’re very welcome thank you for watching and for the nice comment!
This wasn't half bad. Thank you!
Hey that comment wasn’t half mean, you’re welcome 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!
Very helpful. Thank you and good luck. I subscribed
Thanks so much for watching and the nice comment, and welcome to the channel!
helped a tone man
Rad!
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
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 😸
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!
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!
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
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?
can you please tell me how to remove grid lines from xlsx in odoo or using python code
Why do you need the grid lines removed? That’s like a very basic features of excel I’m not sure how or why I’d want to remove them?
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.
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 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!
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!
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!!
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!
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
Please upload more project on python I need it please. 😭😭😭
I will my friend!! Good luck!
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!
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!
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!
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!?
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