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

• SQL Server supplies the latest 100 furnace events.
• pandas handles tabular data; openpyxl controls workbook presentation.
• Timestamped filenames prevent normal report runs from overwriting each other.

Export SQL Server Data to Excel: Reporting Flow

This program converts operational SQL data into a user-friendly Excel report. It validates the source table, reads a controlled batch, preserves SQL column names, writes the data, applies presentation formatting and closes the database connection regardless of success or failure.

2. Requirements and Configuration

python -m pip install pyodbc pandas openpyxl

SERVER = r"SOFTWELL\WINCC"
DATABASE = "SQF_DB"
TABLE = "dbo.tblEvent"
DRIVER = "ODBC Driver 17 for SQL Server"
TOP_RECORDS = 100
OUTPUT_FOLDER = Path(r"D:\SQF_Reports")

The raw server/path strings preserve Windows backslashes. The program needs SQL SELECT access plus write permission for D:\SQF_Reports. Keep connection and output settings in trusted configuration for production deployments.

3. Connect to SQL Server and Verify the Table

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'
""")
if cursor.fetchone()[0] == 0:
    raise RuntimeError("Table dbo.tblEvent is not available.")

The existence check fails early with a clear message instead of letting the later query produce a less focused error. In a production report, also validate required columns and compatible types or manage the schema through versioned migrations.

4. Read the Latest SQL 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)

The query returns newest records first. TOP_RECORDS is an integer constant controlled by the program, so its interpolation is not accepting user SQL. If the limit becomes external input, validate it as a bounded integer or use a supported bound parameter.

Add a unique descending tie-breaker such as Event_ID DESC when two records can share the same DT and TM.

5. Convert Cursor Results into a DataFrame

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)

cursor.description supplies the selected SQL column names. The row list and names become one labeled pandas DataFrame. The empty check prevents creating a misleading blank report.

fetchall() is appropriate for 100 rows. Large exports should use chunked queries/writes or a streaming strategy so the full result does not occupy memory twice as cursor rows and DataFrame data.

6. Create a Unique Excel Report Path

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

timestamp = datetime.now().strftime("%d-%m-%Y_%H-%M-%S-%f")
excel_file = OUTPUT_FOLDER / f"SQF_Event_Report_{timestamp}.xlsx"

Path.mkdir(..., exist_ok=True) creates the output folder and any missing parents without failing when it already exists. Date, time and microseconds make filename collisions unlikely during normal sequential runs.

For multi-process or distributed jobs, use a UUID or atomic file-creation pattern. Consider starting filenames with YYYY-MM-DD so ordinary alphabetical sorting also follows date order.

Write a pandas DataFrame to Excel with openpyxl

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

The context manager saves and closes the workbook automatically. index=False prevents pandas’ row index from becoming an unwanted Excel column. writer.sheets exposes the openpyxl worksheet for detailed formatting before the file is finalized.

Format the Excel Report with openpyxl

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

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

for cell in worksheet["A"][1:]:
    cell.number_format = "DD-MM-YYYY HH:MM:SS"

for letter in ["G", "H", "I", "J", "K", "L", "M", "N"]:
    for cell in worksheet[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

9. Error Handling and Connection Cleanup

The program separates database errors, file-permission failures and other exceptions. finally closes the SQL Server connection in every path.

except pyodbc.Error as error:
    print("SQL Server error:")
    print(error)
except PermissionError:
    print("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()

A locked workbook commonly triggers PermissionError when a fixed filename is reused. Unique names reduce that risk, but folder permissions, antivirus scanning, synchronization software or another process may still block creation.

10. Complete Copy-Paste-Ready Program

"""
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 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 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 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 UNIQUE EXCEL FILE NAME
    # ========================================================

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

    # Date, time 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 heading row
        worksheet.freeze_panes = "A2"

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

        # Format 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 date column
        for cell in worksheet["A"][1:]:
            cell.number_format = "DD-MM-YYYY HH:MM:SS"

        # Format 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 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 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 SQL SERVER CONNECTION
# ============================================================

finally:
    if connection is not None:
        connection.close()

    print("SQL Server connection closed.")

11. Verify the Report and Improve It

Acceptance checks

• The printed file path exists and ends in .xlsx.
• The workbook opens without a repair warning.
• The sheet is named Event Report.
• The header row stays visible while scrolling and all columns have filters.
• The record count matches the SQL query result.
• The first row is the newest event under the defined ordering.
• Date and process-value columns display the intended formats.

Useful production upgrades

• Wrap execution in functions and a __main__ guard.
• Parameterize a report date range, furnace number or charge number.
• Add a “Report Summary” sheet with generation time, filters and row count.
• Create an Excel Table for structured filters and styles.
• Validate the saved workbook by reopening it with openpyxl before distribution.
• Write to a temporary file and atomically rename it after successful completion.
• Log report ID, SQL criteria, row count, duration and output checksum.