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
1# pyright: reportMissingImports=false
2from __future__ import annotations
4from typing import TYPE_CHECKING
6from pandas.compat._optional import import_optional_dependency
8from pandas.io.excel._base import BaseExcelReader
10if TYPE_CHECKING:
11 from pyxlsb import Workbook
13 from pandas._typing import (
14 FilePath,
15 ReadBuffer,
16 Scalar,
17 StorageOptions,
18 )
21class PyxlsbReader(BaseExcelReader["Workbook"]):
22 def __init__(
23 self,
24 filepath_or_buffer: FilePath | ReadBuffer[bytes],
25 storage_options: StorageOptions | None = None,
26 engine_kwargs: dict | None = None,
27 ) -> None:
28 """
29 Reader using pyxlsb engine.
31 Parameters
32 ----------
33 filepath_or_buffer : str, path object, or Workbook
34 Object to be parsed.
35 storage_options : dict, optional
36 Extra options that make sense for a particular storage connection, e.g.
37 host, port, username, password, etc. For HTTP(S) URLs the key-value pairs
38 are forwarded to ``urllib.request.Request`` as header options. For other
39 URLs (e.g. starting with "s3://", and "gcs://") the key-value pairs are
40 forwarded to ``fsspec.open``. Please see ``fsspec`` and ``urllib`` for more
41 details, and for more examples on storage options refer `here
42 <https://pandas.pydata.org/docs/user_guide/io.html?
43 highlight=storage_options#reading-writing-remote-files>`_.
44 engine_kwargs : dict, optional
45 Arbitrary keyword arguments passed to excel engine.
46 """
47 import_optional_dependency("pyxlsb")
48 # This will call load_workbook on the filepath or buffer
49 # And set the result to the book-attribute
50 super().__init__(
51 filepath_or_buffer,
52 storage_options=storage_options,
53 engine_kwargs=engine_kwargs,
54 )
56 @property
57 def _workbook_class(self) -> type[Workbook]:
58 from pyxlsb import Workbook
60 return Workbook
62 def load_workbook(
63 self, filepath_or_buffer: FilePath | ReadBuffer[bytes], engine_kwargs
64 ) -> Workbook:
65 from pyxlsb import open_workbook
67 # TODO: hack in buffer capability
68 # This might need some modifications to the Pyxlsb library
69 # Actual work for opening it is in xlsbpackage.py, line 20-ish
71 return open_workbook(filepath_or_buffer, **engine_kwargs)
73 @property
74 def sheet_names(self) -> list[str]:
75 return self.book.sheets
77 def get_sheet_by_name(self, name: str):
78 self.raise_if_bad_sheet_by_name(name)
79 return self.book.get_sheet(name)
81 def get_sheet_by_index(self, index: int):
82 self.raise_if_bad_sheet_by_index(index)
83 # pyxlsb sheets are indexed from 1 onwards
84 # There's a fix for this in the source, but the pypi package doesn't have it
85 return self.book.get_sheet(index + 1)
87 def _convert_cell(self, cell) -> Scalar:
88 # TODO: there is no way to distinguish between floats and datetimes in pyxlsb
89 # This means that there is no way to read datetime types from an xlsb file yet
90 if cell.v is None:
91 return "" # Prevents non-named columns from not showing up as Unnamed: i
92 if isinstance(cell.v, float):
93 val = int(cell.v)
94 if val == cell.v:
95 return val
96 else:
97 return float(cell.v)
99 return cell.v
101 def get_sheet_data(
102 self,
103 sheet,
104 file_rows_needed: int | None = None,
105 ) -> list[list[Scalar]]:
106 data: list[list[Scalar]] = []
107 previous_row_number = -1
108 # When sparse=True the rows can have different lengths and empty rows are
109 # not returned. The cells are namedtuples of row, col, value (r, c, v).
110 for row in sheet.rows(sparse=True):
111 row_number = row[0].r
112 converted_row = [self._convert_cell(cell) for cell in row]
113 while converted_row and converted_row[-1] == "":
114 # trim trailing empty elements
115 converted_row.pop()
116 if converted_row:
117 data.extend([[]] * (row_number - previous_row_number - 1))
118 data.append(converted_row)
119 previous_row_number = row_number
120 if file_rows_needed is not None and len(data) >= file_rows_needed:
121 break
122 if data:
123 # extend rows to max_width
124 max_width = max(len(data_row) for data_row in data)
125 if min(len(data_row) for data_row in data) < max_width:
126 empty_cell: list[Scalar] = [""]
127 data = [
128 data_row + (max_width - len(data_row)) * empty_cell
129 for data_row in data
130 ]
131 return data