Out[28]: [number:4.0,
number:12.0,
number:20.0,
number:28.0,
number:36.0,
number:44.0,
number:52.0,
number:60.0]
The methods row_values and col_values only deliver the values contained in the
respective row or column:
In [ 29 ]: sheet_1.col_values( 3 , start_rowx= 3 , end_rowx= 7 )
Out[29]: [28.0, 29.0, 30.0, 31.0]
In [ 30 ]: sheet_1.row_values( 3 , start_colx= 3 , end_colx= 7 )
Out[30]: [28.0, 36.0, 44.0, 52.0]
To read out all the data in a Worksheet object, just iterate over all columns and rows that
contain data:
In [ 31 ]: for c in range(sheet_1.ncols):
for r in range(sheet_1.nrows):
print ‘%i’ % sheet_1.cell(r, c).value,
print
Out[31]: 1 2 3 4 5 6 7 8
9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24
25 26 27 28 29 30 31 32
33 34 35 36 37 38 39 40
41 42 43 44 45 46 47 48
49 50 51 52 53 54 55 56
57 58 59 60 61 62 63 64
Using OpenPyxl
There is yet another library to generate and read Excel spreadsheet files in .xlsx format
with Python: OpenPyxl. This library allows us to both create spreadsheet files and read
from them. In addition, while basic usage is similar to the other libraries, the interface is in
some cases a bit more Pythonic and might therefore be worth taking a look at. Import the
library as follows:
In [ 32 ]: import openpyxl as oxl
Let us proceed as before. First, generate a Workbook object:
In [ 33 ]: wb = oxl.Workbook()
Second, create a Worksheet object:
In [ 34 ]: ws = wb.create_sheet(index= 0 , title=‘oxl_sheet’)
Third, write the data to the worksheet:
In [ 35 ]: for c in range(data.shape[ 0 ]):
for r in range(data.shape[ 1 ]):
ws.cell(row=r, column=c).value = data[c, r]
# creates a Cell object and assigns a value
Fourth, close the file object:
In [ 36 ]: wb.save(path + ‘oxl_book.xlsx’)
With OpenPyxl, you can also read workbooks:
In [ 37 ]: wb = oxl.load_workbook(path + ‘oxl_book.xlsx’)
Now, single cells are easily accessed via their cell names:
In [ 38 ]: ws = wb.get_active_sheet()
