How to get rows and cells from an Excel XLSX spreadsheet in Python

Having all the right features to work with spreadsheets is key in this day and age. Working with XLSX files in Python, however, can be a real drag. So why set up a system yourself when you can access one that is already working via an API? Let’s look at this approach.

We will need our client installed using pip install, first.

pip install cloudmersive-convert-api-client

Our API now needs to be instantiated, which will in turn allow us to call edit_document_xlsx_get_rows_and_cells.

from __future__ import print_functionimport timeimport cloudmersive_convert_api_clientfrom cloudmersive_convert_api_client.rest import ApiExceptionfrom pprint import pprint# Configure API key authorization: Apikeyconfiguration = cloudmersive_convert_api_client.Configuration()configuration.api_key['Apikey'] = 'YOUR_API_KEY'# Uncomment below to setup prefix (e.g. Bearer) for API key, if needed# configuration.api_key_prefix['Apikey'] = 'Bearer'# create an instance of the API classapi_instance = cloudmersive_convert_api_client.EditDocumentApi(cloudmersive_convert_api_client.ApiClient(configuration))input = cloudmersive_convert_api_client.GetXlsxRowsAndCellsRequest() # GetXlsxRowsAndCellsRequest | Document input requesttry:# Get rows and cells from a Excel XLSX spreadsheet, worksheetapi_response = api_instance.edit_document_xlsx_get_rows_and_cells(input)pprint(api_response)except ApiException as e:print("Exception when calling EditDocumentApi->edit_document_xlsx_get_rows_and_cells: %s\n" % e)

This will take a GetXlsxRowsAndCellsRequest object, which takes these parameters:

{
"InputFileBytes": "string",
"InputFileUrl": "string",
"WorksheetToQuery": {
"Path": "string",
"WorksheetName": "string"
}
}

And it will then return your data in this sort of format:

{
"Successful": true,
"Rows": [
{
"Path": "string",
"Cells": [
{
"Path": "string",
"TextValue": "string",
"CellIdentifier": "string",
"StyleIndex": 0,
"Formula": "string"
}
]
}
]
}
Image for post
Image for post

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store