def create_sheet(self, name, *args, **kwargs):
""" Creates sheet and appends it to spreadsheet with given name.
``args`` and ``kwargs`` are passed to :py:class:`Sheet`
constructor.
:param name: The name of the Sheet.
:type name: unicode
:returns: Created sheet.
"""
sheet = Sheet(*args, **kwargs)
for validator_creator in self.validator_creators:
for validator, validator_type in zip(
validator_creator(),
['insert', 'delete', 'replace']):
if validator is not None:
sheet.add_validator(validator, validator_type)
for validator in self.validators['add_sheet']:
sheet, name = validator(self, name, sheet)
if name in self.sheets:
raise IntegrityError(
u'Sheet with name \"{0}\" already exists.'.format(name))
else:
self.sheets[name] = sheet
self.names.append(name)
sheet.set_spreadsheet(self, name)
return sheet
def test_11(self):
data = StringIO('''\
`Name`&`E-Mail`&`Phone numbers`
`Foo Bar`&`[email protected]`&`+37060000000;+37061111111`
`Fooer Barer`&`[email protected]`&`+37062222222`\
''')
sheet = Sheet(captions=[u'Name'])
sheet.read(
data, reader_name='CSV', create_columns=True,
reader_args={'delimiter': '&', 'quotechar': '`'})
self.assertEqual(len(sheet), 2)
self.assertEqual(
sheet.captions, [u'Name', u'E-Mail', u'Phone numbers'])
self.assertEqual(
[list(row) for row in sheet],
[
[
u'Foo Bar',
u'*****@*****.**',
u'+37060000000;+37061111111'],
[
u'Fooer Barer',
u'*****@*****.**',
u'+37062222222']])
def academic_import_validate_sheet(spreadsheet, name, sheet):
""" Creates sheet with correct columns.
"""
sheet = Sheet(
captions=(
list(IMPORT_ACADEMICS_REQUIRED_COLUMNS.keys()) +
list(IMPORT_ACADEMICS_OPTIONAL_COLUMNS.keys())))
sheet.add_validator(academic_import_validate_row, 'insert')
return sheet, name
def municipalities_import_validate_sheet(spreadsheet, name, sheet):
""" Creates sheet with correct columns.
"""
sheet = Sheet(
captions=(list(IMPORT_MUNICIPALITIES_REQUIRED_COLUMNS.keys())))
sheet.add_validator(
ImportValidateRow(
IMPORT_MUNICIPALITIES_REQUIRED_COLUMNS,
(u'id', u'code',),
(u'id', u'code',),
(u'id', u'town', u'code',),
),
'insert')
return sheet, name
def title_only_import_validate_sheet(spreadsheet, name, sheet):
""" Creates sheet with correct columns.
"""
sheet = Sheet(
captions=(list(IMPORT_TITLE_ONLY_REQUIRED_COLUMNS.keys())))
sheet.add_validator(
ImportValidateRow(
IMPORT_TITLE_ONLY_REQUIRED_COLUMNS,
(u'id',),
(u'id', u'title',),
(u'id', u'title',),
),
'insert')
return sheet, name
def test_05(self):
file = os.path.join(os.path.dirname(__file__), 'files', 'sheet.csv')
reader = CSVReader()
sheet = Sheet()
sheet.add_column(u'Name')
self.assertEqual(len(sheet), 0)
self.assertEqual(sheet.captions, [u'Name'])
reader(sheet, file.decode('utf-8'), create_columns=False)
self.assertEqual(len(sheet), 2)
self.assertEqual(
sheet.captions, [u'Name'])
self.assertEqual(
[list(row) for row in sheet],
[[u'Foo Bar'], [u'Fooer Barer']])
def base_info_import_validate_sheet(spreadsheet, name, sheet):
""" Creates sheet with correct columns.
"""
sheet = Sheet(
captions=(list(IMPORT_BASE_INFO_REQUIRED_COLUMNS.keys())))
sheet.add_validator(
ImportValidateRow(
IMPORT_BASE_INFO_REQUIRED_COLUMNS,
(u'payment',),
(),
(u'first_name', u'last_name', u'email', u'section',
u'payment'),
),
'insert')
sheet.add_validator(base_info_import_validate_row, 'insert')
return sheet, name
def setUp(self):
self.file = os.path.join(
os.path.dirname(__file__), 'files', 'sheet.ods'
).decode('utf-8')
self.reader = ODFSheetReader()
self.sheet = Sheet()
self.assertEqual(len(self.sheet), 0)
self.assertEqual(self.sheet.captions, [])
def test_14(self):
sheet = Sheet(
captions=[u'Number', u'Square', u'Cube'],
rows=[(i, i * i, i * i * i) for i in range(5)])
file = StringIO()
sheet.write(file, u'CSV')
self.assertEqual(
file.getvalue(),
"""\
"Number";"Square";"Cube"
"0";"0";"0"
"1";"1";"1"
"2";"4";"8"
"3";"9";"27"
"4";"16";"64"
"""
)
def test_01(self):
sheet = Sheet(captions=[u'a', u'b'], rows=[[1, 2], [3, 4], [5, 6],])
col1, col2 = sheet.columns
self.assertEqual(col1.caption, u'a')
self.assertEqual(col1.index, 0)
self.assertEqual(list(col1), [1, 3, 5])
self.assertEqual(col2.caption, u'b')
self.assertEqual(col2.index, 1)
self.assertEqual(list(col2), [2, 4, 6])
self.assertEqual(col1[1], 3)
col1[1] = 10
self.assertEqual(col1[1], 10)
self.assertEqual(sheet[1][u'a'], 10)
sheet.captions[1] = u'Foo'
self.assertEqual(col2.caption, u'Foo')
sheet.rows[1].fields[1] = 3
self.assertEqual(list(col2), [2, 3, 6])
def join(self, caption):
""" Joins all sheets into one. Sheets names are stored in
column with given ``caption``.
.. warning::
An assumption is made, that all sheets have the same columns.
"""
if not self.names:
# No sheets in spreadsheet, return empty sheet.
return Sheet(captions=[caption])
assigned_sheet = self.sheets[self.names[0]]
sheet = Sheet(captions=assigned_sheet.captions + [caption])
for assigned_sheet in self.sheets.values():
for row in assigned_sheet.row_dicts:
row[caption] = assigned_sheet.name
sheet.append_dict(row)
return sheet
def dump_query_to_sheet(self, queryset, sheet_mapping, sheet=None):
""" Dumps query to sheet.
"""
mapping = []
captions = []
related = set()
for caption, parts in sheet_mapping:
captions.append(caption)
mapping.append((caption, parts))
if len(parts) > 1:
related.add(u'__'.join(parts[:-1]))
if sheet is None:
sheet = Sheet()
sheet.add_columns(captions)
for obj in queryset.select_related(*related):
info = {}
for caption, field in mapping:
info[caption] = unicode(get_field_value(obj, field))
sheet.append_dict(info)
return sheet
def dump_query_to_sheet(
queryset, sheet=None, fields=None, exclude=None,
join_rules=None,
merge_rules=None):
""" Dumps query to sheet.
If ``sheet`` is None, then creates one.
:param fields: what fields from object to include, if None then all.
:param exclude:
what fields from object to exclude, if None then none.
:param join_rules: what relationships to join_rules by field.
:param merge_rules:
what relationships to merge_rules into sheet. (``get`` method
must return an item or raise DoesNotExist error).
:returns: sheet object.
"""
if sheet is None:
sheet = Sheet()
if len(queryset) < 1:
return sheet
obj = queryset[0]
if fields is None:
fields = collect_fields(obj, exclude)
def modifier(sheet, row):
""" Changes fields to Unicode strings.
"""
new_row = collections.defaultdict(unicode)
for field_name, (obj, field) in row.items():
if obj is not None:
display_attr = 'get_{0}_display'.format(field.name)
if hasattr(obj, display_attr):
new_row[field_name] = getattr(obj, display_attr)()
else:
value = getattr(obj, field.name)
if value is None:
new_row[field_name] = u''
else:
new_row[field_name] = value
else:
new_row[field_name] = field
return new_row
sheet.add_insert_validator(modifier)
sheet.add_columns([field.verbose_name for field in fields])
mergable = {}
joinable = {}
merge_rules = merge_rules or ()
join_rules = join_rules or ()
for related_obj in obj._meta.get_all_related_objects():
if related_obj.name in merge_rules:
model = related_obj.model
merge_fields = collect_fields(model, ('id',))
mergable[related_obj.name] = merge_fields, model
sheet.add_columns([
field.verbose_name
for field in merge_fields
])
for field_name, model_name, kwargs in join_rules:
if model_name == related_obj.name:
model = related_obj.model
field = model._meta.get_field(field_name)
joinable[related_obj.name.split(':')[1]] = field, kwargs
sheet.add_column(field.verbose_name)
break
for obj in queryset:
row = dict([(field.verbose_name, (obj, field)) for field in fields])
for full_name, (merge_fields, model) in mergable.items():
name = full_name.split(':')[1]
try:
related_obj = getattr(obj, name)
for field in merge_fields:
row[field.verbose_name] = related_obj, field
except model.DoesNotExist:
pass
for name, (field, (filter_kwargs, exclude_kwargs)) in (
joinable.items()):
query = getattr(obj, '{0}_set'.format(name)).all()
row[field.verbose_name] = None, join(
query.filter(**filter_kwargs).exclude(**exclude_kwargs),
field.name)
sheet.append_dict(row)
return sheet
class ODFSheetReaderTest01(unittest.TestCase):
""" Tests for :py:class:`pysheets.readers.ods.ODFSheetReader`.
Testing reading ODF file, which has only one table.
"""
def assertSheet(self, sheet):
self.assertEqual(len(sheet), 2)
self.assertEqual(
sheet.captions, [u'Name', u'E-Mail', u'Phone numbers'])
self.assertEqual(
[list(row) for row in sheet],
[
[
u'Foo Bar',
u'*****@*****.**',
u'+37060000000;+37061111111'],
[
u'Fooer Barer',
u'*****@*****.**',
u'+37062222222']])
def setUp(self):
self.file = os.path.join(
os.path.dirname(__file__), 'files', 'sheet.ods'
).decode('utf-8')
self.reader = ODFSheetReader()
self.sheet = Sheet()
self.assertEqual(len(self.sheet), 0)
self.assertEqual(self.sheet.captions, [])
def tearDown(self):
self.file = None
self.reader = None
self.sheet = None
def test_01(self):
self.reader(self.sheet, self.file)
self.assertSheet(self.sheet)
def test_02(self):
self.reader(self.sheet, self.file, sheet_name=u'Sheet1')
self.assertSheet(self.sheet)
def test_03(self):
self.assertRaises(
ValueError, self.reader, self.sheet, self.file,
sheet_name=u'Sheet2')
def test_04(self):
self.assertRaises(
IntegrityError, self.reader, self.sheet, self.file,
create_columns=False)
def test_05(self):
self.sheet.add_column(u'Name')
self.reader(self.sheet, self.file, create_columns=False)
self.assertEqual(
[list(row) for row in self.sheet],
[[u'Foo Bar'], [u'Fooer Barer']])
def setUp(self):
self.sheet = Sheet(
captions=[u'Number', u'Square', u'Cube'],
rows=[(i, i * i, i * i * i) for i in range(5)])
self.file = StringIO()
class CSVWriterTest(unittest.TestCase):
""" Tests for :py:class:`pysheets.writers.csv.CSVWriter`.
"""
def setUp(self):
self.sheet = Sheet(
captions=[u'Number', u'Square', u'Cube'],
rows=[(i, i * i, i * i * i) for i in range(5)])
self.file = StringIO()
def test_01(self):
writer = CSVWriter()
writer(self.sheet, self.file)
self.assertEqual(
self.file.getvalue(),
"""\
"Number";"Square";"Cube"
"0";"0";"0"
"1";"1";"1"
"2";"4";"8"
"3";"9";"27"
"4";"16";"64"
"""
)
def test_02(self):
writer = CSVWriter(delimiter='&', quotechar='`')
writer(self.sheet, self.file)
self.assertEqual(
self.file.getvalue(),
"""\
`Number`&`Square`&`Cube`
`0`&`0`&`0`
`1`&`1`&`1`
`2`&`4`&`8`
`3`&`9`&`27`
`4`&`16`&`64`
"""
)
def test_03(self):
self.sheet.add_column('Today', [datetime.date(2011, 8, 30)] * 5)
self.sheet.add_column(
'Now', [datetime.datetime(2011, 8, 30, 12, 58, 53)] * 5)
writer = CSVWriter()
writer(self.sheet, self.file)
self.assertEqual(
self.file.getvalue(),
"""\
"Number";"Square";"Cube";"Today";"Now"
"0";"0";"0";"2011-08-30";"2011-08-30 12:58:53"
"1";"1";"1";"2011-08-30";"2011-08-30 12:58:53"
"2";"4";"8";"2011-08-30";"2011-08-30 12:58:53"
"3";"9";"27";"2011-08-30";"2011-08-30 12:58:53"
"4";"16";"64";"2011-08-30";"2011-08-30 12:58:53"
"""
)
def test_04(self):
writer = CSVWriter()
file_descriptor, file_path = tempfile.mkstemp(suffix='.csv')
file_path = file_path.decode('utf-8')
writer(self.sheet, file_path)
with open(file_path, 'rb') as fp:
contents = fp.read()
self.assertEqual(
contents,
"""\
"Number";"Square";"Cube"
"0";"0";"0"
"1";"1";"1"
"2";"4";"8"
"3";"9";"27"
"4";"16";"64"
"""
)
def test_01(self):
""" Ok scenario.
"""
sheet = Sheet(
captions=[u'a', u'b', u'c'],
rows=[
{u'a': 4, u'b': 5, u'c': 6},
[4, 2, 3],
[2, 4, 5],
{u'c': u'haha', u'a': u'caca', u'b': u'dada'},
[1, 7, 5],
[3, 2, 5],
])
self.assertEqual(len(sheet), 6)
self.assertEqual(sheet.captions, [u'a', u'b', u'c'])
self.assertEqual(list(sheet), sheet.rows)
self.assertEqual(list(sheet[4]), [1, 7, 5])
self.assertEqual(list(sheet[-2]), [1, 7, 5])
self.assertEqual(
[
u' '.join(unicode(field) for field in row)
for row in sheet[1::2]],
[u'4 2 3', u'caca dada haha', u'3 2 5'])
self.assertEqual(
[
u' '.join(unicode(field) for field in row)
for row in sheet.filter(func=lambda x: x[u'c'] == 5)],
[u'2 4 5', u'1 7 5', u'3 2 5'])
self.assertEqual(
list(sheet.get(u'c', u'a')),
[[6, 4], [3, 4], [5, 2], [u'haha', u'caca'], [5, 1], [5, 3]]
)
sheet.add_column('d', [1, 2, 3, 4, 5, 6])
column = sheet.get(u'd')
self.assertEqual(column.index, 3)
self.assertEqual(list(column), [1, 2, 3, 4, 5, 6])
self.assertEqual(list(sheet[3]), [u'caca', u'dada', u'haha', 4])
sheet[3] = {u'a': 1, u'b': 2, u'c': 3, u'd': 4}
self.assertEqual(list(sheet[3]), [1, 2, 3, 4])
def compare(rows):
self.assertEqual(
[
u' '.join(unicode(field) for field in row)
for row in sheet],
rows,)
del sheet[3]
compare([
u'4 5 6 1', u'4 2 3 2', u'2 4 5 3', u'1 7 5 5', u'3 2 5 6'],)
sheet.sort()
compare([
u'1 7 5 5', u'2 4 5 3', u'3 2 5 6', u'4 2 3 2', u'4 5 6 1'])
sheet.sort(columns=[u'c', u'a'])
compare([
u'4 2 3 2', u'1 7 5 5', u'2 4 5 3', u'3 2 5 6', u'4 5 6 1'])
sheet.sort(
columns=[u'd', u'b'],
cmp=lambda x, y: (x[u'b'] + x[u'd']) - (y[u'b'] + y[u'd']))
compare([
u'4 2 3 2', u'4 5 6 1', u'2 4 5 3', u'3 2 5 6', u'1 7 5 5'])
sheet.sort(
columns=[u'd', u'b'],
key=lambda x: x[u'b'] + x[u'c'])
compare([
u'4 2 3 2', u'3 2 5 6', u'2 4 5 3', u'4 5 6 1', u'1 7 5 5'])
sheet.sort(
columns=[u'd', u'b'],
key=lambda x: x[u'b'] + x[u'c'],
reverse=True)
compare([
u'1 7 5 5', u'4 5 6 1', u'2 4 5 3', u'3 2 5 6', u'4 2 3 2'])
sheet.sort([u'd'])
sheet.remove(u'b')
compare([u'4 6 1', u'4 3 2', u'2 5 3', u'1 5 5', u'3 5 6'],)
sheet.remove(u'd')
sheet.remove(u'a')
compare([u'6', u'3', u'5', u'5', u'5'])
sheet.remove(u'c')
self.assertEqual(sheet.captions, [])
self.assertEqual(len(sheet), 0)
def test_02(self):
""" Ok scenario.
"""
sheet = Sheet()
self.assertEqual(sheet.captions, [])
self.assertEqual(len(sheet), 0)
self.assertEqual(sheet.rows, [])
sheet.add_column(u'a')
self.assertEqual(sheet.captions, [u'a'])
self.assertEqual(sheet.captions_index, {u'a': 0})
sheet.add_columns([u'b', u'c'])
self.assertEqual(sheet.captions, [u'a', u'b', u'c'])
self.assertEqual(sheet.captions_index, {u'a': 0, u'b': 1, u'c': 2})
sheet.append_dict({u'a': 0, u'b': 1, u'c': 2})
self.assertEqual(sheet.rows[0].fields, [0, 1, 2])
sheet.append_iterable([2, 1, 3])
self.assertEqual(sheet.rows[1].fields, [2, 1, 3])
sheet.append(range(3))
sheet.append({u'a': 'ddd', u'b': 'lll', u'c': (1, 2, 3)})
def test_07(self):
""" Test modifying validators.
"""
def name_validator(sheet, row, replaced_row=None):
""" Splits name into first name and last name.
"""
first_name, last_name = unicode(row[u'Name']).split()
row[u'First name'] = first_name.capitalize()
row[u'Last name'] = last_name.capitalize()
del row[u'Name']
return row
sheet = Sheet(captions=[u'ID', u'First name', u'Last name'])
sheet.add_validator(name_validator, 'insert', 'replace')
self.assertRaises(ValueError, sheet.append, [u'1', u'Foo Bar'])
self.assertRaises(
ValueError, sheet.append, {u'ID': 1, u'Name': u'Foo'})
self.assertEqual(len(sheet), 0)
sheet.append({u'ID': 1, u'Name': u' fOo bAR'})
self.assertEqual(len(sheet), 1)
self.assertEqual(list(sheet[0]), [1, u'Foo', u'Bar'])
sheet[0] = {u'ID': u'0', u'Name': u'bar foo'}
self.assertEqual(len(sheet), 1)
self.assertEqual(list(sheet[0]), [u'0', u'Bar', u'Foo'])
validator = UniqueNameValidator()
sheet.add_insert_validator(validator.insert)
sheet.add_delete_validator(validator.delete)
sheet.add_replace_validator(validator.replace)
self.assertEqual(
sheet.insert_validators, [name_validator, validator.insert])
self.assertEqual(
sheet.delete_validators, [validator.delete])
self.assertEqual(
sheet.replace_validators,
[name_validator, validator.replace])
sheet.append({u'Name': u'Fooer Barer'})
self.assertEqual(
list(u', '.join(row) for row in sheet),
[u'0, Bar, Foo', u'1, Fooer, Barer'])
self.assertRaises(
ValidationError, sheet.append, {u'Name': u'Fooer Barer'})
del sheet[1]
sheet.append({u'Name': u'Fooer Barer'})
self.assertEqual(
list(u', '.join(row) for row in sheet),
[u'0, Bar, Foo', u'2, Fooer, Barer'])
sheet[1] = {u'Name': u'fooer barer', u'ID': u'1'}
self.assertEqual(
list(u', '.join(row) for row in sheet),
[u'0, Bar, Foo', u'1, Fooer, Barer'])
def test_06(self):
""" Test non-modifying validators.
"""
validator = UniqueIntegerValidator(u'ID')
sheet = Sheet(captions=[u'ID'])
sheet.add_insert_validator(validator.insert)
sheet.add_delete_validator(validator.delete)
sheet.add_replace_validator(validator.replace)
self.assertEqual(sheet.insert_validators, [validator.insert])
self.assertEqual(sheet.delete_validators, [validator.delete])
self.assertEqual(sheet.replace_validators, [validator.replace])
sheet.add_validator(validate_nothing)
self.assertEqual(sheet.insert_validators, [validator.insert])
self.assertEqual(sheet.delete_validators, [validator.delete])
self.assertEqual(sheet.replace_validators, [validator.replace])
sheet.add_validator(validate_nothing, 'insert', 'delete', 'replace')
self.assertEqual(sheet.insert_validators,
[validator.insert, validate_nothing])
self.assertEqual(sheet.delete_validators,
[validator.delete, validate_nothing])
self.assertEqual(sheet.replace_validators,
[validator.replace, validate_nothing])
self.assertRaises(
ValueError, sheet.add_validator, validate_nothing, 'foo')
self.assertRaises(ValidationError, sheet.append, [u'baba'])
self.assertEqual(len(sheet), 0)
sheet.append([u'2'])
sheet.append([u'3'])
self.assertEqual(sorted(validator.values), [2, 3])
self.assertEqual(sheet[1][u'ID'], 3)
self.assertEqual(len(sheet), 2)
self.assertRaises(ValidationError, sheet.append, [3])
self.assertEqual(len(sheet), 2)
del sheet[1]
self.assertEqual(validator.values, set([2]))
sheet.append([u'3'])
sheet[1] = {u'ID': 4}
sheet.append([u'3'])
