def styling_rows_fast():
from pyexcelerate import Workbook, Color
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = 123456
ws[1].style.fill.background = Color(255, 0, 0)
wb.save("styling_rows_fast.xlsx")
def styling_columns_fastest():
from pyexcelerate import Workbook, Color, Style, Fill
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = 123456
ws.set_col_style(1, Style(fill=Fill(background=Color(255, 0, 0, 0))))
wb.save("styling_columns_fastest.xlsx")
def styling_available():
from pyexcelerate import Workbook, Color, Style, Fill, Border
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = 123456
ws[1][1].style.font.bold = True
ws[1][1].style.font.italic = True
ws[1][1].style.font.underline = True
ws[1][1].style.font.strikethrough = True
ws[1][1].style.font.color = Color(255, 0, 255)
ws[1][1].style.fill.background = Color(0, 255, 0)
ws[1][1].style.alignment.vertical = 'top'
ws[1][1].style.alignment.horizontal = 'right'
ws[1][1].style.alignment.rotation = 90
ws[1][1].style.alignment.wrap_text = True
ws[1][1].style.borders.top.color = Color(255, 0, 0)
ws[1][
1].style.borders.right.style = '-.' # available: .-, ..-, --, .., =, ., medium -., medium -.., medium --, /-., _
wb.save("styling_available.xlsx")
def styling_ranges():
from pyexcelerate import Workbook, Color
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("test")
ws.range("A1", "C3").value = 1
ws.range("A1", "C1").style.font.bold = True
ws.range("A2", "C3").style.font.italic = True
ws.range("A3", "C3").style.fill.background = Color(255, 0, 0, 0)
ws.range("C1", "C3").style.font.strikethrough = True
wb.save("styling_ranges.xlsx")
def write_excel(file_name, data, engine='pyexcelerate'):
"""
Write multiple sheets to one excel and save to disk.
Ignore df's index and forbidden MultiIndex columns.
Notes:
1. `pyexcelerate` can be nearly three times faster than `pd.to_excel`.
2. save as csv much faster than excel
:param file_name:
:param data: [(sheet_name, df), ...]
:param engine:
pyexcelerate: modify headers' style, display date properly and don't display nan
pd.to_excel:
pd.to_csv: file_name/sheet_name.csv
:return: None
"""
if engine == 'pyexcelerate':
wb = Workbook()
for sheet_name, df in data:
cols = df.columns.tolist()
if len(df) > 0:
# don't display nan
df = df.fillna('')
# display date properly
for col in cols:
if isinstance(df[col].iloc[0], datetime.date):
df[col] = df[col].astype(str)
ws = wb.new_sheet(sheet_name, [cols] + df.values.tolist())
# modify headers' style
h, w = df.shape
right = num2title(w) + '1'
ws.range("A1",
right).style.fill.background = Color(210, 210, 210, 0)
ws.range("A1", right).style.font.bold = True
ws.range("A1", right).style.alignment.horizontal = 'center'
ws.range("A1", right).style.borders.right.style = '_'
ws.range("A1", right).style.borders.bottom.style = '_'
wb.save(file_name)
elif engine == 'pd.to_excel':
writer = pd.ExcelWriter(file_name)
for sheet_name, df in data:
df.to_excel(writer, sheet_name, index=False)
writer.save()
writer.close()
elif engine == 'pd.to_csv':
dir_name = file_name.replace('.xlsx', '')
makedirs(dir_name)
for sheet_name, df in data:
df.to_csv(os.path.join(dir_name, sheet_name + '.csv'), index=False)
else:
pass
def styling_cell_faster():
from pyexcelerate import Workbook, Color
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws.set_cell_value(1, 1, 123456)
ws.get_cell_style(1, 1).font.bold = True
ws.get_cell_style(1, 1).font.italic = True
ws.get_cell_style(1, 1).font.underline = True
ws.get_cell_style(1, 1).font.strikethrough = True
ws.get_cell_style(1, 1).fill.background = Color(0, 255, 0, 0)
ws.set_cell_value(1, 2, datetime.now())
ws.get_cell_style(1, 2).format.format = 'mm/dd/yy'
wb.save("styling_cell_faster.xlsx")
def styling_cell_fast():
from pyexcelerate import Workbook, Color
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = 123456
ws[1][1].style.font.bold = True
ws[1][1].style.font.italic = True
ws[1][1].style.font.underline = True
ws[1][1].style.font.strikethrough = True
ws[1][1].style.fill.background = Color(0, 255, 0, 0)
ws[1][2].value = datetime.now()
ws[1][2].style.format.format = 'mm/dd/yy'
wb.save("styling_cell_fast.xlsx")
def styling_cell_fastest():
from pyexcelerate import Workbook, Color, Style, Font, Fill, Format
from datetime import datetime
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws.set_cell_value(1, 1, 123456)
ws.set_cell_style(1, 1, Style(font=Font(bold=True)))
ws.set_cell_style(1, 1, Style(font=Font(italic=True)))
ws.set_cell_style(1, 1, Style(font=Font(underline=True)))
ws.set_cell_style(1, 1, Style(font=Font(strikethrough=True)))
ws.set_cell_style(1, 1,
Style(fill=Fill(background=Color(255, 228, 75, 52))))
ws.set_cell_value(1, 2, datetime.now())
ws.set_cell_style(1, 2, Style(format=Format('mm/dd/yy')))
wb.save("styling_cell_fastest.xlsx")
def styling_defined_by_objects():
from pyexcelerate import Workbook, Font, Color, Alignment
wb = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = datetime.now()
ws[1][1].style.font = Font(bold=True,
italic=True,
underline=True,
strikethrough=True,
family="Calibri",
size=10,
color=Color(255, 0, 0))
ws[1][1].style.format.format = 'mm/dd/yy'
ws[1][1].style.alignment = Alignment(
horizontal="left", vertical="bottom", rotation=0,
wrap_text=True) #("left", "center", "right"),
wb.save("styling_defined_by_objects.xlsx")
def styling_defined_ALL_style_by_objects():
from pyexcelerate import Workbook, Style, Font, Color, Fill, Alignment, Borders, Border, Format
wb = Workbook()
ws = wb.new_sheet("sheet name")
borders = Borders.Borders(left=Border.Border(color=Color(255, 0, 0),
style="thin"),
right=Border.Border(color=Color(255, 0, 0),
style="mediumDashDotDot"),
top=Border.Border(color=Color(255, 0, 0),
style="double"),
bottom=Border.Border(color=Color(255, 0, 0),
style="slantDashDot"))
ws.cell("E11").value = datetime.now()
ws.cell("E11").style = Style(
font=Font(bold=True,
italic=True,
underline=True,
strikethrough=True,
family="Calibri",
size=20,
color=Color(251, 240, 11)),
fill=Fill(background=Color(33, 133, 255)),
alignment=Alignment(horizontal="left",
vertical="bottom",
rotation=0,
wrap_text=True), #("left", "center", "right"),
borders=borders,
format=Format(
'mm/dd/yy'
), # NOTE: if cell string show ###, then decrease font size or increase col size
# size=-1 # NOTE: don't work, it must use below with row or column statements
)
ws.set_col_style(5, Style(size=-1)) # set width of column # E col
ws.set_row_style(11, Style(size=-1)) # set height of row
wb.save("styling_defined_ALL_style_by_objects.xlsx")
def render_report(request, org_id):
organisation = get_object_or_404(Organisation, pk=org_id)
projects = build_view_object(organisation)
use_indicator_target = False
for project in projects:
if project.use_indicator_target:
print(project.id, project.use_indicator_target)
use_indicator_target = True
break
wb = Workbook()
ws = wb.new_sheet('ProjectList')
ws.set_col_style(1, Style(size=81.5))
ws.set_col_style(2, Style(size=33.5))
ws.set_col_style(3, Style(size=27))
ws.set_col_style(4, Style(size=21))
ws.set_col_style(5, Style(size=21))
ws.set_col_style(6, Style(size=21))
ws.set_col_style(7, Style(size=21))
ws.set_col_style(8, Style(size=33.5))
ws.set_col_style(9, Style(size=33.5))
ws.set_col_style(10, Style(size=14))
ws.set_col_style(11, Style(size=33.5))
ws.set_col_style(12, Style(size=33.5))
ws.set_col_style(13, Style(size=10))
ws.set_col_style(14, Style(size=10))
ws.set_col_style(15, Style(size=10))
ws.set_col_style(16, Style(size=10))
ws.set_col_style(17, Style(size=33.5))
ws.set_col_style(18, Style(size=20))
ws.set_col_style(19, Style(size=20))
ws.set_col_style(20, Style(size=20))
ws.set_col_style(21, Style(size=33.5))
ws.set_col_style(22, Style(size=10))
ws.set_col_style(23, Style(size=33.5))
ws.set_col_style(24, Style(size=10))
ws.set_col_style(25, Style(size=10))
ws.set_col_style(26, Style(size=33.5))
ws.set_col_style(27, Style(size=14))
ws.set_col_style(28, Style(size=14))
ws.set_col_style(29, Style(size=14))
ws.set_col_style(30, Style(size=14))
# r1
ws.set_cell_style(1, 1, Style(font=Font(bold=True, size=24)))
ws.set_cell_value(1, 1, 'Organisation Results and Indicators simple table report')
# r3
for col in range(1, 31):
ws.set_cell_style(3, col, Style(
font=Font(bold=True, size=14, color=Color(255, 255, 255)),
fill=Fill(background=Color(88, 88, 87))
))
ws.set_cell_value(3, 1, 'Project name')
ws.set_cell_value(3, 2, 'Subtitle')
ws.set_cell_value(3, 3, 'IATI id')
ws.set_cell_value(3, 4, 'Date start planned')
ws.set_cell_value(3, 5, 'Date end planned')
ws.set_cell_value(3, 6, 'Date start actual')
ws.set_cell_value(3, 7, 'Date end actual')
ws.set_cell_value(3, 8, 'Result title')
ws.set_cell_value(3, 9, 'Result description')
ws.set_cell_value(3, 10, 'Aggregation')
ws.set_cell_value(3, 11, 'Indicator title')
ws.set_cell_value(3, 12, 'Indicator description')
ws.set_cell_value(3, 13, 'Measure')
ws.set_cell_value(3, 14, 'Ascending')
ws.set_cell_value(3, 15, 'Baseline year')
ws.set_cell_value(3, 16, 'Baseline value')
ws.set_cell_value(3, 17, 'Baseline comment')
col = 17
if use_indicator_target:
col += 1
ws.set_cell_value(3, col, 'Target')
col += 1
ws.set_cell_value(3, col, 'Target comment')
col += 1
ws.set_cell_value(3, col, 'Period start')
col += 1
ws.set_cell_value(3, col, 'Period end')
if not use_indicator_target:
col += 1
ws.set_cell_value(3, col, 'Target value')
col += 1
ws.set_cell_value(3, col, 'Target comment')
ws.set_cell_value(3, 22, 'Actual value')
ws.set_cell_value(3, 23, 'Actual comment')
ws.set_cell_value(3, 24, 'Country')
ws.set_cell_value(3, 25, 'Type')
ws.set_cell_value(3, 26, 'Related partners')
ws.set_cell_value(3, 27, 'Project id')
ws.set_cell_value(3, 28, 'Result id')
ws.set_cell_value(3, 29, 'Indicator id')
ws.set_cell_value(3, 30, 'Period id')
wrap_text = [2, 8, 9, 11, 12, 17, 21, 23]
row = 4
for key, project in enumerate(projects):
highlight = (key % 2) == 0
for result in project.results:
for indicator in result.indicators:
for period in indicator.periods:
for col in range(1, 31):
ws.set_cell_style(row, col, Style(
alignment=Alignment(wrap_text=True) if col in wrap_text else None,
fill=Fill(background=Color(217, 217, 217)) if highlight else None
))
# The empty strings are sort of a hack because the style formatting
# are not applied on a cell with empty content.
ws.set_cell_value(row, 1, project.title or ' ')
ws.set_cell_value(row, 2, project.subtitle or ' ')
ws.set_cell_value(row, 3, project.iati_activity_id or ' ')
ws.set_cell_value(row, 4, project.date_start_planned or ' ')
ws.set_cell_value(row, 5, project.date_end_planned or ' ')
ws.set_cell_value(row, 6, project.date_start_actual or ' ')
ws.set_cell_value(row, 7, project.date_end_planned or ' ')
ws.set_cell_value(row, 8, result.title or ' ')
ws.set_cell_value(row, 9, result.description or ' ')
ws.set_cell_value(row, 10, 'Yes' if result.aggregation_status else 'No')
ws.set_cell_value(row, 11, indicator.title or ' ')
ws.set_cell_value(row, 12, indicator.description or ' ')
ws.set_cell_value(row, 13, 'Percentage' if indicator.measure == PERCENTAGE_MEASURE else 'Unit')
ws.set_cell_value(row, 14, 'Yes' if indicator.ascending else 'No')
ws.set_cell_value(row, 15, indicator.baseline_year or ' ')
ws.set_cell_value(row, 16, indicator.baseline_value or ' ')
ws.set_cell_value(row, 17, indicator.baseline_comment or ' ')
col = 17
if use_indicator_target:
col += 1
ws.set_cell_value(row, col, indicator.target_value or ' ')
col += 1
ws.set_cell_value(row, col, indicator.target_comment or ' ')
col += 1
ws.set_cell_value(row, col, utils.get_period_start(period, project.in_eutf_hierarchy) or ' ')
col += 1
ws.set_cell_value(row, col, utils.get_period_end(period, project.in_eutf_hierarchy) or ' ')
if not use_indicator_target:
col += 1
ws.set_cell_value(row, col, period.target_value or ' ')
col += 1
ws.set_cell_value(row, col, period.target_comment or ' ')
ws.set_cell_value(row, 22, period.actual_value or ' ')
ws.set_cell_value(row, 23, period.actual_comment or ' ')
ws.set_cell_value(row, 24, project.country_codes or ' ')
ws.set_cell_value(row, 25, result.iati_type_name or ' ')
ws.set_cell_value(row, 26, project.partner_names or ' ')
ws.set_cell_value(row, 27, project.id)
ws.set_cell_value(row, 28, result.id)
ws.set_cell_value(row, 29, indicator.id)
ws.set_cell_value(row, 30, period.id)
row += 1
filename = '{}-{}-results-and-indicators-simple-table.xlsx'.format(
datetime.now().strftime('%Y%m%d'), organisation.id)
return utils.make_excel_response(wb, filename)
def _render_excel(reader):
section_title_style = Style(font=Font(size=14, bold=True))
table_header_style = Style(font=Font(bold=True))
wb = Workbook()
ws = wb.new_sheet('Sheet0')
ws.set_col_style(1, Style(size=35))
ws.set_col_style(2, Style(size=19))
ws.set_col_style(3, Style(size=26))
ws.set_col_style(4, Style(size=56))
ws.set_col_style(5, Style(size=11))
ws.set_col_style(6, Style(size=5))
ws.set_col_style(7, Style(size=10))
ws.set_col_style(8, Style(size=12))
ws.set_col_style(9, Style(size=12))
ws.set_col_style(10, Style(size=21))
ws.set_col_style(11, Style(size=7))
ws.set_col_style(12, Style(size=16))
ws.set_col_style(13, Style(size=17))
# r1
ws.range('A1', 'B1').merge()
ws.set_cell_style(
1, 1, Style(font=Font(size=18, bold=True, color=Color(128, 128, 128))))
ws.set_cell_value(1, 1, 'RSR Project overview report')
# r2
ws.set_row_style(2, Style(size=24))
ws.range('A2', 'C2').merge()
ws.set_cell_style(2, 1, section_title_style)
ws.set_cell_value(2, 1, reader.name)
# r3
ws.range('B3', 'C3').merge()
ws.set_cell_value(3, 2, reader.location['address_1'])
# r4
ws.range('B4', 'C4').merge()
ws.set_cell_value(4, 2, reader.location['address_2'])
# r5
ws.range('B5', 'C5').merge()
ws.set_cell_value(5, 2, reader.location['city'])
# r6
ws.range('B6', 'C6').merge()
ws.set_cell_value(6, 2, reader.location['state'])
# r7
ws.range('B7', 'C7').merge()
ws.set_cell_value(7, 2, reader.location['country'])
# r8
ws.set_cell_value(8, 2, 'Phone:')
ws.set_cell_value(8, 3, reader.phone)
# r9
ws.set_cell_value(9, 2, 'Website:')
ws.set_cell_value(9, 3, reader.url)
# r10
ws.set_cell_value(10, 2, 'RSR overview link:')
ws.set_cell_value(10, 3, reader.rsr_link)
# r11
ws.set_cell_style(11, 1, section_title_style)
ws.set_cell_value(11, 1, 'Statistics')
# r12
for i in range(1, 3):
ws.set_cell_style(12, i, table_header_style)
ws.set_cell_value(12, 1, 'Name')
ws.set_cell_value(12, 2, 'Count')
# r13
ws.set_cell_value(13, 1, 'Total number of projects')
ws.set_cell_value(13, 2, reader.projects_count)
# r14
ws.set_cell_value(14, 1, 'Published projects')
ws.set_cell_value(14, 2, reader.published_projects_count)
# r15
ws.set_cell_value(15, 1, 'Unpublished projects')
ws.set_cell_value(15, 2, reader.unpublished_projects_count)
# r16
ws.set_cell_value(16, 1, 'Total number of updates (published)')
ws.set_cell_value(16, 2, reader.updates_count)
# r17
ws.set_cell_value(17, 1, 'Users')
ws.set_cell_value(17, 2, reader.users_count)
# r18
ws.set_cell_style(18, 1, section_title_style)
ws.set_cell_value(18, 1, 'Published project funding')
# r19
for i in range(1, 5):
ws.set_cell_style(19, i, table_header_style)
ws.set_cell_value(19, 1, 'Currency')
ws.set_cell_value(19, 2, 'Budget')
ws.set_cell_value(19, 3, 'Funds')
ws.set_cell_value(19, 4, 'Needed')
# r20
row = 20
for f in reader.published_projects_funding:
ws.set_cell_value(row, 1, f['currency'])
ws.set_cell_value(row, 2, f['total_budget'])
ws.set_cell_value(row, 3, f['total_funds'])
ws.set_cell_value(row, 4, f['total_funds_needed'])
row += 1
# r21
ws.set_cell_style(row, 1, section_title_style)
ws.set_cell_value(row, 1, 'Projects by status')
row += 1
# r22
for i in range(1, 3):
ws.set_cell_style(row, i, table_header_style)
ws.set_cell_value(row, 1, 'Status')
ws.set_cell_value(row, 2, 'Count')
row += 1
# r23
for s in reader.projects_by_activity_status:
ws.set_cell_value(row, 1, s['status'])
ws.set_cell_value(row, 2, s['count'])
row += 1
# r24
ws.set_cell_style(row, 1, section_title_style)
ws.set_cell_value(row, 1, 'Published projects by start year')
row += 1
# r25
for i in range(1, 3):
ws.set_cell_style(row, i, table_header_style)
ws.set_cell_value(row, 1, 'Planned start year')
ws.set_cell_value(row, 2, 'Count')
row += 1
# r26
for p in reader.projects_per_year:
ws.set_cell_value(
row, 1, p['start_year'].strftime('%Y') if p['start_year'] else '')
ws.set_cell_value(row, 2, p['projects_count'])
row += 1
# r27
ws.set_cell_style(row, 1, section_title_style)
ws.set_cell_value(row, 1, 'Published project statistics by country')
row += 1
# r28
for i in range(1, 4):
ws.set_cell_style(row, i, table_header_style)
ws.set_cell_value(row, 1, 'Country')
ws.set_cell_value(row, 2, 'Project count')
ws.set_cell_value(row, 3, 'Update count')
row += 1
# r29
for p in reader.projects_per_country:
ws.set_cell_value(row, 1, p['country'])
ws.set_cell_value(row, 2, p['projects_count'])
ws.set_cell_value(row, 3, p['updates_count'])
row += 1
# r30
ws.set_cell_style(row, 1, section_title_style)
ws.set_cell_value(row, 1, 'Published project overview')
row += 1
# r31
ws.set_cell_value(row, 1, 'Sorted by countries and id')
row += 1
# r32
for i in range(1, 14):
ws.set_cell_style(row, i, table_header_style)
ws.set_cell_value(row, 1, 'Countries')
ws.set_cell_value(row, 2, 'Title')
ws.set_cell_value(row, 3, 'Subtitle')
ws.set_cell_value(row, 4, 'Id')
ws.set_cell_value(row, 5, 'Status')
ws.set_cell_value(row, 6, '¤')
ws.set_cell_value(row, 7, 'Budget')
ws.set_cell_value(row, 8, 'Planned start date')
ws.set_cell_value(row, 9, 'Planned end date')
ws.set_cell_value(row, 10, 'IATI activity id')
ws.set_cell_value(row, 11, '# of updates')
ws.set_cell_value(row, 12, 'Keywords')
ws.set_cell_value(row, 13, 'Project URL')
row += 1
# r33
for country, project in reader.published_projects_overview:
ws.set_cell_value(row, 1, country)
ws.set_cell_value(row, 2, project.title)
ws.set_cell_value(row, 3, project.subtitle)
ws.set_cell_value(row, 4, project.id)
ws.set_cell_value(row, 5, project.iati_status)
ws.set_cell_value(row, 6, project.currency)
ws.set_cell_value(row, 7, project.budget)
ws.set_cell_value(row, 8, project.date_start_planned)
ws.set_cell_value(row, 9, project.date_end_planned)
ws.set_cell_value(row, 10, project.iati_activity_id)
ws.set_cell_value(row, 11, project.updates_count)
ws.set_cell_value(row, 12, project.keyword_labels)
ws.set_cell_value(row, 13, project.absolute_url)
row += 1
filename = '{}-{}-organisation-projects-overview.xlsx'.format(
datetime.now().strftime('%Y%m%d'), reader.id)
return utils.make_excel_response(wb, filename)
def render_report(request, program_id):
program = get_object_or_404(Project.objects.prefetch_related('results'),
pk=program_id)
start_date = utils.parse_date(request.GET.get('period_start', '').strip())
end_date = utils.parse_date(request.GET.get('period_end', '').strip())
project_view = build_view_object(
program, start_date or datetime(1900, 1, 1), end_date
or (datetime.today() + relativedelta(years=10)))
results_by_types = {}
for result in project_view.results:
type = result.iati_type_name
if not type:
continue
if type not in results_by_types:
results_by_types[type] = []
results_by_types[type].append(result)
if not results_by_types:
results_by_types = {'Sheet1': []}
report_title = 'Programme Overview Report{}'.format(': {} - {}'.format(
'' if start_date is None else start_date.strftime('%d-%m-%Y'),
'' if end_date is None else end_date.strftime('%d-%m-%Y')
) if start_date is not None or end_date is not None else '')
disaggregations = get_disaggregations(program)
disaggregation_types_length = 0
for category, types in disaggregations.items():
disaggregation_types_length += len(types.keys())
disaggregations_last_colnum = 7 + (disaggregation_types_length * 2)
wb = Workbook()
for type, results in results_by_types.items():
ws = wb.new_sheet(type)
ws.set_col_style(1, Style(size=60))
ws.set_col_style(2, Style(size=10))
ws.set_col_style(3, Style(size=70))
ws.set_col_style(4, Style(size=25))
ws.set_col_style(5, Style(size=25))
ws.set_col_style(6, Style(size=25))
ws.set_col_style(7, Style(size=25))
# r1
ws.set_row_style(1, Style(size=41))
ws.set_cell_style(1, 1, Style(font=Font(bold=True, size=24)))
ws.set_cell_value(1, 1, report_title)
ws.range('A1', 'C1').merge()
# r2
ws.set_row_style(2, Style(size=36))
ws.set_cell_style(2, 1, Style(font=Font(bold=True, size=12)))
ws.set_cell_value(2, 1, 'Programme title')
ws.set_cell_style(2, 2, Style(font=Font(size=12)))
ws.set_cell_value(2, 2, program.title)
ws.range('B2', 'F2').merge()
# r3
ws.set_row_style(3, Style(size=36))
ws.set_cell_style(3, 1, Style(font=Font(bold=True, size=12)))
ws.set_cell_value(3, 1, 'Result type')
ws.set_cell_style(3, 2, Style(font=Font(size=12)))
ws.set_cell_value(3, 2, '' if type == 'Sheet1' else type)
ws.range('B3', 'C3').merge()
# r4
row = 5
for result in results:
# r5
ws.set_row_style(row, Style(size=36))
result_header1_style = Style(
font=Font(bold=True, size=12, color=Color(255, 255, 255)),
fill=Fill(background=Color(89, 89, 89)))
for i in range(1, 8):
ws.set_cell_style(row, i, result_header1_style)
ws.set_cell_value(row, 1, 'Result title:')
ws.set_cell_value(row, 4, 'Result description:')
if disaggregation_types_length:
ws.set_cell_style(
row, 8,
Style(font=Font(bold=True,
size=12,
color=Color(255, 255, 255)),
alignment=Alignment(horizontal='center'),
fill=Fill(background=Color(89, 89, 89))))
ws.set_cell_value(row, 8, 'Disaggregations')
ws.range(
'H' + str(row),
utils.xl_column_name(disaggregations_last_colnum) +
str(row)).merge()
row += 1
# r6
ws.set_row_style(row, Style(size=42))
result_header2_style = Style(
font=Font(size=12, color=Color(255, 255, 255)),
alignment=Alignment(wrap_text=True),
fill=Fill(background=Color(89, 89, 89)))
result_header_disaggregation_style = Style(
font=Font(size=12, color=Color(255, 255, 255)),
alignment=Alignment(wrap_text=True, horizontal='center'),
fill=Fill(background=Color(89, 89, 89)))
ws.range('A' + str(row), 'C' + str(row)).merge()
ws.set_cell_style(row, 1, result_header2_style)
ws.set_cell_value(row, 1, result.title)
ws.range('D' + str(row), 'G' + str(row)).merge()
ws.set_cell_style(row, 4, result_header2_style)
ws.set_cell_value(row, 4, result.description)
if disaggregation_types_length:
col = 8
for category, types in disaggregations.items():
ws.set_cell_style(row, col,
result_header_disaggregation_style)
ws.set_cell_value(row, col, category.upper())
type_length = len(types.keys()) * 2
next_col = col + type_length
ws.range(
utils.xl_column_name(col) + str(row),
utils.xl_column_name(next_col - 1) + str(row)).merge()
col = next_col
row += 1
for indicator in result.indicators:
# r7
ws.set_row_style(row, Style(size=36))
row7_style = Style(font=Font(bold=True, size=12),
fill=Fill(background=Color(211, 211, 211)))
for i in range(1, disaggregations_last_colnum + 1):
ws.set_cell_style(row, i, row7_style)
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_value(row, 1, 'Indicator title')
ws.set_cell_value(row, 2, 'Indicator description')
ws.set_cell_value(row, 4, 'Indicator type:')
if disaggregation_types_length:
col = 8
types = [
t for ts in disaggregations.values()
for t in ts.keys()
]
for type in types:
ws.set_cell_value(row, col, type)
next_col = col + 2
ws.range(
utils.xl_column_name(col) + str(row),
utils.xl_column_name(next_col - 1) +
str(row)).merge()
col = next_col
row += 1
# r8
row8_style = Style(fill=Fill(background=Color(211, 211, 211)),
alignment=Alignment(wrap_text=True))
for i in range(1, disaggregations_last_colnum + 1):
ws.set_cell_style(row, i, row8_style)
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_value(row, 1, indicator.title)
ws.set_cell_value(row, 2, indicator.description)
ws.set_cell_value(
row, 4, 'Qualitative'
if indicator.is_qualitative else 'Quantitative')
if disaggregation_types_length:
col = 8
while col <= disaggregations_last_colnum:
ws.set_cell_value(row, col, 'value')
col += 1
ws.set_cell_value(row, col, 'target')
col += 1
row += 1
for period in indicator.periods:
# r9
ws.set_row_style(row, Style(size=36))
row9_style = Style(
font=Font(bold=True, size=12),
fill=Fill(background=Color(220, 230, 242)))
for i in range(1, disaggregations_last_colnum + 1):
ws.set_cell_style(row, i, row9_style)
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_value(row, 1, 'Reporting Period:')
ws.set_cell_value(row, 2, 'Number of contrributors')
ws.set_cell_value(row, 4, 'Countries')
ws.set_cell_value(row, 5, 'Aggregated Actual Value')
ws.set_cell_value(row, 6, 'Target value')
ws.set_cell_value(row, 7, '% of Contribution')
row += 1
# r10
number_of_contributors = len(period.contributors)
row10_style = Style(
font=Font(size=12),
fill=Fill(background=Color(220, 230, 242)))
for i in range(1, 7):
ws.set_cell_style(row, i, row10_style)
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_value(
row, 1, '{} - {}'.format(period.period_start,
period.period_end))
ws.set_cell_value(row, 2, number_of_contributors)
ws.set_cell_value(row, 4, len(period.countries))
ws.set_cell_value(row, 5, period.actual_value)
ws.set_cell_value(row, 6, period.target_value)
ws.set_cell_style(
row, 7,
Style(alignment=Alignment(horizontal='right'),
font=Font(size=12),
fill=Fill(background=Color(220, 230, 242))))
ws.set_cell_value(row, 7, '100%')
if disaggregation_types_length:
for i in range(8, disaggregations_last_colnum + 1):
ws.set_cell_style(row, i, row10_style)
col = 8
for category, types in disaggregations.items():
for type in [t for t in types.keys()]:
ws.set_cell_value(
row, col,
period.get_disaggregation_contribution_of(
category, type) or '')
col += 1
ws.set_cell_value(
row, col,
period.get_disaggregation_target_of(
category, type) or '')
col += 1
row += 1
if not number_of_contributors:
continue
for contrib in period.contributors:
# r11
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_style(row, 2, Style(font=Font(bold=True)))
ws.set_cell_value(row, 2, 'Level 1 contributor:')
row += 1
# r12
ws.set_row_style(row, Style(size=30))
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_style(
row, 2,
Style(alignment=Alignment(wrap_text=True,
vertical='top')))
ws.set_cell_value(row, 2, contrib.project.title)
ws.set_cell_style(
row, 4,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(
row, 4, getattr(contrib.country, 'name', ' '))
ws.set_cell_value(row, 5, contrib.updates.total_value)
ws.set_cell_value(row, 6, contrib.target_value)
ws.set_cell_style(
row, 7,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(
row, 7, '{}%'.format(
calculate_percentage(
contrib.updates.total_value,
period.actual_value)))
if disaggregation_types_length:
col = 8
for category, types in disaggregations.items():
for type in [t for t in types.keys()]:
ws.set_cell_value(
row, col,
contrib.get_disaggregation_of(
category, type) or '')
col += 1
ws.set_cell_value(
row, col,
contrib.get_disaggregation_target_of(
category, type) or '')
col += 1
row += 1
if len(contrib.contributors) < 1:
continue
# r13
ws.set_cell_style(row, 3, Style(font=Font(bold=True)))
ws.set_cell_value(row, 3, 'Level 2 sub-contributors:')
row += 1
for subcontrib in contrib.contributors:
# r14
ws.set_cell_style(
row, 3,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 3, subcontrib.project.title)
ws.set_cell_style(
row, 4,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(
row, 4, getattr(subcontrib.country, 'name',
' '))
ws.set_cell_value(row, 5, subcontrib.actual_value)
ws.set_cell_value(row, 6, subcontrib.target_value)
ws.set_cell_style(
row, 7,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(
row, 7, '{}%'.format(
calculate_percentage(
subcontrib.actual_value,
period.actual_value)))
if disaggregation_types_length:
col = 8
for category, types in disaggregations.items():
for type in [t for t in types.keys()]:
ws.set_cell_value(
row, col,
subcontrib.get_disaggregation_of(
category, type) or '')
col += 1
ws.set_cell_value(
row, col,
subcontrib.
get_disaggregation_target_of(
category, type) or '')
col += 1
row += 1
# output
filename = '{}-{}-program-overview-report.xlsx'.format(
datetime.today().strftime('%Y%b%d'), program.id)
return utils.make_excel_response(wb, filename)
def render_report(request, project_id):
queryset = Project.objects.prefetch_related(
'results', 'results__indicators', 'results__indicators__periods')
project = get_object_or_404(queryset, pk=project_id)
in_eutf_hierarchy = project.in_eutf_hierarchy()
wb = Workbook()
ws = wb.new_sheet('ResultsTable')
ws.set_col_style(1, Style(size=75))
ws.set_col_style(2, Style(size=75))
ws.set_col_style(3, Style(size=41))
ws.set_col_style(4, Style(size=18.5))
ws.set_col_style(5, Style(size=34))
ws.set_col_style(6, Style(size=37.5))
ws.set_col_style(7, Style(size=47.5))
ws.set_col_style(8, Style(size=20))
ws.set_col_style(9, Style(size=20))
ws.set_col_style(10, Style(size=34))
ws.set_col_style(11, Style(size=20))
ws.set_col_style(12, Style(size=20))
ws.set_col_style(13, Style(size=20))
ws.set_col_style(14, Style(size=24))
ws.set_col_style(15, Style(size=20.5))
ws.set_col_style(16, Style(size=30))
ws.set_col_style(17, Style(size=22))
ws.set_col_style(18, Style(size=21))
# r1
ws.set_row_style(1, Style(size=36))
ws.set_cell_style(
1, 1,
Style(font=Font(bold=True, size=18, color=Color(255, 255, 255)),
fill=Fill(background=Color(32, 56, 100)),
alignment=Alignment(horizontal='center')))
ws.set_cell_value(1, 1,
'Project Results and Indicators simple table report')
# r2
ws.set_row_style(2, Style(size=36))
for i in range(1, 19):
ws.set_cell_style(
2, i,
Style(font=Font(bold=True, size=14),
fill=Fill(background=Color(214, 234, 248)),
alignment=Alignment(horizontal='center'),
borders=Borders(top=Border(color=Color(0, 0, 0)),
bottom=Border(color=Color(0, 0, 0)))))
ws.set_cell_value(2, 1, 'Project name')
ws.set_cell_value(2, 2, 'Project subtitle')
ws.set_cell_value(2, 3, 'Result title')
ws.set_cell_value(2, 4, 'Result type')
ws.set_cell_value(2, 5, 'Result description')
ws.set_cell_value(2, 6, 'Indicator title')
ws.set_cell_value(2, 7, 'Indicator description')
ws.set_cell_value(2, 8, 'Baseline year')
ws.set_cell_value(2, 9, 'Baseline value')
ws.set_cell_value(2, 10, 'Baseline comment')
ws.set_cell_value(2, 11, 'Period start')
ws.set_cell_value(2, 12, 'Period end')
ws.set_cell_value(2, 13, 'Target value')
ws.set_cell_value(2, 14, 'Target comment')
ws.set_cell_value(2, 15, 'Actual value')
ws.set_cell_value(2, 16, 'Actual comment')
ws.set_cell_value(2, 17, 'Type')
ws.set_cell_value(2, 18, 'Aggregation status')
# r3
row = 3
ws.set_cell_value(row, 1, project.title)
ws.set_cell_value(row, 2, project.subtitle)
prev_type = ''
curr_type = ''
prev_agg_status = ''
curr_agg_status = ''
prev_indicator_type = ''
curr_indicator_type = ''
for result in project.results.exclude(type__exact='').all():
ws.set_cell_value(row, 3, result.title)
curr_type = result.iati_type().name
if curr_type != prev_type:
ws.set_cell_value(row, 4, curr_type)
prev_type = curr_type
ws.set_cell_style(row, 5, Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 5, result.description)
curr_agg_status = 'Yes' if result.aggregation_status else 'No'
if curr_agg_status != prev_agg_status:
ws.set_cell_value(row, 18, curr_agg_status)
prev_agg_status = curr_agg_status
for indicator in result.indicators.all():
ws.set_cell_style(row, 6,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 6, indicator.title)
ws.set_cell_style(row, 7,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 7, indicator.description)
ws.set_cell_value(row, 8, indicator.baseline_year)
ws.set_cell_value(row, 9, indicator.baseline_value)
ws.set_cell_style(row, 10,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 10, indicator.baseline_comment)
curr_indicator_type = 'Qualitative' if indicator.type == '2' else 'Quantitative'
if curr_indicator_type != prev_indicator_type:
ws.set_cell_value(row, 17, curr_indicator_type)
prev_indicator_type = curr_indicator_type
for period in indicator.periods.all():
ws.set_cell_value(
row, 11, utils.get_period_start(period, in_eutf_hierarchy))
ws.set_cell_value(
row, 12, utils.get_period_end(period, in_eutf_hierarchy))
ws.set_cell_value(row, 13, period.target_value)
ws.set_cell_style(row, 14,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 14, period.target_comment)
ws.set_cell_value(row, 15, ensure_decimal(period.actual_value))
ws.set_cell_style(row, 16,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 16, period.actual_comment)
ws.set_row_style(row, Style(size=68))
row += 1
filename = '{}-{}-eutf-project-results-indicators-report.xlsx'.format(
datetime.today().strftime('%Y%b%d'), project.id)
return utils.make_excel_response(wb, filename)
def _to_excel_color(self, color):
return Color((color >> 16) & 0xFF, (color >> 8) & 0xFF, color & 0xFF)
def render_report(request, project_id):
project = get_object_or_404(Project, pk=project_id)
start_date = utils.parse_date(
request.GET.get('start_date', '').strip(), datetime(1900, 1, 1))
end_date = utils.parse_date(
request.GET.get('end_date', '').strip(),
datetime.today() + relativedelta(years=10))
project_view = build_view_object(project, start_date, end_date)
in_eutf_hierarchy = project_view.in_eutf_hierarchy
use_indicator_target = project_view.use_indicator_target
has_disaggregation = IndicatorPeriodDisaggregation.objects\
.filter(period__indicator__result__project=project).count() > 0
max_column = 14 if has_disaggregation else 12
results_by_types = {}
for result in project_view.results:
type = result.iati_type_name
if not type:
continue
if type not in results_by_types:
results_by_types[type] = []
results_by_types[type].append(result)
wb = Workbook()
for type, results in results_by_types.items():
ws = wb.new_sheet(type)
ws.set_col_style(1, Style(size=55))
ws.set_col_style(2, Style(size=60))
ws.set_col_style(3, Style(size=20))
ws.set_col_style(4, Style(size=20))
ws.set_col_style(5, Style(size=35))
ws.set_col_style(6, Style(size=20))
ws.set_col_style(7, Style(size=20))
ws.set_col_style(8, Style(size=20))
ws.set_col_style(9, Style(size=20))
ws.set_col_style(10, Style(size=25))
ws.set_col_style(11, Style(size=20))
ws.set_col_style(12, Style(size=30))
if has_disaggregation:
ws.set_col_style(13, Style(size=30))
ws.set_col_style(14, Style(size=30))
ws.set_col_style(max_column, Style(size=25))
# r1
ws.set_row_style(1, Style(size=41))
ws.set_cell_style(1, 1, Style(font=Font(bold=True, size=24)))
ws.set_cell_value(
1, 1, 'Project Results and Indicators simple table report')
ws.range('A1', 'B1').merge()
# r2
ws.set_row_style(2, Style(size=36))
ws.set_cell_style(2, 1, Style(font=Font(bold=True, size=12)))
ws.set_cell_value(2, 1, 'Project title')
ws.set_cell_style(2, 2, Style(font=Font(size=12)))
ws.set_cell_value(2, 2, project.title)
# r3
ws.set_row_style(3, Style(size=36))
ws.set_cell_style(3, 1, Style(font=Font(bold=True, size=12)))
ws.set_cell_value(3, 1, 'Result type')
ws.set_cell_style(3, 2, Style(font=Font(size=12)))
ws.set_cell_value(3, 2, type)
# r4
ws.set_row_style(4, Style(size=36))
ws.set_cell_value(4, 1, '')
row = 5
for result in results:
# r5
ws.set_row_style(row, Style(size=36))
result_header1_style = Style(
font=Font(bold=True, size=12, color=Color(255, 255, 255)),
fill=Fill(background=Color(89, 89, 89)))
for i in range(1, max_column + 1):
ws.set_cell_style(row, i, result_header1_style)
ws.set_cell_value(row, 1, 'Result title:')
ws.set_cell_value(row, 3, 'Result description:')
row += 1
# r6
ws.set_row_style(row, Style(size=42))
result_header2_style = Style(
font=Font(size=12, color=Color(255, 255, 255)),
alignment=Alignment(wrap_text=True),
fill=Fill(background=Color(89, 89, 89)))
ws.range('A' + str(row), 'B' + str(row)).merge()
ws.set_cell_style(row, 1, result_header2_style)
ws.set_cell_value(row, 1, result.title)
ws.range('C' + str(row),
('N' if has_disaggregation else 'L') + str(row)).merge()
ws.set_cell_style(row, 3, result_header2_style)
ws.set_cell_value(row, 3, result.description)
row += 1
# r7
ws.set_row_style(row, Style(size=36))
row7_style = Style(font=Font(bold=True, size=12),
fill=Fill(background=Color(211, 211, 211)))
for i in range(1, max_column + 1):
ws.set_cell_style(row, i, row7_style)
ws.set_cell_value(row, 1, 'Indicator title')
ws.set_cell_value(row, 2, 'Indicator description')
ws.set_cell_value(row, 3, 'Baseline year')
ws.set_cell_value(row, 4, 'Baseline value')
ws.set_cell_value(row, 5, 'Baseline comment')
col = 5
if use_indicator_target:
col += 1
ws.set_cell_value(row, col, 'Target')
col += 1
ws.set_cell_value(row, col, 'Target comment')
col += 1
ws.set_cell_value(row, col, 'Period start')
col += 1
ws.set_cell_value(row, col, 'Period end')
if not use_indicator_target:
col += 1
ws.set_cell_value(row, col, 'Target value')
col += 1
ws.set_cell_value(row, col, 'Target comment')
ws.set_cell_value(row, 10, 'Actual value')
ws.set_cell_value(row, 11, 'Actual comment')
if has_disaggregation:
ws.set_cell_value(row, 12, 'Disaggregation label')
ws.set_cell_value(row, 13, 'Disaggregation value')
ws.set_cell_value(row, max_column, 'Aggregation status')
row += 1
ws.set_cell_value(row, max_column,
'Yes' if result.aggregation_status else 'No')
for indicator in result.indicators:
# r8
ws.set_cell_style(row, 1,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 1, indicator.title)
ws.set_cell_style(row, 2,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 2, indicator.description)
ws.set_cell_style(
row, 3, Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, 3, indicator.baseline_year)
ws.set_cell_style(
row, 4, Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, 4, indicator.baseline_value)
ws.set_cell_style(row, 5,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 5, indicator.baseline_comment)
col = 5
if use_indicator_target:
col += 1
ws.set_cell_style(
row, col,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, col, indicator.target_value)
col += 1
ws.set_cell_style(
row, col, Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, col, indicator.target_comment)
for period in indicator.periods:
period_start = utils.get_period_start(
period, in_eutf_hierarchy)
period_end = utils.get_period_end(period,
in_eutf_hierarchy)
inner_col = col
inner_col += 1
ws.set_cell_value(
row, inner_col,
period_start.strftime('%Y-%m-%d')
if period_start else '')
inner_col += 1
ws.set_cell_value(
row, inner_col,
period_end.strftime('%Y-%m-%d') if period_end else '')
if not use_indicator_target:
inner_col += 1
ws.set_cell_style(
row, inner_col,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, inner_col, period.target_value)
inner_col += 1
ws.set_cell_style(
row, inner_col,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, inner_col,
period.target_comment)
ws.set_cell_style(
row, 10,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, 10, period.actual_value)
ws.set_cell_style(
row, 11, Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 11, period.actual_comment)
disaggregations = period.disaggregations.order_by(
'dimension_value__name__id')
if has_disaggregation and disaggregations.count():
category = None
last_category = None
for disaggregation in disaggregations.all():
if disaggregation.value is None:
continue
category = disaggregation.dimension_value.name.name
if category != last_category:
ws.set_cell_style(
row, 12,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(
row, 12,
disaggregation.dimension_value.name.name)
last_category = category
ws.set_cell_style(
row, 13,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(
row, 13, disaggregation.dimension_value.value +
': ' + str(disaggregation.value))
row += 1
else:
row += 1
filename = '{}-{}-results-indicators-report.xlsx'.format(
datetime.today().strftime('%Y%b%d'), project.id)
return utils.make_excel_response(wb, filename)
def render_report(request, program_id):
queryset = ProjectHierarchy.objects.prefetch_related('organisation')
project_hierarchy = get_object_or_404(queryset, root_project=program_id)
organisation = project_hierarchy.organisation
projects = build_view_object(organisation)
wb = Workbook()
ws = wb.new_sheet('ProjectList')
ws.set_col_style(1, Style(size=81.5))
ws.set_col_style(2, Style(size=33.5))
ws.set_col_style(3, Style(size=7.67))
ws.set_col_style(4, Style(size=21.17))
ws.set_col_style(5, Style(size=20.67))
ws.set_col_style(6, Style(size=19))
ws.set_col_style(7, Style(size=18.17))
ws.set_col_style(8, Style(size=33.5))
ws.set_col_style(9, Style(size=21))
ws.set_col_style(10, Style(size=14.83))
ws.set_col_style(11, Style(size=33.5))
ws.set_col_style(12, Style(size=23.17))
ws.set_col_style(13, Style(size=10.5))
ws.set_col_style(14, Style(size=14))
ws.set_col_style(15, Style(size=16.67))
ws.set_col_style(16, Style(size=19.5))
ws.set_col_style(17, Style(size=34.33))
ws.set_col_style(18, Style(size=15.67))
ws.set_col_style(19, Style(size=15.67))
ws.set_col_style(20, Style(size=22.33))
ws.set_col_style(21, Style(size=25))
ws.set_col_style(22, Style(size=17.5))
ws.set_col_style(23, Style(size=20.83))
ws.set_col_style(24, Style(size=9.67))
ws.set_col_style(25, Style(size=12.5))
ws.set_col_style(26, Style(size=26.67))
ws.set_col_style(27, Style(size=16.33))
ws.set_col_style(28, Style(size=16.33))
ws.set_col_style(29, Style(size=16.33))
ws.set_col_style(30, Style(size=16.33))
# r1
ws.set_cell_style(
1, 1,
Style(font=Font(bold=True, size=18, color=Color(255, 255, 255)),
fill=Fill(background=Color(32, 56, 100))))
ws.set_cell_value(
1, 1, 'Organisation Results and Indicators simple table report')
# r3
for col in range(1, 31):
ws.set_cell_style(
3, col,
Style(font=Font(bold=True, size=14),
fill=Fill(background=Color(255, 192, 0)),
alignment=Alignment(horizontal='center'),
borders=Borders(bottom=Border(color=Color(0, 0, 0)))))
ws.set_cell_value(3, 1, 'Project name')
ws.set_cell_value(3, 2, 'Subtitle')
ws.set_cell_value(3, 3, 'IATI id')
ws.set_cell_value(3, 4, 'Date start planned')
ws.set_cell_value(3, 5, 'Date end planned')
ws.set_cell_value(3, 6, 'Date start actual')
ws.set_cell_value(3, 7, 'Date end actual')
ws.set_cell_value(3, 8, 'Result title')
ws.set_cell_value(3, 9, 'Result description')
ws.set_cell_value(3, 10, 'Aggregation')
ws.set_cell_value(3, 11, 'Indicator title')
ws.set_cell_value(3, 12, 'Indicator description')
ws.set_cell_value(3, 13, 'Measure')
ws.set_cell_value(3, 14, 'Ascending')
ws.set_cell_value(3, 15, 'Baseline year')
ws.set_cell_value(3, 16, 'Baseline value')
ws.set_cell_value(3, 17, 'Baseline comment')
ws.set_cell_value(3, 18, 'Period start')
ws.set_cell_value(3, 19, 'Period end')
ws.set_cell_value(3, 20, 'Target value')
ws.set_cell_value(3, 21, 'Target comment')
ws.set_cell_value(3, 22, 'Actual value')
ws.set_cell_value(3, 23, 'Actual comment')
ws.set_cell_value(3, 24, 'Country')
ws.set_cell_value(3, 25, 'Type')
ws.set_cell_value(3, 26, 'Related partners')
ws.set_cell_value(3, 27, 'Project id')
ws.set_cell_value(3, 28, 'Result id')
ws.set_cell_value(3, 29, 'Indicator id')
ws.set_cell_value(3, 30, 'Period id')
wrap_text = [2, 8, 9, 11, 12, 17, 21, 23]
row = 4
for project in projects:
highlight = True
for result in project.results:
for indicator in result.indicators:
for period in indicator.periods:
for col in range(1, 31):
ws.set_cell_style(
row, col,
Style(alignment=Alignment(
wrap_text=True) if col in wrap_text else None,
fill=Fill(background=Color(223, 231, 244))
if highlight else None))
# The empty strings are sort of a hack because the style formatting
# are not applied on a cell with empty content.
ws.set_cell_value(row, 1, project.title or ' ')
ws.set_cell_value(row, 2, project.subtitle or ' ')
ws.set_cell_value(row, 3, project.iati_activity_id or ' ')
ws.set_cell_value(row, 4, project.date_start_planned
or ' ')
ws.set_cell_value(row, 5, project.date_end_planned or ' ')
ws.set_cell_value(row, 6, project.date_start_actual or ' ')
ws.set_cell_value(row, 7, project.date_end_planned or ' ')
ws.set_cell_value(row, 8, result.title or ' ')
ws.set_cell_value(row, 9, result.description or ' ')
ws.set_cell_value(
row, 10, 'Yes' if result.aggregation_status else 'No')
ws.set_cell_value(row, 11, indicator.title or ' ')
ws.set_cell_value(row, 12, indicator.description or ' ')
ws.set_cell_value(
row, 13, 'Percentage'
if indicator.measure == PERCENTAGE_MEASURE else 'Unit')
ws.set_cell_value(row, 14,
'Yes' if indicator.ascending else 'No')
ws.set_cell_value(row, 15, indicator.baseline_year or ' ')
ws.set_cell_value(row, 16, indicator.baseline_value or ' ')
ws.set_cell_value(row, 17, indicator.baseline_comment
or ' ')
ws.set_cell_value(
row, 18,
utils.get_period_start(
period, project.in_eutf_hierarchy) or ' ')
ws.set_cell_value(
row, 19,
utils.get_period_end(period, project.in_eutf_hierarchy)
or ' ')
ws.set_cell_value(row, 20, period.target_value or ' ')
ws.set_cell_value(row, 21, period.target_comment or ' ')
ws.set_cell_value(row, 22, period.actual_value or ' ')
ws.set_cell_value(row, 23, period.actual_comment or ' ')
ws.set_cell_value(row, 24, project.country_codes or ' ')
ws.set_cell_value(row, 25, result.iati_type_name or ' ')
ws.set_cell_value(row, 26, project.partner_names or ' ')
ws.set_cell_value(row, 27, project.id)
ws.set_cell_value(row, 28, result.id)
ws.set_cell_value(row, 29, indicator.id)
ws.set_cell_value(row, 30, period.id)
row += 1
highlight = False
filename = '{}-{}-eutf-results-and-indicators-simple-table.xlsx'.format(
datetime.now().strftime('%Y%m%d'), organisation.id)
return utils.make_excel_response(wb, filename)
def _render_excel(reader):
wb = Workbook()
# styles
section_header_style = Style(font=Font(bold=True, size=14))
table_header_style = Style(font=Font(bold=True))
table_footer_first_style = Style(
alignment=Alignment(horizontal='left'), borders=Borders(top=Border(color=Color(0, 0, 0))))
table_footer_style = Style(
alignment=Alignment(horizontal='right'), borders=Borders(top=Border(color=Color(0, 0, 0))))
# sheet0 ==================================================================
ws = wb.new_sheet('Sheet0')
ws.set_col_style(1, Style(size=28))
ws.set_col_style(2, Style(size=35))
ws.set_col_style(3, Style(size=16))
ws.set_col_style(4, Style(size=20))
ws.set_col_style(5, Style(size=33))
ws.set_col_style(6, Style(size=39))
# r1
ws.set_cell_style(1, 1, Style(font=Font(size=18)))
ws.set_cell_value(1, 1, 'Data quality report for ' + reader.organisation.name)
# r2
ws.set_cell_value(2, 1, 'Sorted by country and id, only active and completed projects')
# r3
ws.set_cell_style(3, 1, section_header_style)
ws.set_cell_value(3, 1, 'Project totals')
# r4
ws.set_cell_value(4, 1, 'Planned end date overdue')
ws.set_cell_value(4, 2, reader.planned_and_date_overdue.count())
# r5
ws.set_cell_value(5, 1, 'No edits or updates last 3 months')
ws.set_cell_value(5, 2, reader.no_edit_or_updates.count())
# r6
ws.set_cell_value(6, 1, 'Need funding')
ws.set_cell_value(6, 2, reader.need_funding.count())
# r7
ws.set_cell_value(7, 1, 'Without photo')
ws.set_cell_value(7, 2, reader.without_photo.count())
# r8
ws.set_cell_style(8, 1, section_header_style)
ws.set_cell_value(8, 1, 'Projects with planned end date overdue')
# r9
ws.set_cell_value(9, 1, 'Sorted by country and id')
# r10
for i in range(1, 7):
ws.set_cell_style(10, i, table_header_style)
ws.set_cell_value(10, 1, 'Id')
ws.set_cell_value(10, 2, 'Title')
ws.set_cell_value(10, 3, 'Planned start date')
ws.set_cell_value(10, 4, 'Planned end date')
ws.set_cell_value(10, 5, 'Country')
ws.set_cell_value(10, 6, 'Project URL')
# r11
row = 11
for project, country in reader.planned_and_date_overdue_list:
ws.set_cell_style(row, 1, Style(alignment=Alignment(horizontal='left')))
ws.set_cell_value(row, 1, project.id)
ws.set_cell_value(row, 2, project.title)
ws.set_cell_style(row, 3, Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, 3, project.date_start_planned.strftime('%-d-%b-%Y') if project.date_start_planned else '')
ws.set_cell_style(row, 4, Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, 4, project.date_end_planned.strftime('%-d-%b-%Y') if project.date_end_planned else '')
ws.set_cell_value(row, 5, country)
ws.set_cell_value(row, 6, 'https://{}{}'.format(settings.RSR_DOMAIN, project.get_absolute_url()))
row += 1
# r12
ws.set_cell_style(row, 1, table_footer_first_style)
for i in range(2, 7):
ws.set_cell_style(row, i, table_footer_style)
ws.set_cell_value(row, 1, len(reader.planned_and_date_overdue_list))
# sheet1 ==================================================================
ws = wb.new_sheet('Sheet1')
ws.set_col_style(1, Style(size=17))
ws.set_col_style(2, Style(size=27))
ws.set_col_style(3, Style(size=20))
ws.set_col_style(4, Style(size=16))
ws.set_col_style(5, Style(size=22))
ws.set_col_style(6, Style(size=11))
ws.set_col_style(7, Style(size=38))
# r1
ws.set_cell_style(1, 1, section_header_style)
ws.set_cell_value(1, 1, 'Projects with no edits or updates last 3 months')
# r2
ws.set_cell_value(2, 1, 'Sorted by country and id')
# r4
for i in range(1, 8):
ws.set_cell_style(4, i, table_header_style)
ws.set_cell_value(4, 1, 'Id')
ws.set_cell_value(4, 2, 'Title')
ws.set_cell_value(4, 3, 'Last modified')
ws.set_cell_value(4, 4, 'Planned start date')
ws.set_cell_value(4, 5, 'Planned end date')
ws.set_cell_value(4, 6, 'Country')
ws.set_cell_value(4, 7, 'Project URL')
# r5
row = 5
for project, country in reader.no_edit_or_updates_list:
ws.set_cell_style(row, 1, Style(alignment=Alignment(horizontal='left')))
ws.set_cell_value(row, 1, project.id)
ws.set_cell_value(row, 2, project.title)
ws.set_cell_style(row, 3, Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, 3, project.last_modified_at.strftime('%-d-%b-%Y') if project.last_modified_at else '')
ws.set_cell_style(row, 4, Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, 4, project.date_start_planned.strftime('%-d-%b-%Y') if project.date_start_planned else '')
ws.set_cell_style(row, 5, Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, 5, project.date_end_planned.strftime('%-d-%b-%Y') if project.date_end_planned else '')
ws.set_cell_value(row, 6, country)
ws.set_cell_value(row, 7, 'https://{}{}'.format(settings.RSR_DOMAIN, project.get_absolute_url()))
row += 1
# r6
ws.set_cell_style(row, 1, table_footer_first_style)
for i in range(2, 8):
ws.set_cell_style(row, i, table_footer_style)
ws.set_cell_value(row, 1, len(reader.no_edit_or_updates_list))
# sheet2 ==================================================================
ws = wb.new_sheet('Sheet2')
ws.set_col_style(1, Style(size=17))
ws.set_col_style(2, Style(size=27))
ws.set_col_style(3, Style(size=19))
ws.set_col_style(4, Style(size=19))
ws.set_col_style(5, Style(size=33))
ws.set_col_style(6, Style(size=37))
ws.set_col_style(7, Style(size=12))
ws.set_col_style(8, Style(size=38))
# r1
ws.set_cell_style(1, 1, section_header_style)
ws.set_cell_value(1, 1, 'Projects that need funding')
# r2
for i in range(1, 9):
ws.set_cell_style(2, i, table_header_style)
ws.range('C2', 'D2').merge()
ws.set_cell_value(2, 1, 'Id')
ws.set_cell_value(2, 2, 'Title')
ws.set_cell_value(2, 3, 'Budget')
ws.set_cell_value(2, 5, 'Funds')
ws.set_cell_value(2, 6, 'Funds needed')
ws.set_cell_value(2, 7, 'Country')
ws.set_cell_value(2, 8, 'Project URL')
# r3
row = 3
for project, country in reader.need_funding_list:
ws.set_cell_style(row, 1, Style(alignment=Alignment(horizontal='left')))
ws.set_cell_value(row, 1, project.id)
ws.set_cell_value(row, 2, project.title)
ws.set_cell_style(row, 3, Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, 3, project.currency)
ws.set_cell_style(row, 4, Style(format=Format("#,#0.00")))
ws.set_cell_value(row, 4, project.budget)
ws.set_cell_style(row, 5, Style(format=Format("#,#0.00")))
ws.set_cell_value(row, 5, project.funds)
ws.set_cell_style(row, 6, Style(format=Format("#,#0.00")))
ws.set_cell_value(row, 6, project.funds_needed)
ws.set_cell_value(row, 7, country)
ws.set_cell_value(row, 8, 'https://{}{}'.format(settings.RSR_DOMAIN, project.get_absolute_url()))
row += 1
# r4
ws.set_cell_style(row, 1, table_footer_first_style)
for i in range(2, 9):
ws.set_cell_style(row, i, table_footer_style)
ws.set_cell_value(row, 1, len(reader.need_funding_list))
row += 1
# r5
ws.set_cell_style(row, 1, section_header_style)
ws.set_cell_value(row, 1, 'Projects without photos')
row += 1
# r6
for i in range(1, 7):
ws.set_cell_style(row, i, table_header_style)
ws.set_cell_value(row, 1, 'Id')
ws.set_cell_value(row, 2, 'Title')
ws.set_cell_value(row, 3, 'Planned start date')
ws.set_cell_value(row, 4, 'Planned end date')
ws.set_cell_value(row, 5, 'Country')
ws.set_cell_value(row, 6, 'Project URL')
# r7
row += 1
for project, country in reader.without_photo_list:
ws.set_cell_style(row, 1, Style(alignment=Alignment(horizontal='left')))
ws.set_cell_value(row, 1, project.id)
ws.set_cell_value(row, 2, project.title)
ws.set_cell_style(row, 3, Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, 3, project.date_start_planned.strftime('%-d-%b-%Y') if project.date_start_planned else '')
ws.set_cell_style(row, 4, Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, 4, project.date_end_planned.strftime('%-d-%b-%Y') if project.date_end_planned else '')
ws.set_cell_value(row, 5, country)
ws.set_cell_value(row, 6, 'https://{}{}'.format(settings.RSR_DOMAIN, project.get_absolute_url()))
row += 1
# r8
ws.set_cell_style(row, 1, table_footer_first_style)
for i in range(2, 7):
ws.set_cell_style(row, i, table_footer_style)
ws.set_cell_value(row, 1, len(reader.without_photo_list))
filename = '{}-{}-organisation-data-quality.xlsx'.format(
reader.date.strftime('%Y%m%d'), reader.organisation.id)
return utils.make_excel_response(wb, filename)
def generate_workbok(program, start_date=None, end_date=None):
results = get_results_framework(program, start_date, end_date)
results_by_types = group_results_by_types(results)
if not results_by_types:
results_by_types = {'Sheet1': []}
aggregate_targets = is_aggregating_targets(program)
use_indicator_target = utils.is_using_indicator_target(program)
disaggregations = get_disaggregations(program)
disaggregations_column_start = 16 if aggregate_targets else 15
disaggregation_types_length = 0
for types in disaggregations.values():
disaggregation_types_length += len(types.keys())
disaggregations_last_colnum = disaggregations_column_start - 1 + (
disaggregation_types_length * 2)
wb = Workbook()
header_style = Style(font=Font(bold=True, size=12),
fill=Fill(background=Color(211, 211, 211)))
subheader_style = Style(font=Font(size=12),
fill=Fill(background=Color(211, 211, 211)))
header_disaggregation_style = Style(
font=Font(bold=True, size=12),
fill=Fill(background=Color(211, 211, 211)),
alignment=Alignment(wrap_text=True, horizontal='center'),
)
for type, results in results_by_types.items():
ws = wb.new_sheet(type)
ws.set_col_style(1, Style(size=50))
ws.set_col_style(2, Style(size=50))
ws.set_col_style(3, Style(size=50))
ws.set_col_style(4, Style(size=25))
ws.set_col_style(5, Style(size=20))
ws.set_col_style(6, Style(size=60))
ws.set_col_style(7, Style(size=25))
ws.set_col_style(8, Style(size=25))
ws.set_col_style(9, Style(size=25))
ws.set_col_style(10, Style(size=25))
if aggregate_targets:
ws.set_col_style(AGGREGATED_TARGET_VALUE_COLUMN, Style(size=25))
col = get_dynamic_column_start(aggregate_targets)
ws.set_col_style(col, Style(size=25))
col += 1
ws.set_col_style(col, Style(size=25))
col += 1
ws.set_col_style(col, Style(size=25))
col += 1
ws.set_col_style(col, Style(size=25))
# r1
ws.set_row_style(1, Style(size=36))
for i in range(1, disaggregations_column_start):
ws.set_cell_style(1, i, header_style)
ws.set_cell_value(1, 1, 'Result title')
ws.set_cell_value(1, 2, 'Result description')
ws.set_cell_value(1, 3, 'Indicator title')
ws.set_cell_value(1, 4, 'Reporting period')
ws.set_cell_value(1, 5, 'Hierarchy level')
ws.set_cell_value(1, 6, 'Contributor title')
ws.set_cell_value(1, 7, 'Contributor subtitle')
ws.set_cell_value(1, 8, 'Countries')
ws.set_cell_value(1, 9, 'Sector')
ws.set_cell_value(1, 10, 'Baseline value')
if aggregate_targets:
ws.set_cell_value(1, AGGREGATED_TARGET_VALUE_COLUMN,
'Aggregated target value')
col = get_dynamic_column_start(aggregate_targets)
ws.set_cell_value(1, col, 'Target value')
col += 1
ws.set_cell_value(1, col, 'Aggregated actual value')
col += 1
ws.set_cell_value(1, col, 'Actual value')
col += 1
ws.set_cell_value(1, col, '% of contribution')
if disaggregation_types_length:
col = disaggregations_column_start
for category, types in disaggregations.items():
ws.set_cell_style(1, col, header_disaggregation_style)
ws.set_cell_value(1, col, category.upper())
type_length = len(types.keys()) * 2
next_col = col + type_length
ws.range(
utils.xl_column_name(col) + str(1),
utils.xl_column_name(next_col - 1) + str(1)).merge()
col = next_col
# r2
for i in range(1, disaggregations_column_start):
ws.set_cell_style(2, i, header_style)
if disaggregation_types_length:
col = disaggregations_column_start
types = [t for ts in disaggregations.values() for t in ts.keys()]
for type in types:
ws.set_cell_style(2, col, header_disaggregation_style)
ws.set_cell_value(2, col, type)
next_col = col + 2
ws.range(
utils.xl_column_name(col) + str(2),
utils.xl_column_name(next_col - 1) + str(2)).merge()
col = next_col
# r3
for i in range(1, disaggregations_column_start):
ws.set_cell_style(3, i, header_style)
if disaggregation_types_length:
col = disaggregations_column_start
while col <= disaggregations_last_colnum:
for label in ['value', 'target']:
ws.set_cell_style(3, col, subheader_style)
ws.set_cell_value(3, col, label)
col += 1
# r4
row = 4
for result in results:
for indicator in result.indicators:
for period in indicator.periods:
row = render_period(ws, row, result, indicator, period,
aggregate_targets,
use_indicator_target, disaggregations)
for contributor in period.contributors:
row = render_contributor_hierarchy(
ws, row, result, indicator, period, contributor,
aggregate_targets, use_indicator_target,
disaggregations)
return wb
def render_report(request, project_id):
project = get_object_or_404(Project, pk=project_id)
start_date = utils.parse_date(request.GET.get('start_date', '').strip(), datetime(1900, 1, 1))
end_date = utils.parse_date(request.GET.get('end_date', '').strip(), datetime.today() + relativedelta(years=10))
wb = Workbook()
ws = wb.new_sheet('UpdatesTable')
ws.set_col_style(1, Style(size=17))
ws.set_col_style(2, Style(size=50))
ws.set_col_style(3, Style(size=35))
ws.set_col_style(4, Style(size=27))
ws.set_col_style(5, Style(size=19))
ws.set_col_style(6, Style(size=19))
ws.set_col_style(7, Style(size=19))
ws.set_col_style(8, Style(size=19))
ws.set_col_style(9, Style(size=25))
ws.set_col_style(10, Style(size=25))
ws.set_col_style(11, Style(size=25))
ws.set_col_style(12, Style(size=19))
ws.set_col_style(13, Style(size=19))
ws.set_col_style(14, Style(size=48.5))
# r1
ws.set_row_style(1, Style(size=40.5))
ws.range('A1', 'B1').merge()
ws.set_cell_style(1, 1, Style(font=Font(bold=True, size=24)))
ws.set_cell_value(1, 1, 'Project Updates Review')
# r2
ws.set_cell_style(2, 1, Style(font=Font(bold=True, size=13)))
ws.set_cell_value(2, 1, 'Project title')
ws.set_cell_style(2, 2, Style(font=Font(bold=True, size=13)))
ws.set_cell_value(2, 2, project.title)
# r3
ws.set_cell_style(3, 1, Style(font=Font(bold=True, size=13)))
ws.set_cell_value(3, 1, 'Project #')
ws.set_cell_style(3, 2, Style(
font=Font(bold=True, size=13), alignment=Alignment(horizontal='left')))
ws.set_cell_value(3, 2, project.id)
# r5
ws.set_row_style(5, Style(size=36))
for col in range(1, 15):
ws.set_cell_style(5, col, Style(
font=Font(bold=True, size=13),
alignment=Alignment(vertical='center'),
fill=Fill(background=Color(211, 211, 211))
))
ws.set_cell_value(5, 1, 'Update title')
ws.set_cell_value(5, 2, 'Update text')
ws.set_cell_value(5, 3, 'Photo')
ws.set_cell_value(5, 4, 'Photo caption')
ws.set_cell_value(5, 5, 'Photo credit')
ws.set_cell_value(5, 6, 'Video')
ws.set_cell_value(5, 7, 'Video caption')
ws.set_cell_value(5, 8, 'Video credit')
ws.set_cell_value(5, 9, 'Created at')
ws.set_cell_value(5, 10, 'Last modified date')
ws.set_cell_value(5, 11, 'Event date')
ws.set_cell_value(5, 12, 'First name')
ws.set_cell_value(5, 13, 'Last name')
ws.set_cell_value(5, 14, 'URL')
# r6
row = 6
for update in project.project_updates.filter(event_date__gte=start_date, event_date__lte=end_date):
for col in range(1, 9):
ws.set_cell_style(row, col, Style(alignment=Alignment(wrap_text=True, vertical='top')))
for col in range(9, 15):
ws.set_cell_style(row, col, Style(alignment=Alignment(vertical='top')))
ws.set_cell_value(row, 1, update.title)
ws.set_cell_value(row, 2, update.text)
ws.set_cell_value(row, 3, 'https://rsr.akvo.org/media/{}'.format(update.photo) if update.photo else '')
ws.set_cell_value(row, 4, update.photo_caption)
ws.set_cell_value(row, 5, update.photo_credit)
ws.set_cell_value(row, 6, update.video)
ws.set_cell_value(row, 7, update.video_caption)
ws.set_cell_value(row, 8, update.video_credit)
ws.set_cell_value(row, 9, update.created_at)
ws.set_cell_value(row, 10, update.last_modified_at)
ws.set_cell_value(row, 11, update.event_date)
ws.set_cell_value(row, 12, update.user.first_name)
ws.set_cell_value(row, 13, update.user.last_name)
ws.set_cell_value(row, 14, 'https://{}{}'.format(settings.RSR_DOMAIN, update.get_absolute_url()))
row += 1
filename = '{}-{}-updates-table-report.xlsx'.format(
datetime.today().strftime('%Y%b%d'), project.id)
return utils.make_excel_response(wb, filename)