def test_write_book_reader(self):
reader = pe.BookReader(self.testfile)
writer = pe.BookWriter(self.testfile2)
writer.write_book_reader(reader)
writer.close()
reader2 = pe.BookReader(self.testfile2)
data = pe.utils.to_dict(reader2)
assert data == 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]]})
w = pe.BookWriter(self.testfile)
w.write_book_from_dict(self.content)
w.close()
def test_book_output_stringio(self):
data = {"Sheet 1": [[1, 2, 3], [4, 5, 6]]}
io = BytesIO()
w = pe.BookWriter(("xlsm", io))
w.write_book_from_dict(data)
w.close()
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 setUp(self):
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 _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.content = {
'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]],
'Sheet 1': [[1.0, 2.0, 3.0], [4.0, 5.0, 6.0], [7.0, 8.0, 9.0]]
}
self.testfile = "test.xls"
self.testfile2 = "test.xlsx"
w = pe.BookWriter(self.testfile)
w.write_book_from_dict(self.content)
w.close()
def _write_test_file(self, file):
"""
Make a test file as:
1,1,1,1
2,2,2,2
3,3,3,3
"""
self.rows = 3
w = pyexcel.BookWriter(file)
w.write_book_from_dict(self.content)
w.close()
def main(base_dir):
# Simple open the file using Book
book = pe.Book(filename="multiple-sheets.xls")
# Create a new book by creating a BookWriter instance
newbook = pe.BookWriter("multiple-sheets.ods")
# Now simple state you want to save the content of
# book to newbook
newbook.write_book_reader(book)
# Close the writer
newbook.close()
def test_write_a_book_reader(self):
b = pe.BookReader(self.testfile)
bw = pe.BookWriter(self.testfile2)
for s in b:
data = pe.utils.to_array(s)
sheet = bw.create_sheet(s.name)
sheet.write_array(data)
sheet.close()
bw.close()
x = pe.BookReader(self.testfile2)
for s in x:
data = pe.utils.to_array(s)
assert self.content[s.name] == data
def setUp(self):
self.content = OrderedDict([('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]])])
self.testfile = "test.xls"
self.testfile2 = "test.csv"
w = pe.BookWriter(self.testfile)
w.write_book_from_dict(self.content)
w.close()
def test_write_book_reader(self):
reader = pe.BookReader(self.testfile)
writer = pe.BookWriter(self.testfile2)
writer.write_book_reader(reader)
writer.close()
sheet1 = "test_Sheet 1.csv"
reader1 = pe.Reader(sheet1)
data = pe.utils.to_array(reader1)
assert data == self.content["Sheet 1"]
sheet2 = "test_Sheet 2.csv"
reader2 = pe.Reader(sheet2)
data = pe.utils.to_array(reader2)
assert data == self.content["Sheet 2"]
sheet3 = "test_Sheet 3.csv"
reader3 = pe.Reader(sheet3)
data = pe.utils.to_array(reader3)
assert data == self.content["Sheet 3"]
def setUp(self):
"""
Make a test csv file as:
1,2,3,4
5,6,7,8
9,10,11,12
"""
self.testfile = "testcsv.xls"
self.content = {
"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.testfile)
w.write_book_from_dict(self.content)
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 test_write_book_reader(self):
reader = pe.BookReader(self.testfile)
writer = pe.BookWriter(self.testfile2)
writer.write_book_reader(reader)
writer.close()
sheet1 = "test__Sheet 1__0.csv"
reader1 = pe.Reader(sheet1)
reader1.apply_formatter(pe.formatters.SheetFormatter(float))
data = pe.utils.to_array(reader1)
assert data == self.content["Sheet 1"]
sheet2 = "test__Sheet 2__1.csv"
reader2 = pe.Reader(sheet2)
reader2.apply_formatter(pe.formatters.SheetFormatter(float))
data = pe.utils.to_array(reader2)
assert data == self.content["Sheet 2"]
sheet3 = "test__Sheet 3__2.csv"
reader3 = pe.Reader(sheet3)
reader3.apply_formatter(pe.formatters.SheetFormatter(float))
data = pe.utils.to_array(reader3)
assert data == self.content["Sheet 3"]
def test_not_supported_file(self):
try:
pe.BookWriter("bad.format")
except NotImplementedError:
assert 1 == 1
"""
read_excel_book.py
:copyright: (c) 2014 by C. W.
:license: GPL v3
This shows how to use **BookWriter** class to write a dictionary
to sheet spreadsheet.
"""
import pyexcel as pe
# the dictionary should look like the following:
# * key: a string typed key
# * value: a two dimensional array or a list of lists
data={
"Sheet 1": [[1,2,3],[4,5,6],[7,8,9]],
"Sheet 2": [['X', 'Y', 'Z'], [1,2,3],[4,5,6]],
"Sheet 3": [['O', 'P', 'Q'], [3,2,1],[4,3,2]]
}
# Now simply choose the filename and format you want to save
# file format is decided by the file extension
w=pe.BookWriter("multiple-sheets.xls")
# A call to write the dictionary
w.write_book_from_dict(data)
# Now close the file
w.close()
# The output of the file is "mltiple-sheets.xls"
What this example implies is that you can do the conversion in
between these formats:
==== === === === ==== ====
ods csv xls xlsx xlsm
==== === === === ==== ====
ods y y y y y
csv y y y y y
xls y y y y y
xlsx y y y y y
xlsm y y y y y
---- --- --- --- ---- ----
"""
import pyexcel as pe
# you will need to install pyexcel-ods or pyexcel-ods3
# depending on your python version
from pyexcel.ext import ods
# Simple open the file using Book
book = pe.Book("multiple-sheets.xls")
# Create a new book by creating a BookWriter instance
newbook = pe.BookWriter("multiple-sheets.ods")
# Now simple state you want to save the content of
# book to newbook
newbook.write_book_reader(book)
# Close the writer
newbook.close()
# then you will have the book in ods
def _write_test_file(self, filename):
w = pe.BookWriter(filename)
w.write_book_from_dict(self.content)
w.close()
