xlrd Excel script converting "#N/A" to 42

I found this useful. Thanks to John's initial help.

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]

xlrd docs on the web (or on your computer; open the docs in your browser and do Ctrl-F #N/A) give you the conversion table from Excel internal codes to text.

It might be of use to look at the sheet.row_types() method, and the Cell class docs which give you the cross-reference between type numbers as returned by sheet.row_types() and others. Note that it is generally more efficient to test for those type numbers than it is to use isinstance() on the values, AND there is no ambiguity using type numbers.

As Andrew listed if you have error in the cell, xlrd writes the code of the error, which you can see here:

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

Converting code 0x2A from hex to dec you can get that 42 value. To avoid that you can use something like this in your code:

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