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[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)I got an excellent question in my OpenPyXL course. The question was how to transfer data from one Excel workbook to another with OpenPyXL. Transferring data only is easier, as there is no need to worry about the formatting of cells and sheets. The complete code is available at the bottom of this post.
The question was how to copy data from one sheet in a workbook to several other sheets in a newly created workbook. The source workbook is WB1 with the source sheet WS1. The source sheet has 1000 rows of data. The data shall be copied to the new workbook WB2 and the sheets WS1 to WS10.
To copy the values we need to import Workbook and load_workbook from the OpenPyXL library. We can now load our existing workbook, WB1. WB1 = load_workbook("Source.xlsx", data_only=True). The next thing we need to do is set which sheet we are going to copy the data from. We name the sheet WB1_WS1 WB1_WS1 = WB1["WS1"] . After that we are ready to create a new workbook with WB2 = Workbook(). Notice the brackets for the method.
The question stated that 10 sheets should be created, with the names WS1 to WS10. We can create the sheets with a for loop. Each sheet is created with create_sheet(f"WS{i}"). Notice the usage of Pythons f-string. We then remove the default created sheet, Sheet1. We could also of course have renamed it.
# Create WB2 sheets WS1-WS10
for i in range(1, 11):
WB2.create_sheet(f"WS{i}")
# delete first sheet
WB2.remove(WB2.worksheets[0])The next thing is to create a list for holding our copy ranges, and also which sheets we want to copy the data to. The copy_ranges list holds how many rows we need to copy from the source sheet to the sheets defined in copy_to_sheets.
# Define the copy ranges and sheets copy_ranges = [100, 200, 50, 300, 350] copy_to_sheets = ["WS1", "WS2", "WS3", "WS4", "WS4"]
We start with a for loop, that iterates through the copy_ranges list. for i in range( len(copy_ranges)): We then specify which sheet is in turn for copying ws = WB2[ copy_to_sheets[i] ]. Notice how we specify the sheet with the copy_to_sheets list. When i is 1 we select WS1 and so on. We also initialize our row_offset to 1 so that we can keep track of which rows to copy next. We then set the row_offset with yet another for loop. We increase the offset i times with the corresponding values from copy_ranges.
for s in range(i):
offset += copy_ranges[s]Now it is time to fill our sheets with data! we traverse through our offset range with a for loop and set the values of the corresponding sheet. First we get the row with for j in range(offset, offset + copy_ranges[i]):. Next up are the cells in each row:
for row in WB1_WS1.iter_rows(min_row=j, max_row=j, min_col=1, max_col=WB1_WS1.max_column):.
We get the values for values_row with a list comprehension [cell.value for cell in row]. Finally, we append the row to the sheet with ws.append(values_row).
# Copy the row with the help of iter_rows, append the row
for j in range(offset, offset + copy_ranges[i]):
#if j == 0:
# continue
for row in WB1_WS1.iter_rows(min_row=j, max_row=j, min_col=1, max_col=WB1_WS1.max_column):
values_row = [cell.value for cell in row]
ws.append(values_row)To wrap up, we save the workbook: WB2.save("WB2.xlsx")
That's it! Please comment below if you have questions or any feedback. See you later!
Use this link, Control Excel with Python & OpenPyXL or this code SAVEOPENPYXL to get the course for a discount on Udemy.com
#!/usr/bin/python
# -*- coding: utf-8 -*-
"""
Could you please suggest how to copy the data from on work book to other book with specified rows
Source: Excel work book "WB1" having work sheet "WS1", This sheet having 1000 rows of data
Destination: New work book 'WB2' and work sheets WS1,WS2...WS10
Could you please suggest the code for following condition:
Copy the first 100 rows data and paste it WS1 sheet
Copy the next 200 rows data and paste it WS2 sheet
Copy the next 50 rows data and paste it WS3 sheet
Copy the next 300 rows data and paste it WS4 sheet
Copy the next 350 rows data and paste it WS4 sheet
"""
from openpyxl import Workbook, load_workbook
WB1 = load_workbook("Source.xlsx", data_only=True)
WB1_WS1 = WB1["WS1"]
WB2 = Workbook()
# Create WB2 sheets WS1-WS10
for i in range(1, 11):
WB2.create_sheet(f"WS{i}")
# delete first sheet
WB2.remove(WB2.worksheets[0])
# Define the copy ranges and sheets
copy_ranges = [100, 200, 50, 300, 350]
copy_to_sheets = ["WS1", "WS2", "WS3", "WS4", "WS4"]
# Copy the values from the rows in WB1 to WB2.
for i in range( len(copy_ranges)):
# Set the sheet to copy to
ws = WB2[ copy_to_sheets[i] ]
# Initialize row offset
offset = 1
# Set the row offset
for s in range(i):
offset += copy_ranges[s]
# Copy the row with the help of iter_rows, append the row
for j in range(offset, offset + copy_ranges[i]):
#if j == 0:
# continue
for row in WB1_WS1.iter_rows(min_row=j, max_row=j, min_col=1, max_col=WB1_WS1.max_column):
values_row = [cell.value for cell in row]
ws.append(values_row)
# Save the workbook
WB2.save("WB2.xlsx")I have finally published my first course, Control Excel with Python & OpenPyXL!
The course covers the ins and outs of how to control and automate Excel with the OpenPyXL library.
I am offering this course for a discount for you who are reading this post.
Use this link, Control Excel with Python & OpenPyXL or this code SAVEOPENPYXL to get the course for a discount on Udemy.com
Python + Excel = True? Do you have a huge Excel spreadsheet with a lot of data that you need have sorted in a special way? Do you feel that you would need to assign one of your staff members to update the Excel spreadsheet for two months? That is when you need Python to interact with your spreadsheet! Here we are going to cover how to connect to an Excel spreadsheet with OpenPyXl. (more…)