如何使用工作或学校帐户在 Python 中阅读 SharePoint Online (Office365) Excel 文件? [英] How to read SharePoint Online (Office365) Excel files in Python with Work or School Account?
问题描述
我是一名大学生，我已通过我的大学电子邮件地址注册为 Office 365 教育版用户.我通常使用我的电子邮件帐户登录 https://www.office.com:alice@abc.教育.我的个人资料路径如下:https://abcedu-my.sharepoint.com/personal/alice_abc_edu
I am a university student and I have registered as an Office 365 Education user via my university Email address. I usually log into https://www.office.com with my Email account: alice@abc.edu. The path to my profile is like: https://abcedu-my.sharepoint.com/personal/alice_abc_edu
我的 Office 365 中有一个 Excel (.xlsx) 文件.我想使用 Python 以编程方式访问(或下载)Excel 文件.我用谷歌搜索了一些解决方案.但其中大多数都需要 NTLM 凭据.但我只有我的电子邮件帐户和密码.我不知道我的 NTLM 凭据.是alice@abc.edu 还是alice_abc_edu?或者Email用户名和NTLM是完全不同的认证方式.我不能使用 NTLM?
I have an Excel (.xlsx) file in my Office 365.
And I want to use Python to programmatically access (or download) the Excel file. I have googled about some solutions. But most of them require a NTLM credential. But I only have my Email account and password. I don't know my NTLM credential. Is it alice@abc.edu or alice_abc_edu? Or the Email username and NTLM are totally different authentication ways. And I can't use NTLM?
我用于登录的电子邮件地址的正式名称似乎是工作或学校帐户或Azure Active Directory Credential.但是我不知道如何使用这样的帐户来实现我的要求?此外，我需要在 Python 中进行.RESTful 也可以.但我只是陷入了第一个身份验证步骤.谢谢！
It seems that my Email address that is used to log in is officially called Work or School Account or Azure Active Directory Credential. But I don't know how to use such an account to realize my requirement? Moreover, I need to do it in Python. RESTful would also be OK. But I just got stuck in the first authentication step. Thanks!
我遵循了 Microsoft Graph 教程这里，它告诉我注册一个 Python 应用程序.然后我得到了一个 App ID 和 App Secret.但是当我使用官方的python-sample-send-mail
I have followed the Microsoft Graph tutorial here and it told me to register a Python app. Then I got a App ID and App Secret. But when I use the official python-sample-send-mail
"""send-email sample for Microsoft Graph"""
# Copyright (c) Microsoft. All rights reserved. Licensed under the MIT license.
# See LICENSE in the project root for license information.
import base64
import mimetypes
import os
import pprint
import uuid
import flask
from flask_oauthlib.client import OAuth
import config
APP = flask.Flask(__name__, template_folder='static/templates')
APP.debug = True
APP.secret_key = 'development'
OAUTH = OAuth(APP)
MSGRAPH = OAUTH.remote_app(
'microsoft',
consumer_key=config.CLIENT_ID,
consumer_secret=config.CLIENT_SECRET,
request_token_params={'scope': config.SCOPES},
base_url=config.RESOURCE + config.API_VERSION + '/',
request_token_url=None,
access_token_method='POST',
access_token_url=config.AUTHORITY_URL + config.TOKEN_ENDPOINT,
authorize_url=config.AUTHORITY_URL + config.AUTH_ENDPOINT)
@APP.route('/')
def homepage():
"""Render the home page."""
return flask.render_template('homepage.html')
@APP.route('/login')
def login():
"""Prompt user to authenticate."""
flask.session['state'] = str(uuid.uuid4())
return MSGRAPH.authorize(callback=config.REDIRECT_URI, state=flask.session['state'])
@APP.route('/login/authorized')
def authorized():
"""Handler for the application's Redirect Uri."""
if str(flask.session['state']) != str(flask.request.args['state']):
raise Exception('state returned to redirect URL does not match!')
response = MSGRAPH.authorized_response()
flask.session['access_token'] = response['access_token']
return flask.redirect('/mailform')
@APP.route('/mailform')
def mailform():
"""Sample form for sending email via Microsoft Graph."""
# read user profile data
user_profile = MSGRAPH.get('me', headers=request_headers()).data
user_name = user_profile['displayName']
# get profile photo
photo_data, _, profile_pic = profile_photo(client=MSGRAPH, save_as='me')
# save photo data as config.photo for use in mailform.html/mailsent.html
if profile_pic:
config.photo = base64.b64encode(photo_data).decode()
else:
profile_pic = 'static/images/no-profile-photo.png'
with open(profile_pic, 'rb') as fhandle:
config.photo = base64.b64encode(fhandle.read()).decode()
# upload profile photo to OneDrive
upload_response = upload_file(client=MSGRAPH, filename=profile_pic)
if str(upload_response.status).startswith('2'):
# create a sharing link for the uploaded photo
link_url = sharing_link(client=MSGRAPH, item_id=upload_response.data['id'])
else:
link_url = ''
body = flask.render_template('email.html', name=user_name, link_url=link_url)
return flask.render_template('mailform.html',
name=user_name,
email=user_profile['userPrincipalName'],
profile_pic=profile_pic,
photo_data=config.photo,
link_url=link_url,
body=body)
@APP.route('/send_mail')
def send_mail():
"""Handler for send_mail route."""
profile_pic = flask.request.args['profile_pic']
response = sendmail(client=MSGRAPH,
subject=flask.request.args['subject'],
recipients=flask.request.args['email'].split(';'),
body=flask.request.args['body'],
attachments=[flask.request.args['profile_pic']])
# show results in the mailsent form
response_json = pprint.pformat(response.data)
response_json = None if response_json == "b''" else response_json
return flask.render_template('mailsent.html',
sender=flask.request.args['sender'],
email=flask.request.args['email'],
profile_pic=profile_pic,
photo_data=config.photo,
subject=flask.request.args['subject'],
body_length=len(flask.request.args['body']),
response_status=response.status,
response_json=response_json)
@MSGRAPH.tokengetter
def get_token():
"""Called by flask_oauthlib.client to retrieve current access token."""
return (flask.session.get('access_token'), '')
def request_headers(headers=None):
"""Return dictionary of default HTTP headers for Graph API calls.
Optional argument is other headers to merge/override defaults."""
default_headers = {'SdkVersion': 'sample-python-flask',
'x-client-SKU': 'sample-python-flask',
'client-request-id': str(uuid.uuid4()),
'return-client-request-id': 'true'}
if headers:
default_headers.update(headers)
return default_headers
def profile_photo(*, client=None, user_id='me', save_as=None):
"""Get profile photo.
client = user-authenticated flask-oauthlib client instance
user_id = Graph id value for the user, or 'me' (default) for current user
save_as = optional filename to save the photo locally. Should not include an
extension - the extension is determined by photo's content type.
Returns a tuple of the photo (raw data), content type, saved filename.
"""
endpoint = 'me/photo/$value' if user_id == 'me' else f'users/{user_id}/$value'
photo_response = client.get(endpoint)
if str(photo_response.status).startswith('2'):
# HTTP status code is 2XX, so photo was returned successfully
photo = photo_response.raw_data
metadata_response = client.get(endpoint[:-7]) # remove /$value to get metadata
content_type = metadata_response.data.get('@odata.mediaContentType', '')
else:
photo = ''
content_type = ''
if photo and save_as:
extension = content_type.split('/')[1]
if extension == 'pjpeg':
extension = 'jpeg' # to correct known issue with content type
filename = save_as + '.' + extension
with open(filename, 'wb') as fhandle:
fhandle.write(photo)
else:
filename = ''
return (photo, content_type, filename)
def sendmail(*, client, subject=None, recipients=None, body='',
content_type='HTML', attachments=None):
"""Helper to send email from current user.
client = user-authenticated flask-oauthlib client instance
subject = email subject (required)
recipients = list of recipient email addresses (required)
body = body of the message
content_type = content type (default is 'HTML')
attachments = list of file attachments (local filenames)
Returns the response from the POST to the sendmail API.
"""
# Verify that required arguments have been passed.
if not all([client, subject, recipients]):
raise ValueError('sendmail(): required arguments missing')
# Create recipient list in required format.
recipient_list = [{'EmailAddress': {'Address': address}}
for address in recipients]
# Create list of attachments in required format.
attached_files = []
if attachments:
for filename in attachments:
b64_content = base64.b64encode(open(filename, 'rb').read())
mime_type = mimetypes.guess_type(filename)[0]
mime_type = mime_type if mime_type else ''
attached_files.append(
{'@odata.type': '#microsoft.graph.fileAttachment',
'ContentBytes': b64_content.decode('utf-8'),
'ContentType': mime_type,
'Name': filename})
# Create email message in required format.
email_msg = {'Message': {'Subject': subject,
'Body': {'ContentType': content_type, 'Content': body},
'ToRecipients': recipient_list,
'Attachments': attached_files},
'SaveToSentItems': 'true'}
# Do a POST to Graph's sendMail API and return the response.
return client.post('me/microsoft.graph.sendMail',
headers=request_headers(),
data=email_msg,
format='json')
def sharing_link(*, client, item_id, link_type='view'):
"""Get a sharing link for an item in OneDrive.
client = user-authenticated flask-oauthlib client instance
item_id = the id of the DriveItem (the target of the link)
link_type = 'view' (default), 'edit', or 'embed' (OneDrive Personal only)
Returns the sharing link.
"""
endpoint = f'me/drive/items/{item_id}/createLink'
response = client.post(endpoint,
headers=request_headers(),
data={'type': link_type},
format='json')
if str(response.status).startswith('2'):
# status 201 = link created, status 200 = existing link returned
return response.data['link']['webUrl']
def upload_file(*, client, filename, folder=None):
"""Upload a file to OneDrive for Business.
client = user-authenticated flask-oauthlib client instance
filename = local filename; may include a path
folder = destination subfolder/path in OneDrive for Business
None (default) = root folder
File is uploaded and the response object is returned.
If file already exists, it is overwritten.
If folder does not exist, it is created.
API documentation:
https://developer.microsoft.com/en-us/graph/docs/api-reference/v1.0/api/driveitem_put_content
"""
fname_only = os.path.basename(filename)
# create the Graph endpoint to be used
if folder:
# create endpoint for upload to a subfolder
endpoint = f'me/drive/root:/{folder}/{fname_only}:/content'
else:
# create endpoint for upload to drive root folder
endpoint = f'me/drive/root/children/{fname_only}/content'
content_type, _ = mimetypes.guess_type(fname_only)
with open(filename, 'rb') as fhandle:
file_content = fhandle.read()
return client.put(endpoint,
headers=request_headers({'content-type': content_type}),
data=file_content,
content_type=content_type)
if __name__ == '__main__':
APP.run()
它给了我一个错误:
AADSTS65005:您的组织 abc.edu 目前不支持使用应用程序My Python App"，因为它处于非托管状态.管理员需要通过 abc.edu 的 DNS 验证声明公司的所有权，然后才能提供应用程序 My Python App.请求 ID:9a4874e0-7f8f-4eff-b6f9-9834765d8780，时间戳:01/25/2018 13:51:10跟踪 ID:8d1cc38e-3b5e-4bf1-a003-bda164e00b00关联 ID:2033267e-98ec-4eb1-91e9-c0530ef97fb1时间戳:2018-01-25 13:51:10Z&state=d94af98c-92d9-4016-b3da-afd8e8974f4b HTTP/1.1
因此，我所在大学的 IT 管理员似乎没有启用将应用与 Microsoft Graph 连接的功能.但这是唯一的方法吗?我已经拥有有效的电子邮件帐户和密码.我认为必须有一种方法可以让我直接使用我的凭据以编程方式登录 Office 365?
So it seems that the IT admin of my university doesn't enable the functionality of connecting an App with Microsoft Graph. But is this way the only way? I already have the valid Email account and password. I think there must be a way for me to log in Office 365 programmatically directly with my credential?
推荐答案
按照 Niels V 的建议尝试使用Office365-REST-Python-Client.
As suggested by Niels V try using the Office365-REST-Python-Client.
客户端实现了 Sharepoint REST API.以下是您尝试执行的操作的示例:
The client implements the Sharepoint REST API. Here's an example of what you are trying to do:
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File
url = 'https://yoursharepointsite.com/sites/documentsite'
username = 'yourusername'
password = 'yourpassword'
relative_url = '/sites/documentsite/Documents/filename.xlsx'
本节直接来自 github README.md 使用 ClientContext 方法并让您在 SharePoint 服务器上进行身份验证
This section is straight from the github README.md using the ClientContext approach and gets you authenticated on your SharePoint server
ctx_auth = AuthenticationContext(url)
if ctx_auth.acquire_token_for_user(username, password):
ctx = ClientContext(url, ctx_auth)
web = ctx.web
ctx.load(web)
ctx.execute_query()
print "Web title: {0}".format(web.properties['Title'])
else:
print ctx_auth.get_last_error()
如果您只想下载文件，请使用 File.open_binary() 你只需要:
If you just want to download the file then using File.open_binary() all you need is:
filename = 'filename.xlsx'
with open(filename, 'wb') as output_file:
response = File.open_binary(ctx, relative_url)
output_file.write(response.content)
但是，如果您想分析文件的内容，您可以将文件下载到内存中，然后直接使用 Pandas 或您选择的 python '.xlsx' 工具:
However if you want to analyze the contents of the file you can download the file to memory then directly use Pandas or your python '.xlsx' tool of choice:
import io
import pandas as pd
response = File.open_binary(ctx, relative_url)
#save data to BytesIO stream
bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0) #set file object to start
#read file into pandas dataframe
df = pd.read_excel(bytes_file_obj)
你可以从这里拿走.我希望这会有所帮助！
You can take it from here. I hope this helps!
这篇关于如何使用工作或学校帐户在 Python 中阅读 SharePoint Online (Office365) Excel 文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
