Python xlrd.XL_CELL_NUMBER Examples

def test_has_non_empty_cells():
    row = []
    reader = XLSReader()
    assert has_non_empty_cells(reader, []) is False
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_BLANK, ''))
    assert has_non_empty_cells(reader, row) is False
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_TEXT, u'x1'))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_TEXT, u'input with space'))
    assert has_non_empty_cells(reader, row) is True
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 10))
    assert has_non_empty_cells(reader, row) is True
    row = []
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 10))
    assert has_non_empty_cells(reader, row) is True 

def test_extract_coefficients():
    row = []
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_TEXT, u'dmu1'))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 10))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 25))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 45))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 0))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 7.9))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    reader = XLSReader()
    (dmu, coefficients, dmu_name) = extract_coefficients(
        reader, row, [4, 5, 7, 8, 10])
    assert dmu == 'dmu1'
    assert coefficients == [10, 25, 45, 0, 7.9] 

def test_extract_coefficients_with_numeric_dmu():
    row = []
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 0))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 10))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 25))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 45))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 0))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 7.9))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_TEXT, u' aha '))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    reader = XLSReader()
    (dmu, coefficients, dmu_name) = extract_coefficients(
        reader, row, [3, 4, 5, 6, 7, 8, 9])
    assert dmu == 0
    assert coefficients == [10, 25, 45, 0, 7.9, '', 'aha'] 

def test_xls_value_to_unicode(self):
        """
        Test external choices sheet with numeric values is processed successfully.

        The test ensures that the integer values within the external choices sheet
        are returned as they were initially received.
        """
        value = 32.0
        value_type = xlrd.XL_CELL_NUMBER
        datemode = 1
        csv_data = xls_value_to_unicode(value, value_type, datemode)
        expected_output = "32"
        self.assertEqual(csv_data, expected_output)

        # Test that the decimal value is not changed during conversion.
        value = 46.9
        csv_data = xls_value_to_unicode(value, value_type, datemode)
        expected_output = "46.9"
        self.assertEqual(csv_data, expected_output) 

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_extract_numeric_categories():
    row = []
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 5))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, 15))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_NUMBER, -100))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_TEXT, u'x1'))
    row.append(xlrd.sheet.Cell(xlrd.XL_CELL_EMPTY, ''))
    reader = XLSReader()
    categories, indexes = extract_categories(reader, row)
    assert categories == [5, 15, -100, 'x1'] 

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 cells_all_junk(cells, is_rubbish=None):
    """\
    Return True if all cells in the sequence are junk.
    What qualifies as junk:
    -- empty cell
    -- blank cell
    -- zero-length text
    -- text is all whitespace
    -- number cell and is 0.0
    -- text cell and is_rubbish(cell.value) returns True.
    """
    for cell in cells:
        if cell.ctype in null_cell_types:
            continue
        if cell.ctype == XL_CELL_TEXT:
            if not cell.value:
                continue
            if cell.value.isspace():
                continue
        if cell.ctype == XL_CELL_NUMBER:
            if not cell.value:
                continue
        if is_rubbish is not None and is_rubbish(cell):
            continue
        return False
    return True 

def test_cell_type(self):
        r = TestReader(('Sheet1',(((XL_CELL_NUMBER,0.0),),)))
        book = tuple(r.get_workbooks())[0][0]
        cell = book.sheet_by_index(0).cell(0,0)
        self.assertEqual(cell.value,0.0)
        self.assertEqual(cell.ctype,XL_CELL_NUMBER) 

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