Convert Excel Data to JSON Using Python (xlrd)

Many testers need to export data from a database to Excel, but API testing often requires JSON input; this guide shows how to convert Excel data into JSON using Python.

The script relies on the xlrd and json modules, which should be installed beforehand.

After preparing the Excel file (e.g., 员工信息.xlsx ), the code opens the workbook, reads the first sheet, extracts column titles from the first row, iterates over subsequent rows to build dictionaries, and aggregates them into a list.

Finally, the list is serialized to a JSON string with ensure_ascii=False to preserve non‑ASCII characters, and the result is printed.

#-*- encoding:utf-8 -*-
import json
import xlrd

def readExcel():
    # 打开excel表单
    filename = u'/Users/xxx/员工信息.xlsx'
    excel = xlrd.open_workbook(filename)

    # 得到第一张表单
    sheet1 = excel.sheets()[0]
    #找到有几列几列
    nrows = sheet1.nrows #行数
    ncols = sheet1.ncols #列数

    totalArray=[]
    title=[]
    # 标题
    for i in range(0,ncols):
        title.append(sheet1.cell(0,i).value)

    #数据
    for rowindex in range(1,nrows):
        dic={}
        for colindex in range(0,ncols):
            s=sheet1.cell(rowindex,colindex).value
            dic[title[colindex]]=s
        totalArray.append(dic)

    return json.dumps(totalArray,ensure_ascii=False)

print(readExcel())

The article ends with a QR code invitation to follow for more automation learning resources.