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 collections.abc import (
4 Callable,
5 Hashable,
6 Iterable,
7 MutableMapping,
8 Sequence,
9)
10from typing import (
11 TYPE_CHECKING,
12 Any,
13 Literal,
14 TypeVar,
15 overload,
16)
18from pandas.compat._optional import import_optional_dependency
20from pandas.core.dtypes.common import (
21 is_integer,
22 is_list_like,
23)
25if TYPE_CHECKING:
26 from pandas.io.excel._base import ExcelWriter
28 ExcelWriter_t = type[ExcelWriter]
29 usecols_func = TypeVar("usecols_func", bound=Callable[[Hashable], object])
31_writers: MutableMapping[str, ExcelWriter_t] = {}
34def register_writer(klass: ExcelWriter_t) -> None:
35 """
36 Add engine to the excel writer registry.io.excel.
38 You must use this method to integrate with ``to_excel``.
40 Parameters
41 ----------
42 klass : ExcelWriter
43 """
44 if not callable(klass):
45 raise ValueError("Can only register callables as engines")
46 engine_name = klass._engine
47 _writers[engine_name] = klass
50def get_default_engine(ext: str, mode: Literal["reader", "writer"] = "reader") -> str:
51 """
52 Return the default reader/writer for the given extension.
54 Parameters
55 ----------
56 ext : str
57 The excel file extension for which to get the default engine.
58 mode : str {'reader', 'writer'}
59 Whether to get the default engine for reading or writing.
60 Either 'reader' or 'writer'
62 Returns
63 -------
64 str
65 The default engine for the extension.
66 """
67 _default_readers = {
68 "xlsx": "openpyxl",
69 "xlsm": "openpyxl",
70 "xlsb": "pyxlsb",
71 "xls": "xlrd",
72 "ods": "odf",
73 }
74 _default_writers = {
75 "xlsx": "openpyxl",
76 "xlsm": "openpyxl",
77 "xlsb": "pyxlsb",
78 "ods": "odf",
79 }
80 assert mode in ["reader", "writer"]
81 if mode == "writer":
82 # Prefer xlsxwriter over openpyxl if installed
83 xlsxwriter = import_optional_dependency("xlsxwriter", errors="warn")
84 if xlsxwriter:
85 _default_writers["xlsx"] = "xlsxwriter"
86 return _default_writers[ext]
87 else:
88 return _default_readers[ext]
91def get_writer(engine_name: str) -> ExcelWriter_t:
92 try:
93 return _writers[engine_name]
94 except KeyError as err:
95 raise ValueError(f"No Excel writer '{engine_name}'") from err
98def _excel2num(x: str) -> int:
99 """
100 Convert Excel column name like 'AB' to 0-based column index.
102 Parameters
103 ----------
104 x : str
105 The Excel column name to convert to a 0-based column index.
107 Returns
108 -------
109 num : int
110 The column index corresponding to the name.
112 Raises
113 ------
114 ValueError
115 Part of the Excel column name was invalid.
116 """
117 index = 0
119 for c in x.upper().strip():
120 cp = ord(c)
122 if cp < ord("A") or cp > ord("Z"):
123 raise ValueError(f"Invalid column name: {x}")
125 index = index * 26 + cp - ord("A") + 1
127 return index - 1
130def _range2cols(areas: str) -> list[int]:
131 """
132 Convert comma separated list of column names and ranges to indices.
134 Parameters
135 ----------
136 areas : str
137 A string containing a sequence of column ranges (or areas).
139 Returns
140 -------
141 cols : list
142 A list of 0-based column indices.
144 Examples
145 --------
146 >>> _range2cols("A:E")
147 [0, 1, 2, 3, 4]
148 >>> _range2cols("A,C,Z:AB")
149 [0, 2, 25, 26, 27]
150 """
151 cols: list[int] = []
153 for rng in areas.split(","):
154 if ":" in rng:
155 rngs = rng.split(":")
156 cols.extend(range(_excel2num(rngs[0]), _excel2num(rngs[1]) + 1))
157 else:
158 cols.append(_excel2num(rng))
160 return cols
163@overload
164def maybe_convert_usecols(usecols: str | list[int]) -> list[int]: ...
167@overload
168def maybe_convert_usecols(usecols: list[str]) -> list[str]: ...
171@overload
172def maybe_convert_usecols(usecols: usecols_func) -> usecols_func: ...
175@overload
176def maybe_convert_usecols(usecols: None) -> None: ...
179def maybe_convert_usecols(
180 usecols: str | list[int] | list[str] | usecols_func | None,
181) -> None | list[int] | list[str] | usecols_func:
182 """
183 Convert `usecols` into a compatible format for parsing in `parsers.py`.
185 Parameters
186 ----------
187 usecols : object
188 The use-columns object to potentially convert.
190 Returns
191 -------
192 converted : object
193 The compatible format of `usecols`.
194 """
195 if usecols is None:
196 return usecols
198 if is_integer(usecols):
199 raise ValueError(
200 "Passing an integer for `usecols` is no longer supported. "
201 "Please pass in a list of int from 0 to `usecols` inclusive instead."
202 )
204 if isinstance(usecols, str):
205 return _range2cols(usecols)
207 return usecols
210@overload
211def validate_freeze_panes(freeze_panes: tuple[int, int]) -> Literal[True]: ...
214@overload
215def validate_freeze_panes(freeze_panes: None) -> Literal[False]: ...
218def validate_freeze_panes(freeze_panes: tuple[int, int] | None) -> bool:
219 if freeze_panes is not None:
220 if len(freeze_panes) == 2 and all(
221 isinstance(item, int) for item in freeze_panes
222 ):
223 return True
225 raise ValueError(
226 "freeze_panes must be of form (row, column) "
227 "where row and column are integers"
228 )
230 # freeze_panes wasn't specified, return False so it won't be applied
231 # to output sheet
232 return False
235def fill_mi_header(
236 row: list[Hashable], control_row: list[bool]
237) -> tuple[list[Hashable], list[bool]]:
238 """
239 Forward fill blank entries in row but only inside the same parent index.
241 Used for creating headers in Multiindex.
243 Parameters
244 ----------
245 row : list
246 List of items in a single row.
247 control_row : list of bool
248 Helps to determine if particular column is in same parent index as the
249 previous value. Used to stop propagation of empty cells between
250 different indexes.
252 Returns
253 -------
254 Returns changed row and control_row
255 """
256 last = row[0]
257 for i in range(1, len(row)):
258 if not control_row[i]:
259 last = row[i]
261 if row[i] == "" or row[i] is None:
262 row[i] = last
263 else:
264 control_row[i] = False
265 last = row[i]
267 return row, control_row
270def pop_header_name(
271 row: list[Hashable], index_col: int | Sequence[int]
272) -> tuple[Hashable | None, list[Hashable]]:
273 """
274 Pop the header name for MultiIndex parsing.
276 Parameters
277 ----------
278 row : list
279 The data row to parse for the header name.
280 index_col : int, list
281 The index columns for our data. Assumed to be non-null.
283 Returns
284 -------
285 header_name : str
286 The extracted header name.
287 trimmed_row : list
288 The original data row with the header name removed.
289 """
290 # Pop out header name and fill w/blank.
291 if is_list_like(index_col):
292 assert isinstance(index_col, Iterable)
293 i = max(index_col)
294 else:
295 assert not isinstance(index_col, Iterable)
296 i = index_col
298 header_name = row[i]
299 header_name = None if header_name == "" else header_name
301 return header_name, [*row[:i], "", *row[i + 1 :]]
304def combine_kwargs(engine_kwargs: dict[str, Any] | None, kwargs: dict) -> dict:
305 """
306 Used to combine two sources of kwargs for the backend engine.
308 Use of kwargs is deprecated, this function is solely for use in 1.3 and should
309 be removed in 1.4/2.0. Also _base.ExcelWriter.__new__ ensures either engine_kwargs
310 or kwargs must be None or empty respectively.
312 Parameters
313 ----------
314 engine_kwargs: dict
315 kwargs to be passed through to the engine.
316 kwargs: dict
317 kwargs to be psased through to the engine (deprecated)
319 Returns
320 -------
321 engine_kwargs combined with kwargs
322 """
323 if engine_kwargs is None:
324 result = {}
325 else:
326 result = engine_kwargs.copy()
327 result.update(kwargs)
328 return result