问题描述
对于错误的开始表示歉意。我现在阅读常见问题,希望我的问题符合标准:)。我在电子表格中有以下内容: Col1 Col2
1234 12.5
1234 8.2
1234 9.8
2334 10.1
2334 7.7
4567 9.8
5678 9.9
5678 8.4
我需要在Col2中使用OpenPyxl& Python ie。
1234 30.5
2334 17.8
4567 9.8
5678 18.3
经过几个假启动,我有这样的:
#import modules
import openpyxl
from openpyxl.utils import coordinate_from_string,column_index_from_string
from openpyxl.utils.cell import _get_column_letter
import sys
from datetime import date
from datetime import time
import datetime
import calendar
from openpyxl.styles import颜色,PatternFill,Font,Border
from shutil import copyfile
#set变量
dest_filename ='P:\\Charging\\\可扩展资源\\ChargeableActivity\\January2017ChargeableActivity.xlsx'
total =
#create objects
wb = openpyxl.load_workbook(filename = dest_filename,data_only = True)
ws1 = wb.get_sheet_by_name('ChargeableActivity')
for i in range(1, ws1.max_row):
#convert ws1.cell(row = i,column = 12).value to integer(= Excel convert to number)
if isinstance(ws1.cell(row = i,column = 12).value,long):
RFCNumber = ws1.cell(row = i,column = 12).value
for ws1.iter_cols(min_col = 12,max_col = 12,min_row = 1):
如果ws1.cell(row = i,column = 12).value == RFCNumber:
total = total + ws1.cell(row = i,column = 14).value
print(RFCNumber,'Total =',total)
但输出是累积的, t删除重复的RFC号码:
Col1 Col2
1234 12.5
1234 20.7
1234 30.5
2334 40.6
4567 48.3
5678 58.1
etc
我不是一个编码器,我正在寻找一种方法来节省大量的时间编辑一个大的电子表格。欢迎任何建议。谢谢。
请尝试以下代码:
#定义关键字的一个dict {}:$ pair
ref_total = {}
#获取所有行的数据
对于ws.rows中的行:
#行元组A,B从行元组
cell_A =行[:1] [0]
cell_B =行[1:2] [0]
reference = cell_A.value
如果ref_total.keys()中的引用:
ref_total [reference] + = cell_B.value
else:
ref_total [reference] = cell_B.value $
print('%s%s'%(key,ref_total [key]))
使用Python测试:3.4.2 - openpyxl:2.4.1 - LibreOffice:4.3.3.2 / strong>
Apologies for the false start. I have now read the FAQs and hope my question meets the standards:). I have the following in a spreadsheet :
Col1 Col2
1234 12.5
1234 8.2
1234 9.8
2334 10.1
2334 7.7
4567 9.8
5678 9.9
5678 8.4
i need to total up the figures in Col2 for each reference number in Col1 using OpenPyxl & Python ie.
1234 30.5
2334 17.8
4567 9.8
5678 18.3
After a few false starts i have this :
#import modules
import openpyxl
from openpyxl.utils import coordinate_from_string, column_index_from_string
from openpyxl.utils.cell import _get_column_letter
import sys
from datetime import date
from datetime import time
import datetime
import calendar
from openpyxl.styles import Color, PatternFill, Font, Border
from shutil import copyfile
#set variables
dest_filename = 'P:\\Charging\\Chargeable Resources\\ChargeableActivity\\January2017ChargeableActivity.xlsx'
total = 0
#create objects
wb = openpyxl.load_workbook(filename = dest_filename, data_only=True)
ws1 = wb.get_sheet_by_name('ChargeableActivity')
for i in range(1, ws1.max_row):
#convert ws1.cell(row=i, column=12).value to integer (=Excel convert to number)
if isinstance(ws1.cell(row=i, column=12).value,long):
RFCNumber = ws1.cell(row=i, column=12).value
for col in ws1.iter_cols(min_col=12, max_col = 12, min_row=1):
if ws1.cell(row=i, column=12).value == RFCNumber:
total = total + ws1.cell(row=i, column=14).value
print(RFCNumber,'Total=',total)
But the output is cumulative and doesn't delete duplicate RFC numbers :
Col1 Col2
1234 12.5
1234 20.7
1234 30.5
2334 40.6
4567 48.3
5678 58.1
etcI'm not a coder and am looking for a way to save a lot of time editing a big spreadsheet. Any suggestions welcomed. Thank you.
Try this code:
# Define a dict{} for key:value pairs
ref_total = {}
# Get Data from all rows
for row in ws.rows:
# Slice cells A,B from row tuple
cell_A = row[:1][0]
cell_B = row[1:2][0]
reference = cell_A.value
if reference in ref_total.keys():
ref_total[reference] += cell_B.value
else:
ref_total[reference] = cell_B.value
for key in sorted(ref_total.keys()):
print('%s %s' % (key, ref_total[key]))
Tested with Python:3.4.2 - openpyxl:2.4.1 - LibreOffice: 4.3.3.2
这篇关于Openpyxl& Python:键列,值列 - 如何加总值并将总计分配给相应的键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!