Excel/Excel_Get_dynamic_active_range.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "latin-packing",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-02-23T14:22:16.610471Z",
     "iopub.status.busy": "2021-02-23T14:22:16.610129Z",
     "iopub.status.idle": "2021-02-23T14:22:16.627784Z",
     "shell.execute_reply": "2021-02-23T14:22:16.626866Z",
     "shell.execute_reply.started": "2021-02-23T14:22:16.610384Z"
    },
    "papermill": {},
    "tags": []
   },
   "source": [
    "<img width=\"10%\" alt=\"Naas\" src=\"https://landen.imgix.net/jtci2pxwjczr/assets/5ice39g4.png?w=160\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "compressed-wilson",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "# Excel - Get dynamic active range\n",
    "<a href=\"https://app.naas.ai/user-redirect/naas/downloader?url=https://raw.githubusercontent.com/jupyter-naas/awesome-notebooks/master/Excel/Excel_Get_dynamic_active_range.ipynb\" target=\"_parent\"><img src=\"https://naasai-public.s3.eu-west-3.amazonaws.com/open_in_naas.svg\"/></a><br><br><a href=\"https://github.com/jupyter-naas/awesome-notebooks/issues/new?assignees=&labels=&template=template-request.md&title=Tool+-+Action+of+the+notebook+\">Template request</a> | <a href=\"https://github.com/jupyter-naas/awesome-notebooks/issues/new?assignees=&labels=bug&template=bug_report.md&title=Excel+-+Get+dynamic+active+range:+Error+short+description\">Bug report</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "religious-programmer",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Tags:** #excel #openpyxl #active-range #finance #snippet #dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a291197-14ba-4e65-95d0-7389081c7036",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Author:** [Florent Ravenel](https://www.linkedin.com/in/ACoAABCNSioBW3YZHc2lBHVG0E_TXYWitQkmwog/)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "distinguished-truth",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Input"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "numeric-mediterranean",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "potential-surfing",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-24T08:42:31.044171Z",
     "iopub.status.busy": "2022-02-24T08:42:31.043826Z",
     "iopub.status.idle": "2022-02-24T08:42:31.047187Z",
     "shell.execute_reply": "2022-02-24T08:42:31.046671Z",
     "shell.execute_reply.started": "2022-02-24T08:42:31.044136Z"
    },
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "from openpyxl.utils import get_column_letter"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f602b6d3-91df-4be8-a555-2f51b7391c38",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Setup your Excel parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9fffaceb-bbbe-4e8a-bc87-46fc53fd2245",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-24T08:42:32.265412Z",
     "iopub.status.busy": "2022-02-24T08:42:32.265122Z",
     "iopub.status.idle": "2022-02-24T08:42:32.268688Z",
     "shell.execute_reply": "2022-02-24T08:42:32.267972Z",
     "shell.execute_reply.started": "2022-02-24T08:42:32.265381Z"
    },
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "excel_path = \"Excel_Template.xlsx\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "registered-showcase",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aggressive-trustee",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Load Excel file and get active ws object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "continuous-melbourne",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-24T08:42:34.257051Z",
     "iopub.status.busy": "2022-02-24T08:42:34.256774Z",
     "iopub.status.idle": "2022-02-24T08:42:34.314377Z",
     "shell.execute_reply": "2022-02-24T08:42:34.313708Z",
     "shell.execute_reply.started": "2022-02-24T08:42:34.257023Z"
    },
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "wb = load_workbook(excel_path)\n",
    "ws = wb.active\n",
    "ws"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "tested-astrology",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Get active range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a75cce02-041f-4baa-9def-98725e3a8933",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-24T08:43:35.837106Z",
     "iopub.status.busy": "2022-02-24T08:43:35.836825Z",
     "iopub.status.idle": "2022-02-24T08:43:35.841465Z",
     "shell.execute_reply": "2022-02-24T08:43:35.840730Z",
     "shell.execute_reply.started": "2022-02-24T08:43:35.837077Z"
    },
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "def get_active_range(ws):\n",
    "    max_row = ws.max_row\n",
    "    max_col = get_column_letter(ws.max_column)\n",
    "    active_range = f\"A1:{max_col}{max_row}\"\n",
    "    return active_range\n",
    "\n",
    "active_range = get_active_range(ws)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "lonely-pacific",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-07-02T23:32:10.789097Z",
     "iopub.status.busy": "2021-07-02T23:32:10.788829Z",
     "iopub.status.idle": "2021-07-02T23:32:10.796900Z",
     "shell.execute_reply": "2021-07-02T23:32:10.796358Z",
     "shell.execute_reply.started": "2021-07-02T23:32:10.789033Z"
    },
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Output"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "linear-address",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Display result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "offensive-poetry",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2022-02-24T08:43:36.616903Z",
     "iopub.status.busy": "2022-02-24T08:43:36.616596Z",
     "iopub.status.idle": "2022-02-24T08:43:36.621159Z",
     "shell.execute_reply": "2022-02-24T08:43:36.620605Z",
     "shell.execute_reply.started": "2022-02-24T08:43:36.616872Z"
    },
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "active_range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2edfc2c-c0d7-4cec-a4aa-c9f9fccbfacb",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "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.9.6"
  },
  "naas": {
   "notebook_id": "c7ba2b76-00ca-4870-9e8b-3ffe46aab6c9",
   "notebook_path": "Excel/Excel_Get_dynamic_active_range.ipynb"
  },
  "papermill": {
   "default_parameters": {},
   "environment_variables": {},
   "parameters": {},
   "version": "2.3.3"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}