How to change the shape of a cell with Pyhton and Openpyxl?

Question

All right with you?

I am working with the transposition of databases from one Excel file to the other, and when transposing the values to another file, I would like to set the types of values of cells.

As in the example below, I would like cell 1 to be of the numeric type, and the others to be text.

'''
     Loop que percorre as celulas do arquivo armazenado em "bc" e cola nas 
     células do arquivo "da".

     Este loop verifica se o valor da terceira coluna é igual a variável "mall" 
     e a partir daí começa a fazer a transposição de dados entre os arquivos, colando os dados sempre após a última linha preenchida do arquivo.

'''

da_act = da_mr
# Realiza a transposição dos dados
for r in range(1, (bc_mr + 1)):
    if bc_ws.cell(row=r, column=3).value == mall:
        val = ''
        for c in range(1, (bc_mc + 1)):
            val = bc_ws.cell(row=r, column=c).value
            if c in [1]:
                da_ws.cell(row=da_act, column=c, value=val)
                da_ws.cell(row=da_act, column=c).TYPE_NUMERIC
            else:
                da_ws.cell(row=da_act, column=c, value=val)
                da_ws.cell(row=da_act, column=c).TYPE_STRING

    if bc_ws.cell(row=r, column=3).value == mall:
        da_act = da_act+1
        print(da_act)

However all cells remain "General", and this way Excel can not make calculations with the existing numbers.

How to solve? Remembering that there will be column that the number is without decimal, and others will possess.

Answer 1

Good morning, Vitor.

Excel formats are not like the types in the programming languages, they are like a rule and Excel has some predefined and names for them for example 'Accounting', 'Text' and 'Number'. In this Python library it is possible to define these formats through the number_format property of the cells. Note the following example:

# para que o formato seja numeros sem decimal
sheet['A1'] = 12
sheet['A1'].number_format = '0'

# para que o formato seja numeros com casas decimais
sheet['A2'] = 13
sheet['A2'].number_format = '0.00'

# para que o formato seja texto
sheet['A3'] = 'ASDA'
sheet['A3'].number_format = '@'

Any doubt I’m at your disposal.

In case you want to understand more about the formats this doc helped me a lot: https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/Styles/Numbers.html