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:
Edit Excel File using a REST API in Python
We can edit Excel files by following the simple steps given below:
Upload the Document
Firstly, we will upload the XLSX file to the cloud using the code example given below:
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.
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:
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.
Please try the following free online XLSX editing tool, which is developed using the above API. https://products.groupdocs.app/editor/xlsx
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.