Create an Excel spreadsheet¶

In [4]:
import openpyxl
In [7]:
def create_spreadsheet(filename, strings):
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.append(['String', 'Length'])
    for s in strings:
        sheet.append([s, len(s)])
    n = len(strings)
    f = '=SUM(B2:B' + str(n + 1) + ')'
    sheet.append(['Total', f])
    workbook.save(filename)
In [10]:
def check_spreadsheet(filename, strings):
    n = len(strings)
    lengths_range = 'B2:B' + str(n + 1)
    workbook = openpyxl.load_workbook(filename)
    sheet = workbook.active
    assert sheet.cell(1, 1).value == 'String'
    assert sheet.cell(1, 2).value == 'Length'
    for i, s in enumerate(strings):
        assert sheet.cell(i + 2, 1).value == s
        assert sheet.cell(i + 2, 2).value == len(s)
    assert sheet.cell(len(strings) + 2, 1).value == 'Total'
    assert sheet.cell(len(strings) + 2, 2).value == '=SUM(' + lengths_range + ')'
In [12]:
filename = '../data/misc/animals.xlsx'
animals = ['Aardvark', 'Buffalo', 'Chameleon', 'Diplodocus', 'Elephant', 'Flamingo']
create_spreadsheet(filename, animals)
check_spreadsheet(filename, animals)
In [ ]:
In [ ]: