Automate Excel With Python – Python Excel Tutorial (OpenPyXL)

By
Tech With Tim
-
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
Tech With Tim

RELATED ARTICLESMORE FROM AUTHOR

48 COMMENTS

  7. 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")

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

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

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

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

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

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