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.shared_styles = IndexedList()
        self.shared_styles.add(Style())
        self.loaded_theme = None
        self._worksheet_class = worksheet_class
        self.vba_archive = None
        self.style_properties = None
        self._guess_types = guess_types
        self.data_only = data_only
        self.relationships = []
        self.drawings = []
        self.code_name = u'ThisWorkbook'

        self.encoding = encoding

        if not self.write_only:
            self.worksheets.append(self._worksheet_class(parent_workbook=self))
Beispiel #2
0
 def __init__(self):
     self.shared_strings = IndexedList()
     self.shared_styles = IndexedList()
     self.shared_styles.add(Style())
     self._local_data = DummyLocalData()
     self.encoding = "UTF-8"
     self.excel_base_date = CALENDAR_WINDOWS_1900
    class DummyWorkbook:

        _guess_types = False
        data_only = False

        def __init__(self):
            self.shared_styles = IndexedList(range(28))
            self.shared_styles.add(Style())
    class DummyWorkbook:

        _guess_types = False
        data_only = False

        def __init__(self):
            self.shared_styles = IndexedList(range(28))
            self.shared_styles.add(Style())
Beispiel #5
0
class DummyWorkbook:
    def __init__(self):
        self.shared_strings = IndexedList()
        self.shared_styles = IndexedList()
        self.shared_styles.add(Style())
        self._local_data = DummyLocalData()
        self.encoding = "UTF-8"
        self.excel_base_date = CALENDAR_WINDOWS_1900

    def get_sheet_names(self):
        return []
class DummyWorkbook:

    def __init__(self):
        self.shared_strings = IndexedList()
        self.shared_styles = IndexedList()
        self.shared_styles.add(Style())
        self._local_data = DummyLocalData()
        self.encoding = "UTF-8"
        self.excel_base_date = CALENDAR_WINDOWS_1900

    def get_sheet_names(self):
        return []
Beispiel #7
0
def test_read_standalone_worksheet(datadir):
    class DummyWb(object):

        encoding = 'utf-8'

        excel_base_date = CALENDAR_WINDOWS_1900
        _guess_types = True
        data_only = False

        def get_sheet_by_name(self, value):
            return None

        def get_sheet_names(self):
            return []

    datadir.join("reader").chdir()
    ws = None
    shared_strings = IndexedList(['hello'])

    with open('sheet2.xml') as src:
        ws = read_worksheet(src.read(), DummyWb(), 'Sheet 2', shared_strings,
                            {1: Style()})
        assert isinstance(ws, Worksheet)
        assert ws.cell('G5').value == 'hello'
        assert ws.cell('D30').value == 30
        assert ws.cell('K9').value == 0.09
Beispiel #8
0
    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.shared_styles = IndexedList()
        self.shared_styles.add(Style())
        self.loaded_theme = None
        self._worksheet_class = worksheet_class
        self.vba_archive = None
        self.style_properties = None
        self._guess_types = guess_types
        self.data_only = data_only
        self.relationships = []
        self.drawings = []

        self.encoding = encoding

        if not self.write_only:
            self.worksheets.append(self._worksheet_class(parent_workbook=self))
 def __init__(self):
     self.shared_strings = IndexedList()
     self.shared_styles = IndexedList()
     self.shared_styles.add(Style())
     self._local_data = DummyLocalData()
     self.encoding = "UTF-8"
     self.excel_base_date = CALENDAR_WINDOWS_1900
Beispiel #10
0
def create_string_table(workbook):
    """Compile the string table for a workbook."""

    strings = set()
    for sheet in workbook.worksheets:
        for cell in itervalues(sheet._cells):
            if cell.data_type == cell.TYPE_STRING and cell._value is not None:
                strings.add(cell.value)
    return IndexedList(sorted(strings))
Beispiel #11
0
    def parse_cell_xfs(self):
        """Read styles from the shared style table"""
        cell_xfs = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS)
        _styles = []

        if cell_xfs is None:  # can happen on bad OOXML writers (e.g. Gnumeric)
            return

        builtin_formats = numbers.BUILTIN_FORMATS
        xfs = safe_iterator(cell_xfs, '{%s}xf' % SHEET_MAIN_NS)
        for index, xf in enumerate(xfs):
            _style = {}

            num_fmt = xf.get('numFmtId')
            if num_fmt is not None:
                num_fmt = int(num_fmt)
                if num_fmt < 164:
                    format_code = builtin_formats.get(num_fmt, 'General')
                else:
                    fmt_code = self.custom_num_formats.get(num_fmt)
                    if fmt_code is not None:
                        format_code = fmt_code
                    else:
                        raise MissingNumberFormat('%s' % num_fmt)
                _style['number_format'] = format_code

            if bool_attrib(xf, 'applyAlignment'):
                alignment = {}
                al = xf.find('{%s}alignment' % SHEET_MAIN_NS)
                if al is not None:
                    alignment = al.attrib
                _style['alignment'] = Alignment(**alignment)

            if bool_attrib(xf, 'applyFont'):
                _style['font'] = self.font_list[int(xf.get('fontId'))].copy()

            if bool_attrib(xf, 'applyFill'):
                _style['fill'] = self.fill_list[int(xf.get('fillId'))].copy()

            if bool_attrib(xf, 'applyBorder'):
                _style['border'] = self.border_list[int(
                    xf.get('borderId'))].copy()

            if bool_attrib(xf, 'applyProtection'):
                protection = {}
                prot = xf.find('{%s}protection' % SHEET_MAIN_NS)
                if prot is not None:
                    protection.update(prot.attrib)
                _style['protection'] = Protection(**protection)
            _styles.append(Style(**_style))

        self.shared_styles = IndexedList(_styles)
Beispiel #12
0
 def __init__(self):
     self.shared_styles = IndexedList()
Beispiel #13
0
    def __init__(self, sheet):
        self.sheet = sheet
        self.authors = IndexedList()
        self.comments = []

        self.extract_comments()
 class DummyWorkbook(object):
     shared_styles = IndexedList()
     shared_styles.add(None)  # Workbooks always have a default style
Beispiel #15
0
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.shared_styles = IndexedList()
        self.shared_styles.add(Style())
        self.loaded_theme = None
        self._worksheet_class = worksheet_class
        self.vba_archive = None
        self.style_properties = None
        self._guess_types = guess_types
        self.data_only = data_only
        self.relationships = []
        self.drawings = []

        self.encoding = encoding

        if not self.write_only:
            self.worksheets.append(self._worksheet_class(parent_workbook=self))

    @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 None:
            return

        if 'activeTab' in view.attrib:
            self.active = int(view.attrib['activeTab'])

    @property
    def _local_data(self):
        return self.__thread_local_data

    @property
    def excel_base_date(self):
        return self.properties.excel_base_date

    @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.

        Returns None if no worksheet has the name specified.

        :param name: the name of the worksheet to look for
        :type name: string

        """
        try:
            return self[name]
        except KeyError:
            return

    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)
Beispiel #16
0
 def __init__(self, xml_source):
     self.root = fromstring(xml_source)
     self.style_prop = {'table': {}, 'list': IndexedList()}
     self.color_index = COLOR_INDEX
 def __init__(self):
     self.shared_styles = IndexedList(range(28))
     self.shared_styles.add(Style())
 class WB():
     style_properties = None
     shared_styles = IndexedList()
     worksheets = []
Beispiel #19
0
 def __init__(self, xml_source):
     self.root = fromstring(xml_source)
     self.shared_styles = IndexedList()
     self.cond_styles = []
     self.style_prop = {}
     self.color_index = COLOR_INDEX
    def _write_cell_xfs(self, number_format_node, fonts_node, fills_node,
                        borders_node):
        """ write styles combinations based on ids found in tables """
        # writing the cellXfs
        cell_xfs = SubElement(self._root, 'cellXfs',
                              {'count': '%d' % len(self.styles)})

        # default
        def _get_default_vals():
            return dict(numFmtId='0',
                        fontId='0',
                        fillId='0',
                        xfId='0',
                        borderId='0')

        _fonts = IndexedList()
        _fills = IndexedList()
        _borders = IndexedList()
        _custom_fmts = IndexedList()

        for st in self.styles:
            vals = _get_default_vals()

            font = st.font
            if font != DEFAULTS.font:
                if font not in _fonts:
                    font_id = _fonts.add(font)
                    self._write_font(fonts_node, st.font)
                else:
                    font_id = _fonts.index(font)
                vals['fontId'] = "%d" % (font_id + 1
                                         )  # There is one default font

                vals['applyFont'] = '1'

            border = st.border
            if st.border != DEFAULTS.border:
                if border not in _borders:
                    border_id = _borders.add(border)
                    self._write_border(borders_node, border)
                else:
                    border_id = _borders.index(border)
                vals['borderId'] = "%d" % (border_id + 1
                                           )  # There is one default border
                vals['applyBorder'] = '1'

            fill = st.fill
            if fill != DEFAULTS.fill:
                if fill not in _fills:
                    fill_id = _fills.add(st.fill)
                    fill_node = SubElement(fills_node, 'fill')
                    if isinstance(fill, PatternFill):
                        self._write_pattern_fill(fill_node, fill)
                    elif isinstance(fill, GradientFill):
                        self._write_gradient_fill(fill_node, fill)
                else:
                    fill_id = _fills.index(fill)
                vals['fillId'] = "%d" % (fill_id + 2
                                         )  # There are two default fills
                vals['applyFill'] = '1'

            nf = st.number_format
            if nf != DEFAULTS.number_format:
                fmt_id = numbers.builtin_format_id(nf)
                if fmt_id is None:
                    if nf not in _custom_fmts:
                        fmt_id = _custom_fmts.add(nf) + 165
                        self._write_number_format(number_format_node, fmt_id,
                                                  nf)
                    else:
                        fmt_id = _custom_fmts.index(nf)
                vals['numFmtId'] = '%d' % fmt_id
                vals['applyNumberFormat'] = '1'

            if st.alignment != DEFAULTS.alignment:
                vals['applyAlignment'] = '1'

            if st.protection != DEFAULTS.protection:
                vals['applyProtection'] = '1'

            node = SubElement(cell_xfs, 'xf', vals)

            if st.alignment != DEFAULTS.alignment:
                self._write_alignment(node, st.alignment)

            if st.protection != DEFAULTS.protection:
                self._write_protection(node, st.protection)

        fonts_node.attrib["count"] = "%d" % (len(_fonts) + 1)
        borders_node.attrib["count"] = "%d" % (len(_borders) + 1)
        fills_node.attrib["count"] = "%d" % (len(_fills) + 2)
        number_format_node.attrib['count'] = '%d' % len(_custom_fmts)
Beispiel #21
0
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):
            self._write_comment_shape(root, comment, i)

        return tostring(root)

    def _write_comment_shape(self, root, comment, idx):
        # get zero-indexed coordinates of the comment
        row = comment._parent.row - 1
        column = column_index_from_string(comment._parent.column) - 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%s" % (idx + 1026),
            "type": "#_x0000_t202",
            "style": style,
            "fillcolor": "#ffffe1",
            "{%s}insetmode" % officens: "auto"
        }
        shape = SubElement(root, "{%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 = str(row)
        SubElement(client_data, "{%s}Column" % excelns).text = str(column)
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.shared_styles = IndexedList()
        self.shared_styles.add(Style())
        self.loaded_theme = None
        self._worksheet_class = worksheet_class
        self.vba_archive = None
        self.style_properties = None
        self._guess_types = guess_types
        self.data_only = data_only
        self.relationships = []
        self.drawings = []
        self.code_name = u'ThisWorkbook'

        self.encoding = encoding

        if not self.write_only:
            self.worksheets.append(self._worksheet_class(parent_workbook=self))

    @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 None:
            return

        if 'activeTab' in view.attrib:
            self.active = int(view.attrib['activeTab'])

    @property
    def _local_data(self):
        return self.__thread_local_data

    @property
    def excel_base_date(self):
        return self.properties.excel_base_date

    @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.

        Returns None if no worksheet has the name specified.

        :param name: the name of the worksheet to look for
        :type name: string

        """
        try:
            return self[name]
        except KeyError:
            return

    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 DummyWorkbook(object):
     shared_styles = IndexedList()
Beispiel #24
0
    def _write_cell_xfs(self, number_format_node, fonts_node, fills_node, borders_node):
        """ write styles combinations based on ids found in tables """
        # writing the cellXfs
        cell_xfs = SubElement(self._root, 'cellXfs',
            {'count':'%d' % len(self.styles)})

        # default
        def _get_default_vals():
            return dict(numFmtId='0', fontId='0', fillId='0',
                        xfId='0', borderId='0')

        _fonts = IndexedList()
        _fills = IndexedList()
        _borders = IndexedList()
        _custom_fmts = IndexedList()

        for st in self.styles:
            vals = _get_default_vals()

            font = st.font
            if font != DEFAULTS.font:
                if font not in _fonts:
                    font_id = _fonts.add(font)
                    self._write_font(fonts_node, st.font)
                else:
                    font_id = _fonts.index(font)
                vals['fontId'] = "%d" % (font_id + 1) # There is one default font

                vals['applyFont'] = '1'

            border = st.border
            if st.border != DEFAULTS.border:
                if border not in _borders:
                    border_id = _borders.add(border)
                    self._write_border(borders_node, border)
                else:
                    border_id = _borders.index(border)
                vals['borderId'] = "%d" % (border_id + 1) # There is one default border
                vals['applyBorder'] = '1'


            fill = st.fill
            if fill != DEFAULTS.fill:
                if fill not in _fills:
                    fill_id = _fills.add(st.fill)
                    fill_node = SubElement(fills_node, 'fill')
                    if isinstance(fill, PatternFill):
                        self._write_pattern_fill(fill_node, fill)
                    elif isinstance(fill, GradientFill):
                        self._write_gradient_fill(fill_node, fill)
                else:
                    fill_id = _fills.index(fill)
                vals['fillId'] =  "%d" % (fill_id + 2) # There are two default fills
                vals['applyFill'] = '1'

            nf = st.number_format
            if nf != DEFAULTS.number_format:
                fmt_id = numbers.builtin_format_id(nf)
                if fmt_id is None:
                    if nf not in _custom_fmts:
                        fmt_id = _custom_fmts.add(nf) + 165
                        self._write_number_format(number_format_node, fmt_id, nf)
                    else:
                        fmt_id = _custom_fmts.index(nf)
                vals['numFmtId'] = '%d' % fmt_id
                vals['applyNumberFormat'] = '1'

            if st.alignment != DEFAULTS.alignment:
                vals['applyAlignment'] = '1'

            if st.protection != DEFAULTS.protection:
                vals['applyProtection'] = '1'

            node = SubElement(cell_xfs, 'xf', vals)

            if st.alignment != DEFAULTS.alignment:
                self._write_alignment(node, st.alignment)

            if st.protection != DEFAULTS.protection:
                self._write_protection(node, st.protection)

        fonts_node.attrib["count"] = "%d" % (len(_fonts) + 1)
        borders_node.attrib["count"] = "%d" % (len(_borders) + 1)
        fills_node.attrib["count"] = "%d" % (len(_fills) + 2)
        number_format_node.attrib['count'] = '%d' % len(_custom_fmts)
 def __init__(self):
     self.shared_styles = IndexedList(range(28))
     self.shared_styles.add(Style())
Beispiel #26
0
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)