Microsoft 365 - Excel¶
Process and perform operations on your online Excel files through a Microsoft 365 account. Write, read, and update your spreadsheets easily via the BotCity plugin for Excel.
To be able to use this plugin, you need to have a Microsoft 365 account with a properly created and configured project.
After creating a project, you need to complete the authentication process through the Microsoft 365 Credentials plugin.
For more details, see the previous sections:
pip install botcity-ms365-excel-plugin
Importing the Plugin¶
After you installed this package, the next step is to import the package into your code and start using the functions.
from botcity.plugins.ms365.excel import MS365ExcelPlugin
Setting the service account¶
As mentioned above, in order to use the Excel plugin, it is necessary to perform the account authentication process through the Microsoft 365 Credentials plugin.
With the credentials plugin instance obtained, let's use it to instantiate the Excel plugin.
from botcity.plugins.ms365.credentials import MS365CredentialsPlugin, Scopes from botcity.plugins.ms365.excel import MS365ExcelPlugin # Instantiate the Credentials plugin service = MS365CredentialsPlugin( client_id='<APPLICATION ID>', client_secret='<SECRET VALUE>', ) service.authenticate(scopes=[Scopes.BASIC, Scopes.FILES_READ_WRITE_ALL]) # Instantiate the Excel plugin excel = MS365ExcelPlugin(service_account=service)
To use the Excel service, you can only add the scope
Scopes.FILES_READ_WRITE_ALL referring to the
Files.ReadWrite.All permission of the project.
See more details about scopes and permissions at this link.
Getting the Excel file¶
The first step we must do to use Excel services is to configure the file that will be used.
This Excel file must be stored on a OneDrive that you have access to with your account. With that in mind, we can get the file reference in two ways.
Using file path in OneDrive¶
The simplest way to search for the Excel file that will be used is to pass the file's absolute path on OneDrive.
In this case, the default OneDrive of the account will be used as a reference.
The file path must follow the pattern: '/path/to/file'.
/ means we are starting from the root folder, and the rest of the path will be the "absolute path" of the item on the drive.
excel = MS365ExcelPlugin(service_account=service) # Searching the Excel file using the file path in OneDrive excel.get_excel_file(file_or_path="/documents/My-File.xlsx")
Using the OneDrive plugin to get the file¶
If you are already using the OneDrive plugin in your code or for some other reason, prefer to do it this way, you also have the option to get the file using the OneDrive plugin.
Using one of the methods that return a
File object, you can use this returned object as a parameter of the
from botcity.plugins.ms365.credentials import MS365CredentialsPlugin, Scopes from botcity.plugins.ms365.onedrive import MS365OneDrivePlugin from botcity.plugins.ms365.excel import MS365ExcelPlugin # Instantiate the Credentials plugin service = MS365CredentialsPlugin( client_id='<APPLICATION ID>', client_secret='<SECRET VALUE>', ) service.authenticate(scopes=[Scopes.BASIC, Scopes.FILES_READ_WRITE_ALL]) # Instantiate the OneDrive Plugin onedrive = MS365OneDrivePlugin(service_account=service) # Instantiate the Excel plugin excel = MS365ExcelPlugin(service_account=service) # Getting the Excel file using the OneDrive plugin file = onedrive.get_file_by_name("My-File.xlsx") # Setting the file to be used through the 'File' object excel.get_excel_file(file_or_path=file)
See more details about ways to search for files in the drive by accessing the OneDrive plugin documentation.
We can start performing operations on the worksheets with the Excel file properly configured.
get_excel_file method, we can pass the worksheet name that will be used as a reference in the operations.
If no specific worksheet is passed, the first worksheet in the file will be used by default.
excel = MS365ExcelPlugin(service_account=service) # Searching the Excel file and defining the active worksheet excel.get_excel_file(file_or_path="/Test-Plugin.xlsx", active_sheet="Sheet2")
Fetching existing worksheets¶
In addition to defining which worksheet will be used when searching for the Excel file, we can list all existing worksheets or search for a specific worksheet.
We can also define a new active worksheet using the
Worksheet object returned or through the worksheet's name.
# Returning the list of worksheets sheets = excel.list_sheets() # Returning a specific Worksheet results_sheet = excel.get_worksheet(sheet_name="Results") # Defining the active sheet through the 'active_sheet' property excel.active_sheet = results_sheet # Another way to configure the active worksheet excel.set_active_sheet("New Sheet")
Creating and removing worksheets¶
You can also create new sheets in the file and delete existing sheets.
# Removing a worksheet from the Excel file excel.remove_sheet(sheet_name="Sheet2") # Creating a new worksheet excel.create_sheet(sheet_name="New Sheet")
To be able to use a sheet that was created, you must use the
set_active_sheet method right after creation.
Reading data from worksheet¶
We can easily return the contents of a worksheet in different ways.
We can search for a specific cell, row, or column. In addition, we can return all existing content or even content only from a specific range.
The Microsoft Graph API uses some patterns for certain types of data.
If you use cells with the Date format in your spreadsheets, remember that the API always returns these values in the pattern mm/dd/yyyy.
Therefore, even using dates in the format dd/mm/yyyy, do the necessary treatments when reading the data using the plugin.
If possible, we recommend using dates in the spreadsheet in Text format so that the API returns the content without applying specific patterns.
# Returning the value of a specific cell cell = excel.get_cell(column="B", row=2) print(cell) # Returning the values of a specific column col = excel.get_column(column="A") print(col) # Returning the values of a specific row row = excel.get_row(row=10) print(row)
When searching for the entire content of the worksheet, a list of lists will be returned, where each list will correspond to a row of the worksheet.
# Returning all existing content in the worksheet in the format: List[List[object]] values = excel.as_list() # Each list corresponds to a row in the worksheet for row in values: print(row) # Returning the content of a given range of the worksheet range_values = excel.get_range(range_="A1:E5") # Each list corresponds to a row in the worksheet for row in range_values: print(row)
Writing new data¶
To write new data in the worksheet, we can use the same strategy we use for reading.
You can enter data in a single cell or use a specific worksheet range.
The Microsoft Graph API uses some patterns for certain types of data.
If you use float numbers, we recommend using
. instead of
, when entering the data in the worksheet.
This way, we prevent the API from applying unwanted formatting to the values used.
# Entering data in a specific cell excel.set_cell(column='L', row=7, value="Finished") # Entering data in a specific range # Each list represents a line that will be inserted values = [ ['Product', 'Stock', 'Price'], ['Notebook', '500', '1250.95'], ['Smartphone', '250', '700.99'], ['Mouse', '90', '65.25'] ] # The range used must be compatible with the size of the data list excel.set_range(range_="A1:C4", values=values)
In addition, we can also insert new rows or columns. By default, these new values will be inserted at the end of the worksheet, but you can also use a specific range.
# Inserting a new row at the end of the worksheet row = ["Keyboard", "380", "95.80"] excel.add_row(row_values=row) # Inserting a new column at the end of the worksheet col = ["Seller", "John", "Maria"] excel.add_column(column_values=col)
In addition to reading and writing data in the worksheet, we could remove existing data.
It is possible to clear all the worksheet content and remove values from a range or a specific row or column.
When using the methods to remove columns, remember that each column removed will cause the columns to its right to be moved to the left.
When using the methods to remove rows, remember that each row removed will cause the rows below it to move up.
# Clearing all worksheet content excel.clear() # Clearing the values of a specific range excel.clear(range_="B2:D10") # Removing values from a single column excel.remove_column(column="C") # Removing values from specific columns excel.remove_columns(columns=["F", "H", "J"]) # Removing values from a single row excel.remove_row(row=15) # Removing values from specific rows excel.remove_rows(rows=[2, 4, 6])
For all the above operations, you can pass the worksheet that will be used through the
In this case, when passing a worksheet as a parameter, it will be used as a reference in the context of the method instead of the current active worksheet.