def setUp(self):
"""
Make a test csv file as:
1, 2, 3, 4
5, 6, 7, 8
9, 10, 11, 12
"""
self.testfile1 = "testcsv1.csv"
w = pe.Writer(self.testfile1)
content = [
[1, 'a'],
[2, 'b'],
[3, 'c'],
[4, 'd'],
[5, 'e'],
[6, 'f'],
[7, 'g'],
[8, 'h']
]
w.write_array(content)
w.close()
self.testfile2 = "testcsv2.csv"
w = pe.Writer(self.testfile2)
content = [
[1, 'a', 'c'],
[2, 'b', 'h'],
[3, 'c', 'c'],
[8, 'h', 'd']
]
w.write_array(content)
w.close()
def test_merging(self):
# set up
data = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
import pyexcel as pe
w = pe.Writer("1.csv")
w.write_rows(data)
w.close()
data2 = [['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h', 'i']]
w = pe.Writer("2.csv")
w.write_rows(data2)
w.close()
data3 = [[1.1, 2.2, 3.3], [4.4, 5.5, 6.6], [7.7, 8.8, 9.9]]
w = pe.Writer("3.csv")
w.write_rows(data3)
w.close()
# execute
merged = pe.Sheet()
for file in ["1.csv", "2.csv", "3.csv"]:
r = pe.Reader(file)
merged.row += r
writer = pe.Writer("merged.csv")
writer.write_reader(merged)
writer.close()
r = pe.Reader("merged.csv")
actual = pe.utils.to_array(r)
result = [[u'1', u'2', u'3'], [u'4', u'5', u'6'], [u'7', u'8', u'9'],
[u'a', u'b', u'c'], [u'd', u'e', u'f'], [u'g', u'h', u'i'],
[u'1.1', u'2.2', u'3.3'], [u'4.4', u'5.5', u'6.6'],
[u'7.7', u'8.8', u'9.9']]
assert result == actual
# verify
os.unlink("1.csv")
os.unlink("2.csv")
os.unlink("3.csv")
os.unlink("merged.csv")
def test_write_records(self):
w = pe.Writer(self.testfile)
w.write_dict(self.content)
w.close()
r = pe.SeriesReader(self.testfile)
records = pe.utils.to_records(r)
w2 = pe.Writer(self.testfile)
w2.write_records(records)
w2.close()
r2 = pe.SeriesReader(self.testfile)
actual = pe.utils.to_dict(r2)
assert actual == self.content
def test_not_supported_output_stream(self):
try:
io = StringIO
r = pe.Writer(("sylk", io))
assert 1 == 2
except NotImplementedError:
assert 1 == 1
def test_write_simple_reader_error(self):
try:
w = pe.Writer(self.testfile2)
w.write_reader("abc") # boom
assert 1 == 2
except TypeError:
assert 1 == 1
def setUp(self):
self.testfile = "test.csv"
self.content = [["X", "Y", "Z"], [1, 2, 3], [1, 2, 3], [1, 2, 3],
[1, 2, 3], [1, 2, 3]]
w = pe.Writer(self.testfile)
w.write_array(self.content)
w.close()
def test_series_table(self):
w = pe.Writer(self.testfile)
w.write_dict(self.content)
w.close()
r = pe.SeriesReader(self.testfile)
actual = pe.utils.to_dict(r)
assert actual == self.content
def test_write_series_reader(self):
r = pe.SeriesReader(self.testfile)
w = pe.Writer(self.testfile2)
w.write_reader(r)
w.close()
r2 = pe.SeriesReader(self.testfile2)
content = pe.utils.to_dict(r2)
assert content == self.content
def create_sample_file1(file):
w = pe.Writer(file)
data = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 1.1, 1]
table = []
table.append(data[:4])
table.append(data[4:8])
table.append(data[8:12])
w.write_array(table)
w.close()
def setUp(self):
"""
Make a test csv file as:
1,1,1,1
2,2,2,2
3,3,3,3
"""
self.testfile = "test1.xls"
self.content = {
"X": [1, 2, 3, 4, 5],
"Y": [6, 7, 8, 9, 10],
"Z": [11, 12, 13, 14, 15],
}
w = pe.Writer(self.testfile)
w.write_dict(self.content)
w.close()
self.testfile2 = "test.csv"
self.content2 = {
"O": [1, 2, 3, 4, 5],
"P": [6, 7, 8, 9, 10],
"Q": [11, 12, 13, 14, 15],
}
w = pe.Writer(self.testfile2)
w.write_dict(self.content2)
w.close()
self.testfile3 = "test.xls"
self.content3 = {
"R": [1, 2, 3, 4, 5],
"S": [6, 7, 8, 9, 10],
"T": [11, 12, 13, 14, 15],
}
w = pe.Writer(self.testfile3)
w.write_dict(self.content3)
w.close()
self.testfile4 = "multiple_sheets.xls"
self.content4 = {
"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]],
"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]],
"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]
}
w = pe.BookWriter(self.testfile4)
w.write_book_from_dict(self.content4)
w.close()
def setUp(self):
self.testfile = "test.xlsm"
self.content = [[1.0], ['', 2.0, 2.0, 2.0, 2.0, 2.0],
['', '', 3.0, 3.0, 3.0, 3.0, '', '', 3.0, 3.0, 3.0],
['', '', '', 4.0, 4.0, 4.0, '', '', '', 4.0, 4.0],
['', '', '', '', '', 5.0, 5.0, '', '', ''],
['', '', '', '', '', 6.0]]
w = pe.Writer(self.testfile)
w.write_columns(self.content)
w.close()
def test_csvz_output_stringio(self):
data = [[1, 2, 3], [4, 5, 6]]
io = BytesIO()
w = pe.Writer(("csvz", io))
w.write_rows(data)
w.close()
r = pe.Reader(("csvz", io.getvalue()))
result = ['1', '2', '3', '4', '5', '6']
actual = pe.utils.to_array(r.enumerate())
assert actual == result
def test_xlsm_output_stringio(self):
data = [[1, 2, 3], [4, 5, 6]]
io = BytesIO()
w = pe.Writer(("xlsm", io))
w.write_rows(data)
w.close()
r = pe.load_from_memory("xlsm", io.getvalue())
result = [1, 2, 3, 4, 5, 6]
actual = pe.utils.to_array(r.enumerate())
assert result == actual
def test_xls_output_stringio(self):
data = [[1, 2, 3], [4, 5, 6]]
io = StringIO()
w = pyexcel.Writer(("ods", io))
w.write_rows(data)
w.close()
r = pyexcel.Reader(("ods", io.getvalue()))
result = [1, 2, 3, 4, 5, 6]
actual = pyexcel.utils.to_array(r.enumerate())
assert result == actual
def test_csv_output_stringio(self):
data = [[1, 2, 3], [4, 5, 6]]
io = StringIO()
w = pe.Writer(("csv", io))
w.write_rows(data)
#w.close()
r = pe.Reader(("csv", io.getvalue()))
result = [1, 2, 3, 4, 5, 6]
actual = pe.utils.to_array(r.enumerate())
assert actual == result
def setUp(self):
self.data = {
"1": [1, 2, 3, 4, 5, 6, 7, 8],
"3": [1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8],
"5": [2, 3, 4, 5, 6, 7, 8, 9],
}
self.testfile = "test.xls"
w = pe.Writer(self.testfile)
w.write_dict(self.data)
w.close()
def test_merging(self):
# set up
data = [[1,2,3],[4,5,6],[7,8,9]]
import pyexcel as pe
w=pe.Writer("1.csv")
w.write_rows(data)
w.close()
data2 = [['a','b','c'],['d','e','f'],['g','h','i']]
w=pe.Writer("2.csv")
w.write_rows(data2)
w.close()
data3=[[1.1, 2.2, 3.3],[4.4, 5.5, 6.6],[7.7, 8.8, 9.9]]
w=pe.Writer("3.csv")
w.write_rows(data3)
w.close()
# execute
merged = pe.Reader()
for file in ["1.csv", "2.csv", "3.csv"]:
r = pe.Reader(file)
merged += r
writer = pe.Writer("merged.csv")
writer.write_reader(merged)
writer.close()
r=pe.Reader("merged.csv")
actual = pe.utils.to_array(r)
result = [
[1.0, 2.0, 3.0],
[4.0, 5.0, 6.0],
[7.0, 8.0, 9.0],
['a', 'b', 'c'],
['d', 'e', 'f'],
['g', 'h', 'i'],
[1.1, 2.2, 3.3],
[4.4, 5.5, 6.6],
[7.7, 8.8, 9.9]
]
assert result == actual
# verify
os.unlink("1.csv")
os.unlink("2.csv")
os.unlink("3.csv")
os.unlink("merged.csv")
def test_write_utf_and_read_back(self):
data = [[u'白', u'日', u'依', u'山', u'尽'], [u'黄', u'河', u'入', u'海', u'流'],
[u'欲', u'穷', u'千', u'里', u'目'], [u'更', u'上', u'一', u'层', u'楼']]
w = pyexcel.Writer("utf.csv")
w.write_rows(data)
w.close()
r = pyexcel.Reader("utf.csv")
expected = pyexcel.utils.to_array(r)
print(expected)
print(data)
assert expected == data
def setUp(self):
self.testfile = "test.xlsm"
self.content = [[1, "", "", "", "", ""], [1, 2, "", "", "", ""],
[1, 2, 3, "", "", ""], [1, 2, 3, 4, "", ""],
[1, 2, 3, 4, 5, ""], [1, 2, 3, 4, 5, 6],
[1, "", "", "", "", ""], [1, 2, "", "", "", ""],
[1, 2, 3, "", "", ""], [1, 2, 3, 4, "", ""],
[1, 2, 3, 4, 5, ""]]
w = pe.Writer(self.testfile)
w.write_array(self.content)
w.close()
def setUp(self):
self.testfile = "test.csv"
self.content = {
"X": [1, 2, 3, 4, 5],
"Y": [6, 7, 8, 9, 10],
"Z": [11, 12, 13, 14, 15],
}
w = pe.Writer(self.testfile)
w.write_dict(self.content)
w.close()
self.testfile2 = "test.xlsm"
def setUp(self):
self.testfile = "test.csv"
self.content = {
"X": ["1", "2", "3", "4", "5"],
"Y": ["6", "7", "8", "9", "10"],
"Z": ["11", "12", "13", "14", "15"]
}
w = pe.Writer(self.testfile)
w.write_dict(self.content)
w.close()
self.testfile2 = "test.xlsm"
def setUp(self):
self.data = {
"1": [1, 2, 3, 4, 5, 6, 7, 8],
"2": ["1", "2", "3", "4", "5", "6", "7", "8"],
"3": [1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8],
"4": ["1.1", "2.2", "3.3", "4.4", "5.5", "6.6", "7,7", "8.8"],
"5": [2, 3, 4, 5, 6, 7, 8, 9],
"6": ["2", "3", "4", "5", "6", "7", "8", "9"]
}
self.testfile = "test.csv"
w = pe.Writer(self.testfile)
w.write_dict(self.data)
w.close()
def setUp(self):
self.testfile = "test.csv"
self.content = [
["X", "Y", "Z"],
[1, 1, 1],
[2, 2, 2],
[3, 3, 3],
[4, 4, 4],
[5, 5, 5]
]
w = pe.Writer(self.testfile)
w.write_array(self.content)
w.close()
def create_sample_file(self, file):
"""
1,2,3,4
5,6,7,8
9,10,11,12
"""
w = pe.Writer(file, delimiter=":")
table = []
for i in [0, 4, 8]:
array = [i+1, i+2, i+3, i+4]
table.append(array)
w.write_array(table)
w.close()
def setUp(self):
self.testfile = "test.xlsm"
self.content = {
'Series_6': ['', '', '', '', '', 6.0],
'Series_5': ['', '', '', '', '', 5.0, 5.0, '', '', ''],
'Series_4': ['', '', '', 4.0, 4.0, 4.0, '', '', '', 4.0, 4.0],
'Series_3': ['', '', 3.0, 3.0, 3.0, 3.0, '', '', 3.0, 3.0, 3.0],
'Series_2': ['', 2.0, 2.0, 2.0, 2.0, 2.0],
'Series_1': [1.0]
}
w = pe.Writer(self.testfile)
w.write_dict(self.content)
w.close()
def setUp(self):
"""
Make a test csv file as:
1,2,3,4
5,6,7,8
9,10,11,12
"""
self.testfile = "test.xls"
self.content = {"X": [1, 2, 3], "Y": [4, 5, 6], "Z": [7, 8, 9]}
w = pe.Writer(self.testfile)
w.write_dict(self.content)
w.close()
def setUp(self):
self.testfile = "test.xlsx"
self.content = [
["X", "Y", "Z"],
[1, 11, 12],
[2, 21, 22],
[3, 31, 32],
[4, 41, 42],
[5, 51, 52]
]
w = pe.Writer(self.testfile)
w.write_array(self.content)
w.close()
def setUp(self):
self.data = {
"1": [1, 2, 3, 4, 5, 6, 7, 8],
"2": ["1.0", "2.0", "3.0", "4.0", "5.0", "6.0", "7.0", "8.0"],
"3": [1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8],
"4": ["1.1", "2.2", "3.3", "4.4", "5.5", "6.6", "7.7", "8.8"],
"5": [2, 3, 4, 5, 6, 7, 8, 9],
"6": ["2", "3", "4", "5", "6", "7", "8", "9"]
}
self.io = BytesIO()
w = pe.Writer(("xls", self.io))
w.write_dict(self.data)
w.close()
self.test_tuple = ("xls", self.io.getvalue())
def create_sample_file2_in_memory(file_type):
"""
1,2,3,4
5,6,7,8
9,10,11,12
"""
io = BytesIO()
w = pe.Writer((file_type, io))
table = []
for i in [0, 4, 8]:
array = [i + 1, i + 2, i + 3, i + 4]
table.append(array)
w.write_array(table)
w.close()
return io
def test_write_reader(self):
"""
Use reader as data container
this test case shows the file written by pe
can be read back by itself
"""
self._create_a_file(self.testfile)
r = pe.Reader(self.testfile)
w2 = pe.Writer(self.testfile2)
w2.write_reader(r)
w2.close()
r2 = pe.Reader(self.testfile2)
actual = pe.utils.to_array(r2.rows())
assert actual == self.content