Convert Python to EXE for WinCC Excel Report Generation

python -m pip install --upgrade pip
python -m pip install pyodbc pandas openpyxl pyinstaller

def main():
    if len(sys.argv) == 1:
        run_popup()
    else:
        run_command_line()


if __name__ == "__main__":
    main()

def parse_datetime(value, end_of_day=False):
    """Accept YYYY-MM-DD or YYYY-MM-DD HH:MM[:SS]."""
    try:
        parsed = datetime.fromisoformat(value.strip())
    except ValueError as error:
        raise ValueError(
            "Use YYYY-MM-DD HH:MM:SS, for example 2026-07-27 14:30:00"
        ) from error

    # A date-only entry has 10 characters, so supply the missing time
    if len(value.strip()) == 10:
        parsed = datetime.combine(
            parsed.date(),
            time(23, 59, 59) if end_of_day else time.min,
        )

    return parsed

query = """
    SELECT
        [DT], [TM], [SQF_No], [ChargeNo],
        [Event_From], [Event_To],
        [Temp_Set], [Temp_Act],
        [Cp_Set], [Cp_Act],
        [Oil_Set], [Oil_Act],
        [Jacket_Set], [Jacket_Act],
        [Fan_Status]
    FROM [dbo].[tblEvent]
    WHERE [DT] >= ? AND [DT] < ?
"""

params = [start_datetime.date(), end_datetime.date() + timedelta(days=1)]

if sqf_no is not None:
    query += " AND [SQF_No] = ?"
    params.append(sqf_no)

query += " ORDER BY [DT] DESC, [TM] DESC;"

output_file = output_folder / f"SQF_Filtered_Report_{furnace_text}_{stamp}.xlsx"

with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
    dataframe.to_excel(
        writer,
        sheet_name="Filtered Report",
        index=False,
        startrow=4,
    )

    worksheet = writer.sheets["Filtered Report"]

    # Rows 1 to 3 hold the report title and filter summary
    worksheet["A1"] = "SQF FURNACE EVENT REPORT"

    # Data headers land on row 5, so freeze below them
    worksheet.freeze_panes = "A6"
    worksheet.auto_filter.ref = worksheet.dimensions

python -m PyInstaller --clean --noconfirm --onefile --windowed --name Softwell_Report softwell_report.py

dist\Softwell_Report.exe

Sub OnClick(ByVal Item)
    Dim shell
    Set shell = CreateObject("WScript.Shell")
    shell.Run """C:\Softwell_Report\Softwell_Report.exe""", 1, False
    Set shell = Nothing
End Sub

C:\Softwell_Report\
├── Softwell_Report.exe
└── SQF_Reports\

r"""
================================================================================
CODE 05: WINCC / WINDOWS FILTER POPUP AND EXCEL REPORT GENERATOR
================================================================================

THEORY
------
An operator selects a start date/time, end date/time, and optional furnace.
Python uses parameterized SQL to read the matching date range, applies the exact
time filter, and exports the result to a professionally formatted Excel workbook.

STEPS
-----
1. Run the Python file or double-click the compiled EXE.
2. Enter start and end date/time in the popup.
3. Select All Furnaces or type a furnace number.
4. Select the report output folder.
5. Click Generate Excel Report.
6. The program confirms the record count and generated workbook path.

EXPLANATION
-----------
GUI mode uses only Python's built-in tkinter library. pyodbc reads SQL Server
without a pandas/SQLAlchemy warning. pandas prepares timestamps, and openpyxl
formats the Excel report. Command-line mode remains available for WinCC.
================================================================================
"""

import argparse
import os
import sys
import tkinter as tk
from datetime import datetime, time, timedelta
from pathlib import Path
from tkinter import filedialog, messagebox, ttk

import pandas as pd
import pyodbc
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

SQL_SERVER = r"SOFTWELL\WINCC"
DATABASE = "SQF_DB"
DRIVER = "{ODBC Driver 17 for SQL Server}"


def application_folder():
    """Return the EXE folder when compiled, otherwise the Python-file folder."""
    if getattr(sys, "frozen", False):
        return Path(sys.executable).resolve().parent
    return Path(__file__).resolve().parent


DEFAULT_OUTPUT_FOLDER = application_folder() / "SQF_Reports"


def parse_datetime(value, end_of_day=False):
    """Accept YYYY-MM-DD or YYYY-MM-DD HH:MM[:SS]."""
    try:
        parsed = datetime.fromisoformat(value.strip())
    except ValueError as error:
        raise ValueError(
            "Use YYYY-MM-DD HH:MM:SS, for example 2026-07-27 14:30:00"
        ) from error

    if len(value.strip()) == 10:
        parsed = datetime.combine(
            parsed.date(),
            time(23, 59, 59) if end_of_day else time.min,
        )

    return parsed


def create_connection():
    """Create a Windows-authenticated SQL Server connection."""
    return pyodbc.connect(
        f"Driver={DRIVER};Server={SQL_SERVER};Database={DATABASE};"
        "Trusted_Connection=yes;TrustServerCertificate=yes;"
    )


def get_filtered_data(connection, start_datetime, end_datetime, sqf_no=None):
    """Read the date range, then apply the exact time range."""
    query = """
        SELECT
            [DT], [TM], [SQF_No], [ChargeNo],
            [Event_From], [Event_To],
            [Temp_Set], [Temp_Act],
            [Cp_Set], [Cp_Act],
            [Oil_Set], [Oil_Act],
            [Jacket_Set], [Jacket_Act],
            [Fan_Status]
        FROM [dbo].[tblEvent]
        WHERE [DT] >= ? AND [DT] < ?
    """

    params = [start_datetime.date(), end_datetime.date() + timedelta(days=1)]

    if sqf_no is not None:
        query += " AND [SQF_No] = ?"
        params.append(sqf_no)

    query += " ORDER BY [DT] DESC, [TM] DESC;"

    cursor = connection.cursor()
    cursor.execute(query, params)

    columns = [column[0] for column in cursor.description]
    dataframe = pd.DataFrame.from_records(cursor.fetchall(), columns=columns)

    if dataframe.empty:
        return dataframe

    date_values = pd.to_datetime(dataframe["DT"], errors="coerce")
    time_values = pd.to_timedelta(dataframe["TM"].astype(str), errors="coerce")
    combined = date_values.dt.normalize() + time_values

    dataframe.insert(
        0,
        "Event_Timestamp",
        combined.where(combined.notna(), date_values),
    )

    return dataframe[
        dataframe["Event_Timestamp"].between(start_datetime, end_datetime)
    ].reset_index(drop=True)


def export_to_excel(dataframe, output_folder, start_datetime, end_datetime, sqf_no):
    """Create a timestamped, formatted Excel report."""
    output_folder.mkdir(parents=True, exist_ok=True)

    stamp = datetime.now().strftime("%d-%m-%Y_%H-%M-%S")
    furnace_text = "All" if sqf_no is None else f"SQF-{sqf_no}"
    output_file = output_folder / f"SQF_Filtered_Report_{furnace_text}_{stamp}.xlsx"

    with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
        dataframe.to_excel(
            writer,
            sheet_name="Filtered Report",
            index=False,
            startrow=4,
        )

        worksheet = writer.sheets["Filtered Report"]

        # Report title
        worksheet["A1"] = "SQF FURNACE EVENT REPORT"
        worksheet["A1"].font = Font(bold=True, size=16, color="FFFFFF")
        worksheet["A1"].fill = PatternFill("solid", fgColor="1F4E78")
        worksheet.merge_cells(
            start_row=1,
            start_column=1,
            end_row=1,
            end_column=max(1, len(dataframe.columns)),
        )

        # Filter summary
        worksheet["A2"] = (
            f"Period: {start_datetime:%Y-%m-%d %H:%M:%S} to "
            f"{end_datetime:%Y-%m-%d %H:%M:%S}"
        )
        worksheet["A3"] = f"Furnace: {furnace_text} | Records: {len(dataframe)}"

        # Data header row
        header_row = 5
        header_fill = PatternFill("solid", fgColor="F4B183")

        for cell in worksheet[header_row]:
            cell.font = Font(bold=True)
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal="center")

        worksheet.freeze_panes = "A6"
        worksheet.auto_filter.ref = worksheet.dimensions

        # Row 1 contains merged title cells. Use the numeric column position
        # instead of column_cells[0].column_letter, because merged cells do not
        # provide the column_letter attribute.
        for column_number, column_cells in enumerate(worksheet.columns, start=1):
            letter = get_column_letter(column_number)
            width = max(len(str(cell.value or "")) for cell in column_cells) + 3
            worksheet.column_dimensions[letter].width = min(width, 35)

        for row in worksheet.iter_rows(min_row=6):
            if isinstance(row[0].value, datetime):
                row[0].number_format = "DD-MM-YYYY HH:MM:SS"

    return output_file


def generate_report(start_text, end_text, furnace_text, output_text):
    """Validate filters, query SQL Server, and generate the workbook."""
    start_datetime = parse_datetime(start_text)
    end_datetime = parse_datetime(end_text, end_of_day=True)

    if end_datetime < start_datetime:
        raise ValueError("End date/time must be after start date/time.")

    furnace_value = furnace_text.strip()
    sqf_no = None if furnace_value in {"", "All Furnaces"} else int(furnace_value)

    output_folder = Path(output_text).expanduser().resolve()

    with create_connection() as connection:
        dataframe = get_filtered_data(
            connection,
            start_datetime,
            end_datetime,
            sqf_no,
        )

    output_file = export_to_excel(
        dataframe,
        output_folder,
        start_datetime,
        end_datetime,
        sqf_no,
    )

    return output_file, len(dataframe)


def run_popup():
    """Open the operator filter popup."""
    root = tk.Tk()
    root.title("SQF Filtered Excel Report")
    root.geometry("650x370")
    root.resizable(False, False)

    frame = ttk.Frame(root, padding=22)
    frame.pack(fill="both", expand=True)

    ttk.Label(
        frame,
        text="SQF Furnace Report Filter",
        font=("Segoe UI", 16, "bold"),
    ).grid(row=0, column=0, columnspan=3, pady=(0, 20))

    now = datetime.now()
    start_var = tk.StringVar(
        value=(now - timedelta(days=7)).strftime("%Y-%m-%d 00:00:00")
    )
    end_var = tk.StringVar(value=now.strftime("%Y-%m-%d 23:59:59"))
    furnace_var = tk.StringVar(value="All Furnaces")
    output_var = tk.StringVar(value=str(DEFAULT_OUTPUT_FOLDER))
    status_var = tk.StringVar(value="Select filters and click Generate Excel Report.")

    labels = [
        ("Start date/time", start_var),
        ("End date/time", end_var),
    ]

    for row_number, (label, variable) in enumerate(labels, start=1):
        ttk.Label(frame, text=label).grid(row=row_number, column=0, sticky="w", pady=7)
        ttk.Entry(frame, textvariable=variable, width=36).grid(
            row=row_number,
            column=1,
            columnspan=2,
            sticky="ew",
            pady=7,
        )

    ttk.Label(frame, text="Furnace").grid(row=3, column=0, sticky="w", pady=7)
    furnace_box = ttk.Combobox(
        frame,
        textvariable=furnace_var,
        values=["All Furnaces", "1", "2", "3"],
        width=33,
    )
    furnace_box.grid(row=3, column=1, columnspan=2, sticky="ew", pady=7)

    ttk.Label(frame, text="Output folder").grid(row=4, column=0, sticky="w", pady=7)
    ttk.Entry(frame, textvariable=output_var, width=36).grid(
        row=4,
        column=1,
        sticky="ew",
        pady=7,
    )

    def browse_folder():
        selected = filedialog.askdirectory(initialdir=output_var.get())
        if selected:
            output_var.set(selected)

    ttk.Button(frame, text="Browse...", command=browse_folder).grid(
        row=4,
        column=2,
        padx=(8, 0),
        pady=7,
    )

    def on_generate():
        try:
            status_var.set("Generating report, please wait...")
            root.update_idletasks()

            output_file, record_count = generate_report(
                start_var.get(),
                end_var.get(),
                furnace_var.get(),
                output_var.get(),
            )

            status_var.set(f"Completed: {record_count} records")
            messagebox.showinfo(
                "Report Generated",
                f"Report generated successfully.\n\n"
                f"Records: {record_count}\nFile:\n{output_file}",
            )

            if messagebox.askyesno(
                "Open Report",
                "Do you want to open the Excel report now?",
            ):
                os.startfile(output_file)

        except Exception as error:
            status_var.set("Report generation failed.")
            messagebox.showerror("Report Error", str(error))

    ttk.Button(
        frame,
        text="Generate Excel Report",
        command=on_generate,
    ).grid(row=5, column=0, columnspan=3, pady=(20, 10), ipadx=30, ipady=5)

    ttk.Label(frame, textvariable=status_var, foreground="#1F4E78").grid(
        row=6,
        column=0,
        columnspan=3,
    )

    frame.columnconfigure(1, weight=1)
    root.mainloop()


def run_command_line():
    """Generate a report from WinCC or a command prompt."""
    parser = argparse.ArgumentParser(description="Generate an SQF Excel report.")
    parser.add_argument("start", help='YYYY-MM-DD or "YYYY-MM-DD HH:MM:SS"')
    parser.add_argument("end", help='YYYY-MM-DD or "YYYY-MM-DD HH:MM:SS"')
    parser.add_argument("sqf_no", nargs="?", help="Optional furnace number")
    parser.add_argument("--output", default=str(DEFAULT_OUTPUT_FOLDER))
    args = parser.parse_args()

    output_file, count = generate_report(
        args.start,
        args.end,
        args.sqf_no or "All Furnaces",
        args.output,
    )

    print(f"Exported {count} records to:\n{output_file}")


def main():
    if len(sys.argv) == 1:
        run_popup()
    else:
        run_command_line()


if __name__ == "__main__":
    main()