799 lines
24 KiB
Plaintext
799 lines
24 KiB
Plaintext
{
|
||
"cells": [
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"# CDS1001 Tutorial 9"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### Input your name and student ID in the cell below (<font color='red'>if a cell is not in edit mode, double click it</font>):"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Your name: \n",
|
||
"\n",
|
||
"Your student ID:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### Objectives:\n",
|
||
"- Understand why openpyxl is used in coding \n",
|
||
"- Be able to understand and apply the use of openpyxl to create, read, change, and write excel files automatically"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"## **Instructions for the report**:\n",
|
||
"* Follow Section 1 and Section 2 of the tutorial instruction to launch Python IDLE through Anaconda Navigation.\n",
|
||
"* Refer to Section 2.2 of the tutorial instruction to open tutorial 9 report\n",
|
||
"* Complete Parts 1-3 led by the lecturer\n",
|
||
"* Complete Part 4 independently\n",
|
||
"* Follow Section 3 of the tutorial instruction to save the report and zip the report folder. The zip file is named as CDS1001T9Report{your student_id}.zip (e.g., if student_id is 1234567, then the zip file's name is CDS1001T9Report1234567.zip). <font color='red'>The zip file needs to include the following files:\n",
|
||
" - an .ipynb file of this tutorial report \n",
|
||
" - image files of flowcharts or screenshots used in this tutorial report </font> \n",
|
||
"* Submit the zip file of the report folder to the Moodle. The submission due date is **<font color='red'>21 Nov 2023, 11:55PM</font>**"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"### Part 1 About openpyxl module"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### 1.1. What is the openpyxl module for? (5 points)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"<font color='red'>Edit this cell to answer the question above: </font>"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"### Part 2 Reading Excel Documents"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### 2.1. Write and execute codes for the following questions 1-8. (24 points)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 1. How would you obtain a workbook object for an Excel file example.xlsx and store it in a variable ``wb``?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 16,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 2. How would you print the number of sheets of ``wb``?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 52,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 3. How would you retrieve the Worksheet object ``sheet2`` for a sheet named 'Sheet2' and print its number of rows?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 49,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 4. How would you retrieve the Worksheet object ``active_sheet`` for the workbook’s active sheet and print its title?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 48,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 5. How would you print the value in the cell C5 of the active_sheet?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 47,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 6. How would you print the value of the cell at 2nd row and 3rd column?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 50,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 7. How would you print values of the active_sheet by rows?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 51,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 8. How can you retrieve a tuple of all the Cell objects from A1 to C1, and print their values using a for loop?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 53,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### 2.2. Reading Data from a Spreadsheet"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Say you have a spreadsheet of data from the 2010 US Census and you have the boring task of going through its thousands of rows to count ```both the total population and the number of census tracts for each county```. (A census tract is simply a geographic area defined for the purposes of the census.) \n",
|
||
"\n",
|
||
"Each row represents a single census tract. We’ll name the spreadsheet file censuspopdata.xlsx, which is available in the folder of the tutorial 9 report. Its contents look like the following figure:\n",
|
||
"<img src='fig1.jpg'>\n",
|
||
"\n",
|
||
"Even though Excel can calculate the sum of multiple selected cells, you'd still have to select the cells for each of the 3,000-plus counties. Even if it takes just a few seconds to calculate a county's population by hand, this would take hours to do for the whole spreadsheet.\n",
|
||
"\n",
|
||
"You are asked to write a python code that can read from the census spreadsheet file and calculate statistics for each county in a matter of seconds."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### Task 1: What is the algorithm to tackle this automation task? What are the data structures needed? How are you going to write the code? (3 points)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"<font color='red'>Edit this cell to answer the question above. </font>"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### Task 2: Make a small sized excel file for the testing purpose, by copying a few lines from the censuspopdata.xlsx. (2 points)\n",
|
||
"\n",
|
||
"Rename the small sized excel file as test-2-2.xlsx. Make a screenshot of test-2-2.xlsx, insert the screenshot in the cell below."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"<font color='red'>Edit this cell to insert the screenshot: </font>"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### Task 3: Follow the steps below to write the code:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"##### Step 1: Read the Spreadsheet Data (5 points)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"There is just one sheet in the censuspopdata.xlsx spreadsheet, named 'Population by Census Tract', and each row holds the data for a single census tract. \n",
|
||
"\n",
|
||
"The columns are the tract number (A), the state abbreviation (B), the county name (C), and the population of the tract (D).\n",
|
||
"\n",
|
||
"In the code block below, do the followings:\n",
|
||
"- (1) Import openpyxl module;\n",
|
||
"- (2) Read file name of the excel file (e.g., censuspopdata.xlsx, test-2-2.xlsx)\n",
|
||
"- (3) Create a workbook object for the workbook of the excel file, and store it in variable ``wb_census``\n",
|
||
"- (4) Create a sheet object for the sheet 'Population by Census Tract' of the workbook loaded, and it in variable ``sheet_census``\n",
|
||
"- (5) print the number of rows in the sheet\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 44,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for Step 1\n",
|
||
"#(1) Import openpyxl module \n",
|
||
"\n",
|
||
"#(2) Read the file name of the excel file from the input (e.g., censuspopdata.xlsx, test-2-2.xlsx),\n",
|
||
"#and store it in a string variable filename\n",
|
||
"\n",
|
||
"#(3) Create a workbook object wb_census for the workbook of the excel file with the name in filename\n",
|
||
"\n",
|
||
"#(4) Create a worksheet object sheet for the active sheet of the workbook\n",
|
||
"\n",
|
||
"#(5) print the number of rows in the sheet\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"##### Step 2: Populate the Data Structure (5 points)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"We now need to create and use a variable named countyData to calculate the statistics of the data. The data structure stored in countyData will be a dictionary with state abbreviations as its keys. Each state abbreviation will map to another dictionary, whose keys are strings of the county names in that state. Each county name will in turn map to a dictionary with just two keys, 'tracts' and 'pop'. These keys map to the number of census tracts and population for the county. For example, the dictionary will look similar to this:\n",
|
||
"\n",
|
||
" {'AK': {'Aleutians East': {'pop': 3141, 'tracts': 1},\n",
|
||
" 'Aleutians West': {'pop': 5561, 'tracts': 2},\n",
|
||
" 'Anchorage': {'pop': 291826, 'tracts': 55},\n",
|
||
" 'Bethel': {'pop': 17013, 'tracts': 3},\n",
|
||
" 'Bristol Bay': {'pop': 997, 'tracts': 1}, \n",
|
||
" ... \n",
|
||
" \n",
|
||
"The countyData dictionary’s keys will look like this:\n",
|
||
" \n",
|
||
" countyData[state abbrev][county]['tracts']\n",
|
||
" countyData[state abbrev][county]['pop']\n",
|
||
" \n",
|
||
"In the code below, do the followings: \n",
|
||
"- (1) define the dictionary variable countyData with an empty initial value. \n",
|
||
"- (2) write a for loop to traverse the row id from 2, 3, 4, ..., to the last row number (included).\n",
|
||
"- (3) during the iteration of the loop: extract data of state, county, and pop for each row.\n",
|
||
"- (4) update countyData to compute the total number of census tracts, and the total value of populations.\n",
|
||
"- (5) print the list of counties in state AL"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 45,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for Step 2\n",
|
||
"\n",
|
||
"#(1) define the dictionary variable countyData with an empty initial value. \n",
|
||
"\n",
|
||
"#(2) write a for loop to traverse the row id from 2, 3, 4, ..., to the last row number (included) of the sheet.\n",
|
||
"#(3) during the iteration of the loop: extract data of state, county, and pop for each row.\n",
|
||
"#(4) update countyData to compute the total number of census tracts, and the total value of populations.\n",
|
||
"\n",
|
||
"#(5) print the list of counties in state AL\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"##### Step 3: Print Results (2 points)\n",
|
||
"\n",
|
||
"In the code below, write codes to (1) print the total populations of county Autauga of state AL, and (2) print the total number of census tracts of county Union of state AR"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 46,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for Step 3\n",
|
||
"\n",
|
||
"#(1) print the total populations of county Anchorage of state AK:\n",
|
||
"\n",
|
||
"#(2) print the total number of census tracts of county Union of state AR\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### Task 4: Test the above codes by using the small sized excel file first, and then the large sized excel file. (2 points)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"<font color='red'>Edit this cell to illustrate the testing results of Step 3 for the large sized excel file, and paste the screenshot of the results. </font>"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"### Part 3 Writing Excel Documents"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### 3.1. Write and execute codes for the following questions 1-8, with varibles ``wb`` and ``sheet`` as defined in Task 2.2 for the Excel file example.xlsx . (12 points)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 1. How would you change the title of the sheet to 'Results'?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 57,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 2. How would you create a new sheet with a title 'New Results' for the workbook?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 58,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 3. How would you set the value of cell 'A2' in the sheet 'New Results' to 'Forecasting'"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 59,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 4. How would you save the workbook to the filename new_results.xlsx?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 60,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Write your code here?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"<font color='red'>Edit this cell to paste the screenshot of the sheet 'New Results' of new_results.xlsx. </font>"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### 3.2. Updating a Spreadsheet \n",
|
||
"\n",
|
||
"In this problem, you’ll write a program to update cells in a spreadsheet of produce sales. Your program will look through the spreadsheet, find specific kinds of produce, and update their prices. The spreadsheet is produceSales.xlsx located in the folder of tutorial 9 report.\n",
|
||
"\n",
|
||
"The figure below shows what the spreadsheet looks like.\n",
|
||
"<img src='fig2.jpg'></img>\n",
|
||
"\n",
|
||
"Each row represents an individual sale. The columns are the type of produce sold (A), the cost per pound of that produce (B), the number of pounds sold (C), and the total revenue from the sale (D). The TOTAL column is set to the Excel formula =ROUND(B3\\*C3, 2), which multiplies the cost per pound by the number of pounds sold and rounds the result to the nearest cent. With this formula, the cells in the TOTAL column will automatically update themselves if there is a change in column B or C.\n",
|
||
"\n",
|
||
"Now imagine that the prices of garlic, celery, and lemons were entered incorrectly, leaving you with the task to write a code to go through thousands of rows in this spreadsheet to update the cost per pound for any garlic, celery, and lemon rows.\n",
|
||
"\n",
|
||
"The prices that you need to update are as follows:\n",
|
||
"\n",
|
||
" Celery 1.19\n",
|
||
" Garlic 3.07\n",
|
||
" Lemon 1.27\n",
|
||
" \n",
|
||
"You cannot do a simple find-and-replace for the price, because there might be other items with the same price that you do not want to mistakenly \"correct\". For thousands of rows, this would take hours to do by hand. But you can write a program that can accomplish this in seconds."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### Task 1: What is the algorithm to tackle this automation task? What are the data structures needed? How are you going to write the code? (3 points)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"<font color='red'>Edit this cell to answer the question above. </font>"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### Task 2: Make a small sized excel file for the testing purpose, by copying a few lines from the updateProduceSales.xlsx. (2 points)\n",
|
||
"\n",
|
||
"Rename the small sized excel file as test-3-2.xlsx. Make a screenshot of test-3-2.xlsx, insert the screenshot in the cell below."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"<font color='red'>Edit this cell to insert the screenshot: </font>"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### Task 3: Follow the steps below to write the code:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"##### Step 1: Read a Workbook and Set Up a Data Structure with the Update Information (6 points)\n",
|
||
"\n",
|
||
"In the code block below, do the followings:\n",
|
||
"- (1) Import openpyxl module;\n",
|
||
"- (2) Read file name of the excel file (e.g., produceSales.xlsx, test-3-2.xlsx)\n",
|
||
"- (3) Create a workbook object stored in a variable ``wb_update`` by loading the workbook from the excel file;\n",
|
||
"- (4) Create a sheet object stored in a variable ``sheet_update`` by assigning it to the active sheet of the workbook loaded;\n",
|
||
"- (5) Create a dictionary object stored in a variable PRICE_UPDATES, which maps product type to updated prices\n",
|
||
"- (6) print the number of rows and the number of columns in the sheet"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 61,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for Step 1:\n",
|
||
"# (1) Import openpyxl module;\n",
|
||
"\n",
|
||
"# (2) Read file name of the excel file (e.g., produceSales.xlsx, test-3-2.xlsx)\n",
|
||
"\n",
|
||
"# (3) Create a workbook object stored in a variable ``wb_update`` by loading the workbook from the excel file;\n",
|
||
"\n",
|
||
"# (4) Create a sheet object stored in a variable ``sheet_update`` by assigning it to the active sheet of the workbook loaded;\n",
|
||
"\n",
|
||
"# (5) Create a dictionary object stored in a variable PRICE_UPDATES, which maps product type to updated prices\n",
|
||
"\n",
|
||
"# (6) print the number of rows and the number of columns in the sheet\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"##### Step 2: Check All Rows and Update Incorrect Prices (2 points)\n",
|
||
"\n",
|
||
"The next part of the program will loop through all the rows in the spreadsheet:\n",
|
||
"\n",
|
||
"(1) write a for loop to traverse the row id from 2, 3, 4, ..., to the last row number (included).\n",
|
||
"(2) during each iteration of the loop, update the cost per unit if needed, according to PRICE_UPDATES.\n"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 62,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for Step 2:\n",
|
||
"#(1) write a for loop to traverse the row id from 2, 3, 4, ..., to the last row number (included).\n",
|
||
"#(2) during each iteration of the loop, update the cost per unit if needed, according to PRICE_UPDATES."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"##### Step 3: Write the updated Workbook to a new Excel File (1 point)\n",
|
||
"Write the updated workbook to a new excel file named \"updatedProduceSales.xlsx\""
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 20,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for Step 3:\n",
|
||
"#Write the updated workbook to a new excel file named \"updatedProduceSales.xlsx\""
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### Task 4: Test the above codes by using the small sized excel file first, and then the large sized excel file. (2 points)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"<font color='red'>Edit this cell to illustrate the testing results of Step 3 for the large sized excel file, and paste the screenshot of the results. </font>"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"### Part 4 Manipulating Excel Documents"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"#### 4.1. Write and execute codes for the following questions 1-8: (24 points)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 1. Write a code below to obtain a workbook object for \"sales.xlsx\" and store it in a variable ``wb_sales``, obtain a worksheet object for Sheet 'Sheet1' of the workbook and store it in a variable ``sheet_sales``, print the number of rows and columns of the sheet."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 64,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for question above:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 2. How do you set a formula in cell B14 to sum up the values of cells from B2 to B13?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 63,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for question above:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 3. How would you set the height of the first row to 50, and the width of the first column to 20?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 65,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for question above:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 4. How would you change the font size and the text color of cell 'A1' to 20 and to red, respectively?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 24,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for question above:\n",
|
||
"#sheet_sales.row_dimensions[1].font = openpyxl.styles.Font(size = 20, color='FF0000')"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 5. How would you freeze the first row"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 25,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for question above:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 6. How to create a bar chart for the 12-month sales?"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 67,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for question above:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 7. How to save the updated workbook to a new excel file named 'new_sales.xlsx':"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 27,
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"#Edit this cell for question above:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"Question 8. Open new_sales.xlsx, take a screenshot of the sheet, and insert it in the excel below:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {},
|
||
"source": [
|
||
"<font color='red'>Edit this cell to insert a screenshot of 'new_sales.xlsx' </font>"
|
||
]
|
||
}
|
||
],
|
||
"metadata": {
|
||
"kernelspec": {
|
||
"display_name": "Python 3 (ipykernel)",
|
||
"language": "python",
|
||
"name": "python3"
|
||
},
|
||
"language_info": {
|
||
"codemirror_mode": {
|
||
"name": "ipython",
|
||
"version": 3
|
||
},
|
||
"file_extension": ".py",
|
||
"mimetype": "text/x-python",
|
||
"name": "python",
|
||
"nbconvert_exporter": "python",
|
||
"pygments_lexer": "ipython3",
|
||
"version": "3.9.7"
|
||
}
|
||
},
|
||
"nbformat": 4,
|
||
"nbformat_minor": 4
|
||
}
|