Automate Excel With Python – Python Excel Tutorial (OpenPyXL)

29
48



The first 1,000 people to click this link will get a free career coaching session courtesy of Career Karma: https://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: https://openpyxl.readthedocs.io/en/stable/
Code Written in This Tutorial: https://github.com/techwithtim/ExcelPythonTutorial
Fix Pip (Windows): https://www.youtube.com/watch?v=AdUZArA-kZw
Fix Pip (Mac/Linux): https://www.youtube.com/watch?v=E-WhAS6qzsU&t=4s

⭐️ 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: https://tech-with-tim.teachable.com/p/the-fundamentals-of-programming-with-python
👕 Merchandise: https://teespring.com/stores/tech-with-tim-merch-shop

🔗 Social Medias 🔗
📸 Instagram: https://www.instagram.com/tech_with_tim
📱 Twitter: https://twitter.com/TechWithTimm
⭐ Discord: https://discord.gg/twt
📝 LinkedIn: https://www.linkedin.com/in/tim-ruscica-82631b179/
🌎 Website: https://techwithtim.net
📂 GitHub: https://github.com/techwithtim
🔊 Podcast: https://anchor.fm/tech-with-tim

🎬 My YouTube Gear 🎬
🎥 Main Camera (EOS Canon 90D): https://amzn.to/3cY23y9
🎥 Secondary Camera (Panasonic Lumix G7): https://amzn.to/3fl2iEV
📹 Main Lens (EFS 24mm f/2.8): https://amzn.to/2Yuol5r
🕹 Tripod: https://amzn.to/3hpSprv
🎤 Main Microphone (Rode NT1): https://amzn.to/2HrZxXc
🎤 Secondary Microphone (Synco Wireless Lapel System): https://amzn.to/3e07Swl
🎤 Third Microphone (Rode NTG4+): https://amzn.to/3oi0v8Z
☀️ Lights: https://amzn.to/2ApeiXr
⌨ Keyboard (Daskeyboard 4Q): https://amzn.to/2YpN5vm
🖱 Mouse (Logitech MX Master): https://amzn.to/2HsmRDN
📸 Webcam (Logitech 1080p Pro): https://amzn.to/2B2IXcQ
📢 Speaker (Beats Pill): https://amzn.to/2XYc5ef
🎧 Headphones (Bose Quiet Comfort 35): https://amzn.to/2MWbl3e
🌞 Lamp (BenQ E-reading Lamp): https://amzn.to/3e0UCr8
🌞 Secondary Lamp (BenQ Screenbar Plus): https://amzn.to/30Dtafi
💻 Monitor (BenQ EX2780Q): https://amzn.to/2HsmUPZ
💻 Monitor (LG Ultrawide 34WN750): https://amzn.to/3dSD7tS
🎙 Mic Boom Arm (Rode PSA 1): https://amzn.to/30EZw9m
🎚 Audio Interface (Focusrite Scarlet 4i4): https://amzn.to/2TjXsih

💸 Donations 💸
💵 One-Time Donations: https://www.paypal.com/donate?hosted_button_id=CU9FV329ADNT8
💰 Patreon: https://www.patreon.com/techwithtim
◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️

⭐️ Tags ⭐️
– Tech With Tim
Python
– Excel
Python Tutorial
Python and Excel
– Automating Excel

⭐️ Hashtags ⭐️
#TechWithTim #PythonExcelTutorial

source

Previous articleAtlassian
Next articlePython Tutorial: Intro to AWS and Boto3

48 COMMENTS

  1. Recently started learning python! Fantastic tutorial, keep up this great content! Never thought Python could automate so much. As a financial analyst I use excel every day. Now with this knowledge, I can automate tasks that are somewhat repetitive and save loads of time!. 

    Just had a problem with the last line of code, can't seem to get it to work. When entering the fonts code, it shows up as "TypeError: 'module' object is not callable".

    Any ideas? here's my code: 

    for col in range(1,6):
    ws[get_column_letter(col) + '1'].fonts = fonts(bold = True, color = "0099CCFF")

  2. When you save a workbook, is there a way for the calculations in the workbook to be executed so that the next time you run it you get the newly calculated values based on any new inputs without actually opening the excel file. I have tried using the formulas library for this. But I am running into problems with complicated calculations. I was wondering if there was another way to do this. Thanks!

  3. 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!

  4. 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.

  5. Hello, plz I need help!
    I got a table that got a 3 lines and 4 columns (like what's shown)

    C D R

    A 0 0 F – F

    B 0 0 F – F

    I want to automatically create other tables which will rewrite , the "0" into "1" , one by one , writing every table/possbility

    also in the "R" row , for each "0" there is an "F" and if there is a "1" we write a "T" ; following the order from left to right ; so the lines can have :

    | 0 | 0 |F – F |

    | 1 | 0 |T – F |

    | 0 | 1 |F – T |

    | 1 | 1 |T – T |

    I am completely a newbie in excel and I need this for a homework , plz help

    I think the "R" row needs variable values , do they even exist in excel ??

    Pretty Please take a piece of your time to explain it to me , thx!

    BTW, the "T" and the "F" are not logic functions , thx !!

    PS: I got excel 2019

  6. I think using sheet.cell(row = r, column = c) is also good, rather than converting the column number to a character, also I think the reason that Grades.xlsx didn't show any data is because you used ws.active, but at the time, the active sheet was an empty one.