import openpyxl
import pandas as pd
from openpyxl.worksheet.worksheet import Worksheet
from pandas import DataFrame

def write_to_excel(ws: Worksheet, start_row: int,
                   start_column: int, df: DataFrame,
                   index: bool = True, columns: bool = True) -> None:
    """
    Function which takes a worksheet and writes a table of data to it.

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    start_row : int
        The row to begin on
    start_column : int
        The column to start on
    df : DataFrame
        The data to write
    index : bool, optional
        Whether to write the index labels. The default is True.
    columns : bool, optional
        Whether to write the column labels. The default is True.

    """
    
    # If we want to include the index, reset the index on the dataframe first
    if index:
        write_data = df.reset_index().values.tolist()
    else:
        write_data = df.values.tolist()
    
    # If we want to include columns we need to add the columns as the first in the nested lists
    if columns:
        # If we already added an index, then we are going to want also include a blank space since
        # we don't want a label above the index
        if index:
            write_data = [[""]+list(df.columns)]+write_data
        else:
            write_data = [list(df.columns)]+write_data
    
    # For every value iterate through finding the location to place it in
    for row in range(len(write_data)):
        for column in range(len(write_data[0])):
            ws.cell(row=start_row+row,column=start_column+column).value = write_data[row][column]

# Pull the data
real_estate_returns = pd.read_csv("Tables/Real Estate Returns.csv", index_col=0)
# Rename the columns
real_estate_returns.columns = "Q"+real_estate_returns.columns

# Write the workbook
wb = openpyxl.Workbook()
ws = wb.active
write_to_excel(ws, 4, 2, real_estate_returns)

# Save it
wb.save("Workbooks/Economic Report.xlsx")

from openpyxl.styles.alignment import Alignment
from openpyxl.styles import Font

def create_axis_labels(ws: Worksheet):
    """
    Function which adds axis labels to the worksheet

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify

    """
    # Merge the cells and label it year
    ws.merge_cells(start_row=4, start_column=1, end_row=4+real_estate_returns.shape[0], end_column=1)
    ws.cell(row=4,column=1).value = "Year"
    
    # Add alignment and font
    ws.cell(row=4, column=1).alignment = Alignment(horizontal="center", vertical="center", textRotation=90)
    ws.cell(row=4, column=1).font = Font(size=16, bold=True, color="283747")
    
    # Merge the cells and label it quarter
    ws.merge_cells("B3:F3")
    ws.cell(row=3,column=2).value = "Quarter"
    
    # Add alignment and font
    ws.cell(row=3, column=2).font = Font(size=16, bold=True, color="283747")
    ws.cell(row=3, column=2).alignment = Alignment(horizontal="center", vertical="center")

def create_real_estate_report(ws: Worksheet, real_estate_returns: DataFrame):
    """
    Function to write the real estate report
    
    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    real_estate_returns : DataFrame
        The returns data for real estate

    """
    
    # Write the table
    write_to_excel(ws, 4, 2, real_estate_returns)
    
    # Create the axis labels
    create_axis_labels(ws)
    
wb = openpyxl.Workbook()
ws = wb.active
create_real_estate_report(ws, real_estate_returns)
wb.save("Workbooks/Economic Report.xlsx")

# Examples of number formats
wb = openpyxl.Workbook()
ws = wb.active

# Set the same numbers for three cells
ws.cell(row=1,column=1).value = 5.5542
ws.cell(row=2,column=1).value = 5.5542
ws.cell(row=3,column=1).value = 5.5542

# An integer format
ws.cell(row=1,column=1).number_format = '0'

# One decimal format
ws.cell(row=2,column=1).number_format = '0.0'

# A percentage format with one decimal
ws.cell(row=3,column=1).number_format = '0.0%'

wb.save("Workbooks/Example 12.xlsx")

def create_number_formatting(ws: Worksheet, start_row: int,
                             start_column: int, vertical_length: int,
                             horizontal_length: int, num_format: str) -> None:
    """
    Function to change the number formatting of a certain area of an excel sheet.

    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    start_row : int
        The row to start changing the number formatting at
    start_column : int
        The column to start changing the number formatting at
    vertical_length : int
        How large the area is vertically
    horizontal_length : int
        How large the area is horizontally
    num_format : str
        The number format to apply

    """
    
    # Iterate through rows and columns and set the number format
    for row in range(vertical_length):
        for column in range(horizontal_length):
            ws.cell(row=start_row+row,column=start_column+column).number_format = num_format
            
def create_real_estate_report(ws: Worksheet, real_estate_returns: DataFrame):
    """
    Function to write the real estate report
    
    Parameters
    ----------
    ws : Worksheet
        The worksheet to modify
    real_estate_returns : DataFrame
        The returns data for real estate

    """
    
    # Write the table
    write_to_excel(ws, 4, 2, real_estate_returns)
    
    # Create the axis labels
    create_axis_labels(ws)
    
    # Create percentage number formatting
    create_number_formatting(ws, 5, 3, real_estate_returns.shape[0], real_estate_returns.shape[1], "0.0%")
    
wb = openpyxl.Workbook()
ws = wb.active
create_real_estate_report(ws, real_estate_returns)
wb.save("Workbooks/Economic Report.xlsx")