def export_to_xlsx(data, temp_dir):
start_time = datetime.now()
# full_file_path = "{0}{1}temp.xlsx".format(file_path, os.sep)
file_path = "/tmp/temp.xlsx"
try:
wb = Workbook()
ws = wb.new_sheet("Policies", data=data)
ws.set_row_style(1, Style(font=Font(bold=True))) # bold the header row
# save xlsx file and open it as a binary file
wb.save(file_path)
xlsx_file = open(file_path, 'rb')
output = io.BytesIO()
output.write(xlsx_file.read())
# close and delete file
xlsx_file.close()
os.remove(file_path)
except Exception as ex:
m.logger.fatal("\tunable to export to file: {}".format(ex,))
return None
m.logger.info("\tfile export took {}".format(datetime.now() - start_time))
return output
def add_sheet(csv_path, index):
with open(csv_path, 'r') as f:
csv_reader = csv.reader(f)
csv_title = next(csv_reader)[0]
csv_column_labels = next(csv_reader)
images = {}
for row in csv_reader:
if row[0] not in images:
images[row[0]] = {
'annotations': {},
'area': row[5],
}
image = images[row[0]]
if row[1] not in image['annotations']:
image['annotations'][row[1]] = {
'id': row[1],
'shape': row[3],
'points': json.loads(row[4]),
'labels': [],
}
image['annotations'][row[1]]['labels'].append(row[2])
# rows have the content: image_filename, annotation_id, label_name, shape_name, points, image area
celldata = [[csv_title], csv_column_labels]
for filename in sorted(images):
image = images[filename]
for annotation_id, annotation in image['annotations'].items():
points = iter(annotation['points'])
labels = ' ,'.join(annotation['labels'])
celldata.append([
filename, annotation_id, annotation['shape'],
next(points),
next(points), labels, image['area']
])
for point in points:
celldata.append(['', '', '', point, next(points, ''), '', ''])
ws = workbook.new_sheet("sheet {}".format(index), data=celldata)
ws.set_row_style(1, Style(font=Font(bold=True)))
ws.set_row_style(2, Style(font=Font(bold=True)))
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 __save_xls(self):
wb = Workbook()
for model in self.models:
ws = wb.new_sheet(sheet_name=model)
# sets the column name
for j in range(1, len(self.metric_names) + 1):
ws.set_cell_value(1, j + 1, self.metric_names[j - 1])
# ws.set_cell_style(1, j, Style(fill=Fill(background=Color(224, 224, 224, 224))))
ws.set_cell_style(1, j + 1, Style(font=Font(bold=True)))
# sets the cells values
for i in range(1, self.runs + 1):
# sets the first value in col 1 to "runX"
ws.set_cell_value(i + 1, 1, 'run ' + str(i))
for j in range(1, len(self.metric_names) + 1):
try:
ws.set_cell_value(
i + 1, j + 1,
self.metrics[model][self.metric_names[j - 1]][i -
1])
except IndexError:
ws.set_cell_value(i + 1, j + 1, '')
except KeyError:
pass
# after the last run row plus one empty row
offset = self.runs + 3
for i in range(0, len(self.descriptive_stats_names)):
ws.set_cell_value(i + offset, 1,
self.descriptive_stats_names[i])
for j in range(0, len(self.metric_names) - 1):
try:
ws.set_cell_value(
i + offset, j + 2,
self.descriptive_stats[model][self.metric_names[j]]
[self.descriptive_stats_names[i]])
except KeyError:
pass
wb.save(self.outfile)
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, 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)
# coding=utf-8
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, 1)
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, 2, Style(font=Font(underline=True)))
wb._align_styles()
print(wb._items)
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)
import csv
target_file = sys.argv[2]
csvs = sys.argv[3:]
workbook = Workbook()
numSheets = 0
for path in csvs:
f = open(path, 'r')
rows = np.array(list(csv.reader(f)))
f.close()
# volume name is the first row
# column titles are in the second row
if rows.shape[0] == 2:
continue
numSheets += 1
# rows have the content: image_filename, label_name, label_count
# Excel does not permit worksheet names longer than 31 characters
ws = workbook.new_sheet("sheet " + str(numSheets), data=rows)
# bold font for titles
ws.set_row_style(1, Style(font=Font(bold=True)))
ws.set_row_style(2, Style(font=Font(bold=True)))
if not numSheets:
ws = workbook.new_sheet("No labels found", data=[['No labels found']])
workbook.save(target_file)
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, 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 export_master_excel(request, month, year):
# paymentautoaudit is the name of the other container running separately on port 8000
res = requests.get(
f"http://paymentautoaudit:8000/masterdata/{month}/{year}")
data = res.json()
print(res.status_code)
response = HttpResponse(content_type='application/ms-excel')
response[
'Content-Disposition'] = f'attachment; filename="{data["metadata"]["filename"]}"'
wb = Workbook()
ws = wb.new_sheet(data["metadata"]["tab1_name"])
ws[1].value = [['MVR']]
ws[2].value = [[f'01-{month}-{year}']]
ws.range("A1", "Z1").merge()
ws[1][1].style.font.bold = True
ws[1][1].style.font.size = 22
ws[1][1].style.alignment.horizontal = "center"
ws.set_row_style(1, Style(size=30))
ws.range("A2", "Z2").merge()
ws[2][1].style.alignment.horizontal = "center"
ws[2][1].style.font.bold = True
ws.range("A3", "Z3").merge()
ws[3].value = []
ws[4].value = [data["tab1_table1"]["headers"]]
ws.set_row_style(4, Style(size=25, font=Font(bold=True)))
ws.set_col_style(list(range(1, 29)), Style(size=-1))
t1_row_end = 5 + len(data["tab1_table1"]["data"][0].keys()) - 1
ws[5:t1_row_end].value = data["tab1_table1"]["data"][0].values()
ws[t1_row_end + 1].value = [['Total']]
ws.set_row_style(t1_row_end + 1, Style(
size=25,
font=Font(bold=True),
))
ws.range(f"A{t1_row_end+1}", f"I{t1_row_end+1}").merge()
ws[t1_row_end + 1][1].style.font.bold = True
ws[t1_row_end + 1][1].style.alignment.horizontal = "center"
excel_apply_formula(ws, t1_row_end + 1, (10, 11, 12, 18, 19, 20),
("J", "K", "L", "S", "T", "U"), "SUM", 5, t1_row_end)
t2_start = ws.num_rows + 2
ws[t2_start + 1].value = [['ANALYSIS']]
ws.range(f"A{t2_start+1}", f"J{t2_start+1}").merge()
ws[t2_start + 1][1].style.font.bold = True
ws[t2_start + 1][1].style.font.size = 14
ws[t2_start + 1][1].style.alignment.horizontal = "center"
ws[t2_start + 2].value = [data["tab1_table2"]["headers"]]
ws[t2_start + 3].value = []
ws[t2_start + 4].value = [[' ', ' ROOMS ANALYSIS']]
ws[t2_start + 4][2].style.font.bold = True
ws.set_row_style(t2_start + 2, Style(size=35, font=Font(bold=True)))
ws.set_col_style(list(range(1, 27)), Style(size=-1))
ws[t2_start + 7].value = [['', 'RESTAURANT ANALYSIS']]
ws[t2_start + 7][2].style.font.bold = True
ws.set_row_style(t2_start + 10, Style(size=20))
ws[t2_start + 5:t2_start + 6].value = [
data["tab1_table2"]["data"][0]["row1"],
data["tab1_table2"]["data"][0]["row2"]
]
ws[t2_start + 8:t2_start + 10].value = [
data["tab1_table2"]["data"][0]["row3"],
data["tab1_table2"]["data"][0]["row4"],
data["tab1_table2"]["data"][0]["row5"]
]
# New worksheet
ws1 = wb.new_sheet(data["metadata"]["tab2_name"])
ws1.range("B2", "E2").value = [['CUSTOMER DETAILS']]
ws1[2][2].style.font.bold = True
ws1[2][2].style.alignment.horizontal = "center"
ws1.range("B2", "E2").merge()
ws1.range("F2", "AE2").value = [['PRE CHECK IN']]
# ws1.set_cell_style(2, 3, Style(font=Font(bold=True)))
ws1[2][6].style.font.bold = True
ws1[2][6].style.alignment.horizontal = "center"
ws1.range("F2", "AE2").merge()
ws1.range("AH2", "AN2").value = [['POST CHECK IN']]
ws1[2][34].style.font.bold = True
ws1[2][34].style.alignment.horizontal = "center"
ws1.range("AH2", "AN2").merge()
ws1[2][42].value = 'Cash'
ws1[3][1].value = ''
ws1.range("B3", "AQ3").value = [data["tab2_table1"]["headers"]]
ws1[3][2].style.font.bold = True
ws1.set_row_style(3, Style(size=30, font=Font(bold=True)))
ws1.set_col_style(list(range(1, 43)), Style(size=-1))
tt1_row_end = 4 + len(data["tab2_table1"]["data"][0].keys()) - 1
ws1.range(f"B{tt1_row_end+1}", f"E{tt1_row_end+1}").value = [['Total']]
ws1[tt1_row_end + 1][2].style.font.bold = True
ws1[tt1_row_end + 1][2].style.alignment.horizontal = "center"
ws1.range(f"B{tt1_row_end+1}", f"E{tt1_row_end+1}").merge()
excel_apply_formula(
ws1,
tt1_row_end + 1,
[10, 11, 12, 18, 19, 21, 22, 23, 25, 26, 27, 38, 39, 40, 42, 43], [
"J", "K", "L", "R", "S", "U", "V", "W", "Y", "Z", "AA", "AL", "AM",
"AN", "AP", "AQ"
],
"SUM",
s_row=4,
e_row=tt1_row_end)
formula_string = "=N{0}+U{0}+Y{0}+AC{0}+AG{0}+AH{0}+AL{0}+AP{0}"
for i in range(4, tt1_row_end):
ws1.set_cell_value(i, 43, formula_string.format(i))
ws1.set_row_style(tt1_row_end + 1, Style(font=Font(bold=True)))
ws1[4:tt1_row_end].value = data["tab2_table1"]["data"][0].values()
tt2_start = ws1.num_rows + 3
ws1.range(f"B{tt2_start+1}",
f"E{tt2_start+1}").value = [data["tab2_table2"]["headers"]]
ws1.set_col_style(list(range(1, 4)), Style(size=-1))
ws1.set_row_style(tt2_start + 1, Style(size=30, font=Font(bold=True)))
ws1[tt2_start + 2:tt2_start +
11].value = data["tab2_table2"]["data"][0].values()
print(data["tab2_table2"]["data"][0].values())
ws1[ws1.num_rows + 1][2].value = 'TOTAL'
ws1[ws1.num_rows][2].style.font.bold = True
excel_apply_formula(ws1, tt2_start + 12, [3, 4, 5], ["C", "D", "E"], "SUM",
tt2_start + 2, ws1.num_rows - 1)
ws1.set_row_style(ws1.num_rows, Style(size=25, font=Font(bold=True)))
wb.save(response)
return response
def __init__(self,
number_format=None,
font=None,
fontsize=None,
bold=None,
italic=None,
underline=None,
strike=None,
color=None,
bgcolor=None,
align=None,
valign=None,
wrap_text=None,
grid_color=None,
colspan=None):
self._styles = locals().copy()
del self._styles['self']
self._excel_style = Style()
# Number Format
if number_format is not None:
self._excel_style.format = Format(number_format)
# Fonts
font_kwargs = {}
if font is not None:
font_kwargs['family'] = font
if fontsize is not None:
font_kwargs['size'] = fontsize
if bold is not None:
font_kwargs['bold'] = bold
if italic is not None:
font_kwargs['italic'] = italic
if underline is not None:
font_kwargs['underline'] = underline
if strike is not None:
font_kwargs['strikethrough'] = strike
if color is not None:
font_kwargs['color'] = self._to_excel_color(color)
if len(font_kwargs):
self._excel_style.font = Font(**font_kwargs)
# Fill
if bgcolor is not None:
self._excel_style.fill = Fill(
background=self._to_excel_color(bgcolor))
# Grid
if grid_color is not None:
if isinstance(grid_color, bool) and not grid_color:
self._excel_style.borders = Borders()
else:
border = Border(color=self._to_excel_color(grid_color))
self._excel_style.borders = Borders(border, border, border,
border)
# Alignment
align_kwargs = {}
if align is not None:
align_kwargs['horizontal'] = align
if valign is not None:
align_kwargs['vertical'] = valign
if wrap_text is not None:
align_kwargs['wrap_text'] = wrap_text
if len(align_kwargs):
self._excel_style.alignment = Alignment(**align_kwargs)
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 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_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)