我们从Python开源项目中，提取了以下26个代码示例，用于说明如何使用xlrd.xldate_as_tuple()。
def excel_to_csv(path): wb = xlrd.open_workbook(path) sh = wb.sheet_by_index(0) csv_path = convert_path_to_csv(path) csv_file = open(csv_path, 'wb') wr = csv.writer(csv_file) date_tuple = xlrd.xldate_as_tuple(sh.row_values(0)[-1], wb.datemode) date = datetime(*date_tuple).strftime('%m/%d/%Y') date_fields = [date for i in range(sh.nrows-1)] date_fields = ['Date'] + date_fields for rownum in xrange(sh.nrows): if rownum == 0: wr.writerow([date_fields[rownum]] + sh.row_values(rownum)[:-1] + ['Value']) else: wr.writerow([date_fields[rownum]] + sh.row_values(rownum)) csv_file.close()
def format_excelval(book, type, value, wanttupledate): """ Clean up the incoming excel data """ ## Data Type Codes: ## EMPTY 0 ## TEXT 1 a Unicode string ## NUMBER 2 float ## DATE 3 float ## BOOLEAN 4 int; 1 means TRUE, 0 means FALSE ## ERROR 5 returnrow = [] if type == 2: # TEXT if value == int(value): value = int(value) elif type == 3: # NUMBER datetuple = xlrd.xldate_as_tuple(value, book.datemode) value = datetuple if wanttupledate else tupledate_to_isodate(datetuple) elif type == 5: # ERROR value = xlrd.error_text_from_code[value] return value # # Save to CSV #
def importData(fileName): """ Main entry point - opens import data Excel sheet (assumed to be first sheet) and processes each record, one at a time. """ global importSheet, importRow, headers book = xlrd.open_workbook(fileName) importSheet = book.sheet_by_index(0) print "importing from %s" % importSheet.name # get cell value types from first-non header data row (2nd), since headers are all of type text for colnum in range(0, importSheet.ncols): headers.append((importSheet.cell(0, colnum).value, importSheet.cell(1, colnum).ctype)) configErrorReporting(headers) for importRow in range(1, importSheet.nrows): record = {} for colnum in range(0, importSheet.ncols): if headers[colnum][1] == xlrd.XL_CELL_DATE: dateTuple = xlrd.xldate_as_tuple(importSheet.cell(rowx=importRow, colx=colnum).value, book.datemode) date = datetime.date(dateTuple[0], dateTuple[1], dateTuple[2]) # required format for xsd:Date type, for web service call record[headers[colnum][0]] = date.isoformat() else: value = importSheet.cell_value(rowx=importRow, colx=colnum) if isinstance(value, basestring): record[headers[colnum][0]] = value.strip() else: record[headers[colnum][0]] = value #print "%s: %s" % (type(record[headers[colnum]]), record[headers[colnum]]) record = handleAccountContact(record) book.unload_sheet(importSheet.name)
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 stringify(value)
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
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)
def fucn_time(row_index): col_index = 1 value = sheet.cell(row_index, col_index).value while not value: row_index -= 1 value = sheet.cell(row_index, col_index).value if float(value) < 1: value = xldate_as_tuple(value, 1) time = str(value[3]) + "." + str(value[4]) else: time = value return time
def parse_date_value(self, value): return xlrd.xldate_as_tuple(value, 0)
def _load_index_constituent(self, file_path, index_code): self._create_constituent_table(index_code) excel = xlrd.open_workbook(file_path) sheet = excel.sheets()[0] current_date = '' current_constituent = [] # ?????????,????,?????????? row_indent = 1 if sheet.cell(sheet.nrows-row_indent, 0).value == u'?????????Choice??': row_indent = 6 for row in range(1, sheet.nrows-row_indent): # ??,??????????????? row_date = sheet.cell(row, 1).value if isinstance(row_date, float): row_date = datetime(*xlrd.xldate_as_tuple(row_date, excel.datemode)).strftime(STAND_DATE_FORMAT) #?1????????,?????????????????? if row == 1: current_date = row_date # ??????,??????????????? # ???,????????????,???????????,????????,??????????????????? # if row_date != current_date or row == sheet.nrows-7: if self._in_index_change_range(row_date, current_date) or row == sheet.nrows-7: # sql = u'INSERT OR REPLACE INTO {} ({}, {}) VALUES ({}, "{}");'.format( # IndexCollector._constituent_tablename(index_code), IndexConstituent.DATE_KEY, IndexConstituent.CONSTITUENTS_KEY, current_date, ','.join(current_constituent)) # self.db.execute(sql) #???????,????date????????????,?????????? self.db.execute(u'INSERT OR REPLACE INTO '+ IndexCollector._constituent_tablename(index_code) +' VALUES (?, ?);', (current_date, ','.join(current_constituent))) self.db.commit() current_date = row_date stock_code = sheet.cell(row, 2).value.split('.')[0] # ????,?????????b?,?????????????b??????,????????? if stock_code.startswith('2'): continue operation = sheet.cell(row, 4).value if operation == u'??': current_constituent.append(stock_code) else: current_constituent.remove(stock_code) # ?????????,????????????,???????????? # ????????????????,??????????????
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
def showable_cell_value(celltype, cellvalue, datemode): if celltype == xlrd.XL_CELL_DATE: try: showval = xlrd.xldate_as_tuple(cellvalue, datemode) except xlrd.XLDateError: e1, e2 = sys.exc_info()[:2] showval = "%s:%s" % (e1.__name__, e2) 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
def make_excel_date_caster(file_name): """Make a date caster function that can convert dates from a particular workbook. This is required because dates in Excel workbooks are stupid. """ from xlrd import open_workbook wb = open_workbook(file_name) datemode = wb.datemode def excel_date(v): from xlrd import xldate_as_tuple import datetime try: year, month, day, hour, minute, second = xldate_as_tuple(float(v), datemode) return datetime.date(year, month, day) except ValueError: # Could be actually a string, not a float. Because Excel dates are completely broken. from dateutil import parser try: return parser.parse(v).date() except ValueError: return None return excel_date
def cell_value(cell, datemode): ctype = cell.ctype value = cell.value if ctype == xlrd.XL_CELL_ERROR: raise ValueError(repr(cell), 'cell error') elif ctype == xlrd.XL_CELL_BOOLEAN: return str(value).upper() elif ctype == xlrd.XL_CELL_NUMBER: if value.is_integer(): value = int(value) return str(value) elif ctype == xlrd.XL_CELL_DATE: value = xlrd.xldate_as_tuple(value, datemode) if value[3:] == (0, 0, 0): return datetime.date(*value[:3]).isoformat() else: return datetime.datetime(*value).isoformat() elif ctype in (xlrd.XL_CELL_TEXT, xlrd.XL_CELL_EMPTY, xlrd.XL_CELL_BLANK): return value raise ValueError(repr(cell), 'unknown cell type')
def excel2lines(file_name,sheet_indexes,header = False,annotation=False,date=False): lines = [] num_annotators = False workbook = xlrd.open_workbook(file_name) #collect sheets sheets = [] print(sheet_indexes) for index in sheet_indexes: print(index) sheets.append(workbook.sheet_by_index(int(index))) else: sheets = workbook.sheets() #for each sheet for sheet in sheets: sheetlines = [] if header: first_row = 1 else: first_row = 0 print("gen_functions",sheet.nrows) last_row = sheet.nrows #iterate the lines for rownum in range(first_row,last_row): values = [] #collect annotation values if annotation: for value in sheet.row_values(rownum): if not type(value) == float: value = value.strip() try: if float(value) in range(2): values.append(float(value)) except ValueError: continue if num_annotators: if len(values) != num_annotators: print("number of annotation values on line", rownum,"is not consistent; check the inputfile. Exiting...") exit() else: num_annotators = len(values) print(num_annotators, "annotators") else: rowvals = sheet.row_values(rownum) if date: try: rowvals[date] = datetime.date(*xlrd.xldate_as_tuple(\ sheet.cell_value(rownum,date), workbook.datemode)[:3]) except: continue values = [unicode(x) for x in rowvals] sheetlines.append(values) #each sheet is a list of lists lines.append(sheetlines) return lines
def convert_excel_to_csv(input_excel_file, output_csv_file_path=None, return_csv_file=False): """ Takes the excel file path as input and converts into csv file and if we select return_csv_file as True returns csv file else returns csv file object Arguments: 1. input_excel_file: It is a excel file path which is to be converted into csv file 2. output_csv_file_path: If user gives the output csv path, then creating csv file at that path else creating a csv file in the directory from where he have given excel file. 3. return_csv_file: If the user selects return_csv_file as True, returning the output csv file else returning the object. Returns: Returns the csv file path if user selects return_csv_file as True else returns the object. """ try: if output_csv_file_path is None: if ".xlsx" in input_excel_file: ret_csv_file = input_excel_file.replace(".xlsx", ".csv") else: ret_csv_file = input_excel_file.replace(".xls", ".csv") else: ret_csv_file = output_csv_file_path wb = xlrd.open_workbook(input_excel_file) sh = wb.sheet_by_index(0) csv_file = open(ret_csv_file, 'wb+') wr = csv.writer(csv_file, quoting=csv.QUOTE_ALL) for rownum in xrange(sh.nrows): row_val = sh.row_values(rownum) for index, value in enumerate(row_val): if sh.cell(rownum, index).ctype == 3: year, month, day, hour, minute, sec = xlrd.xldate_as_tuple( value, wb.datemode) date_format = "%02d/%02d/%04d" % (month, day, year) row_val[index] = date_format wr.writerow(row_val) if return_csv_file: csv_file.close() csv_file = ret_csv_file else: csv_file = csv_file except Exception as exception: print_exception(exception) csv_file = None return csv_file
def load_stock_history_quotation(self, stock_codes): #???????? for stock_code in stock_codes: self._create_stock_quotation_table(stock_code) history_files = [] for root, _, files in os.walk('./stock_history/'): for f in files: if f.startswith(u'????'): history_files.append(root + f) #??????,???????? history_files.reverse() for history_file in history_files: excel = xlrd.open_workbook(history_file) #??????? sheet = excel.sheets()[0] # codes_line = sheet.row_values(2) #??????,??????????,??????,????? quotations = dict() for col in range(1, sheet.ncols, 2): code = sheet.cell(1, col).value.split('.')[0] #????????choice?code??? ss = [] if code in stock_codes: #???????????????????1?,2????,3???,???????????,?????,??????,??? #?????,????????,???????,?????date???,???????? for row in range(sheet.nrows-3, 3, -1): #??????,????? date_data = sheet.cell(row, 0).value if isinstance(date_data, basestring) and len(date_data) == 0: break pb = sheet.cell(row, col).value pe = sheet.cell(row, col+1).value #pb,pe???,??????,?????????,?????????,?????,?????? #?????????,???????????????,?????,???????output?? if (isinstance(pb, basestring) and len(pb) == 0) or (isinstance(pe, basestring) and len(pe) == 0): break else: date_tuple = xlrd.xldate_as_tuple(date_data, 0) date = '{}-{:0>2}-{:0>2}'.format(date_tuple[0], date_tuple[1], date_tuple[2]) ss.append((date, safe_to_float(pe), safe_to_float(pb))) else: print 'code ' + code + " not in eastmoney" if len(ss) > 0: quotations[code] = ss self._batch_update_stock_history_quotation(quotations) print 'load history file finish ' + history_file #??????
def parse_file(datafile=datafile): workbook = xlrd.open_workbook(datafile) sheet= workbook.sheet_by_index(0) title_col = 1 type_col = 2 link_col = 3 date_col = 4 events = [] case = -1 agree = '' data = [[sheet.cell_value(r,col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] for row in range(int(startRow)-1,sheet.nrows): title = sheet.cell_value(row,1) etype = sheet.cell_value(row,2) link = sheet.cell_value(row,3) date = datetime.datetime(*xlrd.xldate_as_tuple(sheet.cell_value(row,4),workbook.datemode)) dateString = date.strftime("%a, %d %b") #date=sheet.cell_value(row,4) dict = {'title':title,'type':etype,'link':link,'date':dateString} events.append(dict) print "New event appended" print " Event name: %s \n Type : %s \t link : %s \n date : %r" %(dict['title'],dict['type'],dict['link'],dict['date']) print "Do you want to view the linnk? y/n" agree = raw_input("--> ") if agree=='y'or agree=='Y': if (not webbrowser.open(dict['link'],new=new)): print "Failed to open" case = int(raw_input("1 -- pass \t 2 -- reject \t 0 -- quit \n --> ")) if case==1: pass if case==2: del events[-1] print "Event removed" if case==0: break print "Finished all the events" print "Appending to the file" file = open("data.txt",'w') file.write(str(events)) file.close() return events
