excelify.py

from StringIO import StringIO
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.cell import get_column_letter
from openpyxl.styles import Style, Font


def make_excel(dataset,
               key_headers=None, # The order of attributes, or what to include
               line_headers=None, # The order of table attributes, or what to include
               heading="HEADING HERE", # Heading of the report
               subheading="SUBHEADING HERE", # Subheading of the report
               line_title="lines",
               virtual=False, # Do we save a file? Or do we pass it to Django?
               filename="sample.xlsx"):
    """
    I am totally wasted today, no logic's coming out of my head, so guys please help out!!! :P

    The record data will have to be in the format (key, value) where those will act as the top level info.
    There will be a key called "lines" which will be a list of dicts which will represent the table.
    
    For instance, for 
    `[{"name": "Mafinar", "age": 32, score: [{"language": "Python", "score": 9}, {"language": "C#", "score": 0}, {"language": "JavaScript", "score": "NaN"}]}]` we should get something like:
    
    |Name | Mafinar  |
    |Age  | 32       |
    
    |Language  |Score|
    |Python    |9    |
    |C#        |0    |
    |JavaScript|NaN  |
    """
    #######################
    bold_style = Style(font=Font(bold=True))
    heading_style = Style(font=Font(size=16, bold=True))
    subheading_style = Style(font=Font(size=14, bold=True))
    #######################

    if not isinstance(dataset, list):
        dataset = [dataset]

    wb = Workbook()
    ws = wb.active
    row = 1

    if heading:
        ws["A{}".format(row)].value = heading
        ws["A{}".format(row)].style = heading_style
        row += 1

    if subheading:
        ws["A{}".format(row)].value = subheading
        ws["A{}".format(row)].style = subheading_style
        row += 1

    row += 1

    for data in dataset:
        cache = []

        key_headers = data.keys() if not key_headers else key_headers

        for k in key_headers:
            v = data[k]

            if k == line_title:
                cache = v
                print cache
            else:
                col = get_column_letter(1)
                ws["{}{}".format(col, row)].value = k
                ws["{}{}".format(col, row)].style = bold_style

                col = get_column_letter(2)
                ws["{}{}".format(col, row)].value = v
                row += 1

        row += 1

        if cache:
            keys = cache[0].keys() if not line_headers else line_headers

            for header_idx in range(len(keys)):
                col = get_column_letter(header_idx+1)
                ws["{}{}".format(col, row)].value = keys[header_idx]
                ws["{}{}".format(col, row)].style = bold_style

            row += 1

            for line in cache:
                for header_idx in range(len(keys)):
                    col = get_column_letter(header_idx+1)
                    header = keys[header_idx]
                    ws["{}{}".format(col, row)].value = line.get(header, " ")

                row += 1


        row += 3

    if virtual:
        output = StringIO()
        wb.save(output)
    
        return output.getvalue()
    else:
        wb.save(filename=filename)
        
        return {
            "filename": filename,
            "saved": True,
        }

views.py

from django.http import HttpResponse

from reporthub import base_engine as engine


def excelify(request, start_date, end_date, report_type):
    xl = make_excel(engine.make_report(request.user, 
                                       start_date, 
                                       end_date,
                                       report_type
                                       virtual=True,
                                       heading="My Awesome Fucking Report",
                                       subheading="Plucked at {}".format(start_date),
                                       line_title="rows")

    response = HttpResponse(xl, content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename=app.xlsx'

    return response