"""
Title: Case-Insensitive Text Replacement in Excel Files

Author: Sven Bosau
Website: https://pythonandvba.com
YouTube Channel: https://Youtube.com/@codingisfun

This script leverages the openpyxl library to perform case-insensitive replacement of text in Excel files (.xlsx, .xls). 
It traverses each cell in each worksheet of an Excel file, compares the lowercase content of the cell to the keys 
of a predefined dictionary, and if a match is found, replaces the cell's content with the corresponding value. 

The modified workbook is then saved in a dedicated output directory, ensuring all original casing of the text is 
maintained in the replacements. This provides a handy solution when you need to uniformly replace text across 
multiple Excel files irrespective of their case.

Please make sure the openpyxl library is installed in your Python environment before executing the script. 
You can install it using pip: `pip install openpyxl`.

Don't forget to customize the input file path and replacement dictionary to align with your requirements.
"""

from pathlib import Path
import openpyxl

BASE_DIR = Path(__file__).parent
INPUT_DIR = BASE_DIR / "Files"
OUTPUT_DIR = BASE_DIR / "Output"
OUTPUT_DIR.mkdir(parents=True, exist_ok=True)

replacement_pair = {"small business": "Small Market", "midmarket": "Midsize Market"}

files = list(INPUT_DIR.rglob("*.xls*"))
for file in files:
    wb = openpyxl.load_workbook(file)
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and cell.value.lower() in replacement_pair.keys():
                    cell.value = replacement_pair.get(cell.value.lower())
    wb.save(OUTPUT_DIR / f"{file.stem}_NEW.xlsx")