Xlrd Excel script convert "# N / A" to 42

Question

I have a script that retrieves data from an Excel spreadsheet using the xlrd module, specifically the row_values ​​() method. It seems to do just fine, except when "# N / A" was automatically generated by previous VLookups, in which case xlrd gets "# N / A" as an integer 42.

I looked at string formatting methods, but couldn't figure out how this is a problem.

Besides having a script that revealed the meaning of life (42), can anyone suggest what might be the problem?

Greetings

Note. There are no more Vlookups in the sheet, all values ​​were copied from other sheets, all simple values, no formulas.

Answer 1

xlrd docs ( , Ctrl-F #N/A), Excel .

, sheet.row_types(), , , sheet.row_types() . , , , , isinstance() , .

Answer 2

. .

def xls_proc_text(cell, value_proc=None, text_proc=None):
    """Converts the given cell to appropriate text."""
    """The proc will come in only when the given is value or text."""
    ttype = cell.ctype
    if ttype == xlrd.XL_CELL_EMPTY or ttype == xlrd.XL_CELL_TEXT or ttype == xlrd.XL_CELL_BLANK:
        if text_proc is None:
            return cell.value
        else:
            return text_proc(cell.value)
    if ttype == xlrd.XL_CELL_NUMBER or ttype == xlrd.XL_CELL_DATE or ttype == xlrd.XL_CELL_BOOLEAN:
        if value_proc is None:
            return str(cell.value)
        else:
            return str(value_proc(cell.value))
    if cell.ctype == xlrd.XL_CELL_ERROR:
        # Apply no proc on this.
        return xlrd.error_text_from_code[cell.value]

Answer 3

Andrew, , xlrd , :

0x00: '#NULL!',  # Intersection of two cell ranges is empty
0x07: '#DIV/0!', # Division by zero
0x0F: '#VALUE!', # Wrong type of operand
0x17: '#REF!',   # Illegal or deleted cell reference
0x1D: '#NAME?',  # Wrong function or range name
0x24: '#NUM!',   # Value range overflow
0x2A: '#N/A',    # Argument or function not available

0x2A hex dec 42. , - :

for rownum in xrange(sh.nrows):
    wr.writerow(['#N/A' if col.ctype == xlrd.XL_CELL_ERROR else col.value for col in sh.row(rownum)])

Answer 4

• I simplified the solution, thanks to everyone above. You can determine the error cell based on the cell type.
• The data we have is type # N / A
• value = 42 (instead of # N / A)
• ctype will be 5

A simple solution could be the cell "Detect error" and put "No" instead of 42

textType = sheet.cell(r,0).ctype #Get the type of the cell

        if textType == 5:
            text = None
        else:
            text = sheet.cell(r, 0).value

XLRD Documentation :

You can identify all other types based on the documentation below.

XL_CELL_ERROR 5 int represents internal Excel codes; see text representation in the attached dictionary error_text_from_code