Python使用pandas读取Excel并选取列转json_Python

1、背景介绍

大家都知道在利用python对表格进行匹配的时候，我们进行会读取一张excel表格，然后选中其中的

某一列(或者多列)作为唯一项（key）,

然后在选中

某一列(或者多列)作为值（value）

2、库的安装

库	用途	安装
pandas	读取excel文件	pip install pandas -i https://pypi.tuna.tsinghua.edu.cn/simple/
pyqt5	界面设计	pip install pyqt5 -i https://pypi.tuna.tsinghua.edu.cn/simple/
os	获取绝对路径	内置库无需安装

3、核心代码

①：选择列

df = pd.read_excel(self.excel_file_path, dtype=str, keep_default_na=false)
            headers = df.columns.tolist()

for i in reversed(range(self.key_checkboxes_layout.count())):
    self.key_checkboxes_layout.itemat(i).widget().setparent(none)

for i in reversed(range(self.value_checkboxes_layout.count())):
    self.value_checkboxes_layout.itemat(i).widget().setparent(none)

②：制作json

df['combined_key'] = df[key_cols].apply(lambda row: "=".join(row.values), axis=1)
df['combined_value'] = df[value_cols].apply(lambda row: "=".join(row.values), axis=1)
data = dict(zip(df['combined_key'], df['combined_value']))

with open(self.output_file_path, "w", encoding="utf-8") as f:
    json.dump(data, f, ensure_ascii=false, indent=4)

4、完整代码

import os
import json
import pandas as pd
from pyqt5.qtwidgets import (
    qapplication, qwidget, qvboxlayout, qlabel, qpushbutton,
    qfiledialog, qmessagebox, qcheckbox, qhboxlayout
)


class exceltojsonapp(qwidget):
    def __init__(self):
        super().__init__()
        self.initui()

    def initui(self):
        self.setwindowtitle("excel 转 json 工具")
        self.setgeometry(900, 500, 600, 500)

        layout = qvboxlayout()

        self.folder_label = qlabel("选择 excel 文件:")
        layout.addwidget(self.folder_label)

        self.folder_button = qpushbutton("选择文件")
        self.folder_button.clicked.connect(self.select_excel_file)
        layout.addwidget(self.folder_button)

        self.key_label = qlabel("请选择键列（可多选）:")
        layout.addwidget(self.key_label)
        self.key_checkboxes_layout = qvboxlayout()
        layout.addlayout(self.key_checkboxes_layout)

        self.value_label = qlabel("请选择值列（可多选）:")
        layout.addwidget(self.value_label)
        self.value_checkboxes_layout = qvboxlayout()
        layout.addlayout(self.value_checkboxes_layout)

        self.output_label = qlabel("选择 json 输出文件:")
        layout.addwidget(self.output_label)

        self.output_button = qpushbutton("选择文件")
        self.output_button.clicked.connect(self.select_output_file)
        layout.addwidget(self.output_button)

        self.convert_button = qpushbutton("转换并保存")
        self.convert_button.clicked.connect(self.convert_excel_to_json)
        layout.addwidget(self.convert_button)

        self.setlayout(layout)

    def select_excel_file(self):
        options = qfiledialog.options()
        file_path, _ = qfiledialog.getopenfilename(self, "选择 excel 文件", "", "excel 文件 (*.xls *.xlsx)",
                                                   options=options)
        if file_path:
            self.folder_label.settext(f"选中文件: {file_path}")
            self.excel_file_path = file_path
            self.load_excel_headers()

    def load_excel_headers(self):
        try:
            df = pd.read_excel(self.excel_file_path, dtype=str, keep_default_na=false)
            headers = df.columns.tolist()

            for i in reversed(range(self.key_checkboxes_layout.count())):
                self.key_checkboxes_layout.itemat(i).widget().setparent(none)

            for i in reversed(range(self.value_checkboxes_layout.count())):
                self.value_checkboxes_layout.itemat(i).widget().setparent(none)

            self.key_checkboxes = []
            self.value_checkboxes = []

            for header in headers:
                key_checkbox = qcheckbox(header)
                self.key_checkboxes_layout.addwidget(key_checkbox)
                self.key_checkboxes.append(key_checkbox)

                value_checkbox = qcheckbox(header)
                self.value_checkboxes_layout.addwidget(value_checkbox)
                self.value_checkboxes.append(value_checkbox)
        except exception as e:
            qmessagebox.warning(self, "错误", f"无法读取 excel 表头: {e}")

    def select_output_file(self):
        options = qfiledialog.options()
        file_path, _ = qfiledialog.getsavefilename(self, "保存 json 文件", "", "json 文件 (*.json)", options=options)
        if file_path:
            self.output_label.settext(f"输出文件: {file_path}")
            self.output_file_path = file_path

    def convert_excel_to_json(self):
        try:
            key_cols = [cb.text() for cb in self.key_checkboxes if cb.ischecked()]
            value_cols = [cb.text() for cb in self.value_checkboxes if cb.ischecked()]

            if not hasattr(self, 'excel_file_path') or not hasattr(self, 'output_file_path'):
                qmessagebox.warning(self, "错误", "请先选择 excel 文件和 json 输出路径！")
                return

            df = pd.read_excel(self.excel_file_path, dtype=str, keep_default_na=false)

            for key_col in key_cols:
                if key_col not in df.columns:
                    qmessagebox.warning(self, "错误", f"键列 {key_col} 不存在，请检查！")
                    return

            for value_col in value_cols:
                if value_col not in df.columns:
                    qmessagebox.warning(self, "错误", f"值列 {value_col} 不存在，请检查！")
                    return

            df['combined_key'] = df[key_cols].apply(lambda row: "=".join(row.values), axis=1)
            df['combined_value'] = df[value_cols].apply(lambda row: "=".join(row.values), axis=1)
            data = dict(zip(df['combined_key'], df['combined_value']))

            with open(self.output_file_path, "w", encoding="utf-8") as f:
                json.dump(data, f, ensure_ascii=false, indent=4)

            qmessagebox.information(self, "成功", "转换完成，json 已保存！")
        except exception as e:
            qmessagebox.critical(self, "错误", f"处理文件时出错: {e}")


if __name__ == "__main__":
    app = qapplication([])
    window = exceltojsonapp()
    window.show()
    app.exec_()

效果图