gpt4 book ai didi

python - 如何将 Google 表格数据写入 Microsoft Excel?

转载 作者:行者123 更新时间:2023-11-28 22:20:14 24 4
gpt4 key购买 nike

入门:我非常是 Python 的新手。

我正在处理一些 Google 表格数据,并用它创建 .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

这是我到目前为止所做的代码:

from __future__ import print_function
import httplib2
import oauth2client
import os
import googleapiclient
import openpyxl

from apiclient import discovery
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage
from googleapiclient.discovery import build
from openpyxl import Workbook


""" This is the code to get raw data from a specific Google Sheet"""
try:
import argparse
flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
except ImportError:
flags = None

# If modifying these scopes, delete your previously saved credentials
# at ~/.credentials/sheets.googleapis.com-python-quickstart.json
SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
CLIENT_SECRET_FILE = 'client_secret_noemail.json'
APPLICATION_NAME = 'Google Sheets API Python'


def get_credentials():
"""Gets valid user credentials from storage.

If nothing has been stored, or if the stored credentials are invalid,
the OAuth2 flow is completed to obtain the new credentials.

Returns:
Credentials, the obtained credential.
"""
home_dir = os.path.expanduser('~')
credential_dir = os.path.join(home_dir, '.credentials')
if not os.path.exists(credential_dir):
os.makedirs(credential_dir)
credential_path = os.path.join(credential_dir,
'sheets.googleapis.com-python-quickstart.json')

store = Storage(credential_path)
credentials = store.get()
if not credentials or credentials.invalid:
flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
flow.user_agent = APPLICATION_NAME
if flags:
credentials = tools.run_flow(flow, store, flags)
else: # Needed only for compatibility with Python 2.6
credentials = tools.run_flow(flow, store)
print('Storing credentials to ' + credential_path)
return credentials


def main():
"""Shows basic usage of the Sheets API.

Creates a Sheets API service object and prints the names and majors of
students in a sample spreadsheet:
https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
"""
credentials = get_credentials()
http = credentials.authorize(httplib2.Http())
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
'version=v4')
service = build('sheets', 'v4', http=http,
discoveryServiceUrl=discoveryUrl)

spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
rangeName = 'Class Data!A:F'
result = service.spreadsheets().values().get(
spreadsheetId=spreadsheetId, range=rangeName).execute()
values = result.get('values', [])

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

if __name__ == '__main__':
main()

更新:尝试执行 ws.append(values) 时,出现以下错误:

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 代码更新:

当将索引标志保留为真(未编辑)时,这是数据的布局:

"""
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 Flag True

当将索引标志标记为 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)

Index Flag 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)

No Indexes

谢谢大家的帮助!

最佳答案

这可能不正确,但我的回答有点太多了,无法发表评论。

你在做什么:

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

我会努力

import pandas
if not values:
print('No data found.')
else:
dataframe = pandas.DataFrame([values])
dataframe.to_excel('excel_out.xlsx')

关于python - 如何将 Google 表格数据写入 Microsoft Excel?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49117565/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com