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
Advertisement
Answer
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.