How to read a null cell in excel as numeric zero value using xlrd in Python

Question

I'm importing values from different cells and appending them in a column from 50 excel's sheets (timeserie). I need to add them up when reading them, as I just need the aggregated values. But when the cell has not value it reads it as a string '' and is not possible to make the sum. How can I read null values as zero values in order to be able to sum them? I'm trying with .replace('',0) function but I get this error: TypeError: replace() argument 2 must be str, not int

workbook = xlrd.open_workbook("xxx.xls")
sheets = workbook.sheet_names()

tr_elect1 = []
for sheet_name in sheets:
    sh = workbook.sheet_by_name(sheet_name)
    tr_elect1.append(sh.cell(13, 1).value.replace('',0) + sh.cell(13, 2).value).replace('',0)

tr_elect1

Answer 1

You can define function which returns 0 if cell is empty, otherwise its actual value:

def cell_value(x, y):
    v = sh.cell(x, y).value
    if v:
        return v
    return 0


workbook = xlrd.open_workbook("xxx.xls")
sheets = workbook.sheet_names()

tr_elect1 = []
for sheet_name in sheets:
    sh = workbook.sheet_by_name(sheet_name)
    tr_elect1.append(cell_value(13, 1) + cell_value(13, 2))

Answer 2

Make sure that you're using Python 3.7 and xlrd version 1.2.0 because the xlrd module recently doesn't support all of the attributes of various functions.