def test_pyexcel_issue_140():
TestSheet1 = p.Sheet()
TestSheet1[4, 4] = "4x4"
TestSheet1[0, 0] = "0,0"
expected = [
["0,0", "", "", "", ""],
["", "", "", "", ""],
["", "", "", "", ""],
["", "", "", "", ""],
["", "", "", "", "4x4"],
]
eq_(expected, TestSheet1.to_array())
def test_data_frame(self):
content = [["", "Column 1", "Column 2", "Column 3"],
["Row 1", 1, 2, 3], ["Row 2", 4, 5, 6], ["Row 3", 7, 8, 9]]
s = pe.Sheet(content, name_rows_by_column=0, name_columns_by_row=0)
content = dedent("""
pyexcel sheet:
Column 1 Column 2 Column 3
----- ---------- ---------- ----------
Row 1 1 2 3
Row 2 4 5 6
Row 3 7 8 9""").strip('\n')
self.assertEqual(s.simple, content)
def test_save_file_as_another_one(self):
data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
sheet = pe.Sheet(data)
testfile = "testfile.xls"
testfile2 = "testfile2.csv"
sheet.save_as(testfile)
pe.save_as(file_name=testfile, dest_file_name=testfile2)
sheet = pe.get_sheet(file_name=testfile2)
sheet.format(int)
eq_(sheet.to_array(), data)
os.unlink(testfile)
os.unlink(testfile2)
def test_sheet_save_to_django_model_3(self):
model=FakeDjangoModel()
sheet = pe.Sheet(self.data)
sheet.name_columns_by_row(0)
def wrapper(row):
row[0] = row[0] + 1
return row
sheet.save_to_django_model(model, initializer=wrapper)
assert model.objects.objs == [
{'Y': 2, 'X': 2, 'Z': 3},
{'Y': 5, 'X': 5, 'Z': 6}
]
def test_save_file_as_texttable(self):
"""
test if _texttable can render generator or not
"""
data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
sheet = pe.Sheet(data)
testfile = "testfile.xls"
testfile2 = "testfile2.texttable"
sheet.save_as(testfile)
pe.isave_as(file_name=testfile, dest_file_name=testfile2)
os.unlink(testfile)
os.unlink(testfile2)
def test_column_series(self):
content = [["Column 1", "Column 2", "Column 3"], [1, 2, 3], [4, 5, 6],
[7, 8, 9]]
s = pe.Sheet(content, name_columns_by_row=0)
content = dedent("""
pyexcel sheet:
Column 1 Column 2 Column 3
---------- ---------- ----------
1 2 3
4 5 6
7 8 9""").strip('\n')
self.assertEqual(s.simple, content)
def save_data(self, search_word):
try:
csv_data = self.get_detail_page(search_word)
sheet = pyexcel.Sheet()
for data in csv_data:
sheet.row += pyexcel.get_sheet(adict=data, transpose_after=True)
sheet.colnames = ['title', 'author', 'source', 'info', 'date', 'kws', 'cited', 'downed', 'abstract', 'fund', 'download']
print(sheet)
sheet.save_as(self.csvname)
except Exception as e:
print('404 error!%s' % e)
def test_get_sheet_from_file(self):
data = [
["X", "Y", "Z"],
[1, 2, 3],
[4, 5, 6]
]
sheet = pe.Sheet(data)
testfile = "testfile.xls"
sheet.save_as(testfile)
sheet = pe.get_sheet(file_name=testfile)
assert sheet.to_array() == data
os.unlink(testfile)
def getPlotCSV():
print("new request to build CSV file")
data = json.loads(str(request.data))
sheet = pe.Sheet(data)
io = StringIO.StringIO()
sheet.save_to_memory("csv", io)
output = make_response(io.getvalue())
output.headers["Content-Disposition"] = "attachment; filename=export.csv"
output.headers["Content-type"] = "text/csv"
#return send_file("Adjacency.csv", as_attachment=True, mimetype='application/vnd.ms-excel')
return output
def test_row_value_filter(self):
data = [
['a', 'b', 'c', 'd', 'e'],
[1, 2, 3, 4, 1]
]
sheet = pe.Sheet(data)
sheet.filter(pe.ColumnValueFilter(lambda column: column[1] == 1).invert())
expected = [
['a', 'e'],
[1, 1]
]
assert sheet.to_array() == expected
def test_row_series(self):
content = [["Row 1", 1, 2, 3], ["Row 2", 4, 5, 6], ["Row 3", 7, 8, 9]]
s = pe.Sheet(content, name_rows_by_column=0)
content = dedent("""
Sheet Name: pyexcel
+-------+---+---+---+
| Row 1 | 1 | 2 | 3 |
+-------+---+---+---+
| Row 2 | 4 | 5 | 6 |
+-------+---+---+---+
| Row 3 | 7 | 8 | 9 |
+-------+---+---+---+""").strip('\n')
assert str(s) == content
def test_normal_usage(self):
content = [[1, 2, 3], [4, 588, 6], [7, 8, 999]]
s = pe.Sheet(content)
content = dedent("""
Sheet Name: pyexcel
+---+-----+-----+
| 1 | 2 | 3 |
+---+-----+-----+
| 4 | 588 | 6 |
+---+-----+-----+
| 7 | 8 | 999 |
+---+-----+-----+""").strip('\n')
assert str(s) == content
def test_normal_usage2(self):
data = [
# 0 1 2 3 4 5 6
[1, 2, 3, 4, 5, 6, 7], # 0
[21, 22, 23, 24, 25, 26, 27],
[31, 32, 33, 34, 35, 36, 37],
[41, 42, 43, 44, 45, 46, 47],
[51, 52, 53, 54, 55, 56, 57] # 4
]
s = pe.Sheet(data)
s.add_filter(pe.filters.RegionFilter(slice(1, 4, 1), slice(1, 5, 1)))
expected = [[22, 23, 24, 25], [32, 33, 34, 35], [42, 43, 44, 45]]
assert s.to_array() == expected
def test_normal_usage(self):
content = [[1, 2, 3], [4, 588, 6], [7, 8, 999]]
s = pe.Sheet(content)
# note, plus the trailing '\n'
# due to tabluate produces the extra new line
content = dedent("""
pyexcel sheet:
- --- ---
1 2 3
4 588 6
7 8 999
- --- ---""").strip('\n')
self.assertEqual(s.simple, content)
def test_save_an_array7(self):
data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
sheet = pe.Sheet(data)
sheet.name_columns_by_row(0)
def make_signature(row):
return Signature(X=row["X"], Y=row["Y"], Z=row["Z"])
sheet.save_to_database(self.session,
Signature,
initializer=make_signature)
result = pe.get_dict(session=self.session, table=Signature)
assert result == {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
def test_region(self):
data = [
# 0 1 2 3 4 5 6
[1, 2, 3, 4, 5, 6, 7], # 0
[21, 22, 23, 24, 25, 26, 27],
[31, 32, 33, 34, 35, 36, 37],
[41, 42, 43, 44, 45, 46, 47],
[51, 52, 53, 54, 55, 56, 57] # 4
]
s = pe.Sheet(data)
data = s.region([1, 1], [4, 5])
expected = [[22, 23, 24, 25], [32, 33, 34, 35], [42, 43, 44, 45]]
assert data == expected
def test_normal_usage(self):
content = [[1, 2, 3], [4, 588, 6], [7, 8, 999]]
s = pe.Sheet(content)
content = dedent("""
pyexcel sheet:
+---+-----+-----+
| 1 | 2 | 3 |
+---+-----+-----+
| 4 | 588 | 6 |
+---+-----+-----+
| 7 | 8 | 999 |
+---+-----+-----+""").strip("\n")
self.assertEqual(str(s), content)
def test_save_as_and_append_colnames(self):
data = [[1, 2, 3], [4, 5, 6]]
sheet = pe.Sheet(data)
testfile = "testfile.xls"
testfile2 = "testfile.xls"
sheet.save_as(testfile)
pe.save_as(
file_name=testfile,
dest_file_name=testfile2,
colnames=["X", "Y", "Z"],
)
array = pe.get_array(file_name=testfile2)
eq_(array, [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]])
def test_row_series(self):
content = [["Row 1", 1, 2, 3], ["Row 2", 4, 5, 6], ["Row 3", 7, 8, 9]]
s = pe.Sheet(content, name_rows_by_column=0)
content = dedent("""
pyexcel sheet:
+-------+---+---+---+
| Row 1 | 1 | 2 | 3 |
+-------+---+---+---+
| Row 2 | 4 | 5 | 6 |
+-------+---+---+---+
| Row 3 | 7 | 8 | 9 |
+-------+---+---+---+""").strip("\n")
self.assertEqual(str(s), content)
def usufyToTextExport(d, fPath=None):
"""
Workaround to export to a .txt file or to show the information.
Args:
-----
d: Data to export.
fPath: File path for the output file. If None was provided, it will
assume that it has to print it.
Returns:
--------
unicode: It sometimes returns a unicode representation of the Sheet
received.
"""
# Manual check...
if d == []:
return "+------------------+\n| No data found... |\n+------------------+"
import pyexcel as pe
import pyexcel.ext.text as text
if fPath == None:
isTerminal = True
else:
isTerminal = False
try:
oldData = get_data(fPath)
except:
# No information has been recovered
oldData = {"OSRFramework": []}
# Generating the new tabular data
tabularData = _generateTabularData(d, {"OSRFramework": [[]]},
True,
canUnicode=False)
# The tabular data contains a dict representing the whole book and we need only the sheet!!
sheet = pe.Sheet(tabularData["OSRFramework"])
sheet.name = "Profiles recovered (" + getCurrentStrDatetime() + ")."
# Defining the headers
sheet.name_columns_by_row(0)
text.TABLEFMT = "grid"
try:
with open(fPath, "w") as oF:
oF.write(str(sheet))
except Exception as e:
# If a fPath was not provided... We will only print the info:
return unicode(sheet)
def test_book_file(self):
data = {
"category": [["id", "name"], [1, "News"], [2, "Sports"]],
"post": [["id", "title", "body", "pub_date", "category"],
[
1, "Title A", "formal",
datetime(2015, 1, 20, 23, 28, 29), "News"
],
[
2, "Title B", "informal",
datetime(2015, 1, 20, 23, 28, 30), "Sports"
]]
}
for upload_file_type in ['xls']:
print("Uploading %s" % upload_file_type)
file_name = "test.%s" % upload_file_type
io = pe.save_book_as(bookdict=data,
dest_file_type=upload_file_type)
if not PY2:
if isinstance(io, BytesIO):
content = io.getvalue()
else:
content = io.getvalue().encode('utf-8')
else:
content = io.getvalue()
response = self.app.post('/upload/all',
upload_files=[('file', file_name, content)
])
ret = pe.get_book_dict(file_type="xls", file_content=response.body)
assert data['category'] == ret['category']
sheet = pe.Sheet(data['post'], name_columns_by_row=0)
sheet.column.format("pub_date", lambda d: d.isoformat())
sheet2 = pe.Sheet(ret['post'], name_columns_by_row=0)
for key in sheet.colnames:
if key == "category":
continue
assert sheet.column[key] == sheet2.column[key]
assert sheet2.column['category_id'] == [1, 2]
def test_issue_03(self):
file_prefix = "issue_03_test"
csv_file = "%s.csv" % file_prefix
xls_file = "%s.xls" % file_prefix
my_sheet_name = "mysheetname"
data = [[1,1]]
sheet = pe.Sheet(data, name=my_sheet_name)
sheet.save_as(csv_file)
assert(os.path.exists(csv_file))
sheet.save_as(xls_file)
book = pe.load_book(xls_file)
assert book.sheet_names()[0] == my_sheet_name
os.unlink(csv_file)
os.unlink(xls_file)
def test_row_value_filter(self):
data = [
['a', 1, 2, 3, 4, 1],
['b', 2, 3, 4, 5, 7],
['c', 3, 4, 5, 6, 7]
]
sheet = pe.Sheet(data, name_rows_by_column=0)
sheet.filter(pe.NamedColumnValueFilter(lambda column: column['a'] == 3).invert())
expected = [
['a', 3],
['b', 4],
['c', 5]
]
assert sheet.to_array() == expected
def setUp(self):
file_type = "xlsx"
io = BytesIO()
self.content = [
["X", "Y", "Z"],
[1, 11, 12],
[2, 21, 22],
[3, 31, 32],
[4, 41, 42],
[5, 51, 52]
]
sheet = pe.Sheet(pe.transpose(self.content), name_rows_by_column=0)
sheet.save_to_memory(file_type, io)
self.test_tuple = (file_type, io.getvalue())
def test_empty_series_reader(self):
# debug this further
s = p.Sheet() # seriesreader is gone since v0.0.7
assert s.name == "pyexcel sheet"
test_data = [
[1, 2, 3],
[4, 5, 6],
["Column 1", "Column 2", "Column 3"],
]
s.column += p.internal.sheets.transpose(test_data)
actual = s.array
assert test_data == actual
s.name_columns_by_row(2)
assert s.colnames == test_data[2]
def test_irregular_usage(self):
"""textable doesn't like empty string """
content = [[1, 2, 3], [4, 588, 6], [7, 8]] # one empty string
s = pe.Sheet(content)
content = dedent("""
pyexcel sheet:
+---+-----+---+
| 1 | 2 | 3 |
+---+-----+---+
| 4 | 588 | 6 |
+---+-----+---+
| 7 | 8 | |
+---+-----+---+""").strip("\n")
self.assertEqual(str(s), content)
def test_row_value_filter(self):
data = [
['a', 'b', 'c', 'd', 'e'],
[1, 2, 3, 4, 1],
[2, 3, 4, 5, 7],
[3, 4, 5, 6, 7]
]
sheet = pe.Sheet(data, name_columns_by_row=0)
sheet.filter(pe.NamedRowValueFilter(lambda row: row['a'] == 3).invert())
expected = [
['a', 'b', 'c', 'd', 'e'],
[3, 4, 5, 6, 7]
]
assert sheet.to_array() == expected
def test_get_records_from_file(self):
data = [
["X", "Y", "Z"],
[1, 2, 3],
[4, 5, 6]
]
sheet = pe.Sheet(data)
testfile = "testfile.xls"
sheet.save_as(testfile)
result = pe.iget_records(file_name=testfile)
eq_(list(result), [
{"X": 1, "Y": 2, "Z": 3},
{"X": 4, "Y": 5, "Z": 6}
])
os.unlink(testfile)
def json_to_csv(res):
d = []
l = len(res["results"])
d.append((res["results"][0]["_source"]).keys())
for i in range(l):
d.append((res["results"][i]["_source"]).values())
sheet = pe.Sheet(d)
io = StringIO.StringIO()
sheet.save_to_memory("csv", io)
output = make_response(io.getvalue())
output.headers["Content-Disposition"] = "attachment; filename=convert.csv"
output.headers["Content-type"] = "text/csv"
return output
def export_ods(self) -> None:
print(f'Ukládám výsledky do ODS ...')
# save main values
table = self.export_table_data()
sheet = pyexcel.Sheet(name='stats', sheet=table, name_columns_by_row=0)
sheet.save_as(self.args.output)
# add formulas (pyexcel can't do those, use ezodf instead)
doc = ezodf.opendoc(self.args.output)
doc.backup = False
sheet = doc.sheets[0]
sheet.append_rows(2)
lastrow = sheet.nrows() - 1
sheet[lastrow, 2].formula = '=SUBTOTAL(9;C:C)'
sheet[lastrow, 3].formula = '=SUBTOTAL(9;D:D)'
sheet[lastrow, 4].formula = '=SUBTOTAL(9;E:E)'
doc.save()
