如何将Google Sheets数据写入Microsoft Excel？Python新手求助

Hey there! You’re spot-on about the problem—openpyxl doesn’t support directly writing a full array all at once; you need to iterate through your rows and add them one by one (or use built-in methods that handle iterables). Let’s break down how to fix this step by step.

Step 1: Basic Setup with openpyxl #

First, make sure you’ve imported openpyxl and set up your workbook/worksheet:

import openpyxl

# Create a new workbook and select the default active worksheet
wb = openpyxl.Workbook()
ws = wb.active
# Optional: Give your worksheet a meaningful name
ws.title = "Google Sheets Export"

Step 2: Write Your Array Data Row by Row #

Assuming your Google Sheets data is stored as a 2D list (each sublist represents a single row of data), the append() method is your best friend—it lets you add an entire row to the worksheet in one go:

# Example: Your data pulled from Google Sheets (2D array)
google_sheets_data = [
    ["Product", "Price", "Stock"],
    ["Laptop", 999.99, 15],
    ["Mouse", 24.99, 100],
    ["Keyboard", 49.99, 60]
]

# Loop through each row in your data and append it to the worksheet
for row in google_sheets_data:
    ws.append(row)

Step 3: Save the Final Excel File #

Don’t forget to save your workbook once all data is written:

wb.save("google_sheets_to_excel.xlsx")

Bonus Tips for Edge Cases #

• If your data is a 1D list (just a single row), you can still use ws.append(your_1d_list) to add it as a single row in the sheet.
• If you need to start writing data from a specific cell (not the first row/column), use cell coordinates with nested loops:

  # Start writing at row 3, column 2
  for row_num, row_data in enumerate(google_sheets_data, start=3):
      for col_num, value in enumerate(row_data, start=2):
          ws.cell(row=row_num, column=col_num, value=value)
  

• Double-check that your array doesn’t include unsupported data types—openpyxl handles strings, numbers, booleans, and dates well, but complex objects might need conversion first.

That should get your Google Sheets data into an Excel file without a hitch!

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