def excel_reader(stream, worksheet_name=None, required_headers=[]):
'''Reads an XLSX file (from ``stream``) and yields objects so you can
access the values conveniently.
You can pass in the ``worksheet_name`` to be read. If not passed in or
not present in the file, the first worksheet will be read.
In addition, you may pass a sequence of *required_headers*, and if they
aren't all present, KeyError is raised.
Let's see an example. Suppose you are reading some Excel file and
all you know is it contains the columns "E-mail", "Full Name" and
"Gender", not necessarily in that order::
reader = excel_reader(
open('contacts.xlsx', mode='rb'),
worksheet_name='Mailing',
required_headers=['E-mail', 'Full Name', 'Gender'])
for o in reader:
print(o.full_name, o.e_mail, o.gender)
'''
try:
wb = load_workbook(stream, data_only=True)
except (BadZipFile, InvalidFileException) as e:
raise Problem(
_('That is not an XLSX file.'),
error_title=_('Unable to read the XLSX file'), error_debug=str(e))
# Grab either the worksheet named "Assets", or simply the first one
if worksheet_name and worksheet_name in wb:
sheet = wb[worksheet_name]
else:
sheet = wb[wb.sheetnames[0]]
this_is_the_first_row = True
for row in sheet.rows:
if this_is_the_first_row: # Read and validate the headers
this_is_the_first_row = False
headers = [cell.value for cell in row]
raise_if_missing_required_headers(headers, required_headers)
vars = get_corresponding_variable_names(headers, required_headers)
index_of_var = {var: i for i, var in enumerate(vars)}
class SpreadsheetRow(object):
'''View on a spreadsheet row so you can access data as if
they were instance variables.
'''
__slots__ = ('__cells',)
def __init__(self, cells):
self.__cells = cells
def __getattr__(self, attr):
content = self.__cells[index_of_var[attr]].value
return content
else:
yield SpreadsheetRow(row)
def raise_if_missing_required_headers(headers, required_headers=[], case_sensitive=False):
'''Raises KeyError if the ``required_headers`` aren't all present in
``headers``.
'''
if not case_sensitive:
headers = [h.lower() if h else None for h in headers]
missing_headers = [h for h in required_headers
if h.lower() not in headers]
else:
missing_headers = [h for h in required_headers if h not in headers]
if missing_headers:
if len(missing_headers) == 1:
msg = _('The spreadsheet is missing the required header: ')
else:
msg = _('The spreadsheet is missing the required headers: ')
raise KeyError(
msg + ', '.join(['"{}"'.format(h) for h in missing_headers]))
def maybe_raise_unprocessable(e, **adict):
'''If the provided exception looks like a validation error, raise
422 Unprocessable Entity, optionally with additional information.
'''
if hasattr(e, 'asdict') and callable(e.asdict):
error_msg = getattr(
e, 'error_msg', _('Please correct error(s) in the form.'))
adict['invalid'] = e.asdict()
adict.setdefault('error_title', 'Invalid')
adict.setdefault('error_msg', error_msg)
raise HTTPError(
status_int=422, # Unprocessable Entity
content_type='application/json',
body=dumps(adict),
detail=error_msg, # could be shown to end users
# *comment* is not displayed to end users:
comment=str(e) or 'Form validation error',
)
pages: PAGES,
ops: OPERATIONS,
_find: function (map, name, options) {
var s = map[name].url_templ;
if (!s) throw new Error('burla: No item called "' + name + '".');
for (var key in options) {
var placeholder = ':' + key;
if (s.indexOf(placeholder) == -1) {
throw new Error('burla: URL template "' + name + '" does not use parameter "' + key + '".');
}
var val = options[key];
if (val == null) throw new Error('burla: Operation "' + name + '" needs parameter "' + key + '".');
s = s.replace(placeholder, val);
}
return this.root + s;
},
page: function (name, options) { return this._find(this.pages, name, options); },
op: function (name, options) { return this._find(this.ops, name, options); },
}\n''' \
.replace('PAGES', dumps(
{o.name: o.to_dict() for o in self.gen_pages()},
sort_keys=True)) \
.replace('OPERATIONS', dumps(
{o.name: o.to_dict() for o in self.gen_ops()},
sort_keys=True)) \
.replace('ROOT', dumps(self.root))
DOC_TITLE = _('HTTP API Documentation')