Required Third-Party Libraires

pip install pandas numpy openpyxl

Database Query and Excel Stream Creation

import pandas as pd
import io
from django.db import connection

def generate_excel_stream(record_numbers):
    """
    Queries database and creates an Excel file in memory.
    Returns a BytesIO object containing the Excel data.
    """
    # Create an in-memory binary stream
    excel_stream = io.BytesIO()
    
    query = """
    SELECT DISTINCT 
        bill_rpts.cnumber AS No,
        bill_rpts.socialcode AS payer_credit_code,
        bill_rpts.payer AS payer_name,
        CONVERT(VARCHAR(100), bill_rpts.ddate, 23) AS invoice_date,
        bill_rpts_items.itemnumber AS item_code,
        bill_rpts_items.itemname AS item_name,
        bill_rpts_items.uunit AS unit,
        bill_rpts_items.quantity AS quantity,
        bill_rpts_items.standard AS standard,
        CONVERT(DECIMAL(18,2), bill_rpts_items.isum) AS amount,
        bill_rpts_items.cnote AS remarks,
        CONVERT(DECIMAL(18,2), bill_rpts.isum) AS total_amount,
        bill_rpts.cnote AS additional_info,
        code_dep.titlename AS recipient_unit,
        bill_rpts.reviewers AS recipient,
        bill_rpts.payee AS reviewer
    FROM bill_rpts
    LEFT JOIN bill_rpts_items ON bill_rpts.cnumber = bill_rpts_items.cnumber
    LEFT JOIN code_dep ON code_dep.ccode = bill_rpts.ccode_dep
    WHERE bill_rpts.cnumber IN %s;
    """ % record_numbers
    
    # Execute query and load into DataFrame
    data_frame = pd.read_sql_query(query, connection)
    
    # Write DataFrame to Excel stream
    data_frame.to_excel(
        excel_stream,
        index=False,
        sheet_name='Invoice Details',
        header=True
    )
    
    # Reset stream position to beginning
    excel_stream.seek(0)
    return excel_stream

HTTP Response Handler for File Download

import json
from django.views.decorators.csrf import csrf_exempt
from django.http import HttpResponse

@csrf_exempt
def download_invoice_excel(request):
    """
    API endpoint that processes request and returns Excel file as download.
    """
    result = {'code': 0, 'data': None, 'message': None}
    
    try:
        request_data = json.loads(request.body.decode())
        record_ids = request_data.get('cnumbers')
        
        if record_ids:
            # Generate Excel file in memory
            file_stream = generate_excel_stream(record_ids)
            
            # Create HTTP response with file attachment
            response = HttpResponse(
                file_stream.getvalue(),
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            response['Content-Disposition'] = 'attachment; filename="invoice_details.xlsx"'
            return response
        else:
            result['message'] = 'Invalid parameters: record numbers required'
            return HttpResponse(json.dumps(result), content_type='application/json')
            
    except Exception as error:
        result['message'] = str(error)
        return HttpResponse(json.dumps(result), content_type='application/json', status=500)