Python xlrd.xldate_as_tuple() Examples

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) 

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 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) 

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 

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 

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 

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), " ") 

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 

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 

def xls_to_text(document_path, event, context):
    import xlrd

    book = xlrd.open_workbook(document_path)
    lines = []
    for sheetno, sheet in enumerate(book.sheets()):
        lines.append(sheet.name)
        lines.append('-------------------------------------------')
        for row in sheet.get_rows():
            row_values = []
            for cell in row:
                if cell.ctype == xlrd.XL_CELL_DATE:
                    try: d = datetime(*xlrd.xldate_as_tuple(cell.value, book.datemode))
                    except ValueError: d = datetime(1970, 1, 1)
                    row_values.append(d.date() if d.time() == time(0, 0) else d)
                elif cell.ctype == xlrd.XL_CELL_BOOLEAN: row_values.append(bool(cell.value))
                else: row_values.append(cell.value)
            #end for
            lines.append(' | '.join(map(lambda s: str(s).strip(), row_values)))
        #end for
        lines.append('')  # empty line
    #end for

    return '\n'.join(lines).strip()
#end def


# def ppt_to_text(document_path, event, context):
#     cmdline = [os.path.join(BIN_DIR, 'catppt'), '-dutf-8', document_path]
#     text = _get_subprocess_output(cmdline, shell=False, env=dict(CATDOCRC_PATH=CATDOCRC_PATH))

#     return text.decode('utf-8', errors='ignore').strip()
# #end def 

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 

def _fix_value(cell):
        """Clean up an Excel value for CSV-like representation."""

        if cell.value is None or cell.ctype == xlrd.XL_CELL_EMPTY:
            return ''

        elif cell.ctype == xlrd.XL_CELL_NUMBER:
            # let numbers be integers if possible
            if float(cell.value).is_integer():
                return int(cell.value)
            else:
                return cell.value

        elif cell.ctype == xlrd.XL_CELL_DATE:
            # dates need to be formatted
            try:
                data = xlrd.xldate_as_tuple(cell.value, 0)
                return '{0[0]:04d}-{0[1]:02d}-{0[2]:02d}'.format(data)
            except:
                return cell.value

        elif cell.ctype == xlrd.XL_CELL_BOOLEAN:
            return int(cell.value)

        else: # XL_CELL_TEXT, or anything else
            return cell.value 

def _fix_value(cell):
        """Clean up an Excel value for CSV-like representation."""

        if cell.value is None or cell.ctype == xlrd.XL_CELL_EMPTY:
            return ''

        elif cell.ctype == xlrd.XL_CELL_NUMBER:
            # let numbers be integers if possible
            if float(cell.value).is_integer():
                return int(cell.value)
            else:
                return cell.value

        elif cell.ctype == xlrd.XL_CELL_DATE:
            # dates need to be formatted
            try:
                data = xlrd.xldate_as_tuple(cell.value, 0)
                return '{0[0]:04d}-{0[1]:02d}-{0[2]:02d}'.format(data)
            except:
                return cell.value;

        elif cell.ctype == xlrd.XL_CELL_BOOLEAN:
            return int(cell.value)

        else: # XL_CELL_TEXT, or anything else
            return cell.value 

def getRowData(row, columnNames):
	rowData = {}
	counter = 0

	for cell in row:
		# check if it is of date type print in iso format
		if cell.ctype==xlrd.XL_CELL_DATE:
			rowData[columnNames[counter].lower().replace(' ', '_')] = datetime.datetime(*xlrd.xldate_as_tuple(cell.value,0)).isoformat()
		else:
			rowData[columnNames[counter].lower().replace(' ', '_')] = cell.value
		counter +=1

	return rowData 

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 

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 

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 

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 

def parse_date_value(self, value):
        return xlrd.xldate_as_tuple(value, 0) 

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
    # 解析一行 

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 

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. 

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 

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)) 

def convert_excel_to_csv(input_excel_file,
                         output_csv_file_path=None, return_csv_file=False):

    """
        Takes the excel file path as input and converts
        into csv file and if we select return_csv_file as
        True returns csv file else
        returns csv file object

        Arguments:
            1. input_excel_file: It is a excel file path
               which is to be converted into csv file
            2. output_csv_file_path: If user gives the output csv path,
               then creating csv file at that path else creating a csv file
                in the directory from where he have given excel file.
            3. return_csv_file: If the user selects return_csv_file as True,
               returning the output csv file else returning the object.
        Returns:
            Returns the csv file path if user selects
            return_csv_file as True else returns the object.
    """
    try:
        if output_csv_file_path is None:
            if ".xlsx" in input_excel_file:
                ret_csv_file = input_excel_file.replace(".xlsx", ".csv")
            else:
                ret_csv_file = input_excel_file.replace(".xls", ".csv")
        else:
            ret_csv_file = output_csv_file_path

        wb = xlrd.open_workbook(input_excel_file)
        sh = wb.sheet_by_index(0)
        csv_file = open(ret_csv_file, 'wb+')
        wr = csv.writer(csv_file, quoting=csv.QUOTE_ALL)

        for rownum in xrange(sh.nrows):
            row_val = sh.row_values(rownum)
            for index, value in enumerate(row_val):
                if sh.cell(rownum, index).ctype == 3:
                    year, month, day, hour, minute, sec = xlrd.xldate_as_tuple(
                                                            value, wb.datemode)
                    date_format = "%02d/%02d/%04d" % (month, day, year)
                    row_val[index] = date_format
            wr.writerow(row_val)
        if return_csv_file:
            csv_file.close()
            csv_file = ret_csv_file
        else:
            csv_file = csv_file

    except Exception as exception:
        print_exception(exception)
        csv_file = None

    return csv_file