Transpose or rotate Excel cell data with OpenPyXL

Introduction

In Excel there is a neat Paste Special method named Transpose. The transpose is done by copying your cells and then pasting the data to another place with the transpose option. We can easily do the same with Python and OpenPyXL.

There are currently (To the author's knowledge, December 2019) no transpose functions or methods written in OpenPyXL. I have made a small code sample that transposes rows to columns and columns to rows. Only cell data is transposed with these functions. The functions are divided into three main functions.

The functions

Transpose

The first function is Transpose, which takes an OpenPyXL worksheet and which rows and columns to transpose the values to/from. The function uses two loops, which first traverses the rows and then the columns. We set the worksheet's cell values with ws.cell(row=col,column=row).value = ws.cell(row=row,column=col).value. To set the values, we just invert the column and row values.

Transpose_row_to_col and transpose_col_to_row

I have expanded these functions to accept also the target cell to paste the values in, and also the option to delete the transposed cells values. The functions are identical except for the usage of iter_rows and iter_cols. The target_cell_address is a tuple consisting of the cell's absolute address. If delete_source is True the values in the transposed cells are deleted.

First, an empty list called cell_values is created. After that we use the iter_rows method to iterate through the worksheets rows, one by one. The iter_rows produces cells from the worksheet, by row while the iter_cols returns cells by column. The cells are yielded from a generator.

We append the values of the cells to our cell_ranges list. We then use the fill_cells function to enter the values again to our transposed cells.

I hope that this helps you in transposing and rotating your cell values. As always, if you have any questions or comments please let me know.

Complete code

#!/usr/bin/env python3
"""

Transpose row values to column values and vice versa

"""
import openpyxl
def transpose(ws, min_row, max_row, min_col, max_col):

    for row in range(min_row, max_row+1):

        for col in range(min_col, max_col+1):

            ws.cell(row=col,column=row).value = ws.cell(row=row,column=col).value
def transpose_row_to_col(ws, min_row, max_row, min_col, max_col, target_cell_address=(1,1), delete_source=False):

    cell_values = []

    for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):

        for cell in row:

            cell_values.append(cell.value)

            if delete_source:

                cell.value = ""

    fill_cells(ws, target_cell_address[0], target_cell_address[1], cell_values)
def transpose_col_to_row(ws, min_row, max_row, min_col, max_col, target_cell_address=(1,1), delete_source=False):

    cell_values = []

    for col in ws.iter_cols(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):

        for cell in col:

            cell_values.append(cell.value)

            if delete_source:

                cell.value = ""

    fill_cells(ws, target_cell_address[0], target_cell_address[1], cell_values)
def fill_cells(ws, start_row, start_column, cell_values):

    row = start_row

    column = start_column

    for value in cell_values:

        ws.cell(row=row,column=column).value = value

        row += 1
if __name__ == "__main__":

    # Open workbook

    file_name = "Transpose_cell_values.xlsx"

    wb = openpyxl.load_workbook(file_name)

    ws1 = wb.worksheets[0]

    transpose(ws1, min_row=1, max_row=1, min_col=1, max_col=5)

    #transpose_col_to_row(ws1, min_row=1, max_row=10, min_col=1, max_col=1, target_cell_address=(1,2))

    wb.save(file_name)