python_ hh:mm:ss.000 in excel converts to dataframe by xlwings has problem-CodePudding

When I build exe file because of DRM i can't use pd.read_excel or pd.ExcelFile for open excel files. So I try to use xlwings for open the DRM excel file. But time data converts to something strange data...by xlwings. I don't know what's it and how to fix.

-here is time data-

import pandas as pd
import xlwings as xw
import os

app = xw.App(visible=False)
xlsx2 = xw.Book('tt.xlsm')
ref_sheet = xlsx2.sheets[1].used_range.options(pd.DataFrame, index=0, header=1, chunksize=10000).value
xlsx2.close()
app.kill()

result :

0        0.340030

1        0.340031

2        0.340034

3        0.340038

4        0.340038

CodePudding user response：

In Excel, Time is stored as daytime divided by 24 hours. As an experiment, enter =1/24 into a cell, select Time as the number format, and you should see 01:00:00 or something similar (depending on your region).

In other words, the numbers you get are fractions of a day. To interpret them as a time, use datetime.timedelta:

ref_sheet = xlsx2.sheets[1].used_range.options(
    pd.DataFrame, index=0, header=1, chunksize=10000,
    numbers=datetime.timedelta
).value

As an alternative, we can use pandas.to_timedelta to convert already obtained data:

ref_sheet = xlsx2.sheets[1].used_range.options(
    pd.DataFrame, index=0, header=1, chunksize=10000).value
ref_sheet = pd.to_timedelta(ref_sheet, unit='D')

where unit='D' means interpret the numbers as the number of days.