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 (
6 TYPE_CHECKING,
7 Any,
8)
9import warnings
11import numpy as np
13from pandas.compat._optional import (
14 VERSIONS,
15 import_optional_dependency,
16)
17from pandas.errors import Pandas4Warning
18from pandas.util._exceptions import find_stack_level
20from pandas.io.excel._base import BaseExcelReader
22if TYPE_CHECKING:
23 from xlrd import Book
25 from pandas._typing import (
26 Scalar,
27 StorageOptions,
28 )
31class XlrdReader(BaseExcelReader["Book"]):
32 def __init__(
33 self,
34 filepath_or_buffer,
35 storage_options: StorageOptions | None = None,
36 engine_kwargs: dict[str, Any] | None = None,
37 ) -> None:
38 """
39 Reader using xlrd engine.
41 Parameters
42 ----------
43 filepath_or_buffer : str, path object or Workbook
44 Object to be parsed.
45 storage_options : dict, optional
46 Extra options that make sense for a particular storage connection,
47 e.g. host, port, username, password, etc. For HTTP(S) URLs the
48 key-value pairs are forwarded to ``urllib.request.Request`` as
49 header options. For other URLs (e.g. starting with "s3://", and
50 "gcs://") the key-value pairs are forwarded to ``fsspec.open``.
51 Please see ``fsspec`` and ``urllib`` for more details, and for more
52 examples on storage options refer `here <https://pandas.pydata.org/
53 pandas-docs/stable/user_guide/io.html?
54 highlight=storage_options#reading-writing-remote-files>`__.
55 engine_kwargs : dict, optional
56 Arbitrary keyword arguments passed to excel engine.
57 """
58 err_msg = f"Install xlrd >= {VERSIONS['xlrd']} for xls Excel support"
59 import_optional_dependency("xlrd", extra=err_msg)
60 warnings.warn(
61 "The xlrd engine is deprecated and will be removed in a future version. "
62 "Use the calamine engine instead, by setting engine='calamine'.",
63 Pandas4Warning,
64 stacklevel=find_stack_level(),
65 )
66 super().__init__(
67 filepath_or_buffer,
68 storage_options=storage_options,
69 engine_kwargs=engine_kwargs,
70 )
72 @property
73 def _workbook_class(self) -> type[Book]:
74 from xlrd import Book
76 return Book
78 def load_workbook(self, filepath_or_buffer, engine_kwargs) -> Book:
79 from xlrd import open_workbook
81 if hasattr(filepath_or_buffer, "read"):
82 data = filepath_or_buffer.read()
83 return open_workbook(file_contents=data, **engine_kwargs)
84 else:
85 return open_workbook(filepath_or_buffer, **engine_kwargs)
87 @property
88 def sheet_names(self):
89 return self.book.sheet_names()
91 def get_sheet_by_name(self, name):
92 self.raise_if_bad_sheet_by_name(name)
93 return self.book.sheet_by_name(name)
95 def get_sheet_by_index(self, index):
96 self.raise_if_bad_sheet_by_index(index)
97 return self.book.sheet_by_index(index)
99 def get_sheet_data(
100 self, sheet, file_rows_needed: int | None = None
101 ) -> list[list[Scalar]]:
102 from xlrd import (
103 XL_CELL_BOOLEAN,
104 XL_CELL_DATE,
105 XL_CELL_ERROR,
106 XL_CELL_NUMBER,
107 xldate,
108 )
110 epoch1904 = self.book.datemode
112 def _parse_cell(cell_contents, cell_typ):
113 """
114 converts the contents of the cell into a pandas appropriate object
115 """
116 if cell_typ == XL_CELL_DATE:
117 # Use the newer xlrd datetime handling.
118 try:
119 cell_contents = xldate.xldate_as_datetime(cell_contents, epoch1904)
120 except OverflowError:
121 return cell_contents
123 # Excel doesn't distinguish between dates and time,
124 # so we treat dates on the epoch as times only.
125 # Also, Excel supports 1900 and 1904 epochs.
126 year = (cell_contents.timetuple())[0:3]
127 if (not epoch1904 and year == (1899, 12, 31)) or (
128 epoch1904 and year == (1904, 1, 1)
129 ):
130 cell_contents = time(
131 cell_contents.hour,
132 cell_contents.minute,
133 cell_contents.second,
134 cell_contents.microsecond,
135 )
137 elif cell_typ == XL_CELL_ERROR:
138 cell_contents = np.nan
139 elif cell_typ == XL_CELL_BOOLEAN:
140 cell_contents = bool(cell_contents)
141 elif cell_typ == XL_CELL_NUMBER:
142 # GH5394 - Excel 'numbers' are always floats
143 # it's a minimal perf hit and less surprising
144 if math.isfinite(cell_contents):
145 # GH54564 - don't attempt to convert NaN/Inf
146 val = int(cell_contents)
147 if val == cell_contents:
148 cell_contents = val
149 return cell_contents
151 nrows = sheet.nrows
152 if file_rows_needed is not None:
153 nrows = min(nrows, file_rows_needed)
154 return [
155 [
156 _parse_cell(value, typ)
157 for value, typ in zip(
158 sheet.row_values(i), sheet.row_types(i), strict=True
159 )
160 ]
161 for i in range(nrows)
162 ]