Swapping columns in Excel manually can be tedious, especially when dealing with large datasets. Python automation can simplify this task by allowing you to write a script that automatically swaps the contents of two columns. In this tutorial, we will use the openpyxl library to create a Python script that swaps two columns in an Excel file effortlessly.

To begin, we will first create a new Excel workbook and add some sample data to it. Then, we will define a function that swaps the contents of two specified columns. Finally, we will save the modified Excel file with the swapped columns. This automation can save you time and effort, especially when working with Excel files regularly.

Let us take a excel sheet which has columns as Name,Age and Gender. the excel sheet is arranged in the manner below, we have to swap the columns

Full Code to swap column

from openpyxl import Workbook

# Create a new Excel workbook
wb = Workbook()
ws = wb.active

# Add data to the Excel sheet
data = [
    ['Name', 'Age', 'Gender'],
    ['Alice', 25, 'Female'],
    ['Bob', 30, 'Male'],
    ['Charlie', 35, 'Male'],
    ['David', 40, 'Male'],
    ['Eve', 45, 'Female'],
    ['Frank', 50, 'Male'],
    ['Grace', 55, 'Female'],
    ['Helen', 60, 'Female'],
    ['Ivy', 65, 'Female']
]

for row in data:
    ws.append(row)

# Swap columns (e.g., swap columns 'A' and 'B')
def swap_columns(sheet, col1, col2):
    for i in range(1, sheet.max_row + 1):
        # Get the values of the cells in the two columns
        cell1 = sheet[col1 + str(i)].value
        cell2 = sheet[col2 + str(i)].value
        # Swap the values
        sheet[col1 + str(i)] = cell2
        sheet[col2 + str(i)] = cell1

# Swap columns 'A' and 'B'
swap_columns(ws, 'A', 'B')

# Save the workbook
wb.save('data.xlsx')

Swapped Output

Explanation of the above code:

1. from openpyxl import Workbook: This line imports the Workbook class from the openpyxl module, which is used for creating and manipulating Excel files.
2. wb = Workbook(): This line creates a new Excel workbook object (wb) using the Workbook class imported earlier.
3. ws = wb.active: This line gets the active sheet (ws) from the workbook. By default, a new workbook has one sheet, which is the active sheet.
4. data = [...]: This is a list containing rows of data to be added to the Excel sheet. Each row is represented as a list of values (e.g., ['Name', 'Age', 'Gender']).
5. for row in data: ws.append(row): This loop iterates over each row in the data list and appends it to the active sheet (ws) of the workbook. This adds the data to the Excel sheet.
6. def swap_columns(sheet, col1, col2):: This line defines a function swap_columns that takes three arguments: sheet (the Excel sheet object), col1 (the letter representing the first column), and col2 (the letter representing the second column).
7. for i in range(1, sheet.max_row + 1):: This loop iterates over each row in the Excel sheet (sheet). max_row gives the highest row number that has content.
8. cell1 = sheet[col1 + str(i)].value: This line gets the value of the cell in column col1 and row i.
9. cell2 = sheet[col2 + str(i)].value: This line gets the value of the cell in column col2 and row i.
10. sheet[col1 + str(i)] = cell2: This line sets the value of the cell in column col1 and row i to the value of cell2, effectively swapping the values of the two cells.
11. sheet[col2 + str(i)] = cell1: This line sets the value of the cell in column col2 and row i to the value of cell1, completing the swap.
12. swap_columns(ws, 'A', 'B'): This line calls the swap_columns function on the active sheet ws, swapping the values in columns ‘A’ and ‘B’.
13. wb.save('data.xlsx'): This line saves the workbook (wb) to a file named 'data.xlsx'.

Conclusion,

In this article, we have learned how to use Python automation to swap columns in an Excel file. By utilizing the openpyxl library, we were able to create a script that simplifies the task of column swapping, making it efficient and easy. Python’s ability to automate such tasks can significantly enhance productivity, especially when dealing with large datasets. By applying the concepts learned in this tutorial, you can automate various other Excel-related tasks, making data manipulation and analysis more streamlined.