Edit Excel Sheets using a REST API in Python.

EditExcelSheets_Python.md

Learn how to edit Excel sheets using a REST API in Python: https://blog.groupdocs.cloud/2021/12/23/edit-excel-sheet-using-rest-api-in-python/

The following topics are covered in this article:

1. Excel Spreadsheets Editor REST API and Python SDK
2. Edit Excel Sheets using a REST API in Python
3. Add Table in Excel Sheet using Python

EditExcelSheets_Python_AddTable.py

# This code example demonstrates how to edit Excel sheet and insert a new table.
# API initialization
editApi = groupdocs_editor_cloud.EditApi.from_keys(client_id, client_secret)
fileApi = groupdocs_editor_cloud.FileApi.from_keys(client_id, client_secret)

# Load the uploaded document into editable state
fileInfo = groupdocs_editor_cloud.FileInfo("sample.xlsx")

# Define spreadsheet load options
loadOptions = groupdocs_editor_cloud.SpreadsheetLoadOptions()
loadOptions.file_info = fileInfo

# Provide output folder path 
loadOptions.output_path = "output"
# Provide worksheet index to edit
loadOptions.worksheet_index = 0
# Load the sheet
loadResult = editApi.load(groupdocs_editor_cloud.LoadRequest(loadOptions))        

# Download html document
htmlFile = fileApi.download_file(groupdocs_editor_cloud.DownloadFileRequest(loadResult.html_path))
html = ""       

with open(htmlFile, 'r') as file:
    html = file.read()

# Insert table  
html = html.replace("</TABLE>", """</TABLE> <br/><table style="width: 100%;background-color: #dddddd;border: 1px solid black;">
<caption style=\"font-weight:bold;\"> Persons List</caption>
<tr><th style="background-color: #04AA6D; color: white;">First Name</th><th style="background-color: #04AA6D; color: white;">Last Name</th><th style="background-color: #04AA6D; color: white;">Age</th></tr>
<tr><td>Jill</td><td>Smith</td><td>50</td></tr>
<tr><td>Eve</td><td>Jackson</td><td>94</td></tr>
</table>""")

# Upload html back to storage
with open(htmlFile, 'w') as file:
    file.write(html)

fileApi.upload_file(groupdocs_editor_cloud.UploadFileRequest(loadResult.html_path, htmlFile))

# Save html back to xlsx
saveOptions = groupdocs_editor_cloud.SpreadsheetSaveOptions()
saveOptions.file_info = fileInfo
saveOptions.output_path = "edited.xlsx"
saveOptions.html_path = loadResult.html_path
saveOptions.resources_path = loadResult.resources_path
saveResult = editApi.save(groupdocs_editor_cloud.SaveRequest(saveOptions))

# Done
print("Document edited: " + saveResult.path)

EditExcelSheets_Python_Configurations.py

# This code example demonstrates how to add your Client ID and Secret in the code.
client_id = "659fe7da-715b-4744-a0f7-cf469a392b73"
client_secret = "b377c36cfa28fa69960ebac6b6e36421"
my_storage = ""

configuration = groupdocs_editor_cloud.Configuration(client_id, client_secret)
configuration.api_base_url = "https://api.groupdocs.cloud"

EditExcelSheets_Python_Download.py

# This code example demonstrates how to download the updated Excel file.
# API initialization
file_api = groupdocs_editor_cloud.FileApi.from_config(configuration)

# Download file
request = groupdocs_editor_cloud.DownloadFileRequest("edited.xlsx", my_storage)
response = file_api.download_file(request)

# Move downloaded file to your working directory
shutil.move(response, "C:\\Files\\Editor\\")

EditExcelSheets_Python_Edit.py

# This code example demonstrates how to edit the content of an Excel sheet.
# API initialization
editApi = groupdocs_editor_cloud.EditApi.from_keys(client_id, client_secret)
fileApi = groupdocs_editor_cloud.FileApi.from_keys(client_id, client_secret)

# Load the uploaded document into editable state
fileInfo = groupdocs_editor_cloud.FileInfo("sample.xlsx")

# Define spreadsheet load options
loadOptions = groupdocs_editor_cloud.SpreadsheetLoadOptions()
loadOptions.file_info = fileInfo

# Provide output folder path 
loadOptions.output_path = "output"
# Provide worksheet index to edit
loadOptions.worksheet_index = 0
# Load the sheet
loadResult = editApi.load(groupdocs_editor_cloud.LoadRequest(loadOptions))        

# Download html document
htmlFile = fileApi.download_file(groupdocs_editor_cloud.DownloadFileRequest(loadResult.html_path))
html = ""       

with open(htmlFile, 'r') as file:
    html = file.read()

# Edit something...    
html = html.replace("Welcome", "This is a sample sheet!")

# Upload html back to storage
with open(htmlFile, 'w') as file:
    file.write(html)

fileApi.upload_file(groupdocs_editor_cloud.UploadFileRequest(loadResult.html_path, htmlFile))

# Save html back to xlsx
saveOptions = groupdocs_editor_cloud.SpreadsheetSaveOptions()
saveOptions.file_info = fileInfo
saveOptions.output_path = "edited.xlsx"
saveOptions.html_path = loadResult.html_path
saveOptions.resources_path = loadResult.resources_path
saveResult = editApi.save(groupdocs_editor_cloud.SaveRequest(saveOptions))

# Done
print("Document edited: " + saveResult.path)

EditExcelSheets_Python_Upload.py

# This code example demonstrates how to upload an Excel file to the cloud.
# Create instance of the API
file_api = groupdocs_editor_cloud.FileApi.from_config(configuration)

# upload sample file
request = groupdocs_editor_cloud.UploadFileRequest("sample.xlsx", "C:\\Files\\\Editor\\sample.xlsx", my_storage)
response = file_api.upload_file(request)