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
3import json
4from typing import (
5 TYPE_CHECKING,
6 Any,
7)
9from pandas.io.excel._base import ExcelWriter
10from pandas.io.excel._util import (
11 combine_kwargs,
12 validate_freeze_panes,
13)
15if TYPE_CHECKING:
16 from xlsxwriter import Workbook
18 from pandas._typing import (
19 ExcelWriterIfSheetExists,
20 FilePath,
21 StorageOptions,
22 WriteExcelBuffer,
23 )
26class _XlsxStyler:
27 # Map from openpyxl-oriented styles to flatter xlsxwriter representation
28 # Ordering necessary for both determinism and because some are keyed by
29 # prefixes of others.
30 STYLE_MAPPING: dict[str, list[tuple[tuple[str, ...], str]]] = {
31 "font": [
32 (("name",), "font_name"),
33 (("sz",), "font_size"),
34 (("size",), "font_size"),
35 (("color", "rgb"), "font_color"),
36 (("color",), "font_color"),
37 (("b",), "bold"),
38 (("bold",), "bold"),
39 (("i",), "italic"),
40 (("italic",), "italic"),
41 (("u",), "underline"),
42 (("underline",), "underline"),
43 (("strike",), "font_strikeout"),
44 (("vertAlign",), "font_script"),
45 (("vertalign",), "font_script"),
46 ],
47 "number_format": [(("format_code",), "num_format"), ((), "num_format")],
48 "protection": [(("locked",), "locked"), (("hidden",), "hidden")],
49 "alignment": [
50 (("horizontal",), "align"),
51 (("vertical",), "valign"),
52 (("text_rotation",), "rotation"),
53 (("wrap_text",), "text_wrap"),
54 (("indent",), "indent"),
55 (("shrink_to_fit",), "shrink"),
56 ],
57 "fill": [
58 (("patternType",), "pattern"),
59 (("patterntype",), "pattern"),
60 (("fill_type",), "pattern"),
61 (("start_color", "rgb"), "fg_color"),
62 (("fgColor", "rgb"), "fg_color"),
63 (("fgcolor", "rgb"), "fg_color"),
64 (("start_color",), "fg_color"),
65 (("fgColor",), "fg_color"),
66 (("fgcolor",), "fg_color"),
67 (("end_color", "rgb"), "bg_color"),
68 (("bgColor", "rgb"), "bg_color"),
69 (("bgcolor", "rgb"), "bg_color"),
70 (("end_color",), "bg_color"),
71 (("bgColor",), "bg_color"),
72 (("bgcolor",), "bg_color"),
73 ],
74 "border": [
75 (("color", "rgb"), "border_color"),
76 (("color",), "border_color"),
77 (("style",), "border"),
78 (("top", "color", "rgb"), "top_color"),
79 (("top", "color"), "top_color"),
80 (("top", "style"), "top"),
81 (("top",), "top"),
82 (("right", "color", "rgb"), "right_color"),
83 (("right", "color"), "right_color"),
84 (("right", "style"), "right"),
85 (("right",), "right"),
86 (("bottom", "color", "rgb"), "bottom_color"),
87 (("bottom", "color"), "bottom_color"),
88 (("bottom", "style"), "bottom"),
89 (("bottom",), "bottom"),
90 (("left", "color", "rgb"), "left_color"),
91 (("left", "color"), "left_color"),
92 (("left", "style"), "left"),
93 (("left",), "left"),
94 ],
95 }
97 @classmethod
98 def convert(cls, style_dict, num_format_str=None) -> dict[str, Any]:
99 """
100 converts a style_dict to an xlsxwriter format dict
102 Parameters
103 ----------
104 style_dict : style dictionary to convert
105 num_format_str : optional number format string
106 """
107 # Create an XlsxWriter format object.
108 props = {}
110 if num_format_str is not None:
111 props["num_format"] = num_format_str
113 if style_dict is None:
114 return props
116 if "borders" in style_dict:
117 style_dict = style_dict.copy()
118 style_dict["border"] = style_dict.pop("borders")
120 for style_group_key, style_group in style_dict.items():
121 for src, dst in cls.STYLE_MAPPING.get(style_group_key, []):
122 # src is a sequence of keys into a nested dict
123 # dst is a flat key
124 if dst in props:
125 continue
126 v = style_group
127 for k in src:
128 try:
129 v = v[k]
130 except (KeyError, TypeError):
131 break
132 else:
133 props[dst] = v
135 if isinstance(props.get("pattern"), str):
136 # TODO: support other fill patterns
137 props["pattern"] = 0 if props["pattern"] == "none" else 1
139 for k in ["border", "top", "right", "bottom", "left"]:
140 if isinstance(props.get(k), str):
141 try:
142 props[k] = [
143 "none",
144 "thin",
145 "medium",
146 "dashed",
147 "dotted",
148 "thick",
149 "double",
150 "hair",
151 "mediumDashed",
152 "dashDot",
153 "mediumDashDot",
154 "dashDotDot",
155 "mediumDashDotDot",
156 "slantDashDot",
157 ].index(props[k])
158 except ValueError:
159 props[k] = 2
161 if isinstance(props.get("font_script"), str):
162 props["font_script"] = ["baseline", "superscript", "subscript"].index(
163 props["font_script"]
164 )
166 if isinstance(props.get("underline"), str):
167 props["underline"] = {
168 "none": 0,
169 "single": 1,
170 "double": 2,
171 "singleAccounting": 33,
172 "doubleAccounting": 34,
173 }[props["underline"]]
175 # GH 30107 - xlsxwriter uses different name
176 if props.get("valign") == "center":
177 props["valign"] = "vcenter"
179 return props
182class XlsxWriter(ExcelWriter["Workbook"]):
183 _engine = "xlsxwriter"
184 _supported_extensions = (".xlsx",)
186 def __init__( # pyright: ignore[reportInconsistentConstructor]
187 self,
188 path: FilePath | WriteExcelBuffer | ExcelWriter[Any],
189 engine: str | None = None,
190 date_format: str | None = None,
191 datetime_format: str | None = None,
192 mode: str = "w",
193 storage_options: StorageOptions | None = None,
194 if_sheet_exists: ExcelWriterIfSheetExists | None = None,
195 engine_kwargs: dict[str, Any] | None = None,
196 **kwargs,
197 ) -> None:
198 # Use the xlsxwriter module as the Excel writer.
199 from xlsxwriter import Workbook
201 engine_kwargs = combine_kwargs(engine_kwargs, kwargs)
203 if mode == "a":
204 raise ValueError("Append mode is not supported with xlsxwriter!")
206 super().__init__(
207 path,
208 engine=engine,
209 date_format=date_format,
210 datetime_format=datetime_format,
211 mode=mode,
212 storage_options=storage_options,
213 if_sheet_exists=if_sheet_exists,
214 engine_kwargs=engine_kwargs,
215 )
217 try:
218 self._book = Workbook(self._handles.handle, **engine_kwargs)
219 except TypeError:
220 self._handles.handle.close()
221 raise
223 @property
224 def book(self) -> Workbook:
225 """
226 Book instance of class xlsxwriter.Workbook.
228 This attribute can be used to access engine-specific features.
229 """
230 return self._book
232 @property
233 def sheets(self) -> dict[str, Any]:
234 result = self.book.sheetnames
235 return result
237 def _save(self) -> None:
238 """
239 Save workbook to disk.
240 """
241 self.book.close()
243 def _write_cells(
244 self,
245 cells,
246 sheet_name: str | None = None,
247 startrow: int = 0,
248 startcol: int = 0,
249 freeze_panes: tuple[int, int] | None = None,
250 autofilter_range: str | None = None,
251 ) -> None:
252 # Write the frame cells using xlsxwriter.
253 sheet_name = self._get_sheet_name(sheet_name)
255 wks = self.book.get_worksheet_by_name(sheet_name)
256 if wks is None:
257 wks = self.book.add_worksheet(sheet_name)
259 style_dict = {"null": None}
261 if validate_freeze_panes(freeze_panes):
262 wks.freeze_panes(*(freeze_panes))
264 for cell in cells:
265 val, fmt = self._value_with_fmt(cell.val)
267 stylekey = json.dumps(cell.style)
268 if fmt:
269 stylekey += fmt
271 if stylekey in style_dict:
272 style = style_dict[stylekey]
273 else:
274 style = self.book.add_format(_XlsxStyler.convert(cell.style, fmt))
275 style_dict[stylekey] = style
277 if cell.mergestart is not None and cell.mergeend is not None:
278 wks.merge_range(
279 startrow + cell.row,
280 startcol + cell.col,
281 startrow + cell.mergestart,
282 startcol + cell.mergeend,
283 val,
284 style,
285 )
286 else:
287 wks.write(startrow + cell.row, startcol + cell.col, val, style)
289 if autofilter_range:
290 wks.autofilter(autofilter_range)