How to save a pandas dataframe as excel table to SharePoint using Python

Question

I am trying to save a pandas dataframe as an excel table to a sharepoint site. I have two separate blocks of code which achieve the below.(thanks for Stackoverflow community)

A script which can save a pandas df as excel table using ExcelWriter on local storage.
A Script which can save a local file to Sharepoint online.

I am very confused on how to combine these two to save a df to sharepoint online but the excel file should be a table not just a range of data. Kindly help

SCRIPT 1 to save excel range as table on local

##############################################################################
#
# An example of adding a dataframe to an worksheet table in an xlsx file
# using Pandas and XlsxWriter.
#
# Tables in Excel are used to group rows and columns of data into a single
# structure that can be referenced in a formula or formatted collectively.
#
# SPDX-License-Identifier: BSD-2-Clause
# Copyright 2013-2021, John McNamara, jmcnamara@cpan.org
#

import pandas as pd

# Create a Pandas dataframe from some data.
df = pd.DataFrame({
    'Country':    ['China',    'India',    'United States', 'Indonesia'],
    'Population': [1404338840, 1366938189, 330267887,       269603400],
    'Rank':       [1,          2,          3,               4]})

# Order the columns if necessary.
df = df[['Rank', 'Country', 'Population']]

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_table.xlsx', engine='xlsxwriter')

# Write the dataframe data to XlsxWriter. Turn off the default header and
# index and skip one row to allow us to insert a user defined header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Get the dimensions of the dataframe.
(max_row, max_col) = df.shape

# Create a list of column headers, to use in add_table().
column_settings = [{'header': column} for column in df.columns]

# Add the Excel table structure. Pandas will add the data.
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})

# Make the columns wider for clarity.
worksheet.set_column(0, max_col - 1, 12)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

SCRIPT 2 to save any file to sharepoint

from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
import os 

baseurl = 'https://testsite.sharepoint.com/'
basesite = '/sites/project' # every share point has a home.
siteurl = baseurl + basesite 

localpath = "pandas_table.xlsx"
remotepath = "Shared Documents/General/file.xlsx" # existing folder path under sharepoint site.

ctx_auth = AuthenticationContext(siteurl)
ctx_auth.acquire_token_for_user(, )
ctx = ClientContext(siteurl, ctx_auth) # make sure you auth to the siteurl.

with open(localpath, 'rb') as content_file:
    file_content = content_file.read()

dir, name = os.path.split(remotepath)
file = ctx.web.get_folder_by_server_relative_url(dir).upload_file(name, file_content).execute_query()

I am not really sure how I can use the writer.save() with the sharepoint connector. Kindly advise thanks in advance.

user_803002 · Answer

Given that you have your data in a df the following code will write to sharepoint using the O365 library.

from io import BytesIO
from tempfile import gettempdir

from O365 import Account, FileSystemTokenBackend
import pandas as pd

O365_CLIENT_ID = "client"
O365_SECRET = "secret"
O365_TENANT_ID = ""
O365_SHAREPOINT = ".sharepoint.com"
O365_SITE = "/sites/..."


def save_file(folder_path, filename, data):
    """save file to O365."""
    account = Account(
        (O365_CLIENT_ID, O365_SECRET),
        auth_flow_type="credentials",
        tenant_id=O365_TENANT_ID,
        token_backend=FileSystemTokenBackend(
            token_path=gettempdir(), token_filename="o365_token.txt"
        ),
    )
    if account.authenticate():
        drive = (
            account.sharepoint()
            .get_site(O365_SHAREPOINT, O365_SITE)
            .get_default_document_library()
        )
        subfolders = folder_path.split("/")
        if len(subfolders) != 0:
           items = drive.get_items()
           for subfolder in subfolders:
           try:
               subfolder_drive = list(filter(lambda x, sf=subfolder: sf in x.name, items))[0]
               items = subfolder_drive.get_items()
           except Exception as excep:  # pylint: disable=broad-except
                raise f"Path {folder_path} does not exist." from excep
        else:
            subfolder_drive = drive.get_root_folder()
        subfolder_drive.upload_file(
            item=None,
            item_name=filename,
            stream=data,
            stream_size=data.getbuffer().nbytes,
        )

with BytesIO() as buf:
    df.to_excel(buf, index=False)
    buf.seek(0)
    save_file('folder/to/upload/to', 'filename.xlsx', buf)

Writing to multiple sheets:

with BytesIO() as buf:
    with pd.ExcelWriter(  # pylint: disable=abstract-class-instantiated
        buf,
        engine="xlsxwriter",
    ) as writer:
        df.to_excel(
            writer,
            sheet_name='sheet1',
            index=False,
        )
        df.to_excel(
            writer,
            sheet_name='sheet2',
            index=False,
        )
    buf.seek(0)
    save_file('folder/to/upload/to', 'filename.xlsx', buf)

How to save a pandas dataframe as excel table to SharePoint using Python

1 Answers