def setBorder(sh, sheetRange):
# adjust before coming here
beginCell = "".join((sheetRange['beginHeader'][0], str(sheetRange['beginHeader'][1])))
endCell = "".join((sheetRange['endData'][0], str(sheetRange['endData'][1])))
dataRange = beginCell + ':' + endCell
rows = sh[dataRange]
# formats headers with thick borders
headerRow = sheetRange['beginData'][1] - sheetRange['beginHeader'][1]
borderHeight = 25
hair = Side(border_style='hair', color='d3d3d3')
thick = Side(border_style='thick', color='a9a9a9')
alignV = Alignment(vertical='center')
# alignH = Alignment(horizontal='center')
align = Alignment(vertical='center', horizontal='center')
maxRow = len(rows) # index of the last row
# print("MaxRow", maxRow)
for row, cells in enumerate(rows, 1):
# print("PosRow, cells: ", row, cells)
maxCol = len(cells) # index of the last cell
# print("MaxCol", maxCol)
for col, cell in enumerate(cells, 1):
# print("PosCol, cell: ", col, cell)
sh.row_dimensions[cell.row].height = borderHeight
cell.alignment = alignV
if col != 1:
cell.alignment = align
border = Border(
left=hair,
right=hair,
top=hair,
bottom=hair
)
# applies thick border on corners
if col == 1:
border.left = thick
if col == maxCol:
border.right = thick
if row == 1:
border.top = thick
if row == maxRow:
border.bottom = thick
# applies thick border on header rows
if row == headerRow:
border.top = thick
border.bottom = thick
cell.font = Font(bold=True)
cell.border = border
sh.row_dimensions[sh.max_row + 1].height = borderHeight
def set_border(cell_range):
rows = cell_range
side = Side(border_style='medium', color="FF000000")
side2 = Side(border_style='thin', color='FF000000')
# font = Font(name='', size=12,border=True)
rows = list(rows)
max_y = len(rows) - 1
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # pos_y 行 pos_x 列
for pos_x, cell in enumerate(cells):
border = Border(left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom)
if pos_x == 0:
border.left = side
if pos_x == max_x:
border.right = side
if pos_y == 0:
border.top = side
if pos_y == max_y:
border.bottom = side
# 内部格式
if pos_x != 0 and pos_x != max_x:
border.right = side
border.left = side
if pos_y != 0 and pos_y != max_y:
border.top = side2
border.bottom = side2
# if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
def __create_borders():
"""Create dictionary of the borders """
border = Border()
border.border_style = Border.BORDER_THIN
dict_bordors = {}
top_borders = Border()
top_borders.left = border
top_borders.right = border
top_borders.top = border
bottom_borders = Border()
bottom_borders.bottom = border
bottom_borders.left = border
bottom_borders.right = border
side_borders = Border()
side_borders.right = border
side_borders.left = border
borders = Border()
borders.left = border
borders.top = border
borders.right = border
borders.bottom = border
dict_bordors['full'] = borders
dict_bordors['top'] = top_borders
dict_bordors['bottom'] = bottom_borders
dict_bordors['side'] = side_borders
return dict_bordors
def format_scenario_selector_cells(sheet,
label_col,
selector_col,
row,
active=True):
"""
CellStyles.format_scenario_selector_cells -> None
--``sheet`` is an instance of openpyxl worksheet class
--``label_col`` is the column number where the scenario selector label
lives
--``selector_col`` is the column number where the scenario selector
cell lives
--``row`` is the row where the scenario selector cells live
--``active`` is a bool, indicates whether scenario selector is live
Format cells containing scenario selection label and selector.
"""
side = Side(border_style='thick')
# Left label cell
left_cell = sheet.cell(column=label_col, row=row)
border = Border(left=left_cell.border.left,
top=left_cell.border.top,
bottom=left_cell.border.bottom)
border.left = side
border.top = side
border.bottom = side
left_cell.border = border
left_cell.font = Font(bold=True, size=14)
left_cell.value = "Active Scenario:"
# Blank middle cell
blank_cell = sheet.cell(column=label_col + 1, row=row)
border = Border(top=blank_cell.border.top,
bottom=blank_cell.border.bottom)
border.top = side
border.bottom = side
blank_cell.border = border
# Rightmost cell where selector lives
right_cell = sheet.cell(column=selector_col, row=row)
border = Border(right=right_cell.border.right,
top=right_cell.border.top,
bottom=right_cell.border.bottom)
border.right = side
border.top = side
border.bottom = side
right_cell.border = border
right_cell.alignment = Alignment(horizontal='center')
if active:
color = HARDCODED_COLOR
else:
color = BLACK
font = Font(color=color, bold=True, size=14)
right_cell.font = font
def set_border(ws, min_row, max_row, min_col, max_col):
rows = list(ws.iter_rows(min_row, max_row, min_col, max_col))
side = Side(border_style='thin', color="FF000000")
rows = list(
rows
) # we convert iterator to list for simplicity, but it's not memory efficient solution
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom)
if pos_x == 0:
border.left = side
if pos_x == max_x:
border.right = side
if pos_y == 0:
border.top = side
if pos_y == max_y:
border.bottom = side
# set new border only if it's one of the edge cells
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
def format_area_label(sheet, label, row_num, col_num=None):
"""
CellStyles.format_area_label -> None
--``sheet`` is an instance of openpyxl.worksheet
--``label`` is a the string name of the area to label
--``row_num`` is the row number where the label should be inserted
--``col_num`` is the column number where the labels should be
Format area/statement label and dividing border
"""
side = Side(border_style='double')
if col_num:
col = get_column_letter(col_num)
else:
col = 'A'
cell_cos = col + str(row_num)
cell = sheet[cell_cos]
cell.font = Font(bold=True)
cell.set_explicit_value(label.title(),
data_type=TypeCodes.FORMULA_CACHE_STRING)
if AREA_BORDER:
rows = sheet.iter_rows(row_offset=row_num - 1)
row = rows.__next__()
for cell in row:
border = Border(top=cell.border.top)
border.top = side
cell.border = border
def _apply_color(self, worksheet, blocks: List[SimpleBlock]):
for block in blocks:
for i in range(block.get_top_row(), block.get_bottom_row()+1):
for j in range(block.get_left_col(), block.get_right_col()+1):
col = excel_utils.num2col(j)
if block.block_type.get_best_type() == BasicBlockType.HEADER:
color = self._sandal
elif block.block_type.get_best_type() == BasicBlockType.GLOBAL_ATTRIBUTE:
color = self._blue
elif block.block_type.get_best_type() == BasicBlockType.ATTRIBUTE:
color = self._lblue
elif block.block_type.get_best_type() == BasicBlockType.VALUE:
color = self._grey
else:
color = self._green
worksheet[col + str(i + 1)].fill = color
b = Border()
if i == block.get_top_row():
b.top = Side(border_style='thick', color='FF000000')
if i == block.get_bottom_row():
b.bottom = Side(border_style='thick', color='FF000000')
if j == block.get_left_col():
b.left = Side(border_style='thick', color='FF000000')
if j == block.get_right_col():
b.right = Side(border_style='thick', color='FF000000')
worksheet[col + str(i + 1)].border = b
def clear_border(ws, cell_range):
rows = ws[cell_range]
side = Side(border_style=None, color="00FFFFFF")
rows = list(
rows
) # we convert iterator to list for simplicity, but it's not memory efficient solution
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom)
if pos_x == 0:
border.left = side
if pos_x == max_x:
border.right = side
if pos_y == 0:
border.top = side
if pos_y == max_y:
border.bottom = side
# set new border only if it's one of the edge cells
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
def set_border(ws, cell_range):
"""https://stackoverflow.com/a/34521257"""
rows = ws[cell_range]
side = Side(border_style='thin', color="FF000000")
rows = list(
rows
) # we convert iterator to list for simplicity, but it's not memory efficient solution
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom)
if pos_x == 0:
border.left = side
if pos_x == max_x:
border.right = side
if pos_y == 0:
border.top = side
if pos_y == max_y:
border.bottom = side
# set new border only if it's one of the edge cells
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
def set_border(ws, cell_range):
rows = ws[cell_range]
side = Side(border_style="thin", color="FF000000")
rows = list(rows)
max_y = len(rows) - 1
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1
for pos_x, cell in enumerate(cells):
border = Border(
left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom,
)
if pos_x == 0:
border.left = side
if pos_x == max_x:
border.right = side
if pos_y == 0:
border.top = side
if pos_y == max_y:
border.bottom = side
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
def set_border(ws,
row_start=None,
col_start=None,
row_end=None,
col_end=None,
weight='thin',
outer=None):
side = Side(border_style=weight)
# if no start row/col, return
if not row_start or not col_start:
return
# allow for single cell, row, column
# if row/col end not given or smaller than start row/col
if not row_end or row_end < row_start:
row_end = row_start
if not col_end or col_end < col_start:
col_end = col_start
for i in range(row_start, row_end + 1):
for j in range(col_start, col_end + 1):
if i == row_start or i == row_end or j == col_start or j == col_end:
c = ws.cell(i, j)
border = Border(c.border.left, c.border.right, c.border.top,
c.border.bottom)
if i == row_start:
border.top = side
if i == row_end:
border.bottom = side
if j == col_start:
border.left = side
if j == col_end:
border.right = side
c.border = border
def celula(tipoCelula, celula_letra, celula_num, texto, fill=None):
global sheet
c = celula_letra + str(celula_num)
sheet[c].value = texto
cborder = Border()
align = Alignment()
#todo centrar texto
if tipoCelula == TipoCelula.Cima:
align.horizontal = "right"
if celula_num == 5:
cborder.top = thick
else:
cborder.top = thin
elif tipoCelula == TipoCelula.Baixo:
align.horizontal = "center"
if celula_num == tabela_ultima_linha:
cborder.bottom = thick
else:
cborder.bottom = thin
if header_pos[celula_letra] == header_pos_inicio_semana:
cborder.left = thick
cborder.right = thin
elif header_pos[celula_letra] == header_pos_fim_semana:
cborder.right = thick
cborder.left = thin
elif header_pos[celula_letra] == header_pos_fim_semana - 1:
cborder.right = thin
cborder.left = thick
else:
cborder.right = thin
cborder.left = thin
sheet[c].border = cborder
if fill is not None:
sheet[c].fill = fill
sheet[c].alignment = align
def set_allborder(ws, cell_range):
rows = list(ws.iter_rows(cell_range))
side = Side(border_style='thin', color="FF000000")
for pos_y, cells in enumerate(rows):
for pos_x, cell in enumerate(cells):
border = Border(
left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom
)
border.left = side
border.right = side
border.top = side
border.bottom = side
cell.border = border
def format_border(cell,
top=False,
bottom=False,
left=False,
right=False,
border_style='thin'):
"""
CellStyles.format_bold() -> None
--``cell`` is an instance of openpyxl cell class
--``top`` whether to draw the top border, default is False
--``bottom`` whether to draw the bottom border, default is False
--``left`` whether to draw the left border, default is False
--``right`` whether to draw the right border, default is False
--``border_style`` is any openpyxl border style, default is `thin`
Sets horizontal text alignment in cell.
"""
side = Side(border_style=border_style)
# no_side = Side(border_style=None)
border = Border(top=cell.border.top,
bottom=cell.border.bottom,
left=cell.border.left,
right=cell.border.right)
if top:
border.top = side
if bottom:
border.bottom = side
if left:
border.left = side
if right:
border.right = side
cell.border = border
def set_border(ws, cell_range): rows = ws[cell_range] side = Side(border_style='thin', color="FF000000") rows = list(rows) # we convert iterator to list for simplicity, but it's not memory efficient solution max_y = len(rows) - 1 # index of the last row for pos_y, cells in enumerate(rows): max_x = len(cells) - 1 # index of the last cell for pos_x, cell in enumerate(cells): border = Border( left=cell.border.left, right=cell.border.right, top=cell.border.top, bottom=cell.border.bottom ) if pos_x == 0: border.left = side if pos_x == max_x: border.right = side border.top = side border.bottom = side cell.border = border
def set_border(ws, cell_range):
rows = ws[cell_range]
side = Side(border_style='thin', color="FF000000")
rows = list(rows)
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom)
if pos_x == 0:
border.left = side
if pos_x == max_x:
border.right = side
if pos_y == 0:
border.top = side
if pos_y == max_y:
border.bottom = side
# set new border only if it's one of the edge cells
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
def set_border(ws, cell_range):
rows = list(ws.iter_rows(cell_range))
side = Side(border_style='thin', color="FF000000")
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(
left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom
)
if pos_x == 0:
border.left = side
if pos_x == max_x:
border.right = side
if pos_y == 0:
border.top = side
if pos_y == max_y:
border.bottom = side
# set new border only if it's one of the edge cells
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
def set_border(ws, cell_range, thin_border=True, color=black_color_string):
rows = ws[cell_range]
side = Side(border_style='medium',
color=black_color_string) # Black color border by default
rows = list(rows)
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
if thin_border:
border = Border(left=Side(style='thin', color=color),
right=Side(style='thin', color=color),
top=Side(style='thin', color=color),
bottom=Side(style='thin', color=color))
else:
border = Border(left=Side(),
right=Side(),
top=Side(),
bottom=Side())
if pos_x == 0:
border.left = side
if pos_x == max_x:
border.right = side
if pos_y == 0:
border.top = side
if pos_y == max_y:
border.bottom = side
# Set thin border for each cell
cell.border = border
# set medium border for the edge cells
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
def write(worksheet, table, position):
row_height = [None] * table.height
col_width = [None] * table.width
x, y = position[0] + 1, position[1] + 1
for row_num in range(table.height):
for col_num in range(table.width):
cell = table[row_num][col_num]
if cell is None:
continue
excel_x = x + row_num
excel_y = y + col_num
if any([cell.height > 1, cell.width > 1]):
worksheet.merge_cells(start_row=excel_x,
end_row=excel_x + cell.height - 1,
start_column=excel_y,
end_column=excel_y + cell.width - 1)
excel_cell = worksheet.cell(row=excel_x,
column=excel_y,
value=cell.value)
if cell.style is None:
continue
font_weight = cell.style.get('font_weight')
font_size = cell.style.get('font_size')
if font_weight is not None or font_size is not None:
font = Font(size=font_size, bold=font_weight == 'blod')
excel_cell.font = font
vertical_align = cell.style.get('vertical_align')
horizontal_align = cell.style.get('horizontal_align')
if vertical_align is not None or horizontal_align is not None:
align = Alignment(horizontal=horizontal_align,
vertical=vertical_align)
excel_cell.alignment = align
background_color = cell.style.get('background_color')
if background_color is not None:
fill = PatternFill(start_color=background_color,
end_color=background_color,
fill_type='darkDown')
excel_cell.fill = fill
side = Side(border_style='thin', color="fff0f0f0")
border = Border(left=excel_cell.border.left,
right=excel_cell.border.right,
top=excel_cell.border.top,
bottom=excel_cell.border.bottom)
border.left = side
border.right = side
border.top = side
border.bottom = side
excel_cell.border = border
if all([cell.height == 1, cell.width == 1]):
font_size = font_size or 11
width = cell.style.get('width')
if width is not None:
if width == 'auto':
width = (len(
six.text_type(cell.value).encode('utf-8')) +
len(six.text_type(cell.value))
) / 2 * math.ceil(font_size / 11.0)
col_width[col_num] = max(width,
col_width[col_num],
key=lambda v: v or 0)
height = cell.style.get('height')
if height is not None:
if height == 'auto':
height = math.ceil(font_size * 1.5)
row_height[row_num] = max(height,
row_height[row_num],
key=lambda v: v or 0)
else:
height = cell.style.get('height')
if height is not None:
if height == 'auto':
height = math.ceil(font_size * 1.5)
row_height[row_num] = max(height,
row_height[row_num],
key=lambda v: v or 0)
for i, value in enumerate(row_height):
if value is None:
pass
else:
worksheet.row_dimensions[position[0] + i + 1].height = value
for i, value in enumerate(col_width):
if value is None:
pass
else:
column_letter = get_column_letter(position[1] + i + 1)
worksheet.column_dimensions[column_letter].width = value
def xls_style_to_xlsx(self, xf_ndx):
"""Convert an xls xf_ndx into a 6-tuple of styles for xlsx"""
font = Font()
fill = PatternFill()
border = Border()
alignment = Alignment()
number_format = 'General'
protection = Protection(locked=False, hidden=False)
if xf_ndx < len(self.book.xf_list):
xf = self.book.xf_list[xf_ndx]
xls_font = self.book.font_list[xf.font_index] # Font object
font.b = xls_font.bold
font.i = xls_font.italic
if xls_font.character_set:
font.charset = xls_font.character_set
font.color = self.xls_color_to_xlsx(xls_font.colour_index)
escapement = xls_font.escapement # 1=Superscript, 2=Subscript
family = xls_font.family # FIXME: 0=Any, 1=Roman, 2=Sans, 3=monospace, 4=Script, 5=Old English/Franktur
font.name = xls_font.name
font.sz = self.xls_height_to_xlsx(xls_font.height) # A twip = 1/20 of a pt
if xls_font.struck_out:
font.strike = xls_font.struck_out
if xls_font.underline_type:
font.u = ('single', 'double')[(xls_font.underline_type&3)-1]
xls_format = self.book.format_map[xf.format_key] # Format object
number_format = xls_format.format_str
if False: # xlrd says all cells are locked even if the sheet isn't protected!
protection.locked = xf.protection.cell_locked
protection.hidden = xf.protection.formula_hidden
fill_patterns = {0x00:'none', 0x01:'solid', 0x02:'mediumGray', 0x03:'darkGray', 0x04:'lightGray',
0x05:'darkHorizontal', 0x06:'darkVertical', 0x07:'darkDown', 0x08:'darkUp', 0x09:'darkGrid',
0x0A:'darkTrellis', 0x0B:'lightHorizontal', 0x0C:'lightVertical', 0x0D:'lightDown', 0x0E:'lightUp',
0x0F:'lightGrid', 0x10:'lightTrellis', 0x11:'gray125', 0x12:'gray0625'
}
fill_pattern = xf.background.fill_pattern
fill_background_color = self.xls_color_to_xlsx(xf.background.background_colour_index)
fill_pattern_color = self.xls_color_to_xlsx(xf.background.pattern_colour_index)
fill.patternType = fill_patterns.get(fill_pattern, 'none')
fill.bgColor = fill_background_color
fill.fgColor = fill_pattern_color
horizontal = {0:'general', 1:'left', 2:'center', 3:'right', 4:'fill', 5:'justify', 6:'centerContinuous', 7:'distributed'}
vertical = {0:'top', 1:'center', 2:'bottom', 3:'justify', 4:'distributed'}
hor_align = horizontal.get(xf.alignment.hor_align, None)
if hor_align:
alignment.horizontal = hor_align
vert_align = vertical.get(xf.alignment.vert_align, None)
if vert_align:
alignment.vertical = vert_align
alignment.textRotation = xf.alignment.rotation
alignment.wrap_text = xf.alignment.text_wrapped
alignment.indent = xf.alignment.indent_level
alignment.shrink_to_fit = xf.alignment.shrink_to_fit
border_styles = {0: None, 1:'thin', 2:'medium', 3:'dashed', 4:'dotted',
5:'thick', 6:'double', 7:'hair', 8:'mediumDashed', 9:'dashDot',
10:'mediumDashDot', 11:'dashDotDot', 12:'mediumDashDotDot',
13:'slantDashDot',}
xls_border = xf.border
top = Side(style=border_styles.get(xls_border.top_line_style), color=self.xls_color_to_xlsx(xls_border.top_colour_index))
bottom = Side(style=border_styles.get(xls_border.bottom_line_style), color=self.xls_color_to_xlsx(xls_border.bottom_colour_index))
left = Side(style=border_styles.get(xls_border.left_line_style), color=self.xls_color_to_xlsx(xls_border.left_colour_index))
right = Side(style=border_styles.get(xls_border.right_line_style), color=self.xls_color_to_xlsx(xls_border.right_colour_index))
diag = Side(style=border_styles.get(xls_border.diag_line_style), color=self.xls_color_to_xlsx(xls_border.diag_colour_index))
border.top = top
border.bottom = bottom
border.left = left
border.right = right
border.diagonal = diag
border.diagonalDown = xls_border.diag_down
border.diagonalUp = xls_border.diag_up
return (font, fill, border, alignment, number_format, protection)
def format_border_group(sheet,
st_col,
ed_col,
st_row,
ed_row,
border_style='thin'):
"""
CellStyles.format_border_group -> None
--``sheet`` is an instance of openpyxl worksheet class
--``st_col`` is the starting column in the group
--``ed_col`` is the ending column in the group
--``st_row`` is the starting row in the group
--``ed_row`` is the ending row in the group
--``border_style`` is any openpyxl border style, default is `thin`
Draws thin border around group of cells defined by starting and ending
rows and columns.
"""
side = Side(border_style=border_style)
# SET TOP BORDER
row = st_row
for c in range(st_col, ed_col + 1):
cell = sheet.cell(column=c, row=row)
border = Border(top=cell.border.top)
border.top = side
cell.border = border
# SET LEFT BORDER
col = st_col
for r in range(st_row, ed_row + 1):
cell = sheet.cell(column=col, row=r)
border = Border(left=cell.border.left)
border.left = side
cell.border = border
# SET RIGHT BORDER
col = ed_col
if st_col != ed_col:
for r in range(st_row, ed_row + 1):
cell = sheet.cell(column=col, row=r)
border = Border(right=cell.border.right)
border.right = side
cell.border = border
else:
for r in range(st_row, ed_row + 1):
cell = sheet.cell(column=col, row=r)
border = Border(right=cell.border.right, left=cell.border.left)
border.left = side
border.right = side
cell.border = border
# SET TOP AND BOTTOM BORDERS
row = ed_row
if st_row != ed_row:
for c in range(st_col, ed_col + 1):
cell = sheet.cell(column=c, row=row)
border = Border(bottom=cell.border.bottom,
left=cell.border.left,
top=cell.border.top,
right=cell.border.right)
border.bottom = side
cell.border = border
else:
for c in range(st_col, ed_col + 1):
cell = sheet.cell(column=c, row=row)
border = Border(bottom=cell.border.bottom,
left=cell.border.left,
top=cell.border.top,
right=cell.border.right)
border.top = side
border.bottom = side
cell.border = border
if st_col != ed_col:
# SET UPPER-LEFT CORNER BORDER
cell = sheet.cell(column=st_col, row=st_row)
border = Border(bottom=cell.border.bottom,
left=cell.border.left,
top=cell.border.top,
right=cell.border.right)
border.top = side
border.left = side
cell.border = border
# SET UPPER-RIGHT CORNER BORDER
cell = sheet.cell(column=ed_col, row=st_row)
border = Border(bottom=cell.border.bottom,
left=cell.border.left,
top=cell.border.top,
right=cell.border.right)
border.top = side
border.right = side
cell.border = border
# SET LOWER-LEFT CORNER BORDER
cell = sheet.cell(column=st_col, row=ed_row)
border = Border(bottom=cell.border.bottom,
left=cell.border.left,
top=cell.border.top,
right=cell.border.right)
border.bottom = side
border.left = side
cell.border = border
# SET LOWER-RIGHT CORNER BORDER
cell = sheet.cell(column=ed_col, row=ed_row)
border = Border(bottom=cell.border.bottom,
left=cell.border.left,
top=cell.border.top,
right=cell.border.right)
border.bottom = side
border.right = side
cell.border = border
else:
# SET TOP CELL BORDER
cell = sheet.cell(column=st_col, row=st_row)
border = Border(top=cell.border.top,
bottom=cell.border.bottom,
left=cell.border.left,
right=cell.border.right)
border.top = side
border.left = side
border.right = side
cell.border = border
# SET BOTTOM CELL BORDER
cell = sheet.cell(column=st_col, row=ed_row)
border = Border(top=cell.border.top,
bottom=cell.border.bottom,
left=cell.border.left,
right=cell.border.right)
border.bottom = side
border.left = side
border.right = side
cell.border = border
def save_to_xlsx():
wb = Workbook()
# grab the active worksheet
ws = wb.active
ws['A1']="Simple :"
ws['A2']=""
ws.append(["","< 9"]+list(range(9,22))+["> 21"])
#########
policy = getPolicy()[:]
#########
i=1
for row in policy[0][0] :
for k in range(len(row)):
row[k] = row[k].index(max(row[k]))
if (i==1):
ws.append(['A']+row)
else :
ws.append([i]+row)
i+=1
ws['A14'] = ""
ws['A15'] = "As :"
ws['A16'] = ""
ws.append([""]+["A,"+str(k) for k in range (2,11)])
i=1
for row in policy[1][0] :
for k in range(len(row)):
row[k] = row[k].index(max(row[k]))
if (i==1):
ws.append(['A']+row)
else :
ws.append([i]+row)
i+=1
ws['A28'] = ""
ws['A29'] = "Paires :"
ws['A30'] = ""
ws.append([""]+["A,A"]+[str(k)+","+str(k) for k in range (2,11)])
i=1
for row in policy[2][0] :
for k in range(len(row)):
row[k] = row[k].index(max(row[k]))
if (i==1):
ws.append(['A']+row)
else :
ws.append([i]+row)
i+=1
redFill = PatternFill(start_color='FF6666',
end_color='FF6666',
fill_type='solid')
orangeFill = PatternFill(start_color='FFC966',
end_color='FFC966',
fill_type='solid')
greenFill = PatternFill(start_color='6DC066',
end_color='6DC066',
fill_type='solid')
purpleFill = PatternFill(start_color='8067A2',
end_color='8067A2',
fill_type='solid')
alignment=Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
rows = ws['A3:P13']
side = Side(border_style='thin', color="FF000000")
rows = list(rows) # we convert iterator to list for simplicity, but it's not memory efficient solution
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(
left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom
)
border.left = side
border.right = side
border.top = side
border.bottom = side
if (cell.value==0 and pos_x!=0) :
cell.fill=redFill
if (cell.value==1 and pos_x!=0) :
cell.fill=orangeFill
if (cell.value==2 and pos_x!=0) :
cell.fill=greenFill
if (cell.value==3 and pos_x!=0) :
cell.style='Accent4'
cell.border = border
cell.alignment=alignment
rows = ws['A17:J27']
side = Side(border_style='thin', color="FF000000")
rows = list(rows) # we convert iterator to list for simplicity, but it's not memory efficient solution
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(
left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom
)
border.left = side
border.right = side
border.top = side
border.bottom = side
if (cell.value==0 and pos_x!=0) :
cell.fill=redFill
if (cell.value==1 and pos_x!=0) :
cell.fill=orangeFill
if (cell.value==2 and pos_x!=0) :
cell.fill=greenFill
if (cell.value==3 and pos_x!=0) :
cell.style='Accent4'
cell.border = border
cell.alignment=alignment
rows = ws['A31:K41']
side = Side(border_style='thin', color="FF000000")
rows = list(rows) # we convert iterator to list for simplicity, but it's not memory efficient solution
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(
left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom
)
border.left = side
border.right = side
border.top = side
border.bottom = side
if (cell.value==0 and pos_x!=0) :
cell.fill=redFill
if (cell.value==1 and pos_x!=0) :
cell.fill=orangeFill
if (cell.value==2 and pos_x!=0) :
cell.fill=greenFill
if (cell.value==3 and pos_x!=0) :
cell.fill=purpleFill
cell.border = border
cell.alignment=alignment
# Save the file
wb.save("mypolicy_IAvsIA_100M_v4.xlsx")
max_y = len(outerRows) - 1
for pos_y, cells in enumerate(outerRows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom)
# Checking if an edge cell
if pos_x == 0:
border.left = outerBorderStyle
if pos_x == max_x:
border.right = outerBorderStyle
if pos_y == 0:
border.top = outerBorderStyle
if pos_y == max_y:
border.bottom = outerBorderStyle
# Set new border only if it's one of the edge cells
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
wb.save(SPREADSHEET_NAME)
print('Spreadsheet changes saved')
except FileNotFoundError:
print(
NEW_TICKETS_FILE_NAME,
'(this week\'s tickets) not found, please add to the same directory of the script'
)
print('Directory of script:', os.path.dirname(os.path.realpath(__file__)))
