Python Excel到JSON的转换

There are many ways to convert an excel file to JSON data. In this tutorial, we will look into two python modules to convert excel files to JSON.

有很多方法可以将excel文件转换为JSON数据。 在本教程中，我们将研究两个将excel文件转换为JSON的python模块。

1. excel2json-3
   excel2json-3
2. Pandas
   大熊猫

使用excel2json-3模块将Excel文件转换为JSON文件 (Converting Excel File to JSON Files using excel2json-3 Module)

It’s a very simple module to convert excel files to JSON files. The contents from the excel sheet are converted to JSON string and saved in a file.

这是一个非常简单的模块，可以将excel文件转换为JSON文件。 excel工作表中的内容将转换为JSON字符串并保存在文件中。

The name of the files is derived from the excel sheet names. So, if there are two sheets with the name “Numbers” and “Cars”, the JSON files will be named as Numbers.json and Cars.json respectively.

文件的名称是从excel工作表名称派生的。 因此，如果有两个名为“ Numbers”和“ Cars”的工作表，则JSON文件将分别命名为Numbers.json和Cars.json。

The module supports both .xls and .xlsx file formats for conversion. We can read the excel file from the file system as well as the URL.

该模块支持.xls和.xlsx文件格式进行转换。 我们可以从文件系统以及URL中读取excel文件。

We can install this module using the PIP command.

我们可以使用PIP命令安装此模块。

$ pip install excel2json-3

For our example, I have created an excel file named “records.xlsx” having three sheets.

对于我们的示例，我创建了一个名为“ records.xlsx”的Excel文件，该文件包含三张纸。

Here is the script to convert this excel file to JSON files.

这是将这个excel文件转换为JSON文件的脚本。

import excel2json

excel2json.convert_from_file('records.xlsx')

The script creates three JSON files.

该脚本将创建三个JSON文件。

Employees.json

员工.json

[
    {
        "EmpID": 1.0,
        "EmpName": "Pankaj",
        "EmpRole": "CEO"
    },
    {
        "EmpID": 2.0,
        "EmpName": "David Lee",
        "EmpRole": "Editor"
    },
    {
        "EmpID": 3.0,
        "EmpName": "Lisa Ray",
        "EmpRole": "Author"
    }
]

Cars.json

Cars.json

[
    {
        "Car Name": "Honda City",
        "Car Model": "City",
        "Car Maker": "Honda",
        "Car Price": "20,000 USD"
    },
    {
        "Car Name": "Bugatti Chiron",
        "Car Model": "Chiron",
        "Car Maker": "Bugatti",
        "Car Price": "3 Million USD"
    },
    {
        "Car Name": "Ferrari 458",
        "Car Model": 458.0,
        "Car Maker": "Ferrari",
        "Car Price": "2,30,000 USD"
    }
]

Numbers.json

Numbers.json

[
    {
        "1.0": 3.0,
        "2.0": 4.0
    },
    {
        "1.0": "N1",
        "2.0": "N2"
    },
    {
        "1.0": 5.0,
        "2.0": 6.0
    },
    {
        "1.0": 7.0,
        "2.0": 8.0
    }
]

If you have to read the excel file from a URL, use convert_from_url() function.

如果必须从URL读取excel文件，请使用convert_from_url()函数。

excel2json-3模块的局限性 (Limitations of excel2json-3 module)

• The plugin has very limited features.
  该插件的功能非常有限。
• There are no options to skip any sheet, rows, and columns. This makes it hard to use with bigger excel files.
  没有选项可以跳过任何工作表，行和列。 这使得很难使用更大的excel文件。
• The JSON is saved into files. Most of the times, we want to convert to JSON and use it in our program rather than saving it as a file.
  JSON保存到文件中。 大多数时候，我们想转换为JSON并在程序中使用它，而不是将其保存为文件。
• The integers are getting converted to the floating point numbers.
  整数将转换为浮点数。

使用Pandas模块将Excel工作表转换为JSON字符串 (Converting Excel Sheet to JSON String using Pandas Module)

Pandas module provides functions to read excel sheets into DataFrame object. There are many options to specify headers, read specific columns, skip rows, etc. You can read more about it at Pandas read_excel() – Reading Excel File in Python.

熊猫模块提供了将Excel工作表读入DataFrame对象的功能。 有很多选项可用于指定标题，读取特定列，跳过行等。您可以在Pandas read_excel（）–使用Python读取Excel文件中阅读有关它的更多信息。

We can use the to_json() function to convert the DataFrame object to JSON string. Let’s look at a simple example to read the “Employees” sheet and convert it to JSON string.

我们可以使用to_json（）函数将DataFrame对象转换为JSON字符串。 让我们看一个简单的示例，阅读“ Employees”表并将其转换为JSON字符串。

import pandas

excel_data_df = pandas.read_excel('records.xlsx', sheet_name='Employees')

json_str = excel_data_df.to_json()

print('Excel Sheet to JSON:\n', json_str)

Output:

输出：

Excel Sheet to JSON:
 {"EmpID":{"0":1,"1":2,"2":3},"EmpName":{"0":"Pankaj","1":"David Lee","2":"Lisa Ray"},"EmpRole":{"0":"CEO","1":"Editor","2":"Author"}}

So, the JSON data is created with the orientation of columns. If you want to create the JSON string with row-wise orientation, pass the “orient” parameter value as “records”.

因此，JSON数据是按列的方向创建的。 如果要创建按行方向的JSON字符串，请将“ orient”参数值作为“ records”传递。

json_str = excel_data_df.to_json(orient='records')

Output:

输出：

Excel Sheet to JSON:
 [{"EmpID":1,"EmpName":"Pankaj","EmpRole":"CEO"},{"EmpID":2,"EmpName":"David Lee","EmpRole":"Editor"},{"EmpID":3,"EmpName":"Lisa Ray","EmpRole":"Author"}]

结论 (Conclusion)

If you have a simple and well-structured excel file and you want to convert it to JSON files, use the excel2json-3 module. But, if you want more control on the way the excel data is read and converted to JSON string, use the pandas’ module.

如果您有一个简单且结构良好的excel文件，并且想要将其转换为JSON文件，请使用excel2json-3模块。 但是，如果您想进一步控制excel数据读取并转换为JSON字符串的方式，请使用pandas模块。

参考资料 (References)

• excel2json-3 PyPI.org Page
  excel2json-3 PyPI.org页面
• Pandas DataFrame to_json() API Doc
  Pandas DataFrame to_json（）API文件

翻译自: https://www.journaldev.com/33335/python-excel-to-json-conversion