def _set_data(self):
# assign follower value
num_of_follower = 0 if self.main_table.follower is None else len(
self.main_table.follower)
for i in range(3):
for f in range(num_of_follower):
header_name = attach_column_type[i]
person_name = self.main_table.follower[f].name
self.container[attach_column_type[i]].container[f].set_value(
self._get_ref_value(header_name, person_name))
# set total formula
total_coordinate = coordinate_transfer(self.origin[0] + 1, self.origin[1] + 1 + i) + ':' + \
coordinate_transfer(self.origin[0] + 1 + num_of_follower - 1, self.origin[1] + 1 + i)
total_formula = "=SUM(" + total_coordinate + ")"
self.container[attach_column_type[i]].container[-1].set_formula(
total_formula)
# assign if table has owner
if self.main_table.owner is not None:
self.container[attach_column_type[i]].container.append(
Cube(bg_color=bg_color[4]))
# add total and owner for Target Volume KL
# assign total formula
total_coordinate = coordinate_transfer(self.origin[0] + 1, self.origin[1] + 4) + ':' + \
coordinate_transfer(self.origin[0] + num_of_follower, self.origin[1] + 4)
total_formula = "=SUM(" + total_coordinate + ")"
self.container[attach_column_type[3]].container[-1].set_formula(
total_formula)
# assign if table has owner
if self.main_table.owner is not None:
self.container[attach_column_type[3]].container.append(
Cube(bg_color=bg_color[3]))
def _merge_body_cell(self, end_column, end_row, start_column, start_row,
border, fill, font, al):
self.ws_main.merge_cells(start_row=start_row,
start_column=start_column,
end_row=end_row,
end_column=end_column)
# set merge cells style
coordinate = coordinate_transfer(start_row, start_column) + ':' + coordinate_transfer(end_row, end_column)
style_range(self.ws_main, coordinate, border, fill, font, al)
def _render_header(self):
# render cell with style
scale_x = self.header['scale'][0]
scale_y = self.header['scale'][1]
for i in range(scale_x):
for j in range(scale_y):
x = i + self.origin[0] + 1
# y should add title
y = j + self.origin[1]
current_value = self.header['data'][i][j]
checked_style = self._check_cube_style(i, j)
current_cube = Cube(style=checked_style, value=current_value)
self.core.write_cube_to_book(x, y, current_cube)
# merge cell
if self.header['merge'] is not None:
bx = self.origin[0] + 1
by = self.origin[1]
for m in self.header['merge']:
start_row = m['coordinate'][0] + bx
start_column = m['coordinate'][1] + by
end_row = m['coordinate'][2] + bx
end_column = m['coordinate'][3] + by
self.core.ws.merge_cells(start_row=start_row,
start_column=start_column,
end_row=end_row,
end_column=end_column)
# get style of cell need merged
current_style = m['style']
self._merge_body_cell(end_column, end_row, start_column,
start_row,
border_pattern[current_style.border],
fill_pattern[current_style.fill],
font_pattern[current_style.font],
alignment_pattern[current_style.al])
# set border
coordinate = coordinate_transfer(
start_row, start_column) + ':' + coordinate_transfer(
end_row, end_column)
style_range(self.core.ws,
coordinate,
border=border_pattern[current_style.border],
fill=fill_pattern[current_style.fill],
font=font_pattern[current_style.font],
alignment=alignment_pattern[current_style.al])
def _cal_Target_Proceed(self):
main_table_body = self.main_table.table_body
num_of_follower = 0 if self.main_table.follower is None else len(
self.main_table.follower)
num_of_row = len(
self.main_table.follower[0].target[target_mapper[0]].container)
for i in range(num_of_follower):
# get base volume coordinate
base_coordinate = coordinate_transfer(self.origin[0] + 1 + i,
self.origin[1] + 4)
first_column = coordinate_transfer(main_table_body[0] + 2, main_table_body[1] + 3 + i * 6 + 4) + \
':' + coordinate_transfer(main_table_body[0] + 2 + num_of_row - 1,
main_table_body[1] + 3 + i * 6 + 4)
second_column = coordinate_transfer(main_table_body[0] + 2, main_table_body[1] + 3 + i * 6 + 5) + \
':' + coordinate_transfer(main_table_body[0] + 2 + num_of_row - 1,
main_table_body[1] + 3 + i * 6 + 5)
formula = '=' + base_coordinate + '*' + 'SUMPRODUCT(' + first_column + ',' + second_column + ')'
self.container[attach_column_type[5]].container[i].set_formula(
formula)
# assign total formula
total_coordinate = coordinate_transfer(self.origin[0] + 1, self.origin[1] + 6) + ':' + \
coordinate_transfer(self.origin[0] + num_of_follower, self.origin[1] + 6)
total_formula = "=SUM(" + total_coordinate + ")"
self.container[attach_column_type[5]].container[-1].set_formula(
total_formula)
# assign if table has owner
if self.main_table.owner is not None:
self.container[attach_column_type[5]].container.append(
Cube(bg_color=bg_color[3]))
def _render_table_header(self):
# render cell with style
for i in range(self.header.x):
for j in range(self.header.y):
x = i + self.origin[0] - 1
# y should add title
y = j + self.origin[1] - 1
current_value = self.header.matrix[i][j]
checked_style = self._check_cube_style(i, j)
current_cube = Cube(bg_color[4], value=current_value) if checked_style is None else Cube(
checked_style.fill, value=current_value)
self.write_cube_to_book(x + 1, y + 1, current_cube)
# merge cell
if self.header.merge is not None:
for m in self.header.merge:
start_row = m['coordinate'][0] + self.origin[0]
start_column = m['coordinate'][1] + self.origin[1]
end_row = m['coordinate'][2] + self.origin[0]
end_column = m['coordinate'][3] + self.origin[1]
self.ws_main.merge_cells(start_row=start_row,
start_column=start_column,
end_row=end_row,
end_column=end_column)
# get style of cell need merged
current_style = m['style']
self._merge_body_cell(end_column, end_row, start_column, start_row,
border_pattern[current_style.border], fill_pattern[current_style.fill],
font_pattern[current_style.font], alignment_pattern[current_style.al])
# set border
coordinate = coordinate_transfer(start_row, start_column) + ':' + coordinate_transfer(end_row,
end_column)
style_range(self.ws_main, coordinate,
border=border_pattern[current_style.border],
fill=fill_pattern[current_style.fill],
font=font_pattern[current_style.font],
alignment=alignment_pattern[current_style.al])
def _render_owner(self, counter, index_x, index_y, num_of_column):
# write owner if exist
if self.owner is not None:
# get some column parameter
person_target_column_number = len(self.owner.target)
person_column_number = person_target_column_number
# write person name
for i in range(person_column_number):
self.write_cube_to_book(index_x + counter, index_y + i,
Cube(bg_color[4], self.owner.name))
# merge name cells
start_row = index_x + counter
start_column = index_y
end_row = index_x + counter
end_column = index_y + person_column_number - 1
self._merge_body_cell(end_column, end_row, start_column, start_row,
border_pattern[side_style[1]], fill_pattern[bg_color[4]],
font_pattern[font_style[2]], alignment_pattern[alignment[2]])
counter += 1
# write column name
col_name_style = Style(bg_color[4], font=font_style[2], al=alignment[2])
for i in range(person_column_number):
self.write_cube_to_book(index_x + counter, index_y + i,
Cube(bg_color[4], target_mapper[i], style=col_name_style))
counter += 1
# write column data
for i in range(num_of_column):
for j in range(person_target_column_number):
# cast formula type to real formula if the cube has formula
if formula_type[1] == self.owner.target[target_mapper[j]].container[i].formula:
start_cube = coordinate_transfer(index_x + counter, index_y + j)
end_cube = coordinate_transfer(index_x + counter + i - 1, index_y + j)
formula = "=SUM(" + start_cube + ":" + end_cube + ")"
self.owner.target[target_mapper[j]].container[i].set_formula(formula)
self.write_cube_to_book(index_x + counter + i, index_y + j,
self.owner.target[target_mapper[j]].container[i])
def _render_footer(self):
# render total row
sum_total = self.header['total']
scale_x = len(self.data)
foot_x = self.origin[0] + self.header['scale'][0] + scale_x
foot_y = self.origin[1]
for i in range(self.header['scale'][1]):
if i < 3:
current_cube = Cube(style=Style(bg_color=bg_color[4],
border=side_style[3],
al=alignment[1],
font=font_style[2]),
value='Sum Total')
self.core.write_cube_to_book(foot_x, foot_y + i, current_cube)
else:
flag = sum_total[i]
if flag == 1:
current_cube = Cube(style=Style(bg_color=bg_color[1],
border=side_style[3],
al=alignment[3],
font=font_style[2]))
start = coordinate_transfer(
self.origin[0] + 1 + self.header['scale'][0],
foot_y + i)
end = coordinate_transfer(foot_x - 1, foot_y + i)
current_cube.set_formula('=SUM({0}:{1})'.format(
start, end))
self.core.write_cube_to_book(foot_x, foot_y + i,
current_cube)
else:
current_cube = Cube(style=Style(bg_color=bg_color[1],
border=side_style[3],
al=alignment[3],
font=font_style[2]),
value='N/A')
self.core.write_cube_to_book(foot_x, foot_y + i,
current_cube)
self._merge_body_cell(foot_y + 3, foot_x, foot_y, foot_x,
border_pattern[side_style[3]],
fill_pattern[bg_color[4]],
font_pattern[font_style[2]],
alignment_pattern[alignment[1]])
# render global
for i in range(self.header['scale'][1]):
if i < 3:
current_cube = Cube(style=Style(bg_color=bg_color[4],
border=side_style[1],
al=alignment[1],
font=font_style[2]),
value='Target From Global')
self.core.write_cube_to_book(foot_x + 1, foot_y + i,
current_cube)
elif i < 18:
current_cube = Cube(style=Style(bg_color=bg_color[4],
border=side_style[1],
al=alignment[3],
font=font_style[1]),
value='N/A')
self.core.write_cube_to_book(foot_x + 1, foot_y + i,
current_cube)
else:
current_cube = Cube(style=Style(bg_color=bg_color[3],
border=side_style[1],
al=alignment[3],
font=font_style[2]),
value=None)
self.core.write_cube_to_book(foot_x + 1, foot_y + i,
current_cube)
self._merge_body_cell(foot_y + 3, foot_x + 1, foot_y, foot_x + 1,
border_pattern[side_style[1]],
fill_pattern[bg_color[4]],
font_pattern[font_style[2]],
alignment_pattern[alignment[1]])
def _formula_converter(self, name, row, flag, x, y, number_format):
tmp_y = y - self.origin[1]
if tmp_y < 4:
style = Style(bg_color=bg_color[4],
border=side_style[1],
al=alignment[1])
elif tmp_y >= 3 and flag == 0:
style = Style(bg_color=bg_color[2],
border=side_style[1],
al=alignment[2])
elif flag < 0:
style = Style(bg_color=bg_color[4],
border=side_style[1],
al=alignment[2])
else:
style = Style(bg_color=bg_color[1], border=side_style[1])
formula = ''
if flag == 0:
return Cube(style=style,
number_format=number_format,
value=row[name])
elif name == 'Market Share %':
formula = '={0}/{1}'.format(coordinate_transfer(x, y - 2),
coordinate_transfer(x, y - 1))
elif name == 'Market Growth %':
formula = '={0}/{1}-1'.format(coordinate_transfer(x, y - 3),
coordinate_transfer(x, y - 1))
elif name == 'Market Share Score':
a, b, c, d = divide_column(
self._get_all_data_by_column('Market Share %'))
formula = '=IF({0}<{1}, 5, IF({0}<{2}, 4, IF({0}<{3},3, IF({0}<{4}, 2, 1))))'.format(
coordinate_transfer(x, y - 4), a, b, c, d)
elif name == 'Market Growth Score':
a, b, c, d = divide_column(
self._get_all_data_by_column('Market Growth %'))
formula = '=IF({0}<{1}, 5, IF({0}<{2}, 4, IF({0}<{3},3, IF({0}<{4}, 2, 1))))'.format(
coordinate_transfer(x, y - 3), a, b, c, d)
elif name == 'Market Share Score(0.75)':
formula = '={0}*0.75'.format(coordinate_transfer(x, y - 3))
elif name == 'Market Growth Score(0.15)':
formula = '={0}*0.15'.format(coordinate_transfer(x, y - 3))
elif name == 'Platform Score(0.1)':
formula = '={0}*0.1'.format(coordinate_transfer(x, y - 3))
elif name == 'Platform Score':
formula = '={0}'.format(coordinate_transfer(x, y - 3))
elif name == 'Total Score':
formula = '=SUM({0}:{1})'.format(coordinate_transfer(x, y - 3),
coordinate_transfer(x, y - 1))
elif name == 'Increase %':
row = len(self.data)
formula = '={0}*({1}-{2})/SUMPRODUCT({3}:{4},{5}:{6})'.format(
coordinate_transfer(x, y - 1),
coordinate_transfer(self.origin[0] + 1 + row + 1, y + 1),
coordinate_transfer(self.origin[0] + 1 + row, y - 13),
coordinate_transfer(self.origin[0] + 1 + 1, y - 13),
coordinate_transfer(self.origin[0] + row, y - 13),
coordinate_transfer(self.origin[0] + 1 + 1, y - 1),
coordinate_transfer(self.origin[0] + row, y - 1),
)
elif name == 'Ref Target KL':
formula = '={0}*(1+{1})'.format(coordinate_transfer(x, y - 14),
coordinate_transfer(x, y - 1))
else:
pass
return Cube(style=style, formula=formula, number_format=number_format)
def _add_table_border(self):
coordinate = coordinate_transfer(self.origin[0] + 1, self.origin[1]) + ':' + \
coordinate_transfer(self.end_point[0], self.end_point[1])
style_range(self.core.ws, coordinate, thick_border)
def _render_follower(self, counter, index_x, index_y, num_of_column):
# write follower if exist
if len(self.follower) > 0:
# for all follower
for f in range(len(self.follower)):
# get some column parameter
person_target_column_number = len(self.follower[f].target)
person_ref_column_number = len(self.follower[f].ref)
person_column_number = person_target_column_number + person_ref_column_number
inner_counter = 0
# write person name
for i in range(person_column_number):
self.write_cube_to_book(index_x + counter + inner_counter, index_y + i + f * person_column_number,
Cube(bg_color[4], self.follower[f].name))
# merge name cells
start_row = index_x + counter + inner_counter
start_column = index_y + f * person_column_number
end_row = index_x + counter + inner_counter
end_column = index_y + f * person_column_number + person_column_number - 1
self._merge_body_cell(end_column, end_row, start_column, start_row,
border_pattern[side_style[1]], fill_pattern[bg_color[4]],
font_pattern[font_style[2]], alignment_pattern[alignment[2]])
inner_counter += 1
# write column name
col_name_style = Style(bg_color[4], font=font_style[2], al=alignment[2])
for i in range(person_target_column_number):
self.write_cube_to_book(index_x + counter + inner_counter, index_y + i + f * person_column_number,
Cube(bg_color[4], ref_mapper[i], style=col_name_style))
for i in range(person_target_column_number, person_column_number):
self.write_cube_to_book(index_x + counter + inner_counter, index_y + i + f * person_column_number,
Cube(bg_color[4], target_mapper[i - person_target_column_number],
style=col_name_style))
inner_counter += 1
# write column data
for i in range(num_of_column):
for j in range(person_ref_column_number):
# cast formula type to real formula if the cube has formula
if formula_type[1] == self.follower[f].ref[ref_mapper[j]].container[i].formula:
start_cube = coordinate_transfer(index_x + counter + inner_counter,
index_y + j + f * person_column_number)
end_cube = coordinate_transfer(index_x + counter + i + inner_counter - 1,
index_y + j + f * person_column_number)
formula = "=SUM(" + start_cube + ":" + end_cube + ")"
self.follower[f].ref[ref_mapper[j]].container[i].set_formula(formula)
self.write_cube_to_book(index_x + counter + i + inner_counter,
index_y + j + f * person_column_number,
self.follower[f].ref[ref_mapper[j]].container[i])
for i in range(num_of_column):
for j in range(person_target_column_number):
# cast formula type to real formula if the cube has formula
if formula_type[1] == self.follower[f].target[target_mapper[j]].container[i].formula:
start_cube = coordinate_transfer(index_x + counter + inner_counter,
index_y + j + person_ref_column_number + f * person_column_number)
end_cube = coordinate_transfer(index_x + counter + i + inner_counter - 1,
index_y + j + person_ref_column_number + f * person_column_number)
formula = "=SUM(" + start_cube + ":" + end_cube + ")"
self.follower[f].target[target_mapper[j]].container[i].set_formula(formula)
self.write_cube_to_book(index_x + counter + i + inner_counter, index_y + j +
person_ref_column_number + f * person_column_number,
self.follower[f].target[target_mapper[j]].container[i])
counter += 1
def _set_formula(self, x, y, formula):
self.ws_main[coordinate_transfer(x, y)] = formula