def test_save_book_to_memory_from_sql(self): test_file = pe.save_book_as(dest_file_type="xls", session=Session(), tables=[Signature, Signature2]) book_dict = pe.get_book_dict(file_content=test_file.getvalue(), file_type="xls") expected = OrderedDict() expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]}) expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected
class TestBook: def setUp(self): self.content = OrderedDict() self.content.update({"Sheet1": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) self.content.update({"Sheet2": [[u'A', u'B', u'C'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) self.result1 = [{'Y': 4, 'X': 1, 'Z': 7}, {'Y': 5, 'X': 2, 'Z': 8}, {'Y': 6, 'X': 3, 'Z': 9}] self.result2 = [{'B': 4, 'A': 1, 'C': 7}, {'B': 5, 'A': 2, 'C': 8}, {'B': 6, 'A': 3, 'C': 9}] def test_book_save_to_models(self): model1=FakeDjangoModel() model2=FakeDjangoModel() book = pe.Book(self.content) book.save_to_django_models([model1, model2]) assert model1.objects.objs == self.result1 assert model2.objects.objs == self.result2 def test_model_save_to_models(self): model=FakeDjangoModel() pe.save_book_as(dest_models=[model, None, None], bookdict=self.content) assert model.objects.objs == self.result1 def test_load_book_from_django_model(self): model=FakeDjangoModel() book = pe.Book(self.content) book.save_to_django_models([model]) assert model.objects.objs == self.result1 model._meta.update(["X", "Y", "Z"]) book2 = pe.get_book(models=[model]) assert book2[0].to_array() == book[0].to_array() def test_more_sheets_than_models(self): self.content.update({"IgnoreMe":[[1,2,3]]}) model=FakeDjangoModel() pe.save_book_as(dest_models=[model], bookdict=self.content) assert model.objects.objs == self.result1
def test_get_book_from_book_dict(self): content = OrderedDict() content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) content.update({"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) book = pe.get_book(bookdict=content) assert book.to_dict() == content
def test_get_book_from_sql(self): book_dict = pe.get_book_dict(session=Session(), tables=[Signature, Signature2]) expected = OrderedDict() expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]}) expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected
class TestCSVNXlsMultipleSheets: def setUp(self): self.testfile = "multiple1.csv" self.content = OrderedDict() self.content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) pe.save_book_as(dest_file_name=self.testfile, bookdict=self.content) def test_read_multiple_csv_into_book(self): book = pe.load_book(self.testfile) assert book.sheet_names() == ["Sheet1", "Sheet2", "Sheet3"] book["Sheet1"].format(int) assert self.content["Sheet1"] == book["Sheet1"].to_array() book["Sheet2"].format(int) assert self.content["Sheet2"] == book["Sheet2"].to_array() book["Sheet3"].format(int) assert self.content["Sheet3"] == book["Sheet3"].to_array() def tearDown(self): if os.path.exists("multiple1__Sheet1__0.csv"): os.unlink("multiple1__Sheet1__0.csv") if os.path.exists("multiple1__Sheet2__1.csv"): os.unlink("multiple1__Sheet2__1.csv") if os.path.exists("multiple1__Sheet3__2.csv"): os.unlink("multiple1__Sheet3__2.csv")
class TestSingleSheetReaderForMulitpleSheetBook: def setUp(self): self.testfile = "multiple1.xls" self.content = OrderedDict() self.content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) pe.save_book_as(dest_file_name=self.testfile, bookdict=self.content) def test_non_default_sheet_as_single_sheet_reader(self): r = pe.Reader(self.testfile, "Sheet1") data = pe.utils.to_array(r) assert data == self.content["Sheet1"] r2 = pe.Reader(self.testfile, "Sheet2") data = pe.utils.to_array(r2) assert data == self.content["Sheet2"] r3 = pe.Reader(self.testfile, "Sheet3") data = pe.utils.to_array(r3) assert data == self.content["Sheet3"] def test_non_default_sheet_as_single_sheet_reader_series(self): r = pe.SeriesReader(self.testfile, "Sheet3") data = pe.utils.to_array(r.rows()) assert data == self.content["Sheet3"][1:] def test_non_default_sheet_as_single_sheet_plain_reader(self): r = pe.load(self.testfile, "Sheet2") data = pe.utils.to_array(r.rows()) assert data == self.content["Sheet2"] def tearDown(self): if os.path.exists(self.testfile): os.unlink(self.testfile)
def test_get_book_from_file_stream(self): content = OrderedDict() content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) io = pe.save_book_as(dest_file_type="xls", bookdict=content) book2 = pe.get_book(file_stream=io, file_type="xls") assert book2.to_dict() == content
def test_get_book_dict(self): content = OrderedDict() content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) content.update({"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) io = pe.save_book_as(dest_file_type="xls", bookdict=content) adict = pe.get_book_dict(file_content=io.getvalue(), file_type="xls") assert adict == content
def test_pyexcel_xls_issue_2(): data = OrderedDict() array = [] for i in range(4100): array.append([datetime.datetime.now()]) data.update({"test": array}) save_data("test.xls", data) os.unlink("test.xls")
def test_get_book_from_memory_compatibility(self): content = OrderedDict() content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) content.update({"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) io = pe.save_book_as(dest_file_type="xls", bookdict=content) book2 = pe.get_book(content=io.getvalue(), file_type="xls") assert book2.to_dict() == content
def test_issue_10(self): thedict = OrderedDict() thedict.update({"Column 1": [1,2,3]}) thedict.update({"Column 2": [1,2,3]}) thedict.update({"Column 3": [1,2,3]}) pe.save_as(adict=thedict, dest_file_name="issue10.xls") newdict = pe.get_dict(file_name="issue10.xls") assert isinstance(newdict, OrderedDict) == True assert thedict == newdict
def test_save_book_as_file_from_sql(self): test_file = "book_from_sql.xls" pe.save_book_as(out_file=test_file, session=Session(), tables=[Signature, Signature2]) book_dict = pe.get_book_dict(file_name=test_file) expected = OrderedDict() expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]}) expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected os.unlink(test_file)
def test_save_book_as_file_from_sql(self): test_file="book_from_sql.xls" pe.save_book_as(out_file=test_file, session=Session(), tables=[Signature, Signature2]) book_dict = pe.get_book_dict(file_name=test_file) expected = OrderedDict() expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]}) expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected os.unlink(test_file)
def test_get_book_from_file(self): test_file = "test_get_book.xls" content = OrderedDict() content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) content.update({"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) book = pe.Book(content) book.save_as(test_file) book2 = pe.get_book(file_name=test_file) assert book2.to_dict() == content os.unlink(test_file)
def test_tutorial05_example3(self): content="Column 1,Column 2,Column 3\n1,4,7\n2,5,8\n3,6,9" reader = pe.SeriesReader(("csv", content)) print(reader.column["Column 3"]) extra_data = OrderedDict() extra_data.update({"Column 4":[10, 11, 12]}) extra_data.update({"Column 5":[13, 14, 15]}) reader.column += extra_data print(pe.utils.to_dict(reader)) assert reader.column["Column 4"] == [10, 11, 12] assert reader.column["Column 5"] == [13, 14, 15]
def test_save_book_to_memory_from_sql(self): test_file = pe.save_book_as(dest_file_type="xls", session=Session(), tables=[Signature, Signature2]) book_dict = pe.get_book_dict( file_content=test_file.getvalue(), file_type="xls" ) expected = OrderedDict() expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]}) expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected
class TestXlsNXlsmMultipleSheets(PyexcelMultipleSheetBase): def setUp(self): self.testfile = "multiple1.xls" self.testfile2 = "multiple1.xlsm" self.content = OrderedDict() self.content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) self._write_test_file(self.testfile) def tearDown(self): self._clean_up()
def test_save_book_as_file_from_sql(self): test_file = "book_from_sql.xls" pe.save_book_as( dest_file_name=test_file, session=Session(), tables=[Signature, Signature2], ) book_dict = pe.get_book_dict(file_name=test_file) expected = OrderedDict() expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]}) expected.update( {"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]} ) assert book_dict == expected os.unlink(test_file)
def test_save_book_to_memory_from_sql(self): test_file = pe.save_book_as( dest_file_type="xls", session=Session(), tables=[Signature, Signature2], ) book_dict = pe.get_book_dict( file_content=test_file.getvalue(), file_type="xls" ) expected = OrderedDict() expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]}) expected.update( {"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]} ) assert book_dict == expected
class TestCSVSingleSheet: def _write_test_file(self, file, content): """ Make a test file as: 1,1,1,1 2,2,2,2 3,3,3,3 """ w = pe.BookWriter(file) w.write_book_from_dict(content) w.close() def setUp(self): self.testfile = "multiple1.csv" self.content = OrderedDict() self.content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) self._write_test_file(self.testfile, self.content) def test_load_a_single_sheet(self): b1 = pe.load_book(self.testfile, sheet_name="Sheet1") b1['Sheet1'].format(int) assert len(b1.sheet_names()) == 1 assert b1['Sheet1'].to_array() == self.content['Sheet1'] def test_load_a_single_sheet2(self): b1 = pe.load_book(self.testfile, sheet_index=1) b1['Sheet2'].format(int) assert len(b1.sheet_names()) == 1 assert b1['Sheet2'].to_array() == self.content['Sheet2'] @raises(IndexError) def test_load_a_single_sheet3(self): pe.load_book(self.testfile, sheet_index=10000) @raises(ValueError) def test_load_a_single_sheet4(self): pe.load_book(self.testfile, sheet_name="Not exist") def tearDown(self): clean_up_files([ "multiple1__Sheet1__0.csv", "multiple1__Sheet2__1.csv", "multiple1__Sheet3__2.csv", ])
def test_issue_51_orderred_dict_in_records(self): from pyexcel.utils import yield_from_records records = [] orderred_dict = OrderedDict() orderred_dict.update({"Zebra": 10}) orderred_dict.update({"Hippo": 9}) orderred_dict.update({"Monkey": 8}) records.append(orderred_dict) orderred_dict2 = OrderedDict() orderred_dict2.update({"Zebra": 1}) orderred_dict2.update({"Hippo": 2}) orderred_dict2.update({"Monkey": 3}) records.append(orderred_dict2) array = list(yield_from_records(records)) expected = [['Zebra', 'Hippo', 'Monkey'], [10, 9, 8], [1, 2, 3]] self.assertEqual(array, expected)
def test_issue_51_orderred_dict_in_records(self): from pyexcel.sources.pydata import yield_from_records records = [] orderred_dict = OrderedDict() orderred_dict.update({"Zebra": 10}) orderred_dict.update({"Hippo": 9}) orderred_dict.update({"Monkey": 8}) records.append(orderred_dict) orderred_dict2 = OrderedDict() orderred_dict2.update({"Zebra": 1}) orderred_dict2.update({"Hippo": 2}) orderred_dict2.update({"Monkey": 3}) records.append(orderred_dict2) array = list(yield_from_records(records)) expected = [['Zebra', 'Hippo', 'Monkey'], [10, 9, 8], [1, 2, 3]] self.assertEqual(array, expected)
def test_issue_51_orderred_dict_in_records(): from pyexcel.plugins.sources.pydata.records import RecordsReader records = [] orderred_dict = OrderedDict() orderred_dict.update({"Zebra": 10}) orderred_dict.update({"Hippo": 9}) orderred_dict.update({"Monkey": 8}) records.append(orderred_dict) orderred_dict2 = OrderedDict() orderred_dict2.update({"Zebra": 1}) orderred_dict2.update({"Hippo": 2}) orderred_dict2.update({"Monkey": 3}) records.append(orderred_dict2) records_reader = RecordsReader(records) array = list(records_reader.to_array()) expected = [['Zebra', 'Hippo', 'Monkey'], [10, 9, 8], [1, 2, 3]] eq_(array, expected)
class TestBook: def setUp(self): app.config['TESTING'] = True self.app = app.test_client() self.content = OrderedDict() self.content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) def test_book(self): for struct_type in ["book", "book_dict"]: io = pe.save_book_as(bookdict=self.content, dest_file_type="xls") response = self.app.post('/exchange/%s' % struct_type, buffered=True, data={"file": (io, "test.xls")}, content_type="multipart/form-data") assert response.content_type == "application/vnd.ms-excel" book2 = pe.get_book(file_type='xls', file_content=response.data) assert book2.to_dict() == self.content
class TestBook: def setUp(self): app.config['TESTING'] = True self.app = app.test_client() self.content = OrderedDict() self.content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) def test_book(self): test_sample = ["book", "book_dict"] expected = {u'result': {u'Sheet1': [[1.0, 1.0, 1.0, 1.0], [2.0, 2.0, 2.0, 2.0], [3.0, 3.0, 3.0, 3.0]], u'Sheet3': [[u'X', u'Y', u'Z'], [1.0, 4.0, 7.0], [2.0, 5.0, 8.0], [3.0, 6.0, 9.0]], u'Sheet2': [[4.0, 4.0, 4.0, 4.0], [5.0, 5.0, 5.0, 5.0], [6.0, 6.0, 6.0, 6.0]]}} for struct_type in test_sample: io = BytesIO() book = pe.Book(self.content) book.save_to_memory('xls', io) io.seek(0) response = self.app.post('/respond/%s' % struct_type, buffered=True, data={"file": (io, "test.xls")}, content_type="multipart/form-data") assert json.loads(response.data.decode('utf-8')) == expected
def test_extend_columns2(self): r = self.testclass(self.testfile) columns = OrderedDict() columns.update({"p": ["c1", "x1"]}) columns.update({"a": ["c2", "x2"]}) columns.update({"d": ["c3", "x4"]}) r.extend_columns(columns) assert r.row[0] == ["a", "b", "c", "d", "c1", "c2", "c3"] assert r.row[1] == ["e", "f", "g", "h", "x1", "x2", "x4"] assert r.row[2] == ["i", "j", 1.1, 1, "", "", ""] r2 = self.testclass(self.testfile) columns = OrderedDict() columns.update({"p": ["c1", "x1", "y1", "z1"]}) columns.update({"a": ["c2", "x2", "y2"]}) columns.update({"d": ["c3", "x4"]}) r2.extend_columns(columns) assert r2.row[0] == ["a", "b", "c", "d", "c1", "c2", "c3"] assert r2.row[1] == ["e", "f", "g", "h", "x1", "x2", "x4"] assert r2.row[2] == ["i", "j", 1.1, 1, "y1", "y2", ""] assert r2.row[3] == ["", "", "", "", "z1", "", ""]
def test_extend_columns2(self): r = self.testclass(self.testfile) columns = OrderedDict() columns.update({"p": ['c1', 'x1']}) columns.update({"a": ['c2', 'x2']}) columns.update({"d": ['c3', 'x4']}) r.extend_columns(columns) assert r.row[0] == ['a', 'b', 'c', 'd', 'c1', 'c2', 'c3'] assert r.row[1] == ['e', 'f', 'g', 'h', 'x1', 'x2', 'x4'] assert r.row[2] == ['i', 'j', 1.1, 1, '', '', ''] r2 = self.testclass(self.testfile) columns = OrderedDict() columns.update({"p": ['c1', 'x1', 'y1', 'z1']}) columns.update({"a": ['c2', 'x2', 'y2']}) columns.update({"d": ['c3', 'x4']}) r2.extend_columns(columns) assert r2.row[0] == ['a', 'b', 'c', 'd', 'c1', 'c2', 'c3'] assert r2.row[1] == ['e', 'f', 'g', 'h', 'x1', 'x2', 'x4'] assert r2.row[2] == ['i', 'j', 1.1, 1, 'y1', 'y2', ''] assert r2.row[3] == ['', '', '', '', 'z1', '', '']
class TestCSVNXlsMultipleSheets: def setUp(self): self.testfile = "multiple1.csv" self.content = OrderedDict() self.content.update( {"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update( {"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update( {"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) pe.save_book_as(dest_file_name=self.testfile, bookdict=self.content) def test_read_multiple_csv_into_book(self): book = pe.load_book(self.testfile) assert book.sheet_names() == ["Sheet1", "Sheet2", "Sheet3"] book["Sheet1"].format(int) assert self.content["Sheet1"] == book["Sheet1"].to_array() book["Sheet2"].format(int) assert self.content["Sheet2"] == book["Sheet2"].to_array() book["Sheet3"].format(int) assert self.content["Sheet3"] == book["Sheet3"].to_array() def tearDown(self): if os.path.exists("multiple1__Sheet1__0.csv"): os.unlink("multiple1__Sheet1__0.csv") if os.path.exists("multiple1__Sheet2__1.csv"): os.unlink("multiple1__Sheet2__1.csv") if os.path.exists("multiple1__Sheet3__2.csv"): os.unlink("multiple1__Sheet3__2.csv")
class TestBook: def setUp(self): app.config['TESTING'] = True self.app = app.test_client() self.content = OrderedDict() self.content.update( {"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update( {"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update( {"Sheet3": [['X', 'Y', 'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) def test_book(self): test_sample = ["book", "book_dict"] expected = { 'result': { 'Sheet1': [[1.0, 1.0, 1.0, 1.0], [2.0, 2.0, 2.0, 2.0], [3.0, 3.0, 3.0, 3.0]], 'Sheet3': [['X', 'Y', 'Z'], [1.0, 4.0, 7.0], [2.0, 5.0, 8.0], [3.0, 6.0, 9.0]], 'Sheet2': [[4.0, 4.0, 4.0, 4.0], [5.0, 5.0, 5.0, 5.0], [6.0, 6.0, 6.0, 6.0]] } } for struct_type in test_sample: io = BytesIO() book = pe.Book(self.content) book.save_to_memory('xls', io) io.seek(0) response = self.app.post('/respond/%s' % struct_type, buffered=True, data={"file": (io, "test.xls")}, content_type="multipart/form-data") assert json.loads(response.data.decode('utf-8')) == expected
def test_to_dict(self): """ Note: data file with column headers are tested in test_filters.py """ r = pe.Reader(self.testfile) result = OrderedDict() result.update({"Series_1": [1, 2, 3, 4]}) result.update({"Series_2": [ 5, 6, 7, 8, ]}) result.update({"Series_3": [9, 10, 11, 12]}) actual = pe.to_dict(r.rows()) assert actual.keys() == result.keys() assert result == actual result = { "Series_1": 1, "Series_2": 2, "Series_3": 3, "Series_4": 4, "Series_5": 5, "Series_6": 6, "Series_7": 7, "Series_8": 8, "Series_9": 9, "Series_10": 10, "Series_11": 11, "Series_12": 12 } actual = pe.to_dict(r.enumerate()) assert result == actual
def test_to_dict(self): """ Note: data file with column headers are tested in test_filters.py """ r = pe.Reader(self.testfile) result = OrderedDict() result.update({"Series_1": [1, 2, 3, 4]}) result.update({"Series_2": [5, 6, 7, 8, ]}) result.update({"Series_3": [9, 10, 11, 12]}) actual = pe.to_dict(r.rows()) assert actual.keys() == result.keys() self.assertEqual(result, actual) result = { "Series_1": 1, "Series_2": 2, "Series_3": 3, "Series_4": 4, "Series_5": 5, "Series_6": 6, "Series_7": 7, "Series_8": 8, "Series_9": 9, "Series_10": 10, "Series_11": 11, "Series_12": 12 } actual = pe.to_dict(r.enumerate()) self.assertEqual(result, actual)
class TestCSVzMultipleSheets: def setUp(self): self.testfile = "multiple1.csvz" self.content = OrderedDict() self.content.update( {"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update( {"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update( {"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) w = pe.BookWriter(self.testfile) w.write_book_from_dict(self.content) w.close() def test_read_multiple_csv_into_book(self): book = pe.load_book(self.testfile) assert book.sheet_names() == ["Sheet1", "Sheet2", "Sheet3"] book["Sheet1"].format(int) assert self.content["Sheet1"] == book["Sheet1"].to_array() book["Sheet2"].format(int) assert self.content["Sheet2"] == book["Sheet2"].to_array() book["Sheet3"].format(int) assert self.content["Sheet3"] == book["Sheet3"].to_array() def tearDown(self): if os.path.exists("multiple1.csvz"): os.unlink("multiple1.csvz")
def _produce_ordered_dict(): data_dict = OrderedDict() data_dict.update({ "Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) data_dict.update({ "Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) data_dict.update({ "Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) return data_dict
class TestCSVzMultipleSheets: def setUp(self): self.testfile = "multiple1.csvz" self.content = OrderedDict() self.content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) w = pe.BookWriter(self.testfile) w.write_book_from_dict(self.content) w.close() def test_read_multiple_csv_into_book(self): book = pe.load_book(self.testfile) assert book.sheet_names() == ["Sheet1", "Sheet2", "Sheet3"] book["Sheet1"].format(int) assert self.content["Sheet1"] == book["Sheet1"].to_array() book["Sheet2"].format(int) assert self.content["Sheet2"] == book["Sheet2"].to_array() book["Sheet3"].format(int) assert self.content["Sheet3"] == book["Sheet3"].to_array() def tearDown(self): if os.path.exists("multiple1.csvz"): os.unlink("multiple1.csvz")
class TestBook: def setUp(self): self.content = OrderedDict() self.content.update({"Sheet1": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) self.content.update({"Sheet2": [[u'A', u'B', u'C'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) self.result1 = [{'Y': 4, 'X': 1, 'Z': 7}, {'Y': 5, 'X': 2, 'Z': 8}, {'Y': 6, 'X': 3, 'Z': 9}] self.result2 = [{'B': 4, 'A': 1, 'C': 7}, {'B': 5, 'A': 2, 'C': 8}, {'B': 6, 'A': 3, 'C': 9}] def test_book_save_to_models(self): model1=FakeDjangoModel("Sheet1") model2=FakeDjangoModel("Sheet2") book = pe.Book(self.content) book.save_to_django_models([model1, model2]) assert model1.objects.objs == self.result1 assert model2.objects.objs == self.result2 def test_model_save_to_models(self): model=FakeDjangoModel("Sheet1") pe.save_book_as(dest_models=[model, None, None], bookdict=self.content) assert model.objects.objs == self.result1 def test_load_book_from_django_model(self): model=FakeDjangoModel("Sheet1") book = pe.Book(self.content) book.save_to_django_models([model]) assert model.objects.objs == self.result1 model._meta.update(["X", "Y", "Z"]) book2 = pe.get_book(models=[model]) assert book2[0].to_array() == book[0].to_array() def test_more_sheets_than_models(self): self.content.update({"IgnoreMe":[[1,2,3]]}) model=FakeDjangoModel("Sheet1") pe.save_book_as(dest_models=[model], bookdict=self.content) assert model.objects.objs == self.result1
class TestCSVSingleSheet: def _write_test_file(self, file, content): """ Make a test file as: 1,1,1,1 2,2,2,2 3,3,3,3 """ w = pe.BookWriter(file) w.write_book_from_dict(content) w.close() def setUp(self): self.testfile = "multiple1.csv" self.content = OrderedDict() self.content.update( {"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update( {"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update( {"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) self._write_test_file(self.testfile, self.content) def test_load_a_single_sheet(self): b1 = pe.load_book(self.testfile, sheet_name="Sheet1") b1['Sheet1'].format(int) assert len(b1.sheet_names()) == 1 assert b1['Sheet1'].to_array() == self.content['Sheet1'] def test_load_a_single_sheet2(self): b1 = pe.load_book(self.testfile, sheet_index=1) b1['Sheet2'].format(int) assert len(b1.sheet_names()) == 1 assert b1['Sheet2'].to_array() == self.content['Sheet2'] @raises(IndexError) def test_load_a_single_sheet3(self): pe.load_book(self.testfile, sheet_index=10000) @raises(ValueError) def test_load_a_single_sheet4(self): pe.load_book(self.testfile, sheet_name="Not exist") def tearDown(self): clean_up_files([ "multiple1__Sheet1__0.csv", "multiple1__Sheet2__1.csv", "multiple1__Sheet3__2.csv", ])
class TestXlsNXlsmMultipleSheets(PyexcelMultipleSheetBase): def setUp(self): self.testfile = "multiple1.xls" self.testfile2 = "multiple1.xlsm" self.content = OrderedDict() self.content.update( {"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update( {"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update( {"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) self._write_test_file(self.testfile) def tearDown(self): self._clean_up()
class TestBook: def setUp(self): app.config["TESTING"] = True self.app = app.test_client() self.content = OrderedDict() self.content.update( {"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update( {"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update( {"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) def test_book(self): test_sample = ["book", "book_dict"] expected = { u"result": { u"Sheet1": [ [1.0, 1.0, 1.0, 1.0], [2.0, 2.0, 2.0, 2.0], [3.0, 3.0, 3.0, 3.0], ], u"Sheet3": [ [u"X", u"Y", u"Z"], [1.0, 4.0, 7.0], [2.0, 5.0, 8.0], [3.0, 6.0, 9.0], ], u"Sheet2": [ [4.0, 4.0, 4.0, 4.0], [5.0, 5.0, 5.0, 5.0], [6.0, 6.0, 6.0, 6.0], ], } } for struct_type in test_sample: io = BytesIO() book = pe.Book(self.content) book.save_to_memory("xls", io) io.seek(0) response = self.app.post( "/respond/%s" % struct_type, buffered=True, data={"file": (io, "test.xls")}, content_type="multipart/form-data", ) assert json.loads(response.data.decode("utf-8")) == expected
class TestBook: def setUp(self): app.config['TESTING'] = True self.app = app.test_client() self.content = OrderedDict() self.content.update( {"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update( {"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update( {"Sheet3": [['X', 'Y', 'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) def test_book(self): for struct_type in ["book", "book_dict"]: io = pe.save_book_as(bookdict=self.content, dest_file_type="xls") response = self.app.post('/exchange/%s' % struct_type, buffered=True, data={"file": (io, "test.xls")}, content_type="multipart/form-data") assert response.content_type == "application/vnd.ms-excel" book2 = pe.get_book(file_type='xls', file_content=response.data) assert book2.to_dict() == self.content
class TestSingleSheetReaderForMulitpleSheetBook: def setUp(self): self.testfile = "multiple1.xls" self.content = OrderedDict() self.content.update( {"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]}) self.content.update( {"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]}) self.content.update( {"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) w = pe.BookWriter(self.testfile) w.write_book_from_dict(self.content) w.close() def test_non_default_sheet_as_single_sheet_reader(self): r = pe.Reader(self.testfile, "Sheet1") data = pe.utils.to_array(r) assert data == self.content["Sheet1"] r2 = pe.Reader(self.testfile, "Sheet2") data = pe.utils.to_array(r2) assert data == self.content["Sheet2"] r3 = pe.Reader(self.testfile, "Sheet3") data = pe.utils.to_array(r3) assert data == self.content["Sheet3"] def test_non_default_sheet_as_single_sheet_reader_series(self): r = pe.SeriesReader(self.testfile, "Sheet3") data = pe.utils.to_array(r.rows()) assert data == self.content["Sheet3"][1:] def test_non_default_sheet_as_single_sheet_plain_reader(self): r = pe.load(self.testfile, "Sheet2") data = pe.utils.to_array(r.rows()) assert data == self.content["Sheet2"] def tearDown(self): if os.path.exists(self.testfile): os.unlink(self.testfile)
class TestBook: def setUp(self): self.content = OrderedDict() self.content.update( {"Sheet1": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) self.content.update( {"Sheet2": [[u"A", u"B", u"C"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) self.result1 = [ { "Y": 4, "X": 1, "Z": 7 }, { "Y": 5, "X": 2, "Z": 8 }, { "Y": 6, "X": 3, "Z": 9 }, ] self.result2 = [ { "B": 4, "A": 1, "C": 7 }, { "B": 5, "A": 2, "C": 8 }, { "B": 6, "A": 3, "C": 9 }, ] def test_book_save_to_models(self): model1 = FakeDjangoModel("Sheet1") model2 = FakeDjangoModel("Sheet2") book = pe.Book(self.content) book.save_to_django_models([model1, model2]) assert model1.objects.objs == self.result1 assert model2.objects.objs == self.result2 @raises(AttributeError) def test_book_save_to_models_with_bulk_save_false(self): """ same to previous test but with different parameters """ model1 = FakeDjangoModel("Sheet1") model2 = FakeDjangoModel("Sheet2") book = pe.Book(self.content) book.save_to_django_models([model1, model2], bulk_save=False) def test_model_save_to_models(self): model = FakeDjangoModel("Sheet1") data = { "Sheet1": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]] } pe.save_book_as(dest_models=[model, None, None], bookdict=data) assert model.objects.objs == self.result1 def test_load_book_from_django_model(self): # if a book has more than one sheet # and it saves to only one model, now it will fail # with an exception. model = FakeDjangoModel("Sheet1") book = pe.Book( {"Sheet1": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]}) book.save_to_django_models([model]) assert model.objects.objs == self.result1 model._meta.update(["X", "Y", "Z"]) book2 = pe.get_book(models=[model]) assert book2[0].to_array() == book[0].to_array() @raises(Exception) def test_more_sheets_than_models(self): self.content.update({"IgnoreMe": [[1, 2, 3]]}) model = FakeDjangoModel("Sheet1") pe.save_book_as(dest_models=[model], bookdict=self.content)
def test_get_book_from_sql(self): book_dict = pe.get_book_dict(session=Session(), tables=[Signature, Signature2]) expected = OrderedDict() expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]}) expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]}) assert book_dict == expected