{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# 7. Exercise Solutions" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Exercise 1 (Setup)\n", "\n", "After running `xlwings quickstart file_name`, the generated file has the following contents: " ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import xlwings as xw\n", "\n", "@xw.sub # only required if you want to import it or run it via UDF Server\n", "def main():\n", " wb = xw.Book.caller()\n", " wb.sheets[0].range(\"A1\").value = \"Hello xlwings!\"\n", "\n", "\n", "@xw.func\n", "def hello(name):\n", " return \"hello {0}\".format(name)\n", "\n", "\n", "if __name__ == \"__main__\":\n", " xw.books.active.set_mock_caller()\n", " main()\n", " " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Exercise 2 and 3\n", "\n", "After solving Exercise 2 and 3, the Python file could look like this:" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "# Standard library imports\n", "import os\n", "\n", "# Third party imports (dependencies)\n", "import xlwings as xw\n", "import pandas as pd\n", "import matplotlib.pyplot as plt\n", "\n", "\n", "@xw.sub # only required if you want to import it or run it via UDF Server\n", "def main():\n", " wb = xw.Book.caller()\n", " wb.sheets[0].range(\"A1\").value = \"Hello xlwings!\"\n", "\n", "\n", "# UDF function that was generated here has been deleted (not used)\n", "\n", "\n", "# -----------------------------------------------------------------------------\n", "# EXERCISE 3\n", "# ----------\n", "def create_daterange_dataframe(filename, start_date, end_date):\n", " '''Helper function'''\n", "\n", " # Get the 'correct' current working dir (where the .py and .xlsm files are)\n", " cwd = os.path.dirname(os.path.abspath(__file__))\n", "\n", " # Combine into a full path to the dataset file\n", " full_filename = f'{cwd}\\\\{filename}'\n", "\n", " df_raw = pd.read_csv(full_filename)\n", "\n", " # Change the \"raw\" dates into datetime objects so they can be filtered\n", " df_raw['Date'] = pd.to_datetime(df_raw['Date'])\n", " \n", " # Set the date as the index of the dataframe\n", " df = df_raw.set_index('Date')\n", "\n", " # Filter dataframe for input date range and return it\n", " return df[start_date:end_date]\n", "\n", "\n", "@xw.sub\n", "def update_weather_plot():\n", " '''Macro function to interact with Excel'''\n", "\n", " # Mock the calling Excel file\n", " xw.books.active.set_mock_caller()\n", " \n", " # Get the workbook that is calling the macro and set sheet\n", " wb = xw.Book.caller()\n", " sht3 = wb.sheets['Sheet3']\n", "\n", " # Get input values from Excel\n", " start_date = sht3.range('C21').value\n", " end_date = sht3.range('C22').value\n", " parameter = sht3.range('C23').value\n", "\n", " fig, ax = plt.subplots()\n", "\n", " filename = 'Sydney_weather.csv'\n", "\n", " # Create a dataframe from the dataset and filter it based on inputs\n", " df = create_daterange_dataframe(filename, start_date, end_date)\n", "\n", " # Use the Pandas interface to matplotlib for plotting\n", " df.plot(y=parameter, ax=ax)\n", "\n", " # Insert figure into sheet\n", " sht3.pictures.add(fig, name='MyPlot', update=True)\n", " \n", "# -----------------------------------------------------------------------------\n", "\n", " \n", "if __name__ == \"__main__\":\n", " xw.books.active.set_mock_caller()\n", " main()\n", "\n", " # -------------------------------------------------------------------------\n", " # EXERCISE 2\n", " # ----------\n", " import numpy as np\n", "\n", " # Establish connection to the workbook\n", " wb = xw.Book.caller()\n", " \n", " sht2 = wb.sheets['Sheet2']\n", " \n", " sht2.range('A2').options(transpose=True).value = np.arange(-50, 51, 5)\n", "\n", " # -------------------------------------------------------------------------\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Look of resulting Excel interface\n", "\n", "The look of the resulting Excel interface could be like shown below:\n", "\n", "\"drawing\"\n", "\n", "---" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Demonstration of `pd.describe`" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
MinTempMaxTempRainfallEvaporationSunshineWindGustSpeedWindSpeed9amWindSpeed3pmHumidity9amHumidity3pmPressure9amPressure3pmCloud9amCloud3pmTemp9amTemp3pmRISK_MM
count3334.0000003335.0000003331.0000003286.0000003321.0000002301.0000003311.0000003312.0000003323.0000003324.0000003317.0000003318.0000002771.0000002776.0000003333.0000003333.0000003337.000000
mean14.86505723.0023393.3302315.1874327.17937441.76140815.07067419.33544768.22901054.6991581018.3461561016.0187744.1815234.21866017.81974221.5333333.319868
std4.5536414.4946389.8951722.7774073.81088613.0075237.0797247.48255415.08535516.2935307.0215717.0322112.7495782.6418854.8971774.3037379.885066
min4.30000011.7000000.0000000.0000000.00000017.0000000.0000000.00000019.00000010.000000986.700000989.8000000.0000000.0000006.40000010.2000000.000000
25%11.00000019.6000000.0000003.2000004.30000031.0000009.00000015.00000058.00000044.0000001013.7000001011.3000001.0000001.00000013.80000018.4000000.000000
50%14.90000022.8000000.0000004.8000008.30000041.00000015.00000019.00000069.00000056.0000001018.6000001016.3000005.0000004.00000018.20000021.3000000.000000
75%18.70000026.0000001.4000007.00000010.20000050.00000020.00000024.00000080.00000065.0000001023.1000001020.8000007.0000007.00000021.70000024.5000001.400000
max27.60000045.800000119.40000018.40000013.60000096.00000054.00000057.000000100.00000099.0000001039.0000001036.7000009.0000008.00000036.50000044.700000119.400000
\n", "
" ], "text/plain": [ " MinTemp MaxTemp Rainfall Evaporation Sunshine \\\n", "count 3334.000000 3335.000000 3331.000000 3286.000000 3321.000000 \n", "mean 14.865057 23.002339 3.330231 5.187432 7.179374 \n", "std 4.553641 4.494638 9.895172 2.777407 3.810886 \n", "min 4.300000 11.700000 0.000000 0.000000 0.000000 \n", "25% 11.000000 19.600000 0.000000 3.200000 4.300000 \n", "50% 14.900000 22.800000 0.000000 4.800000 8.300000 \n", "75% 18.700000 26.000000 1.400000 7.000000 10.200000 \n", "max 27.600000 45.800000 119.400000 18.400000 13.600000 \n", "\n", " WindGustSpeed WindSpeed9am WindSpeed3pm Humidity9am Humidity3pm \\\n", "count 2301.000000 3311.000000 3312.000000 3323.000000 3324.000000 \n", "mean 41.761408 15.070674 19.335447 68.229010 54.699158 \n", "std 13.007523 7.079724 7.482554 15.085355 16.293530 \n", "min 17.000000 0.000000 0.000000 19.000000 10.000000 \n", "25% 31.000000 9.000000 15.000000 58.000000 44.000000 \n", "50% 41.000000 15.000000 19.000000 69.000000 56.000000 \n", "75% 50.000000 20.000000 24.000000 80.000000 65.000000 \n", "max 96.000000 54.000000 57.000000 100.000000 99.000000 \n", "\n", " Pressure9am Pressure3pm Cloud9am Cloud3pm Temp9am \\\n", "count 3317.000000 3318.000000 2771.000000 2776.000000 3333.000000 \n", "mean 1018.346156 1016.018774 4.181523 4.218660 17.819742 \n", "std 7.021571 7.032211 2.749578 2.641885 4.897177 \n", "min 986.700000 989.800000 0.000000 0.000000 6.400000 \n", "25% 1013.700000 1011.300000 1.000000 1.000000 13.800000 \n", "50% 1018.600000 1016.300000 5.000000 4.000000 18.200000 \n", "75% 1023.100000 1020.800000 7.000000 7.000000 21.700000 \n", "max 1039.000000 1036.700000 9.000000 8.000000 36.500000 \n", "\n", " Temp3pm RISK_MM \n", "count 3333.000000 3337.000000 \n", "mean 21.533333 3.319868 \n", "std 4.303737 9.885066 \n", "min 10.200000 0.000000 \n", "25% 18.400000 0.000000 \n", "50% 21.300000 0.000000 \n", "75% 24.500000 1.400000 \n", "max 44.700000 119.400000 " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dfd = pd.read_csv('Sydney_weather.csv')\n", "\n", "dfd.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# End of exercises\n", "The cell below is for setting the style of this document. It's not part of the exercises." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "data": { "text/html": [ "" ], "text/plain": [ "" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Apply css theme to notebook\n", "from IPython.display import HTML\n", "HTML(''.format(open('../css/cowi.css').read()))" ] } ], "metadata": { "hide_input": false, "kernelspec": { "display_name": "Python 3.7.4 64-bit ('base': conda)", "language": "python", "name": "python37464bitbaseconda613f2456e8544e9ea50cce92ccae96e8" }, "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.7.4" }, "latex_envs": { "LaTeX_envs_menu_present": true, "autoclose": false, "autocomplete": true, "bibliofile": "biblio.bib", "cite_by": "apalike", "current_citInitial": 1, "eqLabelWithNumbers": true, "eqNumInitial": 1, "hotkeys": { "equation": "Ctrl-E", "itemize": "Ctrl-I" }, "labels_anchors": false, "latex_user_defs": false, "report_style_numbering": false, "user_envs_cfg": false }, "toc": { "base_numbering": "", "nav_menu": {}, "number_sections": false, "sideBar": true, "skip_h1_title": false, "title_cell": "Table of Contents", "title_sidebar": "Table of Contents", "toc_cell": false, "toc_position": { "height": "calc(100% - 180px)", "left": "10px", "top": "150px", "width": "165px" }, "toc_section_display": true, "toc_window_display": false } }, "nbformat": 4, "nbformat_minor": 4 }