08. Working with Files.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with Files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sections"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Paths in Windows use `\\`\n",
    "Paths in MAC and Linux use `/`\n",
    "\n",
    "one option to build paths is the\n",
    "\n",
    "- from pathlib import Path"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Opening and reading files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " El volumen de la unidad C no tiene etiqueta.\n",
      " El n£mero de serie del volumen es: 0CDC-AF0E\n",
      "\n",
      " Directorio de C:\\Users\\jmlom\\Documents\\GitHubRepositories\\python_practice\\files\n",
      "\n",
      "09/11/2020  09:20    <DIR>          .\n",
      "09/11/2020  09:20    <DIR>          ..\n",
      "09/11/2020  10:14    <DIR>          modules\n",
      "09/11/2020  09:35    <DIR>          testing\n",
      "               0 archivos              0 bytes\n",
      "               4 dirs  61,352,796,160 bytes libres\n"
     ]
    }
   ],
   "source": [
    "%ls files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "%mkdir files\\working"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " El volumen de la unidad C no tiene etiqueta.\n",
      " El n£mero de serie del volumen es: 0CDC-AF0E\n",
      "\n",
      " Directorio de C:\\Users\\jmlom\\Documents\\GitHubRepositories\\python_practice\\files\n",
      "\n",
      "10/11/2020  09:21    <DIR>          .\n",
      "10/11/2020  09:21    <DIR>          ..\n",
      "09/11/2020  10:14    <DIR>          modules\n",
      "09/11/2020  09:35    <DIR>          testing\n",
      "10/11/2020  09:21    <DIR>          working\n",
      "               0 archivos              0 bytes\n",
      "               5 dirs  61,313,085,440 bytes libres\n"
     ]
    }
   ],
   "source": [
    "%ls files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "%echo \"hello word\" > files\\working\\text.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<_io.TextIOWrapper name='files\\\\working\\\\text.txt' mode='r' encoding='cp1252'>\n",
      "\"hello word\" \n",
      "\n",
      "--------------\n",
      "\n",
      "--------------\n"
     ]
    }
   ],
   "source": [
    "my_file = open(\"files\\\\working\\\\text.txt\")\n",
    "print(my_file)\n",
    "print(my_file.read())\n",
    "print(\"--------------\")\n",
    "print(my_file.read())\n",
    "print(\"--------------\")\n",
    "my_file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<_io.TextIOWrapper name='files\\\\working\\\\text.txt' mode='r' encoding='cp1252'>\n",
      "\"hello word\" \n",
      "\n",
      "--------------\n",
      "\"hello word\" \n",
      "\n",
      "--------------\n"
     ]
    }
   ],
   "source": [
    "my_file = open(\"files\\\\working\\\\text.txt\")\n",
    "print(my_file)\n",
    "print(my_file.read())\n",
    "print(\"--------------\")\n",
    "my_file.seek(0)\n",
    "print(my_file.read())\n",
    "print(\"--------------\")\n",
    "my_file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<_io.TextIOWrapper name='files\\\\working\\\\multi.txt' mode='r' encoding='cp1252'>\n",
      "hello word\n",
      "hello sun\n",
      "hello noon\n",
      " \n",
      "\n"
     ]
    }
   ],
   "source": [
    "my_file = open(\"files\\\\working\\\\multi.txt\")\n",
    "print(my_file)\n",
    "print(my_file.read())\n",
    "my_file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can tell the number of charcters\n",
    "# Also ask for the position with the method .tell\n",
    "# And we can move the cursor with the .seek method to a spcecific position"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# aNOTHER OPTions is tu use \n",
    "with open(\"configuration.txt\") as file:\n",
    "    content = file.read().splitlines()\n",
    "\n",
    "print(content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# or using file as iterable object\n",
    "with open(\"configuration.txt\") as file:\n",
    "    for line in file:\n",
    "        print(line, end=\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<_io.TextIOWrapper name='files\\\\working\\\\multi.txt' mode='r' encoding='cp1252'>\n",
      "hello word\n",
      "\n"
     ]
    }
   ],
   "source": [
    "my_file = open(\"files\\\\working\\\\multi.txt\")\n",
    "print(my_file)\n",
    "print(my_file.readline())\n",
    "my_file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<_io.TextIOWrapper name='files\\\\working\\\\multi.txt' mode='r' encoding='cp1252'>\n",
      "hello word\n",
      "\n",
      "hello sun\n",
      "\n",
      "hello noon\n",
      "\n"
     ]
    }
   ],
   "source": [
    "my_file = open(\"files\\\\working\\\\multi.txt\")\n",
    "print(my_file)\n",
    "print(my_file.readline())\n",
    "print(my_file.readline())\n",
    "print(my_file.readline())\n",
    "my_file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<_io.TextIOWrapper name='files\\\\working\\\\multi.txt' mode='r' encoding='cp1252'>\n",
      "['hello word\\n', 'hello sun\\n', 'hello noon\\n', ' \\n']\n"
     ]
    }
   ],
   "source": [
    "my_file = open(\"files\\\\working\\\\multi.txt\")\n",
    "print(my_file)\n",
    "print(my_file.readlines())\n",
    "my_file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Opening with \"with open\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['hello word\\n', 'hello sun\\n', 'hello noon\\n', ' \\n']\n"
     ]
    }
   ],
   "source": [
    "with open(\"files\\\\working\\\\multi.txt\", mode=\"r\") as my_file:\n",
    "    print(my_file.readlines())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11\n"
     ]
    }
   ],
   "source": [
    "# We have to remember that whe we open a file the cursos restes\n",
    "with open(\"files\\\\working\\\\multi.txt\", mode=\"a\") as my_file:\n",
    "    text = my_file.write(\"hey hey hey\")\n",
    "    print(text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12\n"
     ]
    }
   ],
   "source": [
    "# We have to remember that whe we open a file the cursos restes\n",
    "with open(\"files\\\\working\\\\happy.txt\", mode=\"w\") as my_file:\n",
    "    text = my_file.write(\"I am happy!!\")\n",
    "    print(text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\jmlom\\\\Documents\\\\GitHubRepositories\\\\python_practice'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%pwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "File not found\n"
     ]
    },
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'basura.txt'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-25-22fd1e4ca82d>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;32mexcept\u001b[0m \u001b[0mFileNotFoundError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      6\u001b[0m         \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"File not found\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 7\u001b[1;33m         \u001b[1;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m<ipython-input-25-22fd1e4ca82d>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Using try, except\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m     \u001b[1;32mwith\u001b[0m \u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"basura.txt\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmode\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"r\"\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mmy_file\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      4\u001b[0m         \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmy_file\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;32mexcept\u001b[0m \u001b[0mFileNotFoundError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'basura.txt'"
     ]
    }
   ],
   "source": [
    "# Using try, except\n",
    "try:\n",
    "    with open(\"basura.txt\", mode=\"r\") as my_file:\n",
    "        print(my_file.read())\n",
    "except FileNotFoundError as err:\n",
    "        print(\"File not found\")\n",
    "        raise err"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CSV files\n",
    "\n",
    "CSV means Comma separated values.\n",
    "It is a common format for data exchange.\n",
    "\n",
    "Not always the comma is used, it could be used another character.\n",
    "\n",
    "Python has a built-in modulo for opening CSV files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"Month\", \"2018\", \"2019\", \"2020\"\n",
      "\"JAN\",  340,  360,  417\n",
      "\"FEB\",  318,  342,  391\n",
      "\"MAR\",  362,  406,  419\n",
      "\"APR\",  348,  396,  461\n",
      "\"MAY\",  363,  420,  472\n",
      "\"JUN\",  435,  472,  535\n",
      "\"JUL\",  491,  548,  622\n",
      "\"AUG\",  505,  559,  606\n",
      "\"SEP\",  404,  463,  508\n",
      "\"OCT\",  359,  407,  461\n",
      "\"NOV\",  310,  362,  390\n",
      "\"DEC\",  337,  405,  432\n"
     ]
    }
   ],
   "source": [
    "with open(\"files\\\\working\\\\data.csv\") as file:\n",
    "    print(file.read())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Month', ' \"2018\"', ' \"2019\"', ' \"2020\"']\n",
      "['JAN', '  340', '  360', '  417']\n",
      "['FEB', '  318', '  342', '  391']\n",
      "['MAR', '  362', '  406', '  419']\n",
      "['APR', '  348', '  396', '  461']\n",
      "['MAY', '  363', '  420', '  472']\n",
      "['JUN', '  435', '  472', '  535']\n",
      "['JUL', '  491', '  548', '  622']\n",
      "['AUG', '  505', '  559', '  606']\n",
      "['SEP', '  404', '  463', '  508']\n",
      "['OCT', '  359', '  407', '  461']\n",
      "['NOV', '  310', '  362', '  390']\n",
      "['DEC', '  337', '  405', '  432']\n"
     ]
    }
   ],
   "source": [
    "# Reading CSV\n",
    "import csv\n",
    "with open(\"files\\\\working\\\\data.csv\") as file:\n",
    "    reader = csv.reader(file)\n",
    "    for row in reader:\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " \"2018\"\n",
      "  340\n",
      "  318\n",
      "  362\n",
      "  348\n",
      "  363\n",
      "  435\n",
      "  491\n",
      "  505\n",
      "  404\n",
      "  359\n",
      "  310\n",
      "  337\n"
     ]
    }
   ],
   "source": [
    "# Reading CSV\n",
    "import csv\n",
    "with open(\"files\\\\working\\\\data.csv\") as file:\n",
    "    reader = csv.reader(file)\n",
    "    for row in reader:\n",
    "        print(row[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['JAN', '  340', '  360', '  417']\n",
      "['FEB', '  318', '  342', '  391']\n",
      "['MAR', '  362', '  406', '  419']\n",
      "['APR', '  348', '  396', '  461']\n",
      "['MAY', '  363', '  420', '  472']\n",
      "['JUN', '  435', '  472', '  535']\n",
      "['JUL', '  491', '  548', '  622']\n",
      "['AUG', '  505', '  559', '  606']\n",
      "['SEP', '  404', '  463', '  508']\n",
      "['OCT', '  359', '  407', '  461']\n",
      "['NOV', '  310', '  362', '  390']\n",
      "['DEC', '  337', '  405', '  432']\n"
     ]
    }
   ],
   "source": [
    "# Reading CSV\n",
    "import csv\n",
    "with open(\"files\\\\working\\\\data.csv\") as file:\n",
    "    reader = csv.reader(file)\n",
    "    next(reader)\n",
    "    for row in reader:\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Writting CSV\n",
    "import csv\n",
    "with open(\"files\\\\working\\\\data.csv\", \"a\", newline=\"\") as file:\n",
    "    # Creating a writting object\n",
    "    writer = csv.writer(file)\n",
    "    csv_data1 = (\"Month\", \" 2021\", \" 2022\", \" 2023\")\n",
    "    writer.writerow(csv_data1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"Month\", \"2018\", \"2019\", \"2020\"\n",
      "\"JAN\",  340,  360,  417\n",
      "\"FEB\",  318,  342,  391\n",
      "\"MAR\",  362,  406,  419\n",
      "\"APR\",  348,  396,  461\n",
      "\"MAY\",  363,  420,  472\n",
      "\"JUN\",  435,  472,  535\n",
      "\"JUL\",  491,  548,  622\n",
      "\"AUG\",  505,  559,  606\n",
      "\"SEP\",  404,  463,  508\n",
      "\"OCT\",  359,  407,  461\n",
      "\"NOV\",  310,  362,  390\n",
      "\"DEC\",  337,  405,  432\n",
      "Month, 2021, 2022, 2023\n",
      "\n"
     ]
    }
   ],
   "source": [
    "with open(\"files\\\\working\\\\data.csv\") as file:\n",
    "    print(file.read())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['root', 'x', '0', '0', 'root', '/root', '/bin/bash']\n",
      "['daemon', 'x', '1', '1', 'daemon', '/usr/sbin', '/usr/sbin/nologin']\n",
      "['bin', 'x', '2', '2', 'bin', '/bin', '/usr/sbin/nologin']\n",
      "['sys', 'x', '3', '3', 'sys', '/dev', '/usr/sbin/nologin']\n",
      "['sync', 'x', '4', '65534', 'sync', '/bin', '/bin/sync']\n",
      "['games', 'x', '5', '60', 'games', '/usr/games', '/usr/sbin/nologin']\n",
      "['man', 'x', '6', '12', 'man', '/var/cache/man', '/usr/sbin/nologin']\n",
      "['lp', 'x', '7', '7', 'lp', '/var/spool/lpd', '/usr/sbin/nologin']\n",
      "['mail', 'x', '8', '8', 'mail', '/var/mail', '/usr/sbin/nologin']\n",
      "['news', 'x', '9', '9', 'news', '/var/spool/news', '/usr/sbin/nologin']\n",
      "['uucp', 'x', '10', '10', 'uucp', '/var/spool/uucp', '/usr/sbin/nologin']\n",
      "['proxy', 'x', '13', '13', 'proxy', '/bin', '/usr/sbin/nologin']\n"
     ]
    }
   ],
   "source": [
    "# When other delimeter is used:\n",
    "import csv\n",
    "with open(\"files\\\\working\\\\passwd.csv\", \"r\") as file:\n",
    "    reader = csv.reader(file, delimiter=\":\", lineterminator=\"\\n\")\n",
    "    for row in reader:\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['excel', 'excel-tab', 'unix']\n"
     ]
    }
   ],
   "source": [
    "# CSV available dialects\n",
    "print(csv.list_dialects())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saludo#Pais#Lenguaje\n",
      "Hola#Mexico#Espanol\n",
      "Hi#Canada#Ingles\n",
      "Ciao#Italia#Italiano\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# We can cerate our own dialect, for example if we want toa add the useage of # as delimiter\n",
    "\n",
    "with open(\"files\\\\working\\\\items.csv\", \"r\") as file:\n",
    "    print(file.read())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Saludo', 'Pais', 'Lenguaje']\n",
      "['Hola', 'Mexico', 'Espanol']\n",
      "['Hi', 'Canada', 'Ingles']\n",
      "['Ciao', 'Italia', 'Italiano']\n"
     ]
    }
   ],
   "source": [
    "csv.register_dialect(\"hashes\", delimiter=\"#\", quoting=csv.QUOTE_NONE, lineterminator=\"\\n\")\n",
    "\n",
    "with open(\"files\\\\working\\\\items.csv\", \"r\") as file:\n",
    "    reader = csv.reader(file, dialect=\"hashes\")\n",
    "    for row in reader:\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"files\\\\working\\\\items.csv\", \"a\") as file:\n",
    "    writer = csv.writer(file, dialect=\"hashes\")\n",
    "    writer.writerow((\"Ni Hao\", \"China\", \"Mandarin\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Saludo', 'Pais', 'Lenguaje']\n",
      "['Hola', 'Mexico', 'Espanol']\n",
      "['Hi', 'Canada', 'Ingles']\n",
      "['Ciao', 'Italia', 'Italiano']\n",
      "['Ni Hao', 'China', 'Mandarin']\n"
     ]
    }
   ],
   "source": [
    "with open(\"files\\\\working\\\\items.csv\", \"r\") as file:\n",
    "    reader = csv.reader(file, dialect=\"hashes\")\n",
    "    for row in reader:\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Excel files\n",
    "\n",
    "There are multiple Python libraries to work with Excel formated data. Personally I like pandas library when I work with Juniper notebooks. but for scripting I will show some examples with Pyton OpenPyXL. This library need to be inslalled with pip install or conda install."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We can validate if the installation was sucessful importing the library\n",
    "import openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Printing all sheet names in the sheetnames attribute\n",
      "['Products', 'Sales 2018']\n",
      "Printing all sheet names in the iterable object\n",
      "Products\n",
      "Sales 2018\n"
     ]
    }
   ],
   "source": [
    "## Loading an excel file into an object\n",
    "wb = openpyxl.load_workbook(\"files\\\\working\\\\store.xlsx\")  \n",
    " \n",
    "## Loading an excel file. If there are formulas, values are read and not the formulas\n",
    "# wb = openpyxl.load_workbook(\"files\\\\working\\\\store.xlsx\", data_only=True)\n",
    " \n",
    "## Printing all sheet names in the sheetnames attribute\n",
    "print(\"Printing all sheet names in the sheetnames attribute\")\n",
    "print(wb.sheetnames)   \n",
    " \n",
    "## Iterating of the sheets in the workbook and printing their title\n",
    "print(\"Printing all sheet names in the iterable object\")\n",
    "for sheet in wb:\n",
    "    print(sheet.title)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Getting a sheet by name. All subsequent operations will be done on this sheet\n",
    "sheet = wb['Products']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Getting the active sheet. By default it's the last opened sheet\n",
    "sheet = wb.active"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mobile Phone 15\n"
     ]
    }
   ],
   "source": [
    "## Getting cells\n",
    "b2_cell = sheet['B2']\n",
    "c2_cell = sheet['c2']\n",
    " \n",
    "## Getting values of cells\n",
    "print(b2_cell.value, c2_cell.value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 3\n"
     ]
    }
   ],
   "source": [
    "# Another way to get the position of a cell\n",
    "print(c2_cell.row, c2_cell.column)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Smart Watch\n"
     ]
    }
   ],
   "source": [
    "## Another way to get the values of cells using the row and columns coordenates\n",
    "print(sheet.cell(row=4, column=2).value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "n\n",
      "s\n"
     ]
    }
   ],
   "source": [
    "## Getting the data type\n",
    "print(sheet['A5'].data_type)\n",
    "print(sheet['B5'].data_type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "utf-8\n"
     ]
    }
   ],
   "source": [
    "# Getting the encoding format of cell\n",
    "print(sheet['A5'].encoding)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Worksheet \"Products\">\n"
     ]
    }
   ],
   "source": [
    "# The parent attribute refers to the name of the sheet that contains the cell\n",
    "print(sheet['B5'].parent)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['__class__', '__delattr__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__slots__', '__str__', '__subclasshook__', '_bind_value', '_comment', '_hyperlink', '_style', '_value', 'alignment', 'base_date', 'border', 'check_error', 'check_string', 'col_idx', 'column', 'column_letter', 'comment', 'coordinate', 'data_type', 'encoding', 'fill', 'font', 'guess_types', 'has_style', 'hyperlink', 'internal_value', 'is_date', 'number_format', 'offset', 'parent', 'pivotButton', 'protection', 'quotePrefix', 'row', 'style', 'style_id', 'value']\n"
     ]
    }
   ],
   "source": [
    "## Printing all cell methods\n",
    "print(dir(b2_cell))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Getting a cell range\n",
    "cell_range = sheet[\"B2:C11\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Product: Mobile Phone    Price:15\n",
      "Product: Laptop    Price:15\n",
      "Product: Smart Watch    Price:50\n",
      "Product: Fitness Band    Price:30\n",
      "Product: VR Headset    Price:20\n",
      "Product: E-Reader    Price:30\n",
      "Product: Headphones    Price:80\n",
      "Product: Camera    Price:20\n",
      "Product: Game Console    Price:25\n",
      "Product: Video Projector    Price:10\n"
     ]
    }
   ],
   "source": [
    "## Iterating over the cell range\n",
    "for product, price in cell_range:\n",
    "    print(f\"Product: {product.value}    Price:{price.value}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sheet Dimentions: A1:E11\n",
      "Max row: 11, Max column: 5\n"
     ]
    }
   ],
   "source": [
    "## Getting the sheet dimensions\n",
    "print(f\"Sheet Dimentions: {sheet.dimensions}\")\n",
    "print(f\"Max row: {sheet.max_row}, Max column: {sheet.max_column}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None Product Name Total Units Unit Price Total Amount\n",
      "1 Mobile Phone 15 400 6000\n",
      "2 Laptop 15 800 12000\n",
      "3 Smart Watch 50 150 7500\n",
      "4 Fitness Band 30 100 3000\n",
      "5 VR Headset 20 300 6000\n",
      "6 E-Reader 30 100 3000\n",
      "7 Headphones 80 80 6400\n",
      "8 Camera 20 600 12000\n",
      "9 Game Console 25 700 17500\n",
      "10 Video Projector 10 800 8000\n"
     ]
    }
   ],
   "source": [
    "## Iterating and getting all data in a sheet\n",
    "for a, b, c, d, e in sheet[sheet.dimensions]:\n",
    "    print(a.value, b.value, c.value, d.value, e.value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None Product Name Total Units Unit Price Total Amount \n",
      "\n",
      "1 Mobile Phone 15 400 6000 \n",
      "\n",
      "2 Laptop 15 800 12000 \n",
      "\n",
      "3 Smart Watch 50 150 7500 \n",
      "\n",
      "4 Fitness Band 30 100 3000 \n",
      "\n",
      "5 VR Headset 20 300 6000 \n",
      "\n",
      "6 E-Reader 30 100 3000 \n",
      "\n",
      "7 Headphones 80 80 6400 \n",
      "\n",
      "8 Camera 20 600 12000 \n",
      "\n",
      "9 Game Console 25 700 17500 \n",
      "\n",
      "10 Video Projector 10 800 8000 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "for row in sheet.rows:\n",
    "    for cell in row:\n",
    "        print(f\"{cell.value} \", end=\"\")\n",
    "    print(\"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(None, 'Product Name', 'Total Units', 'Unit Price', 'Total Amount')\n",
      "(1, 'Mobile Phone', 15, 400, 6000)\n",
      "(2, 'Laptop', 15, 800, 12000)\n",
      "(3, 'Smart Watch', 50, 150, 7500)\n",
      "(4, 'Fitness Band', 30, 100, 3000)\n",
      "(5, 'VR Headset', 20, 300, 6000)\n",
      "(6, 'E-Reader', 30, 100, 3000)\n",
      "(7, 'Headphones', 80, 80, 6400)\n",
      "(8, 'Camera', 20, 600, 12000)\n",
      "(9, 'Game Console', 25, 700, 17500)\n",
      "(10, 'Video Projector', 10, 800, 8000)\n"
     ]
    }
   ],
   "source": [
    "for row in sheet.values:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing Excel Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Importing the module\n",
    "import openpyxl\n",
    "\n",
    "## Loading an excel file. store.xlsx is in the same directory with the python script\n",
    "wb = openpyxl.load_workbook(\"files\\\\working\\\\store.xlsx\")\n",
    " \n",
    "## Getting the active sheet (last opened sheet)\n",
    "sheet = wb.active\n",
    " \n",
    "## Change the value in cell\n",
    "sheet['d2'] = 405\n",
    "\n",
    "## Saving the file\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Loading an excel file. store.xlsx is in the same directory with the python script\n",
    "wb = openpyxl.load_workbook(\"files\\\\working\\\\store.xlsx\")\n",
    "\n",
    "## Getting the active sheet (last opened sheet)\n",
    "sheet = wb.active\n",
    "\n",
    "## Adding a new row\n",
    "new_product = (11, 'Tablet', 12, 600, 12*600)       # this is a tuple\n",
    "sheet.append(new_product)\n",
    "\n",
    "\n",
    "## Saving the file\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Loading an excel file. store.xlsx is in the same directory with the python script\n",
    "wb = openpyxl.load_workbook(\"files\\\\working\\\\store.xlsx\")\n",
    "\n",
    "## Getting the active sheet (last opened sheet)\n",
    "sheet = wb.active\n",
    "\n",
    "## Iterating and updating an entire column\n",
    "for c, d, e in sheet['c2:e12']:\n",
    "    e.value = c.value * d.value\n",
    "\n",
    "## Saving the file\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating new Excel files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Importing the module\n",
    "import openpyxl\n",
    " \n",
    "## Creating an in-memory Workbook\n",
    "wb = openpyxl.Workbook()\n",
    " \n",
    "## Getting the active sheet. This will be the only sheet in the workbook\n",
    "sheet = wb.active\n",
    " \n",
    "## Updating the new sheet using cells' addresses\n",
    "sheet['A1'] = 'Year'\n",
    "sheet['B1'] = 'Sales'\n",
    " \n",
    "sales = {2018:60000, 2019:80000, 2020:100000}   # dictionary\n",
    " \n",
    "## Iterating over the dictionary and appending to the sheet\n",
    "for k,v in sales.items():\n",
    "    sheet.append((k,v))\n",
    "    \n",
    "## Saving the Workbook \n",
    "wb.save(\"files\\\\working\\\\sales.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Coding Excel Formulas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Importing the module\n",
    "import openpyxl\n",
    " \n",
    "## Loading an excel file. store.xlsx is in the same directory with the python script\n",
    "wb = openpyxl.load_workbook(\"files\\\\working\\\\store.xlsx\")\n",
    " \n",
    "## Getting a sheet by name\n",
    "sheet = wb[\"Products\"]\n",
    " \n",
    "## Iterating over a cell range and writing an Excel formula in each cell of a column\n",
    "for c, d, e in sheet[\"c2:e12\"]:\n",
    "    e.value = f\"={c.coordinate}*{d.coordinate}\" #writing an Excel formula to each cell. The formula is written as a string\n",
    "    \n",
    "## Saving the file\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Loading an excel file. store.xlsx is in the same directory with the python script\n",
    "wb = openpyxl.load_workbook(\"files\\\\working\\\\store.xlsx\")\n",
    "\n",
    "## Getting another sheet of the Workbook\n",
    "sheet = wb['Sales 2018']\n",
    " \n",
    "## Writing an Excel formula =sum(B2:B13)\n",
    "sheet['B14']=\"=sum(B2:B13)\"\n",
    "\n",
    "## Saving the file\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sheet Operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Products', 'Sales 2018']\n",
      "['BREAK_COLUMN', 'BREAK_NONE', 'BREAK_ROW', 'HeaderFooter', 'ORIENTATION_LANDSCAPE', 'ORIENTATION_PORTRAIT', 'PAPERSIZE_A3', 'PAPERSIZE_A4', 'PAPERSIZE_A4_SMALL', 'PAPERSIZE_A5', 'PAPERSIZE_EXECUTIVE', 'PAPERSIZE_LEDGER', 'PAPERSIZE_LEGAL', 'PAPERSIZE_LETTER', 'PAPERSIZE_LETTER_SMALL', 'PAPERSIZE_STATEMENT', 'PAPERSIZE_TABLOID', 'SHEETSTATE_HIDDEN', 'SHEETSTATE_VERYHIDDEN', 'SHEETSTATE_VISIBLE', '_WorkbookChild__title', '__class__', '__delattr__', '__delitem__', '__dict__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__getitem__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__setitem__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_add_cell', '_add_column', '_add_row', '_cells', '_cells_by_col', '_cells_by_row', '_charts', '_clean_merge_range', '_comments', '_current_row', '_default_title', '_drawing', '_get_cell', '_hyperlinks', '_id', '_images', '_invalid_row', '_move_cell', '_move_cells', '_parent', '_path', '_pivots', '_print_area', '_print_cols', '_print_rows', '_rel_type', '_rels', '_setup', '_tables', 'active_cell', 'add_chart', 'add_data_validation', 'add_image', 'add_pivot', 'add_table', 'append', 'auto_filter', 'calculate_dimension', 'cell', 'col_breaks', 'column_dimensions', 'columns', 'conditional_formatting', 'data_validations', 'delete_cols', 'delete_rows', 'dimensions', 'encoding', 'evenFooter', 'evenHeader', 'firstFooter', 'firstHeader', 'formula_attributes', 'freeze_panes', 'insert_cols', 'insert_rows', 'iter_cols', 'iter_rows', 'legacy_drawing', 'max_column', 'max_row', 'merge_cells', 'merged_cell_ranges', 'merged_cells', 'mime_type', 'min_column', 'min_row', 'move_range', 'oddFooter', 'oddHeader', 'orientation', 'page_breaks', 'page_margins', 'page_setup', 'paper_size', 'parent', 'path', 'print_area', 'print_options', 'print_title_cols', 'print_title_rows', 'print_titles', 'protection', 'row_breaks', 'row_dimensions', 'rows', 'scenarios', 'selected_cell', 'set_printer_settings', 'sheet_format', 'sheet_properties', 'sheet_state', 'sheet_view', 'show_gridlines', 'show_summary_below', 'show_summary_right', 'title', 'unmerge_cells', 'values', 'views']\n"
     ]
    }
   ],
   "source": [
    "## Importing the module\n",
    "import openpyxl\n",
    " \n",
    "## Loading an excel file. store.xlsx is in the same directory with the python script\n",
    "wb = openpyxl.load_workbook(\"files\\\\working\\\\store.xlsx\")\n",
    " \n",
    "## Printing all sheets by name\n",
    "print(wb.sheetnames)\n",
    " \n",
    "## Getting a sheet by name\n",
    "sheet = wb[\"Products\"]\n",
    " \n",
    "## Printing all sheet's methods\n",
    "print(dir(sheet))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Products'"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Changing the sheet title\n",
    "# sheet.title = 'Products for sale'\n",
    "sheet.title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<openpyxl.worksheet.dimensions.SheetFormatProperties object>\n",
      "Parameters:\n",
      "baseColWidth=8, defaultColWidth=None, defaultRowHeight=17.4, customHeight=None, zeroHeight=None, thickTop=None, thickBottom=None, outlineLevelRow=None, outlineLevelCol=None\n",
      "--------------------------------------------------------------------------------\n",
      "<openpyxl.worksheet.properties.WorksheetProperties object>\n",
      "Parameters:\n",
      "codeName=None, enableFormatConditionsCalculation=None, filterMode=None, published=None, syncHorizontal=None, syncRef=None, syncVertical=None, transitionEvaluation=None, transitionEntry=None, tabColor=None, outlinePr=<openpyxl.worksheet.properties.Outline object>\n",
      "Parameters:\n",
      "applyStyles=None, summaryBelow=True, summaryRight=True, showOutlineSymbols=None, pageSetUpPr=<openpyxl.worksheet.properties.PageSetupProperties object>\n",
      "Parameters:\n",
      "autoPageBreaks=None, fitToPage=None\n"
     ]
    }
   ],
   "source": [
    "## Printing sheet properties\n",
    "print(sheet.sheet_format)\n",
    "print(80*\"-\")\n",
    "print(sheet.sheet_properties)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Create a new sheet on first position in the Workbook (by default will be the last)\n",
    "wb.create_sheet(\"Turnover1\",0)\n",
    " \n",
    "## Getting a sheet by name\n",
    "sheet1 = wb[\"Turnover1\"]\n",
    "\n",
    "## Saving the changes\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Removing a sheet by name\n",
    "# wb.remove(sheet1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Turnover1 Copy4\n",
      "['Turnover13', 'Turnover12', 'Turnover11', 'Turnover1', 'Products', 'Sales 2018', 'Turnover1 Copy', 'Turnover1 Copy1', 'Turnover1 Copy2', 'Turnover1 Copy3', 'Turnover1 Copy4']\n"
     ]
    }
   ],
   "source": [
    "## Copying sheets in the same Workbook\n",
    "source = wb[\"Turnover1\"]\n",
    "destination = wb.copy_worksheet(source)\n",
    "print(destination.title)\n",
    "print(wb.sheetnames)\n",
    "\n",
    "## Saving the changes\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with Styles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Importing the module\n",
    "import openpyxl\n",
    " \n",
    "## Importing everything from the styles submodule\n",
    "from openpyxl.styles import *\n",
    " \n",
    "## Importing copy() function from copy module to copy styles\n",
    "from copy import copy\n",
    " \n",
    "## Loading an excel file. store.xlsx is in the same directory with the python script\n",
    "wb = openpyxl.load_workbook(\"files\\\\working\\\\store.xlsx\")\n",
    " \n",
    "## Getting a sheet\n",
    "sheet = wb['Products']\n",
    " \n",
    "## Getting a cell by address. It will be modified\n",
    "my_cell = sheet['B4']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Alignment', 'Border', 'Color', 'DEFAULT_FONT', 'Fill', 'Font', 'GradientFill', 'NamedStyle', 'NumberFormatDescriptor', 'PatternFill', 'Protection', 'Side', '__builtins__', '__cached__', '__doc__', '__file__', '__loader__', '__name__', '__package__', '__path__', '__spec__', 'alignment', 'borders', 'builtins', 'cell_style', 'colors', 'differential', 'fills', 'fonts', 'is_builtin', 'is_date_format', 'named_styles', 'numbers', 'protection', 'proxy', 'styleable', 'stylesheet', 'table']\n"
     ]
    }
   ],
   "source": [
    "## Printing all available styles\n",
    "print(dir(openpyxl.styles))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Changing and setting a new font\n",
    "font = Font(name='Tahoma', size=16, color=colors.RED, bold=True, italic=True, strike=False)\n",
    "my_cell.font = font\n",
    " \n",
    "## Saving the changes\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Setting a Pattern Fill\n",
    "fill = PatternFill(fill_type='solid', fgColor=colors.YELLOW)\n",
    "my_cell.fill = fill\n",
    "\n",
    "## Saving the changes\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Setting cell border\n",
    "double_border_green = Side(border_style='double', color=colors.GREEN)\n",
    "thin_border_red = Side(border_style='thin', color='FF0000')\n",
    "my_cell.border = Border(left=double_border_green, right=thin_border_red, top=double_border_green,\n",
    "                        bottom=thin_border_red)\n",
    "\n",
    "## Saving the changes\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Setting cell's content alignment\n",
    "alignment = Alignment(horizontal='right', vertical='center')\n",
    "my_cell.alignment = alignment\n",
    "\n",
    "## Saving the changes\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Copy styles from a cell to another\n",
    "new_cell = sheet['B10']\n",
    "new_font = copy(my_cell.font)\n",
    "new_font.color = colors.GREEN\n",
    "new_cell.font = new_font\n",
    "\n",
    "## Saving the changes\n",
    "wb.save(\"files\\\\working\\\\store.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}