Excel_Operations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Excel Automation with Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Table of Content: \n",
    "    1. Write data using openpyxl\n",
    "    2. Write data using pandas\n",
    "    3. Read data using openpyxl\n",
    "    4. Read data using pandas\n",
    "    5. Creating Multiple Sheets in master excel file\n",
    "    6. Combining multiple excel speadsheet into a master excel\n",
    "    7. Plotting bar graph \n",
    "    8. Plotting pie graph\n",
    "    9. Sample Project 1  \n",
    "    10. Sample Project 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Writting data into xlsx file using **openpyxl**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook    \n",
    "wb = Workbook()  \n",
    "sheet = wb.active  \n",
    "\n",
    "sheet['A1'] =\"Roll\"   \n",
    "sheet['A2'] = 1  \n",
    "sheet['A3'] = 2  \n",
    "sheet['A4'] = 3\n",
    "\n",
    "sheet['B1'] =\"Name\"   \n",
    "sheet['B2'] = \"Mickel\"  \n",
    "sheet['B3'] = \"Ronit\"  \n",
    "sheet['B4'] = \"Rohit\"\n",
    "\n",
    "sheet['C1'] =\"Marks\"   \n",
    "sheet['C2'] = 80 \n",
    "sheet['C3'] = 68  \n",
    "sheet['C4'] = 39\n",
    " \n",
    "wb.save(\"Data/Student_Record_1.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Writting data into xlsx file using **Pandas**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "Roll = [1, 2, 3]\n",
    "Name = ['Mickel', 'Ronit', 'Rohit']\n",
    "Marks = [80, 68, 39]\n",
    "\n",
    "df = pd.DataFrame({'Name':Name, 'Roll':Roll, 'Marks': Marks})\n",
    "\n",
    "# Create a Pandas Excel writer using XlsxWriter as the engine.\n",
    "writer = pd.ExcelWriter('Data/Student_Record_2.xlsx')\n",
    "\n",
    "# Convert the dataframe to an XlsxWriter Excel object.\n",
    "df.to_excel(writer, sheet_name='sheet',header=True, index=False)\n",
    "\n",
    "# Close the Pandas Excel writer and output the Excel file.\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Reading data from xlsx file using openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Roll', 'Name', 'Marks']\n",
      "[1, 'Mickel', 80]\n",
      "[2, 'Ronit', 68]\n",
      "[3, 'Rohit', 39]\n"
     ]
    }
   ],
   "source": [
    "import openpyxl\n",
    "path = \"Data/Student_Record_1.xlsx\"\n",
    "wb = openpyxl.load_workbook(path)\n",
    "sheet = wb.active\n",
    "for row in sheet:\n",
    "    print([data.value for data in row])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Reading data from xlsx file using pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     Name  Roll  Marks\n",
      "0  Mickel     1     80\n",
      "1   Ronit     2     68\n",
      "2   Rohit     3     39\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(\"Data/Student_Record_2.xlsx\", sheet_name='sheet1')\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Creating Mutipliple Sheet in single excel spreadsheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before Creating:  ['Sheet']\n",
      "After Creating:  ['Sheet', 'Student']\n",
      "After Creating:  ['Teacher', 'Sheet', 'Student']\n",
      "After Deleting:  ['Teacher', 'Student']\n"
     ]
    }
   ],
   "source": [
    "import openpyxl\n",
    "\n",
    "book = openpyxl.load_workbook('Data/Sample_file.xlsx')\n",
    "print(\"Before Creating: \",book.sheetnames)\n",
    "\n",
    "#Creating new sheets in Sample_file.xlsx\n",
    "book.create_sheet(\"Student\")\n",
    "print(\"After Creating: \",book.sheetnames)\n",
    "\n",
    "# creating new sheet (\"Sheet_name\",Position) \n",
    "book.create_sheet(\"Teacher\", 0)\n",
    "print(\"After Creating: \",book.sheetnames)\n",
    "\n",
    "# Delete sheet from file\n",
    "book.remove(book['Sheet'])\n",
    "print(\"After Deleting: \",book.sheetnames)\n",
    "\n",
    "book.save('Data/Sample_file.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Combining multiple excel speadsheet into a master excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing the module\n",
    "import pandas\n",
    "  \n",
    "# reading the files\n",
    "df1 = pandas.read_excel(\"Data/Student_details.xlsx\")\n",
    "df2 = pandas.read_excel(\"Data/Marks.xlsx\")\n",
    "  \n",
    "# merging the files\n",
    "df3 = df1[[\"Roll No\",\"Name\",\"Email id\"]].merge(df2[[\"Roll No\",\"English\", \"Science\", \"History\"]], on = \"Roll No\", how = \"left\")\n",
    "  \n",
    "# creating a new file\n",
    "df3.to_excel(\"Data/Student_details_with_marks.xlsx\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Drawing Bar graph using Openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "from openpyxl.chart import BarChart, Series, Reference\n",
    "\n",
    "path = \"Data/Mark.xlsx\"\n",
    "wb = openpyxl.load_workbook(path)\n",
    "ws = wb.active\n",
    "\n",
    "chart1 = BarChart()\n",
    "chart1.type = \"col\"\n",
    "chart1.style = 10\n",
    "chart1.title = \"Bar Chart\"\n",
    "chart1.y_axis.title = 'Marks Obtain'\n",
    "chart1.x_axis.title = 'Roll No'\n",
    "\n",
    "data = Reference(ws, min_col=2, min_row=1, max_row=11, max_col=4)\n",
    "cats = Reference(ws, min_col=1, min_row=2, max_row=11)\n",
    "chart1.add_data(data, titles_from_data=True)\n",
    "chart1.set_categories(cats)\n",
    "chart1.shape = 4\n",
    "ws.add_chart(chart1, \"A10\")\n",
    "\n",
    "wb.save(\"Data/Mark.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Drawing Pie Graph using openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "from openpyxl.chart import (\n",
    "    PieChart,\n",
    "    ProjectedPieChart,\n",
    "    Reference\n",
    ")\n",
    "from openpyxl.chart.series import DataPoint\n",
    "\n",
    "path = \"Data/pie.xlsx\"\n",
    "wb = openpyxl.load_workbook(path)\n",
    "ws = wb.active\n",
    "\n",
    "pie = PieChart()\n",
    "labels = Reference(ws, min_col=1, min_row=2, max_row=8)\n",
    "data = Reference(ws, min_col=2, min_row=1, max_row=8)\n",
    "pie.add_data(data, titles_from_data=True)\n",
    "pie.set_categories(labels)\n",
    "pie.title = \"Monthly Salary Expense\"\n",
    "\n",
    "# Cut the first slice out of the pie\n",
    "slice = DataPoint(idx=0, explosion=20)\n",
    "pie.series[0].data_points = [slice]\n",
    "\n",
    "ws.add_chart(pie, \"D1\")\n",
    "\n",
    "wb.save(\"Data/pie.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9. Simple Project 1\n",
    "Task 1: we are going to add two more column in excel spreadsheet **Result** and **Grade**.\n",
    "\n",
    "> In **Result** column we will put **pass** and **Failed** based on Percentage He/She got.<br>\n",
    "1. **Pass**: If percentage is greater than and equal to 40.\n",
    "2. **Failed**: If percentage is less than 40\n",
    "\n",
    "> In **Grade** column we will put **1st class**, **2nd class**, **3rd class** and **Failed** based on Percentage He/She got.\n",
    "1. 1st class: If percentage greater than equal to 60.\n",
    "2. 2nd class: If percentage greater than equal to 50 but less than 60.\n",
    "3. 3rd class: If percentage greater than equal to 40 but less than 50.\n",
    "4. Failed: If percentage less than 40.\n",
    " \n",
    "Task 2: After doing the first task we will create multiple sheet in the same excel file base on Grade He/She got."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Roll</th>\n",
       "      <th>Name</th>\n",
       "      <th>Email</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Asha</td>\n",
       "      <td>asha@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Sonal</td>\n",
       "      <td>sonal@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Krishan</td>\n",
       "      <td>krishan@rs.com</td>\n",
       "      <td>Male</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Sonali</td>\n",
       "      <td>sonali@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Ritu</td>\n",
       "      <td>ritu@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Roll     Name           Email Gender  Percentage\n",
       "0     1    Asha     asha@rs.com   Femal          58\n",
       "1     2   Sonal    sonal@rs.com   Femal          25\n",
       "2     3  Krishan  krishan@rs.com   Male          95\n",
       "3     4   Sonali   sonali@rs.com  Femal          45\n",
       "4     5     Ritu     ritu@rs.com  Femal          67"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# importing required library\n",
    "import pandas as pd\n",
    "import xlrd\n",
    "import openpyxl\n",
    "from openpyxl import Workbook\n",
    "df = pd.read_excel(\"Data/student_result.xlsx\", sheet_name='Sheet1')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Performing Task 1 (Adding Result column)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb= openpyxl.load_workbook(\"Data/student_result.xlsx\")\n",
    "sheet = wb['Sheet1']\n",
    "rows_max=sheet.max_row\n",
    "sheet.cell(row=1, column=6).value =\"Result\"\n",
    "for i in range(2,sheet.max_row + 1):\n",
    "    if sheet.cell(row=i, column=5).value >= 40:\n",
    "        sheet.cell(row=i, column=6).value = \"Pass\"\n",
    "    else:\n",
    "        sheet.cell(row=i, column=6).value =\"Failed\"\n",
    "        \n",
    "wb.save(\"Data/student_result.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Roll</th>\n",
       "      <th>Name</th>\n",
       "      <th>Email</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Percentage</th>\n",
       "      <th>Result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Asha</td>\n",
       "      <td>asha@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>58</td>\n",
       "      <td>Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Sonal</td>\n",
       "      <td>sonal@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>25</td>\n",
       "      <td>Failed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Krishan</td>\n",
       "      <td>krishan@rs.com</td>\n",
       "      <td>Male</td>\n",
       "      <td>95</td>\n",
       "      <td>Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Sonali</td>\n",
       "      <td>sonali@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>45</td>\n",
       "      <td>Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Ritu</td>\n",
       "      <td>ritu@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>67</td>\n",
       "      <td>Pass</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Roll     Name           Email Gender  Percentage  Result\n",
       "0     1    Asha     asha@rs.com   Femal          58    Pass\n",
       "1     2   Sonal    sonal@rs.com   Femal          25  Failed\n",
       "2     3  Krishan  krishan@rs.com   Male          95    Pass\n",
       "3     4   Sonali   sonali@rs.com  Femal          45    Pass\n",
       "4     5     Ritu     ritu@rs.com  Femal          67    Pass"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"Data/student_result.xlsx\", sheet_name='Sheet1')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Performing Task 1 (Adding Grade column )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb= openpyxl.load_workbook(\"Data/student_result.xlsx\")\n",
    "sheet = wb['Sheet1']\n",
    "rows_max=sheet.max_row\n",
    "sheet.cell(row=1, column=7).value =\"Grades\"\n",
    "for i in range(2,sheet.max_row + 1):\n",
    "    if sheet.cell(row=i, column=5).value >= 60:\n",
    "        sheet.cell(row=i, column=7).value = \"1st class\"\n",
    "        \n",
    "    elif sheet.cell(row=i, column=5).value <= 60 and sheet.cell(row=i, column=5).value >=50 :\n",
    "        sheet.cell(row=i, column=7).value = \"2nd class\"\n",
    "        \n",
    "    elif sheet.cell(row=i, column=5).value <= 50 and sheet.cell(row=i, column=5).value >=40 :\n",
    "        sheet.cell(row=i, column=7).value = \"3rd class\"\n",
    "    else:\n",
    "        sheet.cell(row=i, column=7).value =\"Failed\"\n",
    "        \n",
    "wb.save(\"Data/student_result.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Roll</th>\n",
       "      <th>Name</th>\n",
       "      <th>Email</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Percentage</th>\n",
       "      <th>Result</th>\n",
       "      <th>Grades</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Asha</td>\n",
       "      <td>asha@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>58</td>\n",
       "      <td>Pass</td>\n",
       "      <td>2nd class</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Sonal</td>\n",
       "      <td>sonal@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>25</td>\n",
       "      <td>Failed</td>\n",
       "      <td>Failed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Krishan</td>\n",
       "      <td>krishan@rs.com</td>\n",
       "      <td>Male</td>\n",
       "      <td>95</td>\n",
       "      <td>Pass</td>\n",
       "      <td>1st class</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Sonali</td>\n",
       "      <td>sonali@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>45</td>\n",
       "      <td>Pass</td>\n",
       "      <td>3rd class</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Ritu</td>\n",
       "      <td>ritu@rs.com</td>\n",
       "      <td>Femal</td>\n",
       "      <td>67</td>\n",
       "      <td>Pass</td>\n",
       "      <td>1st class</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Roll     Name           Email Gender  Percentage  Result     Grades\n",
       "0     1    Asha     asha@rs.com   Femal          58    Pass  2nd class\n",
       "1     2   Sonal    sonal@rs.com   Femal          25  Failed     Failed\n",
       "2     3  Krishan  krishan@rs.com   Male          95    Pass  1st class\n",
       "3     4   Sonali   sonali@rs.com  Femal          45    Pass  3rd class\n",
       "4     5     Ritu     ritu@rs.com  Femal          67    Pass  1st class"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"Data/student_result.xlsx\", sheet_name='Sheet1')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sperating into multiple sheet in same excel file based on the grade obtain."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import xlrd\n",
    "import openpyxl\n",
    "from openpyxl import Workbook\n",
    "wb= openpyxl.load_workbook(\"Data/student_result.xlsx\")\n",
    "sheet = wb['Sheet1']\n",
    "\n",
    "def multiple_sheet(t1,grade):\n",
    "    work_sheet = wb.create_sheet(title=grade)\n",
    "    for row in t1:\n",
    "        work_sheet.append(row)\n",
    "    wb.save(\"Data/student_result.xlsx\")\n",
    "\n",
    "def grade_wise(row_num,grade):    \n",
    "    t1=[]\n",
    "    for j in row_num:\n",
    "        t=[]\n",
    "        for col in sheet.iter_cols(min_col=1,max_col=sheet.max_column, min_row = j,max_row=j ):\n",
    "            for cell in col:\n",
    "                t.append(cell.value)\n",
    "        t1.append(tuple(t))                \n",
    "    multiple_sheet(t1,grade)\n",
    "    \n",
    "def separating():\n",
    "    df = pd.read_excel(\"Data/student_result.xlsx\", sheet_name='Sheet1')\n",
    "    grades=[]\n",
    "    for i in range(len(df[\"Grades\"])):\n",
    "        rec=df[\"Grades\"][i]\n",
    "        grades.append(rec)            \n",
    "    grades=list(set(grades))\n",
    "    start = 1\n",
    "    cols_max=sheet.max_column\n",
    "    rows_max=sheet.max_row\n",
    "\n",
    "    for grade in grades:\n",
    "        row_col=[]\n",
    "        row_col.append(1)\n",
    "        for col in sheet.iter_cols(min_col=cols_max, min_row = start, max_row=rows_max):\n",
    "            for cell in col:\n",
    "                if cell.value == grade:\n",
    "                    row_col.append(cell.row)\n",
    "            grade_wise(row_col,grade)        \n",
    "        \n",
    "separating()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Simple Project  2\n",
    "In this project we are separating the records in different excel spreadsheet for different **Grade**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import xlrd\n",
    "import openpyxl\n",
    "from openpyxl import Workbook\n",
    "\n",
    "def separate_excel_file(t1,grade):\n",
    "    book = Workbook()\n",
    "    sheet = book.active\n",
    "    for row in t1:\n",
    "        sheet.append(row)\n",
    "    book.save('output/'+grade+'.xlsx')\n",
    "\n",
    "def grade_wise(row_num,grade):    \n",
    "    t1=[]\n",
    "    for j in row_num:\n",
    "        t=[]\n",
    "        for col in sheet.iter_cols(min_col=1,max_col=sheet.max_column, min_row = j,max_row=j ):\n",
    "            for cell in col:\n",
    "                t.append(cell.value)    \n",
    "        t1.append(tuple(t))                \n",
    "    separate_excel_file(t1,grade)\n",
    "    \n",
    "def separating():\n",
    "    df = pd.read_excel(\"Data/student_result.xlsx\", sheet_name='Sheet1')\n",
    "    grades=[]\n",
    "    for i in range(len(df[\"Grades\"])):\n",
    "        rec=df[\"Grades\"][i]\n",
    "        grades.append(rec)            \n",
    "    grades=list(set(grades))\n",
    "    start = 1\n",
    "    cols_max=sheet.max_column\n",
    "    rows_max=sheet.max_row\n",
    "    for grade in grades:\n",
    "        row_col=[]\n",
    "        row_col.append(1)\n",
    "        for col in sheet.iter_cols(min_col=cols_max, min_row = start, max_row=rows_max):\n",
    "            for cell in col:\n",
    "                if cell.value == grade:\n",
    "                    row_col.append(cell.row)\n",
    "            grade_wise(row_col,grade) \n",
    "        \n",
    "        \n",
    "separating()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reference\n",
    "    https://openpyxl.readthedocs.io/en/stable/\n",
    "    https://zetcode.com/python/openpyxl/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": 4
}