Export report to excel

export.py

# -*- coding: utf-8 -*-
from src.reporting.models import Report
from xlsxwriter.utility import xl_rowcol_to_cell, xl_range


class ReportData(object):

    def __init__(self, project, periods):
        self.project = project
        self.periods = periods
        self.reports = project.report_set.filter(period__in=periods, status=Report.APPROVED)

    def get(self, product, distr_point, period):
        row = period.get_row(product, distr_point)
        if row:
            return {
                'left': row.left,
                'sold': row.sold,
                'cost': row.cost,
                'base_price': product.base_price
            }
        else:
            return {
                'left': 0,
                'sold': 0,
                'cost': 0,
                'base_price': product.base_price
            }


class Worksheet(object):

    def __init__(self, project, start_date, end_date, workbook, theme, start_row=0, start_col=0):
        self.project = project
        self.start_date = start_date
        self.end_date = end_date
        self.periods = self.project.reportingperiod_set \
            .filter(from_date__lte=self.end_date, to_date__gte=self.start_date) \
            .order_by('from_date')
        self.periods_count = len(self.periods)
        self.client = self.project.client
        self.product_categories = self.client.productcategory_set.exclude(product=None)
        self.distribution_points = self.project.distribution_points.all()
        self.data = ReportData(project, self.periods)

        self.theme = theme
        self.workbook = workbook
        self.start_row = start_row
        self.start_col = start_col
        self._restrebution_points = []

        self.period_col_count = 5

        if self.periods_count:
            self._init_sheet()
            self.head = Head(self.worksheet, theme)

        self.dev = False
        self.no_total_cols = [0, 1]  # columns we do not need to sum(e.g. РРЦ)

    def _init_sheet(self):
        self.worksheet = self.workbook.add_worksheet(unicode(self.project))
        self.worksheet.set_zoom(80)
        self.worksheet.set_column(self.start_col, self.start_col, 8)
        self.worksheet.set_column(self.start_col+1, self.start_col+2, 20)
        self.worksheet.set_row(self.start_row+3, 40)
        self.worksheet.freeze_panes(5, 0)

    def render_data(self):
        start_col = self.start_col+3

        for distr_point in self.distribution_points:
            start_col = self.add_restrebution_point(distr_point, start_col)

        row = self.start_row+5
        total_rows = []

        for category in self.product_categories:
            row = self.add_product_category(category, row)
            total_rows.append(row-1)

        self.add_total(self.start_row+4, total_rows)

    def add_total(self, row, total_rows):
        self.worksheet.merge_range(row, self.start_col, row, self.start_col+2, u'Общий итог', self.theme.head1)
        col = self.start_col+3
        dist_cols_count = self.periods_count * self.period_col_count + 2

        for dist_i, distr_point in enumerate(self.distribution_points):
            for period_i, period in enumerate(self.periods):
                for col_i in range(self.period_col_count):
                    i = dist_cols_count * dist_i + period_i * self.period_col_count + col_i

                    if col_i in self.no_total_cols:
                        self.worksheet.write(row, col+i, '', self.theme.head1)
                        continue

                    self._fill_total(row, col+i, total_rows)

                for total_dist_i in range(2):
                    i = dist_cols_count * dist_i + self.periods_count * self.period_col_count + total_dist_i

                    self._fill_total(row, col+i, total_rows)

        for total_dist_i in range(1, 3):
            i += 1
            self._fill_total(row, col+i, total_rows)

    def _fill_total(self, row, col, total_rows):
        num_format = self.worksheet.table[row+1][col].format.num_format

        if num_format != 0:
            format = self.theme.head1_currency
        else:
            format = self.theme.head1

        total_cells = []
        for total_row in total_rows:
            total_cells.append(xl_rowcol_to_cell(total_row, col))

        self.worksheet.write_formula(row, col, '=%s' % u'+'.join(total_cells), format)

    def add_product_category(self, category, row):
        col = self.start_col
        start_row = row

        for product in category.product_set.all():
            self.worksheet.set_row(row, None, None, {'level': 1})
            self.worksheet.write(row, col, product.pk, self.theme.center)
            self.worksheet.write(row, col+1, unicode(product.category), self.theme.center_bold)
            self.worksheet.write(row, col+2, product.name, self.theme.center_bold)

            total_sold_cells = []
            total_sum_cells = []

            for i, distr_point in enumerate(self.distribution_points):
                sold_cells = []
                sold_summ_cells = []

                for k, period in enumerate(self.periods):
                    info = self.data.get(product, distr_point, period)

                    coli = col + 3 + i * (self.periods_count * self.period_col_count + 2) + k * self.period_col_count

                    self.worksheet.write_number(row, coli, info['base_price'], self.theme.currency)
                    self.worksheet.write_number(row, coli+1, info['cost'], self.theme.currency)
                    self.worksheet.write_number(row, coli+2, info['sold'], self.theme.default)
                    self.worksheet.write_formula(row, coli+3, '=%s*%s' % (xl_rowcol_to_cell(row, coli+1), xl_rowcol_to_cell(row, coli+2)), self.theme.currency)
                    self.worksheet.write_number(row, coli+4, info['left'], self.theme.default)

                    sold_cells.append(xl_rowcol_to_cell(row, coli+2))
                    sold_summ_cells.append(xl_rowcol_to_cell(row, coli+3))

                # add total cols for period
                self.worksheet.write_formula(row, coli+5, '=SUM(%s)' % u','.join(sold_cells), self.theme.head6)
                self.worksheet.write_formula(row, coli+6, '=SUM(%s)' % u','.join(sold_summ_cells), self.theme.head6_currency)

                total_sold_cells.append(xl_rowcol_to_cell(row, coli+5))
                total_sum_cells.append(xl_rowcol_to_cell(row, coli+6))

            self.worksheet.write_formula(row, coli+7, '=%s' % '+'.join(total_sold_cells), self.theme.head1)
            self.worksheet.write_formula(row, coli+8, '=%s' % '+'.join(total_sum_cells), self.theme.head1_currency)

            row += 1

        self.worksheet.merge_range(row, col, row, col+2, unicode(category), self.theme.total)

        # add total for product category
        for i, distr_point in enumerate(self.distribution_points):
            for k, period in enumerate(self.periods):
                coli = col + 3 + i * (self.periods_count * self.period_col_count + 2) + k * self.period_col_count
                self.worksheet.write(row, coli, '=', self.theme.total)
                coli += 1
                self.worksheet.write(row, coli, '=', self.theme.total)
                coli += 1
                self.worksheet.write_formula(row, coli, '=SUM(%s)' % xl_range(start_row, coli, row-1, coli), self.theme.total)
                coli += 1
                self.worksheet.write_formula(row, coli, '=SUM(%s)' % xl_range(start_row, coli, row-1, coli), self.theme.total_currency)
                coli += 1
                self.worksheet.write_formula(row, coli, '=SUM(%s)' % xl_range(start_row, coli, row-1, coli), self.theme.total)
            coli += 1
            self.worksheet.write_formula(row, coli, '=SUM(%s)' % xl_range(start_row, coli, row-1, coli), self.theme.head6)
            coli += 1
            self.worksheet.write_formula(row, coli, '=SUM(%s)' % xl_range(start_row, coli, row-1, coli), self.theme.head6_currency)

        coli += 1
        self.worksheet.write_formula(row, coli, '=SUM(%s)' % xl_range(start_row, coli, row-1, coli), self.theme.head1)
        coli += 1
        self.worksheet.write_formula(row, coli, '=SUM(%s)' % xl_range(start_row, coli, row-1, coli), self.theme.head1_currency)

        row += 1
        return row

    def add_restrebution_point(self, distr_point, start_col):
        self.worksheet.set_column(start_col, start_col+self.periods_count*self.period_col_count-1, 10, None, {
            'hidden': 0 if self.dev else 1,
            'level': 1,
            'collapsed': 1
        })
        self.worksheet.set_column(start_col+self.periods_count*self.period_col_count+1, start_col+self.periods_count*self.period_col_count+2, 10, None)
        self.head.render_restrebution_point(self.start_row, start_col, unicode(distr_point), distr_point.address, self.periods)
        return start_col+self.periods_count*self.period_col_count+2

    def render(self):
        if self.periods_count:
            self.head.render_head(self.start_row, self.start_col)
            self.render_data()
            total_col = self.start_col + 3 + len(self.distribution_points) * (self.periods_count * self.period_col_count + 2)

            self.worksheet.set_column(total_col, total_col+1, 15)
            self.head.render_total(self.start_row, total_col)


class Head(object):

    def __init__(self, worksheet, theme):
        self.theme = theme
        self.worksheet = worksheet
        self.period_col_count = 5

    def render_head(self, r, c):
        self.worksheet.merge_range(r, c, r, c+2, u'Сеть', self.theme.head1)
        self.worksheet.merge_range(r+1, c, r+1, c+2, u'Адрес', self.theme.head2)
        self.worksheet.merge_range(r+2, c, r+2, c+2, u'Дата', self.theme.head3)

        self.worksheet.write(r+3, c, u'№ п/п', self.theme.head4)
        self.worksheet.write(r+3, c+1, u'Категория', self.theme.head4)
        self.worksheet.write(r+3, c+2, u'Артикул', self.theme.head4)

    def render_restrebution_point(self, r, c, name, address, periods=[]):
        self.worksheet.merge_range(r, c, r, c+len(periods)*self.period_col_count+1, name, self.theme.head1)
        self.worksheet.merge_range(r+1, c, r+1, c+len(periods)*self.period_col_count+1, address, self.theme.head2)

        for i, period in enumerate(periods):
            sc = c+i*self.period_col_count
            self.worksheet.merge_range(r+2, sc, r+2, sc+4, unicode(period), self.theme.head3)

            self.worksheet.write(r+3, sc, u'РРЦ', self.theme.head5)
            self.worksheet.write(r+3, sc+1, u'Цена за единицу', self.theme.head5)
            self.worksheet.write(r+3, sc+2, u'Кол-во продаж', self.theme.head5)
            self.worksheet.write(r+3, sc+3, u'Стоимость продаж', self.theme.head5)
            self.worksheet.write(r+3, sc+4, u'Наличие товара', self.theme.head5)

        sc = c + len(periods)*self.period_col_count

        self.worksheet.merge_range(r+2, sc, r+2, sc+1, '', self.theme.head3)
        self.worksheet.write(r+3, sc, u'Кол-во продаж', self.theme.head6)
        self.worksheet.write(r+3, sc+1, u'Стоимость продаж', self.theme.head6)

    def render_total(self, r, c):
        self.worksheet.merge_range(r, c, r+2, c+1, u'', self.theme.head1)
        self.worksheet.write(r+3, c, u'Кол-во продаж за неделю', self.theme.head1)
        self.worksheet.write(r+3, c+1, u'Стоимость продаж', self.theme.head1)


class Theme(object):

    def __init__(self, workbook):
        self.workbook = workbook
        self._init_head_formats()
        self._init_table_formats()

    def _init_table_formats(self):
        self.cell = self.workbook.add_format({
            'shrink': True
        })

    def _init_head_formats(self):
        num_format = u'# ##0.00"p."'
        base_styles = {
            'font_size': 11,
            'bold': True,
            'align': 'center',
            'border': 1,
            'shrink': True,
            'border_color': '#000000'
        }
        self.default = self.workbook.add_format(dict(base_styles, **{
            'bold': False
        }))
        self.total = self.workbook.add_format(dict(base_styles, **{
            'bg_color': '#aaaaaa',
            'font_color': '#000000'
        }))
        self.total_currency = self.workbook.add_format(dict(base_styles, **{
            'bg_color': '#aaaaaa',
            'font_color': '#000000'
        }))
        self.total_currency.set_num_format(num_format)
        self.head1 = self.workbook.add_format(dict(base_styles, **{
            'bg_color': '#1c3259',
            'font_color': '#ffffff'
        }))
        self.head1_currency = self.workbook.add_format(dict(base_styles, **{
            'bg_color': '#1c3259',
            'font_color': '#ffffff'
        }))
        self.head1_currency.set_num_format(num_format)
        self.head2 = self.workbook.add_format(dict(base_styles, **{
            'bg_color': '#558ed5',
            'font_color': '#000000'
        }))
        self.head3 = self.workbook.add_format(dict(base_styles, **{
            'bg_color': '#01b0f1',
            'font_color': '#000000'
        }))
        self.head4 = self.workbook.add_format(dict(base_styles, **{
            'bg_color': '#558ed5',
            'font_color': '#000000',
            'valign': 'vcenter'
        }))
        self.head5 = self.workbook.add_format(dict(base_styles, **{
            'bg_color': '#558ed5',
            'font_color': '#000000',
            'valign': 'vcenter',
            'font_size': 10,
            'bold': False
        }))
        self.head6 = self.workbook.add_format(dict(base_styles, **{
            'bg_color': '#558ed5',
            'font_color': '#ffffff',
            'valign': 'vcenter',
            'font_size': 10,
            'bold': False
        }))
        self.head6_currency = self.workbook.add_format(dict(base_styles, **{
            'bg_color': '#558ed5',
            'font_color': '#ffffff',
            'valign': 'vcenter',
            'font_size': 10,
            'bold': False,
        }))
        self.head6_currency.set_num_format(num_format)
        self.center = self.workbook.add_format(dict(base_styles, **{
            'valign': 'vcenter',
            'bold': False
        }))
        self.center_bold = self.workbook.add_format(dict(base_styles, **{
            'valign': 'vcenter',
        }))
        self.currency = self.workbook.add_format(dict(base_styles, **{
            'bold': False
        }))
        self.currency.set_num_format(num_format)

forms.py

class ExportForm(forms.Form):
    start_date = forms.DateField(label=_(u'Start date'), widget=DateWidget)
    end_date = forms.DateField(label=_(u'End date'), widget=DateWidget)
    projects = forms.ModelMultipleChoiceField(label=_(u'projects'), queryset=Project.objects.none(),
        widget=forms.CheckboxSelectMultiple)

    def __init__(self, user, *args, **kwargs):
        self.user = user
        super(ExportForm, self).__init__(*args, **kwargs)
        self.fields['projects'].queryset = Project.objects.filter(client__users=self.user)

    def export(self):
        start_date = self.cleaned_data['start_date']
        end_date = self.cleaned_data['end_date']
        projects = self.cleaned_data['projects']

        f = StringIO()
        workbook = Workbook(f)
        theme = Theme(workbook)

        for project in projects:
            sheet = Worksheet(project, start_date, end_date, workbook, theme)
            sheet.render()

        workbook.close()
        return f.getvalue()

views.py

def client_dashboard(request):
    export_form = ExportForm(request.user, request.POST or None)

    if export_form.is_valid():
        resp = HttpResponse(export_form.export(), mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        resp['Content-Disposition'] = 'attachment; filename=report.xlsx'
        return resp

    context = {
        'export_form': export_form,
    }
    return render(request, 'main/client_dashboard.html', context)