def write_data(self, sheet: Worksheet, year_list):
sheet.write_column(1, 0, asset_sheet_content_zh)
col = 1
for year in year_list:
sheet.write(0, col, str(year) + self.date)
sheet.write_column(1, col, self.get_sheet(year).data_list)
col += 1
def DetailsWriter(work_book: Workbook, work_sheet: Worksheet,
excel_sheet_number: int, row: int,
details_list: list) -> int:
''' Function to write details under respective headers in the excel table
Returns a list of count of characters in every cells for the respective row
'''
# Use the required empty value
empty_cell_value = 'NIL'
format_dict = {
'align': 'center',
'valign': 'vcenter',
'fg_color': '#add8e6',
'border': 1
}
cell_format = work_book.add_format(format_dict)
column_width = []
for column, value in enumerate(details_list):
temp_cell_format = None
# Check :- is None / empty
if not value:
value, temp_cell_format = empty_cell_value, cell_format
# If value is a list
elif isinstance(value, list):
# If the type of list elements is non-object type
if len(value) == 0 or not isinstance(value[0], dict):
value = str(value)
# Type of list elements is object type
# TODO :: Nested List need to be handled i.e, [[1,2],[3,4]] , flattening the list might be applicable
else:
# Set the new Sheet name to the value
value = ExcelFileWriter(work_book, excel_sheet_number + 1,
value)
work_sheet.write(row, column, value)
column_width.append(len(str(value)))
return column_width
def MergeRowCells(work_book: Workbook, work_sheet: Worksheet, column: int,
row_start: int, row_end: int, text: str) -> None:
if not text:
return
cell_format = GetDefaultCellFormat(work_book)
# If the cell is in the last row of headers, no need for merging cells
if row_start + 1 == row_end:
work_sheet.write(row_start, column, text, cell_format)
return
# Sample : 'A1:D1'
merge_range_string = NumToExcelAlpha(column) + str(
row_start + 1) + ':' + NumToExcelAlpha(column) + str(row_end)
work_sheet.merge_range(merge_range_string, text, cell_format)
def __init__(self, worksheet: Worksheet, headers=None, header_format=None):
if headers is None:
raise Exception("Invalid headers. List expected!")
if worksheet is None:
raise Exception("Invalid worksheet")
self._headers = headers
self._header_length = [len(header) for header in headers]
self._worksheet = worksheet
for i, header in enumerate(headers):
if header_format is not None:
worksheet.write(0, i, header, header_format)
else:
worksheet.write(0, i, header)
self._next_row = 1
def write(self, frame:Worksheet):
"""
show re on target cell::frame in xlsx
update the content of value a and format based on the re type
:param frame:
:return:
"""
# -normal default
# -crs Cell-specific Reference Signal (CRS)
# -mbsfnrs MBSFN reference signal
# -pbch PBCH
# -uepdsch_dmrs UE-specific Reference Signal (DM-RS) associated with PDSCH
# -edpdcch_dmrs DeModulation Reference Signal (DM-RS) associated with EPDCCH
# -pcs Positioning Reference Signal (PRS)
# -csi_rs SI Reference Signal (CSI-RS)
#only normal cp is supported in current release 2016.1.8
# in future,offset shall derived from an accumulated way or a lookup table
frame.write(self.k+self.n__s*7, self.l+self.n__s*7, self.a, self.reFormat)
def write_column_titles(
worksheet: Worksheet,
num_columns: int,
title_format: Format,
translations: Dict[str, str],
) -> None:
worksheet.write("A1", translations.get("Form", "Form"), title_format)
for i in range(1, num_columns - 1):
worksheet.write_blank(f"{xl_col_to_name(i)}1", "", title_format)
worksheet.write(
f"{xl_col_to_name(num_columns - 1)}1",
translations.get("Question", "Question"),
title_format,
)
worksheet.write(
f"{xl_col_to_name(num_columns)}1",
translations.get("Answer", "Answer"),
title_format,
)
def _static_upper_part(self, worksheet: Worksheet):
# set widths
worksheet.set_column('A:A', 9)
worksheet.set_column('B:B', 36)
worksheet.set_column('C:C', 18)
worksheet.set_column('D:D', 18)
worksheet.write('A1', 'Name des Betriebs', self.fonts.bg)
worksheet.write('D1', 'Musterstrasse 1', self.fonts.rechts)
worksheet.write('D2', 'PLZ und Ort', self.fonts.rechts)
worksheet.write('D3', 'E-Mail: [email protected]', self.fonts.rechtsd)
worksheet.write('A3', 'Telefon: 012 345 67 89', self.fonts.dd)
worksheet.write('B3', '', self.fonts.dd)
worksheet.write('C3', '', self.fonts.dd)
def _write_end(self, worksheet: Worksheet):
worksheet.write('A35', 'Konditionen:', self.fonts.tt)
worksheet.write('B35', '', self.fonts.leer)
worksheet.write('C35', 'netto', self.fonts.ttr)
worksheet.write('A36', 'zahlbar bis:', self.fonts.tt)
worksheet.write('B36', '', self.fonts.leer)
worksheet.write('C36',
(datetime.date.today() +
datetime.timedelta(365 / 12)).strftime('%d.%m.%Y'),
self.fonts.ttr_rot)
worksheet.write('A38', 'Herzlichen Dank für den geschätzten Auftrag',
self.fonts.d)
worksheet.write('A40', 'Mit freundlichen Grüssen', self.fonts.d)
worksheet.write('A41', 'Name des Betriebs', self.fonts.pr)
def _write_bill_meta_info(self, worksheet: Worksheet, bill: Bill):
worksheet.write('A16', 'Datum:', self.fonts.bg)
t = time.strftime("%d.%m.%Y")
worksheet.write('C16', t, self.fonts.d)
worksheet.write('A18', 'Rechnungperiode:', self.fonts.d)
worksheet.write('C18', bill.period_str, self.fonts.pr)
worksheet.write('A19', 'Rechnungsnummer:', self.fonts.d)
worksheet.write('C19', bill.get_bill_nbr_string(), self.fonts.pr)
def _write_table(self, worksheet: Worksheet, bill: Bill):
worksheet.write('A21', 'Anzahl', self.fonts.tt)
worksheet.write('B21', 'Beschreibung', self.fonts.tt)
worksheet.write('C21', 'Preis', self.fonts.tt)
worksheet.write('D21', 'Betrag', self.fonts.tt)
worksheet.write('A22', '', self.fonts.leer)
worksheet.write('B22', 'Eierlieferungen', self.fonts.tt),
worksheet.write('C22', '', self.fonts.leer)
worksheet.write('D22', '', self.fonts.leer)
worksheet.write('A23', bill.nbr_of_eggs, self.fonts.anzahl)
worksheet.write('B23', 'Stück', self.fonts.tt)
worksheet.write('C23', Bill.price_per_egg, self.fonts.ttr)
worksheet.write('D23', '=A23*C23', self.fonts.ttr)
for x in range(23, 29):
for y in range(0, 4):
worksheet.write(x, y, '', self.fonts.leer)
worksheet.write('C30', 'Total', self.fonts.bgu)
worksheet.write('D30', '=SUM(D23:D29)', self.fonts.total)
def _write_postal_information(self, worksheet: Worksheet, bill: Bill):
worksheet.write('A7', 'Kunde:', self.fonts.bg)
worksheet.write('C7', bill.customer_name, self.fonts.d)
worksheet.write('C8', bill.customer_address, self.fonts.d)
worksheet.write('C9', bill.customer_zip_place, self.fonts.d)
def _write_bill_header(self, worksheet: Worksheet):
worksheet.write('A13', 'Rechnung', self.fonts.rechnung)
worksheet.write('B13', '', self.fonts.dd)
worksheet.write('C13', '', self.fonts.dd)
worksheet.write('D13', '', self.fonts.dd)
def _write_camp_detail(self, detail: GeneralCamp, worksheet: Worksheet,
line: int):
if detail.campLieuPrincipal and detail.campLieuPrincipal.adresseLigne1:
worksheet.write(
line, 5, f"{detail.campLieuPrincipal.adresseLigne1}, "
f"{detail.campLieuPrincipal.tamRefCommune.codePostale if detail.campLieuPrincipal.tamRefCommune else None} "
f"{detail.campLieuPrincipal.tamRefCommune.libelle if detail.campLieuPrincipal.tamRefCommune else None}",
self._wrap_format)
if detail.camp817:
worksheet.write(line, 6, detail.camp817.previsionNbreParticipants)
worksheet.write(line, 7, detail.camp817.previsionNbreAnimateurs)
for adherent in detail.campAdherentStaffs:
if adherent.roleStaff == "D":
worksheet.write(line, 8, f"{adherent.prenom} {adherent.nom}")
worksheet.write(
line, 9, "\n".join(message.message
for message in detail.campAlertMessages
if message.level == "error"), self._wrap_format)
worksheet.write(
line, 10, "\n".join(message.message
for message in detail.campAlertMessages
if message.level == "warn"), self._wrap_format)
def _write_headers(self, worksheet: Worksheet):
bold = self._workbook.add_format({'bold': True})
for i in range(len(self.HEADERS)):
worksheet.write(0, i, self.HEADERS[i], bold)
def write_cell(self, row, col, content, worksheet: Worksheet):
cell_format = self.workbook.add_format({'border': 1})
worksheet.write(row, col, content, cell_format)
def write_row_header(self, row, col, content, worksheet: Worksheet):
row_header_format = self.workbook.add_format({'border': 1, 'bold': True })
worksheet.write(row, col, content, row_header_format)
def write_col_header(self, row, col, content, worksheet: Worksheet):
col_header_format = self.workbook.add_format({'border': 1, 'bold': True, 'align': 'center'})
worksheet.write(row, col, content, col_header_format)
