Python: Slow code execution and other problems

Question

Dear colleagues,

Can someone enlighten me on what might be going on?

In summary, it is a code to read cells from a spreadsheet, sort and transport to a new spreadsheet.

The problem: The code does not execute and shows no error. I am using Pycharm. I spent the day yesterday and today testing and, the interesting thing is that the DEBUG does not present error and runs the code quietly.

To understand what I’m creating: I took the results of LOTOFACIL (lottery game) and put in an excel spreadsheet. The spreadsheet was named lotofacil.xlsx. The spreadsheet 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 spreadsheet... by DEBUG. Remembering: normal execution does not work.

Answer 1

Just to iterate the given solution, the way you did, calling the method save() inside the repeat loop, you will be saving the disk file multiple times (15 x 1664 = 24.960 times precisely). Read and write on disk is one of the slowest tasks on a computer, imagine doing this almost 25 thousand times with a file of 15 columns and 1664 rows. Obviously you don’t need to do this. Your object novo_arquivo already represents your entire file, so just modify it, persisting all changes only in memory and only at the end of the run persist them on disk only once.

To do this, simply place the code line:

novo_arquivo.save('loto_python.xlsx')

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