今天在看csdn问答时,看到一个小伙伴提问如何对excel做如下操作。
33 | 45 | 67 | 89 | 行平均 |
---|---|---|---|---|
35 | 65 | 78 | 89 | 行平均 |
109 | 23 | 44 | 69 | 行平均 |
200 | 103 | 98 | 12 | 行平均 |
列总和 | 列总和 | 列总和 | 列总和 | 总和 |
看了会儿觉得不难,就开始搞了,结果发现也不是那么简单。吭吭哧哧搞定了,让我叉会儿腰。
import openpyxl
fn = r'test.xlsx'
wb = openpyxl.load_workbook(fn)
sheet = wb['Sheet1']
data = [[0] * sheet.max_column for _ in range(sheet.max_row)]
total_sum = 0
total_col_sum = [0 for _ in range(sheet.max_row)]
total_row_sum = [0 for _ in range(sheet.max_column)]
for i in range(1, sheet.max_row + 1):
for j in range(1, sheet.max_column + 1):
excel_value = sheet.cell(i, j).value
data[i - 1][j - 1] = excel_value
total_sum += excel_value
total_col_sum[j - 1] += excel_value
total_row_sum[i - 1] += excel_value
# print(excel_value)
wb.close()
print('row=' + str(total_row_sum))
print('col=' + str(total_col_sum))
print('total_sum=' + str(total_sum))
new_data = data.copy()
new_data.append(total_col_sum)
for i in range(len(new_data)):
if i == len(new_data) - 1:
new_data[i].append(total_sum)
else:
new_data[i].append(total_row_sum[i] / sheet.max_row)
print(new_data)
new_file = 'output.xlsx'
wb = openpyxl.Workbook()
ws = wb.active
for i in range(1, len(new_data[1]) + 1):
for j in range(1, len(new_data) + 1):
ws.cell(i, j).value = new_data[i - 1][j - 1]
wb.save(new_file)
wb.close()
本来开开心心去贴答案,结果看到有个老哥先回答了,短短几行搞定。好伤心,技术无止境,自己还是太嫩了。贴上老哥的代码链接膜拜一下。