用Python将excel文件导出成json

最新推荐文章于 2024-08-02 19:15:00 发布

BabyFish13

最新推荐文章于 2024-08-02 19:15:00 发布

阅读量1.9k

点赞数

分类专栏： Python Solution 文章标签： Python excel json export

本文链接：https://blog.csdn.net/BabyFish13/article/details/75040089

版权

Python 同时被 2 个专栏收录

109 篇文章 3 订阅

订阅专栏

Solution

85 篇文章 1 订阅

订阅专栏

1、相关说明
此脚本可以 将excel各个sheet中的数据分别导出到不同的json文件中，以原excel文件名+sheet名进行命名。
数据传入参数有：excelPath, jsonPath, fileName三个。
依赖的库有：xlrd、json、codecs，尤其 xlrd需要事先安装好。

2、Python脚本及测试示例
/Users/nisj/PycharmProjects/BiDataProc/oldPythonBak/excel2json.py

# -*- coding=utf-8 -*-
import xlrd
import warnings
import sys
from collections import OrderedDict
import json
import codecs
reload(sys)
sys.setdefaultencoding('utf8')

warnings.filterwarnings("ignore")

def excel2json(excelPath, jsonPath, fileName):
    wb = xlrd.open_workbook('{excelPath}{fileName}.xls'.format(excelPath=excelPath, fileName=fileName))

    convert_list = []
    for sheetNo in range(0, len(wb.sheets())):
        sheetName = wb.sheet_by_index(sheetNo).name
        sh = wb.sheet_by_index(sheetNo)
        title = sh.row_values(0)

        for rownum in range(1, sh.nrows):
            rowvalue = sh.row_values(rownum)
            single = OrderedDict()
            for colnum in range(0, len(rowvalue)):
                single[title[colnum]] = rowvalue[colnum]
            convert_list.append(single)

        j = json.dumps(convert_list)

        with codecs.open('{jsonPath}{fileName}-{sheetName}.json'.format(jsonPath=jsonPath, fileName=fileName, sheetName=sheetName), "w", "utf-8") as f:
            f.write(j)

# Batch Test
excelPath = '/Users/nisj/Desktop/'
jsonPath = '/Users/nisj/Desktop/'
fileName = 'mysqlDataDownload'
excel2json(excelPath, jsonPath, fileName)