Openpyxl:TypeError-将多列串联到每行一个单元格中 [英] Openpyxl: TypeError - Concatenation of several columns into one cell per row

问题描述

我是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-将多列串联到每行一个单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！