AttributeError while trying to sort, loop and write to worksheets using python

Question

I am currently have a workbook with 5 worksheets. there is data in columns a - e, while each worksheet may have data in the same columns, each worksheet has different different amount rows though. I am entering a formula in column f that will go from cell F4 to whatever the last row is in that worksheet. I am able to loop through sheets and am able to create a formula that goes from F4 to the last row, however I am unable get the two to work together.

Code

import os
import os.path
import urllib
import xlrd
import xlwt
from xlutils.copy import copy

fname = "test.xls"
destination = 'C:\Users\Raj Saha\Google Drive\Python\Excel-Integration'

rb = xlrd.open_workbook(fname,formatting_info=True) #original workbook
r_sheet = rb.sheet_by_index(1) #origianl worksheet
style = xlwt.easyxf('font: bold 1')
wb = copy(rb) #virtual workbook
#sheet = wb.get_sheet(1)

shxrange = range(rb.nsheets)
sh = rb.sheet_by_name("Data 1")


#print "total rows: %d, rows less headers: %d" % (nrows, rows)

for sheet in shxrange:
    nrows = sheet.nrows
    rows = nrows - 4
    for i in range(rows):
        sheet.write(i+3, 5, xlwt.Formula("B%d-C%d" % (i+4, i+4)))
        sheet.write(2,5,"CL1-CL2",style)

wb.save(fname)

I get the following error message:

File "C:/formulas_multi_sheets.py", line 31, in nrows = sheet.nrows

AttributeError: 'int' object has no attribute 'nrows'

I assume the error in line 31 would apply to line 32. I am using python 2.7.

`shxrange = range(rb.nsheets)` would return you a list of integers and not a `sheet` class object — ZdaR, Feb 10 '17 at 08:59

score 0 · Answer 1 · answered Feb 10 '17 at 09:17

0

Here, shxrange gives you integers. What you need is sheet class object. For getting object of every sheet of your workbook,

for sheet_no in shxrange:
    sheet=rb.sheet_by_index(sheet_no)
    nrows = sheet.nrows
    rows = nrows - 4
    for i in range(rows):
        sheet.write(i+3, 5, xlwt.Formula("B%d-C%d" % (i+4, i+4)))
        sheet.write(2,5,"CL1-CL2",style)

answered Feb 10 '17 at 09:17

Chanda Korat

2,453
2
19
23

Thank you. I just tried your answer but am now getting a different AttributeError, `AttributeError: 'Sheet' object has no attribute 'write'` for line 35, `sheet.write(i+3, 5, xlwt.Formula("B%d-C%d" % (i+4, i+4)))`. – Rsaha Feb 10 '17 at 09:24
Because your workbook is in read mode. You have to open it in write mode. – Chanda Korat Feb 10 '17 at 09:41
You can refer this [link](http://stackoverflow.com/questions/2725852/writing-to-existing-workbook-using-xlwt#answer-2726298) – Chanda Korat Feb 10 '17 at 09:50
Thank you. I was using that link as a template. As you can see in my code i do have a virtual workbook, using the `copy` function, and a readable workbook, using `xlrd`. Which is causing the problem. Since i can only read from `rb` but only write to `wb`. – Rsaha Feb 10 '17 at 17:06
Yaa right, so i think you should try like `sheet=wb.sheet_by_index(sheet_no)` – Chanda Korat Feb 11 '17 at 05:48
Thank you again for taking another look at this. I have tried every combination possible. Nothing worked, which is why i decided to go with the methodology below, using win32com. – Rsaha Feb 11 '17 at 22:41

Rsaha · Answer 2 · 2017-02-10T19:07:27.013

Due to the limitations of xlrd and wlwt, i turned to win32com. Here is the following solution that i came up with. I am able to sort data in multiple worksheets and add formulas to multiple worksheets.

Code

import win32com.client as win32
import types
from win32com.client import constants as c

fname = ('C:/Users/test.xls')

xlApp = win32.gencache.EnsureDispatch('Excel.Application')
xlApp.Visible = True

xlDescending = 2
xlSortColumns = 1

sh_range = range(2,6)

wb = xlApp.Workbooks.Open(fname)

for sh in wb.Sheets:
    rangeFrom = 'F4'
    column = 'F'
    lastRow = sh.UsedRange.Rows.Count
    rangeTo = rangeFrom + ':' + column + str(lastRow)
    print "%r" % rangeTo
    xlApp.Worksheets(sh.Name).Activate()
    ws = xlApp.ActiveSheet
    ws.Range('f3').Value = "CL1-CL2"
    ws.Range('f4').Formula = "=B4-C4"
    ws.Range('f4:f4').Select()
    xlApp.Selection.AutoFill(ws.Range(rangeTo),win32.constants.xlFillDefault)    

for i in sh_range:
    xlApp.Sheets(i).Range("A4:E50000").Sort(Key1=xlApp.Sheets(i).Range("A4"), Order1=xlDescending, Orientation=xlSortColumns)

wb.Save()
wb.Close()

AttributeError while trying to sort, loop and write to worksheets using python

2 Answers2