Formula Cell Wont Update Openpyxl

Question

I fill the cells I need, then I set the total formula. It works right in one column, with normal numbers, but in the column with times (hh:mm:ss) the total cell is not being updated. If i manually change a cell, then it will be computed to the total. I dont know why this happens.

excel sheet: The G column total cell (Qt_horas) has the formula, but it does not apply via openpyxl

the code:

df = pd.read_excel('Status Report Coagril 20-04-2022.xlsx', 'Acompanhamento Solics. Projeto')

planilha =  load_workbook('Status Report Coagril 20-04-2022.xlsx', data_only=True)

ws = planilha['Acompanhamento Solics. Projeto']

start = 17

for i, nr_solic in enumerate(sols_filhas):
    query_sol = f"select nr_solicitacao, ds_assunto, st_solic, ds_status from solicservico_v2 a, statussolic b where a.nr_solicitacao = {nr_solic} and a.st_solic = b.cd_status"
    query = db.query(query_sol)
    
    # atribuo as variaveis que vieram da query do banco
    nr_solicitacao = query['nr_solicitacao'].values[0]
    ds_assunto = query['ds_assunto'].values[0]
    ds_status = query['ds_status'].values[0]
    horas_realizadas = formata_horas(db.query(f'SELECT retorna_min_realizados_solic({nr_solicitacao})/60 minutos FROM DUAL')['minutos'].values[0])
    
    # verifico se alguma das células que estou preenchendo, foi erroneamente mergeada com as coluna E ou F, e então desfaço o merge
    e_merged = f'B{start+i}:E{start+i}'
    f_merged = f'B{start+i}:F{start+i}'
    lista = str(ws.merged_cells).split(' ')
    if e_merged in lista:
        ws.unmerge_cells(e_merged)
    if f_merged in lista:
        ws.unmerge_cells(f_merged)
    
    # insiro uma nova linha, a não ser que seja o primeiro registro, pois já há uma linha em branco
    # if i != 0:
        # ws.insert_rows(start+i)
    ws.insert_rows(start+i)
    
    # preenche_linha(ds_assunto, 'B', start+1, 'left', 'no_right', ws) 
    # preenche_linha(None, 'C', start+1, 'left', 'no_left', ws) 
    # preenche_linha(nr_solicitacao, 'D', start+1, 'center', 'total', ws) 
    # preenche_linha(ds_status, 'E', start+1, 'center', 'total', ws) 
    # preenche_linha(1, 'F', start+1, 'center', 'total', ws) 
    # preenche_linha(horas_realizadas, 'G', start+1, 'center', 'total', ws) 
    # preenche_linha('Maxicon', 'H', start+1, 'center', 'total', ws) 
        
    ws[f'B{start+i}'] = ds_assunto
    ws[f'B{start+i}'].alignment = Alignment(horizontal='left')
    ws[f'B{start+i}'].border = aplica_borda_sem_direita()
    
    ws[f'C{start+i}'].border = aplica_borda_sem_esquerda()
    
    ws[f'D{start+i}'] = nr_solicitacao
    ws[f'D{start+i}'].alignment = Alignment(horizontal='center')
    ws[f'D{start+i}'].border =  aplica_borda_total()
    
    ws[f'E{start+i}'] = ds_status
    ws[f'E{start+i}'].alignment = Alignment(horizontal='center')
    ws[f'E{start+i}'].border = aplica_borda_total()
    
    ws[f'F{start+i}'] = 1
    ws[f'F{start+i}'].alignment = Alignment(horizontal='center')
    ws[f'F{start+i}'].border = aplica_borda_total()
    
    ws[f'G{start+i}'].number_format = 'h:mm:ss'
    ws[f'G{start+i}'] = horas_realizadas
    ws[f'G{start+i}'].border = aplica_borda_total()
    ws[f'G{start+i}'].alignment = Alignment(horizontal='center')
    
    ws[f'H{start+i}'] = 'Maxicon'
    ws[f'H{start+i}'].alignment = Alignment(horizontal='center')
    ws[f'H{start+i}'].border = aplica_borda_total()
    
last_sol = len(sols_filhas) + start - 1

ws[f'F{last_sol+1}'] = f'=SUM(F{start}:F{last_sol})'
ws[f'G{last_sol+1}'].number_format = 'h:mm:ss'
ws[f'G{last_sol+1}'] = f'=SUM(G{start}:G{last_sol})'
ws[f'F{last_sol+1}'].alignment = Alignment(horizontal='center')
ws[f'G{last_sol+1}'].alignment = Alignment(horizontal='center')

planilha.save('teste.xlsx')````


  [1]: https://i.stack.imgur.com/e2xOV.png

Answer 1

I would say that even though you are setting the cell format to 'h:mm:ss', Excel does not recognise the cells as being time values until a cell is updated. However the cell format you're using will result in an incorrect result
E.g. those values that do not conform with 'normal time' will be reset to 24hr time as the format assumes the highest value to be 23:59:59.
Anything larger like 197:06:00 and 29:32:00 are going to be converted to 5:06:00 and 5:32:00 respectively.
You should use the format

'[h]:mm:ss'

(where 'h' is in square brackets) to allow hours above 24.

Even given a different format you may still have the same issues summing the values as

'=SUM(GX:GY)'

if there are and you want/need to do the sum in Excel it might work better to use the long hand like

'=SUM(GA+GB+GC+GD+...)'

Otherwise use python to change the format e.g. change everything to seconds and sum as numerical data (Excel can then display in format as required) or just do the summing in python writing all values to Excel.