基于Django+MSSQL实现查询结果导出至Excel的技术问询

Hey there! Let's walk through a few solid ways to export your SQL query results to Excel in your Django app. Since you're already using SQLAlchemy and pymssql, we can build right on top of that existing setup without too much extra work.

Option 1: Use Pandas (Quick & Simple) #

Pandas is hands down the easiest route here—it integrates seamlessly with SQLAlchemy, letting you pull query results into a DataFrame and export to Excel in just a few lines. Perfect if you don't need fancy custom formatting.

First, install the required dependencies:

pip install pandas openpyxl

Then add this to your Django view:

from django.http import HttpResponse
import pandas as pd
from io import BytesIO
# Import your existing SQLAlchemy engine (replace with your actual setup)
from your_app.db_config import sqlalchemy_engine

def export_query_to_excel(request):
    # 1. Define your SQL query (use your existing query here)
    sql_query = "SELECT * FROM your_target_table WHERE your_filter_condition;"

    # 2. Pull results into a Pandas DataFrame using SQLAlchemy
    df = pd.read_sql(sql_query, sqlalchemy_engine)

    # 3. Write the DataFrame to an in-memory Excel file
    output = BytesIO()
    with pd.ExcelWriter(output, engine="openpyxl") as writer:
        df.to_excel(writer, index=False, sheet_name="Query Results")
    output.seek(0)  # Reset the file pointer to the start

    # 4. Return the Excel file as a downloadable response
    response = HttpResponse(
        output,
        content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )
    response["Content-Disposition"] = 'attachment; filename="query_results.xlsx"'
    return response

Pro tip: If you're using SQLAlchemy's ORM instead of raw SQL, you can pass a query statement directly to pd.read_sql, like:

from your_app.models import YourModel
df = pd.read_sql(YourModel.query.filter(YourModel.status == "active").statement, sqlalchemy_engine)

Option 2: Use OpenPyXL (Full Customization) #

If you need to tweak the Excel file's appearance—like bolding headers, setting cell formats, or adding colors—OpenPyXL gives you full control over the workbook structure.

First install the dependency:

pip install openpyxl

Here's a sample view with custom formatting:

from django.http import HttpResponse
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
from io import BytesIO
from your_app.db_config import sqlalchemy_engine

def export_custom_excel(request):
    # 1. Execute your SQL query via SQLAlchemy
    with sqlalchemy_engine.connect() as conn:
        result = conn.execute("SELECT col1, col2, col3 FROM your_table;")
        column_names = result.keys()  # Grab the column headers
        data_rows = result.fetchall()  # Get all the query results

    # 2. Create a new Excel workbook and sheet
    wb = Workbook()
    ws = wb.active
    ws.title = "Custom Results"

    # 3. Style and write the header row
    header_font = Font(bold=True, size=12)
    header_align = Alignment(horizontal="center")
    for col_idx, header in enumerate(column_names, 1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = header_font
        cell.alignment = header_align

    # 4. Write the data rows
    for row_idx, row in enumerate(data_rows, 2):
        for col_idx, value in enumerate(row, 1):
            ws.cell(row=row_idx, column=col_idx, value=value)

    # 5. Save to memory and return the download response
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    response = HttpResponse(
        output,
        content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )
    response["Content-Disposition"] = 'attachment; filename="custom_query_results.xlsx"'
    return response

Key Things to Remember #

• Avoid disk writes: Always use BytesIO to handle the Excel file in memory—this prevents permission issues on your Django server and is more efficient.
• Content-Type headers: Use application/vnd.openxmlformats-officedocument.spreadsheetml.sheet for .xlsx files (the modern format). For older .xls files, use application/vnd.ms-excel, but .xlsx is strongly recommended.
• Encoding: If you run into character encoding issues (like Chinese text appearing garbled), ensure your SQLAlchemy connection is configured to use UTF-8 (most modern MSSQL setups handle this automatically, but double-check your connection string if needed).

内容的提问来源于stack exchange，提问作者saum