'Export nodes in the hierarchy in XLS format, including
ancestor references.
This is controlled by the hierarchy_export setting, which is
a dict like:
"field": "name",        - the field name for the ancestor reference
"root": "Organisation"  - the label for the root level
"branch": "Branch"      - the label for the branch levels
"prefix": "Sub"         - the prefix for the branch label
With this configuration, the ancestor columns would appear like:
Organisation, Branch, SubBranch, SubSubBranch, SubSubSubBranch,...
All parts of the setting can be omitted, the defaults are as follows:
- "field" defaults to "name"
- "root" is automatically generated from the resource name
- "branch" defaults to prefix+root
- "prefix" defaults to "Sub"
@status: experimental'

def export_xls(self, r, **attr):

    resource = self.resource
    tablename = resource.tablename
    h = S3Hierarchy(tablename=tablename)
    if (not h.config):
        r.error(405, ('No    hierarchy    configured    for    %s' % tablename))
    setting = resource.get_config('hierarchy_export', {})
    field = setting.get('field')
    if (not field):
        field = ('name' if ('name' in resource.fields) else resource._id.name)
    prefix = setting.get('prefix', 'Sub')
    root = setting.get('root')
    if (not root):
        root = ''.join((s.capitalize() for s in resource.name.split('_')))
    branch = setting.get('branch')
    if (not branch):
        branch = ('%s%s' % (prefix, root))
    rfield = resource.resolve_selector(field)
    list_fields = resource.list_fields('export_fields', id_column=False)
    rfields = resource.resolve_selectors(list_fields, extra_fields=False)[0]
    selectors = [h.pkey.name, rfield.selector]
    columns = []
    types = []
    headers = {}
    for rf in rfields:
        selectors.append(rf.selector)
        if (rf.colname == rfield.colname):
            continue
        columns.append(rf.colname)
        headers[rf.colname] = rf.label
        if (rf.ftype == 'virtual'):
            types.append('string')
        else:
            types.append(rf.ftype)
    if self.record_id:
        if (r.component and (h.pkey.name != resource._id.name)):
            query = (resource.table._id == self.record_id)
            row = current.db(query).select(h.pkey, limitby=(0, 1)).first()
            if (not row):
                r.error(404, current.ERROR.BAD_RECORD)
            roots = set([row[h.pkey]])
        else:
            roots = set([self.record_id])
    else:
        roots = h.roots
    all_nodes = h.findall(roots, inclusive=True)
    from s3query import FS
    query = FS(h.pkey.name).belongs(all_nodes)
    clone = current.s3db.resource(resource, filter=query)
    data = clone.select(selectors, represent=True, raw_data=True)
    hkey = str(h.pkey)
    data_dict = dict(((row._row[hkey], row) for row in data.rows))
    depth = max((h.depth(node_id) for node_id in roots))
    htypes = []
    hcolumns = []
    colprefix = 'HIERARCHY'
    htype = ('string' if (rfield.ftype == 'virtual') else rfield.ftype)
    for level in xrange((depth + 1)):
        col = ('%s.%s' % (colprefix, level))
        if (level == 0):
            headers[col] = root
        elif (level == 1):
            headers[col] = branch
        else:
            headers[col] = ('%s%s' % (''.join(([prefix] * (level - 1))), branch))
        hcolumns.append(col)
        htypes.append(htype)
    output = [headers, (htypes + types)]
    for node_id in roots:
        rows = h.export_node(node_id, prefix=colprefix, depth=depth, hcol=rfield.colname, columns=columns, data=data_dict)
        output.extend(rows)
    from s3codec import S3Codec
    codec = S3Codec.get_codec('xls')
    result = codec.encode(output, title=resource.name, list_fields=(hcolumns + columns))
    return result
