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'])