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