excel地区编码表格转为JSON

xy12

需要把excel里的省市区转为下面这种格式

[JavaScript] 纯文本查看 复制代码

{
	"list":[
		{
			"value": 110,
			"label": "北京市",
			"children": [{
					"value": 1000,
					"label": "北京市",
					"children": [{
							value: 1000,
							label: "东城区"
						},
						{
							value: 1001,
							label: "西城区"
						}
					]
				},
			]
		}
	]
}

地区码表.xlshttps://www.alipan.com/s/992kCnbHHH8点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。

Mindy

在命令行安装：pip install xlrd openpyxl
然后运行python脚本：

[Python] 纯文本查看 复制代码

import os
import pandas as pd
import json
import tkinter as tk
from tkinter import filedialog, messagebox

def convert_to_json(df):
    result = {"list": []}
    province_dict = {}

    for index, row in df.iterrows():
        province_code = row["省市代码"]
        province = row["省市"]
        city_code = row["市代码"]
        city = row["市"]
        district_code = row["区县市代码"]
        district = row["区县"]
        
        if province_code not in province_dict:
            province_dict[province_code] = {
                "value": province_code,
                "label": province,
                "children": []
            }
        
        city_dict = None
        for c in province_dict[province_code]["children"]:
            if c["value"] == city_code:
                city_dict = c
                break
        
        if city_dict is None:
            city_dict = {
                "value": city_code,
                "label": city,
                "children": []
            }
            province_dict[province_code]["children"].append(city_dict)
        
        city_dict["children"].append({
            "value": district_code,
            "label": district
        })

    result["list"] = list(province_dict.values())
    return result

def load_file():
    file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx;*.xls")])
    if file_path:
        # 使用 openpyxl 引擎读取 xlsx 文件，使用 xlrd 引擎读取 xls 文件
        if file_path.endswith(".xlsx"):
            df = pd.read_excel(file_path, engine='openpyxl')
        else:
            df = pd.read_excel(file_path, engine='xlrd')
        
        json_data = convert_to_json(df)
        
        result_file_path = os.path.abspath('result.json')
        with open(result_file_path, "w", encoding="utf-8") as f:
            json.dump(json_data, f, ensure_ascii=False, indent=4)
        
        messagebox.showinfo("完成", f"JSON文件已生成，路径为：\n{result_file_path}")
        print(f"JSON文件已生成，路径为：{result_file_path}")

# 创建Tkinter主窗口
root = tk.Tk()
root.withdraw()  # 隐藏主窗口

# 显示文件选择对话框
load_file()

xy12

Mindy 发表于 2024-7-4 12:16
在命令行安装：pip install xlrd openpyxl
然后运行python脚本：
[mw_shl_code=python,true]import os

直接cmd里面粘贴这个代码嘛，没用过python

Mindy

xy12 发表于 2024-7-4 12:21
直接cmd里面粘贴这个代码嘛，没用过python

装个python运行就行，文件名自定义：python 文件名.py

Mindy

xy12 发表于 2024-7-4 12:21
直接cmd里面粘贴这个代码嘛，没用过python

链接: https://pan.baidu.com/s/11GBqM_xFBM8NoekicMsx5g?pwd=52pj 提取码: 52pj 复制这段内容后打开百度网盘手机App，操作更方便哦
--来自百度网盘超级会员v7的分享

我给你编译好了，有点大，因为我装的第三方库文件有点多。