Let's refactor your code to work with your existing Excel file instead of creating a new one. Here's how you can tackle both of your requirements smoothly using openpyxl:

Step-by-Step Breakdown #

• Load the existing workbook: Ditch the write-only new workbook and use openpyxl.load_workbook() to access your existing file.
• Write values to Column F: Column F maps to index 6 (openpyxl uses 1-based indexing for columns). We’ll loop through your values list and drop each entry into the corresponding row in Column F, skipping the header row.
• Add the "Order" column: First, find the position of the last existing column, then add the "Order" header and fill the column with your values.

Complete Modified Code #

from openpyxl import load_workbook

values = [5, 7, 1]

# Load your target Excel file (replace with your actual file path)
wb = load_workbook('your_existing_file.xlsx')
ws = wb.active  # Uses the first worksheet; swap with wb['SheetName'] if targeting a specific sheet

# Requirement 1: Write values to Column F (column index 6)
for row_idx, val in enumerate(values, start=2):  # Start at row 2 to avoid overwriting headers
    ws.cell(row=row_idx, column=6, value=val)

# Requirement 2: Add "Order" column at the end and populate values
last_col = ws.max_column + 1
# Set the new column header
ws.cell(row=1, column=last_col, value="Order")
# Fill the column with your values
for row_idx, val in enumerate(values, start=2):
    ws.cell(row=row_idx, column=last_col, value=val)

# Save changes (use the original filename to overwrite, or a new name to keep the original)
wb.save('modified_excel.xlsx')

Quick Notes #

• Replace 'your_existing_file.xlsx' with the actual path to your Excel file.
• If your data lives in a named worksheet (not the default active one), replace wb.active with wb['YourSheetName'].
• Saving to the original filename will overwrite your existing file—use a new name if you want to preserve the original.

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