问题描述
我是openpyxl的新手,无法确定我的错误原因是什么.希望您能看到问题并告诉我要进行哪些更改!
I am new to openpyxl and cannot figure out what the reason for my error is. I hope you can see the problem and show me what to change!
我要做什么:我想将每行从F列到M的单元格连接起来,并将连接后的值放入E列,如下所示.(由于每行都是不同种类的信号,所以行并不总是从F列填充到M列.但是我还没有为此添加if子句.这只是有关结构的信息.)
What I want to do:I want to concatenate the cells from columns F to M per row and put the concatenated value into column E like below.(The rows do not always fill up from column F to column M, since per row are different kind of signals. But I have not put an if clause for this yet. This is just an information about the structure.)
输入:
A B C D E F G H .. M
....... E1 90 2A .. 26
....... 0 80 F8 ..
输出:
A B C D E F G H .. M
....... E1902A..26
....... 080F8..
我到目前为止所做的(代码):
theFile = openpyxl.load_workbook('T013.xlsx')
allSheetNames = theFile.sheetnames
print("All sheet names {} " .format(theFile.sheetnames))
sheet = theFile.active
#loop to concatenate
for i,row in enumerate(sheet.rows,1):
for column in range(6,13): #column F-M
sRow=str(i)
Ecell = sheet['E' + sRow]
ref = sheet["F:M"] #range of cells
for cell in range(ref):
values = str(cell.value) #collect data
Ecell.value = ''.join(values) # write values
我遇到哪种错误(完整的追溯):
C:\Users\..\Desktop\Practical Part\CAN Python>python ConcatenateHEX.py
All sheet names ['T013']
Traceback (most recent call last):
File "ConcatenateBIN.py", line 38, in <module>
for cell in range(ref):
TypeError: 'tuple' object cannot be interpreted as an integer
我已经尝试更改'ref'变量,但错误始终相同!你能支持我吗?非常感谢你!
I already tried to change the 'ref' variable but the error is always the same!Could you please support me? Thank you very much!
编辑(2/10/2020):此外,我想对所有要记录下来的行使用该函数.因此,我想到了这一更改:
EDIT (2/10/2020):Further, I want to use the function for all rows which are too many to write down. Therefore I came up with this change:
def concat_f_to_m():
for row_value in range(1, sheet.max_row+1):
values=[]
del values[:]
for row in sheet.iter_rows(min_col=6, max_col=14, min_row=row_value, max_row=row_value):
for cell in row:
if cell.value != None:
values.append(str(cell.value))
else:
del values[:]
break
#print(values)
sheet[f'E{row_value}'].value= ''.join(values)
concat_f_to_m()
concat_f_to_m()
我无法克服以下问题:从行1到行xyz的所有值都打印在row_value单元格中(例如row_value = 13,从行1到13的所有值都在单元格E13中串联).因此,我想遍历row_value以便遍历所有行,但是以某种方式行不通.您能否提示我如何通过在特定行加入值列表来连接所有行?谢谢!
I cannot overcome the issue that all the values from row 1 to row xyz are printed in the row_value cell (e.g. row_value=13, all values from row 1 to 13 are concatenated in cell E13). I therefore wanted to iterate over row_value in order to go through all rows but somehow that does not work. Could you give me a hint how to concatenate through all rows by joining the values list at the certain row? Thank you!
推荐答案
使用openpyxl
,我创建了一个小函数,可以一次完成一行操作:
Using openpyxl
, I created a little function that will do what you want for a line at a time:
import openpyxl
theFile = openpyxl.load_workbook('T013.xlsx')
allSheetNames = theFile.sheetnames
print("All sheet names: {}" .format(theFile.sheetnames))
sheet = theFile.active
def concat_f_to_m(row_value):
values=[]
del values[:]
for row in sheet.iter_rows(min_col=6, max_col=13, min_row=row_value, max_row=row_value):
for cell in row:
if cell.value == None:
pass
else:
values.append(str(cell.value))
sheet[f'E{row_value}'].value= ''.join(values)
concat_f_to_m(1)
concat_f_to_m(2)
theFile.save('T013.xlsx')
输出:
这篇关于Openpyxl:TypeError-将多列串联到每行一个单元格中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!