def test_merge_two_csv_files_2(self):
file1 = os.path.join("tests", "fixtures",
"test_cook_book_merge_numbers_2.csv")
file2 = os.path.join("tests", "fixtures",
"test_cook_book_merge_alphabets_2.csv")
file_list = [file1, file2]
pe.cookbook.merge_files(file_list)
r = pe.Reader("pyexcel_merged.csv")
data = pe.utils.to_array(r)
print(data)
actual = [[1, 2, 3, '', '', '', '', 'a', 'b', 'c', '', ''],
[4, 5, 6, 10, 11, 12, '', 'd', 'e', 'f', 'm', 'q'],
[7, 8, 9, '', '', '', 9, 'g', 'h', 'I', 'n', 'p']]
assert actual == data
def test_slice(self):
r = pe.Reader(self.testfile)
content1 = [[1, 1, 1, 1]]
assert content1 == r.row[0:1]
content2 = [[1, 1, 1, 1], [2, 2, 2, 2]]
assert content2 == r.row[0:2]
assert content2 == r.row[:2]
content3 = [[2, 2, 2, 2], [3, 3, 3, 3]]
assert content3 == r.row[1:]
content4 = [[1, 1, 1, 1], [2, 2, 2, 2]]
assert content4 == r.row[0:2:1]
content5 = [1, 1, 1, 1]
assert [content5] == r.row[0:0]
r.row[2:1] # bang
def test_slice(self):
r = pe.Reader(self.testfile)
content1 = [['1', '1', '1', '1']]
assert content1 == r.row[0:1]
content2 = [["1", "1", "1", "1"], ["2", "2", "2", "2"]]
assert content2 == r.row[0:2]
assert content2 == r.row[:2]
content3 = [["2", "2", "2", "2"], ["3", "3", "3", "3"]]
assert content3 == r.row[1:]
content4 = [["1", "1", "1", "1"], ["2", "2", "2", "2"]]
assert content4 == r.row[0:2:1]
content5 = ["1", "1", "1", "1"]
assert [content5] == r.row[0:0]
r.row[2:1] # bang
def test_add_rows_odd_column_filter(self):
r = pe.Reader(self.testfile)
r.add_filter(pe.filters.OddColumnFilter())
assert r.number_of_rows() == 3
assert r.number_of_columns() == 2
result = [2, 4, 6, 8, 10, 12]
actual = pe.utils.to_array(r.enumerate())
assert result == actual
# 5 6 7
columns = [['c1', 'c2', 'c3'],
['x1', 'x2', 'x4']]
r.extend_columns(columns)
assert r.row[0] == [2, 4, 'c2']
assert r.row[1] == [6, 8, 'x2']
assert r.row[2] == [10, 12, '']
def main(base_dir):
# Simple give the file name to **Reader**
# "example.xls","example.xlsx","example.ods", "example.xlsm"
spreadsheet = pyexcel.Reader(os.path.join(base_dir, "example.csv"))
# row_range() gives [0 .. number of rows]
for r in spreadsheet.row_range():
# column_range() gives [0 .. number of ranges]
for c in spreadsheet.column_range():
# cell_value(row_index, column_index)
# return the value at the specified
# position
# please note that both row_index
# and column_index starts from 0
print(spreadsheet.cell_value(r, c))
def test_remove_formatter2(self):
r = pe.Reader(self.testfile)
f = lambda x: float(x) + 1
ft = pe.formatters.RowFormatter(1, f)
r.add_formatter(ft)
c1 = r.row_at(1)
c2 = [2.0, 2.0, 2.1, 2.1, 3.0, 3.0]
self.assertEqual(c1, c2)
c1 = r.row_at(1)
c2 = [2.0, 2.0, 2.1, 2.1, 3.0, 3.0]
self.assertEqual(c1, c2)
r.remove_formatter(ft)
c1 = r.row_at(1)
c2 = [1, "1", 1.1, "1.1", 2, "2"]
self.assertEqual(c1, c2)
def test_reading_date_format(self):
"""
date time
25/12/14 11:11:11
25/12/14 12:11:11
01/01/15 13:13:13
0.0 0.0
"""
r = pe.Reader(os.path.join("tests", "fixtures", "date_field.xls"))
assert isinstance(r[1, 0], datetime.date) == True
assert r[1, 0].strftime("%d/%m/%y") == "25/12/14"
assert isinstance(r[1, 1], datetime.time) == True
assert r[1, 1].strftime("%H:%M:%S") == "11:11:11"
assert r[4, 0].strftime("%d/%m/%Y") == "01/01/1900"
assert r[4, 1].strftime("%H:%M:%S") == "00:00:00"
def test_remove_formatter(self):
r = pe.Reader(self.testfile)
ft = pe.formatters.RowFormatter(
1,
str)
r.add_formatter(ft)
c1 = r.row_at(1)
c2 = ["1.0", "1", "1.1", "1.1", "2.0", "2"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
r.remove_formatter(ft)
c1 = r.row_at(1)
c2 = [1, "1", 1.1, "1.1", 2, "2"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
def test_bug_01(self):
"""
if first row of csv is shorter than the rest of the rows,
the csv will be truncated by first row. This is a bug
"a,d,e,f" <- this will be 1
'1',2,3,4 <- 4
'2',3,4,5
'b' <- give '' for missing cells
"""
r = pe.Reader(os.path.join("tests", "fixtures", "bug_01.csv"))
assert len(r.row[0]) == 4
# test "" is append for empty cells
assert r[0,1] == ""
assert r[3,1] == ""
def test_one_formatter_for_two_rows(self):
"""format more than one row
"""
r = pe.Reader(self.testfile)
r.add_formatter(pe.formatters.RowFormatter(
[1,2],
str))
c1 = r.row_at(2)
c2 = ["2.0", "2", "2.2", "2.2", "3.0", "3"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
c1 = r.row_at(1)
c2 = ['1.0', "1", '1.1', "1.1", '2.0', "2"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
def test_update_a_cell_with_a_filter(self):
"""
Filter the sheet first and then update the filtered now
with the filter, you can set its value. then clear
the filters, the value stays with the cell. so if you want
to save the change with original data, please clear the filter
first
"""
r = pe.Reader(self.testfile)
r.add_filter(pe.filters.ColumnFilter([0, 2]))
r.cell_value(2, 1, "k")
assert r[2, 1] == "k"
r.clear_filters()
assert r[2, 3] == "k"
def test_writing_date_format(self):
excel_filename = "testdateformat.xls"
data = [[
datetime.date(2014, 12, 25),
datetime.time(11, 11, 11),
datetime.datetime(2014, 12, 25, 11, 11, 11)
]]
pe.save_as(dest_file_name=excel_filename, array=data)
r = pe.Reader(excel_filename)
assert isinstance(r[0, 0], datetime.date) == True
assert r[0, 0].strftime("%d/%m/%y") == "25/12/14"
assert isinstance(r[0, 1], datetime.time) == True
assert r[0, 1].strftime("%H:%M:%S") == "11:11:11"
assert isinstance(r[0, 2], datetime.date) == True
assert r[0, 2].strftime("%d/%m/%y %H:%M:%S") == "25/12/14 11:11:11"
os.unlink(excel_filename)
def test_merge_two_csv_files_2(self):
file1 = os.path.join("tests", "fixtures",
"test_cook_book_merge_numbers_2.csv")
file2 = os.path.join("tests", "fixtures",
"test_cook_book_merge_alphabets_2.csv")
file_list = [file1, file2]
pe.cookbook.merge_files(file_list)
r = pe.Reader("pyexcel_merged.csv")
data = pe.utils.to_array(r)
print(data)
actual = [['1', '2', '3', '', '', '', '', 'a', 'b', 'c', '', ''],
[
'4', '5', '6', '10', '11', '12', '', 'd', 'e', 'f', 'm',
'q'
], ['7', '8', '9', '', '', '', '9', 'g', 'h', 'I', 'n', 'p']]
assert actual == data
def test_two_formatters(self):
r = pe.Reader(self.testfile)
c1 = r.row_at(1)
c2 = [1, "1", 1.1, "1.1", 2, "2"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
r.add_formatter(pe.formatters.RowFormatter(
1,
int))
r.add_formatter(pe.formatters.RowFormatter(
1,
str))
c1 = r.row_at(1)
c2 = ['1', '1', '1', '1', '2', '2']
for i in range(0, len(c1)):
assert c1[i] == c2[i]
def test_add_rows_even_row_filter2(self):
r = pe.Reader(self.testfile)
r.add_filter(pe.filters.EvenRowFilter())
assert r.number_of_rows() == 2
assert r.number_of_columns() == 4
result = [1, 2, 3, 4, 9, 10, 11, 12]
actual = pe.utils.to_array(r.enumerate())
assert result == actual
content = [
['r', 's', 't', 'o'], # 4
[1, 2, 3, 4], # 5
[True], # 6
[1.1, 2.2, 3.3, 4.4, 5.5]
] # 7
r.extend_rows(content)
assert r.row[3] == content[3]
def test_custom_func_with_a_general_converter(self):
r = pe.Reader(self.test_tuple)
f = lambda x: int(x) + 1
r.add_formatter(pe.formatters.ColumnFormatter(
0,
f))
c1 = r.column_at(0)[1:]
c2 = self.data["5"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
r.add_formatter(pe.formatters.ColumnFormatter(
0,
str))
c1 = r.column_at(0)[1:]
c2 = self.data["6"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
def test_irregular_data_file_rows_to_dict(self):
r = pe.Reader(self.testfile)
result = pe.utils.to_dict(r.rows())
actual = {
'Series_11': [1.0, 2.0, 3.0, 4.0, 5.0, ''],
'Series_8': [1.0, 2.0, '', '', '', ''],
'Series_9': [1.0, 2.0, 3.0, '', '', ''],
'Series_6': [1.0, 2.0, 3.0, 4.0, 5.0, 6.0],
'Series_7': [1.0, '', '', '', '', ''],
'Series_4': [1.0, 2.0, 3.0, 4.0, '', ''],
'Series_5': [1.0, 2.0, 3.0, 4.0, 5.0, ''],
'Series_10': [1.0, 2.0, 3.0, 4.0, '', ''],
'Series_3': [1.0, 2.0, 3.0, '', '', ''],
'Series_1': [1.0, '', '', '', '', ''],
'Series_2': [1.0, 2.0, '', '', '', '']
}
assert result == actual
def test_custom_func_with_a_general_converter(self):
r = pe.Reader(self.testfile)
f = lambda x: float(x) + 1
r.add_formatter(pe.formatters.RowFormatter(
1,
f))
c1 = r.row_at(1)
c2 = [2.0, 2.0, 2.1, 2.1, 3.0, 3.0]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
r.add_formatter(pe.formatters.RowFormatter(
1,
str))
c1 = r.row_at(1)
c2 = ["2.0", "2.0", "2.1", "2.1", "3.0", "3.0"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
def test_two_formatters(self):
r = pe.Reader(self.test_tuple)
r.add_formatter(pe.formatters.ColumnFormatter(
0,
str))
c1 = r.column_at(0)[1:]
c2 = self.data["2"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
r.add_formatter(pe.formatters.ColumnFormatter(
0,
int))
c1 = r.column_at(0)[1:]
c2 = self.data["1"]
for i in range(0, len(c1)):
assert type(c1[i]) == int
assert c1[i] == c2[i]
def test_writing_date_format(self):
excel_filename = "testdateformat.xls"
data = [[
datetime.date(2014, 12, 25),
datetime.time(11, 11, 11),
datetime.datetime(2014, 12, 25, 11, 11, 11)
]]
w = pe.Writer(excel_filename)
w.write_rows(data)
w.close()
r = pe.Reader(excel_filename)
assert isinstance(r[0, 0], datetime.date) == True
assert r[0, 0].strftime("%d/%m/%y") == "25/12/14"
assert isinstance(r[0, 1], datetime.time) == True
assert r[0, 1].strftime("%H:%M:%S") == "11:11:11"
assert isinstance(r[0, 2], datetime.date) == True
assert r[0, 2].strftime("%d/%m/%y") == "25/12/14"
os.unlink(excel_filename)
def test_slice(self):
r = pe.Reader(self.testfile)
content1 = [[1, 1, 1, 1]]
assert content1 == r.row[0:1]
content2 = [[1, 1, 1, 1], [2, 2, 2, 2]]
assert content2 == r.row[0:2]
assert content2 == r.row[:2]
content3 = [[2, 2, 2, 2], [3, 3, 3, 3]]
assert content3 == r.row[1:]
try:
r.row[2:1]
assert 1 == 2
except ValueError:
assert 1 == 1
content4 = [[1, 1, 1, 1], [2, 2, 2, 2]]
assert content4 == r.row[0:2:1]
content5 = [1, 1, 1, 1]
assert [content5] == r.row[0:0]
def test_remove_formatter2(self):
r = pe.Reader(self.testfile)
f = lambda x: float(x) + 1
ft = pe.formatters.RowFormatter(1, f)
r.add_formatter(ft)
c1 = r.row_at(1)
c2 = [2.0, 2.0, 2.1, 2.1, 3.0, 3.0]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
c1 = r.row_at(1)
c2 = [2.0, 2.0, 2.1, 2.1, 3.0, 3.0]
for i in range(0, len(c1)):
assert type(c1[i]) == type(c2[i])
assert c1[i] == c2[i]
r.remove_formatter(ft)
c1 = r.row_at(1)
c2 = [1, "1", 1.1, "1.1", 2, "2"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
def test_remove_formatter2(self):
r = pe.Reader(self.testfile)
f = lambda x: float(x) + 1
ft = pe.formatters.RowFormatter(1, float, f)
r.add_formatter(ft)
c1 = r.row_at(1)
c2 = [2.0, 2.0, 2.1, 2.1, 3.0, 3.0]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
r.add_formatter(pe.formatters.RowFormatter(1, str))
c1 = r.row_at(1)
c2 = ["2.0", "2.0", "2.1", "2.1", "3.0", "3.0"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
r.remove_formatter(ft)
c1 = r.row_at(1)
c2 = ["1", "1", "1.1", "1.1", "2", "2"]
for i in range(0, len(c1)):
assert c1[i] == c2[i]
def test_wrong_io_input(self):
pe.Reader(1000)
import pyexcel
import glob
merged = pyexcel.Reader()
for file in glob.glob("*.ods"):
merged += pyexcel.Reader(file)
writer = pyexcel.Writer("merged.csv")
writer.write_reader(merged)
writer.close()
def test_number_of_columns(self):
r = pe.Reader(self.testfile)
assert 4 == r.number_of_columns()
def test_row_range(self):
r = pe.Reader(self.testfile)
assert self.rows == len(r.row_range())
def test_number_of_rows(self):
r = pe.Reader(self.testfile)
assert self.rows == r.number_of_rows()
def test_use_filter_reader_without_filter(self):
r = pe.Reader(self.testfile)
result = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]
actual = pe.utils.to_array(r.enumerate())
assert result == actual
def test_contains(self):
r = pe.Reader(self.testfile)
f = lambda row: row[0]=='a' and row[1] == 'b'
assert r.contains(f) == True
