Automate Excel With Python - Python Excel Tutorial (OpenPyXL)
ฝัง
- เผยแพร่เมื่อ 7 มิ.ย. 2024
- The first 1,000 people to click this link will get a free career coaching session courtesy of Career Karma: rebrand.ly/tech-with-tim-51321
Welcome to another video! In this video, I will cover how we can use python to automate Excel. I'll be going over everything from creating workbooks to accessing individual cells and stylizing cells. There is a ton of things that you can do with Excel but I'll just be covering the core/base things in OpenPyXl.
📄 Resources 📄
OpenPyXL Docs: openpyxl.readthedocs.io/en/st...
Code Written in This Tutorial: github.com/techwithtim/ExcelP...
Fix Pip (Windows): • How to Install Pygame ...
Fix Pip (Mac/Linux): • How to Install Pygame ...
⭐️ Timestamps ⭐️
00:00 | Introduction
02:14 | Installing openpyxl
03:19 | Testing Installation
04:25 | Loading an Existing Workbook
06:46 | Accessing Worksheets
07:37 | Accessing Cell Values
08:58 | Saving Workbooks
09:52 | Creating, Listing and Changing Sheets
11:50 | Creating a New Workbook
12:39 | Adding/Appending Rows
14:26 | Accessing Multiple Cells
20:46 | Merging Cells
22:27 | Inserting and Deleting Rows
23:35 | Inserting and Deleting Columns
24:48 | Copying and Moving Cells
26:06 | Practical Example, Formulas & Cell Styling
◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️
💰 Courses & Merch 💰
💻 The Fundamentals of Programming w/ Python: tech-with-tim.teachable.com/p...
👕 Merchandise: teespring.com/stores/tech-wit...
🔗 Social Medias 🔗
📸 Instagram: / tech_with_tim
📱 Twitter: / techwithtimm
⭐ Discord: / discord
📝 LinkedIn: / tim-ruscica-82631b179
🌎 Website: techwithtim.net
📂 GitHub: github.com/techwithtim
🔊 Podcast: anchor.fm/tech-with-tim
🎬 My TH-cam Gear 🎬
🎥 Main Camera (EOS Canon 90D): amzn.to/3cY23y9
🎥 Secondary Camera (Panasonic Lumix G7): amzn.to/3fl2iEV
📹 Main Lens (EFS 24mm f/2.8): amzn.to/2Yuol5r
🕹 Tripod: amzn.to/3hpSprv
🎤 Main Microphone (Rode NT1): amzn.to/2HrZxXc
🎤 Secondary Microphone (Synco Wireless Lapel System): amzn.to/3e07Swl
🎤 Third Microphone (Rode NTG4+): amzn.to/3oi0v8Z
☀️ Lights: amzn.to/2ApeiXr
⌨ Keyboard (Daskeyboard 4Q): amzn.to/2YpN5vm
🖱 Mouse (Logitech MX Master): amzn.to/2HsmRDN
📸 Webcam (Logitech 1080p Pro): amzn.to/2B2IXcQ
📢 Speaker (Beats Pill): amzn.to/2XYc5ef
🎧 Headphones (Bose Quiet Comfort 35): amzn.to/2MWbl3e
🌞 Lamp (BenQ E-reading Lamp): amzn.to/3e0UCr8
🌞 Secondary Lamp (BenQ Screenbar Plus): amzn.to/30Dtafi
💻 Monitor (BenQ EX2780Q): amzn.to/2HsmUPZ
💻 Monitor (LG Ultrawide 34WN750): amzn.to/3dSD7tS
🎙 Mic Boom Arm (Rode PSA 1): amzn.to/30EZw9m
🎚 Audio Interface (Focusrite Scarlet 4i4): amzn.to/2TjXsih
💸 Donations 💸
💵 One-Time Donations: www.paypal.com/donate?hosted_...
💰 Patreon: / techwithtim
◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️
⭐️ Tags ⭐️
- Tech With Tim
- Python
- Excel
- Python Tutorial
- Python and Excel
- Automating Excel
⭐️ Hashtags ⭐️
#TechWithTim #PythonExcelTutorial
Start a high paying tech career making $60k+/year with NO DEBT: coursecareers.com/a/techwithtim
And support. Thanks tim
What kind of keyboard are you using. I miss those clicks
I think some bug is there not accepting the mobile number or land line no. with & without the city code and country code. Or a fake one
🇬🇧🇽🇰🌟🌟
Did you love yourself amigo? :)
New to python and starting my own project working with excel files. Tried to go through the documentation for openpyxl and was wasting alot of time learning unnecessary information based on what I was trying to accomplish. You've done a wonderful job with this and I was able to easily follow along and experiment along the way. Put together a py file for my notes on this module. Tim is Great!
This is quality stuff. This guy gets how to make a good tutorial video. No wasted time, points are explained enough to understand then moves on.
recently got into learning python.
my mind is blown with the versatility of python. I now wish i had done software engineering back in school..
Actually did something similar a month ago, where I converted the excel file in to base64 in the end and sent it through an API to then generate it back into xlsc format through typescript.
PyXl is amazing btw, anyone who watches this should try it. Very easy to use.
exactly what i needed, im a civil engineer and learning python. i was coding a building cost estimator desktop app in tkinter and needed python after calculating all materials for me, it should also create an excel workbook and type those results for me. so that i simply print out for the client in the shortest time possible. awesome video. thank you sir.
im a complete beginner though, any one with a better way of doing it is welcome
Python is probably the best way to do that. I don't think you necessarily need tkinter though
Hi, I am an industrial engineer and currenly looking for ways to automate the process of some report that we do in excel. tkinter might be a good idea for people that don't have the expertise or the knowledge of python cause they are just going to use the interface. hope you get what you want. Really appreciate if you reply the result if you finish the project. You should try KNIME too. Good luck. Cheers!
Hands done one of the best Python training videos out there I’ve. Method of instruction, clarity and examples are brilliant. Thank you so much.
I've been learning Python by following a book and got confused and stuck for so long until I found this video. Thanks
Who the heck is this guy!! Just amazing trainings and super easy to follow along. Keep them coming Tim, much appreciated.
man, I really appreciate your work and time spent on explanation. I would never sort it out without your help
Was waiting for such a vid for soooo looooong. Thx Tim :)
Haha ikr. I learned a bit of Openpyxl for a script I run everyday, but this should let me do some more interesting stuff ;)
I am working as a Senior Financial Officer dealing with financial reporting. This video gives me a new powerful tool to quickly manipulate and process a large volume of data. I think I will surprize my boss with my new skills! Thank you, Tim!!!
I'm a tax accountant, this is exactly what I've been doing for the past couple of years. Bringing in so much value to the business, and plugging the cash leakages using coding skills, you get taken more seriously.
@@SwahiliSpicE the excel docs are created from your ide or within excel? seems VS code will not bring in excel file created in excel?
Clear, rich, concise and well delivered. Thanks!
Wow ! All the basic fundamentals are packed in one demo ! Thanks Tim !
Awesome video Tim! Ive struggled a bit with python and definently had some 'aha!' oments watching this! Keep up the good work!
Bruh! You literally made me safe countless hours of work at my job! Lovin' it
Glad I could help!
Boomers at my work will now treat me as a god
I am at my final year, and just got to know about automation and how seniors don't know it. :v
I also wanna feel how you said 😂
🤣
No, they won't
for a quick minute until they start doing themselves to save their jobs
Actually it is exactly like that! :D
Excellent run-through of this. Very cool to be able to interact with Excel spreadsheets in Python!
Thanks for this - Nicely done!
i liked ur comment cause it had no likes. It a lonely comment
by far, the best video for starting excel with python
Thanks for a great video. It helped me automate several redundant tasks and opened my mind to the power of openpyxl
AMAZING, Tim. The best demonstrated matter I have ever seen.
Thank you for the install error explanations. Thats something most videos lack
Clear, rich, concise and well delivered. Thanks!. Clear, rich, concise and well delivered. Thanks!.
to those who are thinking @6:52 that you can directly open the workbook or directly save the workbook using that load_workbook command.. no ppl.. you need to first make or upload a workbook at the location of your text editor or some random location then take that location from there .
As someone who loves Excel and is learning and loving python, I think this module is awesome.
Than look at pandas
@@CrypticConsole I new pandas dealt with data. I didn't know it could access Excel docs. Thanks.
this is a whole another lvl. of automation with python
Awesome tutorial and my only frustration is time limitations to apply all that you have shared. Thanks, and God bless
This strategy is bomb!!! I won 3/4 just testing it out!!! Thank you for sharing!!
Great video. Super useful. Thanks, Tim!
I have a work that recently needed this--- JUST IN TIME! thank youu
Can u help me with the scenario u have in ur work. Thanks
I expected this to be as easy as working with the csv module, but obviously it isnt, and now that i think about it, why would it be. Nevertheless, this video help me understand it even more than i understand the csv module, from installing OpenPyXL, to formulas and cell styling. Great video Tim! Thanks for all that you do for us python noobies☺
I started the transition from VBA to python+OpenPyXL around 2 weeks ago (after years of experience with VBA) and there it is - Tim's video about this very topic!
Why did you decide to change? What are the benefits of using Python in this case?
@@micheleluigimelis426 way less code
@@Bayo106 not having to declare variables to begin.
This video was a blessing to me, Thanks very much!
This is a good example of why I love Python even more than VBA. It is so intuitive and readable compared to most other languages.
totally false statements. Nice try simp!
So surreal reading how a guy called yugiohfanatic is calling someone else a simp. The internet is a beautiful place. @@yugiohfanatic1964
Cool video! I didn't think that it is so easy(thanks man who make this library). After this video, it's obligatory to subscribe.
The best video about openpyxl. It is helpful for me a lot.
Great video! It helped me a lot starting in Python! Many thanks!
Nice video! Great for a beginner to get comfortable with Python and Excel
You are really talented in carrying information across. Thanks for this video.
Appreciate the comment :)
Great work! Thank you for this! Subscribed!
Crystal clear. Thank you.
this was very helpful for my internship (at a messy start up)! Thank you so much Tim!!
As usual, very useful. Thank you!
the posibilities and list of errors is so good as it helps
Thank you so much Tim for sharing this. God bless you and all the best for you
Thanks you so much for explaining in so detail and easiest way. Thanks a lot ❤
A very good video. Thanks Tim.
I think that you could have saved some time in this video, if you would have added a close, open excel file function within the Python code, and would have called it up each time you test your code. That way, it would close that particular file, save it and reopen it automatically without you needing to do it manually, as you did in this amazing video.
Lemogan
Opening a file is easy obv but dont know how you close a file cause once its open the application itself is usually responsible for this
Best tutorial about Excel automation. Thanks, Sir
That now kinda useless with power automate desktop built in windows 10/11
Your videos are straight 🔥🔥🔥🔥 as a programming newbie I'm learning alot from you. Thanks for all you do!
Thank you Tim for the video. Its helpful for people like are in the process of picking up the rudiments of python. Could you do a video about OpenPyXL + Pandas. How these two work together and what are the pros and cons of using openpyxl and pandas on their own
Thank you for this, it helped me a lot for an college assignment
< 3
For anyone like me who finds the lettering system tedious and annoying, rather than using the get column letter function you can just use sheet.cell(1,1) for example which would be cell A1, but I think this is nicer especially if youre using nested for loops.
Thank you! So, to stick more to the tutorial, it will be like *ws.cell(1, 1).value = "Type something"*.
By the way, you have a similar reference system in VBA as well.
thats the way to handle columns greater than "26" also.
Using python for sheets is very helpful,I use it on a regular basis
Most educational thank you Tim !
The best tutorial video about python excel I have ever seen $.$
Great video, it helped me a lot with a project that i have and i only knew how to work with CSV files that didnt helped me at all. Thank you
very clear thank you!
Thank you for this great intro!!
dude. this is sooooooo helpful, thank you
Bruhhhh needed a video like this 4 months ago
Thanks for your video, you helped me a lot in my work :)
wow...this is so very helpful...Thank you
Great video! Well explained!
Thank you for your great explanations ❤️
It helped for creating ustom excel file from python! Thanks a lot
This is great! Thanks for sharing!
For anyone reading there are random real-world use-cases for stuff like this on occasion, I recently did something similar with Word and Powershell. In my case, I would take data from a CSV and generate a document containing stuff like login information, Employee IDs, etc. It can save time when you are doing 25+ of these documents.
why powershell?
I use powershell alot, it does alot of the same things python does and it's integrated into the OS
Pretty much for the reasons Dave described. When in Rome, do as the Romans do.
For general purpose stuff like this in Windows environments, PowerShell is the better tool for the job, not because it's better than Python or anything silly like that but because it's the lingua franca of Windows and has plenty of cmdlets to deal with the tasks you'll run into in a Windows environment but just as importantly the people I work with have a better chance of understanding my code if it's in PowerShell vs Python.
For a more specialized task maybe I dip into the .Net APIs using PowerShell (use the native PowerShell solution first as best practice) or pull out C# in a Windows Environment.
To choose an example where I would definitely prefer Python is anything in the Data Analytics or Machine learning world.
On the operations side if you ever need to deal with networking equipment, the community has built some really good libraries for automating working with routers and switches. Network Automation and SDNs are a thriving space in Python.
I actually tooled around with some Unifi stuff in Python while trying to automate performing resets of multiple Unifi Access Points. (Didn't really work out but learned a bit)
Great work. Good to get started.
tim really is great! thanks for the video, really informative :)
Very good, now i'm not tired for some accounting work.
thx for share your knowlage, I want to learn python and this is so motivating for me....I love programming and learn new things...so again thx very much!new sub here
Thks for the intro. Great vid.
pains of suffering of reading stackoverflow of excel automation and alien language has come to an end 😌
I'm From Brazil!!!!! Perfect Video!!!!
Awesome, was searching for this. Next up, extract data from table in a PDF file, please :)
Oooh yes!!
The python package "Camelot" can already do this.
Very well done 👌
Great video Tim, thank you 👍
Would love more content on making figures in python, specifically line and dot plots that are commonly used in excel.
Tim, thanks a lot for detailed video. Simply the best way to use Excel formulas with get_column_letter, can't figure it out myself
As Tim noted, you cannot save the spreadsheet changes if it is opened in Excel (or in my case, Libre Office) for 'normal' use.
I did figure a sort of workaround to this problem.
If you open the spreadsheet as read only, your python code will run without a problem. Now to view the changes simply reload the spreadsheet.
The reload command in Libre Office can be found under File >>> Reload (or press the keys Alt, f, l in that order(Note l is lowercase L))
Yes, you have to remember to reload the spreadsheet every time you make a change, but it is quicker than the close, reopen way, you could put a reminder in your python code.
Note, if you want to edit the read only open file, there is a button on the top right that says Edit Document. but remember after editing it needs to be saved and reopened as read only.
Use the python command try when doing a workbook save, and if it throws and exception, then ask the user to go through the close/reopen read only routine.
Libreoffice Rocks
Amazing Work
Thank you so much from MO in Bangkok Thailand :)
Thank you for your Great Session
Sehr gut! Danke! Viele Grüße aus Deutschland!
Wow. Thank you so so much for this.
tnks tim its very helpfull for me.
lov u man
Thank u so much for the video!
thanks i will come back for more i need to watch the full video. but yes i admit you helped me with what i wanted thanks brother
Tim. Could you do an equivalent video Automating LibreOffice Calc ?? It would be fantastic. I'd even give a reasonable financially contribution for the effort.
Love your vids bro ❤️❤️
hey I remember asking you about openpyxl several months ago :)
very nice tutorial, thanks
Thanks, super video tutorial.
Am your new subscriber 🤝✨♥️your content and presentation style is 😘👌
Simple and cool 👍🏻
Thanks! Great content!!
So great! thank you!
Very helpful!
Thanks Tim :)
Fastest way to get started. Let's go.
Tim, thanks for sharing your knowledge. Question for you (or any of your other followers if they care to help.)!!!
How would you set a cell alignment to center. I have looked through the "working with styles" in the help files, but cannot get the alignment to work. Any help would be great.