Convert Python to EXE for WinCC Excel Report Generation

• Python is not required on the runtime PC after a successful standalone build.
• The SQL Server ODBC driver, Windows database permission and output-folder permission are still required.
• Always prove the EXE independently before connecting it to the WinCC button.

WinCC One-Button Excel Reporting Workflow

The EXE is an external reporting application. WinCC launches it but does not need to host the Python GUI or Excel-generation libraries inside the WinCC project.

2. Software and Deployment Requirements

• Build PC: compatible Python, pyodbc, pandas, openpyxl and pyinstaller.
• Runtime PC: supported Windows version and Microsoft ODBC Driver 17 for SQL Server.
• Network access from the WinCC station to SOFTWELL\WINCC.
• Windows identity running the EXE must have SELECT access to SQF_DB.dbo.tblEvent.
• Write permission to the selected report folder.
• WinCC Graphics Designer permission to configure and execute the button action.

python -m pip install pyodbc pandas openpyxl pyinstaller

3. How the Python Application Works

The program supports two modes:

• Popup mode: double-click the EXE or launch it from WinCC with no arguments. run_popup() opens the operator form.
• Command-line mode: pass start, end, optional furnace and output folder. This supports unattended or parameter-driven launches.

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

if __name__ == "__main__":
    main()

The attached source duplicated this entry-point block. The corrected version in this blog contains it only once, preventing the GUI or command-line report from running twice.

4. Validate Date, Time and Furnace Filters

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

The form accepts YYYY-MM-DD or YYYY-MM-DD HH:MM[:SS]. A date-only start becomes midnight; a date-only end becomes 23:59:59. The program rejects an end before the start. “All Furnaces” becomes None; values 1–3 become integers.

For maximum precision, consider making the end boundary exclusive—such as the next day at midnight—especially when SQL timestamps can contain fractions beyond whole seconds.

5. Query SQL Server with Bound Parameters

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;"

The initial SQL range safely narrows the candidate dates. The cursor results become a pandas DataFrame. Because this schema stores date and time separately, the program converts both columns and creates Event_Timestamp, then applies the exact inclusive range in pandas.

6. Generate the Formatted Excel Report

The exporter creates the folder, uses a timestamped filename, writes data below a report header, and adds title, selected period, furnace, record count, header styling, filters, frozen panes, widths and date formatting.

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"]
    worksheet["A1"] = "SQF FURNACE EVENT REPORT"
    worksheet.freeze_panes = "A6"
    worksheet.auto_filter.ref = worksheet.dimensions

Using numeric column positions with get_column_letter() avoids failures caused by merged title cells, which do not expose the same column_letter property as normal cells.

Create a Python EXE with PyInstaller

1. Save the corrected program as softwell_report.py.
2. Open Command Prompt in that folder.
3. Create and activate an approved virtual environment if required by your deployment process.
4. Install the dependencies.
5. Run the PyInstaller command.

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

On Windows Command Prompt, enter the command on one line. The generated application is normally located at:

dist\Softwell_Report.exe

If a clean test reports a missing module or data file, inspect the PyInstaller warning file and add only the required hidden import or collection option. Avoid copying an untested build directly to a production HMI.

8. Test the EXE Before WinCC Integration

1. Run dist\Softwell_Report.exe directly.
2. Confirm the popup opens once.
3. Generate an “All Furnaces” report for a short known period.
4. Generate a single-furnace report.
5. Verify record count, first/last timestamp, workbook formatting and output folder.
6. Test invalid date text, end-before-start, no-data range, SQL outage and unwritable folder.
7. Test from the same Windows account/context used by WinCC Runtime.

A successful interactive desktop test does not automatically prove the WinCC Runtime launch context has the same database, filesystem or desktop permissions.

Launch the Python EXE from a WinCC Explorer Button

In WinCC Graphics Designer, add a button labeled Softwell_Report. On its mouse-click event, configure a VBScript action similar to the following and use the actual deployed path:

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

The quoted path supports spaces. Window style 1 requests a normal visible window. False tells WinCC not to block while the EXE is running. The exact event wrapper generated by your WinCC version should be preserved; place the WScript.Shell statements inside that generated click procedure.

10. Deploy on the WinCC Runtime Station

A practical local deployment layout is:

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

• Copy the signed/approved EXE to a stable local folder.
• Install the required SQL Server ODBC driver separately.
• Allow the EXE through endpoint-security application controls.
• Grant the WinCC operator/runtime identity read access to SQL and write access to the report folder.
• Use a fixed local path in the WinCC action; avoid user-profile and mapped-drive dependencies.
• Record application version, checksum, build environment and rollback copy.
• Commission the complete workflow while WinCC Runtime is active.

PyInstaller bundles Python code and libraries, but it does not provision SQL Server, install the Microsoft ODBC driver, grant database rights, install Excel, or configure WinCC permissions. Excel itself is optional for generating the XLSX; it is needed only if operators want to open the report in Microsoft Excel.

11. Complete Corrected Python Code

"""
================================================================================
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
from datetime import datetime, time, timedelta
from pathlib import Path
import os
import sys
import tkinter as tk
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"]
        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)))
        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)}"

        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
        )
    return export_to_excel(
        dataframe, output_folder, start_datetime, end_datetime, sqf_no
    ), 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\nRecords: {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()

12. Troubleshooting and Production Improvements

Recommended production upgrades

• Add a single-instance lock so one operator cannot open several report windows.
• Run database/export work in a worker thread so the GUI remains responsive.
• Use a single indexed datetime2 column and exact SQL range filtering.
• Load server/database/output configuration from a controlled config file.
• Add application logging with timestamps, filters, row count, duration and errors.
• Validate furnace selection against an allowlist.
• Write to a temporary XLSX and rename after validation.
• Digitally sign the final EXE and document the checksum/version.