class SharePoint():
# Generating Auth Access
auth = AuthenticationContext(sharepoint_base_url)
auth.acquire_token_for_user(sharepoint_user, sharepoint_password)
ctx = ClientContext(sharepoint_base_url, auth)
web = ctx.web
ctx.load(web)
ctx.execute_query()
print('Connected to SharePoint: ', web.properties['Title'])
# Collecting files names from the folder
def folder_details(ctx, folder_in_sharepoint):
folder = ctx.web.get_folder_by_server_relative_url(
folder_in_sharepoint)
folder_names = []
sub_folders = folder.files
ctx.load(sub_folders)
ctx.execute_query()
for each_folder in sub_folders:
folder_names.append(each_folder.properties["Name"])
return folder_names
# Passing auth ctx and folder path
file_list = folder_details(ctx, folder_in_sharepoint)
# Reading File from SharePoint Folder and saving it in local.
print("Downloading 3P & Subledger file from sharepoint")
for each_file in file_list:
sharepoint_file = folder_in_sharepoint + each_file
file_response = File.open_binary(ctx, sharepoint_file)
print(file_response)
with open(each_file, 'wb') as output_file:
output_file.write(file_response.content)
# Downloading the UTC Key file from the sharepoint
print("Downloading UTC Key.xlsx file from sharepoint")
sharepoint_file = utc_key_file_in_sharepoint + "UTC Key.xlsx"
file_response = File.open_binary(ctx, sharepoint_file)
print(file_response)
with open("UTC Key.xlsx", 'wb') as output_file:
output_file.write(file_response.content)
# Processing the files and generating the output file.
print("Processing the files and generating the output file")
import automation
# Uploading the output file in the output folder of sharepoint.
with open(output_xlsx, 'rb') as content_file:
print(output_xlsx)
file_content = content_file.read()
target_folder = ctx.web.get_folder_by_server_relative_url(
'/CP/Shared%20Documents/Automation/Subledger%20Automation/Output/')
print(
"Uploading the output file in the output folder of the sharepoint."
)
target_folder.upload_file(output_xlsx, file_content)
ctx.execute_query()
def file(self):
"""Get file"""
if self.is_property_available("File"):
return self.properties["File"]
else:
from office365.sharepoint.files.file import File
return File(self.context, ResourcePath("File", self.resource_path))
def load_file(filename):
# Open the file in temporary memory
response = File.open_binary(ctx, str(folder_url + filename))
bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
# set file object to start
bytes_file_obj.seek(0)
global df
print(f'\n...Loading {filename}...')
if filename.endswith('.xlsx'):
dict = pd.read_excel(bytes_file_obj, sheet_name='Sheet1'
) # read the excel file in python as a dictionary
df = pd.DataFrame.from_dict(
dict) # convert dictionary to a dataframe using pandas
return df
elif filename.endswith('.csv'):
df = pd.read_csv(
bytes_file_obj) # read the .csv file in python as a dataframe
return df
else:
file = input("\n*** File not recognised, please try again: ")
Load_file(file)
def test8_update_file_content(self):
"""Test file upload operation"""
files = self.__class__.target_list.rootFolder.files.get().execute_query()
for file_upload in files:
response = File.save_binary(self.client, file_upload.properties["ServerRelativeUrl"],
self.content_placeholder)
self.assertTrue(response.ok)
def get_file_by_server_relative_url(self, url):
"""Returns the file object located at the specified server-relative URL.
:type url: str
"""
return File(
self.context,
ResourcePathServiceOperation("getFileByServerRelativeUrl", [url], self.resource_path)
)
def get_folder_by_guest_url(self, guestUrl):
"""
:type guestUrl: str
"""
return_type = File(self.context)
qry = ServiceOperationQuery(self, "GetFolderByGuestUrl", [guestUrl], None, None, return_type)
self.context.add_query(qry)
return return_type
def get_file_by_wopi_frame_url(self, wopi_frame_url):
"""
:param str wopi_frame_url:
"""
return_type = File(self.context)
qry = ServiceOperationQuery(self, "GetFileByWOPIFrameUrl",
[wopi_frame_url], None, None, return_type)
self.context.add_query(qry)
return return_type
def get_file_by_id(self, unique_id):
"""Returns the file object with the specified GUID.
:param str unique_id: A GUID that identifies the file object.
"""
return_file = File(self.context)
qry = ServiceOperationQuery(self.context.web, "GetFileById", [unique_id], None, None, return_file)
self.context.add_query(qry)
return return_file
def download_file(self, filename):
"""
:param filename: name of the file to download
:return:
download file in current folder
"""
response = File.open_binary(self.context, f'/{self.folder}/{filename}')
with open(f'./data/{filename}', 'wb') as local_file:
local_file.write(response.content)
def __init__(self, files, file_creation_information):
"""
:type file_creation_information: office365.sharepoint.files.file_creation_information.FileCreationInformation
:type files: FileCollection
"""
super().__init__(files, "add", file_creation_information.to_json(),
file_creation_information.content, None,
File(files.context))
files.add_child(self._return_type)
def create_wiki_page_in_context_web(context, parameters):
"""
:type context: office365.sharepoint.client_context.ClientContext
:type parameters: office365.sharepoint.pages.wiki_page_creation_information.WikiPageCreationInformation
"""
return_file = File(context)
utility = Utility(context)
qry = ServiceOperationQuery(utility, "CreateWikiPageInContextWeb", None, parameters, "parameters", return_file)
qry.static = True
context.add_query(qry)
return return_file
def get_file_by_server_relative_path(self, decoded_url):
"""Returns the file object located at the specified server-relative path.
Prefer this method over get_folder_by_server_relative_url since it supports % and # symbols in names
:type decoded_url: str
"""
return File(
self.context,
ServiceOperationPath("getFileByServerRelativePath",
{"DecodedUrl": decoded_url},
self.resource_path))
def load_by_relativeUrl(self, relativeUrl: str, **kwargs):
ext = os.path.splitext(relativeUrl)[-1]
if ext == ".zip":
temp = tempfile.NamedTemporaryFile(suffix=ext)
_file = (self.ctx.web.get_file_by_server_relative_url(
relativeUrl).download(temp).execute_query())
df = pd.read_csv(temp.name, compression="zip", **kwargs)
temp.close()
else:
response = File.open_binary(self.ctx, relativeUrl)
df = pd.read_csv(StringIO(response.text), **kwargs)
return df
def __init__(self, files, file_creation_information):
"""
:type file_creation_information: office365.sharepoint.file_creation_information.FileCreationInformation
:type files: FileCollection
"""
self._return_type = File(files.context)
super().__init__(files, "add", {
"overwrite": file_creation_information.overwrite,
"url": file_creation_information.url
}, file_creation_information.content, None, self._return_type)
files.add_child(self._return_type)
def create_file_query(files, file_creation_information):
"""
:type file_creation_information: office365.sharepoint.files.file_creation_information.FileCreationInformation
:type files: office365.sharepoint.files.file_collection.FileCollection
"""
return_file = File(files.context)
qry = ServiceOperationQuery(files, "add",
file_creation_information.to_json(),
file_creation_information.content, None,
return_file)
files.add_child(return_file)
return qry
def create_file_query(files, file_create_info):
"""
Constructs a query to create/upload a file
:type file_create_info: office365.sharepoint.files.creation_information.FileCreationInformation
:type files: office365.sharepoint.files.collection.FileCollection
"""
return_file = File(files.context)
qry = ServiceOperationQuery(files, "add", file_create_info.to_json(),
file_create_info.content, None, return_file)
files.add_child(return_file)
return qry
def download(tenant_url, folder, file):
file_path = "%s%s" % (folder, file)
ctx_auth = AuthenticationContext(tenant_url)
ctx_auth.acquire_token_for_user(username, password)
# ctx_auth.with_client_certificate(tenant, client_id, thumbprint, cert_path)
# ctx_auth.acquire_token_for_app(client_id, client_secret)
ctx = ClientContext(tenant_url, ctx_auth)
target = "%s%s%s" % (os.path.dirname(
os.path.abspath(__file__)), "/import/", file)
response = File.open_binary(ctx, file_path)
with open(target, "wb") as local_file:
local_file.write(response.content)
def download(self, request, *args, **kwargs):
sh_file = self.get_object()
relative_url = sh_file.properties['ServerRelativeUrl']
response = File.open_binary(self.client.context, relative_url)
django_response = HttpResponse(
content=response.content,
status=response.status_code,
content_type=response.headers['Content-Type'],
)
django_response[
'Content-Disposition'] = 'attachment; filename=%s' % sh_file.properties[
'Name']
return django_response
def add_template_file(self, url_of_file, template_file_type):
"""Adds a ghosted file to an existing list or document library.
:param int template_file_type: refer TemplateFileType enum
:param str url_of_file: server relative url of a file
"""
target_file = File(self.context)
self.add_child(target_file)
qry = ServiceOperationQuery(self, "addTemplateFile", {
"urlOfFile": url_of_file,
"templateFileType": template_file_type
}, None, None, target_file)
self.context.add_query(qry)
return target_file
def create_wiki_page(self, page_name, page_content):
"""
:param str page_name:
:param str page_content:
"""
result = ClientResult(self.context, File(self.context))
def _list_loaded():
page_url = self.root_folder.serverRelativeUrl + "/" + page_name
wiki_props = WikiPageCreationInformation(page_url, page_content)
result.value = Utility.create_wiki_page_in_context_web(
self.context, wiki_props)
self.ensure_property("RootFolder", _list_loaded)
return result
def add_image(self, page_name, image_file_name, image_stream):
"""
Adds an image to the site assets library of the current web.
Returns a File object ([MS-CSOMSPT] section 3.2.5.64) that represents the image.
:param str image_stream: The image stream.
:param str image_file_name: Indicates the file name of the image to be added.
:param str page_name: Indicates the name of that site page that the image is to be used in.
:return: File
"""
return_type = File(self.context)
params = {"pageName": page_name, "imageFileName": image_file_name, "imageStream": image_stream}
qry = ServiceOperationQuery(self, "AddImage", params, None, None, return_type)
qry.static = True
self.context.add_query(qry)
return return_type
def create_wiki_page(self, page_name, page_content):
"""
Creates a wiki page.
:param str page_name:
:param str page_content:
"""
return_type = File(self.context)
def _root_folder_loaded():
page_url = self.root_folder.serverRelativeUrl + "/" + page_name
wiki_props = WikiPageCreationInformation(page_url, page_content)
Utility.create_wiki_page_in_context_web(self.context, wiki_props,
return_type)
self.ensure_property("RootFolder", _root_folder_loaded)
return return_type
def add(self, attachment_file_information):
"""
Creates an attachment
:type attachment_file_information: AttachmentfileCreationInformation
"""
if isinstance(attachment_file_information, dict):
attachment_file_information = AttachmentfileCreationInformation(
attachment_file_information.get('filename'),
attachment_file_information.get('content'))
target_file = File(self.context)
self.add_child(target_file)
qry = ServiceOperationQuery(
self, "add", {
"filename": attachment_file_information.filename,
}, attachment_file_information.content, None, target_file)
self.context.add_query(qry)
return target_file
def readFile(
ctx: ClientContext
) -> pd.DataFrame:
'''
Reads sharepoint file
'''
response = File.open_binary(ctx, env.sp_mppt)
#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
try:
#read file into pandas dataframe
df = pd.read_excel(bytes_file_obj)
return(True, df)
except ValueError:
return(False, '')
def openShiftPlan(creds):
ctx_auth = AuthenticationContext(siteURL)
if ctx_auth.acquire_token_for_user(creds[0], creds[1]):
ctx = ClientContext(siteURL, 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())
response = File.open_binary(ctx, relativeURL)
#print(ctx.service_root_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
return pd.read_excel(bytes_file_obj, sheet_name='Daily - Infra')
def get_data_from_excel(self, relative_url, worksheet_name, header_row,
start_row):
# adapted from: https://stackoverflow.com/a/69292234/14125255
# (accessed 2021-10-15)
ctx = ClientContext(self.conn.site_url).with_credentials(
UserCredential(self.conn.user, self.conn.password))
response = File.open_binary(ctx, relative_url)
bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0)
ws = load_workbook(bytes_file_obj, data_only=True)[worksheet_name]
headers = {
ws.cell(row=header_row, column=col).value: col
for col in range(1, ws.max_column + 1)
if ws.cell(row=header_row, column=col)
}
data = [{
k: ws.cell(row=row, column=v).value
for k, v in headers.items()
} for row in range(start_row, ws.max_row + 1)]
return data
def load_remote_to_dataframe(self) -> pd.DataFrame:
if (not self._abs_file_url) | (not self._username) | (
not self._password) | (not self._url) | (not self._document):
data = f'- username: {self._username}\n' \
f'- password: {self._password}\n' \
f'- url: {self._url}\n' \
f'- document: {self._document}\n' \
f'- full_url: {self._abs_file_url}\n'
raise Exception(f'Dati richiesti mancanti\n{data}')
user_credentials = UserCredential(self._username, self._password)
with tempfile.TemporaryDirectory() as local_path:
file_name = os.path.basename(self._abs_file_url)
with open(os.path.join(local_path, file_name), 'wb') as local_file:
file = File.from_url(self._abs_file_url).with_credentials(
user_credentials).download(local_file).execute_query()
# print("'{0}' file has been downloaded into {1}".format(file.serverRelativeUrl, local_file.name))
df = pd.read_excel(local_file.name,
engine='openpyxl',
converters={'ProvinciaResidenza': str})
df['DataDiNascita'] = pd.to_datetime(
df.DataNascita).dt.strftime('%Y-%m-%d')
return df
def get_by_id(self, _id):
"""Gets the File with the specified ID."""
return File(self.context,
ServiceOperationPath("getById", [_id], self.resource_path))
def file(self):
"""Get file"""
from office365.sharepoint.files.file import File
return self.properties.get(
"File", File(self.context, ResourcePath("File",
self.resource_path)))
def get_by_url(self, url):
"""Retrieve File object by url"""
return File(
self.context,
ServiceOperationPath("GetByUrl", [url], self.resource_path))