def _get_payload(self, email_info):
payload = {}
for part in email_info.walk():
if part.get_content_maintype() == 'multipart':
continue
if part.get('Content-Disposition') is None:
continue
f_name = part.get_filename()
f_ext = self._settings['Content Type'].get(part.get_content_type(), None)
# See if its possible to get f_ext from .get_content_charset()
if f_name and f_ext:
if f_ext == 'xlsx':
try:
f_path = join(self.parent.src_doc_path, f_name)
if not isfile(f_path):
with open(f_path, mode='wb') as f:
f.write(part.get_payload(decode=True))
f.close()
payload[f_name] = get_book(
file_name=f_path
)
except AttributeError:
with NamedTemporaryFile(mode='w+b', suffix=f_ext) as f:
f.write(part.get_payload(decode=True))
f.seek(0)
payload[f_name] = get_book(
file_type=f_ext,
file_content=f.read()
)
if f_ext == 'wav':
with NamedTemporaryFile(mode='w+b', suffix=f_ext) as f: # change this back to delete=False for scribing
f.write(part.get_payload(decode=True))
f.seek(0)
payload[f_name] = f
return payload
def test_issue_23():
if not IN_TRAVIS:
raise SkipTest()
url = (
"https://github.com/pyexcel/pyexcel-ods3/"
+ "raw/master/tests/fixtures/multilineods.ods"
)
pe.get_book(url=url)
def test_issue_23():
if not IN_TRAVIS:
raise SkipTest()
pe.get_book(
url=(
"https://github.com/pyexcel/pyexcel-ods/"
+ "raw/master/tests/fixtures/white_space.ods"
)
)
def test_simple_option():
runner = CliRunner()
file_fixture = os.path.join("tests", "fixtures", "transcode_simple.csv")
dir_fixture = os.path.join("tests", "fixtures", "file_dir")
glob_fixture = os.path.join("tests", "fixtures", "glob_dir", "*")
output = "test_simple_option.xls"
result = runner.invoke(merge, [file_fixture, dir_fixture, glob_fixture,
output])
eq_(result.exit_code, 0)
book = get_book(file_name=output)
expected = dedent("""
transcode_simple.csv:
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
merge_test.csv:
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
merge_test2.csv:
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+""").strip('\n')
eq_(str(book), expected)
os.unlink(output)
def testBook(self):
fp = open(self.testfile, "rb")
response = self.client.post('/polls/import/', data={"file": fp})
assert response.status_code == 200
response2 = self.client.get('/polls/export/book')
assert response2.status_code == 200
book = pe.get_book(file_type='xls', file_content=response2.content)
content = dedent("""
Sheet Name: question
+----+---------------------------+----------------------------------------------+----------+
| id | pub_date | question_text | slug |
+----+---------------------------+----------------------------------------------+----------+
| 1 | 2015-01-28T00:00:00+00:00 | What is your favourite programming language? | language |
+----+---------------------------+----------------------------------------------+----------+
| 2 | 2015-01-29T00:00:00+00:00 | What is your favourite IDE? | ide |
+----+---------------------------+----------------------------------------------+----------+
Sheet Name: choice
+---------------+----+-------------+-------+
| choice_text | id | question_id | votes |
+---------------+----+-------------+-------+
| Java | 1 | 1 | 0 |
+---------------+----+-------------+-------+
| C++ | 2 | 1 | 0 |
+---------------+----+-------------+-------+
| C | 3 | 1 | 0 |
+---------------+----+-------------+-------+
| Eclipse | 4 | 2 | 0 |
+---------------+----+-------------+-------+
| Visual Studio | 5 | 2 | 0 |
+---------------+----+-------------+-------+
| PyCharm | 6 | 2 | 0 |
+---------------+----+-------------+-------+
| IntelliJ | 7 | 2 | 0 |
+---------------+----+-------------+-------+""").strip('\n')
assert str(book) == content
def split(source_file_type, output_file_type,
output_dir, source, output_file_prefix):
"""
Split an excel file into one sheet per file
\b
SOURCE: a file name or '-'. '-' tells the command to use stdin
OUTPUT_FILE_PREFIX: the splitted file name is form as
prefix_sheetname_index.file_type
"""
params = {}
if source == '-':
params['file_content'] = get_input_content(source_file_type)
params['file_type'] = source_file_type
else:
params['file_name'] = source
book = get_book(**params)
sheet_count = 0
for sheet in book:
file_name = "%s_%s_%d.%s" % (output_file_prefix, sheet.name,
sheet_count, output_file_type)
if output_dir is not None:
file_name = os.path.join(output_dir, file_name)
sheet.save_as(file_name)
sheet_count += 1
def test_get_book_from_book_dict(self):
content = OrderedDict()
content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
content.update({"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
book = pe.get_book(bookdict=content)
assert book.to_dict() == content
def test_get_book_auto_detect_int(self):
book = pe.get_book(file_name=self.test_file, library="pyexcel-xls")
expected = dedent("""
pyexcel_sheet1:
+---+---+-----+
| 1 | 2 | 3.1 |
+---+---+-----+""").strip()
eq_(str(book), expected)
def test_add_book1(self):
"""
test this scenario: book3 = book1 + book2
"""
b1 = pyexcel.get_book(file_name=self.testfile)
b2 = pyexcel.get_book(file_name=self.testfile2)
b3 = b1 + b2
content = pyexcel.utils.to_dict(b3)
sheet_names = content.keys()
assert len(sheet_names) == 6
for name in sheet_names:
if "Sheet3" in name:
assert content[name] == self.content["Sheet3"]
elif "Sheet2" in name:
assert content[name] == self.content["Sheet2"]
elif "Sheet1" in name:
assert content[name] == self.content["Sheet1"]
def get_book(self, **keywords):
"""Get a instance of :class:`Book` from the file
:param keywords: additional key words
:returns: A instance of :class:`Book`
"""
params = self.get_params(**keywords)
return pe.get_book(**params)
def test_get_book_auto_detect_int(self):
book = pe.get_book(file_name=self.test_file)
expected = dedent("""
pyexcel_sheet1:
+---+---+-----+
| 1 | 2 | 3.1 |
+---+---+-----+""").strip()
self.assertEqual(str(book), expected)
def load_book(self, field_name=None, **keywords):
file_type, file_handle = self.get_file_tuple(field_name)
if file_type is not None and file_handle is not None:
return pe.get_book(file_type=file_type,
file_content=file_handle.read(),
**keywords)
else:
return None
def test_load_a_single_sheet(self):
b1 = pyexcel.get_book(
file_name=self.testfile,
sheet_name="Sheet1",
library="pyexcel-xlsx",
)
assert len(b1.sheet_names()) == 1
assert b1["Sheet1"].to_array() == self.content["Sheet1"]
def test_get_book_from_memory_compatibility(self):
content = OrderedDict()
content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
content.update({"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
io = pe.save_book_as(dest_file_type="xls", bookdict=content)
book2 = pe.get_book(content=io.getvalue(), file_type="xls")
assert book2.to_dict() == content
def test_get_book_auto_detect_int_false(self):
book = pe.get_book(file_name=self.test_file, auto_detect_int=False)
expected = dedent("""
test_auto_detect_init.csv:
+-----+-----+-----+
| 1.0 | 2.0 | 3.1 |
+-----+-----+-----+""").strip()
self.assertEqual(str(book), expected)
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_get_book_from_file_stream(self):
content = OrderedDict()
content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
io = pe.save_book_as(dest_file_type="xls", bookdict=content)
book2 = pe.get_book(file_stream=io, file_type="xls")
assert book2.to_dict() == content
def find_x_in_file(excel_file: Path) -> str:
"""Finds a number that is present only in one column and returns it in
formatted string according to the requirements.
Parameters
----------
excel_file : Path
Path to Excel file.
File existence should be checked on the calling side.
Returns
-------
str
Formatted string with resulting number.
"""
book = pyexcel.get_book(file_name=excel_file.absolute().as_posix())
# Filtering out empty sheets
for sheet in (sh for sh in book if sh):
# Trimming the columns
sheet.name_columns_by_row(0)
header = sheet.colnames
if "" in header:
header = header[0:header.index("")]
if "before" in header and "after" in header:
# Trimming the rows
before_column = sheet.column["before"]
if "" in before_column:
before_column = before_column[:before_column.index("")]
after_column = sheet.column["after"]
if "" in after_column:
after_column = after_column[:after_column.index("")]
# Validity checkup
if abs(len(before_column) - len(after_column)) != 1:
raise ValueError(
"'before' and 'after' columns found, but have incorrect size."
)
if not all(
isinstance(element, int)
for element in before_column + after_column):
raise ValueError(
"Processed columns contain non-integer values.")
# Comparison
before_column = set(before_column)
after_column = set(after_column)
if difference := before_column.difference(after_column):
return f"removed: {difference.pop()}"
if difference := after_column.difference(before_column):
return f"added: {difference.pop()}"
def test_get_sheet_from_array(self):
data = [
["X", "Y", "Z"],
[1, 2, 3],
[4, 5, 6]
]
book = pe.get_book(array=data)
result = book.to_dict()
eq_(data, result['pyexcel_sheet1'])
def read_from_file(self, file_path):
raw_data = pyexcel.get_book(file_name=file_path).to_dict()
columns = self.separate_columns(raw_data.popitem()[1])
project_id = columns[0][0]
project_title = columns[0][1]
format = columns[0][2]
data = columns[1]
data_object = Data(project_title, format, data, project_id=project_id)
return data_object
def test_get_sheet_from_array(self):
data = [
["X", "Y", "Z"],
[1, 2, 3],
[4, 5, 6]
]
book = pe.get_book(array=data)
result = book.to_dict()
eq_(data, result['pyexcel_sheet1'])
def test_get_book_auto_detect_float_false(self):
book = pe.get_book(file_name=self.test_file, auto_detect_float=False)
self.assertEqual(book[0].to_array(), [[1, "2.0", "3.1"]])
expected = dedent("""
test_auto_detect_init.csv:
+---+-----+-----+
| 1 | 2.0 | 3.1 |
+---+-----+-----+""").strip()
self.assertEqual(str(book), expected)
def test_get_book_auto_detect_float_false(self):
book = pe.get_book(file_name=self.test_file, auto_detect_float=False)
self.assertEqual(book[0].to_array(), [[1, '2.0', '3.1']])
expected = dedent("""
test_auto_detect_init.csv:
+---+-----+-----+
| 1 | 2.0 | 3.1 |
+---+-----+-----+""").strip()
self.assertEqual(str(book), expected)
def test_random_access_operator(self):
r = pe.get_book(file_name=self.testfile)
value = r["Sheet1"].row[0][1]
assert value == 1
value = r["Sheet3"].row[0][1]
assert value == 'Y'
r["Sheet3"].name_columns_by_row(0)
value = r["Sheet3"].row[0][1]
assert value == 4
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 iter_excel(file_name: str) -> Iterator[FxRow]:
""" Iterate over BoI fxrates Excel sheet.
:param file_name: path and name of BoI fxrates Excel file.
"""
book = pyexcel.get_book(file_name=file_name)
sheet = book.sheet_by_index(0) # The first sheet has the rates we want
rows = iter(sheet.rows()) # Iterate over its rows
next(rows) # Skip row of spoken names of currencies
yield from rows
def test_iterate_through_sheets(self):
b = pe.get_book(file_name=self.testfile)
for s in b:
data = s.array
assert self.content[s.name] == data
si = pe.internal.common.SheetIterator(b)
for s in si:
data = s.array
assert self.content[s.name] == data
def blind_add_row(row):
global output_file
# print "adding row"
book = pyexcel.get_book(
file_name=output_file
) #loads a sheet in a sheet object that can be modified
book.Sheet1.row += row
book.save_as(output_file)
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_get_book_from_memory_compatibility(self):
content = OrderedDict()
content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
content.update(
{"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
io = pe.save_book_as(dest_file_type="xls", bookdict=content)
book2 = pe.get_book(content=io.getvalue(), file_type="xls")
assert book2.to_dict() == content
def __read_xlsx(self):
# data = get_data(self.xlsx_file)
# pp(json.dumps(data))
print("\n\n***************************\n\n")
book = py.get_book(file_name=self.xlsx_file)
for sheet in book:
print(sheet)
def write_xls(all_parameters,
configs,
output_file,
config_attributes={},
metadata={"doc": ""}):
print_pref = "[parameter_to_xls]:"
book_data = build_book(all_parameters, configs, config_attributes,
metadata)
book = get_book(bookdict=book_data)
book.save_as(output_file)
def test_book_attribute():
book = get_book(
file_name=os.path.join("tests", "fixtures", "test-multiple.csvz"))
expected = ("---pyexcel:sheet1---\r\n" + "1,4,9\r\n" + "2,5,8\r\n" +
"3,6,7\r\n" + "---pyexcel---\r\n" +
"---pyexcel:sheet2---\r\n" + "1,4,9\r\n" + "2,5,8\r\n" +
"3,6,7\r\n" + "---pyexcel---\r\n" +
"---pyexcel:sheet3---\r\n" + "1,4,9\r\n" + "2,5,8\r\n" +
"3,6,7\r\n" + "---pyexcel---\r\n")
eq_(book.csv, expected)
def test_book(self):
for struct_type in ["book", "book_dict"]:
io = pe.save_book_as(bookdict=self.content, dest_file_type="xls")
response = self.app.post('/exchange/%s' % struct_type,
buffered=True,
data={"file": (io, "test.xls")},
content_type="multipart/form-data")
assert response.content_type == "application/vnd.ms-excel"
book2 = pe.get_book(file_type='xls', file_content=response.data)
assert book2.to_dict() == self.content
def __init__(self, excel_path):
"""
初始化读取excel文件，获取book形式数据
:param excel_path: excel文件
"""
try:
self.book = pyexcel.get_book(file_name=excel_path)
mylog.info('Read exel:{}'.format(os.path.basename(excel_path)))
except Exception:
mylog.error('Read exel:{} faild !'.format(excel_path))
def test_book(self):
for struct_type in ["book", "book_dict"]:
io = pe.save_book_as(bookdict=self.content, dest_file_type="xls")
response = self.app.post('/exchange/%s' % struct_type,
buffered=True,
data={"file": (io, "test.xls")},
content_type="multipart/form-data")
assert response.content_type == "application/vnd.ms-excel"
book2 = pe.get_book(file_type='xls', file_content=response.data)
assert book2.to_dict() == self.content
def write_to_range(file_path, sheet_name, start_cell, end_cell, data):
book = get_book(file_name=file_path)
# data is expected to be 2 dimensional list
start_cell_index = convert_index.coordinate_to_index(start_cell, True)
start_col = start_cell_index[0]
start_row = start_cell_index[1]
sheet = book[sheet_name]
sheet.paste([start_row, start_col], rows=data)
return book.save_as(file_path)
def sheetChanged(self):
self.log.setText('')
if self.book is None:
self.book = pxl.get_book(file_name=ifile)
isheet = self.sheet.currentText()
if isheet not in self.book.sheet_names():
self.log.setText("Can't find sheet - " + isheet)
return
self.setColumns(isheet)
self.updated = True
def test_get_book_auto_detect_int(self):
book = pe.get_book(file_name=self.test_file, library="pyexcel-ods")
expected = dedent(
"""
pyexcel_sheet1:
+---+---+-----+
| 1 | 2 | 3.1 |
+---+---+-----+"""
).strip()
eq_(str(book), expected)
def test_issue_125_saving_the_order():
test_file = "issue_125.xls"
book = p.Book()
book += p.Sheet([[1]], "A")
book += p.Sheet([[2]], "B")
eq_(book.sheet_names(), ["A", "B"])
book.sort_sheets(reverse=True)
book.save_as(test_file)
book2 = p.get_book(file_name=test_file)
eq_(book2.sheet_names(), ["B", "A"])
os.unlink(test_file)
def search_xlsm(doc):
emails = []
doc_name = doc.split('/')[-1]
new_doc = "{0}.xls".format(doc_name.split('.')[0])
sheet = pe.get_book(file_name=doc)
sheet.save_as("/tmp/{0}".format(new_doc))
emails = search_docs("/tmp/{0}".format(new_doc))
return emails
def read_write():
files = find_excel_files()
print type(files)
print type(files[0])
content = {}
for i in files:
data = pe.get_sheet(file_name = i)
name = i[0:(i.find('.'))]
content[name] = data
book = pe.get_book(bookdict = content)
book.save_as('output.xlsx')
def test_get_book_from_file(self):
test_file = "test_get_book.xls"
content = OrderedDict()
content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
content.update({"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
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_125_saving_the_order():
test_file = 'issue_125.xls'
book = pe.Book()
book += pe.Sheet([[1]], 'A')
book += pe.Sheet([[2]], 'B')
eq_(book.sheet_names(), ['A', 'B'])
book.sort_sheets(reverse=True)
book.save_as(test_file)
book2 = pe.get_book(file_name=test_file)
eq_(book2.sheet_names(), ['B', 'A'])
os.unlink(test_file)
def test_no_title_multiple_sheets(self):
adict = {
'sheet 1': [[1,2],[3,4]],
'sheet 2': [[5,6],[7,8]]
}
book = pe.get_book(bookdict=adict, dest_write_title=False)
get_presentation_call = getattr(book, "get_%s" % self.TABLEFMT)
presentation = get_presentation_call(write_title=False)
self._check_presentation('no_title_multiple_sheets', presentation)
def test_dict(self):
adict = {
'sheet 1': [[1,2],[3,4]],
'sheet 2': [[5,6],[7,8]]
}
book = pe.get_book(bookdict=adict)
get_presentation_call = getattr(book, "get_%s" % self.TABLEFMT)
presentation = get_presentation_call()
self._check_presentation('dict', presentation)
def make_response_from_tables(models, file_type, status=200, **keywords):
"""
Produce a multiple sheet Excel book of *file_type*. It becomes the same
as :meth:`~django_excel.make_response_from_a_table` if you pass *tables*
with an array that has a single table
:param models: a list of Django models
:param file_type: same as :meth:`~django_excel.make_response`
:param status: same as :meth:`~django_excel.make_response`
"""
book = pe.get_book(models=models, **keywords)
return make_response(book, file_type, status, **keywords)
def test_url_source_via_content_type(self):
book = pe.get_book(url="xx.csv")
if PY2:
self.mocked_info.type.return_value = "text/csv"
else:
self.mocked_info.get_content_type.return_value = "text/csv"
content = dedent("""
csv:
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+""").strip("\n")
self.assertEqual(str(book), content)
def test_get_book_auto_detect_int_false(self):
book = pe.get_book(
file_name=self.test_file,
auto_detect_int=False,
library="pyexcel-xlsxr",
)
expected = dedent("""
pyexcel_sheet1:
+-----+-----+-----+
| 1.0 | 2.0 | 3.1 |
+-----+-----+-----+""").strip()
eq_(str(book), expected)
def load_file():
global sheet1
global sheet2
global sheet3
book = pyx.get_book(file_name='movies.xls')
sheet1 = book["1900s"]
sheet2 = book["2000s"]
sheet3 = book["2010s"]
sheet3.name_columns_by_row(0)
print(sheet1[1])
print(sheet2[1])
print(sheet3[1])
def __init__(self, spreadsheet_fn, annotations_fn=None, annotations=None):
self.normalized_source_file = os.path.basename(spreadsheet_fn)
self.book = pyexcel.get_book(file_name=spreadsheet_fn,
auto_detect_datetime=False)
if annotations is not None:
self.annotations = annotations
elif annotations_fn is not None:
self.annotations = self.load_annotations(annotations_fn)
else:
raise Exception(
'Please provide either annotations file path or annotations json'
)
def test_get_book_from_file(self):
test_file = "test_get_book.xls"
content = OrderedDict()
content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
content.update(
{"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
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 read_excel_file(fname, d):
"""Read an entire excel file and output each sheet as a txt file"""
try:
book = pe.get_book(file_name=fname)
except pe.exceptions.FileTypeNotSupported:
print_and_exit(PROGRAM_DESCRIPTION + USAGE_DESCRIPTION +
"\nInput file must be of excel extension!\n")
except:
print_and_exit("\nInput file corrupt!\n")
for name in book.sheet_names():
print("Printing sheet %s" % name)
write_txt(book.sheet_by_name(name), d, remove_extension(fname))
def test_load_book_from_django_model(self):
# if a book has more than one sheet
# and it saves to only one model, now it will fail
# with an exception.
model = FakeDjangoModel("Sheet1")
book = pe.Book(
{"Sheet1": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
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 read_data_test2():
book = pyexcel.get_book(file_name=PROJECT_PATH +
"//file_location//multiple-sheets-example.xlsx")
# 默认的迭代器为 Boo 实例
for sheet in book:
# 每张表都有名字
print('sheet: %s' % sheet.name)
# 一旦拥有了一个表实例，
#就可以将其视为一个读取器实例，可以按照期望的方式迭代它的成员
for row in sheet:
print(row)
def open_excel(file_name='weather.xlsx'):
"""
Either opens an existing file or creates a new file and returns its
workbook reference.
"""
try:
wb = xl.get_book(file_name=file_name)
except FileNotFoundError:
wb = xl.Book()
setup_excel(wb)
save_workbook(wb)
return wb
def parse_fx_excel(filepath):
print "working..."
book = pe.get_book(file_name=filepath)
cnt = 0
tc = 0
all_data = {}
for sheet in book:
name = sheet.name
cnt = cnt + 1
if cnt > 1:
sheet.name_columns_by_row(0)
# print sheet.colnames
records = sheet.to_records()
sum_volume = Decimal("0.00")
sum_commission = Decimal("0.00")
data = {}
for record in records:
if record["Login"]:
tc += 1
sum_volume += Decimal(str(record["Volume"]))
sum_commission += Decimal(str(record["Commission"]))
if not data.has_key(record["Symbol"]):
data[record["Symbol"]] = {
"volume": Decimal("0.00"),
"commission": Decimal("0.00")
}
data_volume = data[record["Symbol"]].get("volume")
data_commission = data[record["Symbol"]].get("commission")
data_volume = data_volume + Decimal(str(record["Volume"]))
data_commission = data_commission + Decimal(
str(record["Commission"]))
data[record["Symbol"]]["volume"] = data_volume
data[record["Symbol"]]["commission"] = data_commission
# print "%s %s %s %s" % (record["Login"], record["Symbol"], record["Commission"], record["Volume"])
print "user %s, volume=%s, commission=%s" % (name, sum_volume,
sum_commission)
all_data[name] = {
"volumes": sum_volume,
"commission": sum_commission,
"group": data,
"details": []
# all details
}
print "%d rows processed" % tc
pp = pprint.PrettyPrinter(indent=4)
pp.pprint(all_data)
print "ok"
return all_data
def main(base_dir):
# Simply give a name to the Book class
book = pe.get_book(file_name= os.path.join(base_dir,"multiple-sheets.ods"))
# the default iterator for a **Book* instance is a SheetIterator
for sheet in book:
# Each sheet has name
print("sheet: %s" % sheet.name)
# Once you have a sheet instance, you can regard it as
# a Reader instance. You can iterate its member in the way
# you wanted it
for row in sheet:
print(row)
def _download_from_labsite( self ):
###################
# did this manually - could not open xls file
# download file
for url, f in files.items():
# download file
downloadFromUrl( url, f[0] )
# extract appropriate sheet
book = pe.get_book(file_name=f[0])
book['Predicted_complexes'].save_as( f[1] )
os.remove( f[0] )
def _download_from_labsite( self ):
###################
# the files were downloaded from the journal website as
# supplemental tables 2 & 4. These files are the ones in
# files and the corresponding sheets contain the relevant data
#
# extract appropriate sheet
for f in files:
print( f[0] )
book = pe.get_book( file_name = f[0] )
print( f[2])
book[ f[2] ].save_as( f[1] )
