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

1# Copyright (c) 2010-2020 openpyxl 

2 

3from warnings import warn 

4 

5from openpyxl.descriptors.serialisable import Serialisable 

6from openpyxl.descriptors import ( 

7 Typed, 

8) 

9from openpyxl.descriptors.sequence import NestedSequence 

10from openpyxl.descriptors.excel import ExtensionList 

11from openpyxl.utils.indexed_list import IndexedList 

12from openpyxl.xml.constants import ARC_STYLE, SHEET_MAIN_NS 

13from openpyxl.xml.functions import fromstring 

14 

15from .builtins import styles 

16from .colors import ColorList, COLOR_INDEX 

17from .differential import DifferentialStyle 

18from .table import TableStyleList 

19from .borders import Border 

20from .fills import Fill 

21from .fonts import Font 

22from .numbers import ( 

23 NumberFormatList, 

24 BUILTIN_FORMATS, 

25 BUILTIN_FORMATS_MAX_SIZE, 

26 BUILTIN_FORMATS_REVERSE, 

27 is_date_format, 

28 builtin_format_code 

29) 

30from .named_styles import ( 

31 _NamedCellStyleList 

32) 

33from .cell_style import CellStyle, CellStyleList 

34 

35 

36class Stylesheet(Serialisable): 

37 

38 tagname = "styleSheet" 

39 

40 numFmts = Typed(expected_type=NumberFormatList) 

41 fonts = NestedSequence(expected_type=Font, count=True) 

42 fills = NestedSequence(expected_type=Fill, count=True) 

43 borders = NestedSequence(expected_type=Border, count=True) 

44 cellStyleXfs = Typed(expected_type=CellStyleList) 

45 cellXfs = Typed(expected_type=CellStyleList) 

46 cellStyles = Typed(expected_type=_NamedCellStyleList) 

47 dxfs = NestedSequence(expected_type=DifferentialStyle, count=True) 

48 tableStyles = Typed(expected_type=TableStyleList, allow_none=True) 

49 colors = Typed(expected_type=ColorList, allow_none=True) 

50 extLst = Typed(expected_type=ExtensionList, allow_none=True) 

51 

52 __elements__ = ('numFmts', 'fonts', 'fills', 'borders', 'cellStyleXfs', 

53 'cellXfs', 'cellStyles', 'dxfs', 'tableStyles', 'colors') 

54 

55 def __init__(self, 

56 numFmts=None, 

57 fonts=(), 

58 fills=(), 

59 borders=(), 

60 cellStyleXfs=None, 

61 cellXfs=None, 

62 cellStyles=None, 

63 dxfs=(), 

64 tableStyles=None, 

65 colors=None, 

66 extLst=None, 

67 ): 

68 if numFmts is None: 

69 numFmts = NumberFormatList() 

70 self.numFmts = numFmts 

71 self.number_formats = IndexedList() 

72 self.fonts = fonts 

73 self.fills = fills 

74 self.borders = borders 

75 if cellStyleXfs is None: 

76 cellStyleXfs = CellStyleList() 

77 self.cellStyleXfs = cellStyleXfs 

78 if cellXfs is None: 

79 cellXfs = CellStyleList() 

80 self.cellXfs = cellXfs 

81 if cellStyles is None: 

82 cellStyles = _NamedCellStyleList() 

83 self.cellStyles = cellStyles 

84 

85 self.dxfs = dxfs 

86 self.tableStyles = tableStyles 

87 self.colors = colors 

88 

89 self.cell_styles = self.cellXfs._to_array() 

90 self.alignments = self.cellXfs.alignments 

91 self.protections = self.cellXfs.prots 

92 self._normalise_numbers() 

93 self.named_styles = self._merge_named_styles() 

94 

95 

96 @classmethod 

97 def from_tree(cls, node): 

98 # strip all attribs 

99 attrs = dict(node.attrib) 

100 for k in attrs: 

101 del node.attrib[k] 

102 return super(Stylesheet, cls).from_tree(node) 

103 

104 

105 def _merge_named_styles(self): 

106 """ 

107 Merge named style names "cellStyles" with their associated styles 

108 "cellStyleXfs" 

109 """ 

110 named_styles = self.cellStyles.names 

111 

112 for style in named_styles: 

113 self._expand_named_style(style) 

114 

115 return named_styles 

116 

117 

118 def _expand_named_style(self, named_style): 

119 """ 

120 Bind format definitions for a named style from the associated style 

121 record 

122 """ 

123 xf = self.cellStyleXfs[named_style.xfId] 

124 named_style.font = self.fonts[xf.fontId] 

125 named_style.fill = self.fills[xf.fillId] 

126 named_style.border = self.borders[xf.borderId] 

127 if xf.numFmtId < BUILTIN_FORMATS_MAX_SIZE: 

128 formats = BUILTIN_FORMATS 

129 else: 

130 formats = self.custom_formats 

131 if xf.numFmtId in formats: 

132 named_style.number_format = formats[xf.numFmtId] 

133 if xf.alignment: 

134 named_style.alignment = xf.alignment 

135 if xf.protection: 

136 named_style.protection = xf.protection 

137 

138 

139 def _split_named_styles(self, wb): 

140 """ 

141 Convert NamedStyle into separate CellStyle and Xf objects 

142 """ 

143 for style in wb._named_styles: 

144 self.cellStyles.cellStyle.append(style.as_name()) 

145 self.cellStyleXfs.xf.append(style.as_xf()) 

146 

147 

148 @property 

149 def custom_formats(self): 

150 return dict([(n.numFmtId, n.formatCode) for n in self.numFmts.numFmt]) 

151 

152 

153 def _normalise_numbers(self): 

154 """ 

155 Rebase custom numFmtIds with a floor of 164 when reading stylesheet 

156 And index datetime formats 

157 """ 

158 date_formats = set() 

159 custom = self.custom_formats 

160 formats = self.number_formats 

161 for idx, style in enumerate(self.cell_styles): 

162 if style.numFmtId in custom: 

163 fmt = custom[style.numFmtId] 

164 if fmt in BUILTIN_FORMATS_REVERSE: # remove builtins 

165 style.numFmtId = BUILTIN_FORMATS_REVERSE[fmt] 

166 else: 

167 style.numFmtId = formats.add(fmt) + BUILTIN_FORMATS_MAX_SIZE 

168 else: 

169 fmt = builtin_format_code(style.numFmtId) 

170 if is_date_format(fmt): 

171 # Create an index of which styles refer to datetimes 

172 date_formats.add(idx) 

173 self.date_formats = date_formats 

174 

175 

176 def to_tree(self, tagname=None, idx=None, namespace=None): 

177 tree = super(Stylesheet, self).to_tree(tagname, idx, namespace) 

178 tree.set("xmlns", SHEET_MAIN_NS) 

179 return tree 

180 

181 

182def apply_stylesheet(archive, wb): 

183 """ 

184 Add styles to workbook if present 

185 """ 

186 try: 

187 src = archive.read(ARC_STYLE) 

188 except KeyError: 

189 return wb 

190 

191 node = fromstring(src) 

192 stylesheet = Stylesheet.from_tree(node) 

193 

194 wb._borders = IndexedList(stylesheet.borders) 

195 wb._fonts = IndexedList(stylesheet.fonts) 

196 wb._fills = IndexedList(stylesheet.fills) 

197 wb._differential_styles.styles = stylesheet.dxfs 

198 wb._number_formats = stylesheet.number_formats 

199 wb._protections = stylesheet.protections 

200 wb._alignments = stylesheet.alignments 

201 wb._table_styles = stylesheet.tableStyles 

202 

203 # need to overwrite openpyxl defaults in case workbook has different ones 

204 wb._cell_styles = stylesheet.cell_styles 

205 wb._named_styles = stylesheet.named_styles 

206 wb._date_formats = stylesheet.date_formats 

207 

208 for ns in wb._named_styles: 

209 ns.bind(wb) 

210 

211 if not wb._named_styles: 

212 normal = styles['Normal'] 

213 wb.add_named_style(normal) 

214 warn("Workbook contains no default style, apply openpyxl's default") 

215 

216 if stylesheet.colors is not None: 

217 wb._colors = stylesheet.colors.index 

218 

219 

220def write_stylesheet(wb): 

221 stylesheet = Stylesheet() 

222 stylesheet.fonts = wb._fonts 

223 stylesheet.fills = wb._fills 

224 stylesheet.borders = wb._borders 

225 stylesheet.dxfs = wb._differential_styles.styles 

226 stylesheet.colors = ColorList(indexedColors=wb._colors) 

227 

228 from .numbers import NumberFormat 

229 fmts = [] 

230 for idx, code in enumerate(wb._number_formats, BUILTIN_FORMATS_MAX_SIZE): 

231 fmt = NumberFormat(idx, code) 

232 fmts.append(fmt) 

233 

234 stylesheet.numFmts.numFmt = fmts 

235 

236 xfs = [] 

237 for style in wb._cell_styles: 

238 xf = CellStyle.from_array(style) 

239 

240 if style.alignmentId: 

241 xf.alignment = wb._alignments[style.alignmentId] 

242 

243 if style.protectionId: 

244 xf.protection = wb._protections[style.protectionId] 

245 xfs.append(xf) 

246 stylesheet.cellXfs = CellStyleList(xf=xfs) 

247 

248 stylesheet._split_named_styles(wb) 

249 stylesheet.tableStyles = wb._table_styles 

250 

251 return stylesheet.to_tree()