Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1from __future__ import annotations
3from datetime import time
4import math
5from typing import TYPE_CHECKING
7import numpy as np
9from pandas.compat._optional import import_optional_dependency
11from pandas.io.excel._base import BaseExcelReader
13if TYPE_CHECKING:
14 from xlrd import Book
16 from pandas._typing import (
17 Scalar,
18 StorageOptions,
19 )
22class XlrdReader(BaseExcelReader["Book"]):
23 def __init__(
24 self,
25 filepath_or_buffer,
26 storage_options: StorageOptions | None = None,
27 engine_kwargs: dict | None = None,
28 ) -> None:
29 """
30 Reader using xlrd engine.
32 Parameters
33 ----------
34 filepath_or_buffer : str, path object or Workbook
35 Object to be parsed.
36 storage_options : dict, optional
37 Extra options that make sense for a particular storage connection,
38 e.g. host, port, username, password, etc. For HTTP(S) URLs the
39 key-value pairs are forwarded to ``urllib.request.Request`` as
40 header options. For other URLs (e.g. starting with "s3://", and
41 "gcs://") the key-value pairs are forwarded to ``fsspec.open``.
42 Please see ``fsspec`` and ``urllib`` for more details, and for more
43 examples on storage options refer `here <https://pandas.pydata.org/
44 pandas-docs/stable/user_guide/io.html?
45 highlight=storage_options#reading-writing-remote-files>`__.
46 engine_kwargs : dict, optional
47 Arbitrary keyword arguments passed to excel engine.
48 """
49 err_msg = "Install xlrd >= 2.0.1 for xls Excel support"
50 import_optional_dependency("xlrd", extra=err_msg)
51 super().__init__(
52 filepath_or_buffer,
53 storage_options=storage_options,
54 engine_kwargs=engine_kwargs,
55 )
57 @property
58 def _workbook_class(self) -> type[Book]:
59 from xlrd import Book
61 return Book
63 def load_workbook(self, filepath_or_buffer, engine_kwargs) -> Book:
64 from xlrd import open_workbook
66 if hasattr(filepath_or_buffer, "read"):
67 data = filepath_or_buffer.read()
68 return open_workbook(file_contents=data, **engine_kwargs)
69 else:
70 return open_workbook(filepath_or_buffer, **engine_kwargs)
72 @property
73 def sheet_names(self):
74 return self.book.sheet_names()
76 def get_sheet_by_name(self, name):
77 self.raise_if_bad_sheet_by_name(name)
78 return self.book.sheet_by_name(name)
80 def get_sheet_by_index(self, index):
81 self.raise_if_bad_sheet_by_index(index)
82 return self.book.sheet_by_index(index)
84 def get_sheet_data(
85 self, sheet, file_rows_needed: int | None = None
86 ) -> list[list[Scalar]]:
87 from xlrd import (
88 XL_CELL_BOOLEAN,
89 XL_CELL_DATE,
90 XL_CELL_ERROR,
91 XL_CELL_NUMBER,
92 xldate,
93 )
95 epoch1904 = self.book.datemode
97 def _parse_cell(cell_contents, cell_typ):
98 """
99 converts the contents of the cell into a pandas appropriate object
100 """
101 if cell_typ == XL_CELL_DATE:
102 # Use the newer xlrd datetime handling.
103 try:
104 cell_contents = xldate.xldate_as_datetime(cell_contents, epoch1904)
105 except OverflowError:
106 return cell_contents
108 # Excel doesn't distinguish between dates and time,
109 # so we treat dates on the epoch as times only.
110 # Also, Excel supports 1900 and 1904 epochs.
111 year = (cell_contents.timetuple())[0:3]
112 if (not epoch1904 and year == (1899, 12, 31)) or (
113 epoch1904 and year == (1904, 1, 1)
114 ):
115 cell_contents = time(
116 cell_contents.hour,
117 cell_contents.minute,
118 cell_contents.second,
119 cell_contents.microsecond,
120 )
122 elif cell_typ == XL_CELL_ERROR:
123 cell_contents = np.nan
124 elif cell_typ == XL_CELL_BOOLEAN:
125 cell_contents = bool(cell_contents)
126 elif cell_typ == XL_CELL_NUMBER:
127 # GH5394 - Excel 'numbers' are always floats
128 # it's a minimal perf hit and less surprising
129 if math.isfinite(cell_contents):
130 # GH54564 - don't attempt to convert NaN/Inf
131 val = int(cell_contents)
132 if val == cell_contents:
133 cell_contents = val
134 return cell_contents
136 nrows = sheet.nrows
137 if file_rows_needed is not None:
138 nrows = min(nrows, file_rows_needed)
139 return [
140 [
141 _parse_cell(value, typ)
142 for value, typ in zip(
143 sheet.row_values(i), sheet.row_types(i), strict=True
144 )
145 ]
146 for i in range(nrows)
147 ]