def test_number_format(DummyWorksheet, Cell):
ws = DummyWorksheet
ws.parent._number_formats.add("dd--hh--mm")
cell = Cell(ws, column="A", row=1)
cell.number_format = "dd--hh--mm"
assert cell.number_format == "dd--hh--mm"
def append(self, iterable):
"""Appends a group of values at the bottom of the current sheet.
* If it's a list: all values are added in order, starting from the first column
* If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters)
:param iterable: list, range or generator, or dict containing values to append
:type iterable: list|tuple|range|generator or dict
Usage:
* append(['This is A1', 'This is B1', 'This is C1'])
* **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
* **or** append({1 : 'This is A1', 3 : 'This is C1'})
:raise: TypeError when iterable is neither a list/tuple nor a dict
"""
row_idx = self._current_row + 1
if (isinstance(iterable, (list, tuple, range))
or isgenerator(iterable)):
for col_idx, content in enumerate(iterable, 1):
if isinstance(content, Cell):
# compatible with write-only mode
cell = content
if cell.parent and cell.parent != self:
raise ValueError(
"Cells cannot be copied from other worksheets")
cell.parent = self
cell.col_idx = col_idx
cell.row = row_idx
else:
cell = Cell(self,
row=row_idx,
col_idx=col_idx,
value=content)
self._cells[(row_idx, col_idx)] = cell
elif isinstance(iterable, dict):
for col_idx, content in iterable.items():
if isinstance(col_idx, basestring):
col_idx = column_index_from_string(col_idx)
cell = Cell(self, row=row_idx, col_idx=col_idx, value=content)
self._cells[(row_idx, col_idx)] = cell
else:
self._invalid_row(iterable)
self._current_row = row_idx
def test_quote_prefix(DummyWorksheet, Cell):
ws = DummyWorksheet
cell = Cell(ws, column="A", row=1)
cell.style_id
cell._style.quotePrefix = 1
assert cell.quotePrefix is True
def test_pivot_button(DummyWorksheet, Cell):
ws = DummyWorksheet
cell = Cell(ws, column="A", row=1)
cell.style_id
cell._style.pivotButton = 1
assert cell.pivotButton is True
def test_protection(DummyWorksheet, Cell):
from openpyxl2.styles import Protection
prot = Protection(locked=False)
ws = DummyWorksheet
ws.parent._protections.add(prot)
cell = Cell(ws, column="A", row=1)
assert cell.protection == prot
def test_alignment(DummyWorksheet, Cell):
from openpyxl2.styles import Alignment
align = Alignment(wrapText=True)
ws = DummyWorksheet
ws.parent._alignments.add(align)
cell = Cell(ws, column="A", row=1)
assert cell.alignment == align
def test_border(DummyWorksheet, Cell):
from openpyxl2.styles import Border
border = Border()
ws = DummyWorksheet
ws.parent._borders.add(border)
cell = Cell(ws, column='A', row=1)
assert cell.border == border
def test_fill(DummyWorksheet, Cell):
from openpyxl2.styles import PatternFill
fill = PatternFill(patternType="solid", fgColor="FF0000")
ws = DummyWorksheet
ws.parent._fills.add(fill)
cell = Cell(ws, column='A', row=1)
assert cell.fill == fill
def test_font(DummyWorksheet, Cell):
from openpyxl2.styles import Font
font = Font(bold=True)
ws = DummyWorksheet
ws.parent._fonts.add(font)
cell = Cell(ws, column='A', row=1)
assert cell.font == font
def _get_cell(self, row, column):
"""
Internal method for getting a cell from a worksheet.
Will create a new cell if one doesn't already exist.
"""
coordinate = (row, column)
if not coordinate in self._cells:
cell = Cell(self, row=row, col_idx=column)
self._add_cell(cell)
return self._cells[coordinate]
def test_append_cell(self, Worksheet):
from openpyxl2.cell import Cell
cell = Cell(None, 'A', 1, 25)
ws = Worksheet(Workbook())
ws.append([])
ws.append([cell])
assert ws['A2'].value == 25
def dummy_cell(DummyWorksheet, Cell):
ws = DummyWorksheet
cell = Cell(ws, column="A", row=1)
return cell
def cell(self, column, row):
return Cell(self, row=row, col_idx=column)
def parse_cell(self, element):
value = element.find(self.VALUE_TAG)
if value is not None:
value = value.text
formula = element.find(self.FORMULA_TAG)
data_type = element.get('t', 'n')
coordinate = element.get('r')
self._col_count += 1
style_id = element.get('s')
# assign formula to cell value unless only the data is desired
if formula is not None and not self.data_only:
data_type = 'f'
if formula.text:
value = "=" + formula.text
else:
value = "="
formula_type = formula.get('t')
if formula_type:
if formula_type != "shared":
self.ws.formula_attributes[coordinate] = dict(
formula.attrib)
else:
si = formula.get(
'si') # Shared group index for shared formulas
# The spec (18.3.1.40) defines shared formulae in
# terms of the following:
#
# `master`: "The first formula in a group of shared
# formulas"
# `ref`: "Range of cells which the formula applies
# to." It's a required attribute on the master
# cell, forbidden otherwise.
# `shared cell`: "A cell is shared only when si is
# used and t is `shared`."
#
# Whether to use the cell's given formula or the
# master's depends on whether the cell is shared,
# whether it's in the ref, and whether it defines its
# own formula, as follows:
#
# Shared? Has formula? | In ref Not in ref
# ========= ==============|======== ===============
# Yes Yes | master impl. defined
# No Yes | own own
# Yes No | master impl. defined
# No No | ?? N/A
#
# The ?? is because the spec is silent on this issue,
# though my inference is that the cell does not
# receive a formula at all.
#
# For this implementation, we are using the master
# formula in the two "impl. defined" cases and no
# formula in the "??" case. This choice of
# implementation allows us to disregard the `ref`
# parameter altogether, and does not require
# computing expressions like `C5 in A1:D6`.
# Presumably, Excel does not generate spreadsheets
# with such contradictions.
if si in self.shared_formula_masters:
trans = self.shared_formula_masters[si]
value = trans.translate_formula(coordinate)
else:
self.shared_formula_masters[si] = Translator(
value, coordinate)
style_array = None
if style_id is not None:
style_id = int(style_id)
style_array = self.styles[style_id]
if coordinate:
row, column = coordinate_to_tuple(coordinate)
else:
row, column = self._row_count, self._col_count
cell = Cell(self.ws, row=row, col_idx=column, style_array=style_array)
self.ws._cells[(row, column)] = cell
if value is not None:
if data_type == 'n':
value = _cast_number(value)
if is_date_format(cell.number_format):
data_type = 'd'
value = from_excel(value, self.epoch)
elif data_type == 'b':
value = bool(int(value))
elif data_type == 's':
value = self.shared_strings[int(value)]
elif data_type == 'str':
data_type = 's'
elif data_type == 'd':
value = from_ISO8601(value)
else:
if data_type == 'inlineStr':
child = element.find(self.INLINE_STRING)
if child is not None:
data_type = 's'
richtext = Text.from_tree(child)
value = richtext.content
if self.guess_types or value is None:
cell.value = value
else:
cell._value = value
cell.data_type = data_type