Program_9.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## `Q9.`##\n",
    "\n",
    "## `Write a Python program to plot the Line chart in MS Excel Sheet using XlsxWriter module to display the annual net income of the companies mentioned below.` ##\n",
    "<br>\n",
    "<img src=\"images\\9.jpg\" alt=\"lc\" align=\"middle\" height = 100, width = 250>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## -----------------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**`Microsoft Excel`** is a software program produced by Microsoft that allows users to organize, format and calculate data with formulas using a spreadsheet system. This software is part of the Microsoft Office suite and is compatible with other applications in the Office suite.  \n",
    "\n",
    "**Microsoft Excel terminology**  \n",
    "\n",
    "* **`Workbook`** `->`The workbook refers to an Excel spreadsheet file. The workbook houses all of the data that you have entered and allows you to sort or calculate the results. A workbook that is available to be viewed and edited by multiple users on a network is known as a Shared Workbook.  A single workbook is saved in a file with the `.xlsx` extension.\n",
    "<br><br>\n",
    "* **`Worksheet`** `->` Within the workbook is where you'll find documents called worksheets. Also known as spreadsheets, you can have multiple worksheets nestled in a workbook. Tabs at the bottom of the of the screen will indicate which of your worksheets you are currently working on. This is also known as an active worksheet or active sheet.\n",
    "<br><br>\n",
    "* **`Cell`** `->` A cell is a rectangle or block housed in a worksheet. Any data that you want to enter into your worksheet must be placed in a cell. Cells can be color coded, display text, numbers and the results of calculations, based on what you want to accomplish. An Active Cell is one that is currently opened for editing.\n",
    "<br><br>\n",
    "* **`Columns and Rows`** `->` Columns and Rows refer to how your cells are aligned. Columns are aligned vertically while rows are aligned horizontally.\n",
    "<br><br>\n",
    "* **`Column and Row headings`** `->` These headings are the lettered and numbered gray areas found just outside of columns and rows. Clicking on a heading will select the entire row or column. You can also alter the row height or column width using the headings.\n",
    "<br><br>\n",
    "* **`Workspace`** `->` Much like worksheets in a workbook, a workspace allows you to open numerous files simultaneously.\n",
    "<br><br>\n",
    "* **`Ribbon`** `->` Above the workbook is a section of command tabs called the Ribbon. A multitude of options are found behind each tab of the ribbon\n",
    "<br><br>\n",
    "* **`Cell Reference`** `->` A cell reference is a set of coordinates that identifies a specific cell. It's a combination of letters and numbers. `A5`, for example, would point to the cell located where column `A` and row `5` intersect.\n",
    "<br><br>\n",
    "* **`Cell Range`** `->` A Cell range is a collection of cells that have been identified as a group based on a variety of criteria. By using a `colon (:)` between cell references, Excel can determine the range, also known as an array. A range in a row, for example, could look like `A1:C1`, telling the formula to look at the cells in a row between `A1` and `C1`, while `B4:D9` would tell the formula to look at all cells in a box bounded by columns `B` and `D` and rows `4` and `9`.   \n",
    "  \n",
    "\n",
    "<img src=\"images\\10.jpeg\" alt=\"excel\" align=\"middle\" height = 350, width = 550>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## -----------------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**About XlsxWriter**  \n",
    "\n",
    "**`XlsxWriter`** is a Python module for writing files in the Excel 2007+ XLSX file format.  \n",
    "\n",
    "**`XlsxWriter`** can be used to write text, numbers, formulas and hyperlinks to multiple worksheets and it supports features such as formatting and many more, including:  \n",
    "\n",
    "* 100% compatible Excel XLSX files.\n",
    "* Full formatting.\n",
    "* Merged cells.\n",
    "* Defined names.\n",
    "* Charts.\n",
    "* Autofilters.\n",
    "* Data validation and drop down lists.\n",
    "* Conditional formatting.\n",
    "* Worksheet PNG/JPEG images.\n",
    "* Rich multi-format strings.\n",
    "* Cell comments.\n",
    "* Memory optimisation mode for writing large files.\n",
    "\n",
    "**Advantages:**\n",
    "\n",
    "* It supports more Excel features than any of the alternative modules.\n",
    "* It has a high degree of fidelity with files produced by Excel. In most cases the files produced are 100% equivalent to files produced by Excel.\n",
    "* It has extensive documentation, example files and tests.\n",
    "* It is fast and can be configured to use very little memory even for very large output files.\n",
    "\n",
    "**Disadvantages:**\n",
    "\n",
    "* It cannot read or modify existing Excel XLSX files."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## -----------------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Description of the methods used in the program**  \n",
    "\n",
    "`xlsxwriter.Workbook()` `->` The Workbook() constructor is used to create a new Excel workbook with a given filename.  \n",
    "\n",
    "`add_worksheet()` `->` The add_worksheet() method adds a new worksheet to a workbook. At least one worksheet should be added to a new workbook.   \n",
    "\n",
    "`add_format()` `->` The add_format() method can be used to create new Format objects which are used to apply formatting to a cell. You can either define the properties at creation time via a dictionary of property values or later via method calls.  \n",
    "\n",
    "`write_row()` `->` The write_row() method can be used to write a list of data in one go. Write a row of data starting from (row, col).    \n",
    "\n",
    "`write_column()` `->` The write_column() method can be used to write a list of data in one go. Write a column of data starting from (row, col).  \n",
    "\n",
    "`add_chart()` `->` This method is use to create a new chart object that can be inserted into a worksheet via the insert_chart() Worksheet method. For plotting the Line chart on an excel sheet, use add_chart() method with type `line` keyword argument of a workbook object. \n",
    "\n",
    "`insert_chart()` `->` This method can be used to insert a chart into a worksheet. A chart object is created via the Workbook add_chart() method where the chart type is specified. \n",
    "\n",
    "`add_series()` `->` Add a data series to a chart. In Excel a chart series is a collection of information that defines which data is plotted such as values, axis labels and formatting. The series options that can be set are:\n",
    "\n",
    "* `values:` This is the most important property of a series and is mandatory for every chart object. This option links the chart with the worksheet data that it displays. The data range can be set using a formula such as `\"=Sheet1!$B$2:$B$9\"` or a list with a sheetname, row and column such as `[\"Sheet1\", 1, 2, 8, 2].`\n",
    "<br><br>\n",
    "* `categories:` This sets the chart category labels. The category is more or less the same as the X axis. In most chart types the categories property is optional and the chart will just assume a sequential series from 1..n.\n",
    "<br><br>\n",
    "* `name:` Set the name for the series. The name is displayed in the formula bar. For non-Pie/Doughnut charts, it is also displayed in the legend. The name property is optional and if it isn't supplied it will default to Series 1..n. The name can also be a formula such as `=Sheet1!$B$1` or a list with a sheetname, row and column such as `[\"Sheet1\", 0, 2].`  \n",
    "\n",
    "`set_style()` `->`  Set the chart style type. The set_style() method is used to set the style of the chart to one of the 48 built-in styles available on the 'Design' tab in Excel. The style index number is counted from 1 on the top left. The default style is `2.`\n",
    "\n",
    "`set_title()` `->` The set_title() method is used to set properties of the chart title.\n",
    "\n",
    "`set_x_axis()` `->` The set_x_axis() method is used to set properties of the X axis.\n",
    "\n",
    "`set_y_axis()` `->` The set_y_axis() method is used to set properties of the Y axis.\n",
    "\n",
    "`close()` `->` The workbook close() method writes all data to the xlsx file and closes it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## -----------------------------------------------------------------------------------------------------------------------------"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import xlsxwriter module\n",
    "import xlsxwriter\n",
    "\n",
    "# Workbook() takes one, non-optional, argument\n",
    "# which is the filename that we want to create.\n",
    "# The file is present in the working directory.\n",
    "workbook = xlsxwriter.Workbook(\"line_chart.xlsx\")\n",
    "\n",
    "# The workbook object is then used to add new\n",
    "# worksheet via the add_worksheet() method.\n",
    "worksheet = workbook.add_worksheet()  # Defaults to Sheet1.\n",
    "\n",
    "# Create a new Format object to formats cells\n",
    "# in worksheets using add_format() method .\n",
    "\n",
    "# The properties of a cell that can be formatted include:\n",
    "# fonts, colors, patterns, borders, alignment and number formatting.\n",
    "# here we create bold format object .\n",
    "bold = workbook.add_format({\"bold\": 1})\n",
    "\n",
    "# create a data list .\n",
    "headings = [\"Year\", \"Microsoft\", \"Alphabet\", \"Amazon\"]\n",
    "\n",
    "data = [\n",
    "    [2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017],\n",
    "    [18.76, 23.15, 16.98, 21.86, 22.07, 12.19, 16.8, 21.2],\n",
    "    [8.376, 9.706, 10.179, 12.733, 14.136, 16.348, 19.478, 12.662],\n",
    "    [1.152, 0.631, 0.139, 0.274, 0.241, 0.596, 2.371, 3.033],\n",
    "]\n",
    "\n",
    "# Write a row of data starting from 'A1'\n",
    "# with bold format .\n",
    "worksheet.write_row(\"A1\", headings, bold)\n",
    "\n",
    "# Write a column of data starting from\n",
    "# 'A2', 'B2', 'C2' 'D2' respectively .\n",
    "worksheet.write_column(\"A2\", data[0])\n",
    "worksheet.write_column(\"B2\", data[1])\n",
    "worksheet.write_column(\"C2\", data[2])\n",
    "worksheet.write_column(\"D2\", data[3])\n",
    "\n",
    "# Create a chart object that can be added\n",
    "# to a worksheet using add_chart() method.\n",
    "\n",
    "# here we create a line chart object .\n",
    "chart = workbook.add_chart({\"type\": \"line\"})\n",
    "\n",
    "# Add a data series to a chart\n",
    "# using add_series method.\n",
    "\n",
    "# Configure the first series.\n",
    "# = Sheet1!$A$1 is equivalent to ['Sheet1', 0, 0].\n",
    "\n",
    "# note : spaces is not inserted in b / w\n",
    "# = and Sheet1, Sheet1 and !\n",
    "# if space is inserted it throws warning.\n",
    "chart.add_series(\n",
    "    {\n",
    "        \"name\": \"=Sheet1!$B$1\",\n",
    "        \"categories\": \"=Sheet1!$A$2:$A$9\",\n",
    "        \"values\": \"=Sheet1!$B$2:$B$9\",\n",
    "    }\n",
    ")\n",
    "\n",
    "# Configure a second series.\n",
    "# Note use of alternative syntax to define ranges.\n",
    "# [sheetname, first_row, first_col, last_row, last_col].\n",
    "chart.add_series(\n",
    "    {\n",
    "        \"name\": [\"Sheet1\", 0, 2],\n",
    "        \"categories\": [\"Sheet1\", 1, 0, 8, 0],\n",
    "        \"values\": [\"Sheet1\", 1, 2, 8, 2],\n",
    "    }\n",
    ")\n",
    "\n",
    "# Configure a third series.\n",
    "# Note use of alternative syntax to define ranges.\n",
    "# [sheetname, first_row, first_col, last_row, last_col].\n",
    "chart.add_series(\n",
    "    {\n",
    "        \"name\": [\"Sheet1\", 0, 3],\n",
    "        \"categories\": [\"Sheet1\", 1, 1, 8, 1],\n",
    "        \"values\": [\"Sheet1\", 1, 3, 8, 3],\n",
    "    }\n",
    ")\n",
    "\n",
    "# Add a chart title\n",
    "chart.set_title({\"name\": \"Company Profit Analysis\"})\n",
    "\n",
    "# Add x-axis label\n",
    "chart.set_x_axis({\"name\": \"Year\"})\n",
    "\n",
    "# Add y-axis label\n",
    "chart.set_y_axis({\"name\": \"Profit (in Billions)\"})\n",
    "\n",
    "# Set an Excel chart style.\n",
    "chart.set_style(4)\n",
    "\n",
    "# add chart to the worksheet with given\n",
    "# offset values at the top-left corner of\n",
    "# a chart is anchored to cell D2 .\n",
    "worksheet.insert_chart(\"D2\", chart, {\"x_offset\": 25, \"y_offset\": 10})\n",
    "\n",
    "# Finally, close the Excel file\n",
    "# via the close() method.\n",
    "workbook.close()"
   ]
  }
 ],
 "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.3"
  },
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "metadata": {
     "collapsed": false
    },
    "source": []
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}