Python: Running slow code and other problems

0

Dear colleagues,

Can anyone clarify what might be happening?

In short, this is a code for reading cells from a worksheet, sorting and transporting to a new worksheet.

The problem: The code does not run and does not display an error. I'm using PyCharm. I spent the day yesterday and today testing, and the interesting thing is that DEBUG does not present an error and runs the code quietly.

To understand what I'm creating: I got the results of LOTOFACIL (lottery game) and put it in an excel spreadsheet. The spreadsheet was named lotofacil.xlsx . The worksheet has 1664 rows and 15 columns.

See an example of the spreadsheet ...

Well, my code is like this ...

    import openpyxl




wb = openpyxl.load_workbook('lotofacil.xlsx') #abre o arquivo
nome_plan = wb.sheetnames[0] #identifica o nome da planilha de indice zero
planilha = wb[nome_plan] #entra na planilha indicada


linhas = planilha.max_row #qtd de linhas
colunas = planilha.max_column #qtd de colunas

lista = [] #valores de cada linha são adicionados e depois excluídos


novo_arquivo = openpyxl.Workbook() #abre novo arquivo excel
nome_planNova = novo_arquivo.sheetnames[0] #identifica o nome da planilha de indice zero
planilhaNova = novo_arquivo[nome_planNova] #entra na planilha indicada
novo_arquivo.save('loto_python.xlsx') #salva a planilha nova


#cria um loop.
x=1
while linhas >= x: #enquanto o número de linhas da planilha (1664), for maior ou igual a 'x' o programa executa.
    y1=1
    while y1 <= colunas: #enquanto o número de colunas da planilha(15), for maior ou igual a 'y' o programa executa.
        celula = planilha.cell(row=x, column=y1).value #recebe o conteudo da celula.
        lista.append(celula) #adiciona o conteúdo da célula na lista. A lista terá o tamanho de 15 posições.
        y1+=1

    lista.sort() #ordena a lista, do menor para o maior.

    y2=1
    while y2 <= colunas:
        planilhaNova.cell(row=x, column=y2).value = lista[y2-1] #pega cada item da lista
        novo_arquivo.save('loto_python.xlsx') #salva novamente a planilha nova
        y2+=1

    lista = [] #esvazia a lista. Isso permite manter o loop e seguir para a linha seguinte.

    x+=1

See the output, already in the new worksheet ... by DEBUG. Remember: normal execution does not work.

    
asked by anonymous 21.05.2018 / 04:34

1 answer

0
Just to iterate through the given solution, in the way you did, calling the save() method inside the repeat loop, you will be saving the file to disk multiple times (15 x 1664 = 24,960 times precisely). Reading and writing to disk is one of the slowest tasks on a computer, imagine doing it almost 25,000 times with a 15-column file and 1664 lines. Obviously you do not have to do this. Your novo_arquivo object already represents your entire file, so you just have to modify it, keeping all the changes in memory only, and only persist them on disk once.

To do this, just put the line of code:

novo_arquivo.save('loto_python.xlsx')

At the end of the program, out of all the loopbacks.

    
22.05.2018 / 02:35