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)