Python xlrd.XL_CELL_DATE Examples

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 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 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 test_converts_excel_dates_to_python_datetime(self, mock_xlrd_date_as_tuple):
        mock_excel_worksheet = Mock()
        def mock_cell(row, col):
            mock_cell = Mock()
            mock_cell.ctype = xlrd.XL_CELL_DATE
            mock_cell.value = (row, col)
            return mock_cell
        mock_excel_worksheet.cell.side_effect = mock_cell
        mock_excel_worksheet.nrows = 4
        mock_excel_worksheet.ncols = 3

        def mock_xlrd_date_as_tuple_function(cell_value, datemode):
            row, col = cell_value
            self.assertEquals(datemode, mock_excel_worksheet.book.datemode)
            return (2011, row, col, 1, 2, 3)
        mock_xlrd_date_as_tuple.side_effect = mock_xlrd_date_as_tuple_function

        worksheet = worksheet_from_excel(mock_excel_worksheet)

        for col in range(mock_excel_worksheet.ncols):
            for row in range(mock_excel_worksheet.nrows):
                self.assertEquals(
                        worksheet[col + 1, row + 1].formula,
                        '=DateTime(2011, %s, %s, 1, 2, 3)' % (row, col)
                ) 

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 

def convert_xsl_to_csv(contents):
    """Converts data in xsl (or xslx) format to CSV."""
    try:
        book = xlrd.open_workbook(file_contents=contents)
    except xlrd.XLRDError as e:
        return None, str(e)
    except UnicodeDecodeError:
        return None, 'The encoding of the file is unknown.'
    if book.nsheets == 0:
        return None, 'The uploaded file contains no sheets.'
    sheet = book.sheet_by_index(0)
    table = []
    for row in xrange(sheet.nrows):
        table_row = []
        for col in xrange(sheet.ncols):
            value = None
            cell_value = sheet.cell_value(row, col)
            cell_type = sheet.cell_type(row, col)
            if cell_type == xlrd.XL_CELL_TEXT:
                value = cell_value
            elif cell_type == xlrd.XL_CELL_NUMBER:
                value = str(int(cell_value))
            elif cell_type == xlrd.XL_CELL_BOOLEAN:
                value = 'true' if cell_value else 'false'
            elif cell_type == xlrd.XL_CELL_DATE:
                # TODO(ryok): support date type.
                pass
            table_row.append(value)
        table.append(table_row)

    csv_output = StringIO.StringIO()
    csv_writer = csv.writer(csv_output)
    csv_writer.writerows(table)
    return csv_output.getvalue(), None 

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 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)
            #end for
            lines.append(' | '.join(map(lambda s: str(s).strip(), row_values)))
        #end for
        lines.append('')  # empty line
    #end for

    return '\n'.join(lines).strip()
#end def


# def ppt_to_text(document_path, event, context):
#     cmdline = [os.path.join(BIN_DIR, 'catppt'), '-dutf-8', document_path]
#     text = _get_subprocess_output(cmdline, shell=False, env=dict(CATDOCRC_PATH=CATDOCRC_PATH))

#     return text.decode('utf-8', errors='ignore').strip()
# #end def 

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 _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 

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 

def __iter_extended_rows(self):

        def type_value(ctype, value):
            """ Detects boolean value, int value, datetime """

            # Boolean
            if ctype == xlrd.XL_CELL_BOOLEAN:
                return bool(value)

            # Excel numbers are only float
            # Float with no decimals can be cast into int
            if ctype == xlrd.XL_CELL_NUMBER and value == value // 1:
                return int(value)

            # Datetime
            if ctype == xlrd.XL_CELL_DATE:
                return xlrd.xldate.xldate_as_datetime(value, self.__book.datemode)

            return value

        for x in range(0, self.__sheet.nrows):
            row_number = x + 1
            row = []
            for y, value in enumerate(self.__sheet.row_values(x)):
                value = type_value(self.__sheet.cell(x, y).ctype, value)
                if self.__fill_merged_cells:
                    for xlo, xhi, ylo, yhi in self.__sheet.merged_cells:
                        if x in range(xlo, xhi) and y in range(ylo, yhi):
                            value = type_value(self.__sheet.cell(xlo, ylo).ctype,
                                               self.__sheet.cell_value(xlo, ylo))
                row.append(value)
            yield (row_number, None, row) 

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 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), " ") 

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 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 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