python读取excel文件-单元格拖拽互换内容，自动统计
代码如下：
import sys
import pandas as pd
from PyQt5.QtWidgets import QApplication, QMainWindow, QTableView, QVBoxLayout, QWidget
from PyQt5.QtCore import Qt, QAbstractTableModel, QModelIndex, QMimeData
class DataFrameModel(QAbstractTableModel):
def __init__(self, df):
super().__init__()
self.df = df
self.sum_row_index = self.df.shape[0] - 1
self.sum_col_index = self.df.shape[1] - 3 # Adjusted because we added two columns
self.df[f'Column_{self.df.shape[1]}'] = None
self.recalculate_all_sum_cells()
def recalculate_all_sum_cells(self):
# 确保 DataFrame 大小足够
if self.sum_row_index >= self.df.shape[0]:
self.df = pd.concat([self.df, pd.DataFrame(index=range(self.df.shape[0], self.sum_row_index + 1))],
ignore_index=True)
if self.sum_col_index + 2 >= self.df.shape[1]:
self.df = pd.concat([self.df, pd.DataFrame(columns=range(self.df.shape[1], self.sum_col_index + 3))],
axis=1)
# 计算行的总和
for row in range(self.sum_row_index):
self.df.iloc[row, self.sum_col_index] = self.df.iloc[row, 1:self.sum_col_index].apply(pd.to_numeric,
errors='coerce').sum()
self.df.iloc[self.sum_row_index, self.sum_col_index] = '合计'
numbers = []
# 计算总和和平均值
for row in range(0, self.sum_row_index, 2):
b_group_sum = pd.to_numeric(self.df.iloc[row + 1, self.sum_col_index],
errors='coerce') if row + 1 < self.sum_row_index else 0
# 计算 total_sum
if row % 2 == 0: # 第一次循环
numbers.append(b_group_sum)
print(numbers)
# 使用循环每次取两个数字相加
for i in range(0, len(numbers) - 1, 2): # 步长为2，以确保每次取两个元素
if i < len(numbers) and i + 1 < len(numbers): # 确保索引不越界
total_sum = numbers[i] + numbers[i + 1]
wucha = numbers[i] - numbers[i + 1]
print(i, "误差", wucha)
print(i, "合计", total_sum,wucha)
# 每隔4行输入一个值
self.df.iloc[(i // 2) * 4 + 0, self.sum_col_index + 1] = total_sum # 使用整数除法来匹配行索引
avg_sum = total_sum / 2
# 确保写入的是合计行
self.df.iloc[(i // 2) * 4 + 0, self.sum_col_index + 2] = avg_sum
self.df.iloc[(i // 2) * 4 + 0, self.sum_col_index + 3] = wucha
def rowCount(self, parent=QModelIndex()):
return self.df.shape[0]
def columnCount(self, parent=QModelIndex()):
return self.df.shape[1]
def data(self, index, role=Qt.DisplayRole):
if role == Qt.DisplayRole:
value = self.df.iloc[index.row(), index.column()]
if index.column() == self.sum_col_index and value == 0.0:
return ''
return '' if pd.isna(value) else str(value)
return None
def headerData(self, section, orientation, role=Qt.DisplayRole):
if role == Qt.DisplayRole:
if orientation == Qt.Horizontal:
if section == self.sum_col_index:
return '合计'
if section == self.sum_col_index + 1:
return '总和'
if section == self.sum_col_index + 2:
return '总和/2'
if section == self.sum_col_index + 3:
return '误差'
return str(self.df.columns[section])
if orientation == Qt.Vertical:
return str(self.df.index[section])
return None
def flags(self, index):
return Qt.ItemIsSelectable | Qt.ItemIsEnabled | Qt.ItemIsDragEnabled | Qt.ItemIsDropEnabled
def mimeTypes(self):
return ['application/vnd.text.index']
def mimeData(self, indexes):
mimedata = QMimeData()
encoded_data = ''
for index in indexes:
if index.isValid():
encoded_data += f'{index.row()} {index.column()} '
mimedata.setData('application/vnd.text.index', encoded_data.encode())
return mimedata
def dropMimeData(self, data, action, row, column, parent):
if action == Qt.IgnoreAction:
return True
if data.hasFormat('application/vnd.text.index'):
encoded_data = data.data('application/vnd.text.index').data().decode()
parts = list(map(int, encoded_data.split()))
if len(parts) == 2:
source_row, source_column = parts
if row == -1:
row = parent.row()
if column == -1:
column = parent.column()
if row < 0 or row >= self.df.shape[0] or column < 0 or column >= self.df.shape[1]:
return False
self.df.iloc[source_row, source_column], self.df.iloc[row, column] = self.df.iloc[row, column], \
self.df.iloc[source_row, source_column]
self.recalculate_all_sum_cells()
self.dataChanged.emit(self.index(0, 0), self.index(self.rowCount() - 1, self.columnCount() - 1))
return True
return False
class ExcelEditor(QMainWindow):
def __init__(self, df):
super().__init__()
self.df = df
self.initUI()
def initUI(self):
self.setWindowTitle('南麂岛Jason-EXCEL读取-拖拽自动计算')
self.setGeometry(100, 100, 1100, 900)
self.tableView = QTableView()
self.model = DataFrameModel(self.df)
self.tableView.setModel(self.model)
self.tableView.setDragEnabled(True)
self.tableView.setAcceptDrops(True)
self.tableView.setDropIndicatorShown(True)
layout = QVBoxLayout()
layout.addWidget(self.tableView)
container = QWidget()
container.setLayout(layout)
self.setCentralWidget(container)
if __name__ == '__main__':
app = QApplication(sys.argv)
df = pd.read_excel('分组数据+格式.xlsx', header=0)
df = df.fillna('')
# 添加合计列
df = pd.concat([df, pd.DataFrame(index=df.index, columns=['合计', '总和', '总和/2'])], axis=1)
editor = ExcelEditor(df)
editor.show()
sys.exit(app.exec_())
