How to format a cell number in excel for hours type through Openpyxl (Python)?

Asked

Viewed 47 times

4

Whoa, you guys are fine?

So, I’m developing a Python test API that when it’s called, it has to return me an Excel file with pre-defined data. One of these data is a time variable that must be formatted in the respective format in the Excel file, as in this example:

Formatação certa das horas

However, even formatting the cell through the ". number_format" method, the data is still as customized as you can see below.

Foto do exemplo de excel gerado pela API

The code I made is below:

from openpyxl import Workbook
from openpyxl.writer.excel import save_virtual_workbook
import datetime
from src.functions.errors import return_error

class classRelatorioExcel:

    def execute(self, req, resp, filters, tabs, identifier):
        try:
            excel_filename = 'Relatorio.xlsx'

            wb = Workbook()
            ws = wb.active
            ws.title = 'teste'

            ws['A1'] = datetime.datetime.strptime('2020-02-10', '%Y-%m-%d')
            ws['A1'].number_format = 'dd/mm/yyyy'

            ws['B1'] = datetime.time(21, 43, 50)
            ws['B1'].number_format = 'hh:mm:ss'

            wb.save(filename=excel_filename)

            output = save_virtual_workbook(wb)

            resp.set_header("Content-Disposition", "attachment; filename=\"%s\"" % excel_filename)
            resp.data = output
        except Exception as error:
            print(f'error: {error}')
            return return_error(resp, 'Erro ao tentar gerar relatório.', 500)    

Is there any way to format the cell so it stays in the hours format instead of the custom one?

  • Dude, I tried it with the various time formats that appear here: https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/Styles/Numbers.html And I couldn’t get excel to identify it as "Time", but from a look there, it might help

  • PSÉ, I also tried these formats and it didn’t work. I think q might be default Excel set as custom when we format to "Time"

  • I haven’t used this module much, much less this method . number_format(), , but have you looked at it here? https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/Styles/Numbers.html tries: Ws['B1']. number_format = 19

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.