from datetime import datetime
from io import BytesIO
from itertools import chain
import json

from django.http import HttpResponse
from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE
from openpyxl.utils import get_column_letter


def excel_response(headers, rows, filename):
    workbook = Workbook(write_only=True)
    sheet = workbook.create_sheet('Products')
    sheet.sheet_view.rightToLeft = True
    sheet.freeze_panes = 'A2'
    for index in range(1, len(headers) + 1):
        sheet.column_dimensions[get_column_letter(index)].width = 24
    for number, row in enumerate(chain((headers,), rows), 1):
        cells = []
        for value in row:
            if isinstance(value, datetime) and value.tzinfo:
                value = value.isoformat()
            if isinstance(value, (dict, list)):
                value = json.dumps(value, ensure_ascii=False)
            if isinstance(value, str):
                value = ILLEGAL_CHARACTERS_RE.sub('', value)
            cell = WriteOnlyCell(sheet, value=value)
            if isinstance(value, str):
                cell.data_type = 's'
            cells.append(cell)
        sheet.append(cells)
    sheet.auto_filter.ref = f'A1:{get_column_letter(len(headers))}{number}'
    content = BytesIO()
    workbook.save(content)
    response = HttpResponse(content.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = f'attachment; filename="{filename}"'
    return response