class DummyWorkbook: data_only = False _colors = [] encoding = "utf8" epoch = CALENDAR_WINDOWS_1900 def __init__(self): self._differential_styles = [DifferentialStyle()] * 5 self.shared_strings = ['a'] * 30 self._fonts = IndexedList() self._fills = IndexedList() self._number_formats = IndexedList() self._borders = IndexedList([Border()] * 30) self._alignments = IndexedList() self._protections = IndexedList() self._cell_styles = IndexedList() self.vba_archive = None for i in range(29): self._cell_styles.add((StyleArray([i] * 9))) self._cell_styles.add(StyleArray( [0, 4, 6, 0, 0, 1, 0, 0, 0])) #fillId=4, borderId=6, alignmentId=1)) self.sheetnames = [] self._date_formats = set() def create_sheet(self, title): return Worksheet(self)
class DummyWorkbook: def __init__(self): self.shared_styles = IndexedList() self._cell_styles = IndexedList() self._cell_styles.add(StyleArray()) self._cell_styles.add(StyleArray([10, 0, 0, 0, 0, 0, 0, 0, 0, 0])) self.sheetnames = []
class DummyWorkbook: def __init__(self): self.shared_styles = IndexedList() self._cell_styles = IndexedList() self._cell_styles.add(StyleArray()) self._cell_styles.add(StyleArray([10,0,0,0,0,0,0,0,0,0])) self.sheetnames = []
class DummyWorkbook: def __init__(self): self.shared_styles = IndexedList() self._cell_styles = IndexedList() self._cell_styles.add(StyleId()) self._cell_styles.add(StyleId(fontId=10, numFmtId=0, borderId=0, fillId=0, protectionId=0, alignmentId=0)) def get_sheet_names(self): return []
def WorkSheetParser(): """Setup a parser instance with an empty source""" from .._reader import WorkSheetParser styles = IndexedList() for i in range(29): styles.add((StyleArray([i]*9))) styles.add(StyleArray([0,4,6,14,0,1,0,0,0])) #fillId=4, borderId=6, number_format=14 alignmentId=1)) date_formats = set([1, 29]) return WorkSheetParser(None, {0:'a'}, date_formats=date_formats)
class DummyWorkbook: def __init__(self): self.shared_styles = IndexedList() self._cell_styles = IndexedList() self._cell_styles.add(StyleId()) self._cell_styles.add( StyleId(fontId=10, numFmtId=0, borderId=0, fillId=0, protectionId=0, alignmentId=0)) def get_sheet_names(self): return []
class CellStyleList(Serialisable): tagname = "cellXfs" __attrs__ = ("count",) count = Integer(allow_none=True) xf = Sequence(expected_type=CellStyle) alignment = Sequence(expected_type=Alignment) protection = Sequence(expected_type=Protection) __elements__ = ('xf',) def __init__(self, count=None, xf=(), ): self.xf = xf @property def count(self): return len(self.xf) def __getitem__(self, idx): return self.xf[idx] def _to_array(self): """ Extract protection and alignments, convert to style array """ self.prots = IndexedList([Protection()]) self.alignments = IndexedList([Alignment()]) styles = [] # allow duplicates for xf in self.xf: style = xf.to_array() if xf.alignment is not None: style.alignmentId = self.alignments.add(xf.alignment) if xf.protection is not None: style.protectionId = self.prots.add(xf.protection) styles.append(style) return IndexedList(styles)
class CellStyleList(Serialisable): tagname = "cellXfs" __attrs__ = ("count", ) count = Integer(allow_none=True) xf = Sequence(expected_type=CellStyle) alignment = Sequence(expected_type=Alignment) protection = Sequence(expected_type=Protection) __elements__ = ('xf', ) def __init__( self, count=None, xf=(), ): self.xf = xf @property def count(self): return len(self.xf) def __getitem__(self, idx): return self.xf[idx] def _to_array(self): """ Extract protection and alignments, convert to style array """ self.prots = IndexedList([Protection()]) self.alignments = IndexedList([Alignment()]) styles = [] # allow duplicates for xf in self.xf: style = xf.to_array() if xf.alignment is not None: style.alignmentId = self.alignments.add(xf.alignment) if xf.protection is not None: style.protectionId = self.prots.add(xf.protection) styles.append(style) return IndexedList(styles)
def from_comments(cls, comments): """ Create a comment sheet from a list of comments for a particular worksheet """ authors = IndexedList() # dedupe authors and get indexes for comment in comments: comment.authorId = authors.add(comment.author) return cls(authors=AuthorList(authors), commentList=comments)
def from_comments(cls, comments): """ Create a comment sheet from a list of comments for a particular worksheet """ authors = IndexedList() # dedupe authors and get indexes for comment in comments: comment.authorId = authors.add(comment.author) return cls(authors=AuthorList(authors), commentList=comments)
class DummyWorkbook: guess_types = False data_only = False _colors = [] encoding = "utf8" def __init__(self): self._differential_styles = [] self.shared_strings = IndexedList() self.shared_strings.add("hello world") self._fonts = IndexedList() self._fills = IndexedList() self._number_formats = IndexedList() self._borders = IndexedList() self._alignments = IndexedList() self._protections = IndexedList() self._cell_styles = IndexedList() self.vba_archive = None for i in range(29): self._cell_styles.add((StyleArray([i]*9))) self._cell_styles.add(StyleArray([0,4,6,0,0,1,0,0,0])) #fillId=4, borderId=6, alignmentId=1)) self.sheetnames = [] def create_sheet(self, title): return Worksheet(self)
class DummyWorkbook: _guess_types = False data_only = False _colors = [] def __init__(self): self._differential_styles = [] self.shared_strings = IndexedList() self.shared_strings.add("hello world") self._fonts = IndexedList() self._fills = IndexedList() self._number_formats = IndexedList() self._borders = IndexedList() self._alignments = IndexedList() self._protections = IndexedList() self._cell_styles = IndexedList() self.vba_archive = None for i in range(29): self._cell_styles.add((StyleArray([i]*9))) self._cell_styles.add(StyleArray([0,4,6,0,0,1,0,0,0])) #fillId=4, borderId=6, alignmentId=1)) self.sheetnames = [] def create_sheet(self, title): return Worksheet(self)
def write_comments(self): """ Create list of comments and authors """ authors = IndexedList() # dedupe authors and get indexes for comment in self.comments: comment.authorId = authors.add(comment.author) root = CommentSheet(authors=AuthorList(authors), commentList=self.comments) return tostring(root.to_tree())
class DummyWorkbook: guess_types = False data_only = False _colors = [] encoding = "utf8" epoch = CALENDAR_MAC_1904 def __init__(self): self._differential_styles = [] self.shared_strings = IndexedList() self._fonts = IndexedList() self._fills = IndexedList() self._number_formats = IndexedList() self._borders = IndexedList() self._alignments = IndexedList() self._protections = IndexedList() self._cell_styles = IndexedList() self.vba_archive = None self._cell_styles.add(StyleArray([0, 0, 0, 14, 0, 0, 0, 0, 0])) self.sheetnames = [] def create_sheet(self, title): return Worksheet(self)
def write_comments(self): """ Create list of comments and authors """ authors = IndexedList() # dedupe authors and get indexes for comment in self.comments: comment.authorId = authors.add(comment.author) root = CommentSheet(authors=AuthorList(authors), commentList=self.comments) return tostring(root.to_tree())
def write_comments(self): """ Create list of comments and authors Sorted by row, col """ # produce xml authors = IndexedList() for comment in self.comments: comment.authorId = authors.add(comment.author) author_list = AuthorList(authors) root = CommentSheet(authors=author_list, commentList=self.comments) return tostring(root.to_tree())
def write_comments(self): """ Create list of comments and authors Sorted by row, col """ # produce xml authors = IndexedList() for comment in self.comments: comment.authorId = authors.add(comment.author) author_list = AuthorList(authors) root = CommentSheet(authors=author_list, commentList=self.comments) return tostring(root.to_tree())
def write_comments(self): """ Create list of comments and authors Sorted by row, col """ # produce xml authors = IndexedList() for _coord, cell in sorted(self.sheet._cells.items()): if cell.comment is not None: comment = Comment(ref=cell.coordinate) comment.authorId = authors.add(cell.comment.author) comment.text.t = cell.comment.text comment.height = cell.comment.height comment.width = cell.comment.width self.comments.append(comment) author_list = AuthorList(authors) root = CommentSheet(authors=author_list, commentList=self.comments) return tostring(root.to_tree())
def write_comments(self): """ Create list of comments and authors Sorted by row, col """ # produce xml authors = IndexedList() for _coord, cell in sorted(self.sheet._cells.items()): if cell.comment is not None: comment = Comment(ref=cell.coordinate) comment.authorId = authors.add(cell.comment.author) comment.text.t = cell.comment.text comment.height = cell.comment.height comment.width = cell.comment.width self.comments.append(comment) author_list = AuthorList(authors) root = CommentSheet(authors=author_list, commentList=self.comments) return tostring(root.to_tree())
class DummyWorkbook: _guess_types = False data_only = False def __init__(self): self.shared_strings = IndexedList() self.shared_strings.add("hello world") self.shared_styles = 28*[DummyStyle()] self.shared_styles.append(Style()) self._fonts = IndexedList() self._fills = IndexedList() self._number_formats = IndexedList() self._borders = IndexedList() self._alignments = IndexedList() self._protections = IndexedList() self._cell_styles = IndexedList() for i in range(29): self._cell_styles.add((StyleId(i, i, i, i, i, i))) self._cell_styles.add(StyleId(fillId=4, borderId=6, alignmentId=1, protectionId=0))
class DummyWorkbook: _guess_types = False data_only = False def __init__(self): self.shared_strings = IndexedList() self.shared_strings.add("hello world") self.shared_styles = 28*[DummyStyle()] self.shared_styles.append(Style()) self._fonts = IndexedList() self._fills = IndexedList() self._number_formats = IndexedList() self._borders = IndexedList() self._alignments = IndexedList() self._protections = IndexedList() self._cell_styles = IndexedList() self.vba_archive = None for i in range(29): self._cell_styles.add((StyleId(i, i, i, i, i, i))) self._cell_styles.add(StyleId(fillId=4, borderId=6, alignmentId=1, protectionId=0))
class Workbook(object): """Workbook is the container for all other parts of the document.""" _read_only = False _data_only = False _keep_links = True template = False path = "/xl/workbook.xml" def __init__(self, write_only=False, ): self._sheets = [] self._active_sheet_index = 0 self.defined_names = DefinedNameList() self._external_links = [] self.properties = DocumentProperties() self.security = DocumentSecurity() self.__write_only = write_only self.shared_strings = IndexedList() self._setup_styles() self.loaded_theme = None self.vba_archive = None self.is_template = False self._differential_styles = DifferentialStyleList() self.code_name = None self.excel_base_date = CALENDAR_WINDOWS_1900 self.encoding = "utf-8" if not self.write_only: self._sheets.append(Worksheet(self)) self.rels = RelationshipList() def _setup_styles(self): """Bootstrap styles""" from openpyxl.styles.alignment import Alignment from openpyxl.styles.borders import DEFAULT_BORDER from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL from openpyxl.styles.fonts import DEFAULT_FONT from openpyxl.styles.protection import Protection from openpyxl.styles.colors import COLOR_INDEX from openpyxl.styles.named_styles import NamedStyleList 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([StyleArray()]) self._named_styles = NamedStyleList() self.add_named_style(NamedStyle(font=DEFAULT_FONT, builtinId=0)) @property def read_only(self): return self._read_only @property def data_only(self): return self._data_only @property def write_only(self): return self.__write_only @property def keep_links(self): return self._keep_links @deprecated("Use the .active property") def get_active_sheet(self): """Returns the current active sheet.""" return self.active @property def active(self): """Get the currently active sheet or None""" try: return self._sheets[self._active_sheet_index] except IndexError: pass @active.setter def active(self, value): """Set the active sheet""" self._active_sheet_index = value def create_sheet(self, title=None, index=None): """Create a worksheet (at an optional index). :param title: optional title of the sheet :type tile: unicode :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 = WriteOnlyWorksheet(parent=self, title=title) else: new_ws = Worksheet(parent=self, title=title) self._add_sheet(sheet=new_ws, index=index) return new_ws def _add_sheet(self, sheet, index=None): """Add an worksheet (at an optional index).""" if not isinstance(sheet, (Worksheet, WriteOnlyWorksheet, Chartsheet)): raise TypeError("Cannot be added to a workbook") if sheet.parent != self: raise ValueError("You cannot add worksheets from another workbook.") if index is None: self._sheets.append(sheet) else: self._sheets.insert(index, sheet) def remove(self, worksheet): """Remove a worksheet from this workbook.""" self._sheets.remove(worksheet) @deprecated("Use wb.remove(worksheet) or del wb[sheetname]") def remove_sheet(self, worksheet): """Remove a worksheet from this workbook.""" self.remove(worksheet) def create_chartsheet(self, title=None, index=None): if self.read_only: raise ReadOnlyWorkbookException("Cannot create new sheet in a read-only workbook") cs = Chartsheet(parent=self, title=title) self._add_sheet(cs, index) return cs @deprecated("Use wb[sheetname]") 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 index(self, worksheet): """Return the index of a worksheet.""" return self.worksheets.index(worksheet) @deprecated("Use wb.index(worksheet)") def get_index(self, worksheet): """Return the index of the worksheet.""" return self.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) def __iter__(self): return iter(self.worksheets) @deprecated("Use wb.sheetnames") def get_sheet_names(self): return self.sheetnames @property def worksheets(self): return [s for s in self._sheets if isinstance(s, (Worksheet, ReadOnlyWorksheet, WriteOnlyWorksheet))] @property def chartsheets(self): return [s for s in self._sheets if isinstance(s, Chartsheet)] @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._sheets] def create_named_range(self, name, worksheet=None, value=None, scope=None): """Create a new named_range on a worksheet""" defn = DefinedName(name=name, localSheetId=scope) if worksheet is not None: defn.value = "{0}!{1}".format(quote_sheetname(worksheet.title), value) else: defn.value = value self.defined_names.append(defn) def add_named_style(self, style): """ Add a named style """ self._named_styles.append(style) style.bind(self) @property def named_styles(self): """ List available named styles """ return self._named_styles.names @deprecated("Use workbook.defined_names.definedName") def get_named_ranges(self): """Return all named ranges""" return self.defined_names.definedName @deprecated("Use workbook.defined_names.append") def add_named_range(self, named_range): """Add an existing named_range to the list of named_ranges.""" self.defined_names.append(named_range) @deprecated("Use workbook.defined_names[name]") def get_named_range(self, name): """Return the range specified by name.""" return self.defined_names[name] @deprecated("Use del workbook.defined_names[name]") def remove_named_range(self, named_range): """Remove a named_range from this workbook.""" del self.defined_names[named_range] @property def mime_type(self): """ The mime type is determined by whether a workbook is a template or not and whether it contains macros or not. Excel requires the file extension to match but openpyxl does not enforce this. """ ct = self.template and XLTX or XLSX if self.vba_archive: ct = self.template and XLTM or XLSM return ct 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) @property def style_names(self): """ List of named styles """ return [s.name for s in self._named_styles] def copy_worksheet(self, from_worksheet): """Copy an existing worksheet in the current workbook :warning: This function cannot copy worksheets between workbooks. worksheets can only be copied within the workbook that they belong :param from_worksheet: the worksheet to be copied from :return: copy of the initial worksheet """ if self.__write_only or self._read_only: raise ValueError("Cannot copy worksheets in read-only or write-only mode") new_title = u"{0} Copy".format(from_worksheet.title) to_worksheet = self.create_sheet(title=new_title) cp = WorksheetCopy(source_worksheet=from_worksheet, target_worksheet=to_worksheet) cp.copy_worksheet() return to_worksheet def close(self): """ Close workbook file if open. Only affects read-only and write-only modes. """ if hasattr(self, '_archive'): self._archive.close()
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.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 num_fmt_node in num_fmts: fmt_code = num_fmt_node.get('formatCode').lower() self.number_formats.append(fmt_code) 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)) if numFmtId < 164: format_code = builtin_formats.get(numFmtId, 'General') else: format_code = self.number_formats[numFmtId-165] _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)
class Workbook(object): """Workbook is the container for all other parts of the document.""" _read_only = False _data_only = False def __init__( self, write_only=False, ): self._sheets = [] self._active_sheet_index = 0 self.defined_names = DefinedNameList() self._external_links = [] self.properties = DocumentProperties() self.security = DocumentSecurity() self.__write_only = write_only self.shared_strings = IndexedList() self._setup_styles() self.loaded_theme = None self.vba_archive = None self.is_template = False self._differential_styles = DifferentialStyleList() self.code_name = None self.excel_base_date = CALENDAR_WINDOWS_1900 self.encoding = "utf-8" if not self.write_only: self._sheets.append(Worksheet(self)) self.rels = RelationshipList() def _setup_styles(self): """Bootstrap styles""" from openpyxl.styles.alignment import Alignment from openpyxl.styles.borders import DEFAULT_BORDER from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL from openpyxl.styles.fonts import DEFAULT_FONT from openpyxl.styles.protection import Protection from openpyxl.styles.colors import COLOR_INDEX from openpyxl.styles.named_styles import NamedStyles 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([StyleArray()]) self._named_styles = NamedStyles( [NamedStyle(font=DEFAULT_FONT, builtinId=0)]) @property def read_only(self): return self._read_only @property def data_only(self): return self._data_only @property def write_only(self): return self.__write_only @deprecated("Use the .active property") def get_active_sheet(self): """Returns the current active sheet.""" return self.active @property def active(self): """Get the currently active sheet""" return self._sheets[self._active_sheet_index] @active.setter def active(self, value): """Set the active sheet""" self._active_sheet_index = value def create_sheet(self, title=None, index=None): """Create a worksheet (at an optional index). :param title: optional title of the sheet :type tile: unicode :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 = WriteOnlyWorksheet(parent=self, title=title) else: new_ws = Worksheet(parent=self, title=title) self._add_sheet(sheet=new_ws, index=index) return new_ws def _add_sheet(self, sheet, index=None): """Add an worksheet (at an optional index).""" if not isinstance(sheet, (Worksheet, WriteOnlyWorksheet, Chartsheet)): raise TypeError("Cannot be added to a workbook") if sheet.parent != self: raise ValueError( "You cannot add worksheets from another workbook.") if index is None: self._sheets.append(sheet) else: self._sheets.insert(index, sheet) def remove(self, worksheet): """Remove a worksheet from this workbook.""" self._sheets.remove(worksheet) @deprecated("Use wb.remove(worksheet) or del wb[sheetname]") def remove_sheet(self, worksheet): """Remove a worksheet from this workbook.""" self.remove(worksheet) def create_chartsheet(self, title=None, index=None): if self.read_only: raise ReadOnlyWorkbookException( "Cannot create new sheet in a read-only workbook") cs = Chartsheet(parent=self, title=title) self._add_sheet(cs, index) return cs @deprecated("Use wb[sheetname]") 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 index(self, worksheet): """Return the index of a worksheet.""" return self.worksheets.index(worksheet) @deprecated("Use wb.index(worksheet)") def get_index(self, worksheet): """Return the index of the worksheet.""" return self.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) def __iter__(self): return iter(self.worksheets) @deprecated("Use wb.sheetnames") def get_sheet_names(self): return self.sheetnames @property def worksheets(self): return [ s for s in self._sheets if isinstance(s, (Worksheet, ReadOnlyWorksheet, WriteOnlyWorksheet)) ] @property def chartsheets(self): return [s for s in self._sheets if isinstance(s, Chartsheet)] @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._sheets] def create_named_range(self, name, worksheet=None, value=None, scope=None): """Create a new named_range on a worksheet""" defn = DefinedName(name=name, localSheetId=scope) if worksheet is not None: defn.value = "{0}!{1}".format(worksheet.title, value) else: defn.value = value self.defined_names.append(defn) @deprecated("Use workbook.defined_names.definedName") def get_named_ranges(self): """Return all named ranges""" return self.defined_names.definedName @deprecated("Use workbook.defined_names.append") def add_named_range(self, named_range): """Add an existing named_range to the list of named_ranges.""" self.defined_names.append(named_range) @deprecated("Use workbook.defined_names[name]") def get_named_range(self, name): """Return the range specified by name.""" return self.defined_names[name] @deprecated("Use del workbook.defined_names[name]") def remove_named_range(self, named_range): """Remove a named_range from this workbook.""" del self.defined_names[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) @property def style_names(self): """ List of named styles """ return [s.name for s in self._named_styles] def copy_worksheet(self, from_worksheet): """Copy an existing worksheet in the current workbook :warning: This function cannot copy worksheets between workbooks. worksheets can only be copied within the workbook that they belong :param from_worksheet: the worksheet to be copied from :return: copy of the initial worksheet """ if self.__write_only or self._read_only: raise ValueError( "Cannot copy worksheets in read-only or write-only mode") new_title = "{0} Copy".format(from_worksheet.title) to_worksheet = self.create_sheet(title=new_title) cp = WorksheetCopy(source_worksheet=from_worksheet, target_worksheet=to_worksheet) cp.copy_worksheet() return to_worksheet
class Workbook(object): """Workbook is the container for all other parts of the document.""" _read_only = False _data_only = False template = False path = "/xl/workbook.xml" def __init__( self, write_only=False, iso_dates=False, ): self._sheets = [] self._pivots = [] self._active_sheet_index = 0 self.defined_names = DefinedNameList() self._external_links = [] self.properties = DocumentProperties() self.security = DocumentSecurity() self.__write_only = write_only self.shared_strings = IndexedList() self._setup_styles() self.loaded_theme = None self.vba_archive = None self.is_template = False self._differential_styles = DifferentialStyleList() self.code_name = None self.epoch = CALENDAR_WINDOWS_1900 self.encoding = "utf-8" self.iso_dates = iso_dates if not self.write_only: self._sheets.append(Worksheet(self)) self.rels = RelationshipList() self.calculation = CalcProperties() self.views = [BookView()] def _setup_styles(self): """Bootstrap styles""" 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([StyleArray()]) self._named_styles = NamedStyleList() self.add_named_style(NamedStyle(font=copy(DEFAULT_FONT), builtinId=0)) self._table_styles = TableStyleList() @property def read_only(self): return self._read_only @property def data_only(self): return self._data_only @property def write_only(self): return self.__write_only @property def guess_types(self): return getattr(self, '__guess_types', False) @guess_types.setter def guess_types(self, value): self.__guess_types = value @deprecated("Use the .active property") def get_active_sheet(self): """Returns the current active sheet.""" return self.active @property def excel_base_date(self): return self.epoch @property def active(self): """Get the currently active sheet or None :type: :class:`openpyxl.worksheet.worksheet.Worksheet` """ try: return self._sheets[self._active_sheet_index] except IndexError: pass @active.setter def active(self, value): """Set the active sheet""" if not isinstance(value, (_WorkbookChild, INTEGER_TYPES)): raise TypeError( "Value must be either a worksheet, chartsheet or numerical index" ) if isinstance(value, INTEGER_TYPES): self._active_sheet_index = value return #if self._sheets and 0 <= value < len(self._sheets): #value = self._sheets[value] #else: #raise ValueError("Sheet index is outside the range of possible values", value) if value not in self._sheets: raise ValueError("Worksheet is not in the workbook") if value.sheet_state != "visible": raise ValueError("Only visible sheets can be made active") idx = self._sheets.index(value) self._active_sheet_index = idx def create_sheet(self, title=None, index=None): """Create a worksheet (at an optional index). :param title: optional title of the sheet :type title: unicode :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 = WriteOnlyWorksheet(parent=self, title=title) else: new_ws = Worksheet(parent=self, title=title) self._add_sheet(sheet=new_ws, index=index) return new_ws def _add_sheet(self, sheet, index=None): """Add an worksheet (at an optional index).""" if not isinstance(sheet, (Worksheet, WriteOnlyWorksheet, Chartsheet)): raise TypeError("Cannot be added to a workbook") if sheet.parent != self: raise ValueError( "You cannot add worksheets from another workbook.") if index is None: self._sheets.append(sheet) else: self._sheets.insert(index, sheet) def remove(self, worksheet): """Remove `worksheet` from this workbook.""" idx = self._sheets.index(worksheet) localnames = self.defined_names.localnames(scope=idx) for name in localnames: self.defined_names.delete(name, scope=idx) self._sheets.remove(worksheet) @deprecated("Use wb.remove(worksheet) or del wb[sheetname]") def remove_sheet(self, worksheet): """Remove `worksheet` from this workbook.""" self.remove(worksheet) def create_chartsheet(self, title=None, index=None): if self.read_only: raise ReadOnlyWorkbookException( "Cannot create new sheet in a read-only workbook") cs = Chartsheet(parent=self, title=title) self._add_sheet(cs, index) return cs @deprecated("Use wb[sheetname]") 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 index(self, worksheet): """Return the index of a worksheet.""" return self.worksheets.index(worksheet) @deprecated("Use wb.index(worksheet)") def get_index(self, worksheet): """Return the index of the worksheet.""" return self.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 + self.chartsheets: 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) def __iter__(self): return iter(self.worksheets) @deprecated("Use wb.sheetnames") def get_sheet_names(self): return self.sheetnames @property def worksheets(self): """A list of sheets in this workbook :type: list of :class:`openpyxl.worksheet.worksheet.Worksheet` """ return [ s for s in self._sheets if isinstance(s, (Worksheet, ReadOnlyWorksheet, WriteOnlyWorksheet)) ] @property def chartsheets(self): """A list of Chartsheets in this workbook :type: list of :class:`openpyxl.chartsheet.chartsheet.Chartsheet` """ return [s for s in self._sheets if isinstance(s, Chartsheet)] @property def sheetnames(self): """Returns the list of the names of worksheets in this workbook. Names are returned in the worksheets order. :type: list of strings """ return [s.title for s in self._sheets] def create_named_range(self, name, worksheet=None, value=None, scope=None): """Create a new named_range on a worksheet""" defn = DefinedName(name=name, localSheetId=scope) if worksheet is not None: defn.value = "{0}!{1}".format(quote_sheetname(worksheet.title), value) else: defn.value = value self.defined_names.append(defn) def add_named_style(self, style): """ Add a named style """ self._named_styles.append(style) style.bind(self) @property def named_styles(self): """ List available named styles """ return self._named_styles.names @deprecated("Use workbook.defined_names.definedName") def get_named_ranges(self): """Return all named ranges""" return self.defined_names.definedName @deprecated("Use workbook.defined_names.append") def add_named_range(self, named_range): """Add an existing named_range to the list of named_ranges.""" self.defined_names.append(named_range) @deprecated("Use workbook.defined_names[name]") def get_named_range(self, name): """Return the range specified by name.""" return self.defined_names[name] @deprecated("Use del workbook.defined_names[name]") def remove_named_range(self, named_range): """Remove a named_range from this workbook.""" del self.defined_names[named_range] @property def mime_type(self): """ The mime type is determined by whether a workbook is a template or not and whether it contains macros or not. Excel requires the file extension to match but openpyxl does not enforce this. """ ct = self.template and XLTX or XLSX if self.vba_archive: ct = self.template and XLTM or XLSM return ct 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 and not self.worksheets: self.create_sheet() save_workbook(self, filename) @property def style_names(self): """ List of named styles """ return [s.name for s in self._named_styles] def copy_worksheet(self, from_worksheet): """Copy an existing worksheet in the current workbook .. warning:: This function cannot copy worksheets between workbooks. worksheets can only be copied within the workbook that they belong :param from_worksheet: the worksheet to be copied from :return: copy of the initial worksheet """ if self.__write_only or self._read_only: raise ValueError( "Cannot copy worksheets in read-only or write-only mode") new_title = u"{0} Copy".format(from_worksheet.title) to_worksheet = self.create_sheet(title=new_title) cp = WorksheetCopy(source_worksheet=from_worksheet, target_worksheet=to_worksheet) cp.copy_worksheet() return to_worksheet def close(self): """ Close workbook file if open. Only affects read-only and write-only modes. """ if hasattr(self, '_archive'): self._archive.close()
class SharedStylesParser(object): def __init__(self, xml_source): self.root = fromstring(xml_source) self.cell_styles = IndexedList() self.differential_styles = [] 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.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() self.parse_named_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.differential_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 """ node = self.root.find("{%s}cellStyleXfs" % SHEET_MAIN_NS) styles = self._parse_xfs(node) names = self._parse_style_names() for style in names.values(): _id = styles[style.xfId] style.border = self.border_list[_id.borderId] style.fill = self.fill_list[_id.fillId] style.font = self.font_list[_id.fontId] if _id.alignmentId: style.alignment = self.alignments[_id.alignmentId] if _id.protectionId: style.protection = self.protections[_id.protectionId] self.named_styles = names def _parse_style_names(self): """ Extract style names. There can be duplicates in which case last wins """ node = self.root.find("{%s}cellStyles" % SHEET_MAIN_NS) names = {} for _name in node: name = _name.get("name") style = NamedStyle(name=name, builtinId=_name.get("builtinId"), hidden=_name.get("hidden")) style.xfId = int(_name.get("xfId")) names[name] = style return names def parse_cell_styles(self): """ Extract individual cell styles """ node = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS) if node is not None: self.cell_styles = self._parse_xfs(node) def _parse_xfs(self, node): """Read styles from the shared style table""" _style_ids = [] xfs = safe_iterator(node, '{%s}xf' % SHEET_MAIN_NS) for xf in xfs: style = StyleArray.from_tree(xf) al = xf.find('{%s}alignment' % SHEET_MAIN_NS) if al is not None: alignment = Alignment(**al.attrib) style.alignmentId = self.alignments.add(alignment) prot = xf.find('{%s}protection' % SHEET_MAIN_NS) if prot is not None: protection = Protection(**prot.attrib) style.protectionId = self.protections.add(protection) numFmtId = int(xf.get("numFmtId", 0)) # check for custom formats and normalise indices if numFmtId in self.custom_number_formats: format_code = self.custom_number_formats[numFmtId] style.numFmtId = self.number_formats.add(format_code) + 164 _style_ids.append(style) return IndexedList(_style_ids)
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.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 num_fmt_node in num_fmts: fmt_code = num_fmt_node.get('formatCode').lower() self.number_formats.append(fmt_code) 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)) if numFmtId < 164: format_code = builtin_formats.get(numFmtId, 'General') else: try: format_code = self.number_formats[numFmtId - 164] except IndexError: if self.number_formats: format_code = self.number_formats[0] else: format_code = None _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)
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.conditional_formats = [] 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 openpyxl.styles.alignment import Alignment from openpyxl.styles.borders import DEFAULT_BORDER from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL from openpyxl.styles.fonts import DEFAULT_FONT from openpyxl.styles.protection import Protection from openpyxl.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(0, 0, 0, 0, 0, 0)]) @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.write_only: save_dump(self, filename) else: save_workbook(self, filename)
class Workbook(object): """Workbook is the container for all other parts of the document.""" def __init__(self, optimized_write=False, encoding='utf-8', guess_types=False, data_only=False, read_only=False, write_only=False): self._sheets = [] self._active_sheet_index = 0 self._named_ranges = [] self._external_links = [] self.properties = DocumentProperties() self.security = DocumentSecurity() self.__write_only = write_only or optimized_write self.__read_only = read_only self.shared_strings = IndexedList() self._setup_styles() self.loaded_theme = None self.vba_archive = None self.is_template = False self._differential_styles = [] self._guess_types = guess_types self.data_only = data_only self._drawings = [] self._charts = [] self._images = [] self.code_name = None self.excel_base_date = CALENDAR_WINDOWS_1900 self.encoding = encoding if not self.write_only: self._sheets.append(Worksheet(self)) def _setup_styles(self): """Bootstrap styles""" from openpyxl.styles.alignment import Alignment from openpyxl.styles.borders import DEFAULT_BORDER from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL from openpyxl.styles.fonts import DEFAULT_FONT from openpyxl.styles.protection import Protection from openpyxl.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([StyleArray()]) self._named_styles = {'Normal': NamedStyle(font=DEFAULT_FONT)} @property def read_only(self): return self.__read_only @property def write_only(self): return self.__write_only @deprecated("Use the .active property") def get_active_sheet(self): """Returns the current active sheet.""" return self.active @property def active(self): """Get the currently active sheet""" return self._sheets[self._active_sheet_index] @active.setter def active(self, value): """Set the active sheet""" self._active_sheet_index = value def create_sheet(self, title=None, index=None): """Create a worksheet (at an optional index). :param title: optional title of the sheet :type tile: unicode :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 = WriteOnlyWorksheet(parent_workbook=self, title=title) else: new_ws = Worksheet(parent=self, title=title) self._add_sheet(sheet=new_ws, index=index) return new_ws def _add_sheet(self, sheet, index=None): """Add an worksheet (at an optional index).""" if not isinstance(sheet, (Worksheet, Chartsheet)): raise TypeError("Cannot be added to a workbook") if sheet.parent != self: raise ValueError( "You cannot add worksheets from another workbook.") if index is None: self._sheets.append(sheet) else: self._sheets.insert(index, sheet) def remove_sheet(self, worksheet): """Remove a worksheet from this workbook.""" self._sheets.remove(worksheet) def create_chartsheet(self, title=None, index=None): if self.read_only: raise ReadOnlyWorkbookException( "Cannot create new sheet in a read-only workbook") cs = Chartsheet(parent=self, title=title) self._add_sheet(cs, index=None) return cs 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 worksheets(self): return [s for s in self._sheets if isinstance(s, Worksheet)] @property def chartsheets(self): return [s for s in self._sheets if isinstance(s, Chartsheet)] @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._sheets] def create_named_range(self, name, worksheet, range, scope=None): """Create a new named_range on a worksheet""" 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)
class ExcelRenderer(mistune.Renderer): """ Renders a Markdown formatted text to an OOXML worksheet. Each block item (paragraph, headline, code...) is rendered in a separate cell, while inline elements (including newlines) are placed into cells as rich text. Some features of Markdown are not supported: - Tables - Images - HTML tags - Footnotes (for the moment) - List item continuation after a nested list (is considered as a new list item) Links are output in text as footnotes and target is placed at the end, after a blank cell. When using this renderer, the markdown processor will output an instance of :py:class:`Collector`. Use its :py:func:`~Collector.render` function to get the result in a worksheet. .. sourcecode:: python from openpyxl import Workbook renderer = ExcelRenderer() markdown = mistune.Markdown(renderer=renderer) collector = markdown(text) wb = Workbook() ws = wb.active collector.render(ws) wb.save(<output path>) """ def __init__(self, *args, **kwargs): super().__init__(*args, **kwargs) self.links = IndexedList() def placeholder(self): return Collector(self) # ==== Block elements ==== def _get_block(self, bucket): if bucket.inline is not None: return BlockCollector(bucket.inline) else: return bucket.block def block_code(self, code, language=None): #print("block_code: ", code, language) return self._get_block(code).nest("code") def block_quote(self, text): #print("block_quote: ", text) return self._get_block(text).nest("quote") def block_html(self, html): #print("block_html: ", html) return None def header(self, text, level, raw=None): #print("header: ", text, level, raw) return self._get_block(text).nest("h" + str(level)) def hrule(self): #print("hrule: ") return BlockCollector(InlineCollector("")).nest("hrule") def list(self, body, ordered=True): #print("list: ", body, ordered) return self._get_block(body).nest("list") def list_item(self, text): #print("list_item: ", text) return self._get_block(text).nest("list_item") def paragraph(self, text): #print("paragraph: ", text) return self._get_block(text).nest("paragraph") def table(self, header, body): #print("table: ", header, body) return None def table_row(self, content): #print("table_row", content) return None def table_cell(self, content, **flags): #print("table_cell: ", content, flags) return None # ==== Inline elements ==== def autolink(self, link, is_email=False): #print("autolink: ", link, is_email) return link.inline.apply_marker("l") def codespan(self, text): #print("codespan: ", text) return InlineCollector(text).apply_marker("c") def double_emphasis(self, text): return text.inline.apply_marker("i").apply_marker("b") def emphasis(self, text): #print("emphasis: ", text) return text.inline.apply_marker("i") def image(self, src, title, alt_text): return None def linebreak(self): return InlineCollector("\n") def newline(self): return None def link(self, link, title, content): #print("link: ", link, title, content) self.links.add(link) idx = self.links.index(link) + 1 return content.inline.apply_marker("l").append("[{:d}]".format(idx)) def strikethrough(self, text): #print("strikethrough: ", text) return text.inline.apply_marker("s") def text(self, text): #print("text: ", text) return InlineCollector(text) def inline_html(self, text): return text.inline def terminate(self): """ Called by the collector when the render function is called """ collector = Collector(self) if len(self.links) > 0: # Blank line collector += BlockCollector(InlineCollector("")) # Each link in a special cell with format # "[<idx>] <link>" for idx, link in enumerate(self.links, start=1): content = InlineCollector("[{:d}] ".format(idx)) content += InlineCollector(link).apply_marker("l") block = BlockCollector(content).nest("link") collector += block # Reset links self.links = IndexedList() return collector
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 openpyxl.styles.alignment import Alignment from openpyxl.styles.borders import DEFAULT_BORDER from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL from openpyxl.styles.fonts import DEFAULT_FONT from openpyxl.styles.protection import Protection from openpyxl.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.border] 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)
class SharedStylesParser(object): def __init__(self, xml_source): self.root = fromstring(xml_source) self.cell_styles = IndexedList() self.differential_styles = [] 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.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() self.parse_named_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.differential_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 """ node = self.root.find("{%s}cellStyleXfs" % SHEET_MAIN_NS) styles = self._parse_xfs(node) names = self._parse_style_names() for style in names.values(): _id = styles[style.xfId] style.border = self.border_list[_id.borderId] style.fill = self.fill_list[_id.fillId] style.font = self.font_list[_id.fontId] if _id.alignmentId: style.alignment = self.alignments[_id.alignmentId] if _id.protectionId: style.protection = self.protections[_id.protectionId] self.named_styles = names def _parse_style_names(self): """ Extract style names. There can be duplicates in which case last wins """ node = self.root.find("{%s}cellStyles" % SHEET_MAIN_NS) names = {} for _name in safe_iterator(node, '{%s}cellStyle' % SHEET_MAIN_NS): name = _name.get("name") style = NamedStyle(name=name, builtinId=_name.get("builtinId"), hidden=_name.get("hidden") ) style.xfId = int(_name.get("xfId")) names[name] = style return names def parse_cell_styles(self): """ Extract individual cell styles """ node = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS) if node is not None: self.cell_styles = self._parse_xfs(node) def _parse_xfs(self, node): """Read styles from the shared style table""" _style_ids = [] xfs = safe_iterator(node, '{%s}xf' % SHEET_MAIN_NS) for xf in xfs: style = StyleArray.from_tree(xf) al = xf.find('{%s}alignment' % SHEET_MAIN_NS) if al is not None: alignment = Alignment(**al.attrib) style.alignmentId = self.alignments.add(alignment) prot = xf.find('{%s}protection' % SHEET_MAIN_NS) if prot is not None: protection = Protection(**prot.attrib) style.protectionId = self.protections.add(protection) numFmtId = int(xf.get("numFmtId", 0)) # check for custom formats and normalise indices if numFmtId in self.custom_number_formats: format_code = self.custom_number_formats[numFmtId] style.numFmtId = self.number_formats.add(format_code) + 164 _style_ids.append(style) return IndexedList(_style_ids)
class Workbook(object): """Workbook is the container for all other parts of the document.""" def __init__(self, optimized_write=False, encoding='utf-8', 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.security = DocumentSecurity() self.__write_only = write_only or optimized_write self.__read_only = read_only self.shared_strings = IndexedList() self._setup_styles() self.loaded_theme = None self.vba_archive = None self.is_template = False self._differential_styles = [] self._guess_types = guess_types self.data_only = data_only self._drawings = [] self._charts = [] self._images = [] self.code_name = None self.excel_base_date = CALENDAR_WINDOWS_1900 self.encoding = encoding if not self.write_only: self.worksheets.append(Worksheet(parent_workbook=self)) def _setup_styles(self): """Bootstrap styles""" from openpyxl.styles.alignment import Alignment from openpyxl.styles.borders import DEFAULT_BORDER from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL from openpyxl.styles.fonts import DEFAULT_FONT from openpyxl.styles.protection import Protection from openpyxl.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()]) self._named_styles = {'Normal': NamedStyle(font=DEFAULT_FONT)} @property def read_only(self): return self.__read_only @property def write_only(self): return self.__write_only @deprecated("Use the .active property") 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 = WriteOnlyWorksheet(parent_workbook=self, title=title) else: new_ws = Worksheet(parent_workbook=self, title=title) 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).""" cls = Worksheet if self.write_only: cls = WriteOnlyWorksheet if not isinstance(worksheet, cls): raise TypeError("The parameter you have given is not of the type '%s'" % cls.__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""" 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)