def __iadd__(self, other):
"""
Operator overloading +=
example::
book += book2
book += book2["Sheet1"]
"""
if isinstance(other, Book):
names = other.sheet_names()
for name in names:
new_key = name
if len(names) == 1:
new_key = other.filename
if new_key in self.__name_array:
uid = local_uuid()
new_key = "%s_%s" % (name, uid)
self.__sheets[new_key] = Sheet(other[name].array, new_key)
elif isinstance(other, Sheet):
new_key = other.name
if new_key in self.__name_array:
uid = local_uuid()
new_key = "%s_%s" % (other.name, uid)
self.__sheets[new_key] = Sheet(other.array, new_key)
else:
raise TypeError
self.__name_array = list(self.__sheets.keys())
return self
def load_from_sheets(self, sheets):
"""
Load content from existing sheets
:param dict sheets: a dictionary of sheets. Each sheet is
a list of lists
"""
if sheets is None:
return
keys = sheets.keys()
if not isinstance(sheets, compact.OrderedDict):
# if the end user does not care about the order
# we put alphatical order
keys = sorted(keys)
for name in keys:
value = sheets[name]
if isinstance(value, Sheet):
sheet = value
sheet.name = name
else:
# array
sheet = Sheet(value, name)
# this sheets keep sheet order
self.__sheets.update({name: sheet})
# this provide the convenience of access the sheet
self.__dict__[name.replace(" ", "_")] = sheet
self.__name_array = list(self.__sheets.keys())
def test_random_access_to_unknown_area():
test_content = [[1, 2]]
sheet = Sheet(copy.deepcopy(test_content))
eq_(sheet.to_array(), test_content)
expected = [[1, 2, ""], ["", "", ""], ["", "", 100]]
sheet[2, 2] = 100
eq_(sheet.array, expected)
def test_sheet_content():
array = [[1, 2]]
sheet = Sheet(array)
expected = dedent("""
+---+---+
| 1 | 2 |
+---+---+""").strip("\n")
eq_(str(sheet.content), expected)
def test_html_representation():
array = [[1, 2]]
sheet = Sheet(array)
expected = dedent("""
pyexcel sheet:
<table>
<tbody>
<tr><td style="text-align: right;">1</td><td style="text-align: right;">2</td></tr>
</tbody>
</table>""").strip("\n") # flake8: noqa
eq_(str(sheet._repr_html_()), expected)
def test_random_access():
test_content = [[1, 2]]
sheet = Sheet(test_content)
eq_(sheet.to_array(), test_content)
expected = dedent("""
pyexcel sheet:
+---+-----+
| 1 | 100 |
+---+-----+""").strip("\n")
sheet[0, 1] = 100
eq_(str(sheet), expected)
def test_random_access_to_unknown_area():
test_content = [[1, 2]]
sheet = Sheet(test_content)
eq_(sheet.to_array(), test_content)
expected = [
[1, 2, ''],
['', '', ''],
['', '', 100]
]
sheet[2, 2] = 100
eq_(sheet.array, expected)
def get_sheet(**keywords):
"""
Get an instance of :class:`Sheet` from an excel source
"""
sheet_params = {}
for field in constants.VALID_SHEET_PARAMETERS:
if field in keywords:
sheet_params[field] = keywords.pop(field)
named_content = sources.get_sheet_stream(**keywords)
sheet = Sheet(named_content.payload, named_content.name, **sheet_params)
return sheet
def save_as(**keywords):
"""
Save a sheet from a data source to another one
"""
dest_keywords, source_keywords = _split_keywords(**keywords)
sheet_params = {}
for field in constants.VALID_SHEET_PARAMETERS:
if field in source_keywords:
sheet_params[field] = source_keywords.pop(field)
sheet_stream = sources.get_sheet_stream(**source_keywords)
sheet = Sheet(sheet_stream.payload, sheet_stream.name, **sheet_params)
return sources.save_sheet(sheet, **dest_keywords)
def test_named_sheet_access():
test_content = [['A', 'B'], [1, 2]]
sheet = Sheet(test_content, name_columns_by_row=0)
eq_(sheet.to_array(), test_content)
expected = dedent("""
pyexcel sheet:
+-----+---+
| A | B |
+=====+===+
| 100 | 2 |
+-----+---+""").strip('\n')
sheet[0, 'A'] = 100
print(str(sheet))
eq_(str(sheet), expected)
def test_named_sheet_access():
test_content = [["A", "B"], [1, 2]]
sheet = Sheet(copy.deepcopy(test_content), name_columns_by_row=0)
eq_(sheet.to_array(), test_content)
expected = dedent("""
pyexcel sheet:
+-----+---+
| A | B |
+=====+===+
| 100 | 2 |
+-----+---+""").strip("\n")
sheet[0, "A"] = 100
print(str(sheet))
eq_(str(sheet), expected)
def test_data_frame_access():
test_content = [['', 'A', 'B'], ['R', 1, 2]]
sheet = Sheet(copy.deepcopy(test_content),
name_columns_by_row=0,
name_rows_by_column=0)
eq_(sheet.to_array(), test_content)
expected = dedent("""
pyexcel sheet:
+---+-----+---+
| | A | B |
+===+=====+===+
| R | 100 | 2 |
+---+-----+---+""").strip('\n')
sheet['R', 'A'] = 100
print(str(sheet))
eq_(str(sheet), expected)
def get_sheet(**keywords):
"""Get an instance of :class:`Sheet` from an excel source
:param file_name: a file with supported file extension
:param file_content: the file content
:param file_stream: the file stream
:param file_type: the file type in *content*
:param session: database session
:param table: database table
:param model: a django model
:param adict: a dictionary of one dimensional arrays
:param url: a download http url for your excel file
:param with_keys: load with previous dictionary's keys, default is True
:param records: a list of dictionaries that have the same keys
:param array: a two dimensional array, a list of lists
:param keywords: additional parameters, see :meth:`Sheet.__init__`
:param sheet_name: sheet name. if sheet_name is not given,
the default sheet at index 0 is loaded
Not all parameters are needed. Here is a table
========================== =========================================
source parameters
========================== =========================================
loading from file file_name, sheet_name, keywords
loading from string file_content, file_type, sheet_name, keywords
loading from stream file_stream, file_type, sheet_name, keywords
loading from sql session, table
loading from sql in django model
loading from query sets any query sets(sqlalchemy or django)
loading from dictionary adict, with_keys
loading from records records
loading from array array
loading from an url url
========================== =========================================
see also :ref:`a-list-of-data-structures`
"""
sheet_params = {}
for field in constants.VALID_SHEET_PARAMETERS:
if field in keywords:
sheet_params[field] = keywords.pop(field)
named_content = sources.get_sheet_stream(**keywords)
sheet = Sheet(named_content.payload, named_content.name, **sheet_params)
return sheet
def test_data_frame_access():
test_content = [["", "A", "B"], ["R", 1, 2]]
sheet = Sheet(
copy.deepcopy(test_content),
name_columns_by_row=0,
name_rows_by_column=0,
)
eq_(sheet.to_array(), test_content)
expected = dedent("""
pyexcel sheet:
+---+-----+---+
| | A | B |
+===+=====+===+
| R | 100 | 2 |
+---+-----+---+""").strip("\n")
sheet["R", "A"] = 100
print(str(sheet))
eq_(str(sheet), expected)
def load_from_sheets(self, sheets):
"""
Load content from existing sheets
:param dict sheets: a dictionary of sheets. Each sheet is
a list of lists
"""
if sheets is None:
return
keys = sheets.keys()
for name in keys:
value = sheets[name]
if isinstance(value, Sheet):
sheet = value
sheet.name = name
else:
# array
sheet = Sheet(value, name)
# this sheets keep sheet order
self.__sheets.update({name: sheet})
# this provide the convenience of access the sheet
self.__dict__[name.replace(" ", "_")] = sheet
self.__name_array = list(self.__sheets.keys())
import os
import django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'Soprano.settings')
django.setup()
from pyexcel.sheet import Sheet
from soprano.uploaders import print_layout_uploader
from django.db.utils import IntegrityError
layouts_dir = '/home/dfitzgerald/projects/PsychENCODE/RPPA/Layouts'
for layout in os.listdir(layouts_dir):
layout_name = layout.split('.')[0]
layout_matrix = [
line.strip('\n').split('\t')
for line in open(os.path.join(layouts_dir, layout))
]
print(layout)
sheet = Sheet(layout_matrix)
try:
print_layout_uploader(sheet, layout_name)
except IntegrityError:
pass
def test_svg_representation():
array = [["a", "b"], [1, 2]]
sheet = Sheet(array)
io = sheet.plot()
assert io._repr_svg_() is not None
def export_network(instance, request):
# get the license from this animal
license_object = instance.get_object().mouse.mouse_set.license
# get the user from this animal
user_object = license_object.owner
# get all the mice from this license and user
# first get the mouse_sets
mouse_sets = MouseSet.objects.filter(owner=user_object, license=license_object)
# now get the mice within this mouse set
mice = [list(el.mouse.all()) for el in mouse_sets]
# flatten the list
mice = [el for sublist in mice for el in sublist]
# get the corresponding scoresheets
scoresheets = [list(el.score_sheet.all()) for el in mice]
# get the fields
# get the fields
fields = ([f.name for f in ScoreSheet._meta.get_fields() if not f.is_relation] + ['mouse__mouse_name',
'owner__username'])
# allocate a dictionary to store the scoresheets
out_dict = {}
# for all the mice
for idx, animal in enumerate(scoresheets):
# generate the name of the sheet
sheet_name = str(mice[idx])
dest_keywords, source_keywords = _split_keywords(query_sets=animal, column_names=fields,
dest_file_type='xls')
sheet_params = {}
for field in constants.VALID_SHEET_PARAMETERS:
if field in source_keywords:
sheet_params[field] = source_keywords.pop(field)
sheet_stream = sources.get_sheet_stream(**source_keywords)
# turn it into a sheet
sheet = Sheet(sheet_stream.payload, sheet_name, **sheet_params)
# put in the dictionary
out_dict[sheet_name] = sheet
# turn the dictionary into a book
out_book = pe.get_book(bookdict=out_dict)
# get the file name for the book
file_name = ' '.join(('Score Sheet', license_object.license_id.split('_')[1], 'Food Water', str(user_object))) \
+ '.xls'
# get the final path
book_path = os.path.join(license_object.score_sheet_path, file_name)
# save the book to file
out_book.save_as(book_path)
# the following code prompts the user to save the spreadsheet wherever, might be useful later
# book_stream = sources.save_book(out_book, file_type='xls')
# # get the response
# response = excel._make_response(book_stream, 'xls', 200, file_name=file_name)
# # get the content disposition from the response to add to the http response
# content_disposition = {'Content-Disposition': response['Content-Disposition']}
# # get the http response
# hresponse = HttpResponse(response, content_type='application/vnd.ms-excel')
# # edit the headers to include the content disposition, since for some reason httpresponse doesn't do it
# hresponse._headers['content-disposition'] = list(content_disposition.items())[0]
# return hresponse
return HttpResponseRedirect('/loggers/score_sheet/')
def save_as(**keywords):
"""Save a sheet from a data source to another one
It accepts two sets of keywords. Why two sets? one set is
source, the other set is destination. In order to distinguish
the two sets, source set will be exactly the same
as the ones for :meth:`pyexcel.get_sheet`; destination
set are exactly the same as the ones for :class:`pyexcel.Sheet.save_as`
but require a 'dest' prefix.
:param file_name: a file with supported file extension
:param file_content: the file content
:param file_stream: the file stream
:param file_type: the file type in *content*
:param session: database session
:param table: database table
:param model: a django model
:param adict: a dictionary of one dimensional arrays
:param url: a download http url for your excel file
:param with_keys: load with previous dictionary's keys, default is True
:param records: a list of dictionaries that have the same keys
:param array: a two dimensional array, a list of lists
:param keywords: additional parameters, see :meth:`Sheet.__init__`
:param sheet_name: sheet name. if sheet_name is not given,
the default sheet at index 0 is loaded
:param dest_file_name: another file name. **out_file** is deprecated
though is still accepted.
:param dest_file_type: this is needed if you want to save to memory
:param dest_session: the target database session
:param dest_table: the target destination table
:param dest_model: the target django model
:param dest_mapdict: a mapping dictionary,
see :meth:`pyexcel.Sheet.save_to_memory`
:param dest_initializer: a custom initializer function for table or model
:param dest_mapdict: nominate headers
:param dest_batch_size: object creation batch size.
it is Django specific
:returns: IO stream if saving to memory. None otherwise
if csv file is destination format, python csv
`fmtparams <https://docs.python.org/release/3.1.5/
library/csv.html#dialects-and-formatting-parameters>`_
are accepted
for example: dest_lineterminator will replace default '\r\n'
to the one you specified
========================== =========================================
source parameters
========================== =========================================
loading from file file_name, sheet_name, keywords
loading from string file_content, file_type, sheet_name, keywords
loading from stream file_stream, file_type, sheet_name, keywords
loading from sql session, table
loading from sql in django model
loading from query sets any query sets(sqlalchemy or django)
loading from dictionary adict, with_keys
loading from records records
loading from array array
loading from an url url
========================== =========================================
================= =============================================
Saving to source parameters
================= =============================================
file dest_file_name, dest_sheet_name,
keywords with prefix 'dest'
memory dest_file_type, dest_content,
dest_sheet_name, keywords with prefix 'dest'
sql dest_session, dest_table,
dest_initializer, dest_mapdict
django model dest_model, dest_initializer,
dest_mapdict, dest_batch_size
================= =============================================
In addition, this function use :class:`pyexcel.Sheet` to
render the data which could have performance penalty. In exchange,
parameters for :class:`pyexcel.Sheet` can be passed on, e.g.
`name_columns_by_row`.
"""
dest_keywords, source_keywords = _split_keywords(**keywords)
sheet_params = {}
for field in constants.VALID_SHEET_PARAMETERS:
if field in source_keywords:
sheet_params[field] = source_keywords.pop(field)
sheet_stream = sources.get_sheet_stream(**source_keywords)
sheet = Sheet(sheet_stream.payload, sheet_stream.name, **sheet_params)
return sources.save_sheet(sheet, **dest_keywords)