Xlrd Xlrderror String continue Expected Chars Found
Python xlrd.xldate_as_tuple() Examples
The following are 25 code examples of xlrd.xldate_as_tuple() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module xlrd , or try the search function 
.
Example #1
def assertExcelRow(self, sheet, row_num, values, tz=None): """ Asserts the cell values in the given worksheet row. Date values are converted using the provided timezone. """ expected_values = [] for expected in values: # if expected value is datetime, localize and remove microseconds if isinstance(expected, datetime): expected = expected.astimezone(tz).replace(microsecond=0, tzinfo=None) elif isinstance(expected, date): expected = datetime.combine(expected, time(0, 0)) expected_values.append(expected) actual_values = [] for c in range(0, sheet.ncols): cell = sheet.cell(row_num, c) actual = cell.value if cell.ctype == XL_CELL_DATE: actual = datetime(*xldate_as_tuple(actual, sheet.book.datemode)) actual_values.append(actual) self.assertEqual(actual_values, expected_values)
Example #2
def get_cell_val(sheet, i, j, datemode=0): ctype = sheet.cell(i, j).ctype cell = sheet.cell_value(i, j) if ctype == 2 and cell % 1 == 0: # 如果是整形 cell = int(cell) elif ctype == 3: # 转换为datetime对象 if cell >= 1.0 and cell < 61.0: date_value = xlrd.xldate_as_datetime(cell, datemode) cell = date_value.strftime('%Y/%m/%d %H:%M:%S') return cell date_value = xlrd.xldate_as_tuple(cell, datemode) if date_value[0]==0 and date_value[1] == 0 and date_value[2] == 0: cell = '%d:%02d:%02d'%(date_value[3], date_value[4], date_value[5]) elif date_value[3]==0 and date_value[4] == 0 and date_value[5] == 0: cell = date(*date_value[:3]).strftime('%Y/%m/%d') else: cell = datetime(*date_value).strftime('%Y/%m/%d %H:%M:%S') elif ctype == 4: cell = True if cell == 1 else False return cell Example #3
def get_row_data(bk, sh, rowx, colrange): result = [] dmode = bk.datemode ctys = sh.row_types(rowx) cvals = sh.row_values(rowx) for colx in colrange: cty = ctys[colx] cval = cvals[colx] if bk.formatting_info: cxfx = str(sh.cell_xf_index(rowx, colx)) else: cxfx = '' if cty == xlrd.XL_CELL_DATE: try: showval = xlrd.xldate_as_tuple(cval, dmode) except xlrd.XLDateError as e: showval = "%s:%s" % (type(e).__name__, e) cty = xlrd.XL_CELL_ERROR elif cty == xlrd.XL_CELL_ERROR: showval = xlrd.error_text_from_code.get(cval, '<Unknown error code 0x%02x>' % cval) else: showval = cval result.append((colx, cty, showval, cxfx)) return result
Example #4
def get_row_data(bk, sh, rowx, colrange): result = [] dmode = bk.datemode ctys = sh.row_types(rowx) cvals = sh.row_values(rowx) for colx in colrange: cty = ctys[colx] cval = cvals[colx] if bk.formatting_info: cxfx = str(sh.cell_xf_index(rowx, colx)) else: cxfx = '' if cty == xlrd.XL_CELL_DATE: try: showval = xlrd.xldate_as_tuple(cval, dmode) except xlrd.XLDateError as e: showval = "%s:%s" % (type(e).__name__, e) cty = xlrd.XL_CELL_ERROR elif cty == xlrd.XL_CELL_ERROR: showval = xlrd.error_text_from_code.get(cval, '<Unknown error code 0x%02x>' % cval) else: showval = cval result.append((colx, cty, showval, cxfx)) return result
Example #5
def convert_cell(self, cell, sheet): value = cell.value try: if cell.ctype == 3: if value == 0: return None year, month, day, hour, minute, second = \ xlrd.xldate_as_tuple(value, sheet.book.datemode) if (year, month, day) == (0, 0, 0): value = time(hour, minute, second) return value.isoformat() else: value = datetime(year, month, day, hour, minute, second) return value.isoformat() except Exception: pass return safe_string(value)
Example #6
def cell_display(cell, datemode=0, encoding='ascii'): cty = cell.ctype if cty == xlrd.XL_CELL_EMPTY: return 'undefined' if cty == xlrd.XL_CELL_BLANK: return 'blank' if cty == xlrd.XL_CELL_NUMBER: return 'number (%.4f)' % cell.value if cty == xlrd.XL_CELL_DATE: try: return "date (%04d-%02d-%02d %02d:%02d:%02d)" \ % xlrd.xldate_as_tuple(cell.value, datemode) except xlrd.xldate.XLDateError: return "date? (%.6f)" % cell.value if cty == xlrd.XL_CELL_TEXT: return "text (%s)" % cell.value.encode(encoding, 'replace') if cty == xlrd.XL_CELL_ERROR: if cell.value in xlrd.error_text_from_code: return "error (%s)" % xlrd.error_text_from_code[cell.value] return "unknown error code (%r)" % cell.value if cty == xlrd.XL_CELL_BOOLEAN: return "logical (%s)" % ['FALSE', 'TRUE'][cell.value] raise Exception("Unknown Cell.ctype: %r" % cty) Example #7
def read_xls(_file): agent_config_vars['data_format'] = 'CSV' # treat as CSV from here out agent_config_vars['timestamp_format'] = ['epoch'] # open workbook with xlrd.open_workbook(_file) as wb: # for each sheet in the workbook for sheet in wb.sheets(): # for each row in the sheet for row in sheet.get_rows(): # build dict of <field name: value> d = label_message(list(map(lambda x: x.value, row))) # turn datetime into epoch timestamp = '' while timestamp == '' and len(agent_config_vars['timestamp_field']) != 0: timestamp_field = agent_config_vars['timestamp_field'].pop(0) try: timestamp_xlrd = d[timestamp_field] except KeyError: continue timestamp = get_timestamp_from_datetime(datetime( *xlrd.xldate_as_tuple( timestamp_xlrd, sheet.book.datemode))) d[timestamp_field] = timestamp agent_config_vars['timestamp_field'] = [timestamp_field] yield d
Example #8
def unknownValue(self,num,ctype): if ctype == 2 and num % 1 == 0: # 如果是整形 return self.Int(num) elif ctype == 3: # 转成datetime对象 date = datetime(*xldate_as_tuple(num, 0)) return date.strftime('%Y/%d/%m %H:%M:%S') elif ctype == 4: return False if num == 0 else True else: return num # 解析一行 Example #9
def TranslateSpreadsheet(sheet, csvfile): print 'Translating spreadsheet %s to CSV %s' % (sheet, csvfile) wb = xlrd.open_workbook(sheet) sheet = wb.sheet_by_index(0) with open(csvfile, 'w') as out: writer = csv.writer(out, quotechar='\"') date_columns = [] for rownum in range(0, sheet.nrows): row = sheet.row_values(rownum) for i in range(0, len(row)): if type(row[i]) == unicode: row[i] = row[i].encode('ascii', 'ignore') # For the first two rows, find any labels containing 'Date' substring... if rownum==0 or rownum==1: for i in range(0, len(row)): if isinstance(row[i], basestring) and 'Date' in row[i]: print 'Found date column %s at %d' % (row[i], i) date_columns.append(i) else: for i in date_columns: translated_date = xlrd.xldate_as_tuple(row[i], 0) row[i] = '%04d-%02d-%02d' % (translated_date[0], translated_date[1], translated_date[2]) for i in range(0, len(row)): if type(row[i]) == float and int(row[i]) == row[i]: row[i] = int(row[i]) # Translate all strings to ascii, throwing away any unknown characters writer.writerow(row) # Find the directory of this script. Example #10
def worksheet_from_excel(excel_sheet): worksheet = Worksheet() for col in range(excel_sheet.ncols): for row in range(excel_sheet.nrows): cell = excel_sheet.cell(row, col) if cell.ctype == XL_CELL_ERROR: formula = '=%s' % (error_text_from_code[cell.value], ) elif cell.ctype == XL_CELL_DATE: formula = '=DateTime(%s, %s, %s, %s, %s, %s)' % xldate_as_tuple( cell.value, excel_sheet.book.datemode) else: formula = unicode(excel_sheet.cell(row, col).value) worksheet[col + 1, row + 1].formula = formula return worksheet
Example #11
def format_date(self, date_str): if self.ftype == "csv": return datetime.strptime(date_str, self.date_format) elif self.ftype == 'xlsx' or self.ftype == 'xls': return datetime(*xlrd.xldate_as_tuple(date_str, self.datemode))
Example #12
def showable_cell_value(celltype, cellvalue, datemode): if celltype == xlrd.XL_CELL_DATE: try: showval = xlrd.xldate_as_tuple(cellvalue, datemode) except xlrd.XLDateError as e: showval = "%s:%s" % (type(e).__name__, e) elif celltype == xlrd.XL_CELL_ERROR: showval = xlrd.error_text_from_code.get( cellvalue, '<Unknown error code 0x%02x>' % cellvalue) else: showval = cellvalue return showval
Example #13
def showable_cell_value(celltype, cellvalue, datemode): if celltype == xlrd.XL_CELL_DATE: try: showval = xlrd.xldate_as_tuple(cellvalue, datemode) except xlrd.XLDateError as e: showval = "%s:%s" % (type(e).__name__, e) elif celltype == xlrd.XL_CELL_ERROR: showval = xlrd.error_text_from_code.get( cellvalue, '<Unknown error code 0x%02x>' % cellvalue) else: showval = cellvalue return showval
Example #14
def parse_date_value(self, value): return xlrd.xldate_as_tuple(value, 0)
Example #15
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) ### example on how you can get the data #sheet_data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] ### other useful methods: # print "\nROWS, COLUMNS, and CELLS:" # print "Number of rows in the sheet:", # print sheet.nrows # print "Type of data in cell (row 3, col 2):", # print sheet.cell_type(3, 2) # print "Value in cell (row 3, col 2):", # print sheet.cell_value(3, 2) # print "Get a slice of values in column 3, from rows 1-3:" # print sheet.col_values(3, start_rowx=1, end_rowx=4) # print "\nDATES:" # print "Type of data in cell (row 1, col 0):", # print sheet.cell_type(1, 0) # exceltime = sheet.cell_value(1, 0) # print "Time in Excel format:", # print exceltime # print "Convert time to a Python datetime tuple, from the Excel float:", # print xlrd.xldate_as_tuple(exceltime, 0) data = { 'maxtime': (0, 0, 0, 0, 0, 0), 'maxvalue': 0, 'mintime': (0, 0, 0, 0, 0, 0), 'minvalue': 0, 'avgcoast': 0 } return data Example #16
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) data = None # YOUR CODE HERE # Remember that you can use xlrd.xldate_as_tuple(sometime, 0) to convert # Excel date to Python tuple of (year, month, day, hour, minute, second) return data
Example #17
def xls_value_to_unicode(value, value_type, datemode): """ Take a xls formatted value and try to make a unicode string representation. """ if value_type == xlrd.XL_CELL_BOOLEAN: return "TRUE" if value else "FALSE" elif value_type == xlrd.XL_CELL_NUMBER: # Try to display as an int if possible. int_value = int(value) if int_value == value: return unicode(int_value) else: return unicode(value) elif value_type is xlrd.XL_CELL_DATE: # Warn that it is better to single quote as a string. # error_location = cellFormatString % (ss_row_idx, ss_col_idx) # raise Exception( # "Cannot handle excel formatted date at " + error_location) datetime_or_time_only = xlrd.xldate_as_tuple(value, datemode) if datetime_or_time_only[:3] == (0, 0, 0): # must be time only return unicode(datetime.time(*datetime_or_time_only[3:])) return unicode(datetime.datetime(*datetime_or_time_only)) else: # ensure unicode and replace nbsp spaces with normal ones # to avoid this issue: # https://github.com/modilabs/pyxform/issues/83 return unicode(value).replace(unichr(160), " ")
Example #18
def showable_cell_value(celltype, cellvalue, datemode): if celltype == xlrd.XL_CELL_DATE: try: showval = xlrd.xldate_as_tuple(cellvalue, datemode) except xlrd.XLDateError as e: showval = "%s:%s" % (type(e).__name__, e) elif celltype == xlrd.XL_CELL_ERROR: showval = xlrd.error_text_from_code.get( cellvalue, '<Unknown error code 0x%02x>' % cellvalue) else: showval = cellvalue return showval
Example #19
def getRowData(row, columnNames): rowData = {} counter = 0 for cell in row: # check if it is of date type print in iso format if cell.ctype==xlrd.XL_CELL_DATE: rowData[columnNames[counter].lower().replace(' ', '_')] = datetime.datetime(*xlrd.xldate_as_tuple(cell.value,0)).isoformat() else: rowData[columnNames[counter].lower().replace(' ', '_')] = cell.value counter +=1 return rowData Example #20
def _fix_value(cell): """Clean up an Excel value for CSV-like representation.""" if cell.value is None or cell.ctype == xlrd.XL_CELL_EMPTY: return '' elif cell.ctype == xlrd.XL_CELL_NUMBER: # let numbers be integers if possible if float(cell.value).is_integer(): return int(cell.value) else: return cell.value elif cell.ctype == xlrd.XL_CELL_DATE: # dates need to be formatted try: data = xlrd.xldate_as_tuple(cell.value, 0) return '{0[0]:04d}-{0[1]:02d}-{0[2]:02d}'.format(data) except: return cell.value; elif cell.ctype == xlrd.XL_CELL_BOOLEAN: return int(cell.value) else: # XL_CELL_TEXT, or anything else return cell.value Example #21
def _fix_value(cell): """Clean up an Excel value for CSV-like representation.""" if cell.value is None or cell.ctype == xlrd.XL_CELL_EMPTY: return '' elif cell.ctype == xlrd.XL_CELL_NUMBER: # let numbers be integers if possible if float(cell.value).is_integer(): return int(cell.value) else: return cell.value elif cell.ctype == xlrd.XL_CELL_DATE: # dates need to be formatted try: data = xlrd.xldate_as_tuple(cell.value, 0) return '{0[0]:04d}-{0[1]:02d}-{0[2]:02d}'.format(data) except: return cell.value elif cell.ctype == xlrd.XL_CELL_BOOLEAN: return int(cell.value) else: # XL_CELL_TEXT, or anything else return cell.value Example #22
def showable_cell_value(celltype, cellvalue, datemode): if celltype == xlrd.XL_CELL_DATE: try: showval = xlrd.xldate_as_tuple(cellvalue, datemode) except xlrd.XLDateError as e: showval = "%s:%s" % (type(e).__name__, e) elif celltype == xlrd.XL_CELL_ERROR: showval = xlrd.error_text_from_code.get( cellvalue, '<Unknown error code 0x%02x>' % cellvalue) else: showval = cellvalue return showval
Example #23
def xls_to_text(document_path, event, context): import xlrd book = xlrd.open_workbook(document_path) lines = [] for sheetno, sheet in enumerate(book.sheets()): lines.append(sheet.name) lines.append('-------------------------------------------') for row in sheet.get_rows(): row_values = [] for cell in row: if cell.ctype == xlrd.XL_CELL_DATE: try: d = datetime(*xlrd.xldate_as_tuple(cell.value, book.datemode)) except ValueError: d = datetime(1970, 1, 1) row_values.append(d.date() if d.time() == time(0, 0) else d) elif cell.ctype == xlrd.XL_CELL_BOOLEAN: row_values.append(bool(cell.value)) else: row_values.append(cell.value)
Subscribe to:
Post Comments (Atom)
0 Response to "Xlrd Xlrderror String continue Expected Chars Found"
Post a Comment