xlrd get sheet by index code example

Example 1: xlrd select sheet by name

from __future__ import print_function
from os.path import join, dirname, abspath
import xlrd

fname = join(dirname(dirname(abspath(__file__))), 'test_data', 'Cad Data Mar 2014.xlsx')

# Open the workbook
xl_workbook = xlrd.open_workbook(fname)

# List sheet names, and pull a sheet by name
#
sheet_names = xl_workbook.sheet_names()
print('Sheet Names', sheet_names)

xl_sheet = xl_workbook.sheet_by_name(sheet_names[0])

# Or grab the first sheet by index 
#  (sheets are zero-indexed)
#
xl_sheet = xl_workbook.sheet_by_index(0)
print ('Sheet name: %s' % xl_sheet.name)

# Pull the first row by index
#  (rows/columns are also zero-indexed)
#
row = xl_sheet.row(0)  # 1st row

# Print 1st row values and types
#
from xlrd.sheet import ctype_text   

print('(Column #) type:value')
for idx, cell_obj in enumerate(row):
    cell_type_str = ctype_text.get(cell_obj.ctype, 'unknown type')
    print('(%s) %s %s' % (idx, cell_type_str, cell_obj.value))

# Print all values, iterating through rows and columns
#
num_cols = xl_sheet.ncols   # Number of columns
for row_idx in range(0, xl_sheet.nrows):    # Iterate through rows
    print ('-'*40)
    print ('Row: %s' % row_idx)   # Print row number
    for col_idx in range(0, num_cols):  # Iterate through columns
        cell_obj = xl_sheet.cell(row_idx, col_idx)  # Get cell object by row, col
        print ('Column: [%s] cell_obj: [%s]' % (col_idx, cell_obj))

Example 2: xlrd cell range

import xlrd
import re

def parse_cell(cell):
    r = re.compile("([a-zA-Z]+)([0-9]+)")
    m = r.match(cell)
    col = m.group(1).upper()
    number_row = int(m.group(2)) - 1

    ABC = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"]

    number_col = 0
    for char in col:
        number_col += ABC.index(char)

    return (number_row, number_col)


def get_cell_range_values(sheet, start_cell, end_cell):
    start_cell_parsed = parse_cell(start_cell)
    end_cell_parsed = parse_cell(end_cell)

    data = sheet.col_values(start_rowx=start_cell_parsed[0], colx=start_cell_parsed[1], end_rowx=end_cell_parsed[0] + 1)
    return data


file = r"C:\Users\Lisa\Desktop\Frank\export.XLSX"
book = xlrd.open_workbook(file)
sheet = book.sheet_by_index(0)
data = get_cell_range_values(sheet, "A3", "A130")