How to use xlrd - 10 common examples

secs += 1.0
						fract = 0.0
					elif fract <= 0.00001:
						fract = 0.0
					
					secs = int(secs)
					if fract:
						value = '{:02d}:{:02d}:{:02d}.{}'.format(
							secs // (60*60), (secs // 60) % 60, secs % 60,
							'{:.20f}'.format(fract)[2:],
						)
					else:
						value = '{:02d}:{:02d}:{:02d}'.format(secs // (60*60), (secs // 60) % 60, secs % 60)
				else:
					try:
						datetuple = xlrd.xldate_as_tuple(value, self.book.datemode)
						validDate = True
					except:
						value = 'UnreadableDate'
						validDate = False
					if validDate:
						if date_as_tuple:
							value = datetuple
						else:
							# time only - no date component
							if datetuple[0] == 0 and datetuple[1] == 0 and  datetuple[2] == 0:
								value = "%02d:%02d:%02d" % datetuple[3:]
							# date only, no time
							elif datetuple[3] == 0 and datetuple[4] == 0 and datetuple[5] == 0:
								value = "%04d/%02d/%02d" % datetuple[:3]
							else: # full date
								value = "%04d/%02d/%02d %02d:%02d:%02d" % datetuple

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

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

dbfile.close()

    # xls or xslx
    elif 'xls' in extension:

      ALERT("database file detected as xls or xlsx based on extension", ALERT.NORMAL)

      try:
        import xlrd
      except ImportError as e:
        ALERT("please install and upgrade the python-xlrd library", ALERT.BAD)
        exit(1)

      # open the xls file
      try:
        wb = xlrd.open_workbook(ARGS.database)
      except IOError as e:
        ALERT("no such file or directory '%s'. ensure you have the correct database file passed in --database/-d" % ARGS.database, ALERT.BAD)
        exit(1)
      sh = wb.sheet_by_name('Bulletin Search')

      # read the spreadsheet into a temp file
      f = NamedTemporaryFile(mode='wb')
      wr = csv.writer(f, quoting=csv.QUOTE_NONE, delimiter=',')

      data = ''

      # loop through xls
      for rownum in xrange(sh.nrows):

        values = sh.row_values(rownum)

def decodeExcel(data, worksheet, row=None, column=None):
    """
    Decode excel file
    """
    content = None
    
    # old file format
    try:
        wb = xlrd.open_workbook(file_contents=data)
    except Exception as e:
        raise TestPropertiesException('ERR_PRO_023: excel data not supported')

    # read sheet
    try:
        ws = wb.sheet_by_name(worksheet)
    except Exception as e:
        wb.release_resources()
        raise TestPropertiesException('ERR_PRO_024: worksheet %s does not exists on excel file' % worksheet)
    
    # read cell 
    if row is not None and column is not None:
        col = ws.col_values( int(column) )
        content = col[ int(row) ]
        
    # read column

def __init__(self, path, worksheet=None, in_memory=False):
        """Initialize self."""
        try:
            import xlrd
        except ImportError:
            raise ImportError(
                "No module named 'xlrd'\n"
                "\n"
                "This is an optional data source that requires the "
                "third-party library 'xlrd'."
            )

        self._file_repr = repr(path)

        # Open Excel file and get worksheet.
        book = xlrd.open_workbook(path, on_demand=True)
        if worksheet:
            sheet = book.sheet_by_name(worksheet)
        else:
            sheet = book.sheet_by_index(0)

        # Build SQLite table from records, release resources.
        iterrows = (sheet.row(i) for i in range(sheet.nrows))
        iterrows = ([x.value for x in row] for row in iterrows)
        columns = next(iterrows)  # <- Get header row.
        connection, table = _load_temp_sqlite_table(columns, iterrows)
        book.release_resources()

        # Calling super() with older convention to support Python 2.7 & 2.6.
        super(ExcelSource, self).__init__(connection, table)

def __init__(self, excelPath, sheetName):
        self.data = xlrd.open_workbook(excelPath)
        self.table = self.data.sheet_by_name(sheetName)
        # 获取第一行作为key值
        self.keys = self.table.row_values(0)
        # 获取总行数
        self.rowNum = self.table.nrows
        # 获取总列数
        self.colNum = self.table.ncols

def test_number_cell(self):
        cell = sheet.cell(1, 1)
        self.assertEqual(cell.ctype, xlrd.XL_CELL_NUMBER)
        self.assertEqual(cell.value, 100)
        self.assertTrue(cell.has_xf)

# Random date/times in Excel's 0-9999.9999+ range.
            ('1982-08-25T00:15:20.213', 30188.010650613425),
            ('2065-04-19T00:16:48.290', 60376.011670023145),
            ('3222-06-11T03:08:08.251', 483014.13065105322),
            ('4379-08-03T06:14:48.580', 905652.26028449077),
            ('5949-12-30T12:59:54.263', 1479232.5416002662),

            # End of Excel's date range.
            ('9999-12-31T23:59:59.000', 2958465.999988426),
        ]

        # Convert the Excel date strings to datetime objects and compare
        # against the dateitme return value of xldate.xldate_as_datetime().
        for excel_date in excel_dates:
            exp = datetime.strptime(excel_date[0], "%Y-%m-%dT%H:%M:%S.%f")
            got = xldate.xldate_as_datetime(excel_date[1], not_1904)

            self.assertEqual(got, exp)

def topology_import(self, file):
        book = open_workbook(file_contents=file.read())
        status = "Topology successfully imported."
        for obj_type in ("device", "link"):
            try:
                sheet = book.sheet_by_name(obj_type)
            except XLRDError:
                continue
            properties = sheet.row_values(0)
            for row_index in range(1, sheet.nrows):
                values = {"dont_update_pools": True}
                for index, property in enumerate(properties):
                    func = field_conversion[property_types[property]]
                    values[property] = func(sheet.row_values(row_index)[index])
                try:
                    factory(obj_type, **values).serialized
                except Exception as e:
                    info(f"{str(values)} could not be imported ({str(e)})")
                    status = "Partial import (see logs)."
            Session.commit()
        for pool in fetch_all("pool"):
            pool.compute_pool()
        self.log("info", status)