Warning: Can not open [/home/conf/public_html/cgi-bin/show_python.log]. Ignore Read parameters from a text file.

Args:
param_path (str): The path to the parameter file.

Returns:
dict: A dictionary containing the parameters.

Download script from xlsx2python.py
Related files:

import os
import sys
import openpyxl as px
import csv
import re


def_replace = {
        "EXP": "exp",
        "LOG": "log10",
        "LN": "log",
        "SQRT": "sqrt",
        "SIN": "sin",
        "COS": "cos",
        "TAN": "tan",
        "ASIN": "asin",
        "ACOS": "acos",
        "ATAN": "atan",
        "ABS": "abs",
        "INT": "int",
        "FLOAT": "float",
}

script_path = 'temp.py'
fp = None
log_path = 'input.log'
log = None
cmd_path = None

input_path = None
isheet = None


def pint(s, def_val = None):
    try:
        return int(s)
    except:
        return def_val


args = sys.argv
if len(args) > 1:
    input_path = args[1]
if len(args) > 2:
    isheet = pint(args[2], def_val = None)
if len(args) > 3:
    cmd_path = args[3]

if input_path is None:
    print()
    print("Error: input file is not specified")
    sys.exit()

if not input_path.endswith(".xlsx"):
    print()
    print(f"Error: input file [{input_path}] must be .xlsx")
    sys.exit()


def help(param_path):
    print()
    print("@indent=number_of_spaces: set the number of indent spaces. default: 0")
    print("@use_validate=[0|1]: Use validate function() instead of print() function")
    print("const var_name_cell value_cell commnet_cells: print python eq for constant definition and set parameter")
    print("cell_number [> var_name]: cell_number is like A1. read the cell and convert. assign to var_name if given")
    print("open: open script file (is opened at the start)")
    print("close: close script file")
    print("w str: write str to script file")
    print("var=val: set val to var for replacement")
    print("del var: delete parameter var")
    print(f"save: save parameters to [{param_path}]")
    print("show: show parameters")
    print("read execl_path isheet: read sheet isheet from excel_path")
    print("delf path: delete file 'path'")
    print("import path: read commands from file 'path' and run them")
    print("reads path: read commands from file 'path'")
    print("run command: run 'command'")
    print("help: show this help")
    print("quit|exit: exit from this program")

def read_params(param_path):
    """
    Read parameters from a text file.

    Args:
        param_path (str): The path to the parameter file.

    Returns:
        dict: A dictionary containing the parameters.
    """
    print(f"Read parameters from [{param_path}]")

    d = {}
    if os.path.isfile(param_path):
        f = open(param_path, "r")
    else:
        return d

    for line in f:
        aa = line.split("=")
        if len(aa) >= 2:
            key = aa[0].upper()
            val = aa[1]
            d[key] = val.strip()

    f.close()

    show_parameters(d)

    return d

def show_parameters(d):
    print("Parameters:")
    for key, val in sorted(d.items()):
        print(f" {key} = {val}")

def save(param_path, d):
    print()
    print(f"Save parameters to [{param_path}]")
    with open(param_path, "w") as f:
        for key, val in d.items():
            try:
                f.write(f"{key}={val}\n")
            except:
                pass
    show_parameters(d)

def xlsx2csv(input_path, output_path, isheet = None):
    """
    Convert an Excel file to a CSV file.

    Parameters:
    input_path (str): The path to the input Excel file.
    output_path (str): The path to the output CSV file.
    isheet (int, optional): The index of the sheet to convert. If not specified, the active sheet will be used.

    Returns:
    Worksheet: The worksheet object that was converted to CSV.
    """
    print(f"Read Excel file from [{input_path}]")

    # Load the workbook
    wb = px.load_workbook(input_path, data_only = True)
    if isheet is not None:
        ws_value = wb.worksheets[isheet]
    else:
        ws_value = wb.active
    # Close the workbook
    wb.close()

    # Load the workbook
    wb = px.load_workbook(input_path, data_only = False)
    if isheet is not None:
        ws_eq = wb.worksheets[isheet]
    else:
        ws_eq = wb.active
    # Create a new CSV file
    with open(output_path, 'w', newline='', encoding = 'utf-8') as f:
        writer = csv.writer(f)
        # Write the data from the worksheet to the CSV file
        for row in ws_eq.iter_rows(values_only=True):
            writer.writerow(row)

    # Close the CSV file
    f.close()
    # Close the workbook
    wb.close()

    return ws_value, ws_eq

def convert(eq, replace_dict):
    eq = eq.replace("$", "")
    for key, value in def_replace.items():
        eq = re.sub(key, value, eq, flags = re.IGNORECASE)

    for key, value in replace_dict.items():
        key = re.escape(key)
        eq = re.sub(rf"(?<=[^\d\w]){key}(?=[^\d\w])", value, eq)
#        eq = re.sub(rf"(?<=\W^){key}(?=\W$)", value, eq)
        eq = re.sub(rf"(?<=[^\d\w]){key}$", value, eq)
        eq = re.sub(rf"^{key}(?<=[^\d\w])", value, eq)

    eq = re.sub(r"\s*(?<=[^(\de)])([\+\-\*\/])\s*", r" \1 ", eq)
    eq = eq.replace('^', '**')
    eq = eq.lstrip("=")

    return eq

def analyze_const(ws, args):
    if len(args) < 2:
       return None, None, None, None

    try:
        name_cell = ws[args[0]].value
    except:
        return None, None, None, None

    m = re.match(r'^\s*(\S+)([\s\[\()]+)(\S*)$', f"{name_cell}")
    if m:
        aa = m.groups()
        var_name = aa[0]
        try:
            c = ws[aa[2]].value
        except:
            c = ''
        comments = aa[1] + ' ' + aa[2] + ' ' + c
    else:
        var_name = name_cell
        comments = ''
    if ' ' in var_name:
        aa = var_name.split(' ', 1)
        if len(aa) >= 2:
            var_name = aa[0]
            comments = aa[1] + ' ' + comments

    try:
        val = ws[args[1]].value
    except:
        return None, None, None, None

    if len(args) > 2:
        for s in args[2:]:
            try:
                comments += ws[s].value
            except:
                continue

    return f"{var_name}".strip(), f"{val}".strip(), f"{args[1]}".upper(), f"{comments}".strip()

def open_script():
    global fp

    fp = open(script_path, 'a', encoding = 'utf-8')

def open_log():
    global log

    log = open(log_path, 'a', encoding = 'utf-8')

def close_script():
    global fp

    if fp:
        fp.close()
    fp = None

def add_program(replace_dict, s, fp = None):
    sindent = ' ' * pint(replace_dict.get('@INDENT', '0'), 0)
    print(sindent + s)
    if fp:
        fp.write(sindent + s + "\n")
#        fp.flush()
        close_script()
        open_script()

def add_validation(var_name, cell_name, cell_value, replace_dict, fp):
    use_validation = replace_dict.get('@USE_VALIDATION', 0)
    if use_validation:
        add_program(replace_dict, f'validate({var_name}, {cell_value}, "{cell_name}")', fp)
    else:
        add_program(replace_dict, f'print(f"{var_name} = ' + '{' + var_name + '}' + f' ({cell_name}: {cell_value})' + '")', fp)

def read_command_file(cmd_path, param_path, output_path, replace_dict, ws_eq, ws_value):
    if cmd_path:
        print()
        print(f"Read commands from [{cmd_path}]")
        fp = open(cmd_path)
        while 1:
            line = fp.readline()
            if not line:
                fp.close()
                break

            print(f"<{line.strip()}")
            ret = process_command(False, line, param_path, output_path, replace_dict, ws_eq, ws_value)

def process_command(save_log, line, param_path, output_path, replace_dict, ws_eq, ws_value):
        line = line.replace('>', ' > ')
        if save_log and log:
            log.write(line + '\n')

        aa = line.split()

        if aa is None or len(aa) == 0:
            return 1
        if aa[0].lower() == "quit" or aa[0].lower() == "exit":
            return 0

        if aa[0].lower() == "help" or aa[0] == '?':
            help(param_path)
        elif aa[0].lower() == "save":
            save(param_path, replace_dict)
        elif aa[0].lower() == "read":
            if len(aa) > 1:
                input_path = aa[1]
                param_path = input_path.replace(".xlsx", ".in")
                output_path = input_path.replace(".xlsx", ".csv")
            if len(aa) > 2:
                isheet = int(aa[2])

            ws_value, ws_eq = xlsx2csv(input_path, output_path, isheet)
        elif aa[0].lower() == 'close':
            close_script()
        elif aa[0].lower() == 'open':
            open_script()
        elif aa[0].lower() == "delf":
            if len(aa) > 1:
                del_path = aa[1]
                print(f"Delete [{del_path}]")
                if not os.path.isfile(del_path):
                    print(f" Warning: Could not delete [{del_path}]")
        elif aa[0].lower() == "reads":
            if len(aa) > 1:
                read_command_file(aa[1], param_path, output_path, replace_dict, ws_eq)
        elif aa[0].lower() == "import":
            if len(aa) > 1:
                import_path = aa[1]
                if os.path.isfile(import_path):
                    with open(import_path, "r") as fimp:
                        print(f"Import from [{import_path}]")
                        for line in fimp:
                            line = line.rstrip()
#                            print(f"<{line}")
                            add_program(replace_dict, line, fp)
                else:
                    print(f"Warning: No import file given")
        elif aa[0].lower() == "show":
            show_parameters(replace_dict)
        elif aa[0].lower() == 'run':
            cmd = f"python {script_path}"
            print(f"Run [{cmd}]")
            os.system(cmd)
        elif aa[0].lower() == 'w':
            aa2 = line.split(' ', 1)
            if len(aa2) >= 2:
                add_program(replace_dict, aa2[1], fp)
            else:
                add_program(replace_dict, "", fp)
        elif '=' in aa[0]:
            key, val = aa[0].split("=")
            key = key.upper()
            print(f"#set {key} = {val}")
            replace_dict[key] = val
        elif aa[0] == 'del':
            if len(aa) >= 2:
                key = aa[1].upper()
                if key in replace_dict.keys():
                    print(f"delete parameter [{key}]")
                    del replace_dict[key]
        elif 'const' in aa[0].lower():
            var_name, val, cell_name, comment = analyze_const(ws_eq, aa[1:])
            if var_name is not None:
                add_program(replace_dict, f"# {aa[1]}={aa[2]} ({val})", fp)
                val = convert(val, replace_dict)
                add_program(replace_dict, f"{var_name} = {val} # {comment}", fp)
                print(f"# set {cell_name} = {var_name}")
                replace_dict[cell_name] = var_name
            else:
                print(f"Warning: not enough arguments or invalid cell(s) for const")
        else:
            cell = aa[0].strip().upper()

            if len(aa) >= 3 and aa[1] == '>':
                var_name = aa[2]
            else:
                var_name = None

            if cell.isalpha():
                print(f" column data in [{cell}] ")
                try:
                    col = px.utils.column_index_from_string(cell) - 1
                except:
                    print(f"Error: Invalid word for Excel column [{cell}]")
                    return 1

                for i, row in enumerate(ws_eq.iter_rows(min_col=col+1, max_col=col+1, max_row=ws_eq.max_row, values_only=True)):
                    print(f"{cell}{i+1}: row[0]")
            elif cell.isdigit():
                print(f" row data in [{cell}] ")
                for row in ws_eq.iter_rows(min_row=int(cell), max_row=int(cell), values_only=True):
                    print(row)
            else:
                try:
                    eq = ws_eq[cell].value
                except:
                    print(f"Warning: cell [{cell}] is not a valid index for worksheet")
                    return 1

                try:
                    cell_value = ws_value[cell].value
                except:
                    cell_value = eq

#                print(f"Convert the cell data in [{cell}]")
                eq = f"{eq}"
                add_program(replace_dict, f"# {cell}: {eq}", fp)
                if eq is None:
                    return 1
                eq = convert(eq, replace_dict)
                if var_name is None:
                    print(f"# => {eq}")
                else:
                    add_program(replace_dict, f"{var_name} = {eq}", fp)
                    add_validation(var_name, cell.upper(), cell_value, replace_dict, fp)

                    print(f"# set {cell} = {var_name}")
                    replace_dict[cell] = var_name

        return 1

def main():
    global input_path


    param_path = input_path.replace(".xlsx", ".in")
    output_path = input_path.replace(".xlsx", ".csv")

    ws_value, ws_eq = xlsx2csv(input_path, output_path)
    replace_dict = read_params(param_path)

    print(f"Open [{script_path}] to append")
    open_script()
    print(f"Open [{log_path}] to append")
    open_log()
    log.write("\n")

    read_command_file(cmd_path, param_path, output_path, replace_dict, ws_eq, ws_value)

    while True:
        line = input('>')
        ret = process_command(True, line, param_path, output_path, replace_dict, ws_eq, ws_value)
        if ret == 0:
            break

if __name__ == "__main__":
    main()