Hide keyboard shortcuts

Hot-keys on this page

r m x p   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

1import pandas._libs.json as json 

2 

3from pandas.io.excel._base import ExcelWriter 

4from pandas.io.excel._util import _validate_freeze_panes 

5 

6 

7class _XlsxStyler: 

8 # Map from openpyxl-oriented styles to flatter xlsxwriter representation 

9 # Ordering necessary for both determinism and because some are keyed by 

10 # prefixes of others. 

11 STYLE_MAPPING = { 

12 "font": [ 

13 (("name",), "font_name"), 

14 (("sz",), "font_size"), 

15 (("size",), "font_size"), 

16 (("color", "rgb"), "font_color"), 

17 (("color",), "font_color"), 

18 (("b",), "bold"), 

19 (("bold",), "bold"), 

20 (("i",), "italic"), 

21 (("italic",), "italic"), 

22 (("u",), "underline"), 

23 (("underline",), "underline"), 

24 (("strike",), "font_strikeout"), 

25 (("vertAlign",), "font_script"), 

26 (("vertalign",), "font_script"), 

27 ], 

28 "number_format": [(("format_code",), "num_format"), ((), "num_format")], 

29 "protection": [(("locked",), "locked"), (("hidden",), "hidden")], 

30 "alignment": [ 

31 (("horizontal",), "align"), 

32 (("vertical",), "valign"), 

33 (("text_rotation",), "rotation"), 

34 (("wrap_text",), "text_wrap"), 

35 (("indent",), "indent"), 

36 (("shrink_to_fit",), "shrink"), 

37 ], 

38 "fill": [ 

39 (("patternType",), "pattern"), 

40 (("patterntype",), "pattern"), 

41 (("fill_type",), "pattern"), 

42 (("start_color", "rgb"), "fg_color"), 

43 (("fgColor", "rgb"), "fg_color"), 

44 (("fgcolor", "rgb"), "fg_color"), 

45 (("start_color",), "fg_color"), 

46 (("fgColor",), "fg_color"), 

47 (("fgcolor",), "fg_color"), 

48 (("end_color", "rgb"), "bg_color"), 

49 (("bgColor", "rgb"), "bg_color"), 

50 (("bgcolor", "rgb"), "bg_color"), 

51 (("end_color",), "bg_color"), 

52 (("bgColor",), "bg_color"), 

53 (("bgcolor",), "bg_color"), 

54 ], 

55 "border": [ 

56 (("color", "rgb"), "border_color"), 

57 (("color",), "border_color"), 

58 (("style",), "border"), 

59 (("top", "color", "rgb"), "top_color"), 

60 (("top", "color"), "top_color"), 

61 (("top", "style"), "top"), 

62 (("top",), "top"), 

63 (("right", "color", "rgb"), "right_color"), 

64 (("right", "color"), "right_color"), 

65 (("right", "style"), "right"), 

66 (("right",), "right"), 

67 (("bottom", "color", "rgb"), "bottom_color"), 

68 (("bottom", "color"), "bottom_color"), 

69 (("bottom", "style"), "bottom"), 

70 (("bottom",), "bottom"), 

71 (("left", "color", "rgb"), "left_color"), 

72 (("left", "color"), "left_color"), 

73 (("left", "style"), "left"), 

74 (("left",), "left"), 

75 ], 

76 } 

77 

78 @classmethod 

79 def convert(cls, style_dict, num_format_str=None): 

80 """ 

81 converts a style_dict to an xlsxwriter format dict 

82 

83 Parameters 

84 ---------- 

85 style_dict : style dictionary to convert 

86 num_format_str : optional number format string 

87 """ 

88 

89 # Create a XlsxWriter format object. 

90 props = {} 

91 

92 if num_format_str is not None: 

93 props["num_format"] = num_format_str 

94 

95 if style_dict is None: 

96 return props 

97 

98 if "borders" in style_dict: 

99 style_dict = style_dict.copy() 

100 style_dict["border"] = style_dict.pop("borders") 

101 

102 for style_group_key, style_group in style_dict.items(): 

103 for src, dst in cls.STYLE_MAPPING.get(style_group_key, []): 

104 # src is a sequence of keys into a nested dict 

105 # dst is a flat key 

106 if dst in props: 

107 continue 

108 v = style_group 

109 for k in src: 

110 try: 

111 v = v[k] 

112 except (KeyError, TypeError): 

113 break 

114 else: 

115 props[dst] = v 

116 

117 if isinstance(props.get("pattern"), str): 

118 # TODO: support other fill patterns 

119 props["pattern"] = 0 if props["pattern"] == "none" else 1 

120 

121 for k in ["border", "top", "right", "bottom", "left"]: 

122 if isinstance(props.get(k), str): 

123 try: 

124 props[k] = [ 

125 "none", 

126 "thin", 

127 "medium", 

128 "dashed", 

129 "dotted", 

130 "thick", 

131 "double", 

132 "hair", 

133 "mediumDashed", 

134 "dashDot", 

135 "mediumDashDot", 

136 "dashDotDot", 

137 "mediumDashDotDot", 

138 "slantDashDot", 

139 ].index(props[k]) 

140 except ValueError: 

141 props[k] = 2 

142 

143 if isinstance(props.get("font_script"), str): 

144 props["font_script"] = ["baseline", "superscript", "subscript"].index( 

145 props["font_script"] 

146 ) 

147 

148 if isinstance(props.get("underline"), str): 

149 props["underline"] = { 

150 "none": 0, 

151 "single": 1, 

152 "double": 2, 

153 "singleAccounting": 33, 

154 "doubleAccounting": 34, 

155 }[props["underline"]] 

156 

157 return props 

158 

159 

160class _XlsxWriter(ExcelWriter): 

161 engine = "xlsxwriter" 

162 supported_extensions = (".xlsx",) 

163 

164 def __init__( 

165 self, 

166 path, 

167 engine=None, 

168 date_format=None, 

169 datetime_format=None, 

170 mode="w", 

171 **engine_kwargs, 

172 ): 

173 # Use the xlsxwriter module as the Excel writer. 

174 import xlsxwriter 

175 

176 if mode == "a": 

177 raise ValueError("Append mode is not supported with xlsxwriter!") 

178 

179 super().__init__( 

180 path, 

181 engine=engine, 

182 date_format=date_format, 

183 datetime_format=datetime_format, 

184 mode=mode, 

185 **engine_kwargs, 

186 ) 

187 

188 self.book = xlsxwriter.Workbook(path, **engine_kwargs) 

189 

190 def save(self): 

191 """ 

192 Save workbook to disk. 

193 """ 

194 

195 return self.book.close() 

196 

197 def write_cells( 

198 self, cells, sheet_name=None, startrow=0, startcol=0, freeze_panes=None 

199 ): 

200 # Write the frame cells using xlsxwriter. 

201 sheet_name = self._get_sheet_name(sheet_name) 

202 

203 if sheet_name in self.sheets: 

204 wks = self.sheets[sheet_name] 

205 else: 

206 wks = self.book.add_worksheet(sheet_name) 

207 self.sheets[sheet_name] = wks 

208 

209 style_dict = {"null": None} 

210 

211 if _validate_freeze_panes(freeze_panes): 

212 wks.freeze_panes(*(freeze_panes)) 

213 

214 for cell in cells: 

215 val, fmt = self._value_with_fmt(cell.val) 

216 

217 stylekey = json.dumps(cell.style) 

218 if fmt: 

219 stylekey += fmt 

220 

221 if stylekey in style_dict: 

222 style = style_dict[stylekey] 

223 else: 

224 style = self.book.add_format(_XlsxStyler.convert(cell.style, fmt)) 

225 style_dict[stylekey] = style 

226 

227 if cell.mergestart is not None and cell.mergeend is not None: 

228 wks.merge_range( 

229 startrow + cell.row, 

230 startcol + cell.col, 

231 startrow + cell.mergestart, 

232 startcol + cell.mergeend, 

233 val, 

234 style, 

235 ) 

236 else: 

237 wks.write(startrow + cell.row, startcol + cell.col, val, style)