|
|
楼主 |
发表于 2024-3-19 16:49:13
|
显示全部楼层
from PySide6.QtWidgets import QApplication, QFileDialog, QMessageBox, QPlainTextEdit, QLineEdit, QLabel, QVBoxLayout, QHBoxLayout, QWidget, QPushButton, QComboBox
from PySide6.QtGui import QTextCharFormat, QTextCursor, QColor
import openpyxl
class MainWindow(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle('Excel文件内容转换')
self.resize(500, 300)
self.layout = QVBoxLayout()
# 工作簿选择按钮和标签
self.workbook_btn = QPushButton('选择工作簿')
self.workbook_label = QLabel('工作簿:')
# 工作表选择框
self.sheet_label = QLabel('工作表:')
self.sheet_combo = QComboBox()
# 转换列选择框
self.source_col_label = QLabel('源列:')
self.source_col_combo = QComboBox()
self.dest_col_label = QLabel('目标列:')
self.dest_col_combo = QComboBox()
# 单元格格式选择框
self.cell_format_label = QLabel('单元格格式:')
self.cell_format_combo = QComboBox()
# 转换按钮
self.convert_btn = QPushButton('转换')
# 添加控件到布局中
self.layout.addWidget(self.workbook_btn)
self.layout.addWidget(self.workbook_label)
self.layout.addWidget(self.sheet_label)
self.layout.addWidget(self.sheet_combo)
self.layout.addWidget(self.source_col_label)
self.layout.addWidget(self.source_col_combo)
self.layout.addWidget(self.dest_col_label)
self.layout.addWidget(self.dest_col_combo)
self.layout.addWidget(self.cell_format_label)
self.layout.addWidget(self.cell_format_combo)
self.layout.addWidget(self.convert_btn)
self.setLayout(self.layout)
# 绑定按钮点击事件
self.workbook_btn.clicked.connect(self.select_workbook)
self.convert_btn.clicked.connect(self.convert)
def select_workbook(self):
file_dialog = QFileDialog()
workbook_path = file_dialog.getOpenFileName(self, '选择工作簿')
self.workbook_label.setText(f'工作簿: {workbook_path}')
self.load_sheets(workbook_path)
def load_sheets(self, workbook_path):
if not workbook_path:
return
workbook = openpyxl.load_workbook(filename=workbook_path)
sheets = workbook.sheetnames
self.sheet_combo.clear()
self.sheet_combo.addItems(sheets)
def load_columns(self, workbook_path, sheet_name):
if not workbook_path or not sheet_name:
return
workbook = openpyxl.load_workbook(filename=workbook_path)
sheet = workbook[sheet_name]
columns = sheet.iter_cols(values_only=True, max_row=1)
column_names = next(columns)
self.source_col_combo.clear()
self.dest_col_combo.clear()
self.source_col_combo.addItems(column_names)
self.dest_col_combo.addItems(column_names)
def convert(self):
workbook_path = self.workbook_label.text().split(': ')[-1]
sheet_name = self.sheet_combo.currentText()
source_col = self.source_col_combo.currentText()
dest_col = self.dest_col_combo.currentText()
cell_format = self.cell_format_combo.currentText()
# 打开工作簿
try:
workbook = openpyxl.load_workbook(filename=workbook_path)
except Exception as e:
QMessageBox.warning(self, '错误', f'打开工作簿失败：{str(e)}')
return
# 获取源列和目标列的索引
try:
source_col_index = self.get_column_index(source_col)
dest_col_index = self.get_column_index(dest_col)
except ValueError as e:
QMessageBox.warning(self, '错误', str(e))
return
# 获取活动的工作表
sheet = workbook[sheet_name]
# 遍历行并转换格式
for row in sheet.iter_rows(min_row=2, values_only=True):
source_value = row[source_col_index - 1]
# 执行转换操作，这里只是一个示例，你可以根据需要进行修改
dest_value = str(source_value).upper()
# 将转换结果写入目标列
sheet.cell(row=row[0].row, column=dest_col_index).value = dest_value
# 设置单元格格式
cell = sheet.cell(row=row[0].row, column=dest_col_index)
cell.number_format = cell_format
# 保存转换后的文件
try:
workbook.save(filename=workbook_path)
except Exception as e:
QMessageBox.warning(self, '错误', f'保存工作簿失败：{str(e)}')
return
# 清空下拉框选择
self.sheet_combo.clear()
self.source_col_combo.clear()
self.dest_col_combo.clear()
self.cell_format_combo.clear()
# 询问用户是否继续转换
reply = QMessageBox.question(
self, '询问', '是否继续转换?', QMessageBox.Yes | QMessageBox.No
)
if reply == QMessageBox.No:
QApplication.quit()
def get_column_index(self, column_name):
if not column_name.isalpha():
raise ValueError('列名必须由字母组成')
index = 0
for char in column_name:
index = index * 26 + ord(char.upper()) - ord('A') + 1
return index
app = QApplication([])
window = MainWindow()
window.show()
app.exec() |
|
( 粤ICP备18085999号-1 | 粤公网安备 44051102000585号)
狗仔卡
显身卡
发表于 2024-3-19 16:42:28