|
发表于 2024-3-21 07:26:52
|
显示全部楼层
好的,下面是根据你的要求使用PySide6编写的代码:
- import sys
- import os
- import xlrd
- from PySide6.QtCore import Qt
- from PySide6.QtWidgets import QApplication, QMainWindow, QFileDialog, QMessageBox, QLabel, QPushButton, QComboBox
- class ExcelConverter(QMainWindow):
- def __init__(self):
- super().__init__()
- self.setWindowTitle("Excel转换器")
- self.resize(400, 300)
- self.workbook_path = ""
- self.sheet_names = []
- self.column_names = []
-
- self.file_label = QLabel(self)
- self.file_label.setGeometry(20, 20, 361, 16)
- self.file_label.setText("请选择要打开的工作簿:")
-
- self.file_button = QPushButton(self)
- self.file_button.setGeometry(20, 50, 101, 31)
- self.file_button.setText("选择文件")
- self.file_button.clicked.connect(self.open_workbook)
-
- self.sheet_label = QLabel(self)
- self.sheet_label.setGeometry(20, 100, 191, 16)
- self.sheet_label.setText("请选择要操作的Sheet:")
-
- self.sheet_combobox = QComboBox(self)
- self.sheet_combobox.setGeometry(20, 130, 361, 31)
- self.sheet_combobox.currentIndexChanged.connect(self.load_columns)
-
- self.column_label = QLabel(self)
- self.column_label.setGeometry(20, 180, 191, 16)
- self.column_label.setText("请选择要转换格式的列:")
-
- self.column_combobox = QComboBox(self)
- self.column_combobox.setGeometry(20, 210, 361, 31)
-
- self.format_label = QLabel(self)
- self.format_label.setGeometry(20, 260, 191, 16)
- self.format_label.setText("请选择转换后的格式:")
-
- self.format_combobox = QComboBox(self)
- self.format_combobox.setGeometry(20, 290, 361, 31)
- self.format_combobox.addItems(["常规", "数值", "文本", "日期"])
-
- self.convert_button = QPushButton(self)
- self.convert_button.setGeometry(150, 340, 101, 31)
- self.convert_button.setText("开始转换")
- self.convert_button.clicked.connect(self.convert_data)
-
- def open_workbook(self):
- file_dialog = QFileDialog()
- file_dialog.setFileMode(QFileDialog.AnyFile)
- file_dialog.setNameFilter("Excel 文件 (*.xls *.xlsx)")
- if file_dialog.exec_():
- self.workbook_path = file_dialog.selectedFiles()[0]
- self.file_label.setText(f"选择的工作簿:{self.workbook_path}")
- self.load_sheets()
-
- def load_sheets(self):
- self.sheet_combobox.clear()
- try:
- workbook = xlrd.open_workbook(self.workbook_path)
- self.sheet_names = workbook.sheet_names()
- self.sheet_combobox.addItems(self.sheet_names)
- except Exception as e:
- QMessageBox.critical(self, "错误", f"无法打开工作簿:{e}")
-
- def load_columns(self):
- self.column_combobox.clear()
- selected_sheet = self.sheet_combobox.currentText()
- try:
- workbook = xlrd.open_workbook(self.workbook_path)
- sheet = workbook.sheet_by_name(selected_sheet)
- self.column_names = sheet.row_values(0)
- self.column_combobox.addItems(self.column_names)
- except Exception as e:
- QMessageBox.critical(self, "错误", f"无法读取工作簿的列名:{e}")
-
- def convert_data(self):
- selected_sheet = self.sheet_combobox.currentText()
- selected_column = self.column_combobox.currentText()
- selected_format = self.format_combobox.currentText()
-
- try:
- workbook = xlrd.open_workbook(self.workbook_path, formatting_info=True)
- sheet_index = workbook.sheet_names().index(selected_sheet)
- sheet = workbook.sheet_by_index(sheet_index)
-
- column_index = self.column_names.index(selected_column)
- format_index = self.get_format_index(workbook, sheet, column_index)
-
- converted_data = self.convert_column(sheet, column_index, format_index, selected_format)
- if converted_data:
- save_path, _ = QFileDialog.getSaveFileName(filter="Excel 文件 (*.xlsx)")
- if save_path:
- new_workbook = xlrd.copy(workbook)
- new_sheet = new_workbook.get_sheet(sheet_index)
-
- for row_index, cell_value in converted_data.items():
- new_sheet.write(row_index, len(self.column_names), cell_value)
-
- new_workbook.save(save_path)
-
- reply = QMessageBox.question(self, "询问", "转换成功!是否继续转换?", QMessageBox.Yes | QMessageBox.No)
- if reply == QMessageBox.No:
- sys.exit()
-
- except Exception as e:
- QMessageBox.critical(self, "错误", f"转换失败:{e}")
-
- def get_format_index(self, workbook, sheet, column_index):
- xfs = workbook.xf_list
- colxfs = sheet.colinfo_map[column_index].xf_index
- return xfs[colxfs].format_key
-
- def convert_column(self, sheet, column_index, format_index, selected_format):
- converted_data = {}
-
- if selected_format == "常规":
- return None
-
- for row_index in range(1, sheet.nrows):
- cell_value = sheet.cell_value(row_index, column_index)
- cell_type = sheet.cell_type(row_index, column_index)
- if cell_type == xlrd.XL_CELL_DATE and selected_format == "日期":
- converted_value = xlrd.xldate_as_datetime(cell_value, 0).strftime("%Y-%m-%d")
- elif cell_type == xlrd.XL_CELL_NUMBER and selected_format == "数值":
- converted_value = str(int(cell_value))
- elif cell_type == xlrd.XL_CELL_TEXT and selected_format == "文本":
- converted_value = str(cell_value)
- else:
- continue
-
- converted_data[row_index] = converted_value
-
- return converted_data
- if __name__ == "__main__":
- app = QApplication(sys.argv)
- converter = ExcelConverter()
- converter.show()
- sys.exit(app.exec())
复制代码
这个程序使用了xlrd库来读取Excel文件,并使用PySide6创建了一个简单的界面。用户可以选择要打开的工作簿,然后通过下拉框选择要操作的Sheet和要转换格式的列名称。在转换过程中,根据用户选择的转换格式以及单元格的类型,将数据进行相应的格式转换,并将转换后的数据写入新添加的“转换后列”。
需要注意的是,这个程序只能读取xls和xlsx格式的工作簿,并且依赖于xlrd库来处理Excel文件。希望这个程序能够满足你的需求,如有其他问题请随时提问!
以上回复来自 FishC AI 小助理,如未能正确解答您的问题,请继续追问。 |
|