Export SQL Server Data to Excel Using Python, pandas and openpyxl

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

from pathlib import Path

SERVER = r"SOFTWELL\WINCC"
DATABASE = "SQF_DB"
TABLE = "dbo.tblEvent"
DRIVER = "ODBC Driver 17 for SQL Server"

# Export the latest 100 records
TOP_RECORDS = 100

# Excel report folder
OUTPUT_FOLDER = Path(r"D:\SQF_Reports")

import pyodbc

connection_string = (
    f"DRIVER={{{DRIVER}}};"
    f"SERVER={SERVER};"
    f"DATABASE={DATABASE};"
    "Trusted_Connection=yes;"
    "TrustServerCertificate=yes;"
)

connection = pyodbc.connect(connection_string, timeout=15)
cursor = connection.cursor()

cursor.execute("""
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'dbo'
      AND TABLE_NAME = 'tblEvent'
""")

table_available = cursor.fetchone()[0]

if table_available == 0:
    raise RuntimeError("Table dbo.tblEvent is not available.")

select_query = f"""
    SELECT TOP ({TOP_RECORDS})
        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
    ORDER BY DT DESC, TM DESC
"""

cursor.execute(select_query)

import pandas as pd

column_names = [column[0] for column in cursor.description]
rows = cursor.fetchall()

if not rows:
    raise RuntimeError("No records are available in dbo.tblEvent.")

dataframe = pd.DataFrame.from_records(rows, columns=column_names)

from datetime import datetime

OUTPUT_FOLDER.mkdir(parents=True, exist_ok=True)

# Date, time, seconds and microseconds prevent overwriting
timestamp = datetime.now().strftime("%d-%m-%Y_%H-%M-%S-%f")

excel_file = OUTPUT_FOLDER / f"SQF_Event_Report_{timestamp}.xlsx"

with pd.ExcelWriter(
    excel_file,
    engine="openpyxl",
    datetime_format="DD-MM-YYYY HH:MM:SS",
) as writer:
    dataframe.to_excel(
        writer,
        sheet_name="Event Report",
        index=False,
    )
    worksheet = writer.sheets["Event Report"]

from openpyxl.styles import Alignment, Font, PatternFill

# Freeze the heading row and add filters to every column
worksheet.freeze_panes = "A2"
worksheet.auto_filter.ref = worksheet.dimensions

# Format the header row
header_fill = PatternFill(fill_type="solid", fgColor="F4B183")

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

# Format the date column
for cell in worksheet["A"][1:]:
    cell.number_format = "DD-MM-YYYY HH:MM:SS"

# Format the process-value columns
numeric_columns = [
    "G", "H",  # Temperature
    "I", "J",  # CP
    "K", "L",  # Oil
    "M", "N",  # Jacket
]

for column_letter in numeric_columns:
    for cell in worksheet[column_letter][1:]:
        cell.number_format = "0.00"
        cell.alignment = Alignment(horizontal="center")

for column_cells in worksheet.columns:
    column_letter = column_cells[0].column_letter

    maximum_length = max(
        len(str(cell.value)) if cell.value is not None else 0
        for cell in column_cells
    )

    worksheet.column_dimensions[column_letter].width = min(maximum_length + 3, 35)

worksheet.row_dimensions[1].height = 25

except pyodbc.Error as error:
    print("SQL Server error:")
    print(error)

except PermissionError:
    print(
        "Excel file permission error.\n"
        "Close the Excel file and run the program again."
    )

except Exception as error:
    print("Program stopped:")
    print(error)

finally:
    if connection is not None:
        connection.close()
        print("SQL Server connection closed.")

r"""
STEP 05: SQL Server to Excel Report

SQL Server : SOFTWELL\WINCC
Database   : SQF_DB
Table      : dbo.tblEvent
Output     : D:\SQF_Reports

Install once:
python -m pip install pyodbc pandas openpyxl
"""

from datetime import datetime
from pathlib import Path

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

# ============================================================
# 1. SQL SERVER SETTINGS
# ============================================================
SERVER = r"SOFTWELL\WINCC"
DATABASE = "SQF_DB"
TABLE = "dbo.tblEvent"
DRIVER = "ODBC Driver 17 for SQL Server"

# Export the latest 100 records
TOP_RECORDS = 100

# Excel report folder
OUTPUT_FOLDER = Path(r"D:\SQF_Reports")

# ============================================================
# 2. CONNECT TO SQL SERVER
# ============================================================
connection = None

try:
    connection_string = (
        f"DRIVER={{{DRIVER}}};"
        f"SERVER={SERVER};"
        f"DATABASE={DATABASE};"
        "Trusted_Connection=yes;"
        "TrustServerCertificate=yes;"
    )

    connection = pyodbc.connect(connection_string, timeout=15)
    cursor = connection.cursor()

    print("=" * 65)
    print("STEP 05 - SQL SERVER TO EXCEL REPORT")
    print("=" * 65)
    print("SQL Server connection successful.")

    # ========================================================
    # 3. CHECK WHETHER THE TABLE IS AVAILABLE
    # ========================================================
    cursor.execute("""
        SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'dbo'
          AND TABLE_NAME = 'tblEvent'
    """)

    table_available = cursor.fetchone()[0]

    if table_available == 0:
        raise RuntimeError("Table dbo.tblEvent is not available.")

    print("Table dbo.tblEvent verified.")

    # ========================================================
    # 4. READ THE LATEST 100 RECORDS
    # ========================================================
    select_query = f"""
        SELECT TOP ({TOP_RECORDS})
            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
        ORDER BY DT DESC, TM DESC
    """

    cursor.execute(select_query)

    # Read SQL column names
    column_names = [column[0] for column in cursor.description]

    # Read SQL rows
    rows = cursor.fetchall()

    if not rows:
        raise RuntimeError("No records are available in dbo.tblEvent.")

    # Convert SQL records into a pandas DataFrame
    dataframe = pd.DataFrame.from_records(rows, columns=column_names)

    print(f"{len(dataframe)} records received from SQL Server.")

    # ========================================================
    # 5. CREATE A UNIQUE EXCEL FILE NAME
    # ========================================================
    OUTPUT_FOLDER.mkdir(parents=True, exist_ok=True)

    # Date, time, seconds and microseconds prevent overwriting
    timestamp = datetime.now().strftime("%d-%m-%Y_%H-%M-%S-%f")

    excel_file = OUTPUT_FOLDER / f"SQF_Event_Report_{timestamp}.xlsx"

    # ========================================================
    # 6. WRITE SQL DATA INTO EXCEL
    # ========================================================
    with pd.ExcelWriter(
        excel_file,
        engine="openpyxl",
        datetime_format="DD-MM-YYYY HH:MM:SS",
    ) as writer:
        dataframe.to_excel(
            writer,
            sheet_name="Event Report",
            index=False,
        )

        worksheet = writer.sheets["Event Report"]

        # Freeze the heading row
        worksheet.freeze_panes = "A2"

        # Add filters to all columns
        worksheet.auto_filter.ref = worksheet.dimensions

        # Format the header
        header_fill = PatternFill(fill_type="solid", fgColor="F4B183")

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

        # Format the date column
        for cell in worksheet["A"][1:]:
            cell.number_format = "DD-MM-YYYY HH:MM:SS"

        # Format the process-value columns
        numeric_columns = [
            "G", "H",  # Temperature
            "I", "J",  # CP
            "K", "L",  # Oil
            "M", "N",  # Jacket
        ]

        for column_letter in numeric_columns:
            for cell in worksheet[column_letter][1:]:
                cell.number_format = "0.00"
                cell.alignment = Alignment(horizontal="center")

        # Automatically adjust the column widths
        for column_cells in worksheet.columns:
            column_letter = column_cells[0].column_letter

            maximum_length = max(
                len(str(cell.value)) if cell.value is not None else 0
                for cell in column_cells
            )

            worksheet.column_dimensions[column_letter].width = min(
                maximum_length + 3,
                35,
            )

        worksheet.row_dimensions[1].height = 25

    # ========================================================
    # 7. DISPLAY THE SUCCESS MESSAGE
    # ========================================================
    print("-" * 65)
    print("Excel report created successfully.")
    print(f"Excel file: {excel_file}")
    print("=" * 65)

# ============================================================
# 8. ERROR HANDLING
# ============================================================
except pyodbc.Error as error:
    print("SQL Server error:")
    print(error)

except PermissionError:
    print(
        "Excel file permission error.\n"
        "Close the Excel file and run the program again."
    )

except Exception as error:
    print("Program stopped:")
    print(error)

# ============================================================
# 9. CLOSE THE SQL SERVER CONNECTION
# ============================================================
finally:
    if connection is not None:
        connection.close()
        print("SQL Server connection closed.")