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、核心代码
①：选择 列
1 2 3 4 5 6 7 8 | 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
1 2 3 4 5 6 | 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、完整代码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | import osimport jsonimport pandas as pdfrom 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_() |
效果图
以上就是Python使用pandas读取Excel并选取列转json的详细内容，更多关于Python Excel转json的资料请关注IT俱乐部其它相关文章！
