Find a string inside a python list

Question

Hello I have a spreadsheet xls and a XML where I read this information and my goal is to find information from XMl inside the spreadsheet for this I am using the method IN more can not find

THE XML:

<nfeProc xmlns="http://www.portalfiscal.inf.br/nfe" versao="4.00">
<NFe xmlns="http://www.portalfiscal.inf.br/nfe">
<infNFe Id="NFe35200915332149000145550010000190821314695600" versao="4.00">
<ide>
...
</ide>
<emit>
...
</emit>
<dest>
...
</dest>
<autXML>
<CNPJ>15332149000145</CNPJ>
</autXML>
<det nItem="1">
<prod>
<cProd>SL-043210</cProd>
<cEAN>6949999876804</cEAN>
<xProd>KIT ONIX / PRISMA 2020 COM MOLDURA BOTAO MODELO UNIVERSAL</xProd>
<NCM>85122021</NCM>
<CEST>0105500</CEST>
<CFOP>5405</CFOP>
<uCom>PC</uCom>
<qCom>3.0000</qCom>
<vUnCom>300.000000</vUnCom>
<vProd>900.00</vProd>
<cEANTrib>6949999876804</cEANTrib>
<uTrib>PC</uTrib>
<qTrib>3.0000</qTrib>
<vUnTrib>300.000000</vUnTrib>
<indTot>1</indTot>
<xPed>2373</xPed>
</prod>

The spreadsheet:

What I’ve already tried:

import xlrd
import os.path
import sys
import xml.etree.ElementTree as ET
#Bibliotecas
#------------------------------------------------------------------------------------------#
workbook = xlrd.open_workbook(r"C:\Users\Expedição\Videos\CSV\produtos_filtrado.xls") # Escolhe o arquivo a ser lido.

worksheet = workbook.sheet_by_index(0) #Escolha a aba a ser lida. 

for i in range(worksheet.nrows): #itere sobre os itens da aba 
    lista = (worksheet.row(i))
    print(lista)
#localiza arquivo,lista todos os itens
#------------------------------------------------------------------------------------------
#XML = input("Coloque o diretorio do XML: ").strip('"')
tree = ET.parse(r"C:\Users\Expedição\Downloads\35200915332149000145550010000190821314695600-nfe.xml")
root = tree.getroot()

ns = {'nfe': 'http://www.portalfiscal.inf.br/nfe'}
for det in root.findall('.//nfe:det', ns):
    nItem = det.attrib['nItem']
    EAN = det.find('.//nfe:cEAN', ns).text

    if EAN in lista:
        print("encontrado")
        print(EAN)
    else:
        print("nao achou")
        print(EAN)

Answer 1

The problem is in this for:

for i in range(worksheet.nrows): #itere sobre os itens da aba 
    lista = (worksheet.row(i))

You iterate through the worksheet lines, but with each iteration of for to lista is overwritten. At the end, it will only have the value of the last line read.

If you want a list of all lines, you have to add the cell values in the list. I understood that the value is in the first column, so it would look like this:

lista = []
for i in range(worksheet.nrows):
    lista.append(worksheet.cell_value(i, 0))

Only this also takes the "GTIN/EAN" header and puts it in the list. Then you can change the range to ignore the first line. Also, then when you read XML, the value of EAN is a string. But if the spreadsheet values are as numbers, the list will have values like 6949999876804.0 (who are float's). Then vc have to convert everything to string before making the comparison. Ex:

# se os valores da planilha estão como números
lista = []
for i in range(1, worksheet.nrows): # range começa em 1 (ignora o cabeçalho)
    lista.append(f'{worksheet.cell_value(i, 0):.0f}') # transforma em string, sem as casas decimais
    # se os valores da planilhas forem strings (tipo "texto"), pode fazer assim:
    # lista.append(worksheet.cell_value(i, 0))

for det in root.findall('.//nfe:det', ns):
    nItem = det.attrib['nItem']
    EAN = det.find('.//nfe:cEAN', ns).text
    if EAN in lista:
        print(f'EAN {EAN} encontrado')
    else:
        print(f'EAN {EAN} não encontrado')

Answer 2

There is a problem in the list and it comes with the text element: before the number you use for comparison, the way you are creating the list, you are actually creating a variable and not a list. I’ll put here the code of how I performed the operation

import xlrd
import os.path
import sys
import xml.etree.ElementTree as ET

workbook = xlrd.open_workbook(r"C:\Users\Expedição\Videos\CSV\produtos_filtrado.xls")

worksheet = workbook.sheet_by_index(3) #Escolha a aba a ser lida. 

lista = []
for i in range(worksheet.nrows): #itere sobre os itens da aba 
    numb_ean = (worksheet.row(i))
    lista.append(str(numb_ean[0]).replace('text:', "").replace("'", ''))
    
print(lista)

#XML = input("Coloque o diretorio do XML: ").strip('"')
tree = ET.parse(r"C:\Users\Expedição\Downloads\35200915332149000145550010000190821314695600-nfe.xml")
root = tree.getroot()

ns = {'nfe': 'http://www.portalfiscal.inf.br/nfe'}
for EAN in root.findall('.//det', ns):
    nItem = det[0].attrib['nItem']
    EAN = det[0].find('.//cEAN', ns).text
    #print (EAN)

    if  str(EAN) in lista:
        print("encontrado")
        print(EAN)
    else:
        print("nao achou")
        print(EAN)