def create_first_column(self, wks): self.enable_batch(True) zero_coords = (1, 1) style_cell = Cell(zero_coords, worksheet=wks) self.set_header_styles(style_cell) drange = DataRange( start=zero_coords, end=( zero_coords[0] + self.header_height - 1, zero_coords[1], ), worksheet=wks, ) drange.apply_format(style_cell) brigade_zero_coords = (self.header_height + 1, 1) brigade_cell = Cell(brigade_zero_coords, worksheet=wks) self.set_brigade_style(brigade_cell) drange = DataRange( start=brigade_zero_coords, end=( brigade_zero_coords[0] + len(self.brigades) - 1, brigade_zero_coords[1], ), worksheet=wks, ) drange.apply_format(brigade_cell) wks.adjust_row_height(1, None, 50) wks.adjust_row_height(2, self.header_height, 75) wks.adjust_column_width(1, None, 200) self.enable_batch(False) values = [ [["Название мероприятия", "Участник/статус"]], ] brigades_titles = [str(brigade) for brigade in self.brigades] ranges = [ (zero_coords, (zero_coords[0] + self.header_height - 1, zero_coords[1])), ( brigade_zero_coords, ( brigade_zero_coords[0] + len(self.brigades) - 1, brigade_zero_coords[1], ), ), ] values.append([brigades_titles]) self.wks.update_values_batch(ranges=ranges, values=values, majordim="COLUMNS")
def apply_cells_formating(self, wks, dx): # форматирование self.enable_batch(True) # for header zero_coords = (1, 2) style_cell = Cell(zero_coords, worksheet=wks) self.set_header_styles(style_cell) drange = DataRange( start=zero_coords, end=( zero_coords[0] + self.header_height - 1, zero_coords[1] + dx, ), worksheet=wks, ) drange.apply_format(style_cell) # for body data_zero_coords = (self.header_height + 1, 2) data_cell = Cell(data_zero_coords, worksheet=wks) self.set_data_style(data_cell) data_cell.set_number_format( pattern="##0.#####", format_type=pygsheets.FormatType.NUMBER ) drange = DataRange( start=data_zero_coords, end=( data_zero_coords[0] + len(self.brigades) - 1, data_zero_coords[1] + dx, ), worksheet=wks, ) drange.apply_format(data_cell) wks.adjust_column_width( data_zero_coords[1], data_zero_coords[1] + dx, 150, ) self.enable_batch(False)
def past_header(self, title, zeroCell): self.enable_batch(True) style_cell = Cell(zeroCell, worksheet=self.wks) self.set_header_styles(style_cell) drange = DataRange( start=zeroCell, end=( zeroCell[0] + self.headeing_height - 1, zeroCell[1] + self.columns - 1, ), worksheet=self.wks, ) drange.merge_cells("MERGE_ALL") drange.apply_format(style_cell) self.enable_batch(False) style_cell.value = title self.current_row = drange.end_addr[0]
def past_info_cells(self, nextRow, values): style_cell = Cell((nextRow, self.zeroCell[1]), worksheet=self.wks) self.enable_batch(True) self.set_info_styles(style_cell) style_range = DataRange( start=(style_cell.row, style_cell.col), end=(style_cell.row + len(values) - 1, style_cell.col + self.columns - 1), worksheet=self.wks, ) style_range.apply_format(style_cell) ranges = [] for index, _ in enumerate(values): info_cell = Cell( (style_cell.row + index, style_cell.col + 1), worksheet=self.wks ) value_range = DataRange( start=(info_cell.row, info_cell.col), end=(info_cell.row, info_cell.col + self.columns - 2), worksheet=self.wks, ) value_range.merge_cells("MERGE_ALL") ranges.append( [ (style_cell.row + index, style_cell.col), (info_cell.row, info_cell.col), ] ) self.current_row = style_cell.row + index self.enable_batch(False) self.wks.update_values_batch(ranges=ranges, values=values, majordim="COLUMNS")
# -- get the working sheet sheet = spreadsheet.worksheet_by_title('Dashboard') # -- working sheet info print(f'WorkSheet : {sheet.title} size: ({sheet.rows}x{sheet.cols})') # -- update values in wks sheet.update_value('A1', '20') sheet.update_value('B1', '30') sheet.update_value('C1', '=A1+B1') # -- setting validaton on a cell sheet.set_data_validation(start="C2", end="C2", condition_type='ONE_OF_LIST', condition_values=['One', 'Two'], showCustomUi='True') # -- change cell color cellB6 = sheet.cell('B6') c1, c2, c3 = colors.hex_to_rgb("#4285f4") cellB6.color = (c1 / 255, c2 / 255, c3 / 255, 0.9 ) # color is a tuple (red, green, blue, alpha) of float values # -- change row color row10 = DataRange( f'A10', 'Z10', worksheet=sheet) # or row10 = sheet.get_row(10, returnas = "range") row10.apply_format(cellB6) # -- formating column text
def past_boec(self, nextRow, queryset, worth=0): params = queryset.values_list("id", "last_name", "first_name", "middle_name") data = [] for boec in params: full_name = f"{boec[1]} {boec[2]} {boec[3]}" participant = Participant.objects.get( boec=boec[0], event=self.object, worth=worth ) ignored = participant.ignored # TODO определиться что делать в случае length != 1 boec_seasons = Season.objects.filter(boec=boec[0]).order_by("-year") last_season: Season = boec_seasons[0] # вынесено, чтобы зачесть пред. отряду при выезде в новый отряд report = last_season.season_reports.first() last_season_year = report.year # Если мероприятие осеннее, то мы зачтем все весенние мероприятия выезжавшим в новом сезоне if ( boec_seasons.count() > 1 and last_season.year == 2021 and self.object.start_date.date() < autumn_started_date ): last_season = boec_seasons[1] is_accepted = check_is_accepted( worth=worth, year=last_season_year, event_date=self.object.start_date.date(), ignored=ignored, ) if ( self.object.worth == Event.EventWorth.VOLUNTEER and worth == Participant.WorthEnum.DEFAULT ): is_accepted = True report = last_season.season_reports.first() row = [ full_name.strip(), str(report.brigade), last_season.year, is_accepted, ] data.append(row) if len(data) != 0: self.enable_batch(True) style_cell = Cell((nextRow, self.zeroCell[1]), worksheet=self.wks) self.set_info_styles(style_cell) style_range = DataRange( start=(nextRow, self.zeroCell[1]), end=(nextRow + len(data) - 1, self.zeroCell[1] + self.columns - 1), worksheet=self.wks, ) style_range.apply_format(style_cell) self.enable_batch(False) self.wks.update_values_batch( ranges=[ ( (nextRow, self.zeroCell[1]), (nextRow + len(data) - 1, self.zeroCell[1] + self.columns - 1), ) ], values=[data], majordim="ROWS", ) self.wks.set_data_validation( start=(nextRow, self.zeroCell[1] + self.columns - 1), end=(nextRow + len(data) - 1, self.zeroCell[1] + self.columns - 1), condition_type="BOOLEAN", ) self.current_row = style_range.end_addr[0]