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 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.
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.
#!/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, target_cell_address, 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, target_cell_address, 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 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)