Changing the time forma t on excel output

📊 Plotly Python
1

Hello all,
So I’m wondering is there any chance to change the date format in the picture to 14.03.2022.
This is my code for getting the excel sheet:

@app.callback(Output("link6", "children"),
             [Input("excel_data6", "data")],
            state=[State("start_date_picker", "date"),
            State("start_time_picker", "value"),
            State("end_date_picker", "date"),
            State("end_time_picker", "value"),
            State("database", "value")])
def download_sheet(output, start_date_picker, start_time_picker, end_date_picker, end_time_picker, database):
    if not output:
        return None
    df = pd.read_json(output, orient='split')
    df["Timestamp"] = df["Timestamp"].dt.tz_localize(None)
    start_time = datetime.datetime.strptime(start_date_picker, "%Y-%m-%d") + datetime.timedelta(hours=int(start_time_picker[0:2]))
    end_time = datetime.datetime.strptime(end_date_picker, "%Y-%m-%d") + datetime.timedelta(hours=int(end_time_picker[0:2]))
    xlsx_io = io.BytesIO()
    writer = pd.ExcelWriter(xlsx_io, engine='xlsxwriter', options={"remove_timezone" : True})
    df.to_excel(writer, sheet_name="Error Log")
    writer.save()
    xlsx_io.seek(0)
    # https://en.wikipedia.org/wiki/Data_URI_scheme
    media_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    data = base64.b64encode(xlsx_io.read()).decode("utf-8")
    href_data_downloadable = f'data:{media_type};base64,{data}'
    file_name = "Parameter_Log-{0}-{1}_{2}.xlsx".format(str(database), start_time, end_time)
    return html.A("Download Excel Sheet", download=file_name, href=href_data_downloadable)

image

2

That’s just Timestamp. Excel stores time as numbers, and what you see is not what it really looks like. The format you circle depends on your Excel settings. If you do really want it to look the way you want everywhere, you can convert it to strings.

3

Do you know what changes I need to do in excel for it to display it how I want?
Thank you

4

Right-click, format cells.

5

Hi @Traxdata
@stu 's advice is a good solution. For similar challenges you can try to use a Python library called Humanize.
Or you can also try this post about how to convert the string to a date type.

1 Like