如何将Google Sheets数据写入Microsoft Excel？

Question

Primer:我对Python还不熟悉。在

我正在处理一些googlesheets数据，并用它创建.xlsx表格。通过下面的代码，我可以让Python读取数据并将其放入数组中。然而，我似乎不知道如何让openpyxl成功地将其写入文档。我假设这与尝试编写数组有关，而不是迭代出现的行。在

如果您能提供任何帮助/建议，我们将不胜感激。在

尝试运行时出现以下错误：

Traceback (most recent call last):
  File "quickstart.py", line 94, in <module>
    main()
  File "quickstart.py", line 90, in main
    ws.append([values])
  File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\worksheet\worksheet.py", line 763, in append
    cell = Cell(self, row=row_idx, col_idx=col_idx, value=content)
  File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\cell\cell.py", line 115, in __init__
    self.value = value
  File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\cell\cell.py", line 299, in value
    self._bind_value(value)
  File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\cell\cell.py", line 206, in _bind_value
    raise ValueError("Cannot convert {0!r} to Excel".format(value))
ValueError: Cannot convert [['Student Name', 'Gender', 'Class Level', 'Home State', 'Major', 'Extracurricular Activity'], ['Alexandra', 'Female', '4. Senior', 'CA', 'English', 'Drama Club'], ['Andrew', 'Male', '1. Freshman', 'SD', 'Math', 'Lacrosse'], ['Anna', 'Female', '1. Freshman', 'NC', 'English', 'Basketball'], ['Becky', 'Female', '2. Sophomore', 'SD', 'Art', 'Baseball'], ['Benjamin', 'Male', '4. Senior', 'WI', 'English', 'Basketball'], ['Carl', 'Male', '3. Junior', 'MD', 'Art', 'Debate'], ['Carrie', 'Female', '3. Junior', 'NE', 'English', 'Track & Field'], ['Dorothy', 'Female', '4. Senior', 'MD', 'Math', 'Lacrosse'], ['Dylan', 'Male', '1. Freshman', 'MA', 'Math', 'Baseball'], ['Edward', 'Male', '3. Junior', 'FL', 'English', 'Drama Club'], ['Ellen', 'Female', '1. Freshman', 'WI', 'Physics', 'Drama Club'], ['Fiona', 'Female', '1. Freshman', 'MA', 'Art', 'Debate'], ['John', 'Male', '3. Junior', 'CA', 'Physics', 'Basketball'], ['Jonathan', 'Male', '2. Sophomore', 'SC', 'Math', 'Debate'], ['Joseph', 'Male', '1. Freshman', 'AK', 'English', 'Drama Club'], ['Josephine', 'Female', '1. Freshman', 'NY', 'Math', 'Debate'], ['Karen', 'Female', '2. Sophomore', 'NH', 'English', 'Basketball'], ['Kevin', 'Male', '2. Sophomore', 'NE', 'Physics', 'Drama Club'], ['Lisa', 'Female', '3. Junior', 'SC', 'Art', 'Lacrosse'], ['Mary', 'Female', '2. Sophomore', 'AK',
'Physics', 'Track & Field'], ['Maureen', 'Female', '1. Freshman', 'CA', 'Physics', 'Basketball'], ['Nick', 'Male', '4. Senior', 'NY', 'Art', 'Baseball'], ['Olivia', 'Female', '4. Senior', 'NC', 'Physics', 'Track & Field'], ['Pamela', 'Female', '3. Junior', 'RI', 'Math', 'Baseball'], ['Patrick', 'Male', '1. Freshman', 'NY', 'Art', 'Lacrosse'], ['Robert', 'Male', '1. Freshman', 'CA', 'English', 'Track & Field'], ['Sean', 'Male', '1. Freshman', 'NH', 'Physics', 'Track & Field'], ['Stacy', 'Female', '1. Freshman', 'NY', 'Math', 'Baseball'], ['Thomas', 'Male', '2. Sophomore', 'RI', 'Art', 'Lacrosse'], ['Will', 'Male', '4. Senior', 'FL', 'Math', 'Debate']] to Excel

以下是我目前所做的代码：

^{pr2}$

更新：尝试ws.append（值），我得到以下错误：

Traceback (most recent call last):
  File "quickstart.py", line 94, in <module>
    main()
  File "quickstart.py", line 90, in main
    ws.append(values)
  File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\worksheet\worksheet.py", line 763, in append
    cell = Cell(self, row=row_idx, col_idx=col_idx, value=content)
  File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\cell\cell.py", line 115, in __init__
    self.value = value
  File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\cell\cell.py", line 299, in value
    self._bind_value(value)
  File "C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\cell\cell.py", line 206, in _bind_value
    raise ValueError("Cannot convert {0!r} to Excel".format(value))
ValueError: Cannot convert ['Student Name', 'Gender', 'Class Level', 'Home State', 'Major', 'Extracurricular Activity'] to Excel

使用Pandas代码更新：

将索引标志保留为true（未编辑）时，数据的布局如下：

"""
Using pandas and dataframes. For writing large amounts of data, this is probably the best way. 
"""
df=DataFrame(data=values)
df.to_excel('Pandas.xlsx')

将索引标志标记为false时，数据的外观如下：

"""
Using pandas and dataframes. For writing large amounts of data, this is probably the best way. 
"""
df=DataFrame(data=values)
df.to_excel('Pandas.xlsx', index=False)

更新2：解决方案！ 以下来自Zyd和Ben.T的代码完全解决了我的问题。在

"""
Using pandas and dataframes. For writing large amounts of data, this is probably the best way. 
"""
df=DataFrame(data=values)
df.to_excel('Pandas.xlsx', header=False, index=False)

谢谢大家的帮助！在

Answer 1

似乎名为values的变量是一个包含google电子表格每一行的列表的列表。 我试过了：

import openpyxl
# to recreate a list of list
list_input = [['one','two'],['second row','with',14]]

wb = openpyxl.Workbook()
ws = wb.active
ws.append(list_input)
wb.save("test.xlsx")

我得到的错误和你的更新一样。在

将ws.append(list_input)替换为

^{pr2}$

我的代码起作用了，所以我将您的行ws.append([values])替换为

for row in values:
    ws.append(row)

希望对你有所帮助。在

Answer 2

我的回答可能不太正确。在

你在做什么：

if not values:
    print('No data found.')
else:
    wb = Workbook()
    ws = wb.active  
    # Add new row at bottom
    ws.append([values])
    wb.save("users.xlsx")  # Write to disk

我会努力的

^{pr2}$