如何用openpyxl对Excel C列数据按A-Z排序？附现有代码

Hey there! To get your Excel output sorted alphabetically by the sAMAccountName (Column C), the cleanest approach is to collect and sort your valid user data first, then write it to Excel—this is more efficient than sorting after writing to the worksheet. Here's exactly where and what to modify in your code:

Step 1: Collect Valid User Data #

Instead of writing directly to Excel as you validate users, first gather all valid entries into a list. Add this right before your for user in retrieved_users: loop:

# Create an empty list to store valid user data
valid_users = []

Then adjust your user loop to populate this list instead of writing to Excel immediately:

for user in retrieved_users:
    attributes = user['attributes']
    sAMAccountName = attributes['sAMAccountName']
    if user_validation(sAMAccountName):  # You can omit "== True" for cleaner code
        # Store user data as a dictionary for easy sorting
        user_entry = {
            'surname': attributes['sn'],
            'givenName': attributes['givenName'],
            'sAMAccountName': sAMAccountName
        }
        valid_users.append(user_entry)

Step 2: Sort the Valid Users List #

After collecting all valid users, sort the list alphabetically by sAMAccountName. Add this right after the loop:

# Sort users by sAMAccountName (case-insensitive A-Z order)
valid_users.sort(key=lambda x: x['sAMAccountName'].lower())

Using .lower() ensures the sorting ignores uppercase/lowercase differences (e.g., "Alice" and "alice" are treated the same). If you want case-sensitive sorting, just remove .lower().

Step 3: Write the Sorted Data to Excel #

Now loop through the sorted list to populate your worksheet. Replace your original Excel-writing loop with this:

data_row = 2
for user in valid_users:
    ws_01.cell(data_row, 1, str(user['surname']))
    ws_01.cell(data_row, 2, str(user['givenName']))
    ws_01.cell(data_row, 3, str(user['sAMAccountName']))
    data_row += 1

Step 4: Save the Worksheet #

Keep your existing save code as-is—it will now save the sorted data:

decoded_users_all_inf = root_path + reports_dir + users_all_inf_excel_file
wb.save(decoded_users_all_inf)

Alternative: Sort After Writing to Excel #

If you prefer to sort directly in the worksheet after writing all data (less efficient but useful if you need to keep the original write flow), add this code right before wb.save():

# Define the range of data to sort (from row 2 to last data row, columns A-C)
last_row = data_row - 1
sort_range = f"A2:C{last_row}"

# Add sort condition for Column C (sAMAccountName)
ws_01.auto_filter.add_sort_condition(
    f"C2:C{last_row}",
    descending=False,
    case_sensitive=False
)
# Apply the filter/sort
ws_01.auto_filter.ref = f"A1:C{last_row}"

This uses OpenPyXL's built-in sorting functionality to reorder the rows after they're written.

Either approach will give you the A-Z sorted Column C you need!

内容的提问来源于stack exchange，提问作者Kubix