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)