Edit Excel Sheet using REST API in Python

Excel is one of the most popular and widely used spreadsheet applications. It enables us to organize, analyze and store data in tabular form. We can easily add, edit or delete the content of Excel files using Python. In this article, we will learn how to edit an Excel sheet using a REST API in Python.

The following topics shall be covered in this article:

Excel Spreadsheets Editor REST API and Python SDK
Edit Excel Files using a REST API in Python
Add Table in Excel Sheet using Python

Excel Spreadsheets Editor REST API and Python SDK

For modifying the XLSX files, we will be using the Python SDK of GroupDocs.Editor Cloud API. It allows editing documents of the supported formats. Please install it using the following command in the console:

pip install groupdocs_editor_cloud

Please get your Client ID and Secret from the dashboard before following the mentioned steps. Once you have your ID and secret, add in the code as shown below:

	# 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"

view raw EditExcelSheets_Python_Configurations.py hosted with ❤ by GitHub

Edit Excel File using a REST API in Python

We can edit Excel files by following the simple steps given below:

Upload the XLSX file to the Cloud
Edit Excel Spreadsheet Data
Download the updated file

Upload the Document

Firstly, we will upload the XLSX file to the cloud using the code example given below:

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

view raw EditExcelSheets_Python_Upload.py hosted with ❤ by GitHub

As a result, the uploaded XLSX file will be available in the files section of the dashboard on the cloud.

Edit Excel Spreadsheet Data in Python

We can edit the content of an Excel sheet by following the steps given below:

Firstly, create instances of the FileApi and the EditApi.
Next, provide the uploaded XLSX file path.
Then, download the file as an HTML document.
Next, read the downloaded HTML file as a string.
Then, edit the HTML and save the updated HTML document.
After that, upload the updated HTML file.
Finally, save HTML back to XLSX using the EditApi.save() method.

The following code sample shows how to edit Excel sheet data using a REST API in Python.

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

view raw EditExcelSheets_Python_Edit.py hosted with ❤ by GitHub

Edit Excel File using a REST API in Python.

Download the Updated File

The above code sample will save the edited Excel file (XLSX) on the cloud. It can be downloaded using the following code sample:

	# 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\\")

view raw EditExcelSheets_Python_Download.py hosted with ❤ by GitHub

Add Table in Excel Sheet using Python

We can add a table in the Excel sheet by following the steps mentioned earlier. However, we need to update the HTML to add a table in the document as shown below:

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

The following code sample shows how to add a table in an Excel spreadsheet using a REST API in Python. Please follow the steps mentioned earlier to upload and download a file.

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

view raw EditExcelSheets_Python_AddTable.py hosted with ❤ by GitHub

Try Online

Please try the following free online XLSX editing tool, which is developed using the above API. https://products.groupdocs.app/editor/xlsx

Conclusion

In this article, we have learned:

how to edit Excel sheets data on the cloud;
how to add a table in the Excel sheet using Python;
upload Excel file to the cloud;
how to download updated Excel file from the cloud.

Besides, you can learn more about GroupDocs.Editor Cloud API using the documentation. We also provide an API Reference section that lets you visualize and interact with our APIs directly through the browser. In case of any ambiguity, please feel free to contact us on the forum.

Excel Spreadsheets Editor REST API and Python SDK#

Edit Excel File using a REST API in Python#

Upload the Document#

Edit Excel Spreadsheet Data in Python#

Download the Updated File#

Add Table in Excel Sheet using Python#

Try Online#

Conclusion#

See Also#