Python xlrd.XL_CELL_BOOLEAN 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 test_no_error_on_bools(self,h):
        r = TestReader(
            ('Sheet',[[(XL_CELL_BOOLEAN,True)]]),
            )
        # fire methods on filter
        f = ErrorFilter()
        c = Mock()
        process(r,f,c)
        compare(c.method_calls,[
            ('start', (), {}),
            ('workbook', (C('xlrd.Book'), 'test.xls'),{}),
            ('sheet', (C('xlrd.sheet.Sheet',name='Sheet',strict=False), u'Sheet'),{}),
            ('row', (0, 0),{}),
            ('cell', (0, 0, 0, 0),{}),
            ('finish', (), {})
            ])
        self.assertEqual(len(h.records),0) 

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 __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 _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 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 test_copy_boolean_cells(self):
        r = TestReader(
            ('Bools',([[(XL_CELL_BOOLEAN,True)]]),),
            )
        w = TestWriter()
        r(w)
        self.assertEqual(w.files.keys(),['test.xls'])
        a = open_workbook(file_contents=w.files['test.xls'].file.read())
        cell = a.sheet_by_index(0).cell(0,0)
        self.assertEqual(cell.ctype,XL_CELL_BOOLEAN)
        self.assertEqual(cell.value,True) 

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