def _setup_styles(self): """Bootstrap styles""" from ..styles.alignment import Alignment from ..styles.borders import DEFAULT_BORDER from ..styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL from ..styles.fonts import DEFAULT_FONT from ..styles.protection import Protection from ..styles.colors import COLOR_INDEX self._fonts = IndexedList() self._fonts.add(DEFAULT_FONT) self._alignments = IndexedList([Alignment()]) self._borders = IndexedList() self._borders.add(DEFAULT_BORDER) self._fills = IndexedList() self._fills.add(DEFAULT_EMPTY_FILL) self._fills.add(DEFAULT_GRAY_FILL) self._number_formats = IndexedList() self._protections = IndexedList([Protection()]) self._colors = COLOR_INDEX self._cell_styles = IndexedList([StyleId()])
def parse(self): self.parse_custom_num_formats() self.parse_color_index() self.style_prop['color_index'] = self.color_index self.font_list = IndexedList(self.parse_fonts()) self.fill_list = IndexedList(self.parse_fills()) self.border_list = IndexedList(self.parse_borders()) self.parse_dxfs() self.parse_cell_styles()
def __init__(self, xml_source): self.root = fromstring(xml_source) self.shared_styles = [] self.cell_styles = IndexedList() self.cond_styles = [] self.style_prop = {} self.color_index = COLOR_INDEX self.font_list = IndexedList() self.fill_list = IndexedList() self.border_list = IndexedList() self.alignments = IndexedList([Alignment()]) self.protections = IndexedList([Protection()]) self.custom_number_formats = {} self.number_formats = IndexedList()
def parse_color_index(self): """Read in the list of indexed colors""" colors =\ self.root.findall('{%s}colors/{%s}indexedColors/{%s}rgbColor' % (SHEET_MAIN_NS, SHEET_MAIN_NS, SHEET_MAIN_NS)) if not colors: return self.color_index = IndexedList([node.get('rgb') for node in colors])
def _parse_xfs(self, node): """Read styles from the shared style table""" _styles = [] _style_ids = [] builtin_formats = numbers.BUILTIN_FORMATS xfs = safe_iterator(node, '{%s}xf' % SHEET_MAIN_NS) for index, xf in enumerate(xfs): _style = {} attrs = dict(xf.attrib) alignmentId = protectionId = 0 numFmtId = int(xf.get("numFmtId", 0)) fontId = int(xf.get("fontId", 0)) fillId = int(xf.get("fillId", 0)) borderId = int(xf.get("borderId", 0)) # check for custom formats and normalise indices if numFmtId in self.custom_number_formats: format_code = self.custom_number_formats[numFmtId] attrs["numFmtId"] = self.number_formats.add(format_code) + 164 else: format_code = builtin_formats[numFmtId] _style['number_format'] = format_code if bool_attrib(xf, 'applyAlignment'): al = xf.find('{%s}alignment' % SHEET_MAIN_NS) if al is not None: alignment = Alignment(**al.attrib) attrs['alignmentId'] = self.alignments.add(alignment) _style['alignment'] = alignment if bool_attrib(xf, 'applyFont'): _style['font'] = self.font_list[fontId] if bool_attrib(xf, 'applyFill'): _style['fill'] = self.fill_list[fillId] if bool_attrib(xf, 'applyBorder'): _style['border'] = self.border_list[borderId] if bool_attrib(xf, 'applyProtection'): prot = xf.find('{%s}protection' % SHEET_MAIN_NS) if prot is not None: protection = Protection(**prot.attrib) attrs['protectionId'] = self.protections.add(protection) _style['protection'] = protection _styles.append(Style(**_style)) _style_ids.append(StyleId(**attrs)) self.shared_styles = _styles self.cell_styles = IndexedList(_style_ids)
def parse_named_styles(self): """ Extract named styles """ ns = [] styles_node = self.root.find("{%s}cellStyleXfs" % SHEET_MAIN_NS) self._parse_xfs(styles_node) _ids = self.cell_styles for _name, idx in self._parse_style_names(): _id = _ids[idx] style = NamedStyle(name=_name) style.border = self.border_list[_id.border] style.fill = self.fill_list[_id.fill] style.font = self.font_list[_id.font] if _id.alignment: style.alignment = self.alignments[_id.alignment] if _id.protection: style.protection = self.protections[_id.protection] ns.append(style) self.named_styles = IndexedList(ns)
def __init__(self, optimized_write=False, encoding='utf-8', worksheet_class=Worksheet, guess_types=False, data_only=False, read_only=False, write_only=False): self.worksheets = [] self._active_sheet_index = 0 self._named_ranges = [] self._external_links = [] self.properties = DocumentProperties() self.style = Style() self.security = DocumentSecurity() self.__write_only = write_only or optimized_write self.__read_only = read_only self.__thread_local_data = threading.local() self.shared_strings = IndexedList() self._setup_styles() self.loaded_theme = None self._worksheet_class = worksheet_class self.vba_archive = None self.is_template = False self._differential_styles = [] self._guess_types = guess_types self.data_only = data_only self.relationships = [] self.drawings = [] self.code_name = None self.excel_base_date = CALENDAR_WINDOWS_1900 self.encoding = encoding if not self.write_only: self.worksheets.append(self._worksheet_class(parent_workbook=self))
class Workbook(object): """Workbook is the container for all other parts of the document.""" _optimized_worksheet_class = DumpWorksheet def __init__(self, optimized_write=False, encoding='utf-8', worksheet_class=Worksheet, guess_types=False, data_only=False, read_only=False, write_only=False): self.worksheets = [] self._active_sheet_index = 0 self._named_ranges = [] self._external_links = [] self.properties = DocumentProperties() self.style = Style() self.security = DocumentSecurity() self.__write_only = write_only or optimized_write self.__read_only = read_only self.__thread_local_data = threading.local() self.shared_strings = IndexedList() self._setup_styles() self.loaded_theme = None self._worksheet_class = worksheet_class self.vba_archive = None self.is_template = False self._differential_styles = [] self._guess_types = guess_types self.data_only = data_only self.relationships = [] self.drawings = [] self.code_name = None self.excel_base_date = CALENDAR_WINDOWS_1900 self.encoding = encoding if not self.write_only: self.worksheets.append(self._worksheet_class(parent_workbook=self)) def _setup_styles(self): """Bootstrap styles""" from ..styles.alignment import Alignment from ..styles.borders import DEFAULT_BORDER from ..styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL from ..styles.fonts import DEFAULT_FONT from ..styles.protection import Protection from ..styles.colors import COLOR_INDEX self._fonts = IndexedList() self._fonts.add(DEFAULT_FONT) self._alignments = IndexedList([Alignment()]) self._borders = IndexedList() self._borders.add(DEFAULT_BORDER) self._fills = IndexedList() self._fills.add(DEFAULT_EMPTY_FILL) self._fills.add(DEFAULT_GRAY_FILL) self._number_formats = IndexedList() self._protections = IndexedList([Protection()]) self._colors = COLOR_INDEX self._cell_styles = IndexedList([StyleId()]) @deprecated('this method is private and should not be called directly') def read_workbook_settings(self, xml_source): self._read_workbook_settings(xml_source) def _read_workbook_settings(self, xml_source): root = fromstring(xml_source) view = root.find('*/' '{%s}workbookView' % SHEET_MAIN_NS) if view is not None: if 'activeTab' in view.attrib: self.active = int(view.attrib['activeTab']) @property def shared_styles(self): """ Legacy On the fly conversion of style references to style objects """ styles = [] for sid in self._cell_styles: font = self._fonts[sid.font] fill = self._fills[sid.fill] border = self._borders[sid.fill] alignment = self._alignments[sid.alignment] protection = self._protections[sid.protection] nf_id = sid.number_format if nf_id < 164: number_format = BUILTIN_FORMATS.get(nf_id, "General") else: number_format = self._number_formats[sid.number_format - 164] styles.append( Style(font, fill, border, alignment, number_format, protection)) return styles @property def _local_data(self): return self.__thread_local_data @property def read_only(self): return self.__read_only @property def write_only(self): return self.__write_only def get_active_sheet(self): """Returns the current active sheet.""" return self.active @property def active(self): """Get the currently active sheet""" return self.worksheets[self._active_sheet_index] @active.setter def active(self, value): """Set the active sheet""" self._active_sheet_index = value def create_sheet(self, index=None, title=None): """Create a worksheet (at an optional index). :param index: optional position at which the sheet will be inserted :type index: int """ if self.read_only: raise ReadOnlyWorkbookException( 'Cannot create new sheet in a read-only workbook') if self.write_only: new_ws = self._optimized_worksheet_class(parent_workbook=self, title=title) self._worksheet_class = self._optimized_worksheet_class else: if title is not None: new_ws = self._worksheet_class(parent_workbook=self, title=title) else: new_ws = self._worksheet_class(parent_workbook=self) self._add_sheet(worksheet=new_ws, index=index) return new_ws @deprecated( "you probably want to use Workbook.create_sheet('sheet name') instead") def add_sheet(self, worksheet, index=None): self._add_sheet(worksheet, index) def _add_sheet(self, worksheet, index=None): """Add an existing worksheet (at an optional index).""" if not isinstance(worksheet, self._worksheet_class): raise TypeError( "The parameter you have given is not of the type '%s'" % self._worksheet_class.__name__) if worksheet.parent != self: raise ValueError( "You cannot add worksheets from another workbook.") if index is None: self.worksheets.append(worksheet) else: self.worksheets.insert(index, worksheet) def remove_sheet(self, worksheet): """Remove a worksheet from this workbook.""" self.worksheets.remove(worksheet) def get_sheet_by_name(self, name): """Returns a worksheet by its name. :param name: the name of the worksheet to look for :type name: string """ return self[name] def __contains__(self, key): return key in set(self.sheetnames) def get_index(self, worksheet): """Return the index of the worksheet.""" return self.worksheets.index(worksheet) def __getitem__(self, key): """Returns a worksheet by its name. :param name: the name of the worksheet to look for :type name: string """ for sheet in self.worksheets: if sheet.title == key: return sheet raise KeyError("Worksheet {0} does not exist.".format(key)) def __delitem__(self, key): sheet = self[key] self.remove_sheet(sheet) def __iter__(self): return iter(self.worksheets) def get_sheet_names(self): return self.sheetnames @property def sheetnames(self): """Returns the list of the names of worksheets in the workbook. Names are returned in the worksheets order. :rtype: list of strings """ return [s.title for s in self.worksheets] def create_named_range(self, name, worksheet, range, scope=None): """Create a new named_range on a worksheet""" if not isinstance(worksheet, self._worksheet_class): raise TypeError("Worksheet is not of the right type") named_range = NamedRange(name, [(worksheet, range)], scope) self.add_named_range(named_range) def get_named_ranges(self): """Return all named ranges""" return self._named_ranges def add_named_range(self, named_range): """Add an existing named_range to the list of named_ranges.""" self._named_ranges.append(named_range) def get_named_range(self, name): """Return the range specified by name.""" requested_range = None for named_range in self._named_ranges: if named_range.name == name: requested_range = named_range break return requested_range def remove_named_range(self, named_range): """Remove a named_range from this workbook.""" self._named_ranges.remove(named_range) def save(self, filename): """Save the current workbook under the given `filename`. Use this function instead of using an `ExcelWriter`. .. warning:: When creating your workbook using `write_only` set to True, you will only be able to call this function once. Subsequents attempts to modify or save the file will raise an :class:`openpyxl.shared.exc.WorkbookAlreadySaved` exception. """ if self.read_only: raise TypeError("""Workbook is read-only""") if self.write_only: save_dump(self, filename) else: save_workbook(self, filename)
def read_string_table(xml_source): """Read in all shared strings in the table""" root = fromstring(text=xml_source) nodes = safe_iterator(root, '{%s}si' % SHEET_MAIN_NS) strings = (get_string(node) for node in nodes) return IndexedList(strings)
class SharedStylesParser(object): def __init__(self, xml_source): self.root = fromstring(xml_source) self.shared_styles = [] self.cell_styles = IndexedList() self.cond_styles = [] self.style_prop = {} self.color_index = COLOR_INDEX self.font_list = IndexedList() self.fill_list = IndexedList() self.border_list = IndexedList() self.alignments = IndexedList([Alignment()]) self.protections = IndexedList([Protection()]) self.custom_number_formats = {} self.number_formats = IndexedList() def parse(self): self.parse_custom_num_formats() self.parse_color_index() self.style_prop['color_index'] = self.color_index self.font_list = IndexedList(self.parse_fonts()) self.fill_list = IndexedList(self.parse_fills()) self.border_list = IndexedList(self.parse_borders()) self.parse_dxfs() self.parse_cell_styles() def parse_custom_num_formats(self): """Read in custom numeric formatting rules from the shared style table""" custom_formats = {} num_fmts = self.root.findall('{%s}numFmts/{%s}numFmt' % (SHEET_MAIN_NS, SHEET_MAIN_NS)) for node in num_fmts: idx = int(node.get('numFmtId')) self.custom_number_formats[idx] = node.get('formatCode') self.number_formats.append(node.get('formatCode')) def parse_color_index(self): """Read in the list of indexed colors""" colors =\ self.root.findall('{%s}colors/{%s}indexedColors/{%s}rgbColor' % (SHEET_MAIN_NS, SHEET_MAIN_NS, SHEET_MAIN_NS)) if not colors: return self.color_index = IndexedList([node.get('rgb') for node in colors]) def parse_dxfs(self): """Read in the dxfs effects - used by conditional formatting.""" for node in self.root.findall("{%s}dxfs/{%s}dxf" % (SHEET_MAIN_NS, SHEET_MAIN_NS)): self.cond_styles.append(DifferentialStyle.from_tree(node)) def parse_fonts(self): """Read in the fonts""" fonts = self.root.findall('{%s}fonts/{%s}font' % (SHEET_MAIN_NS, SHEET_MAIN_NS)) for node in fonts: yield Font.from_tree(node) def parse_fills(self): """Read in the list of fills""" fills = self.root.findall('{%s}fills/{%s}fill' % (SHEET_MAIN_NS, SHEET_MAIN_NS)) for fill in fills: yield Fill.from_tree(fill) def parse_borders(self): """Read in the boarders""" borders = self.root.findall('{%s}borders/{%s}border' % (SHEET_MAIN_NS, SHEET_MAIN_NS)) for border_node in borders: yield Border.from_tree(border_node) def parse_named_styles(self): """ Extract named styles """ ns = [] styles_node = self.root.find("{%s}cellStyleXfs" % SHEET_MAIN_NS) self._parse_xfs(styles_node) _ids = self.cell_styles for _name, idx in self._parse_style_names(): _id = _ids[idx] style = NamedStyle(name=_name) style.border = self.border_list[_id.border] style.fill = self.fill_list[_id.fill] style.font = self.font_list[_id.font] if _id.alignment: style.alignment = self.alignments[_id.alignment] if _id.protection: style.protection = self.protections[_id.protection] ns.append(style) self.named_styles = IndexedList(ns) def _parse_style_names(self): names_node = self.root.find("{%s}cellStyles" % SHEET_MAIN_NS) for _name in names_node: yield _name.get("name"), int(_name.get("xfId")) def parse_cell_styles(self): """ Extract individual cell styles """ node = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS) if node is not None: self._parse_xfs(node) def _parse_xfs(self, node): """Read styles from the shared style table""" _styles = [] _style_ids = [] builtin_formats = numbers.BUILTIN_FORMATS xfs = safe_iterator(node, '{%s}xf' % SHEET_MAIN_NS) for index, xf in enumerate(xfs): _style = {} attrs = dict(xf.attrib) alignmentId = protectionId = 0 numFmtId = int(xf.get("numFmtId", 0)) fontId = int(xf.get("fontId", 0)) fillId = int(xf.get("fillId", 0)) borderId = int(xf.get("borderId", 0)) # check for custom formats and normalise indices if numFmtId in self.custom_number_formats: format_code = self.custom_number_formats[numFmtId] attrs["numFmtId"] = self.number_formats.add(format_code) + 164 else: format_code = builtin_formats[numFmtId] _style['number_format'] = format_code if bool_attrib(xf, 'applyAlignment'): al = xf.find('{%s}alignment' % SHEET_MAIN_NS) if al is not None: alignment = Alignment(**al.attrib) attrs['alignmentId'] = self.alignments.add(alignment) _style['alignment'] = alignment if bool_attrib(xf, 'applyFont'): _style['font'] = self.font_list[fontId] if bool_attrib(xf, 'applyFill'): _style['fill'] = self.fill_list[fillId] if bool_attrib(xf, 'applyBorder'): _style['border'] = self.border_list[borderId] if bool_attrib(xf, 'applyProtection'): prot = xf.find('{%s}protection' % SHEET_MAIN_NS) if prot is not None: protection = Protection(**prot.attrib) attrs['protectionId'] = self.protections.add(protection) _style['protection'] = protection _styles.append(Style(**_style)) _style_ids.append(StyleId(**attrs)) self.shared_styles = _styles self.cell_styles = IndexedList(_style_ids)
def __init__(self, sheet): self.sheet = sheet self.authors = IndexedList() self.comments = [] self.extract_comments()
class CommentWriter(object): def extract_comments(self): """ extract list of comments and authors """ for _coord, cell in iteritems(self.sheet._cells): if cell.comment is not None: self.authors.add(cell.comment.author) self.comments.append(cell.comment) def __init__(self, sheet): self.sheet = sheet self.authors = IndexedList() self.comments = [] self.extract_comments() def write_comments(self): # produce xml root = Element("{%s}comments" % SHEET_MAIN_NS) authorlist_tag = SubElement(root, "{%s}authors" % SHEET_MAIN_NS) for author in self.authors: leaf = SubElement(authorlist_tag, "{%s}author" % SHEET_MAIN_NS) leaf.text = author commentlist_tag = SubElement(root, "{%s}commentList" % SHEET_MAIN_NS) for comment in self.comments: attrs = { 'ref': comment._parent.coordinate, 'authorId': '%d' % self.authors.index(comment.author), 'shapeId': '0' } comment_tag = SubElement(commentlist_tag, "{%s}comment" % SHEET_MAIN_NS, attrs) text_tag = SubElement(comment_tag, "{%s}text" % SHEET_MAIN_NS) run_tag = SubElement(text_tag, "{%s}r" % SHEET_MAIN_NS) SubElement(run_tag, "{%s}rPr" % SHEET_MAIN_NS) t_tag = SubElement(run_tag, "{%s}t" % SHEET_MAIN_NS) t_tag.text = comment.text return tostring(root) def write_comments_vml(self): root = Element("xml") shape_layout = SubElement(root, "{%s}shapelayout" % officens, {"{%s}ext" % vmlns: "edit"}) SubElement(shape_layout, "{%s}idmap" % officens, { "{%s}ext" % vmlns: "edit", "data": "1" }) shape_type = SubElement( root, "{%s}shapetype" % vmlns, { "id": "_x0000_t202", "coordsize": "21600,21600", "{%s}spt" % officens: "202", "path": "m,l,21600r21600,l21600,xe" }) SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"}) SubElement(shape_type, "{%s}path" % vmlns, { "gradientshapeok": "t", "{%s}connecttype" % officens: "rect" }) for i, comment in enumerate(self.comments, 1026): shape = self._write_comment_shape(comment, i) root.append(shape) return tostring(root) def _write_comment_shape(self, comment, idx): # get zero-indexed coordinates of the comment col, row = coordinate_from_string(comment._parent.coordinate) row -= 1 column = column_index_from_string(col) - 1 style = ("position:absolute; margin-left:59.25pt;" "margin-top:1.5pt;width:%(width)s;height:%(height)s;" "z-index:1;visibility:hidden") % { 'height': comment._height, 'width': comment._width } attrs = { "id": "_x0000_s%04d" % idx, "type": "#_x0000_t202", "style": style, "fillcolor": "#ffffe1", "{%s}insetmode" % officens: "auto" } shape = Element("{%s}shape" % vmlns, attrs) SubElement(shape, "{%s}fill" % vmlns, {"color2": "#ffffe1"}) SubElement(shape, "{%s}shadow" % vmlns, { "color": "black", "obscured": "t" }) SubElement(shape, "{%s}path" % vmlns, {"{%s}connecttype" % officens: "none"}) textbox = SubElement(shape, "{%s}textbox" % vmlns, {"style": "mso-direction-alt:auto"}) SubElement(textbox, "div", {"style": "text-align:left"}) client_data = SubElement(shape, "{%s}ClientData" % excelns, {"ObjectType": "Note"}) SubElement(client_data, "{%s}MoveWithCells" % excelns) SubElement(client_data, "{%s}SizeWithCells" % excelns) SubElement(client_data, "{%s}AutoFill" % excelns).text = "False" SubElement(client_data, "{%s}Row" % excelns).text = "%d" % row SubElement(client_data, "{%s}Column" % excelns).text = "%d" % column return shape