Control Excel with Python & OpenPyXL

Name: Control Excel with Python & OpenPyXL
Rating: 4.2 (1057 reviews)

Become an Excel Power User with Python & OpenPyXL

Created byConny Soderholm

Last updated 7/2023

English

What you'll learn

Controlling Excel with Python
Automate Excel with Python
How to use OpenPyXL

Course content

9 sections • 47 lectures • 3h 1m total length

Welcome to the course!2:10
Getting started0:37
Installing OpenPyXL1:51
Install Python from python.org, install OpenPyXL with pip, and install an Excel viewer such as Microsoft Excel or OpenOffice; start using OpenPyXL to automate Excel documents.
Reading the documentation1:38
Be careful with your workbooks, always backup!1:10
Always back up your workbooks when using OpenPyXL to prevent data loss, since there is no undo button, and errors can overwrite your original data.
Introduction Quiz

Workbooks and Sheets introduction0:33
Creating Workbooks1:19
Opening Workbooks1:30
Writing and saving to Sheets, Creating and Deleting Sheets5:17
Copying Sheets2:55
Getting sheet by index and name3:37
Workbooks and Sheets Summary5:47
Learn to create and open workbooks, add and remove sheets, rename them, access sheets by index or name, copy worksheets, and enumerate sheet titles with openpyxl.
Workbooks and Sheets quiz

Manipulating Cells introduction0:28
Deleting and inserting rows and columns7:21
In this lecture, we are inserting and deleting rows and columns. Note that OpenPyXL has changed from a 0-based index to a 1-based index when inserting and deleting rows. The source code is updated to reflect these changes but the video shows the 0-based index usage.
The append method for rows3:55
Moving and copying Ranges of Cells7:22
Learn to move and copy cell ranges in openpyxl by offsetting ranges like A1:J10, moving cells, and duplicating values with iter_rows and worksheet cells.
Inserting formulas5:23
Tables5:00
Halfway through!0:31
Congratulations on reaching half the course. Post questions or comments in the discussion forum, offer feedback as a review, and continue studying.
Manipulating Cells quiz

Formatting & filtering introduction0:42
Learn cell formatting in Excel, including color, font, size, and data formats, copy and merge cells, apply autofilter for sorting, use the fold method, and configure page setups for printing.
Formatting cells, fonts, colors, cell type (date, text etc)16:56
Copying cell formatting3:18
CORRECTION: In the video we are setting the old_cell font to
old_cell.font = Font(name='Arial', size=18, color=colors.RED).
Since then OpenPyXL has updated to not use the colors constants RED, BLUE etc.
Instead we have to write the colors as a HEX value. The code for red is "FF0000" and thus the new line becomes:
old_cell.font = Font(name="Arial", size=18, color="FF0000")
https://www.rapidtables.com/web/color/RGB_Color.html has a picker as well as a table for HEX colors.
Merge and unmerge cells5:00
Learn to merge and unmerge cells in OpenPyXL, using range borders like A1:B1 and A1:C4, and understand how merging preserves the upper-left value while discarding others.
Autofilter5:13
Learn how to apply autofilter. Sorting cannot currently be done with OpenPyXL. I have attached the file 6.4_resources.py that contains the Win32 library that can apply the sort.
Freeze panes3:16
Page setup6:36
Learn to configure print page setups in openpyxl by applying paper size, orientation, and margins to sheets or workbooks, using page_setup options and predefined constants.
Fold4:14
Formatting and Filtering quiz

Useful Python code introduction0:43
Tkinter file dialogs8:31
Use tkinter file dialogs to open or save Excel workbooks with openPyXL, employing ask open file name and ask save as, and set the initial directory.
Creating files and folders5:28
Getting file path and name4:44
Learn to use pathlib to manage file paths and join the current working directory with file names. Retrieve files by extension with glob and open and read them in Python.
Opening multiple files4:08
Timing your scripts3:43
Time your Python scripts with the time module to measure OpenPyXL operations, using start and end timestamps. Observe how results vary with input size and avoid background processes.
Start Excel for viewing your work2:04
Start Excel for viewing your work using Python and the os module to launch Excel.exe and open your workbook with OpenPyXL, such as 8.6_Open_me.xlsx, enabling editing and quick verification.

Requirements

Basic Python Knowledge
Some kind of .xlsx viewer, like Excel or OpenOffice

Description

Requirements

Basic Python knowledge - Don't worry, we will cover each code snippet
You will need a computer with Python 3 installed, then we will cover how to install OpenPyXL
Some form of spreadsheet program. I use Excel, but you can also use OpenOffice or similar applications

Description

Become an Excel Power User - Learn how to control & automate Excel with Python and OpenPyXL

Do you have a bunch of Excel files that you need to format or put some value in a cell on each page? Do you need to create Charts on your spreadsheets or insert formulas? Maybe you just want to show off at the office! Whatever your needs, you can expect a comprehensive guide going through the nuts and bolts of how automating Excel works. The course dives straight into OpenPyXL, so you will be up and running creating and manipulating spreadsheets in no-time.

I have had so much use of OpenPyXL when dealing with spreadsheets. You can create advanced sorts and filters, insert and delete rows, copy cells, set custom formatting and much more. I even got a job based on my OpenPyXL knowledge. I am sure you will benefit from learning OpenPyXL if you have even the slightest interest in making your everyday life easier.

After taking this course you will:

Know how to create and manipulate Workbooks and Sheets
Read cell data with absolute and relative references
Iterate over cells and perform actions on each cell
Know how to delete rows and columns
Insert formulas and tables
Format your spreadsheets with fonts, colors and cell types
Understand how you could apply this knowledge to your own work
Create files and folders
Interact with open and save file dialogs

Whether it's gaming, business, engineering, or data you're passionate about, this course will give you everything you need for working on spreadsheets with Python. Take this course today, and begin your journey to having a full-fledged career as an Excel Power User!

Who this course is for:

Anyone interested in controlling and automating Excel with Python - with or without coding experience. People who want to get rid of manually editing hundreds of spreadsheets by hand
Especially suitable for those who would like to extract or add data on multiple spreadsheets or automate the creation of spreadsheets from input data
People who would like to know more about solving Excel problems with Python
People who would like to start consulting services by automating Excel tasks

Who this course is for:

Beginner and Intermediate Python users who want to automate Excel
Users who want to become an Excel Power User

Control Excel with Python & OpenPyXL

What you'll learn

Explore related topics

Course content

Introduction5 lectures • 7min

Using OpenPyXL, Workbooks and Sheets7 lectures • 21min

Reading data from Cells4 lectures • 16min

Iterating over Cells4 lectures • 11min

Manipulating Cells7 lectures • 30min

Formatting and Filtering8 lectures • 45min

Visuals4 lectures • 20min

Useful Python code with OpenPyXL7 lectures • 29min

Conclusion1 lecture • 1min

Requirements

Description

Who this course is for: