# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     name: python3
# ---

# + [markdown] id="view-in-github" colab_type="text"
# <a href="https://colab.research.google.com/github/ProfessorPatrickSlatraigh/CST2312/blob/main/XL_simple_worksheet.ipynb" target="_parent"><img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"/></a>

# + [markdown] id="FRTzh_KEUAlV"
# # **Excel - Create a Simple Worksheet**
#
# *using openpyxl*
#

# + [markdown] id="EQI9oif2UGEu"
#
#
# ---
#
#

# + [markdown] id="QJiGW_hbUKEF"
# Mount Google Drive.
#
# Workbooks and related files will be stored in the **excel** folder of **My Drive**.

# + colab={"base_uri": "https://localhost:8080/"} id="Ns8F3wKYTwOn" outputId="592b199c-1bc2-4fb4-816b-ea3f51c11a5f"
from google.colab import drive
drive.mount('/content/gdrive')

# + [markdown] id="aE-hniRjXUz0"
# Not necessary, but check the default directory on Colab and list the files in content/gdrive/MyDrive/excel.

# + colab={"base_uri": "https://localhost:8080/"} id="CSBuZwwFXeGA" outputId="9cf719b0-92d5-4c56-8a2d-bd382e63846a"
print("The current working directory is: ")
# ! pwd
print("\nThe contents of /content/gdrive/excel/ -")
# ! ls /content/gdrive/MyDrive/excel/ -a

# + [markdown] id="iaUsAkRIbX4F"
# Install **openpyxl** module for Python, if it is needed.
#
# Try the cell with the import of **openpyxl** before pip installing the module.

# + id="QwXtXrXnbSTW"
# You only need to try running this cell if the next cell throws an Exception
# ! pip install openpyxl

# + [markdown] id="HbpSHpHhUZFT"
# Import **openpyxl** module.

# + id="pCw4vqHnVxJ9"
try:
    import openpyxl
except:
    raise Exception("Unable to import pyxl module.  Check pip import openpyxl.")

# + [markdown] id="I-mUebMsciR2"
# At this point you should have access to the **excel** directory in the **content** folder of your **Colab** environment and you should have the **openpyxl** module imported.
#
# You are ready to work on **Excel** sheets and workbooks in **Python**!
#

# + [markdown] id="iejLR513c6lB"
#
#
# ---
#
#

# + [markdown] id="v-LIAJovc70n"
# Create an **openpyxl** Workbook object named **wb**
#
# Create an **openpyxl** worksheet object named **ws** as the active worksheet in the workbook **wb**.
#

# + colab={"base_uri": "https://localhost:8080/"} id="KJt-9Lb-d1JD" outputId="5ebcb790-3d67-4aa0-e161-1db9c616f9a8"
wb = openpyxl.Workbook()
ws = wb.active

print("The type of wb is: ", type(wb))
print("The type of ws is: ", type(ws))
print("The active worksheet is: ws in the workbook: wb.")

# + [markdown] id="zs9OUBPLeaPy"
# We have an active worksheet in a workbook, in memory now -- it has not been saved to a filename on disk yet!  

# + [markdown] id="wmzPp_S-eiIp"
# Create a matrix as a list of lists with some data which we will add to the ws worksheet.
#
# For this example, use the names of CUNY campuses and their street adddress, and borough in New York City.
#
# Store that data as a matrix of lists (rows) within a list (range).
#
# Each row list will have three elements, representing three columns in a sheet.
#
# The matrix_range list will begin with a list of the three column headings.
#

# + id="Hc--M0rVe0Xk"
matrix_range = [
["Campus","Address","Borough"], 
["Baruch College","55 Lexington Avenue","Manhattan"], 
["Borough of Mahattan Community College","199 Chambers Street","Manhattan"], 
["Bronx Community College","2155 University Avenue","Bronx"], 
["Brooklyn College","2900 Bedford Avenue","Brooklyn"], 
["College of Staten Island","2800 Victory Boulevard","Staten Island"], 
["Craig Newmark Graduate School of Journalism","219 W 40th Street","Manhattan"], 
["CUNY Graduate Center","365 Fifth Avenue","Manhattan"], 
["CUNY Graduate School of Public Health and Health Policy","55 W 125th Street","Manhattan"], 
["CUNY School of Labor and Urban Studies","25 W 23rd Street","Manhattan"], 
["CUNY School of Law","2 Court Square","Queens"], 
["CUNY School of Professional Studies","119 W 31st Street","Manhattan"], 
["Guttman Community College","50 W 40th Street","Manhattan"], 
["Hostos Community College","500 Grand Concourse","Bronx"], 
["Hunter College","695 Park Avenue","Manhattan"], 
["John Jay College of Criminal Justice","524 W 59th Street","Manhattan"], 
["Kingsborough Community College","2001 Oriental Boulevard","Brooklyn"], 
["LaGuardia Community College","31-10 Thomson Avenue","Queens"], 
["Lehman College","250 Bedford Park Blvd West","Bronx"], 
["Macaulay Honors College","35 W 67th Street","Manhattan"], 
["Medgar Evars College","1650 Bedford Avenue","Brooklyn"], 
["New York City College of Technology","300 Jay Street","Brooklyn"], 
["Queens College","65-30 Kissena Boulevard","Queens"], 
["Queensborough Community College","220-05 56th Avenue","Queens"], 
["The City College of New York","160 Convent Avenue","Manhattan"], 
["York College","94-20 Guy <NAME>oulevard","Queens"]
]

# + [markdown] id="Mi2E5TFVkuM2"
# Check the matrix.

# + colab={"base_uri": "https://localhost:8080/"} id="o3kzxYl2kxiG" outputId="6b28bebc-436f-4495-b7ab-9454183978ed"
print("The length of matrix_range is: ", len(matrix_range))

print("\nThe rows of matrix_range are: ")
i = 0
for row in matrix_range:
    print("Row #", i, "\t",row)
    i +=1


# + [markdown] id="jbSynRazlmjw"
# At this point we have an empty worksheet **ws** as the active worksheet in a workbook **wb** and a **matrix** of data in memory. 

# + [markdown] id="5O_sy7FVlzqC"
# Use the **openpyxl** **append** method on the object **ws** to append the rows of the object **matrix**.

# + id="BaS1FVyHmjWq"
for row in matrix_range:
    ws.append(row)

# + [markdown] id="0tHb4HJlnHWR"
# Give the active worksheet **ws** a title (the name on the worksheet tab) of "CampusLocations".
#
# *Note: this step could have been done before appending the data from **matrix**.  The worksheet name could be assigned at anytime when the worksheet is open.*
#

# + id="PCRkj-VknWjq"
ws.title = "CampusLocations"

# + [markdown] id="VBQ-p18Tn7-P"
# Now save the workbook **wb** to the **excel** folder in **/content/gdrive/MyDrive/** with the filename "CUNY_campuses.xlsx".
#
# *Note: the workbook **wb** contains only one worksheet (**ws**).*

# + id="Isyl-wNnoTnN"
wb.save("/content/gdrive/MyDrive/excel/CUNY_campuses.xlsx")


# + [markdown] id="ET1y_EPNpGKH"
# Go to **Google Drive** and the **excel** folder to open and inspect the new workbook in **Google Sheets** or download it to view it in **Excel**.

# + [markdown] id="X393PwatBI4-"
#
#
# ---
#
#

# + [markdown] id="ER-aExJODGUx"
# ### **Formatting Excel Sheets**
#

# + [markdown] id="twCCKx4gDQRO"
# We can adjust column widths and format our column headings.
#
# We can also create a named range for our table of data.
#

# + [markdown] id="0DN_8a1SDYSw"
#
#
# ---
#
#

# + [markdown] id="RsWivUrrDZMP"
# ### **Creating Excel Tables**

# + [markdown] id="u5eduoH9DgvC"
# We can add a sheet with a dynamic table based on our worksheet with data.

# + [markdown] id="yx8CXhb6Dp1i"
# Let's start by importing the objects we need from **openpyxl**.
#
# *   **Table** - for Excel tables, with filtering, formatting, etc.
# *   **TableStyleInfo** - to created a named style to be applied to a table
# *   **Image** - 
#
#

# + id="EobqShLGD9oY"
from openpyxl.worksheet.table import Table, TableStyleInfo

# + [markdown] id="TMGXe6-aEPl5"
# If we want to access an Excel workbook which already exists, stored on an accessible drive, we should import the **load_workbook** method from **openpyxl**.

# + id="HscMicbcFOLA"
from openpyxl import load_workbook

# + [markdown] id="iSkHH_1uFgKt"
# Open an existing Excel workbook and create an active worksheet **ws**.

# + id="NbCWlAjFFn2U"
wb = load_workbook("/content/gdrive/MyDrive/excel/CUNY_campuses.xlsx")
ws = wb.active

# + [markdown] id="bbOHxjIJF5-0"
# Create a table object named **tabl_1** with the display name '**Table1**'.
#
# *   Table names must be unique within a workbook. 
#
# *   By default tables are created with a header from the first row. 
#
# *   Table filters for all the columns must always contain strings. 
#
# *   Table headers and table column headings must always contain strings.
#

# + id="B4L6zgSXGIHA"
tabl_1 = Table(displayName="Table1", ref="A1:C26")

# + [markdown] id="SdhABwxIHAK5"
# Create a style in an object **style_basic** and give the style the name **TableStyleBasic** then apply that style the table **tabl_1**.
#
# Styles are managed using the the **TableStyleInfo object**. This allows you to stripe rows or columns and apply the different color schemes.
#

# + id="CkruNT2KHZX3"
style_clean = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn= False, showRowStripes=True, showColumnStripes=False)
tabl_1.tableStyleInfo = style_clean


# + [markdown] id="7qRNAKuWJUn_"
# Now the table can be added to the worksheet using **ws.add_table()**.
#
# *   Table must be added using ws.add_table() method to avoid duplicate names.
# *   Using this method ensures table name is unque through out defined names and all other table name.

# + id="hscptOvLNIZh"
ws.add_table(tabl_1)

# + [markdown] id="BuIEsZGUNR1r"
# Then the workbook can be saved as an **Excel** document.
#

# + id="McYeu9G2NWBr"
wb.save("/content/gdrive/MyDrive/excel/CUNY_campuses_withTable.xlsx")

# + id="rK3i4hT5R7cw"
# to see the table tabl_1 created in the worksheet ws
print(ws.tabl_1)

# + [markdown] id="tmttJeLgKgZ4"
#
#
# ---
#
#

# + [markdown] id="YLJg4u5cQu-4"
# **Functions and Attributes of Tables**

# + id="-sYf1wcoQ1Oz"
# CHECK THIS IN THE CURRENT VERSION OF OPENPYXL
# from openpyxl import tables

# + id="ZriLQcw4OXcb"
# to query the contents of the ws object
dir(ws)

# + [markdown] id="CeqGOhEWJw5e"
# **ws.tables** ???was??? is a dictionary-like object of all the tables in a particular worksheet.
#
# **ws._tables** is a list object of tables in a worksheet.
#
#

# + [markdown] id="fbAu9wt0L7oS"
# You can query all of the tables in a worksheet.

# + id="97_X6kNJKFRk"
# you can query all of the tables in a worksheet with the list ._tables
print(ws._tables)

# + [markdown] id="jx_f5nSEL37b"
# ??? You can get a table by its name or range

# + id="lQe-A2PpKiWW"
# CHECK THIS IN THE CURRENT VERSION OF OPENPYXL
# references a table by its name or a range
ws.tables["Table1"]
# or
ws.tables["A1:C26"]

# + [markdown] id="N0j8VRd8LUUX"
# You can get a count of the number of tables in a worksheet.

# + id="iL6WvKmqLXff"
# returns integer count of the number of tables in the worksheet
print(len(ws._tables))

# + [markdown] id="2K2aS3r0LD8e"
# You can get table names and their ranges of all tables in a worksheet.

# + id="opzzr58fK0eF"
# returns a list of table names and their ranges.
# print(ws.tables.items())
print(ws._tables)

# + [markdown] id="mS0VGvukMFQj"
# You can delete a table from a worksheet.

# + id="svJ_lvOkMIMh"
# CHECK THIS IN THE CURRENT VERSION OF OPENPYXL
# del ws.tables["Table1"]

StarcoderdataJupyterScriptsDedupFiltered