python-3.x 如何用openpyxl对xlsx列中的数据进行排序?

wj8zmpe1  于 2023-02-01  发布在  Python
关注(0)|答案(1)|浏览(247)

我想在Excel文件中按列C保存从A到Z的排序数据。
我的代码:

### EXCEL
# Set column names
A = 'SURNAME'
B = 'NAME'
C = 'sAMAccountName'

# Set worksheet
wb = Workbook() # create excel worksheet
ws_01 = wb.active # Grab the active worksheet
ws_01.title = "all inf" # Set the title of the worksheet
# Set first row
title_row = 1
ws_01.cell(title_row, 1, A) # cell(row, col, value)
ws_01.cell(title_row, 2, B)
ws_01.cell(title_row, 3, C)

data_row = 2
for user in retrieved_users:
    attributes = user['attributes']
    sAMAccountName = attributes['sAMAccountName']
    if(user_validation(sAMAccountName) == True):
        A = attributes['sn']
        B = attributes['givenName']
        C = sAMAccountName
        ws_01.cell(data_row, 1, str(A))
        ws_01.cell(data_row, 2, str(B))
        ws_01.cell(data_row, 3, str(C))
        data_row = data_row + 1
    
# Save it in an Excel file
decoded_users_all_inf = root_path + reports_dir + users_all_inf_excel_file
wb.save(decoded_users_all_inf)

我在代码中添加了什么和什么来实现这一点?

4urapxun

4urapxun1#

如果要对retrieved_users进行排序,则可以使用内置的list.sortkey来访问sAMAccountName

retrieved_users = [
    {"attributes": {"sn": "a", "givenName": "Alice", "sAMAccountName": "z"}},
    {"attributes": {"sn": "b", "givenName": "Bob", "sAMAccountName": "x"}},
    {"attributes": {"sn": "c", "givenName": "Charlie", "sAMAccountName": "y"}},
    ]

retrieved_users.sort(key=lambda d: d["attributes"]["sAMAccountName"])

retrieved_users包含

[{'attributes': {'sn': 'b', 'givenName': 'Bob', 'sAMAccountName': 'x'}},
 {'attributes': {'sn': 'c', 'givenName': 'Charlie', 'sAMAccountName': 'y'}},
 {'attributes': {'sn': 'a', 'givenName': 'Alice', 'sAMAccountName': 'z'}}]

另外,您可以执行ws.append(row)一次追加整行,而不是执行ws.cell(row, col, value)三次:

wb = Workbook()
ws = wb.active

ws.append(('SURNAME', 'NAME', 'sAMAccountName'))

相当于

wb = Workbook()
ws = wb.active

ws.cell(1, 1, 'SURNAME')
ws.cell(1, 2, 'NAME')
ws.cell(1, 3, 'sAMAccountName')

相关问题