Read time from excel sheet using xlrd, in time format, not in float

Question

I am trying to read some data from an excel file. One of the columns has time values ​​in the format HH: MM: SS. Xlrd reads this time and converts it to a float. I have other time values ​​in my python file that I want to compare with the time values ​​imported by excel. I can’t do this while one of them is “time” and the other is “floating”. Any suggestions?

This is what 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 read this data -

 wb=xlrd.open_workbook('datasheet.xls') sh = wb.sheet_by_index(0) timerange=sh.col_values(0) print timerange 

This is a result 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] 

Answer 1

Excel stores time as a fraction of the day. You can convert this time in Python 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 higher accuracy, you can get it by converting to milliseconds or microseconds and creating time this way.

Answer 2

The xlrd library has a built-in xldate_as_tuple () function to get most of your path:

 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:]) 

Answer 3

  def convert_excel_time(t, hour24=True): if t > 1: t = t%1 seconds = round(t*86400) minutes, seconds = divmod(seconds, 60) hours, minutes = divmod(minutes, 60) if hour24: if hours > 12: hours -= 12 return "%d:%d:%d PM" % (hours, minutes, seconds) else: return "%d:%d:%d AM" % (hours, minutes, seconds) return "%d:%d:%d" % (hours, minutes, seconds) print convert_excel_time(0.400983796) print convert_excel_time(0.900983796, hour24=False) print convert_excel_time(0.4006944444444) print convert_excel_time(1.4006944444444) 

Answer 4

You can use the xlrd built-in function "xldate_as_datetime" (as per Troy's notification)

 import xlrd from datetime import time wb=xlrd.open_workbook('datasheet.xls') date_values = xlrd.xldate_as_datetime(cell_with_excel_time, wb.datemode)