<jupyter_start><jupyter_text>**Excel - Create a Simple Worksheet***using openpyxl* --- Mount Google Drive.Workbooks and related files will be stored in the **excel** folder of **My Drive**.<jupyter_code>from google.colab import drive
drive.mount('/content/gdrive')<jupyter_output>Mounted at /content/gdrive<jupyter_text>Not necessary, but check the default directory on Colab and list the files in content/gdrive/MyDrive/excel.<jupyter_code>print("The current working directory is: ")
! pwd
print("\nThe contents of /content/gdrive/excel/ -")
! ls /content/gdrive/MyDrive/excel/ -a<jupyter_output>The current working directory is: 
/content

The contents of /content/gdrive/excel/ -
 CUNY_campuses2.xlsx	     CUNY_campuses.xlsx
 CUNY_campuses_BACKUP.xlsx  'New Microsoft Excel Worksheet.xlsx'<jupyter_text>Install **openpyxl** module for Python, if it is needed.Try the cell with the import of **openpyxl** before pip installing the module.<jupyter_code># You only need to try running this cell if the next cell throws an Exception
! pip install openpyxl<jupyter_output><empty_output><jupyter_text>Import **openpyxl** module.<jupyter_code>try:
    import openpyxl
except:
    raise Exception("Unable to import pyxl module.  Check pip import openpyxl.")<jupyter_output><empty_output><jupyter_text>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**! --- Create an **openpyxl** Workbook object named **wb**Create an **openpyxl** worksheet object named **ws** as the active worksheet in the workbook **wb**.<jupyter_code>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.")<jupyter_output>The type of wb is:  <class 'openpyxl.workbook.workbook.Workbook'>
The type of ws is:  <class 'openpyxl.worksheet.worksheet.Worksheet'>
The active worksheet is: ws in the workbook: wb.<jupyter_text>We have an active worksheet in a workbook, in memory now -- it has not been saved to a filename on disk yet!   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.<jupyter_code>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"], 
["<NAME> 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 Gu<NAME> Boulevard","Queens"]
]<jupyter_output><empty_output><jupyter_text>Check the matrix.<jupyter_code>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<jupyter_output>The length of matrix_range is:  26

The rows of matrix_range are: 
Row # 0 	 ['Campus', 'Address', 'Borough']
Row # 1 	 ['Baruch College', '55 Lexington Avenue', 'Manhattan']
Row # 2 	 ['Borough of Mahattan Community College', '199 Chambers Street', 'Manhattan']
Row # 3 	 ['Bronx Community College', '2155 University Avenue', 'Bronx']
Row # 4 	 ['Brooklyn College', '2900 Bedford Avenue', 'Brooklyn']
Row # 5 	 ['College of Staten Island', '2800 Victory Boulevard', 'Staten Island']
Row # 6 	 ['Craig Newmark Graduate School of Journalism', '219 W 40th Street', 'Manhattan']
Row # 7 	 ['CUNY Graduate Center', '365 Fifth Avenue', 'Manhattan']
Row # 8 	 ['CUNY Graduate School of Public Health and Health Policy', '55 W 125th Street', 'Manhattan']
Row # 9 	 ['CUNY School of Labor and Urban Studies', '25 W 23rd Street', 'Manhattan']
Row # 10 	 ['CUNY School of Law', '2 Court Square', 'Queens']
Row # 11 	 ['CUNY School of Professional Studies', '119 W 31st Street', 'Manhattan']
Row # 12 	 ['Guttma[...]<jupyter_text>At this point we have an empty worksheet **ws** as the active worksheet in a workbook **wb** and a **matrix** of data in memory.  Use the **openpyxl** **append** method on the object **ws** to append the rows of the object **matrix**.<jupyter_code>for row in matrix_range:
    ws.append(row)<jupyter_output><empty_output><jupyter_text>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.*<jupyter_code>ws.title = "CampusLocations"<jupyter_output><empty_output><jupyter_text>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**).*<jupyter_code>wb.save("/content/gdrive/MyDrive/excel/CUNY_campuses.xlsx")<jupyter_output><empty_output><jupyter_text>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**. ---  **Formatting Excel Sheets** We can adjust column widths and format our column headings.We can also create a named range for our table of data. ---  **Creating Excel Tables** We can add a sheet with a dynamic table based on our worksheet with data. 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** -<jupyter_code>from openpyxl.worksheet.table import Table, TableStyleInfo<jupyter_output><empty_output><jupyter_text>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**.<jupyter_code>from openpyxl import load_workbook<jupyter_output><empty_output><jupyter_text>Open an existing Excel workbook and create an active worksheet **ws**.<jupyter_code>wb = load_workbook("/content/gdrive/MyDrive/excel/CUNY_campuses.xlsx")
ws = wb.active<jupyter_output><empty_output><jupyter_text>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.<jupyter_code>tabl_1 = Table(displayName="Table1", ref="A1:C26")<jupyter_output><empty_output><jupyter_text>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.<jupyter_code>style_clean = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn= False, showRowStripes=True, showColumnStripes=False)
tabl_1.tableStyleInfo = style_clean<jupyter_output><empty_output><jupyter_text>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.<jupyter_code>ws.add_table(tabl_1)<jupyter_output><empty_output><jupyter_text>Then the workbook can be saved as an **Excel** document.<jupyter_code>wb.save("/content/gdrive/MyDrive/excel/CUNY_campuses_withTable.xlsx")
# to see the table tabl_1 created in the worksheet ws
print(ws.tabl_1)<jupyter_output><empty_output><jupyter_text>--- **Functions and Attributes of Tables**<jupyter_code># CHECK THIS IN THE CURRENT VERSION OF OPENPYXL
# from openpyxl import tables
# to query the contents of the ws object
dir(ws)<jupyter_output><empty_output><jupyter_text>**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. You can query all of the tables in a worksheet.<jupyter_code># you can query all of the tables in a worksheet with the list ._tables
print(ws._tables)<jupyter_output><empty_output><jupyter_text>??? You can get a table by its name or range<jupyter_code># 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"]<jupyter_output><empty_output><jupyter_text>You can get a count of the number of tables in a worksheet.<jupyter_code># returns integer count of the number of tables in the worksheet
print(len(ws._tables))<jupyter_output><empty_output><jupyter_text>You can get table names and their ranges of all tables in a worksheet.<jupyter_code># returns a list of table names and their ranges.
# print(ws.tables.items())
print(ws._tables)<jupyter_output><empty_output><jupyter_text>You can delete a table from a worksheet.<jupyter_code># CHECK THIS IN THE CURRENT VERSION OF OPENPYXL
# del ws.tables["Table1"]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11553189502194826, 'fraction_numerical': 0.016251050714485852, 'mean_word_length': 5.908387096774193, 'pattern_counts': {'":': 0, '<': 93, '<?xml version=': 0, '>': 93, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '526756', 'license': 'MIT', 'n_tokens_mistral': 3456, 'n_tokens_neox': 3236, 'n_words': 1216, 'path': 'XL_simple_worksheet.ipynb', 'repo_name': 'ProfessorPatrickSlatraigh/CST2312', 'size': '10719'}