|
Laboratory Excel: Exercises relating to spreadsheets
Prerequisites: for-loops, functions, tuples, openpyxl
If you don't know the openpyxl package yet, we suggest you read the
beginning of https://automatetheboringstuff.com/chapter12/ while you
attempt the exercises below.
Example
To read and print the contents of cell A1 from Sheet1 of the
file
read-sample.xlsx, we can use the program
read-sample.py.
A corresponding program write-sample.py
demonstrates how to creat a file like
write-sample.xlsx.
The following exercises demonstrate the following use cases of
manipulating spreadsheets from Python
- creating a spreadsheet automatically from Python code
- reading a spreadsheet into Python
- carrying out numerical operations on spreadsheet data in Python
- manipulating a spreadsheet in Python (transpose)
- carrying out data checks on a spreadsheet in Python
Create a file labexcel.py with the following functions:
Write a function readcell(filename, sheetname, cellid) which
accepts an FILENAME of the xlsx file, the SHEETNAME, and the
CELLID, and which returns the content of the cell with id CELLID.
Example:
>>> print(readcell('read-sample.xlsx', 'Sheet1', 'A1'))
This is the content of cell A1.
Write a function sumcol(filename, sheetname, column, startrow,
endrow) which computes the sum of the numbers in column COLUMN
starting from row STARTROW up to and including ENDROW.
Example:
>>> print(sumcol('write-sample.xlsx', 'MySheet', 'D', 1, 8))
204
Write a function writecell(filename, cellid, data) that writes
the DATA value into the cell with id CELLID within an xlsx
file with name FILENAME into sheet called Sheet. The function
should return None.
Example:
>>> print(writecell('my-data.xlsx', 'A2', 'this is the data')
None
>>> print(writecell('my-data.xlsx', 'A1', 42)
None
Write a function transpose(inputfilename, outputfilename) that
reads an xlsx file with name INPUTFILENAME, transposes the data in
the spreadsheet, and writes the transposed data into the xlsx file
with name OUTPUTFILENAME. With transpose, we mean that the
contents of cell B1 will be written into the cell A2, and the
contents of cell C1 will be written into the cell A3, ond the
contents of cell B5 will be written into the cell E2, etc.
The function should return None.
You can assume there is only one worksheet in the workbook, and
that it is 'active', i.e. you can use:
wb = openpyxl.load_workbook(filename, read_only=True)
s = wb.active
to get a handle s to the worksheet that should be transposed.
Write a function check_resort_data1(filename) that opens an
xlsx file with name FILENAME, and checks the correctness of the
total price of the accommodation based on the number of nights.
The spreadsheet contains data from holiday makers in some resort in
Spain. The correct price is EUR 91 per night per party. Study the
sample file resort.xlsx to understand the structure.
The return value of the function should be a list, that contains
one tuple for every row that contains incorrect data. The tuple
should contain 4 elements: first the index of the row that contains
the error. Then the name of the party as the second element. As the
third element the correct price. The fourth element should contain
the difference between the charged and the correct price, i.e. the
correct price subtracted from the actually charged price.
Example (only the first tuple of the output is shown):
>>> check_resort_data1('resort.xlsx')
[(50, 'Hernandez', 637, -21), ...]
Not for submission: Can you identify the type of error? Discuss
with your peers or demonstrators if you like.
Write a second function check_resort_data2(filename) that
checks whether the number of nights has been computed correctly,
given the start and end date.
The function should return a list of tuples, containing one tuple
for every row that contains an error. As for the
check_resort_data1 function, the first two items in the tuple
should be the row index and the name of the party. The fourth item
should be the correct number of nights, the fifth item should be
the difference between the numbers of nights given in the
spreadsheet, and the correct number of nights based on the arrival
and departure dates.
While it is not good software engineering practice, for the purpose
of this question, you are allowed to copy code from
check_resort_data1. However, you can (and are encouraged to)
create an additional function that both check_resort_data1 and
check_resort_data2 use that combines common code elements of
the two functions.
Example (only the first tuple of the output is shown):
>>> check_resort_data2('resort.xlsx')
[(15, 'Ryan', 7, 1), ...]
Submit your file with subject line lab excel to get feedback and
formative marks.
|