Excel to JSON Conversion using Python and Pandas

An Excel file can be converted into JSON using any of the following methods:

  • Method 1: Using pandas and json packages, or
  • Method 2: Using openpyxl and json packages.

In most cases, Method 1 is sufficient, but you may need to use the second method if you want to process each row of the Excel before converting it into JSON.

In the code examples, we will use an Excel file named employees.xlsx with two worksheets – names and roles.

The roles sheet contains the data shown in the following Figure. We will use it in the last example.

Method 1: Using pandas and json

As a pre-requisite, ensure that pandas and openpyxl (needed for pandas to work) are installed. If not, install them using pip by executing these commands:

pip install pandas

pip install openpyxl

After that, this method works in the following steps:

  • Read the Excel file using pd.excel() function,
  • Convert the resulting DataFrame into JSON string,
  • (Optional) Save the JSON data in a file.

Here is the code example.


If you want to be able to prettify the resulting JSON file using the indent attribute on json.dump() function, you can use the following approach.

Output (truncated):

The output shows that the second approach yields a more readable JSON file than the first.

Method 2: Using openpyxl and json

This method is suitable if you want to process the Excel file row by row before converting the result into JSON.

The method gets the work done in three steps:

  • Load the Excel file using openpyxl.load_workbook() function,
  • Iterate through the Excel rows, process the data, and store them in a list,
  • Convert the resulting list of data into JSON, then save the JSON data in a file.

The following code contains a Python function you can reuse to convert an Excel file into JSON.


[{"Id": 1.0, "Name": "Allan", "Year Employed": 2007.0}, …, {"Id": 5.0, "Name": "Alice", "Year Employed": 2014.0}]
[{"id": 1.0, "Role": "CEO", "Department": "Management"}, …, {"id": 5.0, "Role": "Software developer", "Department": "Technology"}]


This guide discussed two methods for converting Excel into JSON in Python. The most common approach of using pandas is discussed as method 1, and the second method outlines how to use openpyxl to perform the conversion. The second method is the best choice if you want to process each Excel file row before writing results into JSON.