我们从Python开源项目中，提取了以下21个代码示例，用于说明如何使用xlrd.XL_CELL_DATE。
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 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 configErrorReporting(headers): """ Configure import exception log, which is an Excel spreadsheet in the same format as the input format, but with an extra column added - "Error", which contains the error message. Can only be called after first row of input Excel spreadsheet is read to initialize the global, "headers" """ dateFmt = easyxf( 'font: name Arial, bold True, height 200;', #'borders: left thick, right thick, top thick, bottom thick;', num_format_str='MM-DD-YYYY' ) headerFmt = easyxf( 'font: name Arial, bold True, height 200;', ) global errorsWorkbook, erroutSheet, erroutRow errorsWorkbook = Workbook() erroutSheet = errorsWorkbook.add_sheet('Import Errors') for colnum in range(0, len(headers)): erroutSheet.write(0, colnum, headers[colnum][0], tern(headers[colnum][0]==xlrd.XL_CELL_DATE, dateFmt, headerFmt)) # Add extra column for error message erroutSheet.write(0, len(headers), "Error", headerFmt) erroutSheet.flush_row_data() erroutRow = 1 errorsWorkbook.save('errors.xls')
def writeErrorRec(record): """ Exception report spreadsheet output format same as input format and assumes first column is of type Date - adds an extra, last column for error message. """ dateFmt = XFStyle() dateFmt.num_format_str='MM-DD-YYYY' defaultFmt = XFStyle() global erroutRow for colnum in range(0, len(headers)): erroutSheet.write(erroutRow, colnum, record[headers[colnum][0]], tern(headers[colnum][0]==xlrd.XL_CELL_DATE, dateFmt, defaultFmt)) # add extra column for error message erroutSheet.row(erroutRow).write(len(headers), record['Error']) erroutRow += 1 # flush every 200 rows... #if ((erroutRow % 200) + 1 == 200): # since, xlwt uses aggressive caching, we must # write each row manually, or the data may be lost upon exception erroutSheet.flush_row_data() errorsWorkbook.save('errors.xls')
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 _get_cell_raw(self, cell): if cell.ctype == xlrd.XL_CELL_TEXT: return cell.value; if cell.ctype == xlrd.XL_CELL_NUMBER: number = int(cell.value); return u"%d" % number if number == cell.value else u"%g" % cell.value; if cell.ctype == xlrd.XL_CELL_DATE: dt = xlrd.xldate.xldate_as_datetime(cell.value, self._workbook.datemode); return u"%s" % dt; if cell.ctype == xlrd.XL_CELL_BOOLEAN: return u"true" if cell.value else u"false"; return u"";
def _get_cell_string(self, cell): cell_text = ""; if cell.ctype == xlrd.XL_CELL_TEXT: cell_text = cell.value; if cell.ctype == xlrd.XL_CELL_NUMBER: number = int(cell.value); cell_text = u"%d" % number if number == cell.value else u"%g" % cell.value; if cell.ctype == xlrd.XL_CELL_DATE: dt = xlrd.xldate.xldate_as_datetime(cell.value, self._workbook.datemode); cell_text = u"%s" % dt; if cell.ctype == xlrd.XL_CELL_BOOLEAN: cell_text = u"true" if cell.value else u"false"; return u'"%s"' % cell_text;
def _get_cell_number(self, cell): if cell.ctype == xlrd.XL_CELL_TEXT: #?????????????,?????,??????,????????... return cell.value; if cell.ctype == xlrd.XL_CELL_NUMBER: number = int(cell.value); return u"%d" % number if number == cell.value else u"%g" % cell.value; if cell.ctype == xlrd.XL_CELL_DATE: dt = xlrd.xldate.xldate_as_datetime(cell.value, self._workbook.datemode); return u"%d" % time.mktime(dt.timetuple()); if cell.ctype == xlrd.XL_CELL_BOOLEAN: return u"1" if cell.value else u"0"; return u"0";
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 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 cell(self,rdrowx,rdcolx,wtrowx,wtcolx): cell = self.rdsheet.cell(rdrowx,rdcolx) # setup column attributes if not already set if wtcolx not in self.wtcols and rdcolx in self.rdsheet.colinfo_map: rdcol = self.rdsheet.colinfo_map[rdcolx] wtcol = self.wtsheet.col(wtcolx) wtcol.width = rdcol.width wtcol.set_style(self.style_list[rdcol.xf_index]) wtcol.hidden = rdcol.hidden wtcol.level = rdcol.outline_level wtcol.collapsed = rdcol.collapsed self.wtcols.add(wtcolx) # copy cell cty = cell.ctype if cty == xlrd.XL_CELL_EMPTY: return if cell.xf_index is not None: style = self.style_list[cell.xf_index] else: style = default_style rdcoords2d = (rdrowx, rdcolx) if rdcoords2d in self.merged_cell_top_left_map: # The cell is the governing cell of a group of # merged cells. rlo, rhi, clo, chi = self.merged_cell_top_left_map[rdcoords2d] assert (rlo, clo) == rdcoords2d self.wtsheet.write_merge( wtrowx, wtrowx + rhi - rlo - 1, wtcolx, wtcolx + chi - clo - 1, cell.value, style) return if rdcoords2d in self.merged_cell_already_set: # The cell is in a group of merged cells. # It has been handled by the write_merge() call above. # We avoid writing a record again because: # (1) It's a waste of CPU time and disk space. # (2) xlwt does not (as at 2007-01-12) ensure that only # the last record is written to the file. # (3) If you write a data record for a cell # followed by a blank record for the same cell, # Excel will display a blank but OOo Calc and # Gnumeric will display the data :-( return wtrow = self.wtsheet.row(wtrowx) if cty == xlrd.XL_CELL_TEXT: wtrow.set_cell_text(wtcolx, cell.value, style) elif cty == xlrd.XL_CELL_NUMBER or cty == xlrd.XL_CELL_DATE: wtrow.set_cell_number(wtcolx, cell.value, style) elif cty == xlrd.XL_CELL_BLANK: wtrow.set_cell_blank(wtcolx, style) elif cty == xlrd.XL_CELL_BOOLEAN: wtrow.set_cell_boolean(wtcolx, cell.value, style) elif cty == xlrd.XL_CELL_ERROR: wtrow.set_cell_error(wtcolx, cell.value, style) else: raise Exception( "Unknown xlrd cell type %r with value %r at (shx=%r,rowx=%r,colx=%r)" \ % (cty, value, sheetx, rowx, colx) )
