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]