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)
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
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)