In [ 39 ]: cell = ws[‘B4’]
In [ 40 ]: cell.column
Out[40]: ‘B’
In [ 41 ]: cell.row
Out[41]: 4
In [ 42 ]: cell.value
Out[42]: 12
Similarly, you can access cell ranges as in Excel:
In [ 43 ]: ws[‘B1’:‘B4’]
Out[43]: ((<Cell oxl_sheet.B1>,),
(<Cell oxl_sheet.B2>,),
(<Cell oxl_sheet.B3>,),
(<Cell oxl_sheet.B4>,))
In [ 44 ]: for cell in ws[‘B1’:‘B4’]:
print cell[ 0 ].value
Out[44]: 9
10
11
12
There is also a range method to which you can provide the cell range in Excel syntax as a
string:
In [ 45 ]: ws.range(‘B1:C4’)
# same as ws[‘B1’:‘C4’]
Out[45]: ((<Cell oxl_sheet.B1>, <Cell oxl_sheet.C1>),
(<Cell oxl_sheet.B2>, <Cell oxl_sheet.C2>),
(<Cell oxl_sheet.B3>, <Cell oxl_sheet.C3>),
(<Cell oxl_sheet.B4>, <Cell oxl_sheet.C4>))
In [ 46 ]: for row in ws.range(‘B1:C4’):
for cell in row:
print cell.value,
print
Out[46]: 9 17
10 18
11 19
12 20
Refer to the library’s website for more details.
Using pandas for Reading and Writing
Chapter 7 shows how to interact with Excel spreadsheet files using the pandas library. Let
us use these approaches to read the data written with the xlwt library. We need a
DataFrame object for each sheet. With header=None, pandas does not interpret the first
data row as the header for the data set:
In [ 47 ]: df_1 = pd.read_excel(path + ‘workbook.xlsx’,
‘first_sheet’, header=None)
df_2 = pd.read_excel(path + ‘workbook.xlsx’,
‘second_sheet’, header=None)
To recover the column names/values of the spreadsheet file, let us generate a list with
capital letters as column names for the DataFrame objects:
In [ 48 ]: import string
columns = []
for c in range(data.shape[ 0 ]):
columns.append(string.uppercase[c])
columns
Out[48]: [‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘G’, ‘H’]
We pass this list as the new column names to the two objects:
