Trying to replace a value in a sheet.row list that I have created from xlrd

I imported an excel spreadsheet and I am trying to clean up empty values with default values in all rows in my spreadsheet. I don't need to update the spreadsheet, I just need to set default values because I am using this information to insert into a local database. Whenever I try to do so, it never gets processed correctly. Here is my original iteration of the code:

for root,dirs,files in os.walk(path):
xlsfiles=['1128CNLOAD.xlsx']
#xlsfiles=[ _ for _ in files if _.endswith('CNLOAD.xlsx') ]
print (xlsfiles)
for xlsfile in xlsfiles:
    book=xlrd.open_workbook(os.path.join(root,xlsfile))
    sheet=book.sheet_by_index(0)
    cell=sheet.cell(1,1)
    print (sheet)

    sheet0 = book.sheet_by_index(0)
    #sheet1 = book.sheet_by_index(1)

    for rownum in range(sheet0.nrows):
        print sheet0.row_values(rownum)

    values=()

    print sheet0.nrows 

    for row_index in range(1, sheet.nrows):
        if sheet.cell(row_index,4).value == '':
            sheet.cell(row_index,4).value = 0.0
        print sheet.row(row_index)

The code spits returns no errors but nothing gets updated and the cells I am trying to update are still empty.

I also tried to change the loop to just do a value replace for the list which is seen below:

                for row_index in range(1, sheet.nrows):
                if sheet.row(1)[4] == "empty:''":
                    sheet.row(1)[4] = "number:0.0"

When I print after running this update, the list has not changed.

print(sheet.row(1))

[text:u'FRFHF', text:u' ', number:0.15, number:0.15, empty:'', empty:'', number:2.5, number:2.5, empty:'', empty:'']

Thank you for any help and let me know if you have any questions.

Solution

xlrd isn't really set up to edit the spreadsheet once you have it in memory. You can do it, but you have to use the undocumented internal implementation.

On my version (0.7.1), cells are stored internally to the sheet in a couple of different two-dimensional arrays - sheet._cell_types and sheet._cell_values are the main two. The types are defined by a set of constants in biffh.py, which the xlrd module imports. When you call cell, it constructs a new Cell instance using the value and type looked up for the given row/column pair. You could update those directly, or you could use the put_cell method.

So it looks like this would work:

if sheet.cell_type(1, 4) == xlrd.XL_CELL_EMPTY:
    sheet._cell_types[1][4] = xlrd.XL_CELL_NUMBER
    sheet._cell_values[1][4] = 0.0

Alternately:

if sheet.cell_type(1, 4) == xlrd.XL_CELL_EMPTY:
    sheet.put_cell(1, 4, xlrd.XL_CELL_NUMBER, 0.0, sheet.cell_xf_index(1, 4))

You may need to review the code to make sure this didn't change if you're on a different version.