'Convert a table in an XLS (MS Excel) sheet into an ElementTree,
consisting of <table name="format">, <row> and
<col field="fieldname"> elements (see: L{csv2tree}).
The returned ElementTree can be imported using S3CSV
stylesheets (through S3Resource.import_xml()).
@param source: the XLS source (stream, or XLRD book, or
None if sheet is an open XLRD sheet)
@param resourcename: the resource name
@param extra_data: dict of extra cols {key:value} to add to each row
@param hashtags: dict of hashtags for extra cols {key:hashtag}
@param sheet: sheet name or index, or an open XLRD sheet
(open work sheet overrides source)
@param rows: Rows range, integer (length from 0) or
tuple (start, length) - or a tuple (start,) to
read all available rows after start
@param cols: Columns range, like "rows"
@param fields: Field map, a dict {index: fieldname} where
index is the column index counted from the
first column within the specified range,
e.g. cols=(2,7), fields={1:"MyField"}
means column 3 in the sheet is "MyField",
the field map can be omitted to read the field
names from the sheet (see "header_row")
@param header_row: the first row contains column headers
(if fields is None, they will be used
as field names in the output - otherwise
they will be ignored)
@return: an etree.ElementTree representing the table'

@classmethod
def xls2tree(cls, source, resourcename=None, extra_data=None, hashtags=None, sheet=None, rows=None, cols=None, fields=None, header_row=True):

    import xlrd
    ATTRIBUTE = cls.ATTRIBUTE
    FIELD = ATTRIBUTE.field
    HASHTAG = ATTRIBUTE.hashtag
    TAG = cls.TAG
    COL = TAG.col
    SubElement = etree.SubElement
    DEFAULT_SHEET_NAME = 'SahanaData'
    root = etree.Element(TAG.table)
    if (resourcename is not None):
        root.set(ATTRIBUTE.name, resourcename)
    if isinstance(sheet, xlrd.sheet.Sheet):
        s = sheet
    else:
        if hasattr(source, 'read'):
            if hasattr(source, 'seek'):
                source.seek(0)
            wb = xlrd.open_workbook(file_contents=source.read(), on_demand=True)
        elif isinstance(source, xlrd.book.Book):
            wb = source
        else:
            raise RuntimeError(('xls2tree:    invalid    source    %s' % type(source)))
        try:
            if isinstance(sheet, (int, long)):
                s = wb.sheet_by_index(sheet)
            elif isinstance(sheet, basestring):
                s = wb.sheet_by_name(sheet)
            elif (sheet is None):
                if (DEFAULT_SHEET_NAME in wb.sheet_names()):
                    s = wb.sheet_by_name(DEFAULT_SHEET_NAME)
                else:
                    s = wb.sheet_by_index(0)
            else:
                raise SyntaxError(('xls2tree:    invalid    sheet    %s' % sheet))
        except IndexError as xlrd.XLRDError:
            s = None
    def cell_range(cells, max_cells):
        '\n                                                                Helper    method    to    calculate    a    cell    range\n\n                                                                @param    cells:    the    specified    range\n                                                                @param    max_cells:    maximum    number    of    cells\n                                                '
        if (not cells):
            cells = (0, max_cells)
        elif (not isinstance(cells, (tuple, list))):
            cells = (0, cells)
        elif (len(cells) == 1):
            cells = (cells[0], max_cells)
        else:
            cells = (cells[0], (cells[0] + cells[1]))
        return cells
    if s:
        rows = cell_range(rows, s.nrows)
        cols = cell_range(cols, s.ncols)
        if fields:
            headers = fields
        elif (not header_row):
            headers = dict(((i, ('%s' % i)) for i in range((cols[1] - cols[0]))))
        else:
            headers = {}
        decode_date = (lambda v: xlrd.xldate.xldate_as_datetime(v, wb.datemode))
        def decode(t, v):
            '\n                                                                                Helper    method    to    decode    the    cell    value    by    type\n\n                                                                                @param    t:    the    cell    type\n                                                                                @param    v:    the    cell    value\n                                                                                @return:    text    representation    of    the    cell    value\n                                                                '
            text = ''
            if v:
                if (t is None):
                    text = s3_unicode(v).strip()
                elif (t == xlrd.XL_CELL_TEXT):
                    text = v.strip()
                elif (t == xlrd.XL_CELL_NUMBER):
                    text = (str(long(v)) if (long(v) == v) else str(v))
                elif (t == xlrd.XL_CELL_DATE):
                    text = s3_encode_iso_datetime(decode_date(v))
                elif (t == xlrd.XL_CELL_BOOLEAN):
                    text = str(value).lower()
            return text
        def add_col(row, name, t, v, hashtags=None):
            '\n                                                                                Helper    method    to    add    a    column    to    an    output    row\n\n                                                                                @param    row:    the    output    row    (etree.Element)\n                                                                                @param    name:    the    column    name\n                                                                                @param    t:    the    cell    type\n                                                                                @param    v:    the    cell    value\n                                                                '
            col = SubElement(row, COL)
            col.set(FIELD, name)
            if hashtags:
                hashtag = hashtags.get(name)
                if (hashtag and hashtag[1:]):
                    col.set(HASHTAG, hashtag)
            col.text = decode(t, v)
        hashtags = (dict(hashtags) if hashtags else {})
        ROW = TAG.row
        record_idx = 0
        extra_fields = (set(extra_data) if extra_data else None)
        check_headers = (extra_fields is not None)
        for ridx in range(*rows):
            types = s.row_types(ridx, *cols)
            values = s.row_values(ridx, *cols)
            if (not any(((v != '') for v in values))):
                continue
            if (header_row and (record_idx == 0)):
                if (not fields):
                    for (cidx, value) in enumerate(values):
                        header = decode(types[cidx], value)
                        headers[cidx] = header
                        if check_headers:
                            extra_fields.discard(header)
                    check_headers = False
            else:
                if ((not fields) and ((header_row and (record_idx == 1)) or (record_idx == 0))):
                    items = {}
                    for (cidx, name) in headers.items():
                        try:
                            t = types[cidx]
                            v = values[cidx]
                        except IndexError:
                            continue
                        if (t not in (xlrd.XL_CELL_TEXT, xlrd.XL_CELL_EMPTY)):
                            items = None
                            break
                        elif v:
                            items[name] = v
                    if (items and all(((v[0] == '#') for v in items.values()))):
                        hashtags.update(items)
                        continue
                orow = SubElement(root, ROW)
                for (cidx, name) in headers.items():
                    if check_headers:
                        extra_fields.discard(name)
                    try:
                        t = types[cidx]
                        v = values[cidx]
                    except IndexError:
                        pass
                    else:
                        add_col(orow, name, t, v, hashtags=hashtags)
                check_headers = False
                if extra_fields:
                    for key in extra_fields:
                        add_col(orow, key, None, extra_data[key], hashtags=hashtags)
            record_idx += 1
    return etree.ElementTree(root)
