如何用Python脚本覆盖Excel中的行？附代码片段

Let’s break this down for you—your current code uses xlsxwriter, which is great for creating new Excel files but can’t modify existing ones. Since you need to overwrite rows in an existing file, openpyxl is the right tool here (you already imported it, perfect!). Here’s a step-by-step implementation tailored to your code:

Step 1: Understand the Library Limitation #

First, a quick clarifier:

• xlsxwriter: Only creates new workbooks, can’t edit existing ones.
• openpyxl: Supports reading and writing .xlsx files, making it ideal for modifying existing rows.

Step 2: Full Working Code Example #

Here’s how to integrate your existing variables into a workflow that overwrites specific rows:

import openpyxl
import os
import sys

# Your existing variables
string0 = "Username"
stringhost = "Host"
string1 = "Last password change"
string2 = "Password expires"
string3 = "Password inactive"
string4 = "Account expires"
string5 = "Minimum number of days between password change"
string6 = "Maximum number of days between password change"
string7 = "Number of days of warning before password expires"
hostid = "---"

# Define your full Excel file path (fill in the rest of your sample filename)
file_path = '/media/sf_vboxshared/sample.xlsx'

# Check if the file exists to avoid errors
if not os.path.exists(file_path):
    print(f"Error: File {file_path} does not exist!")
    sys.exit(1)

# Load the existing workbook
wb = openpyxl.load_workbook(file_path)
ws = wb.active  # Use the active sheet, or specify by name: wb['Sheet1']

# Define which row you want to overwrite (Excel uses 1-based indexing)
target_row = 2  # Example: overwrite the second row

# Write your values to the target row, column by column
ws.cell(row=target_row, column=1, value=string0)
ws.cell(row=target_row, column=2, value=stringhost)
ws.cell(row=target_row, column=3, value=string1)
ws.cell(row=target_row, column=4, value=string2)
ws.cell(row=target_row, column=5, value=string3)
ws.cell(row=target_row, column=6, value=string4)
ws.cell(row=target_row, column=7, value=string5)
ws.cell(row=target_row, column=8, value=string6)
ws.cell(row=target_row, column=9, value=string7)
ws.cell(row=target_row, column=10, value=hostid)

# Save the changes back to the file
wb.save(file_path)
print(f"Successfully overwrote row {target_row} in {file_path}!")

Key Tips for Flexibility #

• Overwrite Multiple Rows: Wrap the cell-writing logic in a loop if you need to update several rows at once:

  # Overwrite rows 2 through 5
  for row_num in range(2, 6):
      ws.cell(row=row_num, column=1, value=f"User_{row_num}")
      ws.cell(row=row_num, column=2, value=stringhost)
      # Add other column values as needed
  

• Preserve Formatting: If you want to keep the original cell styles (fonts, colors, borders), copy the style from a reference cell (like your header row) before writing new values:

  # Copy style from header row (row 1) to target row
  for col in range(1, 11):
      target_cell = ws.cell(row=target_row, column=col)
      header_cell = ws.cell(row=1, column=col)
      target_cell.font = header_cell.font
      target_cell.fill = header_cell.fill
      target_cell.border = header_cell.border
  

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