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