Python使用pandas讀取Excel并選取列轉json

更新時間：2025年02月11日 10:45:03 作者：一晌小貪歡

這篇文章主要為大家詳細介紹了通過Python和pyqt5設計一個工具,可以實現pandas讀取Excel選取列作為鍵或值轉json,感興趣的小伙伴可以了解下

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_()

效果圖