Python / Excel Automation - Text from 2 cells into 1 cell

user_9022522 0
Viewed 136

I'm using python to automate the process of creating multiple name tags using an excel sheet list.

My problem is that I need to take the 'name' column and 'enterprise' column values ​​and put them in a single cell of a new document.

Like this:

enter image description here

To this:

enter image description here

Right now I'm using openpyxl and although I manage to transfer one of the columns I can't do it with both. The code below was one of the things that I've tried.

import openpyxl as xl

e = xl.load_workbook('etiquetas.xlsx')
eplan = e['Planilha1']

c = xl.load_workbook('Crachá Relação 15.10.19.xlsx')
cplan = c['Plan1']

maxlinhas = cplan.max_row

for i in range (2, maxlinhas+1):
    nome = cplan.cell(row = i, column = 1).value
    preenchernome = eplan.cell(row = i-1, column = 1)
    empresa = cplan.cell(row=i, column=2).value
    preencherempresa = eplan.cell(row=i - 1, column=1)
    preenchernome.value = nome, empresa
e.save('teste.xlsx')

But this code returns the following error:

ValueError: Cannot convert ('Gladisson Garcia Westphal', 'Agro Divel') to Excel

excel automation python
2 Answers

As per the docs preenchernome.value can only have one value

try to use this

preenchernome.value = '{}\n{}'.format(nome, empresa)
user_122398490

The value, that is passed on the target cell should be a single string. Thus:

wksTarget.cell(row = i, column = 1).value = '{}\n{}'.format(name, family)

should be ok. This is the whole code, that worked for me:

import openpyxl as xl
import os

wbSource = xl.load_workbook(os.path.dirname(os.path.realpath(__file__)) + '\myExcel.xlsx')
wksSourse = wbSource['Sheet1']

wbTarget = xl.load_workbook(os.path.dirname(os.path.realpath(__file__)) + '\Target.xlsx')
wksTarget = wbTarget['Sheet1']

for i in range (1, wksSourse.max_row+1):
    name = wksSourse.cell(row = i, column = 1).value
    family = wksSourse.cell(row = i, column = 2).value

    wksTarget.cell(row = i, column = 1).value = '{}\n{}'.format(name, family)

wbTarget.save(os.path.dirname(os.path.realpath(__file__)) + '\Target.xlsx')
wbTarget.close()
user_54486260
Related