Read time from excel sheet using xlrd, in time format and not in float
Solution 1
Excel stores times as fractions of a day. You can convert this to a Python time as follows:
from datetime import time
x = excel_time # a float
x = int(x * 24 * 3600) # convert to number of seconds
my_time = time(x//3600, (x%3600)//60, x%60) # hours, minutes, seconds
If you need more precision, you can get it by converting to milliseconds or microseconds and creating a time that way.
Solution 2
The xlrd library has a built-in, xldate_as_tuple() function for getting you most of the way there:
import xlrd
from datetime import time
wb=xlrd.open_workbook('datasheet.xls')
date_values = xlrd.xldate_as_tuple(cell_with_excel_time, wb.datemode)
# date_values is now a tuple with the values: (year, month, day, hour, minute, seconds),
# so you just need to pass the last 3 to the time() function.
time_value = time(*date_values[3:])
Kriti Kapoor
Updated on June 17, 2022Comments
-
Kriti Kapoor almost 2 years
I am trying to read some data from a excel file. One of the columns has time values in the format HH:MM:SS. Xlrd reads this time and converts it into float. I have another time values in my python file which I want to compare with the excel-imported time values. I am not able to do that as long as one of them is a "time" and the other is a "float". Any suggestions?
This is how my excel file looks like -
Time L_6_1 PW_6_1 Tc_6_1 Te_6_1 0:00:00 10000 500 290 270 1:00:00 10000 600 290 270 2:00:00 10000 700 290 270 3:00:00 10000 800 290 270 4:00:00 10000 900 290 270
And this is how I am reading this data -
wb=xlrd.open_workbook('datasheet.xls') sh = wb.sheet_by_index(0) timerange=sh.col_values(0) print timerange
This is the output with float values for time -
[u'Time', 0.0, 0.041666666666666664, 0.083333333333333301, 0.125, 0.166666666666 66699, 0.20833333333333301, 0.25, 0.29166666666666702, 0.33333333333333298, 0.37 5, 0.41666666666666702, 0.45833333333333298, 0.5, 0.54166666666666696, 0.5833333 3333333304, 0.625, 0.66666666666666696, 0.70833333333333304, 0.75, 0.79166666666 666696, 0.83333333333333304, 0.875, 0.91666666666666696, 0.95833333333333304]
-
Or Duan almost 9 yearsBest answer! I always prefer to use existing code then re-inventing the wheel
-
Exelian over 3 yearsIt's helpful if you also explain what your code is doing. This way you'll get more upvotes and your answer will be useful to people searching on Stack Overflow