Python and openyxl how to arrange columns of an excel the same way another excel columns are displayed

alim1990 0
Viewed 55

We have an excel file with the same column names of another excel file but in a shuffled way:

excel_1_columns = ["name", "address", "phone"]

and

excel_2_columns = ["address", "name", "phone"]

We want to arrange excel 2 the same way excel 1 is.

Actually these names are just for the purpose of making you understand but it can contain hundreds of columns.

excel_1_columns and excel_2_columns always have the same length but not the same order.

We want to use openpyxl to arrange both to look the same.

We had the idea of converting to data frames and then save into an excel, but we don't want to lose the formatting and the theme used with the original excels.

I started with getting columns names from excel 1:

excel_1_columns = []

for row in intro_sheet.iter_rows(min_row=1, max_row=1):
        for cell in row:
            excel_1_columns.append(cell.value)

My logic is to loop over excel_1_columns and append a new column into excel 2:

for col in col_order:
    excel_2.insert_cols(idx = 1)
    # rename
    excel_2.cell(row = 1, column = 1).value = str(col)

and then we work within excel 2 and move cell values from old columns into the new appended ones and delete the old ones.

How can we move cell values from column named old_phone for example to phone column that was appended in the for loop?

openpyxl python
1 Answers

My thought on your requirement. Assumes the cells are all data only with no formulas. If there are formulas there may be issues with the references. Read this section in the Openpyxl docs if you haven't already https://openpyxl.readthedocs.io/en/stable/editing_worksheets.html?highlight=delete_cols%20translate#moving-ranges-of-cells

I haven't bothered to read an 'original' workbook header list since adding that feature should be straight forward. For my example code the required sequence is ascending numeric order. I've included a list in that order which you would obtain from the 'excel 1' sheet; col_order = ['header1', 'header2', 'header3', 'header4', 'header5', 'header6', 'header7']

For testing I created a sheet with 7 columns with these headers, jumbled so they do not match the lists sequencence.

The example code will iterate row 1 (header row) and compare the current header in each column to its required position per col_order. Depending on this check one of two options is taken;

  1. Correct column position; nothing is changed and the code moves to the next column.
  2. Incorrect column position one of two actions is taken. The required column position of the current column is checked;
    a) if the column is empty the current column data is moved directly to the correct column
    b) if the column has another columns data at that moment, the current column data is moved to a holding column. Holding columns start at the end of the existing columns (ws.max_column)

The copy process also copies the cell style information so that detail is preserved. After the column data is copied its originating column is cleared of all data so that column is free for data to be entered from the correct column later. The deletion of the column data is achieved by deleting the column then inserting a column at the same location.

At the end of the first iteration through the header row all columns are either in their correct position or in a holding column.
The code then processes the holding columns moving them to their correct location all of which should have been cleared of data in the previous iteration.

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from copy import copy


def move_cells(sheet, cur_pos, cur_col):
    for existing_cell in sheet[get_column_letter(cur_pos + 1)]:
        new_cell = existing_cell.offset(column=cur_col)
        new_cell.value = existing_cell.value
        if existing_cell.has_style:
            new_cell.font = copy(existing_cell.font)
            new_cell.border = copy(existing_cell.border)
            new_cell.fill = copy(existing_cell.fill)
            new_cell.number_format = copy(existing_cell.number_format)
            new_cell.protection = copy(existing_cell.protection)
            new_cell.alignment = copy(existing_cell.alignment)
    del_row = cur_pos + 1
    sheet.delete_cols(del_row, amount=1)
    sheet.insert_cols(del_row, amount=1)


col_order = ['header1', 'header2', 'header3', 'header4', 'header5', 'header6', 'header7']
excel_file = 'foo1.xlsx'
wb = load_workbook(file)
ws = wb['Sheet1']

total_columns = ws.max_column
current_column = 0
holding_column = total_columns
print("")

list_with_values = []
for enum, cell in enumerate(ws[1]):
    list_with_values.append(cell.value)
    required_position = col_order.index(cell.value)
    print("Column with Header '" + cell.value + "': current position: "
          + str(enum) + " Required position: " + str(required_position))

    if required_position != enum:
        print("This column is in the wrong position")
        header_check = cell.offset(column=required_position - enum).value
        print("Header of required position is : " + str(header_check))
        if header_check is None:
            # The column is clear we can move the correct column to it
            print("The required column is not in use, moving this column direct to " + str(required_position))
            if required_position > enum:
                current_column = enum - required_position
            else:
                current_column = -abs(enum - required_position)
        else:
            # The column is in use, move to holding column
            print("The required column is in use, move the column to holding column " + str(holding_column))
            current_column = holding_column - enum
            holding_column += 1

        print("Moving cells; enum: " + str(enum) + " current_column: " + str(current_column))
        move_cells(ws, enum, current_column)

    else:
        print("This column is in the right position. It will not be moved.")

    print("--------------------------------------------------\n")

print("Moving columns placed in holding to proper positions")
for column in ws.iter_cols(min_row=1, max_row=1, min_col=total_columns+1, max_col=ws.max_column):
    for cell in column:
        required_position = col_order.index(cell.value)
        holding_column = cell.col_idx - 1
        print("Moving " + str(cell.value) + " from column " + str(holding_column)
              + " to column " + str(required_position))
        if required_position > holding_column:
            current_column = holding_column - required_position
        else:
            current_column = -abs(holding_column - required_position)
        move_cells(ws, holding_column, current_column)
        holding_column += 1

wb.save(excel_file)

Images would not format properly for some reason so I have just left as links.
Image Before running code
https://i.stack.imgur.com/hlXET.jpg
Image After running code
https://i.stack.imgur.com/YMsM7.png
moken0
Related