Python xlrd 模块，XL_CELL_NUMBER 实例源码

def cell_display(cell, datemode=0, encoding='ascii'):
    cty = cell.ctype
    if cty == xlrd.XL_CELL_EMPTY:
        return 'undefined'
    if cty == xlrd.XL_CELL_BLANK:
        return 'blank'
    if cty == xlrd.XL_CELL_NUMBER:
        return 'number (%.4f)' % cell.value
    if cty == xlrd.XL_CELL_DATE:
        try:
            return "date (%04d-%02d-%02d %02d:%02d:%02d)" \
                % xlrd.xldate_as_tuple(cell.value, datemode)
        except xlrd.xldate.XLDateError:
            return "date? (%.6f)" % cell.value
    if cty == xlrd.XL_CELL_TEXT:
        return "text (%s)" % cell.value.encode(encoding, 'replace')
    if cty == xlrd.XL_CELL_ERROR:
        if cell.value in xlrd.error_text_from_code:
            return "error (%s)" % xlrd.error_text_from_code[cell.value]
        return "unknown error code (%r)" % cell.value
    if cty == xlrd.XL_CELL_BOOLEAN:
        return "logical (%s)" % ['FALSE', 'TRUE'][cell.value]
    raise Exception("Unknown Cell.ctype: %r" % cty)

def build(self):
        import xlrd
        tf = os.path.dirname(os.path.abspath(__file__))+os.sep+"sources"+os.sep+"pmsp.xls"
        if not tf:
            tf = self.download()
        if not tf:
            return
        f = xlrd.open_workbook(tf)
        self.descriptions = []
        self.values = []
        self.units = []
        self.codes = []
        sh = f.sheets()[0]
        for i in range(1,sh.nrows):
            r = sh.row(i)
            if (r[0].ctype == xlrd.XL_CELL_NUMBER) and (r[1].ctype == xlrd.XL_CELL_TEXT) and (r[7].ctype == xlrd.XL_CELL_TEXT) and (r[9].ctype == xlrd.XL_CELL_NUMBER):
                c = str(int(r[0].value))
                cs = c[0:2]+"."+c[2:4]+"."
                if len(c[4:]) == 1:
                    cs += "0"+c[4:]
                else:
                    cs += c[4:]
                self.codes.append(cs)
                self.descriptions.append(r[1].value)
                self.units.append(r[7].value)
                self.values.append(r[9].value)
        #f.close()
        print "parsed data: ",len(self.codes),"/",len(self.descriptions),"/",len(self.values),"/",len(self.units)

def build(self):
        import xlrd
        tf = None
        if not tf:
            defaultlocation = os.path.dirname(os.path.abspath(__file__))+os.sep+"sources"+os.sep+"seinfra.xls"
            if os.path.exists(defaultlocation):
                print "building from ",defaultlocation
                tf = defaultlocation
            else:
                tf = self.download()
        if not tf:
            return
        f = xlrd.open_workbook(tf)
        self.descriptions = []
        self.values = []
        self.units = []
        self.codes = []
        sh = f.sheets()[0]
        for i in range(5,sh.nrows):
            r = sh.row(i)
            if (r[0].ctype == xlrd.XL_CELL_TEXT) and (r[1].ctype == xlrd.XL_CELL_TEXT) and (r[4].ctype == xlrd.XL_CELL_TEXT) and (r[5].ctype == xlrd.XL_CELL_NUMBER):
                self.codes.append(r[0].value)
                self.descriptions.append(r[1].value)
                self.units.append(r[4].value)
                self.values.append(r[5].value)
        #f.close()
        print "parsed data: ",len(self.codes),"/",len(self.descriptions),"/",len(self.values),"/",len(self.units)

def _get_cell_raw(self, cell):
        if cell.ctype == xlrd.XL_CELL_TEXT:
            return cell.value;
        if cell.ctype == xlrd.XL_CELL_NUMBER:
            number = int(cell.value);
            return u"%d" % number if number == cell.value else u"%g" % cell.value;
        if cell.ctype == xlrd.XL_CELL_DATE:
            dt = xlrd.xldate.xldate_as_datetime(cell.value, self._workbook.datemode);
            return u"%s" % dt;
        if cell.ctype == xlrd.XL_CELL_BOOLEAN:
            return  u"true" if cell.value else u"false";
        return u"";

def _get_cell_string(self, cell):
        cell_text = "";
        if cell.ctype == xlrd.XL_CELL_TEXT:
            cell_text = cell.value;
        if cell.ctype == xlrd.XL_CELL_NUMBER:
            number = int(cell.value);
            cell_text = u"%d" % number if number == cell.value else u"%g" % cell.value;
        if cell.ctype == xlrd.XL_CELL_DATE:
            dt = xlrd.xldate.xldate_as_datetime(cell.value, self._workbook.datemode);
            cell_text = u"%s" % dt;
        if cell.ctype == xlrd.XL_CELL_BOOLEAN:
            cell_text =  u"true" if cell.value else u"false";
        return u'"%s"' % cell_text;

def _get_cell_number(self, cell):
        if cell.ctype == xlrd.XL_CELL_TEXT:
            #?????????????,?????,??????,????????...
            return cell.value;
        if cell.ctype == xlrd.XL_CELL_NUMBER:
            number = int(cell.value);
            return u"%d" % number if number == cell.value else u"%g" % cell.value;
        if cell.ctype == xlrd.XL_CELL_DATE:
            dt = xlrd.xldate.xldate_as_datetime(cell.value, self._workbook.datemode);
            return u"%d" % time.mktime(dt.timetuple());
        if cell.ctype == xlrd.XL_CELL_BOOLEAN:
            return  u"1" if cell.value else u"0";
        return u"0";

def cells_all_junk(cells, is_rubbish=None):
    """\
    Return True if all cells in the sequence are junk.
    What qualifies as junk:
    -- empty cell
    -- blank cell
    -- zero-length text
    -- text is all whitespace
    -- number cell and is 0.0
    -- text cell and is_rubbish(cell.value) returns True.
    """
    for cell in cells:
        if cell.ctype in null_cell_types:
            continue
        if cell.ctype == XL_CELL_TEXT:
            if not cell.value:
                continue
            if cell.value.isspace():
                continue
        if cell.ctype == XL_CELL_NUMBER:
            if not cell.value:
                continue
        if is_rubbish is not None and is_rubbish(cell):
            continue
        return False
    return True

def cell_value(cell, datemode):
    ctype = cell.ctype
    value = cell.value

    if ctype == xlrd.XL_CELL_ERROR:
        raise ValueError(repr(cell), 'cell error')

    elif ctype == xlrd.XL_CELL_BOOLEAN:
        return str(value).upper()

    elif ctype == xlrd.XL_CELL_NUMBER:
        if value.is_integer():
            value = int(value)
        return str(value)

    elif ctype == xlrd.XL_CELL_DATE:
        value = xlrd.xldate_as_tuple(value, datemode)
        if value[3:] == (0, 0, 0):
            return datetime.date(*value[:3]).isoformat()
        else:
            return datetime.datetime(*value).isoformat()

    elif ctype in (xlrd.XL_CELL_TEXT, xlrd.XL_CELL_EMPTY, xlrd.XL_CELL_BLANK):
        return value

    raise ValueError(repr(cell), 'unknown cell type')

def parse_1_pos_row( cells_in_row, rowno ):

    col_count = len(cells_in_row)
    if ( col_count < 9 ):
        print "?%d???%d???????'????'? " % ( rowno, col_count)
        return None

    one_entry =  PositionAggreRecord()

    #??
    one_entry.contract = cells_in_row[0].value

    #??
    target = get_target_from_contract( one_entry.contract )
    if (target is None):
        print "?%d? ?????‘????’??????'????'? " % ( rowno, )
        return None

    one_entry.target = target

    #?/?
    buy_vol_cell = cells_in_row[1]
    if ( buy_vol_cell.ctype == xlrd.XL_CELL_NUMBER  ):
        one_entry.b_or_s = '?'
        one_entry.volume = int(buy_vol_cell.value)
        one_entry.avg_price = float(cells_in_row[2].value)
    else:
        one_entry.b_or_s = '?'
        one_entry.volume = int(cells_in_row[3].value)
        one_entry.avg_price = float(cells_in_row[4].value)

    #??
    one_entry.prev_settle_price = float(cells_in_row[5].value)
    #??
    one_entry.today_settle_price = float(cells_in_row[6].value)

    #??
    one_entry.profit = float (cells_in_row[7].value)

    return one_entry

# ??'????' sheet? ???? TradeAggreRecord???

def cell(self,rdrowx,rdcolx,wtrowx,wtcolx):
        cell = self.rdsheet.cell(rdrowx,rdcolx)
        # setup column attributes if not already set
        if wtcolx not in self.wtcols and rdcolx in self.rdsheet.colinfo_map:
            rdcol = self.rdsheet.colinfo_map[rdcolx]
            wtcol = self.wtsheet.col(wtcolx)
            wtcol.width = rdcol.width
            wtcol.set_style(self.style_list[rdcol.xf_index])
            wtcol.hidden = rdcol.hidden
            wtcol.level = rdcol.outline_level
            wtcol.collapsed = rdcol.collapsed
            self.wtcols.add(wtcolx)
        # copy cell
        cty = cell.ctype
        if cty == xlrd.XL_CELL_EMPTY:
            return
        if cell.xf_index is not None:
            style = self.style_list[cell.xf_index]
        else:
            style = default_style
        rdcoords2d = (rdrowx, rdcolx)
        if rdcoords2d in self.merged_cell_top_left_map:
            # The cell is the governing cell of a group of 
            # merged cells.
            rlo, rhi, clo, chi = self.merged_cell_top_left_map[rdcoords2d]
            assert (rlo, clo) == rdcoords2d
            self.wtsheet.write_merge(
                wtrowx, wtrowx + rhi - rlo - 1,
                wtcolx, wtcolx + chi - clo - 1, 
                cell.value, style)
            return
        if rdcoords2d in self.merged_cell_already_set:
            # The cell is in a group of merged cells.
            # It has been handled by the write_merge() call above.
            # We avoid writing a record again because:
            # (1) It's a waste of CPU time and disk space.
            # (2) xlwt does not (as at 2007-01-12) ensure that only
            # the last record is written to the file.
            # (3) If you write a data record for a cell
            # followed by a blank record for the same cell,
            # Excel will display a blank but OOo Calc and
            # Gnumeric will display the data :-(
            return
        wtrow = self.wtsheet.row(wtrowx)
        if cty == xlrd.XL_CELL_TEXT:
            wtrow.set_cell_text(wtcolx, cell.value, style)
        elif cty == xlrd.XL_CELL_NUMBER or cty == xlrd.XL_CELL_DATE:
            wtrow.set_cell_number(wtcolx, cell.value, style)
        elif cty == xlrd.XL_CELL_BLANK:
            wtrow.set_cell_blank(wtcolx, style)
        elif cty == xlrd.XL_CELL_BOOLEAN:
            wtrow.set_cell_boolean(wtcolx, cell.value, style)
        elif cty == xlrd.XL_CELL_ERROR:
            wtrow.set_cell_error(wtcolx, cell.value, style)
        else:
            raise Exception(
                "Unknown xlrd cell type %r with value %r at (shx=%r,rowx=%r,colx=%r)" \
                % (cty, value, sheetx, rowx, colx)
                )