如何使用 openpyxl 交换列 [英] How to swap columns using openpyxl

问题描述

我有 .xlsx 文件.行很好，值也很好.但我需要按新列位置列表更改列顺序，例如:old = [1, 2, 3, 4]new = [2, 1, 4, 3]

I've .xlsx file. Rows are good, values are just fine. But i need to change the columns order by list of new columns positions, e.g: old = [1, 2, 3, 4] new = [2, 1, 4, 3]

已检查文档 - 此问题没有简单的选项.我试图遍历列，所以:

Docs are checked - there is no straightforward options for this problem. I've tried to iterate over columns, so:

old = {cell.column: cell.value for cell in ws[1]}.keys() # [1, 2, 3, 4]
new = [2, 1, 4, 3]

for i, col in enumerate(ws.iter_cols(max_col=old[-1]), 1):
    if old[i-1] != new[i-1]:
        for one in ws[get_column_letter(i)]:
            old_cell = one
            new_cell = ws.cell(old_cell.row, old[new[i-1]]-1)
            new_cell.value, old_cell.value = old_cell.value, new_cell.value
        old[i] = new_cell.column
        old[new_cell.column] = old_cell.column

...但它只适用于少数情况.可能我缺少一些通用的解决方案.

...but it work only for a few cases. Probably i'm missing some general solution.

在和它应该是，例如，old = [1, 2, 3, 4] new = [2, 1, 4, 3]:

At the and it should be, for example, old = [1, 2, 3, 4] new = [2, 1, 4, 3]:

输入文件:

x A  B  C  D 
1 A1 B1 C1 D1
2 A2 B2 C2 D2
3 A3 B3 C3 D3
4 A4 B4 C3 D4

输出文件:

x A  B  C  D 
1 B1 A1 D1 C1
2 B2 A2 D2 C2
3 B3 A3 D3 C3
4 B4 A4 D3 C4

推荐答案

您当前的方法有覆盖单元格的风险.我很想以正确的顺序将单元格从现有列移动到新列，然后删除旧列.

Your current approach risks overwriting cells. I'd be tempted to move the cells from existing columns to new columns in the correct order and then delete the old ones.

for c in ws['A']:
    new_cell = c.offset(column=6)
    new_cell.value = c.value

for c in ws['B']:
    new_cell = c.offset(column=5)
    new_cell.value = c.value

ws.delete_cols(min_col=1, max_col=4)

这只是为了给您提供可以优化和参数化的总体思路，以便您可以一次处理每一行.

This is just to give you the general idea could be optimised and parametrised so you could do each row at once.

或者你可以使用 move_range:

Or you could use move_range:

ws.move_range(min_col=1, max_col=1, min_row=1, max_row=ws.max_row, cols=5)

无论哪种情况，请注意不要在移动之前覆盖现有单元格.

In either case be careful not to overwrite existing cells before you've moved them.

这篇关于如何使用 openpyxl 交换列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！