Python：使用openpyxl读取Excel文件转为json数据

文档

• https://openpyxl.readthedocs.io/en/stable/
• https://pypi.org/project/openpyxl/

安装

pip install openpyxl

环境

$ python --version
Python 3.7.0

读取文件示例：将Excel文件读取为json数据

有如下一个文件 data.xlsx

实现代码

# -*- coding: utf-8 -*-import jsonfrom openpyxl.reader.excel import load_workbookdef read_excel(filename):book = load_workbook(filename)worksheet = book.worksheets[0]row_num = 0titles = []lst = []for row in worksheet.rows:row_num += 1if row_num == 1:# 表头for cell in row:# 移除空格value = cell.value.replace(' ', '')titles.append(value)else:# 内容item = {}for key, cell in zip(titles, row):item[key] = cell.valuelst.append(item)return lstif __name__ == '__main__':data = read_excel('./data.xlsx')print(json.dumps(data, ensure_ascii=False, indent=2))

输出读取的json数据

[{"姓名": "曹操","国家": "魏国"},{"姓名": "刘备","国家": "蜀国"},{"姓名": "孙权","国家": "吴国"}
]