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!
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.)
Input:
A B C D E F G H .. M
....... E1 90 2A .. 26
....... 0 80 F8 ..
Output:
A B C D E F G H .. M
....... E1902A..26
....... 080F8..
What I did so far (Code):
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
Which kind of error I get (complete Traceback):
C:Users..DesktopPractical PartCAN 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
I already tried to change the 'ref' variable but the error is always the same!
Could you please support me? Thank you very much!
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()
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!
See Question&Answers more detail:
os