Basic Spreadsheet Interaction
Fundamental Python libraries to work with Excel spreadsheet files are xlrd and xlwt (cf.
http://www.python-excel.org). Although quite popular, a major drawback of xlwt is that it
can only write spreadsheet files compatible with Microsoft Excel 97 / 2000 /XP/ 2003 ,
OpenOffice.org Calc, and Gnumeric — i.e., those with the suffix .xls. Therefore, we also
use the libraries xlsxwriter and OpenPyxl, which generate spreadsheet files in the current
.xslx format. We’ll begin, then, with a few imports.
In [ 1 ]: import numpy as np
import pandas as pd
import xlrd, xlwt
import xlsxwriter
path = ‘data/’
Generating Workbooks (.xls)
We start by generating a workbook with two sheets.
[ 48 ]
First, the Workbook object wb. Note
that this is an in-memory version of the workbook only (so far):
In [ 2 ]: wb = xlwt.Workbook()
In [ 3 ]: wb
Out[3]: <xlwt.Workbook.Workbook at 0x7f7dcc49df10>
The second step is to add one or multiple sheets to the Workbook object:
In [ 4 ]: wb.add_sheet(‘first_sheet’, cell_overwrite_ok=True)
Out[4]: <xlwt.Worksheet.Worksheet at 0x7f7dac9dde90>
We now have one Worksheet object, which has index number 0:
In [ 5 ]: wb.get_active_sheet()
Out[5]: 0
To further work with the sheet, define an alias for it:
In [ 6 ]: ws_1 = wb.get_sheet( 0 )
ws_1
Out[6]: <xlwt.Worksheet.Worksheet at 0x7f7dac9dde90>
Of course, these two steps — instantiation and alias definition — can be combined into a
single step:
In [ 7 ]: ws_2 = wb.add_sheet(‘second_sheet’)
Both Worksheet objects are still empty. Therefore, let us generate a NumPy ndarray object
containing some numbers:
In [ 8 ]: data = np.arange( 1 , 65 ).reshape(( 8 , 8 ))
In [ 9 ]: data
Out[9]: array([[ 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 the write method and providing row and column information (with zero-based
indexing), data is easily written to a certain cell in a certain worksheet:
In [ 10 ]: ws_1.write( 0 , 0 , 100 )
# write 100 in cell “A1”
