Python Excel – Basic Worksheet Operations

Part 4 in the Working with Excel series focuses on Worksheet(s) manipulation.  

Background

After completing Part 3 of this series, you should be comfortable using Python and openpyxl to:

• append row(s),
• modify data,
• insert column(s),
• delete row(s) and column(s)

The j-greats.xlsx file should exist on your system. If you do not have this particular file, click here for instructions.

Add the following code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

from openpyxl import load_workbook

file_name = 'j-greats.xlsx'
w_book    = load_workbook(filename=file_name)
w_sheet   = w_book.active

Add Worksheet(s)

In the j-greats.xlsx file, there is currently one Worksheet (Sheet1). The code below will add a few additional Worksheets to the Workbook.

w_book.create_sheet('Sheet2')
w_book.create_sheet('Sheet3')
w_book.create_sheet('Sheet4')

w_book.save(file_name)
w_book.close()

The openpyxl library does not have a method to create multiple Worksheets with one line of code. Therefore, the code to perform this task is as follows:

• Lines [1-3] create three new Worksheets.
• Line [4] saves the changes.
• Line [5] closes the open Workbook.

Output

Rename Single Worksheet

Worksheet titles can be changed (renamed) as follows.

With the j-greats.xlsx file open as shown in the Required Starter Code, append the following two lines of code and run.

w_sheet.title = 'Test'
w_book.save(file_name)
w_book.close()

• Line [1] changes the Worksheet Title from Sheet1 to Test.
• Line [2] saves the changes.
• Line [3] closes the open Workbook.

Output

Rename Multiple Worksheets

This example iterators through all Worksheets in the Workbook and changes (renames) each one. Before running this code, ensure the j-great.xlsx file contains four Worksheets, or modify the code as needed.

genre = ['Jazz', 'Rock', 'Rap', 'Elevator']

for sheet, genre in zip(w_book, genre):
    sheet.title = genre
w_book.save(file_name)
w_book.close()

• Line [1] contains a list of new Worksheet names.
• Line [2] initializes the iterator to iterate through all the Worksheets.
  • Line [3] changes the current Worksheet title based on the genre list.
• Line [4] saves the changes.
• Line [5] closes the open Workbook.

Output

Reorder Multiple Worksheets using Worksheets Numbers

From time to time, the Worksheet order will need to be changed. The examples below offer two ways to reorder.

At this point, the j-greats-xlsx file should now contain four new Worksheets in the following order:

The code below leaves Worksheet 0 in the same position and reorders the remainder. List Comprehension accomplishes this.

new_order = [0, 3, 1, 2]
w_book._sheets = [w_book._sheets[i] for i in new_order]
w_book.save(filename=file_name)
w_book.close()

• Line [1] assigns a list of the new Worksheets order to the new_order variable.
• Line [2] uses List Comprehension to re-arrange the order.
• Line [3] saves the changes.
• Line [4] closes the open Workbook.

Output

Reorder Multiple Worksheets in Alphabetical Order

Another way to reorder Worksheets is to sort the Titles in alphabetical order. For this example, the lambda function takes care of the reorder. A one-liner at its finest!

w_book._sheets.sort(key=lambda ws: ws.title)
w_book.save(filename=file_name)
w_book.close()

• Line [1] accesses the Worksheets in the Workbook and uses a lambda to perform a sort based on the Worksheet Title.
• Line [2] saves the changes.
• Line [3] closes the open Workbook.

Output

Delete Single Worksheet

To delete a single Worksheet, run the following code.

w_book.remove('Elevator')
w_book.save(filename=file_name)
w_book.close()

• Line [1] removes the referenced Worksheet Elevator.
• Line [2] saves the changes.
• Line [3] closes the open Workbook.

Output

Delete Multiple Worksheets

To delete multiple Worksheets, run the following code. After running this code, the only remaining Worksheet is Jazz.

print(w_book.get_sheet_names())
for ws in w_book:
    if ws.title != 'Jazz':
        w_book.remove(ws)
w_book.save(filename=file_name)
w_book.close()

• Line [1] displays a list of the Worksheet names. This list returns: ['Jazz', 'Rap', 'Rock']
• Line [2] initializes the loop.
  • Line [3] checks to see if the Worksheet title is not equal to Jazz.
    • Line [4] executes and removes the Worksheet if Line [7] is True.
• Line [5] saves the changes.
• Line [6] closes the open Workbook.

Output

Calculate Total Rows/Columns in Worksheet

In this step, the total number of rows and columns containing data on the active Worksheet will be output to the terminal.

print('Total Rows: ' + str(w_sheet.max_row))
print('Total Cols: ' + str(w_sheet.max_column))

Output

Total Rows: 12
Total Cols: 8

Summary

In this article, we learned how to:

• Add Worksheet(s)
• Rename Worksheet(s)
• Reorder Worksheet(s)
• Delete Worksheet(s)
• Calculate Total Rows/Columns in Worksheet

Next Up

Part 5 in Working with Excel will focus on how to:

• Calculate using Excel Sum
• Calculate using Excel Average
• Calculate using Excel Max
• Configure Current Date