Python使用pandas读取Excel并选取列转json
这篇文章主要为大家详细介绍了通过Python和pyqt5设计一个工具,可以实现pandas读取Excel选取列作为键或值转json,感兴趣的小伙伴可以了解下
目录
- 1、背景介绍
- 2、库的安装
- 3、核心代码
- 4、完整代码
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_()
效果图
以上就是Python使用pandas读取Excel并选取列转json的详细内容，更多关于Python Excel转json的资料请关注昆居客【www.kunjuke.com】其它相关文章！