Accessing worksheets using xlwt 'get_sheet'

Question

I would like to access worksheets of a spreadsheet. I've copied the main workbook to another workbook using xlutils.copy(). But don't know the right way to access worksheets using xlwt module. My sample code:

import xlrd
import xlwt
from xlutils.copy import copy

wb1 = xlrd.open_workbook('workbook1.xls', formatting_info=True)
wb2 = copy(master_wb)

worksheet_name = 'XYZ' (worksheet_name is a iterative parameter)

worksheet = wb2.get_sheet(worksheet_name)

Could someone please tell me what's the right command line to access the existing worksheets in a workbook using xlwt module? I know we can use 'add_sheet' method to add a worksheet in the existing workbook using xlwt module.

Any help, appreciated.

Answer 1

Well, here is my answer. Let me take it step-by-step. Considerting previous answers, xlrd is the right module to get the worksheets.

1. xlrd.Book object is returned by open_workbook.

   rb = open_workbook('sampleXLS.xls',formatting_info=True)

2. nsheets is an attribute integer which returns the total number of sheets in the workbook.

   numberOfSheets=rb.nsheets

3. Since you have copied this to a new workbook wb -> basically to write things, wb to modify excel wb = copy(rb)

4. there are two ways to get the sheet information,

   a. if you just want to read the sheets, use sheet=rb.sheet_by_index(sheetNumber)

   b. if you want to edit the sheet, use ws = wb.get_sheet(sheetNumber) (this is required in this context to the asked question)

you know how many number of sheets in excel workbook now and how to get them individually, putting all of them together,

Sample Code:

reference: http://www.simplistix.co.uk/presentations/python-excel.pdf

from xlrd import open_workbook
from xlutils.copy import copy
from xlwt import Workbook

rb = open_workbook('sampleXLS.xls',formatting_info=True)
numberOfSheets=rb.nsheets
wb = copy(rb)

for each in range(sheetsCount):
    sheet=rb.sheet_by_index(each)
    ws = wb.get_sheet(each)
    ## both prints will give you the same thing
    print sheet.name
    print ws.name

Answer 2

The sheets() method is curiously absent from the xlwt.Workbook class, so the other answer using that method will not work - only xlrd.book (for reading XLS files) has a sheets() method. Because all the class attributes are private, you have to do something like this:

def get_sheet_by_name(book, name):
    """Get a sheet by name from xlwt.Workbook, a strangely missing method.
    Returns None if no sheet with the given name is present.
    """
    # Note, we have to use exceptions for flow control because the
    # xlwt API is broken and gives us no other choice.
    try:
        for idx in itertools.count():
            sheet = book.get_sheet(idx)
            if sheet.name == name:
                return sheet
    except IndexError:
        return None

If you don't need it to return None for a non-existent sheet then just remove the try/except block. If you want to access multiple sheets by name repeatedly it would be more efficient to put them in a dictionary, like this:

sheets = {}
try:
    for idx in itertools.count():
        sheet = book.get_sheet(idx)
        sheets[sheet.name] = sheet
except IndexError:
        pass

Answer 3

You can do sheets = wb1.sheets() to get a list of sheet objects, then call .name on each to get their names. To find the index of your sheet, use

[s.name for s in sheets].index(sheetname)