Problems whith columns in Python xlrd

Question

I am a Python Beginner and I'm trying to make a script to sum all values in a column of a excel spereadsheet, and then write this value. I'm using xlrd and xlwt python packages. It works well for the first columm, but it doesn't works to the second untill the last column. Here is my code. Hope someone can help me.

import xlwt
import xlrd

workbook_page = xlrd.open_workbook('page_level.xlsx')
worksheet_page = workbook_page.sheet_by_name('Daily External Referrers')
num_rows = worksheet_page.nrows - 1
curr_row = 0`
num_cols = worksheet_page.ncols - 1`
curr_col = 1
soma = 0
while curr_col < num_cols:
    curr_col = curr_col + 1
    while curr_row < num_rows:
        curr_row = curr_row + 1
        row = (worksheet_page.cell_value(curr_row, curr_col))
        if isinstance (row, float):
            soma = soma + row
        else:
            pass
       worksheet_page.write(num_rows+1, curr_col, soma)
worksheet_page.save('page_level.xlsx')

Please copy and paste your code into the question rather than linking to a screenshot. We want this question to remain valuable in the future, and if the link breaks it may not be. — eddie_cat, Aug 08 '14 at 21:02
What specifically isn't working? Is there an error message? Are values not getting written? — wnnmaw, Aug 08 '14 at 21:13
It works preety well for the first column, but for the other columns the sum value is always 0. — Danilo Carvalho Silva, Aug 08 '14 at 21:18

Charles Clayton · Accepted Answer · 2014-08-08T23:06:29.580

I don't think you can directly edit the excel workbook you're reading with xlrd, you have to copy it to new workbook and edit that.

Just a prefence as well, but I would also use for loops rather than while loops just for ease.

I can't test it at the moment, but maybe try something more like this?

import xlwt, xlrd
from xlutils.copy import copy

read_workbook_page = xlrd.open_workbook('page_level.xlsx')

workbook_page = copy(read_workbook_page)
worksheet_page = workbook_page.sheet_by_name('Daily External Referrers')

num_rows = worksheet_page.nrows - 1
num_cols = worksheet_page.ncols - 1

for row in range(num_rows):
     column_sum = 0
     for column in range(1, num_cols):
          column_sum += worksheet_page.cell_value( row, column )
     worksheet_page.write(row, num_cols, column_sum )

worksheet_page.save('page_level.xlsx')

Problems whith columns in Python xlrd

1 Answers1