excel 如何将某个单元格添加到其相应的列/行

aiqt4smr 于 2022-11-26 发布在其他

关注(0)|答案(1)|浏览(297)

我有一个Excel文件，看起来像

。
对于每一个名字，我想为每一组添加各自的单元格，所以我希望一个for循环迭代+4行来遍历所有的名字。
以下是我目前所做的工作：

import openpyxl

 doc = openpyxl.load_workbook('World Cup Bet Tournament.xlsx')

 doc_activation = doc.active

 ############################################
 """ Creating the final dictionary """
 final_dict = {}
 groups_dict = {}
 group_list = []
 ############################################

 for row_1 in range(2, 42):
     for col_1 in doc_activation.iter_cols(1, 1):
         name = col_1[row_1].value
         if name is None:
             break
         else:
             final_dict[name] = groups_dict
             for row_2 in range(1, 2):
                 for col_2 in doc_activation.iter_cols(2, 9):
                     group = col_2[row_2].value
                     groups_dict[group] = group_list

  print(final_dict)

和输出：

{'1. Mathias L.R': {'GROUP A': [], 'GROUP B': [], 'GROUP C': [], 'GROUP D': [], 'GROUP E': [], 'GROUP F': [], 'GROUP G': [], 'GROUP H': []}, '2. Noah L.R': {'GROUP A': [], 'GROUP B': [], 'GROUP C': [], 'GROUP D': [], 'GROUP E': [], 'GROUP F': [], 'GROUP G': [], 'GROUP H': []}, '3. Jessy P.N': {'GROUP A': [], 'GROUP B': [], 'GROUP C': [], 'GROUP D': [], 'GROUP E': [], 'GROUP F': [], 'GROUP G': [], 'GROUP H': []}, '4. Enzo B.': {'GROUP A': [], 'GROUP B': [], 'GROUP C': [], 'GROUP D': [], 'GROUP E': [], 'GROUP F': [], 'GROUP G': [], 'GROUP H': []}, '5. Savio M.': {'GROUP A': [], 'GROUP B': [], 'GROUP C': [], 'GROUP D': [], 'GROUP E': [], 'GROUP F': [], 'GROUP G': [], 'GROUP H': []}, '6. Jonathan M.': {'GROUP A': [], 'GROUP B': [], 'GROUP C': [], 'GROUP D': [], 'GROUP E': [], 'GROUP F': [], 'GROUP G': [], 'GROUP H': []}, '7. Hans M.': {'GROUP A': [], 'GROUP B': [], 'GROUP C': [], 'GROUP D': [], 'GROUP E': [], 'GROUP F': [], 'GROUP G': [], 'GROUP H': []}, '8. J-E': {'GROUP A': [], 'GROUP B': [], 'GROUP C': [], 'GROUP D': [], 'GROUP E': [], 'GROUP F': [], 'GROUP G': [], 'GROUP H': []}, '9. Schadrac ': {'GROUP A': [], 'GROUP B': [], 'GROUP C': [], 'GROUP D': [], 'GROUP E': [], 'GROUP F': [], 'GROUP G': [], 'GROUP H': []}, '10. Mathieu G.': {'GROUP A': [], 'GROUP B': [], 'GROUP C': [], 'GROUP D': [], 'GROUP E': [], 'GROUP F': [], 'GROUP G': [], 'GROUP H': []}}

因此，对于每个代表名字的键，都有一个字典值，这个字典的键是每个组的名字。它的值是一个列表，包含每个国家对应的球员。

excel

来源：https://stackoverflow.com/questions/74519801/how-can-i-add-a-certain-cell-to-its-respective-column-row

1条答案

按热度按时间

jyztefdp1#

您只需要根据单元格中的值构建用户组，然后将其添加到以组名为关键字的组字典中，例如GROUPA、GROUPB等，然后将其添加到用户名下的整个字典中。
请参阅示例代码

import openpyxl

doc = openpyxl.load_workbook('World Cup Bet Tournament.xlsx')
doc_activation = doc.active

max_rows = doc_activation.max_row
max_cols = doc_activation.max_column

group, name = '', ''
groupdict, userdict = {}, {}
group_name_offset = 3

for min_row in range(2, max_rows+1, 4):
    group_name_offset -= 4
    for column_list in doc_activation.iter_cols(min_col=2, max_col=max_cols, min_row=min_row, max_row=min_row+3):
        group_list = []
        for count, cell in enumerate(column_list, 1):
            if count == 1:
                group = cell.offset(row=group_name_offset).value.replace(" ", "")
                if cell.column_letter == 'B':
                    name = cell.offset(column=-1).value
                    groupdict = {}
            group_list.append(cell.value)
        groupdict[group] = group_list
        userdict[name] = groupdict

### Print the User's groups
for name, groups in userdict.items():
    print(name)
    for key, teams in groups.items():
        print(f'{key} : {teams}')

输出量

1. Mathias L.R
GROUPA : ['1. Pays-Bas', '2. Sénégal', '3. Équateur', '4. Qatar']
GROUPB : ['1. Angleterre', '2. Pays de Ga', '3. États-Unis', '4. Iran']
GROUPC : ['1. Argentine', '2. Pologne', '3. Mexique', '4. Arabie Saou']
GROUPD : ['1. Danemark', '2. France', '3. Tunisie', '4. Australie']
GROUPE : ['1. Espagne', '2. Allemagne', '3. Japon', '4. Costa Rica']
GROUPF : ['1. Belgique', '2. Canada', '3. Croatie', '4. Maroc']
GROUPG : ['1. Brésil', '2. Cameroun', '3. Serbie', '4. Suisse']
GROUPH : ['1. Portugal', '2. Ghana', '3. Uruguay', '4. Corée du Su']
2. Noah L.R
GROUPA : ['1. Pays-Bas', '2. Sénégal', '3. Équateur', '4. Qatar']
GROUPB : ['1. Angleterre', '2. États-Unis', '3. Pays de Ga', '4. Iran']
GROUPC : ['1. Argentine', '2. Mexique...

赞(0）回复(0）举报 2022-11-26

我来回答

excel 如何将某个单元格添加到其相应的列/行

1条答案

相关问题

热门标签

最新问答