Python xlrd.xldate_as_tuple方法代码示例

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def assertExcelRow(self, sheet, row_num, values, tz=None):
        """
        Asserts the cell values in the given worksheet row. Date values are converted using the provided timezone.
        """
        expected_values = []
        for expected in values:
            # if expected value is datetime, localize and remove microseconds
            if isinstance(expected, datetime):
                expected = expected.astimezone(tz).replace(microsecond=0, tzinfo=None)
            elif isinstance(expected, date):
                expected = datetime.combine(expected, time(0, 0))

            expected_values.append(expected)

        actual_values = []
        for c in range(0, sheet.ncols):
            cell = sheet.cell(row_num, c)
            actual = cell.value

            if cell.ctype == XL_CELL_DATE:
                actual = datetime(*xldate_as_tuple(actual, sheet.book.datemode))

            actual_values.append(actual)

        self.assertEqual(actual_values, expected_values) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def get_cell_val(sheet, i, j, datemode=0):
    ctype = sheet.cell(i, j).ctype
    cell = sheet.cell_value(i, j)
    if ctype == 2 and cell % 1 == 0: # 如果是整形
        cell = int(cell)
    elif ctype == 3:
        # 转换为datetime对象
        if cell >= 1.0 and cell < 61.0:
            date_value = xlrd.xldate_as_datetime(cell, datemode)
            cell = date_value.strftime('%Y/%m/%d %H:%M:%S')
            return cell
        date_value = xlrd.xldate_as_tuple(cell, datemode)
        if date_value[0]==0 and date_value[1] == 0 and date_value[2] == 0:
            cell = '%d:%02d:%02d'%(date_value[3], date_value[4], date_value[5])
        elif date_value[3]==0 and date_value[4] == 0 and date_value[5] == 0:
            cell = date(*date_value[:3]).strftime('%Y/%m/%d')
        else:
            cell = datetime(*date_value).strftime('%Y/%m/%d %H:%M:%S')
    elif ctype == 4:
        cell = True if cell == 1 else False
    return cell 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def get_row_data(bk, sh, rowx, colrange):
        result = []
        dmode = bk.datemode
        ctys = sh.row_types(rowx)
        cvals = sh.row_values(rowx)
        for colx in colrange:
            cty = ctys[colx]
            cval = cvals[colx]
            if bk.formatting_info:
                cxfx = str(sh.cell_xf_index(rowx, colx))
            else:
                cxfx = ''
            if cty == xlrd.XL_CELL_DATE:
                try:
                    showval = xlrd.xldate_as_tuple(cval, dmode)
                except xlrd.XLDateError as e:
                    showval = "%s:%s" % (type(e).__name__, e)
                    cty = xlrd.XL_CELL_ERROR
            elif cty == xlrd.XL_CELL_ERROR:
                showval = xlrd.error_text_from_code.get(cval, '<Unknown error code 0x%02x>' % cval)
            else:
                showval = cval
            result.append((colx, cty, showval, cxfx))
        return result 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def convert_cell(self, cell, sheet):
        value = cell.value
        try:
            if cell.ctype == 3:
                if value == 0:
                    return None
                year, month, day, hour, minute, second = \
                    xlrd.xldate_as_tuple(value, sheet.book.datemode)
                if (year, month, day) == (0, 0, 0):
                    value = time(hour, minute, second)
                    return value.isoformat()
                else:
                    value = datetime(year, month, day, hour, minute, second)
                    return value.isoformat()
        except Exception:
            pass
        return safe_string(value) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
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) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def read_xls(_file):
    agent_config_vars['data_format'] = 'CSV' # treat as CSV from here out
    agent_config_vars['timestamp_format'] = ['epoch']
    # open workbook
    with xlrd.open_workbook(_file) as wb:
        # for each sheet in the workbook
        for sheet in wb.sheets():
            # for each row in the sheet
            for row in sheet.get_rows():
                # build dict of <field name: value>
                d = label_message(list(map(lambda x: x.value, row)))
                # turn datetime into epoch
                timestamp = ''
                while timestamp == '' and len(agent_config_vars['timestamp_field']) != 0:
                    timestamp_field = agent_config_vars['timestamp_field'].pop(0)
                    try:
                        timestamp_xlrd = d[timestamp_field]
                    except KeyError:
                        continue
                    timestamp = get_timestamp_from_datetime(datetime(
                        *xlrd.xldate_as_tuple(
                            timestamp_xlrd,
                            sheet.book.datemode)))
                d[timestamp_field] = timestamp
                agent_config_vars['timestamp_field'] = [timestamp_field]
                yield d 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def unknownValue(self,num,ctype):
        if ctype == 2 and num % 1 == 0:  # 如果是整形
                return self.Int(num)
        elif ctype == 3:
            # 转成datetime对象
            date = datetime(*xldate_as_tuple(num, 0))
            return date.strftime('%Y/%d/%m %H:%M:%S')
        elif ctype == 4:
            return False if num == 0 else True
        else:
            return num
    # 解析一行 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def TranslateSpreadsheet(sheet, csvfile):
  print 'Translating spreadsheet %s to CSV %s' % (sheet, csvfile)
  wb = xlrd.open_workbook(sheet)
  sheet = wb.sheet_by_index(0)
  with open(csvfile, 'w') as out:
    writer = csv.writer(out, quotechar='\"')
    date_columns = []
    for rownum in range(0, sheet.nrows):
      row = sheet.row_values(rownum)
      for i in range(0, len(row)):
        if type(row[i]) == unicode:
          row[i] = row[i].encode('ascii', 'ignore')
      # For the first two rows, find any labels containing 'Date' substring...
      if rownum==0 or rownum==1:
        for i in range(0, len(row)):
          if isinstance(row[i], basestring) and 'Date' in row[i]:
            print 'Found date column %s at %d' % (row[i], i)
            date_columns.append(i)
      else:
        for i in date_columns:
          translated_date = xlrd.xldate_as_tuple(row[i], 0)
          row[i] = '%04d-%02d-%02d' % (translated_date[0], translated_date[1], translated_date[2])
      for i in range(0, len(row)):
        if type(row[i]) == float and int(row[i]) == row[i]:
          row[i] = int(row[i])

      # Translate all strings to ascii, throwing away any unknown characters
      writer.writerow(row)


# Find the directory of this script. 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def worksheet_from_excel(excel_sheet):
    worksheet = Worksheet()
    for col in range(excel_sheet.ncols):
        for row in range(excel_sheet.nrows):
            cell = excel_sheet.cell(row, col)
            if cell.ctype == XL_CELL_ERROR:
                formula = '=%s' % (error_text_from_code[cell.value], )
            elif cell.ctype == XL_CELL_DATE:
                formula = '=DateTime(%s, %s, %s, %s, %s, %s)' % xldate_as_tuple(
                    cell.value, excel_sheet.book.datemode)
            else:
                formula = unicode(excel_sheet.cell(row, col).value)
            worksheet[col + 1, row + 1].formula = formula
    return worksheet 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def format_date(self, date_str):
        if self.ftype == "csv":
            return datetime.strptime(date_str, self.date_format)
        elif self.ftype == 'xlsx' or self.ftype == 'xls':
            return datetime(*xlrd.xldate_as_tuple(date_str, self.datemode)) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def showable_cell_value(celltype, cellvalue, datemode):
    if celltype == xlrd.XL_CELL_DATE:
        try:
            showval = xlrd.xldate_as_tuple(cellvalue, datemode)
        except xlrd.XLDateError as e:
            showval = "%s:%s" % (type(e).__name__, e)
    elif celltype == xlrd.XL_CELL_ERROR:
        showval = xlrd.error_text_from_code.get(
            cellvalue, '<Unknown error code 0x%02x>' % cellvalue)
    else:
        showval = cellvalue
    return showval 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def parse_date_value(self, value):
        return xlrd.xldate_as_tuple(value, 0) 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def parse_file(datafile):
    workbook = xlrd.open_workbook(datafile)
    sheet = workbook.sheet_by_index(0)

    ### example on how you can get the data
    #sheet_data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)]

    ### other useful methods:
    # print "\nROWS, COLUMNS, and CELLS:"
    # print "Number of rows in the sheet:", 
    # print sheet.nrows
    # print "Type of data in cell (row 3, col 2):", 
    # print sheet.cell_type(3, 2)
    # print "Value in cell (row 3, col 2):", 
    # print sheet.cell_value(3, 2)
    # print "Get a slice of values in column 3, from rows 1-3:"
    # print sheet.col_values(3, start_rowx=1, end_rowx=4)

    # print "\nDATES:"
    # print "Type of data in cell (row 1, col 0):", 
    # print sheet.cell_type(1, 0)
    # exceltime = sheet.cell_value(1, 0)
    # print "Time in Excel format:",
    # print exceltime
    # print "Convert time to a Python datetime tuple, from the Excel float:",
    # print xlrd.xldate_as_tuple(exceltime, 0)
    
    
    data = {
            'maxtime': (0, 0, 0, 0, 0, 0),
            'maxvalue': 0,
            'mintime': (0, 0, 0, 0, 0, 0),
            'minvalue': 0,
            'avgcoast': 0
    }
    return data 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def parse_file(datafile):
    workbook = xlrd.open_workbook(datafile)
    sheet = workbook.sheet_by_index(0)
    data = None
    # YOUR CODE HERE
    # Remember that you can use xlrd.xldate_as_tuple(sometime, 0) to convert
    # Excel date to Python tuple of (year, month, day, hour, minute, second)
    return data 

# 需要导入模块: import xlrd [as 别名]
# 或者: from xlrd import xldate_as_tuple [as 别名]
def xls_value_to_unicode(value, value_type, datemode):
    """
    Take a xls formatted value and try to make a unicode string
    representation.
    """
    if value_type == xlrd.XL_CELL_BOOLEAN:
        return "TRUE" if value else "FALSE"
    elif value_type == xlrd.XL_CELL_NUMBER:
        # Try to display as an int if possible.
        int_value = int(value)
        if int_value == value:
            return unicode(int_value)
        else:
            return unicode(value)
    elif value_type is xlrd.XL_CELL_DATE:
        # Warn that it is better to single quote as a string.
        # error_location = cellFormatString % (ss_row_idx, ss_col_idx)
        # raise Exception(
        #   "Cannot handle excel formatted date at " + error_location)
        datetime_or_time_only = xlrd.xldate_as_tuple(value, datemode)
        if datetime_or_time_only[:3] == (0, 0, 0):
            # must be time only
            return unicode(datetime.time(*datetime_or_time_only[3:]))
        return unicode(datetime.datetime(*datetime_or_time_only))
    else:
        # ensure unicode and replace nbsp spaces with normal ones
        # to avoid this issue:
        # https://github.com/modilabs/pyxform/issues/83
        return unicode(value).replace(unichr(160), " ")