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)