def xl_date(cell): try: date_tuple = xlrd3.xldate_as_tuple(cell.value, wbk.datemode) return '{:4d}-{:02d}-{:02d}'.format(date_tuple[0], date_tuple[1], date_tuple[2]) except: return None
def get_row_data(bk, sh, rowx, colrange): result = [] dmode = bk.date_mode 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 xls2csv(self, excel_filename, csv_filename): try: book = xlrd3.open_workbook(excel_filename) print("The number of worksheets: %d" % book.nsheets) sheet_name = book.sheet_names()[0].split(' ')[0] #The first sheet sh = book.sheet_by_index(0) print("Sheet name:", sh.name, " | Number of rows:", sh.nrows, " | Number of columns:", sh.ncols) csv_file = open(csv_filename, 'w') w = csv.writer(csv_file, csv.excel) for row in range(sh.nrows): cell_types = sh.row_types(row) this_row = [] for col in range(sh.ncols): if cell_types[col] == xlrd3.XL_CELL_DATE: cell_val = datetime.datetime(*xlrd3.xldate_as_tuple( sh.cell_value(row, col), book.datemode)) else: cell_val = str(sh.cell_value(row, col)).encode('utf8') this_row.append(cell_val) if row == 0 or (row > 0 and this_row[1].isdigit()): w.writerow(this_row) print("%s has been created" % csv_filename) except IOError: print("IOError: Please ensure %s exists" % excel_filename)
def _parseCell(self, sheet, cell, colIndex): cellName = (str(sheet.cell_value(self.nameRowIndex, colIndex))).strip() cellType = cell.ctype cellValue = cell.value if cellType == xlrd.XL_CELL_NUMBER: intValue = int(cellValue) if cellValue == intValue: cellValue = intValue elif cellType == xlrd.XL_CELL_DATE: timeTuple = xlrd.xldate_as_tuple(cellValue, self._xls.datemode) cellValue = self._toTimeStr(timeTuple) return (cellName, (str(cellValue)).strip())
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 xls2csv(self, excel_filename,csv_filename): try: book = xlrd3.open_workbook(excel_filename) print("The number of worksheets: %d" % book.nsheets) sheet_name = book.sheet_names()[0].split(' ')[0] #The first sheet sh = book.sheet_by_index(0) print("Sheet name:", sh.name, " | Number of rows:", sh.nrows, " | Number of columns:", sh.ncols) csv_file = open(csv_filename,'w') w = csv.writer(csv_file, csv.excel) for row in range(sh.nrows): cell_types = sh.row_types(row) this_row = [] for col in range(sh.ncols): if cell_types[col] == xlrd3.XL_CELL_DATE: cell_val = datetime.datetime(*xlrd3.xldate_as_tuple(sh.cell_value(row,col), book.datemode)) else: cell_val = str(sh.cell_value(row,col)).encode('utf8') this_row.append(cell_val) if row == 0 or (row > 0 and this_row[1].isdigit()): w.writerow(this_row) print("%s has been created" % csv_filename) except IOError: print("IOError: Please ensure %s exists" % excel_filename)