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")
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
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 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
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_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_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_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_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_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_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_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)
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_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
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 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 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]]})
self._write_test_file(self.testfile, self.content)
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 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 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()
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
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 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 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 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):
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
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_add(self):
s = Sheet(self.data, "test")
s.name_rows_by_column(0)
data = OrderedDict({"Row 5": [10, 11, 12]})
s = s.row + data
assert s.row["Row 5"] == [10, 11, 12]
def test_add(self):
s = Sheet(self.data, "test")
s.name_columns_by_row(2)
data = OrderedDict({"Column 4": [10, 11, 12]})
s = s.column + data
assert s.column["Column 4"] == [10, 11, 12]
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
def test_add(self):
s = Sheet(self.data, "test")
s.name_columns_by_row(0)
data = OrderedDict({"Column 4": [10, 11, 12]})
s = s.column + data
eq_(s.column.Column_4, [10, 11, 12])
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)
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_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_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
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)
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 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_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)