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

# # OpenPyXL Library Basics

# Source:  [https://github.com/d-insight/code-bank.git](https://github.com/d-insight/code-bank.git)  
# License: [MIT License](https://opensource.org/licenses/MIT). See open source [license](LICENSE) in the Code Bank repository. 

# A Python library to read/write Excel 2010 xlsx/xlsm files. For more documentation, see: https://openpyxl.readthedocs.io/en/stable/

# -------------

# ## Install

# +
# #!python -m pip install openpyxl
# -

from openpyxl import load_workbook
from openpyxl import Workbook

# ## Files

path = "data/"
file = path + "SalesSampleData.xlsx"

# ## Getting Sheets from a Workbook

workbook = load_workbook(filename=file)
sheet = workbook.active

print(f'Worksheet names: {workbook.sheetnames}')
print(f'Active worksheet: {sheet}')
print(f'The title of the Active Worksheet is: {sheet.title}')

# ## Reading Cell Data

cell = sheet['B3']
print(cell.value)

sheet["A5"].value

# ## Iterating Over Rows and Columns

for cell in sheet['A']:
    print(cell.value)
    if cell.row == 10:
        break

# Alternative
for value in sheet.iter_rows(
        min_row=1, max_row=5,
        min_col=1, max_col=3,
        values_only=True,
    ):
    print(value)

# ## Writing Excel Spreadsheets

# Initiate a New Workbook
workbook = Workbook()

# Select the sheet to write on (the active one)
sheet = workbook.active

    sheet['A1'] = 'Hello'
    sheet['A2'] = 'from'
    sheet['A3'] = 'Me'

# Save the workbook
workbook.save(path + "new_workbook.xlsx")

# ## Adding and Removing Sheets

workbook.create_sheet()
print(workbook.sheetnames)

workbook.create_sheet(index=1, title='Second sheet')
print(workbook.sheetnames)

workbook.save(path + "new_workbook.xlsx")

# Let's delete some sheets

print(workbook.sheetnames)

del workbook['Second sheet']
print(workbook.sheetnames)

workbook.save(path + "new_workbook.xlsx")

# ## Adding and Deleting Rows and Columns

# insert a column before A (on the active sheet)
sheet.insert_cols(idx=1)
# insert 2 rows starting on the second row
sheet.insert_rows(idx=2, amount=2)

workbook.save(path + "new_workbook.xlsx")

# Delete column A
sheet.delete_cols(idx=1)
# delete 2 rows starting on the second row
sheet.delete_rows(idx=2, amount=2)

workbook.save(path + "new_workbook.xlsx")


# _________________________________________________________________________________________________________

# ### Functions

# +
def open_workbook(path):
    workbook = load_workbook(filename=path)
    print(f'Worksheet names: {workbook.sheetnames}')
    sheet = workbook.active
    print(sheet)
    print(f'The title of the Active Worksheet is: {sheet.title}')

def get_cell_info(path):
    workbook = load_workbook(filename=path)
    sheet = workbook.active
    print(sheet)
    print(f'The title of the Worksheet is: {sheet.title}')
    print(f'The value of {sheet["A5"].value=}')
    print(f'The value of {sheet["C6"].value=}')
    cell = sheet['B3']
    print(f'{cell.value=}')

def iterating_range(path):
    workbook = load_workbook(filename=path)
    sheet = workbook.active
    for cell in sheet['A']:
        print(cell)
        if cell.row == 10:
            break

def iterating_over_values(path):
    workbook = load_workbook(filename=path)
    sheet = workbook.active
    for value in sheet.iter_rows(
            min_row=1, max_row=5,
            min_col=1, max_col=3,
            values_only=True,
        ):
        print(value)

def create_workbook(path):
    workbook = Workbook()
    sheet = workbook.active
    sheet['A1'] = 'Hello'
    sheet['A2'] = 'from'
    sheet['A3'] = 'OpenPyXL'
    workbook.save(path)

def create_worksheets(path):
    workbook = Workbook()
    print(workbook.sheetnames)
    # Add a new worksheet
    workbook.create_sheet()
    print(workbook.sheetnames)
    # Insert a worksheet
    workbook.create_sheet(index=1,
                          title='Second sheet')
    print(workbook.sheetnames)
    workbook.save(path)

def remove_worksheets(path):
    workbook = Workbook()
    workbook.create_sheet()
    # Insert a worksheet
    workbook.create_sheet(index=1,
                          title='Second sheet')
    print(workbook.sheetnames)
    del workbook['Second sheet']            # workbook.remove(sheet1)
    print(workbook.sheetnames)
    workbook.save(path)

def inserting_cols_rows(path):
    workbook = Workbook()
    sheet = workbook.active
    sheet['A1'] = 'Hello'
    sheet['A2'] = 'from'
    sheet['A3'] = 'OpenPyXL'
    # insert a column before A
    sheet.insert_cols(idx=1)
    # insert 2 rows starting on the second row
    sheet.insert_rows(idx=2, amount=2)
    workbook.save(path)

def deleting_cols_rows(path):
    workbook = Workbook()
    sheet = workbook.active
    sheet['A1'] = 'Hello'
    sheet['B1'] = 'from'
    sheet['C1'] = 'OpenPyXL'
    sheet['A2'] = 'row 2'
    sheet['A3'] = 'row 3'
    sheet['A4'] = 'row 4'
    # Delete column A
    sheet.delete_cols(idx=1)
    # delete 2 rows starting on the second row
    sheet.delete_rows(idx=2, amount=2)
    workbook.save(path)

StarcoderdataJupyterScriptsDedupFiltered