Exemple #1
0
    def add_header_and_footer(file_name, title, description, show_legend, table_description):
        """
        Add header and footer to excel file.

        :param file_name: Excel file name.
        :param title: Query tile.
        :param description: Query description.
        """
        workbook = load_workbook(filename=file_name, use_iterators=True)
        sheet = workbook.active
        new_workbook = OWorkbook(encoding="UTF-8")
        header_len = 5
        new_sheet = new_workbook.active

        title_label = unicode(_("Title"))
        table_description_label = unicode(_("Argomento"))

        line_num = 2

        r_alignment = OAlignment(horizontal='right')
        l_alignment = OAlignment(horizontal='left')
        top_and_left_alignment = OAlignment(vertical='top', horizontal='left', wrapText=True)

        header_fill = PatternFill(patternType='solid',
                                  fgColor=Color('8B1F3F'))

        header_font = Font(color="FFFFFF", bold=True)
        header_style = Style(fill=header_fill, font=header_font, alignment=top_and_left_alignment)

        body_font = Font(color="1F556F")

        r_style = Style(font=body_font, alignment=r_alignment)
        body_style = Style(font=body_font)

        cell = new_sheet.cell(row=1, column=1)
        cell.value = table_description_label
        cell.style = header_style

        cell = new_sheet.cell(row=1, column=2)
        cell.value = table_description
        cell.style = header_style

        new_sheet.merge_cells(start_row=1, start_column=2,
                              end_row=1, end_column=header_len+1)


        cell = new_sheet.cell(row=2, column=1)
        cell.value = title_label
        cell.style = header_style

        cell = new_sheet.cell(row=2, column=2)
        cell.value = title
        cell.style = header_style

        new_sheet.merge_cells(start_row=2, start_column=2,
                              end_row=2, end_column=header_len+1)

        if description is not None:
            description_label = unicode(_("Description"))
            s = StringIO.StringIO(description)
            char_per_cell = 10
            for line in s:
                line_num += 1
                if len(line) > char_per_cell * header_len:
                    add = (len(line) / (char_per_cell * header_len)) + 1
                    line_num += add

            cell = new_sheet.cell(row=3, column=1)
            cell.style = header_style
            cell.value = description_label
            new_sheet.merge_cells(start_row=3,
                                  start_column=1,
                                  end_row=line_num + 1,
                                  end_column=1)

            cell = new_sheet.cell(row=3, column=2)
            cell.style = header_style
            cell.value = description

            new_sheet.merge_cells(start_row=3,
                                  start_column=2,
                                  end_row=line_num + 1,
                                  end_column=header_len + 1)

        if show_legend == 'True':
            line_num += 3
            cell = new_sheet.cell(row=line_num, column=1)
            cell.value = "%s (%s)" % (settings.LEGEND, settings.DL_ART)
            cell.style = body_style
            legend_cells = 4
            new_sheet.merge_cells(start_row=line_num,
                                  start_column=1,
                                  end_row=line_num,
                                  end_column=legend_cells + 1)

        k = 1 + line_num
        max_widths = dict()

        default_width = 10
        for r, row in enumerate(sheet.iter_rows()):
            for c, cell in enumerate(row):
                max_widths[c] = default_width
            break

        # Copy rows from existing sheet.
        for r, row in enumerate(sheet.iter_rows()):
            for c, cell in enumerate(row):
                value = cell.value
                if value is None:
                    continue
                cell = new_sheet.cell(row=r + k + 1, column=c + 1)
                if isinstance(value, six.string_types):
                    value = value.strip()
                    value = value.encode('utf-8')
                    if value.startswith("*") or value.isdigit():
                        cell.style = r_style
                    else:
                        cell.style = body_style
                else:
                    cell.style = body_style
                cell.value = value
                r_size = arial10.fit_width(str(value), False) / 255
                column_len = round(r_size)
                if r >= 1 and column_len > max_widths[c]:
                        max_widths[c] = column_len

        for i in max_widths:
            column_letter = get_column_letter(i+1)
            new_sheet.column_dimensions[column_letter].width = max_widths[i]

        sheet_rows = len(new_sheet.rows)
        k = k + sheet_rows - 1

        stat_bitmap = 'l4s/static/img/testata_Statistica.bmp'

        """
        if settings.DEBUG:
            stat_bitmap = 'l4s/static/img/testata_Statistica.bmp'
        else:
            stat_bitmap = static('/img/testata_Statistica.bmp')
        """

        stat_img = Image(stat_bitmap)
        stat_img.drawing.left = 0
        stat_img.drawing.top = k * 19

        #new_cell = new_sheet.cell(row=k, column=1)
        #stat_img.anchor(new_cell)
        new_sheet.add_image(stat_img)

        new_workbook.save(file_name)
Exemple #2
0
    def generate_report():
        extension = 'xls'
        f = NamedTemporaryFile(suffix=extension)

        content_type = 'application/vnd.ms-excel'
        # Setup response
        queries_s = request.REQUEST.get('queries')
        queries = eval("[%s]" % queries_s)[0]
        manual_request_s = request.REQUEST.get('manual_requests')
        manual_requests = eval("[%s]" % manual_request_s)[0]
        run_queries_auth_s = request.REQUEST.get('run_queries_auth')
        run_queries_auth = eval("[%s]" % run_queries_auth_s)[0]
        run_queries_anon_s = request.REQUEST.get('run_queries_anon')
        run_queries_anon = eval("[%s]" % run_queries_anon_s)[0]

        new_workbook = new_xlwt_colored_workbook()

        new_sheet = new_workbook.add_sheet("Lod4Stat", cell_overwrite_ok=True)
        head_cfg = 'pattern: pattern solid, fore_colour custom_colour;'
        head_cfg += 'font: colour white, bold True;'
        head_cfg += 'alignment: horizontal left, vertical top, wrap true;'
        head_cell = easyxf(head_cfg)
        header_len = 5
        us_title = unicode(_('Usage report'))
        year = request.REQUEST.get('year')
        month = request.REQUEST.get('month')
        us_title += " " + unicode(_('Year')) + " " + year
        if month != "None":
            month_name = calendar.month_name[ast.literal_eval(month)]
            us_title += ", " + unicode(_('Month')).lower() + " " + month_name

        body_cfg = 'font: colour blue;'
        body_cfg += 'alignment: vertical top, wrap true;'
        body_cell = easyxf(body_cfg)

        new_sheet.write(0, 0, us_title, head_cell)
        new_sheet.write_merge(0, 0, 0, header_len - 1, us_title, head_cell)

        max_widths = dict()
        default_width = 10
        for col in range(2):
            max_widths[col] = default_width

        start = 3
        title = unicode(_("Number of saved queries for user types"))
        new_sheet.write(start, 0, title, head_cell)
        new_sheet.write_merge(start, start, 0, 1, title, head_cell)
        start = start + 1

        title = unicode(_("User type"))
        new_sheet.write(start, 0, title, head_cell)
        column_len = arial10.fit_width(title, False)
        if column_len > max_widths[0]:
            max_widths[0] = column_len
        title = unicode(_("Saved queries"))
        new_sheet.write(start, 1, title, head_cell)
        column_len = arial10.fit_width(title, False)
        if column_len > max_widths[1]:
            max_widths[1] = column_len
        start = start + 1

        q = 0
        for q, query in enumerate(queries):
            new_sheet.write(q + start, 0, query[0], body_cell)
            column_len = arial10.fit_width(query[0], False)
            if column_len > max_widths[0]:
                max_widths[0] = column_len
            new_sheet.write(q + start, 1, query[1], body_cell)

        start = start + q + 3
        title = unicode(_("Number of manual requests for user types"))
        new_sheet.write(start, 0, title, head_cell)
        new_sheet.write_merge(start, start, 0, 1, title, head_cell)
        start = start + 1

        title = unicode(_("User type"))
        new_sheet.write(start, 0, title, head_cell)
        column_len = arial10.fit_width(title, False)
        if column_len > max_widths[0]:
            max_widths[0] = column_len
        title = unicode(_("Manual requests"))
        new_sheet.write(start, 1, title, head_cell)
        column_len = arial10.fit_width(title, False)
        if column_len > max_widths[1]:
            max_widths[1] = column_len

        start = start + 1

        for q, query in enumerate(manual_requests):
            new_sheet.write(q + start, 0, query[0], body_cell)
            new_sheet.write(q + start, 1, query[1], body_cell)
            column_len = arial10.fit_width(query[0], False)
            if column_len > max_widths[0]:
                max_widths[0] = column_len
            new_sheet.write(q + start, 1, query[1], body_cell)

        start = start + q + 3
        title = _("Number of executed queries for user types (logged users)")
        title = unicode(title)
        new_sheet.write(start, 0, title, head_cell)
        new_sheet.write_merge(start, start, 0, 1, title, head_cell)
        start = start + 1

        title = unicode(_("User type"))
        new_sheet.write(start, 0, title, head_cell)
        title = unicode(_("Run queries"))
        new_sheet.write(start, 1, title, head_cell)
        column_len = arial10.fit_width(title, False)
        if column_len > max_widths[1]:
            max_widths[1] = column_len

        start = start + 1

        for q, query in enumerate(run_queries_auth):
            new_sheet.write(q + start, 0, query[0], body_cell)
            new_sheet.write(q + start, 1, query[1], body_cell)
            column_len = arial10.fit_width(query[0], False)
            if column_len > max_widths[0]:
                max_widths[0] = column_len
            new_sheet.write(q + start, 1, query[1], body_cell)


        start = start + q + 3
        title = unicode(_("Number of executed queries (anonymous users)"))
        new_sheet.write(start, 0, title, head_cell)
        new_sheet.write_merge(start, start, 0, 1, title, head_cell)
        start = start + 1
        title = unicode(_("Run queries"))
        new_sheet.write(start, 0, title, head_cell)
        new_sheet.write_merge(start, start, 0, 1, title, head_cell)
        column_len = arial10.fit_width(title, False)
        if column_len > max_widths[0]:
            max_widths[0] = column_len

        start = start + 1

        for q, query in enumerate(run_queries_anon):
            new_sheet.write(q + start, 0, query[0], body_cell)
            new_sheet.write_merge(q + start,
                                  q + start,
                                  0,
                                  1,
                                  query[0],
                                  body_cell)
            column_len = arial10.fit_width(str(query[0]), False)
            if column_len > max_widths[0]:
                max_widths[0] = column_len

        # Adjust column width.
        for col in max_widths:
            new_sheet.col(col).width = round(max_widths[col]).__int__() + 1

        start = start + q + 3

        stat_bitmap = 'l4s/static/img/testata_Statistica.bmp'

        """
        if settings.DEBUG:
            stat_bitmap = 'l4s/static/img/testata_Statistica.bmp'
        else:
            stat_bitmap = static('/img/testata_Statistica.bmp')
        """

        new_sheet.insert_bitmap(stat_bitmap, start, 0)

        new_workbook.save(f.name)

        title = us_title.strip().encode("UTF-8").replace(" ", '_')

        if len(title) > max_length_filename:
            title = title[:max_length_filename]

        filename = '%s.%s' % (title, extension)

        data = f.read()
        response = HttpResponse(data)
        response["Content-Type"] = content_type
        response['Content-Transfer-Encoding'] = 'binary'
        response[
            'Content-Disposition'] = 'attachment; filename="%s"' % filename
        return response
Exemple #3
0
    def add_header_and_footer(file_name, title, description, show_legend, table_description):
        """
        Add header and footer to excel file.

        :param file_name: Excel file name.
        :param title: Query tile.
        :param description: Query description.
        """
        workbook = open_workbook(file_name)
        sheet = workbook.sheet_by_index(0)
        header_len = 5
        sheet_rows = sheet.nrows

        new_workbook = new_xlwt_colored_workbook()

        head_cfg = 'pattern: pattern solid, fore_colour custom_colour;'
        head_cfg += 'font: colour white, bold True;'
        head_cfg += 'alignment: horizontal left, vertical top, wrap true;'
        head_cell = easyxf(head_cfg)

        body_cfg = 'font: colour blue;'
        body_cfg += 'alignment: vertical top, wrap true;'
        body_cell = easyxf(body_cfg)

        ast_cfg = 'font: colour blue;'
        ast_cfg += 'alignment: vertical top, horizontal right, wrap true;'
        ast_cell = easyxf(ast_cfg)

        new_sheet = new_workbook.add_sheet("Lod4Stat", cell_overwrite_ok=True)

        table_description_label = unicode(_("Argomento"))
        new_sheet.write(0, 0, table_description_label, head_cell)
        new_sheet.write(0, 1, table_description, head_cell)
        new_sheet.write_merge(0, 0, 1, header_len, table_description, head_cell)

        title_label = unicode(_("Title"))
        new_sheet.write(1, 0, title_label, head_cell)
        new_sheet.write(1, 1, title, head_cell)
        new_sheet.write_merge(1, 1, 1, header_len, title, head_cell)


        line_num = 2
        if description is not None:
            description_label = unicode(_("Description"))
            s = StringIO.StringIO(description)
            char_per_cell = 10
            for line in s:
                line_num += 1
                if len(line) > char_per_cell * header_len:
                    add = (len(line) / (char_per_cell * header_len)) + 1
                    line_num += add

            new_sheet.write(2, 0, description_label, head_cell)
            new_sheet.write_merge(2, line_num, 0, 0, description_label, head_cell)
            new_sheet.write(2, 1, description, head_cell)
            new_sheet.write_merge(2, line_num, 1, header_len, description, head_cell)

        #print "show_legend ", show_legend

        if show_legend == 'True':
            legend = "%s (%s)" % (settings.LEGEND, settings.DL_ART)
            legend_cells = 4
            new_sheet.write(line_num + 2, 0, legend, body_cell)
            new_sheet.write_merge(line_num + 2,
                                  line_num + 2,
                                  0,
                                  legend_cells,
                                  legend,
                                  body_cell)
            line_num += 2

        k = 2 + line_num
        max_widths = dict()
        default_width = 10
        for col in range(sheet.ncols):
            max_widths[col] = default_width
        #Copy rows from existing sheets
        for rows in range(0, sheet_rows):
            data = [sheet.cell_value(rows, col) for col in range(sheet.ncols)]
            for index, value in enumerate(data):
                #print index, value
                column_len = arial10.fit_width(value, False)
                #print column_len
                #print isinstance(value, unicode)
                if isinstance(value, unicode):
                    if value.isdigit():
                        #print "a"
                        #print "prima '",value,"'"
                        #print type(value)

                        if len(value) > 1 and value.startswith("0"): #sembra un baco della eval quando i numeri cominciano con 0
                            value = str(value)
                        else:
                            value = ast.literal_eval(value)

                        #print "dopo ", value
                        #print type(value)
                        new_sheet.write(rows+k, index, value, body_cell)
                    else:
                        #print "b"
                        #print value
                        value = value.strip()
                        if value.startswith("*"):
                            new_sheet.write(rows+k, index, value, ast_cell)
                        else:
                            new_sheet.write(rows+k, index, value, body_cell)
                else:
                    new_sheet.write(rows+k, index, value, body_cell)
                if column_len > max_widths[index]:
                    max_widths[index] = column_len
        # Adjust column width.
        for col in max_widths:
            new_sheet.col(col).width = round(max_widths[col]).__int__() + 1
            new_sheet.col(col)

        k = k + sheet_rows + 1

        stat_bitmap = 'l4s/static/img/testata_Statistica.bmp'

        """
        if settings.DEBUG:
            stat_bitmap = 'l4s/static/img/testata_Statistica.bmp'
        else:
            stat_bitmap = static('/img/testata_Statistica.bmp')
        """

        new_sheet.insert_bitmap(stat_bitmap, k, 0)
        new_workbook.save(file_name)