def test_get_book_from_file(self):
test_file = "test_get_book.xls"
content = _produce_ordered_dict()
book = pe.Book(content)
book.save_as(test_file)
book_stream = pe.iget_book(file_name=test_file)
assert book_stream.to_dict() != content
book3 = pe.Book(book_stream.to_dict())
eq_(book3.to_dict(), content)
os.unlink(test_file)
def test_book_presentation(self):
data = {
'Sheet 1': [[1.0, 2.0, 3.0], [4.0, 5.0, 6.0], [7.0, 8.0, 9.0]],
'Sheet 2': [['X', 'Y', 'Z'], [1.0, 2.0, 3.0], [4.0, 5.0, 6.0]],
'Sheet 3': [['O', 'P', 'Q'], [3.0, 2.0, 1.0], [4.0, 3.0, 2.0]]
}
book = pe.Book(data)
content = dedent("""
Sheet Name: Sheet 1
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 4 | 5 | 6 |
+---+---+---+
| 7 | 8 | 9 |
+---+---+---+
Sheet Name: Sheet 2
+---+---+---+
| X | Y | Z |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 4 | 5 | 6 |
+---+---+---+
Sheet Name: Sheet 3
+---+---+---+
| O | P | Q |
+---+---+---+
| 3 | 2 | 1 |
+---+---+---+
| 4 | 3 | 2 |
+---+---+---+
""").strip("\n")
assert str(book) == content
def test_issue_125():
book = pe.Book()
book += pe.Sheet([[1]], 'A')
book += pe.Sheet([[2]], 'B')
eq_(book.sheet_names(), ['A', 'B'])
book.sort_sheets(reverse=True)
eq_(book.sheet_names(), ['B', 'A'])
def __init__(self, month):
# TODO improve report_dates to be a month and run for the month until not the month
super().__init__(report_dates=month, report_type='monthly_mars_report')
self.report_model = pe.Book()
# self.final_report_fields = ['Absent', 'Late', 'DND Duration', 'Duration', 'numDND', 'Inbound Ans',
# 'Inbound Lost', 'Outbound', 'Inbound Duration', 'Outbound Duration']
self.final_report_fields = {'Absent', 'Late', 'Duration'}
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_parse_book(self):
test_sample = ["book", "book_dict"]
expected_dict = {
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 data_struct_type in test_sample:
sheet = pe.Book(self.book_content)
tmp_filename = "test.xls"
sheet.save_as(tmp_filename)
with open(tmp_filename, "rb") as fp:
response = self.client.post("/polls/parse/" + data_struct_type,
data={"file": fp})
self.assertEqual(json.loads(response.content.decode("utf-8")),
expected_dict)
os.unlink(tmp_filename)
def test_issue_60_chinese_text_in_python_2_stdout_on_book():
import sys
adict = {"Sheet 1": [["这", "是", "中", "文"], ["这", "是", "中", "文"]]}
book = p.Book()
book.bookdict = adict
sys.stdout.write(repr(book))
def measure_compilation_time(build_dir):
# Create the sheet
print('************ Measuring the compilation times **********')
sheet = pyexcel.Sheet()
rownames = [benchmark for benchmark, _ in support.benchmarks_gen()]
sheet.column += rownames
# Time how long it takes to build the benchmark using the timeit module, and the commands in the make.out
# file present in the build directory.
def build_time(benchmark_dir):
os.chdir(benchmark_dir)
return (timeit.timeit(
'subprocess.check_call(["bash", "make.out"], stderr=subprocess.DEVNULL, stdout=subprocess.DEVNULL)',
number=30,
setup='import subprocess') / 30)
# Fill in the sheet
times = []
for benchmark, _ in support.benchmarks_gen():
print('************ ' + benchmark + ' **********')
benchmark_dir = os.path.join(build_dir, benchmark)
times.append(build_time(benchmark_dir))
sheet.column += times
# Create the report book and write it out
report = pyexcel.Book(sheets={'Compilation times': sheet})
report.save_as(os.path.join(config.reports_dir, 'compilation_times.ods'))
def test_book_presentation(self):
data = {
'Sheet 1': [[1.0, 2.0, 3.0], [4.0, 5.0, 6.0], [7.0, 8.0, 9.0]],
'Sheet 2': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]],
'Sheet 3': [['O', 'P', 'Q'], [3.0, 2.0, 1.0], [4.0, 3.0, 2.0]]
}
book = pe.Book(data)
content = dedent("""
Sheet 1:
+-----+-----+-----+
| 1.0 | 2.0 | 3.0 |
+-----+-----+-----+
| 4.0 | 5.0 | 6.0 |
+-----+-----+-----+
| 7.0 | 8.0 | 9.0 |
+-----+-----+-----+
Sheet 2:
+---+---+---+
| X | Y | Z |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 4 | 5 | 6 |
+---+---+---+
Sheet 3:
+-----+-----+-----+
| O | P | Q |
+-----+-----+-----+
| 3.0 | 2.0 | 1.0 |
+-----+-----+-----+
| 4.0 | 3.0 | 2.0 |
+-----+-----+-----+""").strip("\n")
self.assertEqual(str(book), content)
def to_excel(types, rows, result_format, query_string):
"""Save result to spreadsheet document.
Args:
types: query result_types.
rows: query result_rows.
result_format: One of 'xls', 'xlsx', or 'ods'.
query_string: The query string (is written to the document).
Returns:
The (binary) file contents.
"""
assert result_format in ('xls', 'xlsx', 'ods')
resp = io.BytesIO()
book = pyexcel.Book(
OrderedDict(
[
('Results', _result_array(types, rows)),
('Query', [['Query'], [query_string]]),
]
)
)
book.save_to_memory(result_format, resp)
resp.seek(0)
return resp
def test_issue_125():
book = p.Book()
book += p.Sheet([[1]], "A")
book += p.Sheet([[2]], "B")
eq_(book.sheet_names(), ["A", "B"])
book.sort_sheets(reverse=True)
eq_(book.sheet_names(), ["B", "A"])
def test_book_presentation(self):
data = {
'Sheet 1': [[1.0, 2.0, 3.0], [4.0, 5.0, 6.0], [7.0, 8.0, 9.0]],
'Sheet 2': [['X', 'Y', 'Z'], [1.0, 2.0, 3.0], [4.0, 5.0, 6.0]],
'Sheet 3': [['O', 'P', 'Q'], [3.0, 2.0, 1.0], [4.0, 3.0, 2.0]]
}
book = pe.Book(data)
content = dedent("""
Sheet 1:
- - -
1 2 3
4 5 6
7 8 9
- - -
Sheet 2:
--- --- ---
X Y Z
1.0 2.0 3.0
4.0 5.0 6.0
--- --- ---
Sheet 3:
--- --- ---
O P Q
3.0 2.0 1.0
4.0 3.0 2.0
--- --- ---""").strip("\n")
self.assertEqual(book.simple, content)
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_a_dictionary_of_sheet():
test_data = [["a", "b"]]
book_dict = {"test": p.Sheet(test_data)}
book = p.Book(book_dict)
eq_(book.test.array, test_data)
def test_get_book_from_memory(self):
content = _produce_ordered_dict()
io = pe.save_book_as(dest_file_type="xls", bookdict=content)
book_stream = pe.iget_book(file_content=io.getvalue(), file_type="xls")
assert book_stream.to_dict() != content
book = pe.Book(book_stream.to_dict())
eq_(book.to_dict(), content)
def test_book_presentation(self):
data = {
"Sheet 1": [[1.0, 2.0, 3.0], [4.0, 5.0, 6.0], [7.0, 8.0, 9.0]],
"Sheet 2": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]],
"Sheet 3": [["O", "P", "Q"], [3.0, 2.0, 1.0], [4.0, 3.0, 2.0]],
}
book = pe.Book(data)
content = dedent("""
Sheet 1:
+-----+-----+-----+
| 1.0 | 2.0 | 3.0 |
+-----+-----+-----+
| 4.0 | 5.0 | 6.0 |
+-----+-----+-----+
| 7.0 | 8.0 | 9.0 |
+-----+-----+-----+
Sheet 2:
+---+---+---+
| X | Y | Z |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 4 | 5 | 6 |
+---+---+---+
Sheet 3:
+-----+-----+-----+
| O | P | Q |
+-----+-----+-----+
| 3.0 | 2.0 | 1.0 |
+-----+-----+-----+
| 4.0 | 3.0 | 2.0 |
+-----+-----+-----+""").strip("\n")
self.assertEqual(str(book), content)
def test_sheet_ordering():
test_data = [["a", "b"]]
book_dict = {"first": test_data, "middle": test_data, "last": test_data}
book = p.Book(book_dict)
eq_(book.sheet_names(), ["first", "middle", "last"])
def test_book_len():
test_data = [["a", "b"]]
book_dict = {"test": p.Sheet(test_data)}
book = p.Book(book_dict)
eq_(len(book.test.array), 1)
def test_issue_09():
p.book.LOCAL_UUID = 0
merged = p.Book()
sheet1 = p.Sheet(sheet=[[1, 2]])
sheet2 = p.Sheet(sheet=[[1, 2]])
merged += sheet1
merged += sheet2
eq_(merged[1].name, "pyexcel sheet_1")
def test_issue_09(self):
pe.utils.LOCAL_UUID = 0
merged = pe.Book()
sheet1 = pe.Sheet(sheet=[[1,2]])
sheet2 = pe.Sheet(sheet=[[1,2]])
merged += sheet1
merged += sheet2
assert merged[1].name == "pyexcel_1"
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_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_issue_09(self):
pe.utils.LOCAL_UUID = 0
merged = pe.Book()
sheet1 = pe.Sheet(sheet=[[1,2]])
sheet2 = pe.Sheet(sheet=[[1,2]])
merged += sheet1
merged += sheet2
self.assertEqual(merged[1].name, "pyexcel sheet_1")
def test_issue_68():
data = [[1]]
sheet = p.Sheet(data)
stream = sheet.save_to_memory("csv")
eq_(stream.read(), "1\r\n")
data = {"sheet": [[1]]}
book = p.Book(data)
stream = book.save_to_memory("csv")
eq_(stream.read(), "1\r\n")
def to_excel(types, rows, result_format, query_string):
assert result_format in ('xls', 'xlsx', 'ods')
respIO = io.BytesIO()
book = pyexcel.Book(
OrderedDict([('Results', _result_array(types, rows)),
('Query', [['Query'], [query_string]])]))
book.save_to_memory(result_format, respIO)
respIO.seek(0)
return respIO
def test_get_book_from_file(self):
test_file = "test_get_book.xls"
content = _produce_ordered_dict()
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_issue_68():
data = [[1]]
sheet = pe.Sheet(data)
stream = sheet.save_to_memory('csv')
eq_(stream.read(), '1\r\n')
data = {"sheet": [[1]]}
book = pe.Book(data)
stream = book.save_to_memory('csv')
eq_(stream.read(), '1\r\n')
def test_book_save_to_stringio(self):
data = {"Sheet 1": [[1, 2, 3], [4, 5, 6]]}
book = pe.Book(data)
io = BytesIO()
book.save_to_memory("xlsm", io)
b = pe.load_book_from_memory("xlsm", io.getvalue())
result = [1, 2, 3, 4, 5, 6]
actual = pe.utils.to_array(b[0].enumerate())
assert result == actual
def test_book_save_a_dict(self):
data = [[1, 4, 'X'], [2, 5, 'Y'], [3, 6, 'Z']]
sheet1 = Signature.__tablename__
sheet_dict = {sheet1: data}
book = pe.Book(sheet_dict)
book[sheet1].transpose()
book[sheet1].name_columns_by_row(2)
book.save_to_database(self.session, [Signature])
result = pe.get_dict(session=self.session, table=Signature)
assert result == {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
def test_file_in_the_csvz_has_csv_extension(self):
data = OrderedDict()
data["sheet1"] = [[1, 2, 3]]
data["sheet2"] = [[1, 2, 3]]
data["sheet3"] = [[1, 2, 3]]
book = pe.Book(data)
book.save_as(self.testfile)
zip = zipfile.ZipFile(self.testfile, 'r')
list_of_files = zip.namelist()
assert list_of_files == ['sheet1.csv', 'sheet2.csv', 'sheet3.csv']
