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)

Remember to add the Required Starter Code (lines [1-4]) to the top of each code snippet. This snippet will allow the code in this article to run error-free.

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

Required starter code:

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:

  • Line [1-3] creates three new Worksheets.
  • Line [4] saves the changes.
  • Line [5] closes the 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 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 [3] initializes the iterator to iterate through all the Worksheets.
  • Line [4] changes the current Worksheet title based on the genre list.
  • Line [5] saves the changes.
  • Line [6] closes the 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:

Worksheet 0 Jazz
Worksheet 1 Rock
Worksheet 2 Rap
Worksheet 3 Elevator

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 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 lambda to perform a sort based on the Worksheet Title.
  • Line [2] saves the changes.
  • Line [3] closes the 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 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.
  • Lines [3-4] loop until the end of the Worksheets.
  • Line [5] saves the changes.
  • Line [6] closes the 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