How to use the xlrd.xldate function in xlrd
To help you get started, we’ve selected a few xlrd examples, based on popular ways it is used in public projects.
Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
python-excel / xlrd / tests / test_xldate_to_datetime.py View on Github 
# Random date/times in Excel's 0-9999.9999+ range.
('1982-08-25T00:15:20.213', 30188.010650613425),
('2065-04-19T00:16:48.290', 60376.011670023145),
('3222-06-11T03:08:08.251', 483014.13065105322),
('4379-08-03T06:14:48.580', 905652.26028449077),
('5949-12-30T12:59:54.263', 1479232.5416002662),
# End of Excel's date range.
('9999-12-31T23:59:59.000', 2958465.999988426),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%dT%H:%M:%S.%f")
got = xldate.xldate_as_datetime(excel_date[1], not_1904)
self.assertEqual(got, exp)if field_type is None:
return None
elif field_type is fields.TextField:
if cell.ctype != xlrd.XL_CELL_BLANK:
return value
else:
return ""
elif field_type is fields.DatetimeField:
if value == 0.0:
return None
try:
time_tuple = xlrd.xldate_as_tuple(value, sheet.book.datemode)
except xlrd.xldate.XLDateTooLarge:
return None
value = field_type.serialize(datetime.datetime(*time_tuple))
return value.split("T00:00:00")[0]
elif field_type is fields.BoolField:
if value == 0:
return False
elif value == 1:
return True
elif cell.xf_index is None:
return value # TODO: test
else:
book = sheet.book
xf = book.xf_list[cell.xf_index] lambda date: xlrd.xldate.xldate_as_datetime(date, 0)))
except ValueError:for i in range(sheet["col"]):
hlable.append(self.intToABC(i+1))
for i in range(sheet["row"]):
vlable.append(str(i+1))
tableWidget.setRowCount(sheet["row"])
tableWidget.setColumnCount(sheet["col"])
tableWidget.setVerticalHeaderLabels(vlable)
tableWidget.setHorizontalHeaderLabels(hlable)
tableWidget.setEditTriggers(QAbstractItemView.NoEditTriggers)
for i in range(sheet["row"]):
for j in range(sheet["col"]):
value = sheet["data"][i][j].value
if sheet["data"][i][j].ctype == 3:
value = xlrd.xldate.xldate_as_datetime(value,0).strftime('%Y/%m/%d %H:%M:%S')
tableWidget.setItem(i,j,QTableWidgetItem(str(value)))
for span in sheet["merged"]:
tableWidget.setSpan(span[0],span[2],span[1]-span[0],span[3]-span[2])
QTableWidget.resizeColumnsToContents(tableWidget)
QTableWidget.resizeRowsToContents(tableWidget)
self.TableWidgets.append(tableWidget)
self.addTab(tableWidget,sheet["name"])def _parse_cell(cell_contents, cell_typ):
"""converts the contents of the cell into a pandas
appropriate object"""
if cell_typ == XL_CELL_DATE:
# Use the newer xlrd datetime handling.
try:
cell_contents = xldate.xldate_as_datetime(
cell_contents, epoch1904)
except OverflowError:
return cell_contents
# Excel doesn't distinguish between dates and time,
# so we treat dates on the epoch as times only.
# Also, Excel supports 1900 and 1904 epochs.
year = (cell_contents.timetuple())[0:3]
if ((not epoch1904 and year == (1899, 12, 31)) or
(epoch1904 and year == (1904, 1, 1))):
cell_contents = time(cell_contents.hour,
cell_contents.minute,
cell_contents.second,
cell_contents.microsecond)
elif cell_typ == XL_CELL_ERROR:def _parse_cell(cell_contents, cell_typ):
"""converts the contents of the cell into a pandas
appropriate object"""
if cell_typ == XL_CELL_DATE:
if xlrd_0_9_3:
# Use the newer xlrd datetime handling.
try:
cell_contents = \
xldate.xldate_as_datetime(cell_contents,
epoch1904)
except OverflowError:
return cell_contents
# Excel doesn't distinguish between dates and time,
# so we treat dates on the epoch as times only.
# Also, Excel supports 1900 and 1904 epochs.
year = (cell_contents.timetuple())[0:3]
if ((not epoch1904 and year == (1899, 12, 31)) or
(epoch1904 and year == (1904, 1, 1))):
cell_contents = time(cell_contents.hour,
cell_contents.minute,
cell_contents.second,
cell_contents.microsecond)
else:
# Use the xlrd <= 0.9.2 date handling.
try:def write_row(self, array):
"""
write a row into the file
"""
for i in range(0, len(array)):
value = array[i]
style = None
tmp_array = []
if isinstance(value, datetime.date) or isinstance(value, datetime.datetime):
tmp_array = [value.year, value.month, value.day]
value = xlrd.xldate.xldate_from_date_tuple(tmp_array, 0)
style = XFStyle()
style.num_format_str = "DD/MM/YY"
elif isinstance(value, datetime.time):
tmp_array = [value.hour, value.minute, value.second]
value = xlrd.xldate.xldate_from_time_tuple(tmp_array)
style = XFStyle()
style.num_format_str = "HH:MM:SS"
if style:
self.ws.write(self.current_row, i, value, style)
else:
self.ws.write(self.current_row, i, value)
self.current_row += 1'assets_type': data[0],
'name': data[1],
'sn': data[2],
'buy_user': int(data[5]),
'management_ip': data[6],
'manufacturer': data[7],
'model': data[8],
'provider': data[9],
'status': int(data[10]),
'put_zone': int(data[11]),
'group': int(data[12]),
'project': int(data[13]),
'business': int(data[14]),
}
if data[3]: assets['buy_time'] = xlrd.xldate.xldate_as_datetime(data[3], 0)
if data[4]: assets['expire_date'] = xlrd.xldate.xldate_as_datetime(data[4], 0)
if assets.get('assets_type') in ['vmser', 'server']:
server_assets = {
'ip': data[15],
'keyfile': data[16],
'username': data[17],
'passwd': data[18],
'hostname': data[19],
'port': data[20],
'raid': data[21],
'line': data[22],
}
else:
net_assets = {
'ip': data[15],
'bandwidth': data[16],
'port_number': data[17],# book = xlrd.open_workbook(file_contents=file_contents.read())
book = get_excel_book(url)
# print(book)
name = 'HSCEI'
for sheet in range(book.nsheets):
sh = book.sheet_by_index(sheet)
for rx in range(sh.nrows):
row = sh.row(rx)
# df = DataFrame(row)
# print(df)
# print(row)
date = row[0].value
pe = row[1].value
# print(type(pe))
if date and pe and type(pe) == float:
py_date = xlrd.xldate.xldate_as_datetime(date, book.datemode)
# print(py_date)
date = str(py_date)
print(pd.to_datetime(date))
Index.objects(name=name, date=date).update_one(name=name, date=date, pe=pe, upsert=True)assets = {
'assets_type':data[0],
'name':data[1],
'sn':data[2],
'buy_user':int(data[5]),
'management_ip':data[6],
'manufacturer':data[7],
'model':data[8],
'provider':data[9],
'status':int(data[10]),
'put_zone':int(data[11]),
'group':int(data[12]),
# 'project':int(data[13]),
# 'business':int(data[14]),
}
if data[3]:assets['buy_time'] = xlrd.xldate.xldate_as_datetime(data[3],0)
if data[4]:assets['expire_date'] = xlrd.xldate.xldate_as_datetime(data[4],0)
if assets.get('assets_type') in ['vmser','server']:
server_assets = {
'ip':data[13],
'keyfile':data[14],
'username':data[15],
'passwd':data[16],
'hostname':data[17],
'port':data[18],
'raid':data[19],
'line':data[20],
}
else:
net_assets = {
'ip':data[13],
'bandwidth':data[14],xlrd
Library for developers to extract data from Microsoft Excel (tm) .xls spreadsheet files
Package Health Score
80 / 100Popular xlrd functions
- xlrd.biffh.fprintf
- xlrd.biffh.XLRDError
- xlrd.cellname
- xlrd.error_text_from_code
- xlrd.error_text_from_code.get
- xlrd.formula.Operand
- xlrd.open_workbook
- xlrd.timemachine.REPR
- xlrd.timemachine.xrange
- xlrd.XL_CELL_BOOLEAN
- xlrd.XL_CELL_DATE
- xlrd.XL_CELL_EMPTY
- xlrd.XL_CELL_ERROR
- xlrd.XL_CELL_NUMBER
- xlrd.XL_CELL_TEXT
- xlrd.xldate
- xlrd.xldate.xldate_as_datetime
- xlrd.xldate.xldate_from_date_tuple
- xlrd.xldate_as_tuple
- xlrd.XLRDError