class RelativeRect(Serialisable): tagname = "rect" namespace = DRAWING_NS l = MinMax(min=0, max=100, allow_none=True) left = Alias('l') t = MinMax(min=0, max=100, allow_none=True) top = Alias('t') r = MinMax(min=0, max=100, allow_none=True) right = Alias('r') b = MinMax(min=0, max=100, allow_none=True) bottom = Alias('b') def __init__( self, l=None, t=None, r=None, b=None, ): self.l = l self.t = t self.r = r self.b = b
class DateGroupItem(Serialisable): tagname = "dateGroupItem" year = Integer() month = MinMax(min=1, max=12, allow_none=True) day = MinMax(min=1, max=31, allow_none=True) hour = MinMax(min=0, max=23, allow_none=True) minute = MinMax(min=0, max=59, allow_none=True) second = Integer(min=0, max=59, allow_none=True) dateTimeGrouping = Set( values=(['year', 'month', 'day', 'hour', 'minute', 'second'])) def __init__( self, year=None, month=None, day=None, hour=None, minute=None, second=None, dateTimeGrouping=None, ): self.year = year self.month = month self.day = day self.hour = hour self.minute = minute self.second = second self.dateTimeGrouping = dateTimeGrouping
class Color(Serialisable): """Named colors for use in styles.""" tagname = "color" rgb = RGB() indexed = Integer() auto = Bool() theme = Integer() tint = MinMax(min=-1, max=1, expected_type=float) type = String() def __init__(self, rgb=BLACK, indexed=None, auto=None, theme=None, tint=0.0, index=None, type='rgb'): if index is not None: indexed = index if indexed is not None: self.type = 'indexed' self.indexed = indexed elif theme is not None: self.type = 'theme' self.theme = theme elif auto is not None: self.type = 'auto' self.auto = auto else: self.rgb = rgb self.type = 'rgb' self.tint = tint @property def value(self): return getattr(self, self.type) @value.setter def value(self, value): setattr(self, self.type, value) def __iter__(self): attrs = [(self.type, self.value)] if self.tint != 0: attrs.append(('tint', self.tint)) for k, v in attrs: yield k, safe_string(v) @property def index(self): # legacy return self.value def __add__(self, other): """ Adding colours is undefined behaviour best do nothing """ if not isinstance(other, Color): return super(Color, self).__add__(other) return self
class Color(HashableObject): """Named colors for use in styles.""" tagname = "color" rgb = RGB() indexed = Integer() auto = Bool() theme = Integer() tint = MinMax(min=-1, max=1, expected_type=float) type = String() __fields__ = ('rgb', 'indexed', 'auto', 'theme', 'tint', 'type') def __init__(self, rgb=BLACK, indexed=None, auto=None, theme=None, tint=0.0, index=None, type='rgb'): if index is not None: indexed = index if indexed is not None: self.type = 'indexed' self.indexed = indexed elif theme is not None: self.type = 'theme' self.theme = theme elif auto is not None: self.type = 'auto' self.auto = auto else: self.rgb = rgb self.type = 'rgb' self.tint = tint @property def value(self): return getattr(self, self.type) @value.setter def value(self, value): setattr(self, self.type, value) def __iter__(self): attrs = [(self.type, self.value)] if self.tint != 0: attrs.append(('tint', self.tint)) for k, v in attrs: yield k, safe_string(v) @property def index(self): # legacy return self.value
class HSLColor(Serialisable): tagname = "hslClr" hue = Integer() sat = MinMax(min=0, max=100) lum = MinMax(min=0, max=100) #TODO add color transform options def __init__(self, hue=None, sat=None, lum=None, ): self.hue = hue self.sat = sat self.lum = lum
class RGBPercent(Serialisable): tagname = "rgbClr" r = MinMax(min=0, max=100) g = MinMax(min=0, max=100) b = MinMax(min=0, max=100) #TODO add color transform options def __init__(self, r=None, g=None, b=None, ): self.r = r self.g = g self.b = b
class Stop(Serialisable): tagname = "stop" position = MinMax(min=0, max=1) color = ColorDescriptor() def __init__(self, color, position): self.position = position self.color = color
class Alignment(HashableObject): """Alignment options for use in styles.""" __fields__ = ( 'horizontal', 'vertical', 'textRotation', 'wrapText', 'shrinkToFit', 'indent', 'relativeIndent', 'justifyLastLine', 'readingOrder', ) horizontal = Set(values=horizontal_alignments) vertical = Set(values=vertical_aligments) textRotation = MinMax(min=0, max=180) text_rotation = Alias('textRotation') wrapText = Bool() wrap_text = Alias('wrapText') shrinkToFit = Bool() shrink_to_fit = Alias('shrinkToFit') indent = Min(min=0) relativeIndent = Min(min=0) justifyLastLine = Bool() readingOrder = Min(min=0) def __init__(self, horizontal='general', vertical='bottom', textRotation=0, wrapText=False, shrinkToFit=False, indent=0, relativeIndent=0, justifyLastLine=False, readingOrder=0, text_rotation=None, wrap_text=None, shrink_to_fit=None): self.horizontal = horizontal self.vertical = vertical self.indent = indent self.relativeIndent = relativeIndent self.justifyLastLine = justifyLastLine self.readingOrder = readingOrder if text_rotation is not None: textRotation = text_rotation self.textRotation = textRotation if wrap_text is not None: wrapText = wrap_text self.wrapText = wrapText if shrink_to_fit is not None: shrinkToFit = shrink_to_fit self.shrinkToFit = shrinkToFit
class ConnectionSite(Serialisable): ang = MinMax(min=0, max=360) # guess work, can also be a name pos = Typed(expected_type=AdjPoint2D, ) def __init__(self, ang=None, pos=None, ): self.ang = ang self.pos = pos
class GradientStop(Serialisable): tagname = "gradStop" pos = MinMax(min=0, max=100, allow_none=True) # Color Choice Group def __init__(self, pos=None, ): self.pos = pos
class Font(Serialisable): tagname = "latin" namespace = DRAWING_NS typeface = String() panose = HexBinary(allow_none=True) pitchFamily = MinMax(min=0, max=52, allow_none=True) charset = Integer(allow_none=True) def __init__(self, typeface=None, panose=None, pitchFamily=None, charset=None, ): self.typeface = typeface self.panose = panose self.pitchFamily = pitchFamily self.charset = charset
class GradientStop(Serialisable): tagname = "gs" namespace = DRAWING_NS pos = MinMax(min=0, max=100000, allow_none=True) # Color Choice Group scrgbClr = Typed(expected_type=RGBPercent, allow_none=True) RGBPercent = Alias('scrgbClr') srgbClr = NestedValue( expected_type=str, allow_none=True) # needs pattern and can have transform RGB = Alias('srgbClr') hslClr = Typed(expected_type=HSLColor, allow_none=True) sysClr = Typed(expected_type=SystemColor, allow_none=True) schemeClr = Typed(expected_type=SchemeColor, allow_none=True) prstClr = NestedNoneSet(values=PRESET_COLORS) __elements__ = ('scrgbClr', 'srgbClr', 'hslClr', 'sysClr', 'schemeClr', 'prstClr') def __init__( self, pos=None, scrgbClr=None, srgbClr=None, hslClr=None, sysClr=None, schemeClr=None, prstClr=None, ): if pos is None: pos = 0 self.pos = pos self.scrgbClr = scrgbClr self.srgbClr = srgbClr self.hslClr = hslClr self.sysClr = sysClr self.schemeClr = schemeClr self.prstClr = prstClr
class CellRange(Serialisable): """ Represents a range in a sheet: title and coordinates. This object is used to perform operations on ranges, like: - shift, expand or shrink - union/intersection with another sheet range, We can check whether a range is: - equal or not equal to another, - disjoint of another, - contained in another. We can get: - the size of a range. - the range bounds (vertices) - the coordinates, - the string representation, """ min_col = MinMax(min=1, max=18278, expected_type=int) min_row = MinMax(min=1, max=1048576, expected_type=int) max_col = MinMax(min=1, max=18278, expected_type=int) max_row = MinMax(min=1, max=1048576, expected_type=int) def __init__(self, range_string=None, min_col=None, min_row=None, max_col=None, max_row=None, title=None): if range_string is not None: if "!" in range_string: title, (min_col, min_row, max_col, max_row) = range_to_tuple(range_string) else: min_col, min_row, max_col, max_row = range_boundaries( range_string) self.min_col = min_col self.min_row = min_row self.max_col = max_col self.max_row = max_row self.title = title if min_col > max_col: fmt = "{max_col} must be greater than {min_col}" raise ValueError(fmt.format(min_col=min_col, max_col=max_col)) if min_row > max_row: fmt = "{max_row} must be greater than {min_row}" raise ValueError(fmt.format(min_row=min_row, max_row=max_row)) @property def bounds(self): """ Vertices of the range as a tuple """ return self.min_col, self.min_row, self.max_col, self.max_row @property def coord(self): """ Excel-style representation of the range """ fmt = "{min_col}{min_row}:{max_col}{max_row}" if (self.min_col == self.max_col and self.min_row == self.max_row): fmt = "{min_col}{min_row}" return fmt.format(min_col=get_column_letter(self.min_col), min_row=self.min_row, max_col=get_column_letter(self.max_col), max_row=self.max_row) @property def rows(self): """ Return cell coordinates as rows """ for row in range(self.min_row, self.max_row + 1): yield [(row, col) for col in range(self.min_col, self.max_col + 1)] @property def cols(self): """ Return cell coordinates as columns """ for col in range(self.min_col, self.max_col + 1): yield [(row, col) for row in range(self.min_row, self.max_row + 1)] @property def cells(self): from itertools import product return product(range(self.min_row, self.max_row + 1), range(self.min_col, self.max_col + 1)) def _check_title(self, other): """ Check whether comparisons between ranges are possible. Cannot compare ranges from different worksheets Skip if the range passed in has no title. """ if not isinstance(other, CellRange): raise TypeError(repr(type(other))) if other.title and self.title != other.title: raise ValueError( "Cannot work with ranges from different worksheets") def __repr__(self): fmt = u"<{cls} {coord}>" if self.title: fmt = u"<{cls} {title!r}!{coord}>" return fmt.format(cls=self.__class__.__name__, title=self.title, coord=self.coord) def __str__(self): fmt = "{coord}" title = self.title if title: fmt = u"{title}!{coord}" title = quote_sheetname(title) return fmt.format(title=title, coord=self.coord) def __copy__(self): return self.__class__(min_col=self.min_col, min_row=self.min_row, max_col=self.max_col, max_row=self.max_row, title=self.title) def shift(self, col_shift=0, row_shift=0): """ Shift the focus of the range according to the shift values (*col_shift*, *row_shift*). :type col_shift: int :param col_shift: number of columns to be moved by, can be negative :type row_shift: int :param row_shift: number of rows to be moved by, can be negative :raise: :class:`ValueError` if any row or column index < 1 """ if (self.min_col + col_shift <= 0 or self.min_row + row_shift <= 0): raise ValueError( "Invalid shift value: col_shift={0}, row_shift={1}".format( col_shift, row_shift)) self.min_col += col_shift self.min_row += row_shift self.max_col += col_shift self.max_row += row_shift def __ne__(self, other): """ Test whether the ranges are not equal. :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range :return: ``True`` if *range* != *other*. """ try: self._check_title(other) except ValueError: return True return (other.min_row != self.min_row or self.max_row != other.max_row or other.min_col != self.min_col or self.max_col != other.max_col) def __eq__(self, other): """ Test whether the ranges are equal. :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range :return: ``True`` if *range* == *other*. """ return not self.__ne__(other) def issubset(self, other): """ Test whether every cell in this range is also in *other*. :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range :return: ``True`` if *range* <= *other*. """ self._check_title(other) return ( (other.min_row <= self.min_row <= self.max_row <= other.max_row) and (other.min_col <= self.min_col <= self.max_col <= other.max_col)) __le__ = issubset def __lt__(self, other): """ Test whether *other* contains every cell of this range, and more. :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range :return: ``True`` if *range* < *other*. """ return self.__le__(other) and self.__ne__(other) def issuperset(self, other): """ Test whether every cell in *other* is in this range. :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range :return: ``True`` if *range* >= *other* (or *other* in *range*). """ self._check_title(other) return ( (self.min_row <= other.min_row <= other.max_row <= self.max_row) and (self.min_col <= other.min_col <= other.max_col <= self.max_col)) __ge__ = issuperset def __contains__(self, coord): """ Check whether the range contains a particular cell coordinate """ cr = CellRange(coord) if cr.title is None: cr.title = self.title return self.issuperset(cr) def __gt__(self, other): """ Test whether this range contains every cell in *other*, and more. :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range :return: ``True`` if *range* > *other*. """ return self.__ge__(other) and self.__ne__(other) def isdisjoint(self, other): """ Return ``True`` if this range has no cell in common with *other*. Ranges are disjoint if and only if their intersection is the empty range. :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range. :return: ``True`` if the range has no cells in common with other. """ self._check_title(other) # Sort by top-left vertex if self.bounds > other.bounds: self, other = other, self return (self.max_col < other.min_col or self.max_row < other.min_row or other.max_row < self.min_row) def intersection(self, other): """ Return a new range with cells common to this range and *other* :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range. :return: the intersecting sheet range. :raise: :class:`ValueError` if the *other* range doesn't intersect with this range. """ if self.isdisjoint(other): raise ValueError("Range {0} doesn't intersect {0}".format( self, other)) min_row = max(self.min_row, other.min_row) max_row = min(self.max_row, other.max_row) min_col = max(self.min_col, other.min_col) max_col = min(self.max_col, other.max_col) return CellRange(min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row) __and__ = intersection def union(self, other): """ Return the minimal superset of this range and *other*. This new range will contain all cells from this range, *other*, and any additional cells required to form a rectangular ``CellRange``. :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range. :return: a ``CellRange`` that is a superset of this and *other*. """ self._check_title(other) min_row = min(self.min_row, other.min_row) max_row = max(self.max_row, other.max_row) min_col = min(self.min_col, other.min_col) max_col = max(self.max_col, other.max_col) return CellRange(min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row, title=self.title) __or__ = union def __iter__(self): """ For use as a dictionary elsewhere in the library. """ for x in self.__attrs__: if x == "title": continue v = getattr(self, x) yield x, v def expand(self, right=0, down=0, left=0, up=0): """ Expand the range by the dimensions provided. :type right: int :param right: expand range to the right by this number of cells :type down: int :param down: expand range down by this number of cells :type left: int :param left: expand range to the left by this number of cells :type up: int :param up: expand range up by this number of cells """ self.min_col -= left self.min_row -= up self.max_col += right self.max_row += down def shrink(self, right=0, bottom=0, left=0, top=0): """ Shrink the range by the dimensions provided. :type right: int :param right: shrink range from the right by this number of cells :type down: int :param down: shrink range from the top by this number of cells :type left: int :param left: shrink range from the left by this number of cells :type up: int :param up: shrink range from the bottown by this number of cells """ self.min_col += left self.min_row += top self.max_col -= right self.max_row -= bottom @property def size(self): """ Return the size of the range as a dictionary of rows and columns. """ cols = self.max_col + 1 - self.min_col rows = self.max_row + 1 - self.min_row return {'columns': cols, 'rows': rows} @property def top(self): """A list of cell coordinates that comprise the top of the range""" return [(self.min_row, col) for col in range(self.min_col, self.max_col + 1)] @property def bottom(self): """A list of cell coordinates that comprise the bottom of the range""" return [(self.max_row, col) for col in range(self.min_col, self.max_col + 1)] @property def left(self): """A list of cell coordinates that comprise the left-side of the range""" return [(row, self.min_col) for row in range(self.min_row, self.max_row + 1)] @property def right(self): """A list of cell coordinates that comprise the right-side of the range""" return [(row, self.max_col) for row in range(self.min_row, self.max_row + 1)]
class ChartBase(Serialisable): """ Base class for all charts """ legend = Typed(expected_type=Legend, allow_none=True) layout = Typed(expected_type=Layout, allow_none=True) roundedCorners = Bool(allow_none=True) axId = ValueSequence(expected_type=int) visible_cells_only = Bool(allow_none=True) display_blanks = Set(values=['span', 'gap', 'zero']) _series_type = "" ser = () series = Alias('ser') title = TitleDescriptor() anchor = "E15" # default anchor position width = 15 # in cm, approx 5 rows height = 7.5 # in cm, approx 14 rows _id = 1 _path = "/xl/charts/chart{0}.xml" style = MinMax(allow_none=True, min=1, max=48) mime_type = "application/vnd.openxmlformats-officedocument.drawingml.chart+xml" graphical_properties = Typed(expected_type=GraphicalProperties, allow_none=True) __elements__ = () def __init__(self, axId=(), **kw): self._charts = [self] self.title = None self.layout = None self.roundedCorners = None self.legend = Legend() self.graphical_properties = None self.style = None self.plot_area = PlotArea() self.axId = axId self.display_blanks = 'gap' self.pivotSource = None self.pivotFormats = () self.visible_cells_only = True self.idx_base = 0 super(ChartBase, self).__init__() def __hash__(self): """ Just need to check for identity """ return id(self) def __iadd__(self, other): """ Combine the chart with another one """ if not isinstance(other, ChartBase): raise TypeError("Only other charts can be added") self._charts.append(other) return self def to_tree(self, namespace=None, tagname=None, idx=None): self.axId = [id for id in self._axes] if self.ser is not None: for s in self.ser: s.__elements__ = attribute_mapping[self._series_type] return super(ChartBase, self).to_tree(tagname, idx) def _reindex(self): """ Normalise and rebase series: sort by order and then rebase order """ # sort data series in order and rebase ds = sorted(self.series, key=attrgetter("order")) for idx, s in enumerate(ds): s.order = idx self.series = ds def _write(self): from .chartspace import ChartSpace, ChartContainer self.plot_area.layout = self.layout idx_base = self.idx_base for chart in self._charts: if chart not in self.plot_area._charts: chart.idx_base = idx_base idx_base += len(chart.series) self.plot_area._charts = self._charts container = ChartContainer(plotArea=self.plot_area, legend=self.legend, title=self.title) if isinstance(chart, _3DBase): container.view3D = chart.view3D container.floor = chart.floor container.sideWall = chart.sideWall container.backWall = chart.backWall container.plotVisOnly = self.visible_cells_only container.dispBlanksAs = self.display_blanks container.pivotFmts = self.pivotFormats cs = ChartSpace(chart=container) cs.style = self.style cs.roundedCorners = self.roundedCorners cs.pivotSource = self.pivotSource return cs.to_tree() @property def _axes(self): x = getattr(self, "x_axis", None) y = getattr(self, "y_axis", None) z = getattr(self, "z_axis", None) return OrderedDict([(axis.axId, axis) for axis in (x, y, z) if axis]) def set_categories(self, labels): """ Set the categories / x-axis values """ if not isinstance(labels, Reference): labels = Reference(range_string=labels) for s in self.ser: s.cat = AxDataSource(numRef=NumRef(f=labels)) def add_data(self, data, from_rows=False, titles_from_data=False): """ Add a range of data in a single pass. The default is to treat each column as a data series. """ if not isinstance(data, Reference): data = Reference(range_string=data) if from_rows: values = data.rows else: values = data.cols for ref in values: series = SeriesFactory(ref, title_from_data=titles_from_data) self.series.append(series) def append(self, value): """Append a data series to the chart""" l = self.series[:] l.append(value) self.series = l @property def path(self): return self._path.format(self._id)
class Reference(Strict): """ Normalise cell range references """ min_row = MinMax(min=1, max=1000000, expected_type=int) max_row = MinMax(min=1, max=1000000, expected_type=int) min_col = MinMax(min=1, max=16384, expected_type=int) max_col = MinMax(min=1, max=16384, expected_type=int) range_string = String(allow_none=True) def __init__(self, worksheet=None, min_col=None, min_row=None, max_col=None, max_row=None, range_string=None): if range_string is not None: sheetname, boundaries = range_to_tuple(range_string) min_col, min_row, max_col, max_row = boundaries worksheet = DummyWorksheet(sheetname) self.worksheet = worksheet self.min_col = min_col self.min_row = min_row if max_col is None: max_col = min_col self.max_col = max_col if max_row is None: max_row = min_row self.max_row = max_row def __repr__(self): return str(self) def __str__(self): fmt = u"{0}!${1}${2}:${3}${4}" if (self.min_col == self.max_col and self.min_row == self.max_row): fmt = u"{0}!${1}${2}" return fmt.format(self.sheetname, get_column_letter(self.min_col), self.min_row, get_column_letter(self.max_col), self.max_row) __str__ = __str__ def __len__(self): if self.min_row == self.max_row: return 1 + self.max_col - self.min_col return 1 + self.max_row - self.min_row def __eq__(self, other): return str(self) == str(other) @property def rows(self): """ Return all rows in the range """ for row in range(self.min_row, self.max_row + 1): yield Reference(self.worksheet, self.min_col, row, self.max_col, row) @property def cols(self): """ Return all columns in the range """ for col in range(self.min_col, self.max_col + 1): yield Reference(self.worksheet, col, self.min_row, col, self.max_row) def pop(self): """ Return and remove the first cell """ cell = "{0}{1}".format(get_column_letter(self.min_col), self.min_row) if self.min_row == self.max_row: self.min_col += 1 else: self.min_row += 1 return cell @property def sheetname(self): return quote_sheetname(self.worksheet.title)
class CharacterProperties(Serialisable): tagname = "defRPr" namespace = DRAWING_NS kumimoji = Bool(allow_none=True) lang = String(allow_none=True) altLang = String(allow_none=True) sz = MinMax(allow_none=True, min=100, max=400000) # 100ths of a point b = Bool(allow_none=True) i = Bool(allow_none=True) u = NoneSet(values=(['words', 'sng', 'dbl', 'heavy', 'dotted', 'dottedHeavy', 'dash', 'dashHeavy', 'dashLong', 'dashLongHeavy', 'dotDash', 'dotDashHeavy', 'dotDotDash', 'dotDotDashHeavy', 'wavy', 'wavyHeavy', 'wavyDbl'])) strike = NoneSet(values=(['noStrike', 'sngStrike', 'dblStrike'])) kern = Integer(allow_none=True) cap = NoneSet(values=(['small', 'all'])) spc = Integer(allow_none=True) normalizeH = Bool(allow_none=True) baseline = Integer(allow_none=True) noProof = Bool(allow_none=True) dirty = Bool(allow_none=True) err = Bool(allow_none=True) smtClean = Bool(allow_none=True) smtId = Integer(allow_none=True) bmk = String(allow_none=True) ln = Typed(expected_type=LineProperties, allow_none=True) highlight = Typed(expected_type=Color, allow_none=True) latin = Typed(expected_type=Font, allow_none=True) ea = Typed(expected_type=Font, allow_none=True) cs = Typed(expected_type=Font, allow_none=True) sym = Typed(expected_type=Font, allow_none=True) hlinkClick = Typed(expected_type=Hyperlink, allow_none=True) hlinkMouseOver = Typed(expected_type=Hyperlink, allow_none=True) rtl = NestedBool(allow_none=True) extLst = Typed(expected_type=OfficeArtExtensionList, allow_none=True) # uses element group EG_FillProperties noFill = EmptyTag(namespace=DRAWING_NS) solidFill = ColorChoiceDescriptor() gradFill = Typed(expected_type=GradientFillProperties, allow_none=True) blipFill = Typed(expected_type=BlipFillProperties, allow_none=True) pattFill = Typed(expected_type=PatternFillProperties, allow_none=True) grpFill = EmptyTag(namespace=DRAWING_NS) # uses element group EG_EffectProperties effectLst = Typed(expected_type=EffectList, allow_none=True) effectDag = Typed(expected_type=EffectContainer, allow_none=True) # uses element group EG_TextUnderlineLine uLnTx = EmptyTag() uLn = Typed(expected_type=LineProperties, allow_none=True) # uses element group EG_TextUnderlineFill uFillTx = EmptyTag() uFill = EmptyTag() __elements__ = ('ln', 'noFill', 'solidFill', 'gradFill', 'blipFill', 'pattFill', 'grpFill', 'effectLst', 'effectDag', 'highlight','uLnTx', 'uLn', 'uFillTx', 'uFill', 'latin', 'ea', 'cs', 'sym', 'hlinkClick', 'hlinkMouseOver', 'rtl', ) def __init__(self, kumimoji=None, lang=None, altLang=None, sz=None, b=None, i=None, u=None, strike=None, kern=None, cap=None, spc=None, normalizeH=None, baseline=None, noProof=None, dirty=None, err=None, smtClean=None, smtId=None, bmk=None, ln=None, highlight=None, latin=None, ea=None, cs=None, sym=None, hlinkClick=None, hlinkMouseOver=None, rtl=None, extLst=None, noFill=None, solidFill=None, gradFill=None, blipFill=None, pattFill=None, grpFill=None, effectLst=None, effectDag=None, uLnTx=None, uLn=None, uFillTx=None, uFill=None, ): self.kumimoji = kumimoji self.lang = lang self.altLang = altLang self.sz = sz self.b = b self.i = i self.u = u self.strike = strike self.kern = kern self.cap = cap self.spc = spc self.normalizeH = normalizeH self.baseline = baseline self.noProof = noProof self.dirty = dirty self.err = err self.smtClean = smtClean self.smtId = smtId self.bmk = bmk self.ln = ln self.highlight = highlight self.latin = latin self.ea = ea self.cs = cs self.sym = sym self.hlinkClick = hlinkClick self.hlinkMouseOver = hlinkMouseOver self.rtl = rtl self.noFill = noFill self.solidFill = solidFill self.gradFill = gradFill self.blipFill = blipFill self.pattFill = pattFill self.grpFill = grpFill self.effectLst = effectLst self.effectDag = effectDag self.uLnTx = uLnTx self.uLn = uLn self.uFillTx = uFillTx self.uFill = uFill
class LineProperties(Serialisable): tagname = "ln" namespace = DRAWING_NS w = MinMax(min=0, max=20116800, allow_none=True) # EMU width = Alias('w') cap = NoneSet(values=(['rnd', 'sq', 'flat'])) cmpd = NoneSet(values=(['sng', 'dbl', 'thickThin', 'thinThick', 'tri'])) algn = NoneSet(values=(['ctr', 'in'])) noFill = EmptyTag() solidFill = ColorChoiceDescriptor() gradFill = Typed(expected_type=GradientFillProperties, allow_none=True) pattFill = Typed(expected_type=PatternFillProperties, allow_none=True) prstDash = NestedNoneSet(values=([ 'solid', 'dot', 'dash', 'lgDash', 'dashDot', 'lgDashDot', 'lgDashDotDot', 'sysDash', 'sysDot', 'sysDashDot', 'sysDashDotDot' ]), namespace=namespace) dashStyle = Alias('prstDash') custDash = Typed(expected_type=DashStop, allow_none=True) round = EmptyTag() bevel = EmptyTag() miter = Typed(expected_type=LineJoinMiterProperties, allow_none=True) headEnd = Typed(expected_type=LineEndProperties, allow_none=True) tailEnd = Typed(expected_type=LineEndProperties, allow_none=True) extLst = Typed(expected_type=OfficeArtExtensionList, allow_none=True) __elements__ = ('noFill', 'solidFill', 'gradFill', 'pattFill', 'prstDash', 'custDash', 'round', 'bevel', 'mitre', 'headEnd', 'tailEnd') def __init__( self, w=None, cap=None, cmpd=None, algn=None, noFill=None, solidFill=None, gradFill=None, pattFill=None, prstDash=None, custDash=None, round=None, bevel=None, miter=None, headEnd=None, tailEnd=None, extLst=None, ): self.w = w self.cap = cap self.cmpd = cmpd self.algn = algn self.noFill = noFill self.solidFill = solidFill self.gradFill = gradFill self.pattFill = pattFill if prstDash is None: prstDash = "solid" self.prstDash = prstDash self.custDash = custDash self.round = round self.bevel = bevel self.mitre = bevel self.headEnd = headEnd self.tailEnd = tailEnd
class CellRange(Strict): """ Represents a range in a sheet: title and coordinates. This object is used to perform operations on ranges, like: - shift, expand or shrink - union/intersection with another sheet range, We can check whether a range is: - equal or not equal to another, - disjoint of another, - contained in another. We can get: - the size of a range. - the range bounds (vertices) - the coordinates, - the string representation, """ min_col = MinMax(min=1, max=18278, expected_type=int) min_row = MinMax(min=1, max=1048576, expected_type=int) max_col = MinMax(min=1, max=18278, expected_type=int) max_row = MinMax(min=1, max=1048576, expected_type=int) def __init__(self, range_string=None, min_col=None, min_row=None, max_col=None, max_row=None, title=None): if range_string is not None: try: title, (min_col, min_row, max_col, max_row) = range_to_tuple(range_string) except ValueError: min_col, min_row, max_col, max_row = range_boundaries(range_string) self.min_col = min_col self.min_row = min_row self.max_col = max_col self.max_row = max_row self.title = title if min_col > max_col: fmt = "{max_col} must be greater than {min_col}" raise ValueError(fmt.format(min_col=min_col, max_col=max_col)) if min_row > max_row: fmt = "{max_row} must be greater than {min_row}" raise ValueError(fmt.format(min_row=min_row, max_row=max_row)) @property def bounds(self): """ Vertices of the range as a tuple """ return self.min_col, self.min_row, self.max_col, self.max_row @property def coord(self): """ Excel style representation of the range """ fmt = "{min_col}{min_row}:{max_col}{max_row}" if (self.min_col == self.max_col and self.min_row == self.max_row): fmt = "{min_col}{min_row}" return fmt.format( min_col=get_column_letter(self.min_col), min_row=self.min_row, max_col=get_column_letter(self.max_col), max_row=self.max_row ) def _check_title(self, other): """ Check whether comparisons between ranges are possible. Cannot compare ranges from different worksheets Skip if the range passed in has no title. """ if not isinstance(other, CellRange): raise TypeError(repr(type(other))) if other.title and self.title != other.title: raise ValueError("Cannot work with ranges from different worksheets") def __repr__(self): fmt = u"<{cls} {coord}>" if self.title: fmt = u"<{cls} {title!r}!{coord}>" return safe_repr(fmt.format(cls=self.__class__.__name__, title=self.title, coord=self.coord)) def _get_range_string(self): fmt = "{coord}" title = self.title if self.title: fmt = u"{title}!{coord}" title = quote_sheetname(self.title) return fmt.format(title=title, coord=self.coord) __unicode__ = _get_range_string def __str__(self): coord = self._get_range_string() return safe_repr(coord) def __copy__(self): return self.__class__(min_col=self.min_col, min_row=self.min_row, max_col=self.max_col, max_row=self.max_row, title=self.title) def shift(self, col_shift=0, row_shift=0): """ Shift the range according to the shift values (*col_shift*, *row_shift*). :type col_shift: int :param col_shift: number of columns to be moved by, can be negative :type row_shift: int :param row_shift: number of rows to be moved by, can be negative :raise: :class:`ValueError` if any row or column index < 1 """ if (self.min_col + col_shift <= 0 or self.min_row + row_shift <= 0): raise ValueError("Invalid shift value: col_shift={0}, row_shift={1}".format(col_shift, row_shift)) self.min_col += col_shift self.min_row += row_shift self.max_col += col_shift self.max_row += row_shift def __ne__(self, other): """ Test whether the ranges are not equal. :type other: CellRange :param other: Other sheet range :return: ``True`` if *range* != *other*. """ try: self._check_title(other) except ValueError: return True return ( other.min_row != self.min_row or self.max_row != other.max_row or other.min_col != self.min_col or self.max_col != other.max_col ) def __eq__(self, other): """ Test whether the ranges are equal. :type other: CellRange :param other: Other sheet range :return: ``True`` if *range* == *other*. """ return not self.__ne__(other) def issubset(self, other): """ Test whether every element in the range is in *other*. :type other: SheetRange :param other: Other sheet range :return: ``True`` if *range* <= *other*. """ self._check_title(other) return ( (other.min_row <= self.min_row <= self.max_row <= other.max_row) and (other.min_col <= self.min_col <= self.max_col <= other.max_col) ) __le__ = issubset def __lt__(self, other): """ Test whether every element in the range is in *other*, but not all. :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range :return: ``True`` if *range* < *other*. """ return self.__le__(other) and self.__ne__(other) def issuperset(self, other): """ Test whether every element in *other* is in the range. :type other: openpyxl.worksheet.cell_range.CellRange or tuple[int, int] :param other: Other sheet range or cell index (*row_idx*, *col_idx*). :return: ``True`` if *range* >= *other* (or *other* in *range*). """ self._check_title(other) return ( (self.min_row <= other.min_row <= other.max_row <= self.max_row) and (self.min_col <= other.min_col <= other.max_col <= self.max_col) ) __ge__ = issuperset def __contains__(self, coord): """ Check whether the range contains a particular cell coordinate """ cr = self.__class__(coord) if cr.title is None: cr.title = self.title return self.issuperset(cr) def __gt__(self, other): """ Test whether every element in *other* is in the range, but not all. :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range :return: ``True`` if *range* > *other*. """ return self.__ge__(other) and self.__ne__(other) def isdisjoint(self, other): """ Return ``True`` if the range has no elements in common with other. Ranges are disjoint if and only if their intersection is the empty range. :type other: openpyxl.worksheet.cell_range.CellRange :param other: Other sheet range. :return: `True`` if the range has no elements in common with other. """ self._check_title(other) # sort by top-left vertex if self.bounds > other.bounds: i = self self = other other = i return (self.max_col, self.max_row) < (other.min_col, other.max_row) def intersection(self, other): """ Return a new range with elements common to the range and another :type others: tuple[openpyxl.worksheet.cell_range.CellRange] :param others: Other sheet ranges. :return: the current sheet range. :raise: :class:`ValueError` if an *other* range don't intersect with the current range. """ if self.isdisjoint(other): raise ValueError("Range {0} don't intersect {0}".format(self, other)) min_row = max(self.min_row, other.min_row) max_row = min(self.max_row, other.max_row) min_col = max(self.min_col, other.min_col) max_col = min(self.max_col, other.max_col) return CellRange(min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row) __and__ = intersection def union(self, other): """ Return a new range with elements from the range and all *others*. :type others: tuple[openpyxl.worksheet.cell_range.CellRange] :param others: Other sheet ranges. :return: the current sheet range. """ self._check_title(other) min_row = min(self.min_row, other.min_row) max_row = max(self.max_row, other.max_row) min_col = min(self.min_col, other.min_col) max_col = max(self.max_col, other.max_col) return CellRange(min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row, title=self.title) __or__ = union def expand(self, right=0, down=0, left=0, up=0): """ Expand the range by the dimensions provided. :type right: int :param right: expand range to the right by this number of cells :type down: int :param down: expand range down by this number of cells :type left: int :param left: expand range to the left by this number of cells :type up: int :param up: expand range up by this number of cells """ self.min_col -= left self.min_row -= up self.max_col += right self.max_row += down def shrink(self, right=0, bottom=0, left=0, top=0): """ Shrink the range by the dimensions provided. :type right: int :param right: shrink range from the right by this number of cells :type down: int :param down: shrink range from the top by this number of cells :type left: int :param left: shrink range from the left by this number of cells :type up: int :param up: shrink range from the bottown by this number of cells """ self.min_col += left self.min_row += top self.max_col -= right self.max_row -= bottom @property def size(self): """ Return the size of the range as a dicitionary of rows and columns. """ cols = self.max_col + 1 - self.min_col rows = self.max_row + 1 - self.min_row return {'columns':cols, 'rows':rows}
class ChartBase(Serialisable): """ Base class for all charts """ legend = Typed(expected_type=Legend, allow_none=True) layout = Typed(expected_type=Layout, allow_none=True) roundedCorners = Bool(allow_none=True) _series_type = "" ser = () series = Alias('ser') title = TitleDescriptor() anchor = "E15" # default anchor position width = 15 # in cm, approx 5 rows height = 7.5 # in cm, approx 14 rows _id = 1 _path = "/xl/charts/chart{0}.xml" style = MinMax(allow_none=True, min=1, max=48) mime_type = "application/vnd.openxmlformats-officedocument.drawingml.chart+xml" graphical_properties = Typed(expected_type=GraphicalProperties, allow_none=True) __elements__ = () def __init__(self, **kw): self._charts = [self] self.title = None self.layout = None self.roundedCorners = None self.legend = Legend() self.graphical_properties = None self.style = None self.plot_area = PlotArea() super(ChartBase, self).__init__(**kw) def __hash__(self): """ Just need to check for identity """ return id(self) def __iadd__(self, other): """ Combine the chart with another one """ if not isinstance(other, ChartBase): raise TypeError("Only other charts can be added") self._charts.append(other) return self def to_tree(self, tagname=None, idx=None): if self.ser is not None: for s in self.ser: s.__elements__ = attribute_mapping[self._series_type] return super(ChartBase, self).to_tree(tagname, idx) def _write(self): from .chartspace import ChartSpace, ChartContainer self.plot_area = PlotArea() self.plot_area.layout = self.layout self.plot_area.graphical_properties = self.graphical_properties idx_base = 0 for chart in self._charts: chart.idx_base = idx_base self.plot_area._charts.append(chart) idx_base += len(chart.series) axIds = [] for axId in ("x_axis", "y_axis", 'z_axis'): for chart in self._charts: axis = getattr(chart, axId, None) if axis is None: continue if axis.axId not in axIds: ax = getattr(self.plot_area, axis.tagname) ax.append(axis) axIds.append(axis.axId) container = ChartContainer(plotArea=self.plot_area, legend=self.legend, title=self.title) if isinstance(chart, _3DBase): container.view3D = chart.view3D container.floor = chart.floor container.sideWall = chart.sideWall container.backWall = chart.backWall cs = ChartSpace(chart=container) cs.style = self.style cs.roundedCorners = self.roundedCorners tree = cs.to_tree() tree.set("xmlns", CHART_NS) return tree @property def axId(self): x = getattr(self, "x_axis", None) y = getattr(self, "y_axis", None) z = getattr(self, "z_axis", None) ids = [AxId(axis.axId) for axis in (x, y, z) if axis] return ids def set_categories(self, labels): """ Set the categories / x-axis values """ if not isinstance(labels, Reference): labels = Reference(range_string=labels) for s in self.ser: s.cat = AxDataSource(numRef=NumRef(f=labels)) def add_data(self, data, from_rows=False, titles_from_data=False): """ Add a range of data in a single pass. The default is to treat each column as a data series. """ if not isinstance(data, Reference): data = Reference(range_string=data) if from_rows: values = data.rows else: values = data.cols for v in values: range_string = u"{0}!{1}:{2}".format(data.sheetname, v[0], v[-1]) series = SeriesFactory(range_string, title_from_data=titles_from_data) self.ser.append(series) def append(self, value): """Append a data series to the chart""" l = self.series[:] l.append(value) self.series = l @property def path(self): return self._path.format(self._id)
class Font(HashableObject): """Font options used in styles.""" spec = """18.8.22, p.3930""" UNDERLINE_DOUBLE = 'double' UNDERLINE_DOUBLE_ACCOUNTING = 'doubleAccounting' UNDERLINE_SINGLE = 'single' UNDERLINE_SINGLE_ACCOUNTING = 'singleAccounting' name = String(nested=True) charset = Integer(allow_none=True, nested=True) family = MinMax(min=0, max=14, nested=True) sz = Float(nested=True) size = Alias("sz") b = Bool(nested=True) bold = Alias("b") i = Bool(nested=True) italic = Alias("i") strike = Bool(nested=True) strikethrough = Alias("strike") outline = Bool(nested=True) shadow = Bool(nested=True) condense = Bool(nested=True) extend = Bool(nested=True) u = NoneSet(values=('single', 'double', 'singleAccounting', 'doubleAccounting'), nested=True ) underline = Alias("u") vertAlign = NoneSet(values=('superscript', 'subscript', 'baseline'), nested=True) color = ColorDescriptor() scheme = NoneSet(values=("major", "minor"), nested=True) tagname = "font" __nested__ = ('name', 'charset', 'family', 'b', 'i', 'strike', 'outline', 'shadow', 'condense', 'extend', 'sz', 'u', 'vertAlign', 'scheme') __fields__ = ('name', 'charset', 'family', 'b', 'i', 'strike', 'outline', 'shadow', 'condense', 'extend', 'sz', 'u', 'vertAlign', 'scheme', 'color') @classmethod def _create_nested(cls, el, tag): if tag == "u": return el.get("val", "single") return super(Font, cls)._create_nested(el, tag) def to_tree(self, tagname=None): el = Element(self.tagname) attrs = list(self.__nested__) attrs.insert(10, 'color') for attr in attrs: value = getattr(self, attr) if value: if attr == 'color': color = value.to_tree() el.append(color) else: SubElement(el, attr, val=safe_string(value)) return el def __init__(self, name='Calibri', sz=11, b=False, i=False, charset=None, u=None, strike=False, color=BLACK, scheme=None, family=2, size=None, bold=None, italic=None, strikethrough=None, underline=None, vertAlign=None, outline=False, shadow=False, condense=False, extend=False): self.name = name self.family = family if size is not None: sz = size self.sz = sz if bold is not None: b = bold self.b = b if italic is not None: i = italic self.i = i if underline is not None: u = underline self.u = u if strikethrough is not None: strike = strikethrough self.strike = strike self.color = color self.vertAlign = vertAlign self.charset = charset self.outline = outline self.shadow = shadow self.condense = condense self.extend = extend self.scheme = scheme
class Font(HashableObject): """Font options used in styles.""" spec = """18.8.22, p.3930""" UNDERLINE_NONE = 'none' UNDERLINE_DOUBLE = 'double' UNDERLINE_DOUBLE_ACCOUNTING = 'doubleAccounting' UNDERLINE_SINGLE = 'single' UNDERLINE_SINGLE_ACCOUNTING = 'singleAccounting' name = String() charset = Integer(allow_none=True) family = MinMax(min=0, max=14) sz = Float() size = Alias("sz") b = Bool() bold = Alias("b") i = Bool() italic = Alias("i") strike = Bool() strikethrough = Alias("strike") outline = Bool() shadow = Bool() condense = Bool() extend = Bool() u = Set(values=set([None, UNDERLINE_DOUBLE, UNDERLINE_NONE, UNDERLINE_DOUBLE_ACCOUNTING, UNDERLINE_SINGLE, UNDERLINE_SINGLE_ACCOUNTING])) underline = Alias("u") vertAlign = Set(values=set(['superscript', 'subscript', 'baseline', None])) color = Typed(expected_type=Color) scheme = Set(values=(None, "major", "minor")) __fields__ = ('name', 'sz', 'b', 'i', 'u', 'strike', 'color', 'vertAlign', 'charset', 'outline', 'shadow', 'condense', 'extend', 'family', ) def __init__(self, name='Calibri', sz=11, b=False, i=False, charset=None, u=None, strike=False, color=Color(), scheme=None, family=2, size=None, bold=None, italic=None, strikethrough=None, underline=UNDERLINE_NONE, vertAlign=None, outline=False, shadow=False, condense=False, extend=False): self.name = name self.family = family if size is not None: sz = size self.sz = sz if bold is not None: b = bold self.b = b if italic is not None: i = italic self.i = i if underline is not None: u = underline self.u = u if strikethrough is not None: strike = strikethrough self.strike = strike self.color = color self.vertAlign = vertAlign self.charset = charset self.outline = outline self.shadow = shadow self.condense = condense self.extend = extend self.scheme = scheme
class Reference(Strict): """ Normalise cell range references """ min_row = MinMax(min=1, max=1000000, expected_type=int) max_row = MinMax(min=1, max=1000000, expected_type=int) min_col = MinMax(min=1, max=16384, expected_type=int) max_col = MinMax(min=1, max=16384, expected_type=int) range_string = String(allow_none=True) def __init__(self, worksheet=None, min_col=None, min_row=None, max_col=None, max_row=None, range_string=None ): if range_string is not None: sheetname, boundaries = range_to_tuple(range_string) min_col, min_row, max_col, max_row = boundaries worksheet = DummyWorksheet(sheetname) self.worksheet = worksheet self.min_col = min_col self.min_row = min_row if max_col is None: max_col = min_col self.max_col = max_col if max_row is None: max_row = min_row self.max_row = max_row def __repr__(self): return unicode(self) def __str__(self): fmt = u"{0}!${1}${2}:${3}${4}" if (self.min_col == self.max_col and self.min_row == self.max_row): fmt = u"{0}!${1}${2}" return fmt.format(self.sheetname, get_column_letter(self.min_col), self.min_row, get_column_letter(self.max_col), self.max_row ) __unicode__ = __str__ def __len__(self): if self.min_row == self.max_row: return 1 + self.max_col - self.min_col return 1 + self.max_row - self.min_row @property def rows(self): """ Return all cells in range by column """ for row in range(self.min_row, self.max_row+1): yield tuple('%s%d' % (get_column_letter(col), row) for col in range(self.min_col, self.max_col+1)) @property def cols(self): """ Return all cells in range by row """ for col in range(self.min_col, self.max_col+1): yield tuple('%s%d' % (get_column_letter(col), row) for row in range(self.min_row, self.max_row+1)) @property def cells(self): """ Return a flattened list of all cells (by column) """ return chain.from_iterable(self.cols) def pop(self): """ Return and remove the first cell """ cell = next(self.cells) if self.min_row == self.max_row: self.min_col += 1 else: self.min_row += 1 return cell @property def sheetname(self): return quote_sheetname(self.worksheet.title)
class Alignment(Serialisable): """Alignment options for use in styles.""" tagname = "alignment" __fields__ = ( 'horizontal', 'vertical', 'textRotation', 'wrapText', 'shrinkToFit', 'indent', 'relativeIndent', 'justifyLastLine', 'readingOrder', ) horizontal = NoneSet(values=horizontal_alignments) vertical = NoneSet(values=vertical_aligments) textRotation = NoneSet(values=range(181)) textRotation.values.add(255) text_rotation = Alias('textRotation') wrapText = Bool(allow_none=True) wrap_text = Alias('wrapText') shrinkToFit = Bool(allow_none=True) shrink_to_fit = Alias('shrinkToFit') indent = MinMax(min=0, max=255) relativeIndent = MinMax(min=-255, max=255) justifyLastLine = Bool(allow_none=True) readingOrder = Min(min=0) def __init__(self, horizontal=None, vertical=None, textRotation=0, wrapText=None, shrinkToFit=None, indent=0, relativeIndent=0, justifyLastLine=None, readingOrder=0, text_rotation=None, wrap_text=None, shrink_to_fit=None, mergeCell=None): self.horizontal = horizontal self.vertical = vertical self.indent = indent self.relativeIndent = relativeIndent self.justifyLastLine = justifyLastLine self.readingOrder = readingOrder if text_rotation is not None: textRotation = text_rotation if textRotation is not None: self.textRotation = int(textRotation) if wrap_text is not None: wrapText = wrap_text self.wrapText = wrapText if shrink_to_fit is not None: shrinkToFit = shrink_to_fit self.shrinkToFit = shrinkToFit # mergeCell is vestigial def __iter__(self): for attr in self.__attrs__: value = getattr(self, attr) if value is not None and value != 0: yield attr, safe_string(value)