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