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 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 _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 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 compile_info(plot_objects, field=None): """ :plot_objects: List containing selected ObsPlot objects :return: .xlsx containing a colored field map of the passed plots. """ wb = Workbook() if field: field_set = [field] else: field_set = get_field_object_set(plot_objects) for field in field_set: field_plots = get_plots_in_field(plot_objects, field) worksheet = wb.create_sheet() worksheet.title = field.field_name experiment_current = field_plots[0].experiment experiment_colors = iter(COLOR_SET) cell_color = experiment_colors.next() for plot in field_plots: if plot.experiment != experiment_current: experiment_current = plot.experiment # Cycles through three experiment colors so the spreadsheet doesn't look incredibly dull try: cell_color = experiment_colors.next() except StopIteration: experiment_colors = iter(COLOR_SET) cell_color = experiment_colors.next() cell_fill = PatternFill(start_color=cell_color, end_color=cell_color, fill_type="solid") side = Side(border_style="thin", color="003300") for coordinate in plot.get_coordinates(): worksheet[coordinate] = plot.plot_id worksheet[coordinate].fill = cell_fill border = Border(bottom=worksheet[coordinate].border.bottom) if check_row_even(coordinate): # Slips in a border every 2 rows border.bottom = side worksheet[coordinate].border = border domain = get_plot_domains(field_plots) add_axes(worksheet, domain, field_plots) wb.remove_sheet(wb.get_sheet_by_name("Sheet")) return wb
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) 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) # 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 = 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 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")
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
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__))) time.sleep(4) sys.exit()