엑셀 다운로드 (Pandas, Workbook)

[Pandas 활용]

from datetime import datetime
from io import BytesIO

import pandas as pd
import openpyxl

from fastapi.responses import StreamingResponse


def make_excel_file(cols: list, rows: list) -> StreamingResponse:
    buffer = BytesIO()

    df = pd.DataFrame(data=[rows], columns=cols)

    file_name = f"example_{datetime.now().date()}"
    encoded_file_name = file_name.encode('utf-8').decode('cp949')

    # filename은 front에서도 지정 가능
    headers = {
        "Content-Disposition": f'attachment; filename="{encoded_file_name}.xlsx"',
    }
    with pd.ExcelWriter(buffer, engine="openpyxl") as writer:
        df.to_excel(writer, index=False)

    return StreamingResponse(
        BytesIO(buffer.getvalue()),
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers=headers,
    )

@app.post("/excel")
def download(command: GetExampleRequest, db: Session = Depends(get_db)):
    # return {"cols": [...], "rows": [...]}
    resp = 읽기_서비스.get_data_for_excel(command=command, db=db) 
    return make_excel_file(cols=resp['cols'], rows=resp['rows'])

 

[Workbook 활용]

from io import BytesIO

from sqlalchemy.orm import Session
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font

def get_excel_data(db: Session):
    rows = 읽기_서비스.get(db)

    wb = Workbook()
    ws = wb.active

    th_fill = PatternFill(start_color="f2f2f2", end_color="f2f2f2", fill_type="solid")
    th_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
    th_alignment = Alignment(horizontal="center", vertical="center")
    th_font = Font(size=10, bold=True, name='Arial')
    
    td_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
    td_alignment = Alignment(horizontal="center", vertical="center")
    td_font = Font(size=9, name='Arial')

    headers = ["No", "이름", "아이디", "금액", "할인금액", "수수료"]
    ws.append(headers)

    for cell in ws[1]:
        cell.fill = th_fill
        cell.border = th_border
        cell.alignment = th_alignment
        cell.font = th_font

    for idx, row in enumerate(row):
        ws.append([
            idx + 1,
            row['name'],
            row['id'],
            row['price'],
            row['sales'],
            row['fee'],
        ])

        for cell in ws[idx]:
            cell.border = td_border
            cell.alignment = td_alignment
            cell.font = td_font

    column_widths = [5, 20, 20, 30, 15, 50]
    for i, column_width in enumerate(column_widths, start=1):
        ws.column_dimensions[chr(64 + i)].width = column_width

    output = BytesIO()
    wb.save(output)
    output.seek(0)

    filename = f"order_{datetime.now().strftime('%Y%m%d%H%M%S')}.xlsx"

    return output, filename
    
    
@app.get(
    '/excel_download',
    summary='엑셀 파일 다운로드'
)
def download_excel(db: Session = Depends(get_db)):  
    file, filename = get_excel_data(db)

    headers = {
        'Content-Disposition': f'attachment; filename="{filename}"',
        'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    }

    return StreamingResponse(file, headers=headers)