initialiser.py

import os

import sys

# Add the parent directory of 'columns' to sys.path
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), "../")))

import pandas as pd
from openpyxl.formatting.rule import FormulaRule
from openpyxl.reader.excel import load_workbook
from openpyxl.styles import PatternFill, Side, Border, Alignment

import columns
from columns import DataType
from columns import Types

"""
Initialise an empty Excel workbook with data validation rules, formatting, and dropdowns.
Generates an empty DataSheet.xlsx with column headers based on types.csv.
Places generated DataSheet.xlsx in the same directory as the script.
"""
class Initialiser:
    def __init__(self, patientdata_file_path, types_filepath):
        """
        Initialize the Validator object with a CSV file path.

        Args:
            csv_folder_path (str): The path to the CSV file to be validated.
        """
        self.csv_folder_path: str = patientdata_file_path
        self.types_filepath: str = types_filepath
        self.df: pd.DataFrame = None
        self.wb = None
        self.ws = None
        self.wb_name = "DataSheet.xlsx"
        self.ws_name = "Sheet1"
        self.types: columns.Types

    def initialise(self):
        """
        Do preliminary initialisation
        """
        try:
            self.types = Types(self.types_filepath)
            print(f"Types '{self.types_filepath}' read successfully!")
        except Exception as e:
            print(f"Error reading types file '{self.types_filepath}': {e}")

    def readCSV(self):
        """
        - Generates an empty DataFrame with column headers based on types.csv
        - Creates an empty Excel workbook with the correct column structure
        - Initializes the Excel workbook and worksheet
        """
        try:
            # Generate a mapping from csv_name to datasheet_name
            csv_to_datasheet_map = {}
            for category in self.types.categories:
                for field in category.fields:
                    csv_to_datasheet_map[field.csv_name] = field.datasheet_name

            # Create an empty DataFrame with all datasheet column names
            datasheet_columns = [field.datasheet_name for category in self.types.categories for field in category.fields]
            self.df = pd.DataFrame(columns=datasheet_columns)
            
            print("Empty DataFrame created with column headers based on types.csv")

            # Save empty DataFrame to an Excel file
            self.df.to_excel(self.wb_name, sheet_name=self.ws_name, index=False)

            # Load and store the workbook
            self.wb = load_workbook(self.wb_name)

            if self.ws_name not in self.wb.sheetnames:
                raise ValueError(f"Sheet '{self.ws_name}' does not exist in the workbook.")

            self.ws = self.wb[self.ws_name]
            print(f"Empty Excel workbook '{self.wb_name}' created successfully!")

        except Exception as e:
            print(f"Error creating empty Excel file: {e}")

    def applyValidationRules(self):
        """
        - Apply data validation rules to an Excel workbook.
        - Disable column renaming #TODO
        - Also adds "Example" Row to workbook #TODO
        """
        try:
            # Add the dv rules listed in DataType enums to the worksheet
            for dt in DataType:
                if dt.validation != None:
                    self.ws.add_data_validation(dt.validation)

            # for each category in types, for each field in the category, apply the validation rule to its datasheet range
            for category in self.types.categories:
                for field in category.fields:
                    if field.datatype.validation != None:
                        field.datatype.validation.add(field.datasheet_range)

            self.wb.save(self.wb_name)
            print("Validation rules applied and saved successfully!")

        except Exception as e:
            print(f"Error applying validation rules: {e}")

    def applyFormatting(self):
        """
        Apply formatting rules to an Excel workbook.
        - Applies a border to first 500 rows
        - Colour first row of cells according to their category
        - Pin first row, and first 3 columns of cells
        - Resizes column width to fit the content.
        - First Row: Triples height, centres text
        - Bold required fields #TODO
        """

        # Apply border to all cells
        border_style = Border(
            left=Side(border_style="thin", color="000000"),
            right=Side(border_style="thin", color="000000"),
            top=Side(border_style="thin", color="000000"),
            bottom=Side(border_style="thin", color="000000")
        )

        for row in self.ws.iter_rows(min_row=1, max_row=500, min_col=1, max_col=self.ws.max_column):
            for cell in row:
                cell.border = border_style
        print("Borders applied successfully!")

        # Colours first row of cells according to their category
        for category in self.types.categories:
            for field in category.fields:
                custom_fill = PatternFill(start_color=field.colour, end_color=field.colour, fill_type="solid")
                start_cell, end_cell = field.datasheet_range.split(":")
                # Extract the column letters from start_cell and adjust the row to 1
                column_letter = ''.join([char for char in start_cell if char.isalpha()])
                start_cell_adjusted = f"{column_letter}1"

                # Apply the color to the adjusted start cell (now row 1)
                self.ws[start_cell_adjusted].fill = custom_fill
        print("Cell colours applied successfully!")

        # Pin first row, and first 3 columns of cells
        self.ws.freeze_panes = "D2"  # Freeze everything above row 2 and to the left of column D

        # First Row: Triples height, centres text
        for col in self.ws.columns:
            max_length = 0
            column = col[0].column_letter
            for cell in col:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(cell.value)
                except:
                    pass
            adjusted_width = min(40, max_length + 2)
            self.ws.column_dimensions[column].width = adjusted_width

        self.ws.row_dimensions[1].height = 70
        for cell in self.ws[1]:  # Loop through each cell in the first row
            cell.alignment = Alignment(wrap_text=True, horizontal="center", vertical="center")


        self.wb.save(self.wb_name)
        print("Formatting applied and saved successfully!")

    def applyConditionalFormatting(self):
        """
        Apply conditional formatting to highlight empty required cells in rows where
        any cell in the same category is filled.
        """
        red_fill = PatternFill(start_color="ea9999", end_color="ea9999",
                               fill_type="solid")  # Red fill for required fields

        for category in self.types.categories:  # Iterate through each category
            # Collect column letters for required fields in this category
            required_columns = [
                ''.join(filter(str.isalpha, f.datasheet_range.split(":")[0]))
                for f in category.fields if f.required  # Only include required fields
            ]
            category_columns = [
                ''.join(filter(str.isalpha, f.datasheet_range.split(":")[0]))
                for f in category.fields
            ]

            for row_number in range(2, 301): # start and end rows
                # Mark a cell red only if it is empty
                for col in required_columns:
                    formula = f'=ISBLANK(${col}{row_number})'
                    rule = FormulaRule(formula=[formula], fill=red_fill)
                    self.ws.conditional_formatting.add(f"{col}{row_number}", rule)


        self.wb.save(self.wb_name)
        print("Conditional formatting applied successfully!")

    def rename_columns(self):
        """
        This method is no longer needed since we generate empty sheets directly with datasheet names.
        Kept for backward compatibility but does nothing.
        """
        # No-op: columns are already in datasheet format when creating empty DataFrame
        pass

if __name__ == "__main__":
    # types_file_path is still needed to define the schema
    types_file_path = "../types.csv"
    # patientdata_file_path is no longer used but kept for backward compatibility
    patientdata_file_path = "../cron/backups"  # Not used anymore, kept for compatibility

    # delete the existing DataSheet.xlsx file
    try:
        os.remove("DataSheet.xlsx")
    except:
        pass

    initialiser = Initialiser(patientdata_file_path, types_file_path)
    initialiser.initialise()
    initialiser.readCSV()
    initialiser.applyValidationRules()
    initialiser.applyFormatting()
    initialiser.applyConditionalFormatting()