def excel_data_to_table(excel_output_path, sheet_name, df): # Convert data in excel file to table format wb = openpyxl.load_workbook(filename=excel_output_path) # Create table tab = Table(displayName=sheet_name, ref=f'A1:{chr(len(df.columns) + 64)}{len(df) + 1}') # Add a default style with striped rows and banded columns style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) tab.tableStyleInfo = style # Change header font color # ws = wb[sheet_name] # header_col = ws.row_dimensions[1] # header_col.font = Font(color=colors.WHITE) # Add table to sheet and save workbook wb[sheet_name].add_table(tab) wb.save(excel_output_path) wb.close()
def add_module_to_excel(workflow_db, module): wb = openpyxl.load_workbook(filename=workflow_db) if module in wb.sheetnames: print('module {} already exists. If this is an error:'.format(module)) print('1. Delete the worksheet named {}'.format(module)) print('2. Remove the workflow entry from the workflows sheet for {}'.format(module)) print('3. Remove the directory ./{} and all of its contents'.format(module)) else: ws = wb.create_sheet(module) ws.append(['Stage', 'Status', 'Function', 'Documentation']) ws.append([1, 'enabled', 'hello_world', 'Prints Hello World!!']) ws.move_range("A1:D2", rows=1, cols=1) table_name = 'control?{}?Function'.format(module) tab = Table(displayName=table_name, ref="B2:E3") style = TableStyleInfo(name="TableStyleMedium3", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style ws.add_table(tab) _workflow_sheet = wb['workflows'] for table in _workflow_sheet._tables: if table.name == 'workflow': # print(get_table_length(table)) _workflow_id = get_table_length(table) + 1 row = [_workflow_id, 'enabled', module, 'Update this workflow documentation'] add_row_to_table(_workflow_sheet, table, row) wb.save(workflow_db)
def build_table(self): column = self.column for j in range(1, self.step + 1): self.ws.column_dimensions[cell.get_column_letter(j)].width = 25 self.ws.column_dimensions['C'].width = self.ws.column_dimensions[ 'D'].width = 60 base_cell = self.ws.cell(row=self.cur_row, column=column) self.ws.merge_cells(start_row=self.cur_row, end_row=self.cur_row + 2, start_column=column, end_column=column + self.step - 1) base_cell.value = 'Event Report' base_cell.alignment = self.align_head base_cell.font = self.font_head base_cell.fill = self.fill_head for i, head in enumerate( ['Date', 'Time', *self.changes_all_event_fields]): self.ws.cell(row=self.cur_row + 3, column=column + i, value=head) for row in self.all_signal_data: self.ws.append(row) table_row_start = self.cur_row + 3 table_column_end = self.step table_row_end = len(self.all_signal_data) + table_row_start tab = Table(displayName='Report', ref="A" + str(table_row_start) + ":" + cell.get_column_letter(table_column_end) + str(table_row_end)) tab.tableStyleInfo = self.style self.ws.add_table(tab)
def _create_table(sheet, title, style='TableStyleMedium9'): """Takes a worksheet assuming there is only one table of data already present. Creates an 'Excel Table' from the max_column and max_row range giving it the passed in title. A default style is also applied and the column widths adjusted. """ # Code originally from: # https://stackoverflow.com/questions/39529662/python-automatically-adjust-width-of-an-excel-files-columns for col in sheet.columns: max_length = 0 column = col[0].column # Get the column name for cell in col: try: # Necessary to avoid error on empty cells if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = max_length + 3 if max_length < 10 else max_length sheet.column_dimensions[column].width = adjusted_width c = sheet.max_column - 1 r = sheet.max_row - 1 coords = _indices_to_coords(c,r) table = Table(displayName=title, ref=f"A1:{coords['coord']}") style = TableStyleInfo( name=style, showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False ) table.tableStyleInfo = style sheet.add_table(table)
def save_updated_excel_file(updated_list): ''' Takes original excel sheet and formats it for use with the scraped data, including adding color to 2nd column to show green "80FF80" if headshot exists. ''' thin_border = Border(bottom=Side(style='thin')) workbook = Workbook() worksheet = workbook.active worksheet.column_dimensions["B"].width = 20 worksheet.cell(3, 2).value = "Player Name" worksheet.cell(3, 3).value = "Headshot" worksheet.cell(3, 4).value = "Rank" row = 4 for line in updated_list: worksheet.cell(row, 2).value = line[0] worksheet.cell(row, 3).fill = line[1] worksheet.cell(row, 3).border = thin_border worksheet.cell(row, 4).value = line[2] row += 1 tab = Table(displayName="Table1", ref=("B3:D" + str(row-1))) style = TableStyleInfo(name="TableStyleLight8", showFirstColumn=False, showLastColumn=False, showRowStripes=False, showColumnStripes=True) tab.tableStyleInfo = style worksheet.add_table(tab) workbook.save('docs/test_changed.xlsx')
def create_worksheet(excel_workbook, headers, output_dictionary, sheet_name): """ """ sheet = excel_workbook.create_sheet(title=sheet_name) for i in range(0, len(headers)): sheet.cell(column=i + 1, row=1, value=headers[i]) row_id = 2 for key in output_dictionary.keys(): for i in range(0, len(headers)): if isinstance(output_dictionary[key][headers[i]], list): sheet.cell(column=i + 1, row=row_id, value="{0}".format(",".join( output_dictionary[key][headers[i]]))) else: sheet.cell(column=i + 1, row=row_id, value="{0}".format( output_dictionary[key][headers[i]])) row_id = row_id + 1 if len(headers) > 26: ### Super Ugly !! Fix That table_cells = "A1:A" + chr(64 + len(headers) - 26) else: table_cells = "A1:" + chr(64 + len(headers)) + str(row_id) style = TableStyleInfo(name="TableStyleMedium9", showRowStripes="True") table = Table(displayName=sheet_name, ref=table_cells) table.tableStyleInfo = style sheet.add_table(table) return excel_workbook
def list2xls(objects, attributes, path, append=False): """ 把objects的所有attributes指定的属性保存在path指定的xlsx中 :param objects: list 对象列表 :param attributes: list[str] 属性名称列表 :param path: str 保存路径 :param append: bool 是否追加 """ if append: wb = load_workbook(path) ws = wb.active if ws.title == 'sheet1' and objects: ws.title = objects[0].__class__.__name__ else: wb = Workbook() ws = wb.active ws.title = objects[0].__class__.__name__ if objects else 'sheet1' ws.append(attributes) for o in objects: ws.append([getattr(o, a, '') for a in attributes]) if objects: tab = Table(displayName=ws.title, ref="%c%d:%c%d" % (ord('A'), 1, ord('@') + ws.max_column, ws.max_row)) style = TableStyleInfo(name='TableStyleMedium9', showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style ws.add_table(tab) wb.save(path)
def export_xlsx(request): wb = Workbook() ws = wb.active ws.title = 'Sheet' all_data = _get_daily_data(_is_confirm=True) ws.append(["铁路局", "车站", "过车辆数", "报警数", "问题及处理", "问题跟踪"]) for row in all_data: ws.append([ row[9], row[1], row[2], row[3], row[4], row[5], ]) _tab_ref = "A1:F%s" % (len(all_data) + 1) tab = Table(displayName="Table1", ref=_tab_ref) style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style ws.add_table(tab) _file_name = datetime.datetime.now().strftime( '%m%d') + '接发车各站运行情况日统计表.xlsx' wb.save(_file_name) return file_download(request, _file_name)
def create_excel_table_from_df(df: pd.DataFrame, sheet_: Worksheet, row_ini: 1, table_name): """Crea tabla de Excel en la hoja indicada a partir de un pandas DataFrame. Parametros: df: pandas DataFrame row_ini: fila inicial, por default 1 sheet_: Worksheet object openpyxl table_name: nombre de la tabla""" col_last = get_excel_style(1, df.shape[1])[:-1] # Crear tabla de Excel tabla_excel = Table( displayName=table_name, ref=f"A{row_ini}:{col_last}{df.shape[0] + row_ini}") # nombre y tamaño # declarar estilos a la tabla style = TableStyleInfo(name="TableStyleMedium2", showRowStripes=False) # asignar el estilo tabla_excel.tableStyleInfo = style # agregar tabla a la hoja sheet_.add_table(tabla_excel)
def makeup_xl(file_name): """Applies some cosmetic updates to xlsx file file_name - path to excel file """ wb = load_workbook(filename=file_name) ws = wb.active # format as a table tab = Table( displayName="meTable1", ref=ws.dimensions) # ws.dimensions - dimesion with data in sheet style = TableStyleInfo(name="TableStyleLight9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) tab.tableStyleInfo = style ws.add_table(tab) # set columns width in populated range = 30 try: i = 1 for i in range(1, (ws.max_column + 1)): ws.column_dimensions[get_column_letter(i)].width = 30 except ValueError: pass wb.save(file_name) return
def writeCodesets(ws, codesets): ws.append([ "CodeSetName", "CodeSetExternalID", "Datatype", "Code", "Value", "Mneumonic", "Description", "Mapping", "CodeSetSource", "CodeSetVersion" ]) rows = 1 for codeset in codesets: #print(codeset) for codes in codeset['codes']: elaboration = '' desc = '' for text in codes['documentation']: if (text['purpose'] == "SYNOPSIS"): desc = text['text'] elif (text['purpose'] == 'ELABORATION'): elaboration = text['text'] ws.append([ codeset['name'], codeset['id'], codeset['type'], codes['value'], desc, codes['name'], elaboration, "", "FIX Trading Community", "FIXLatest" ]) rows += 1 #print (codes) codetable = Table(displayName="CodeSetTable", ref=f"A1:J{rows}") style = TableStyleInfo(name="TableStyleMedium2", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) codetable.tableStyleInfo = style ws.add_table(codetable) return
def stylizacja(plik): from openpyxl import load_workbook from openpyxl.utils import get_column_letter from openpyxl.worksheet.table import Table, TableStyleInfo lista = [ 'Detal', 'Maszyna', 'Ilość maszyn', 'Ilość sztuk na operację', 'Nr op.', 'Nazwa operacji', 'Tj', 'Norma', 'Uwagi' ] szer = 0.71 szer_lista = [ 7.29, 26.57, 13.57, 22.57, 8, 16.14, 7.29, 8.57, 16 ] work = load_workbook(plik) worksheet = work[work.sheetnames[0]] worksheet.insert_rows(0) worksheet.delete_cols(13) worksheet.delete_cols(9) worksheet.delete_cols(8) worksheet.delete_cols(1) for h in range(9): worksheet.column_dimensions[get_column_letter(h + 1)].width = \ szer_lista[h] + szer for i, col in enumerate(lista): worksheet.cell(row=1, column=i + 1).value = col # tabela rozmiar = 'A1:I' + str(len(worksheet['A'])) tab = Table(displayName='Table1', ref=rozmiar) style = TableStyleInfo( name='TableStyleMedium1', showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False ) tab.tableStyleInfo = style worksheet.add_table(tab) work.save(plik) work.close() print('Udane')
def import_data(self): df = self._process_data() workbook = load_workbook(self._save_path()) worksheet = workbook.get_sheet_by_name(self.type) workbook.remove_sheet(worksheet) # workbook.save(self._save_path()) writer = pd.ExcelWriter(self._save_path(), engine='openpyxl') writer.book = workbook writer.sheets = dict((ws.title, ws) for ws in workbook.worksheets) # workbook.create_sheet(self.type) df.to_excel(writer, sheet_name=self.type) worksheet = writer.sheets[self.type] table = Table( displayName=self.type, ref="A1:{co}{ro}".format( co=base26(df.shape[1] + 1), ro=df.shape[0] + 1)) # Add a default style with striped rows and banded columns style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) table.tableStyleInfo = style worksheet.add_table(table) writer.save()
def create_new_table(tab_range, tab_name): ''' Create a new Excel table object with given its range and name. Parameters ---------- Returns ------- ''' # Create a style for the table new_tab_style = TableStyleInfo(name="TableStyleLight15", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) # Create a new Excel table object new_tab_obj = Table(displayName="_".join(tab_name.split()), ref=tab_range) # Set the created style as the style for the new table new_tab_obj.tableStyleInfo = new_tab_style return new_tab_obj
def save_updated_excel_file(updated_list): """ Take original excel sheet and format it. For use with the scraped data, including adding color to 2nd column to show green "80FF80" if headshot exists. """ thin_border = Border(bottom=Side(style='thin'), left=Side(style='thin')) thick_border = Border(bottom=Side(style='thick')) workbook = Workbook() worksheet = workbook.active worksheet.column_dimensions["B"].width = 20 worksheet.cell(3, 2).value = "RANK" worksheet.cell(3, 3).value = "Player Name" worksheet.cell(3, 4).value = "2017" worksheet.cell(3, 5).value = "2018" worksheet.cell(3, 6).value = "2019" worksheet.cell(3, 7).value = "2020" row = 4 for line in updated_list: worksheet.cell(row, 2).value = line[0] worksheet.cell(row, 2).font = Font(bold=True, size=14.0) worksheet.cell(row, 2).alignment = Alignment(horizontal="center", shrinkToFit=True) worksheet.cell(row, 3).value = line[1] worksheet.cell(row, 3).alignment = Alignment(horizontal="left") worksheet.cell(row, 4).border = thin_border worksheet.cell(row, 4).fill = line[2] worksheet.cell(row, 5).border = thin_border worksheet.cell(row, 5).fill = line[3] worksheet.cell(row, 6).border = thin_border worksheet.cell(row, 6).fill = line[4] worksheet.cell(row, 7).border = thin_border worksheet.cell(row, 7).fill = line[5] row += 1 # column widths worksheet.column_dimensions["B"].width = 6 worksheet.column_dimensions["C"].width = 20 worksheet.column_dimensions["D"].width = 10 worksheet.column_dimensions["E"].width = 10 worksheet.column_dimensions["F"].width = 10 worksheet.column_dimensions["G"].width = 10 # thick line for the cutoff rank for i in range(8): worksheet.cell(67, i+1).border = thick_border tab = Table(displayName="Table1", ref=("B3:F" + str(row-1))) style = TableStyleInfo(name="TableStyleLight8", showFirstColumn=False, showLastColumn=False, showRowStripes=False, showColumnStripes=False) tab.tableStyleInfo = style worksheet.add_table(tab) workbook.save('OWGR.xlsx')
def setStyle(self, path="", num=0): tab = Table(displayName="Table1", ref= "A1:K{0}".format( num+1 ) ) style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style self.ws.add_table(tab) #self.wb.save("C:\\Users\\gogho\\OneDrive\\桌面\\Chrom\\test\\create_sample.xlsx") self.wb.save( path + ".xlsx" )
def table(queryset, year): columns = [ _('ref. number'), _('Company ref. number'), _('Full name, date of birth'), _('City'), _('Total price for the doctor'), ] if type_of_table == "staff": columns.append(_('Total price')) data = [] for report in queryset: row = [ report.get_full_ref_number, report.get_full_company_ref_number, ' '.join((str(report.patients_last_name), str(report.patients_first_name))) + ', ' + str(report.patients_date_of_birth.strftime("%d.%m.%Y")), report.city.name, report.get_total_price_doctor, ] if type_of_table == "staff": row.append(report.get_total_price) data.append(row) worksheet.append(columns) worksheet.column_dimensions["A"].width = 20 worksheet.column_dimensions["B"].width = 25 worksheet.column_dimensions["C"].width = 45 worksheet.column_dimensions["D"].width = 25 worksheet.column_dimensions["E"].width = 25 if type_of_table == "staff": worksheet.column_dimensions["F"].width = 25 table_ref = "A1:F" else: table_ref = "A1:E" for row in data: worksheet.append(row) tab = Table(displayName='year_' + str(year), ref=table_ref + str(len(data) + 1), totalsRowShown=True) style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) tab.tableStyleInfo = style worksheet.add_table(tab)
def post(self, request, *args, **kwargs): rids = loads(request.body)['rids'] if rids: # find all model instances with an id in rids objects = self.model.objects.filter(id__in=rids) else: # return all model instances from namespace datafilter = {'archived': False} # parent_pk is part of the batch system and if found will only fetch a subset of the model with Parent=parent_pk if 'parent_pk' in kwargs.keys(): datafilter['Parent'] = self.kwargs['parent_pk'] objects = self.model.objects.filter(**datafilter) # turn objects into a workbook data = model_to_list_list(objects) wb = Workbook() ws = wb.active ws.title = self.model.__name__ ws.append(self.model.order) for row in data: ws.append( [striptags(x) for x in row] ) # We are removing all HTML tags...THIS MAY NOT ALWAYS BE DESIRED tab = Table( displayName="Table1", ref=f"A1:{get_column_letter(len(self.model.order))}{len(data)}") style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=True, showLastColumn=False, showRowStripes=True, showColumnStripes=False) tab.tableStyleInfo = style ws.add_table(tab) # set column width per user settings up = apps.get_model('GBEX_app', 'Profile').objects.filter(user=request.user) if up: json_data = loads(up[0].table_settings) if self.model.__name__ in json_data: if 'column_widths' in json_data[self.model.__name__]: cws = json_data[self.model.__name__]['column_widths'] for i, col_name in enumerate(self.model.order): if col_name in cws: ws.column_dimensions[get_column_letter( i + 1)].width = units.pixels_to_points( cws[col_name]) / 6 response = HttpResponse( save_virtual_workbook(wb), content_type= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response['Content-Disposition'] = 'attachment; filename="export.xlsx"' return response
def saveReports(): # Add a default style with striped rows and banded columns style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) count = 0 for row in ws.iter_rows(row_offset=1): count += 1 tab = Table(displayName="Table1", ref="A1:W" + str(count)) tab.tableStyleInfo = style ws.add_table(tab) count = 0 for row in ws_buy.iter_rows(row_offset=1): count += 1 tab = Table(displayName="Table1", ref="A1:W" + str(count)) tab.tableStyleInfo = style ws_buy.add_table(tab) count = 0 for row in ws_sell.iter_rows(row_offset=1): count += 1 tab = Table(displayName="Table1", ref="A1:W" + str(count)) tab.tableStyleInfo = style ws_sell.add_table(tab) count = 0 for row in ws_buyAll.iter_rows(row_offset=1): count += 1 tab = Table(displayName="Table1", ref="A1:W" + str(count)) tab.tableStyleInfo = style ws_buyAll.add_table(tab) count = 0 for row in ws_sellAll.iter_rows(row_offset=1): count += 1 tab = Table(displayName="Table1", ref="A1:W" + str(count)) tab.tableStyleInfo = style ws_sellAll.add_table(tab) wb.save(logname + ".xlsx")
def add_table_to_worksheet(_work_sheet, name, data, first_row_is_header=True, table_style='TableStyleMedium2', row_offset=2, col_offset=1): """ Add a list of dictionaries (rows) as an Excel table. By default the first row is the header. The dictionaries are list of key:value pairs constituting each row of the table. Args: _work_sheet: (object) An openpyxl ws object (this must be changes to ws name only) name: (string) The name of the table. Must be globally unique within the Excel workbook data: (list) Table data as a list of dictionaries with each element constituting a row of the table first_row_is_header: (bool) True (default) or False. Uses the first element of data as table headers table_style: (sting) An Excel table style row_offset: (int) Integer to determine number of spacer rows to add col_offset: (int) Integer to determine number of spacer columns to add Returns: Nothing """ if first_row_is_header: column_headers = data.pop(0) else: print('first_row_is_header=False is currently not supported') exit() _start_table_data = _new_table_setup(_work_sheet, column_headers, row_offset=row_offset, col_offset=col_offset) new_table_end_col = _start_table_data[0] new_table_start_row = _start_table_data[1] last_data_row = _add_table_data(_work_sheet, column_headers, data, col_offset=col_offset) # Calculate new data refs and insert table _t_scol = _get_cell_column_letter(1 + col_offset) _t_ecol = _get_cell_column_letter(new_table_end_col) _scoord = '{}{}'.format(_t_scol, new_table_start_row) _ecoord = '{}{}'.format(_t_ecol, last_data_row) _t_ref = '{}:{}'.format(_scoord, _ecoord) tab = Table(displayName=name, ref=_t_ref) style = TableStyleInfo(name=table_style, showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) tab.tableStyleInfo = style _work_sheet.add_table(tab)
def tableCreate(tableName, pos): #pos=位置 e.g.A1:B5 tab = Table(displayName="tableName", ref=pos) # ref is the table range style = TableStyleInfo( name="TableStyleMedium9", showFirstColumn=False, # the style of table showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style # apply the style to table ws.add_table(tab) # add table to word sheet
def create_table(ws, display_name, range): tab = Table(displayName=display_name, ref=range) tab.headerRowCount = 1 style = TableStyleInfo(name="TableStyleMedium2", showFirstColumn=False, showLastColumn=False, showRowStripes=False, showColumnStripes=False) tab.tableStyleInfo = style ws.add_table(tab)
def entetes_resultats_generaux(usines:list, nom_fichier:str): styleFond, styleTeteTab, styleVal, styleTableau, styleEntree, styleTitre = p.STYLES_EXCEL ligne_depart = 10 try: document = openpyxl.load_workbook(p.CHEMIN_ECRITURE_RESULTATS+nom_fichier) except FileNotFoundError: fonctionsMatrices.print_log_erreur("Le document résultat n'est pas trouvé à l'emplacement "+p.CHEMIN_ECRITURE_RESULTATS+nom_fichier, inspect.stack()[0][3]) sys.exit("Le document résultat n'est pas trouvé à l'emplacement "+ p.CHEMIN_ECRITURE_RESULTATS+nom_fichier) feuilleprinc = document.get_sheet_by_name(document.get_sheet_names()[0]) feuilleprinc.column_dimensions['B'].width = 24 feuilleprinc.column_dimensions['C'].width = 0 feuilleprinc.column_dimensions['D'].width = 20 feuilleprinc.column_dimensions['E'].width = 10 FretAmont = ["MP bateau", "MP route"] FretAval = ["Ventes pro","Ventes Jardineries", "Interdepot"] Intrants = ["Matières Premières", "CO2 issu de la tourbe"] HorsEnergie = ["Protoxyde d'azote"] Energie = ["Electricité", "Fuel"] Emballages = ["PE neuf", "PE recyclé", "PVC", "Carton"] entetes = FretAmont+FretAval+Intrants+HorsEnergie+Energie+Emballages colonnes = ["Poste d'émission", "Site", "Empreinte carbone (tCO2e)","", "Emission par m3 (kgCO2e/m3)", ""] for i in range(len(colonnes)): feuilleprinc.cell(row = ligne_depart, column = i+2).value = colonnes[i] feuilleprinc.cell(row=ligne_depart, column=i+2).style = styleTeteTab i = 0 for poste in entetes: for j in range(len(usines)): feuilleprinc["B"+str(i+ligne_depart+1)] = poste feuilleprinc["C"+str(i+ligne_depart+1)] = str(usines[j][0]) + " " + usines[j][1] i+=1 alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" tableau = Table(displayName = "Resultats_principaux", ref = "B"+str(ligne_depart)+":"+alphabet[len(entetes)]+str(ligne_depart+i)) tableau.tableStyleInfo = styleTableau production_totale = sum([x[1]for x in p.PROD_PAR_SITE]) feuilleprinc['C7'] = "Total:" feuilleprinc['D7'] = "=TEXT(SUM(D11:D"+str(199)+"), \"# ##0\")&+\" tCO2e\"" feuilleprinc['E7'] = "et" feuilleprinc['F7'] = "=TEXT(1000*SUM(D11:D"+str(199)+")/"+str(production_totale)+", \"# ##0,0\")&+\" kgCO2e/m3\"" # rouge = openpyxl.styles.colors.Color(rgb='00FF0000') for c in "CDEF": feuilleprinc[c+"7"].font = Font(size=18, bold=True) #Si on a la possibilité, on sauvegarde try: document.save(p.CHEMIN_ECRITURE_RESULTATS+nom_fichier) except PermissionError: #Soit à cause du fichier réseau, soit parce que l'excel est ouvert ailleurs fonctionsMatrices.print_log_erreur("Permissions insuffisantes pour enregistrer le fichier résultat", inspect.stack()[0][3]) except FileNotFoundError: #Emplacement inexistant fonctionsMatrices.print_log_erreur("Emplacement d'écriture introuvable: "+str(p.CHEMIN_ECRITURE_RESULTATS + nom_fichier), inspect.stack()[0][3])
def final_report(self): ''' This Class will Take the data from the other classes and create a Final Sheet to be used for the Reports. It will Write the Data needed in the current iteration of the Reports and append it to the Sheet. Then it will make the Data appear as a Table in the very last Excel Sheet. ''' ws = self.wb_wt.create_sheet("DTO Report", 4) ws = self.wb_wt.worksheets[4] wt4 = self.wb_sap.worksheets[4] for rows in wt4.iter_rows( ): # This Will Append the Previous Data from any Other Reports ws.append([cell.value for cell in rows]) refs = ws.max_row # This Establishes the Length of that Data # Deletes the Summations from Prev. Reports ws.delete_rows(refs, refs + 1) currentDT = datetime.datetime.now() # Current Data # Variable Data is the data that get written to the Report data = [[ currentDT.strftime("%m/%d/%Y"), (self.dto["F9"] + self.dto["F9A"] + self.dto["F9B"] + self.dto["F9X"]), self.dto["F11A"], (self.dto["Insourced"] + self.dto["Tooling"] + self.dto["RCI"]), self.dto["7RMY20"], self.dto["LYNX"], self.dto["None"], (self.dto["Legacy"] + self.dto["Pre-IT4"] + self.dto["Multiple"] + self.dto["Aeros"]), self.total_dto, self.NP, self.P, self.me_approval, self.x_issued, self.t_issued, self.XT, (self.dto["Maximus"] + self.dto["Saturn"] + self.dto["Isis"]), 0, 0, 0, self.costhr ]] for row in data: # Appends the Data Row to the Report ws.append(row) tablelength = ws.max_row # Determines the Length of the Table w/ the Data now # Creates Dimensions for the Table based on that Length tableref = "A1:U" + str(tablelength) # Creates the Table with a Display Name and the ref. Ref is the prev. Line tab = Table(displayName="DTOTable", ref=tableref) style = TableStyleInfo(name="TableStyleDark2", showFirstColumn=True, showLastColumn=True, showRowStripes=True, showColumnStripes=True) # Sets Table Style based on the Table Style information parsed in the two lines above. tab.tableStyleInfo = style ws.add_table(tab) # Finally Enables the Table to appear # Theese Lines Write the Summations for the Xiss, Tiss, X->T ws['M' + str(tablelength + 1)] = "=SUM(M2:M" + str(tablelength) + ")" ws['N' + str(tablelength + 1)] = "=SUM(N2:N" + str(tablelength) + ")" ws['O' + str(tablelength + 1)] = "=SUM(O2:O" + str(tablelength) + ")" # Style of Page Below ws.page_setup.fitToHeight = 0 ws.page_setup.fitToWidth = 1 i = 0 cols = ws.max_column
def write_xlsx(header, file_name, query, conn_str): ''' Writes the results of the query to an .xlsx file, the contents is stored in a table, and has the columns width expanded for ease of use and makes it easier to read. ''' print file_name dest_filename = file_name.replace( '.sql', '_' + time.strftime('%m-%d-%y_%H%M') + '.xlsx') wb = Workbook() ws = wb.active conn = pyodbc.connect(conn_str) result = conn.execute(query).fetchall() if result: # add column header to top of excel list ws.append(header.split(',')) # generic list, used to write contents pulled from database to the excel file lst = [] # loop through contents pulled from database and write to excel list for row in result: convert_str = [] lst = list(row) for item in lst: new_item = str(item) new_item = new_item.replace( '\x1f', '') # \x1f breaks writing xlsx files with tables new_item = new_item.replace('None', '') convert_str.append(new_item) ws.append(convert_str) # Expand all Columns to match Larges Text in column for column_cells in ws.columns: length = max(len(str(cell.value)) for cell in column_cells) * 1.3 ws.column_dimensions[column_cells[0].column].width = length # Add table formatting to the Excel List, Only work to letter Z # breaks if query is larger than 26 columns end_column = NUM_TO_ALPHA[len(list(ws.rows)[0])] + str( len(list(ws.rows))) tab = Table(displayName='Table', ref='A1:' + end_column) style = TableStyleInfo(name="TableStyleLight11", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style ws.add_table(tab) # write the file wb.save(dest_filename) wb.close() return dest_filename
def final_report(self): ''' This Class will Take the data from the other classes and create a Final Sheet to be used for the Reports. It will Write the Data needed in the current iteration of the Reports and append it to the Sheet. Then it will make the Data appear as a Table in the very last Excel Sheet. ''' ws = self.wb_wt.create_sheet("Final Report", 4) ws = self.wb_wt.worksheets[4] wt4 = self.wb_sap.worksheets[4] for rows in wt4.iter_rows( ): #This Will Append the Previous Data from any Other Reports ws.append([cell.value for cell in rows]) refs = ws.max_row #This Establishes the Length of that Data ws.delete_rows(refs, refs + 1) #Deletes the Summations from Prev. Reports currentDT = datetime.datetime.now() #Current Data #Variable Data is the data that get written to the Report data = [[ currentDT.strftime("%Y/%m/%d"), self.fnine, self.feleven, (self.ins + self.tooling + self.rci), self.sevenr, self.lynx, self.new, (self.pre + self.saturn + self.maxim + self.legacy + self.aeros + self.isis), self.total, self.NP, self.P, self.me_approval, self.x_issued, self.t_issued, self.XT, 0, 0, 0, 0, 0, self.costhr ]] for row in data: #Appends the Data Row to the Report ws.append(row) tablelength = ws.max_row #Determines the Length of the Table w/ the Data now tableref = "A1:U" + str( tablelength ) #Creates Dimensions for the Table based on that Length tab = Table( displayName="Data", ref=tableref ) #Creates the Table with a Display Name and the ref. Ref is the prev. Line style = TableStyleInfo(name="TableStyleDark1", showFirstColumn=True, showLastColumn=True, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style #Sets Table Style based on the Table Style information parsed in the two lines above. ws.add_table(tab) #Finally Enables the Table to appear #Theese Lines Write the Summations for the Xiss, Tiss, X->T ws['M' + str(tablelength + 1)] = "=SUM(M2:M" + str(tablelength) + ")" ws['N' + str(tablelength + 1)] = "=SUM(N2:N" + str(tablelength) + ")" ws['O' + str(tablelength + 1)] = "=SUM(O2:O" + str(tablelength) + ")" #Style of Page Below ws.page_setup.fitToHeight = 0 ws.page_setup.fitToWidth = 1 i = 0 cols = ws.max_column for i in range(cols + 1): ws[chr(65 + i) + "1"].alignment = Alignment(wrap_text=True)
def saveReports(): tab = Table(displayName="Table1", ref="A1:C503") # Add a default style with striped rows and banded columns style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style ws.add_table(tab) wb.save(logname + ".xlsx")
def build_table(self, index): column = self.column for j in range(1, self.step + 1): self.ws.column_dimensions[cell.get_column_letter(j)].width = 25 base_cell = self.ws.cell(row=self.cur_row, column=column) self.ws.merge_cells(start_row=self.cur_row, end_row=self.cur_row + 2, start_column=column, end_column=column + self.step - 1) base_cell.value = self.object_fullpaths[index] base_cell.alignment = self.align_head if index % 2: base_cell.font = self.alt_font_head base_cell.fill = self.alt_fill_head else: base_cell.font = self.font_head base_cell.fill = self.fill_head fields = { 6: self.changes_mv_fields, 7: self.consumption_mt_fields, 9: self.average_mv_fields }.get(self.step) for i, head in enumerate(['Date', 'Time', *fields]): self.ws.cell(row=self.cur_row + 3, column=column + i, value=head) for row in self.all_signal_data[index]: self.ws.append(row) table_row_start = self.cur_row + 3 table_column_end = self.step table_row_end = len(self.all_signal_data[index]) + table_row_start tab = Table(displayName='Report' + str(index), ref="A" + str(table_row_start) + ":" + cell.get_column_letter(table_column_end) + str(table_row_end)) tab.tableStyleInfo = self.style self.ws.add_table(tab) if self.signal_type in ['All Measurements', 'All Metering']: if self.data_type in ['Average', 'Consumption']: self.build_chart(min_col=6, max_col=7, min_row=table_row_start, max_row=table_row_end) else: self.build_chart(min_col=self.step, min_row=table_row_start, max_row=table_row_end) if self.template_path: self.ws = self.wb.copy_worksheet(self.ws_template) self.ws.title = 'Report-' + str(self.sheet_index) self.sheet_index += 1 else: self.ws = self.wb.create_sheet() self.cur_row = self.ws.max_row + 2
def xexport_dynamic_data(dict_data): from openpyxl import Workbook from openpyxl.worksheet.table import Table, TableStyleInfo wb = Workbook() ws = wb.active organization = Organization.get(id=1) file_name = "{}.xlsx".format(dict_data.get("file_name")) headers = dict_data.get("headers") sheet_name = str(dict_data.get("sheet")) title = str(dict_data.get("title")) data = dict_data.get("data") widths = dict_data.get("widths") date_ = str(dict_data.get("date")) extend_rows = dict_data.get("extend_rows") others = dict_data.get("others") footers = dict_data.get("footers") exclude_row = dict_data.get("exclude_row") format_money = dict_data.get("format_money") # add column headings. NB. these must be strings ws.append(headers) for row in data: print(row) ws.append(row) dict_alph = { 1: "A", 2: "C", 3: "D", 4: "E", 5: "F", 6: "G", 7: "H", 8: "I", } REF = "A1:{}{}".format(dict_alph.get(len(headers)), len(data) + 1) print(REF) tab = Table(displayName="Table1", ref=REF) # Add a default style with striped rows and banded columns style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style ws.add_table(tab) wb.save(file_name) try: wb.close() openFile(file_name) except Exception as e: print(e)
def addtable(df, ws): nrow, ncol = df.shape title= ws.title print(title) # get_column_letter(27) tab = Table(displayName= title, ref='A1:{col}{row}'.format(col= get_column_letter(ncol), row= nrow + 1)) # Add a default style with striped rows and banded columns style = TableStyleInfo(name="TableStyleLight8") tab.tableStyleInfo = style ws.add_table(tab) return ws
def get_list(): # http://openpyxl.readthedocs.io/en/default/pandas.html# path = 'data/sample.txt' df = pd.read_csv(path, sep='\t') df['注文日'] = pd.to_datetime(df['注文日']) wb = Workbook() ws = wb.get_active_sheet() ws.append([None, None, None, '商品受注リスト']) ws.append([ None, None, None, None, None, None, '作成日: ' + datetime.date.today().strftime('%Y-%m-%d') ]) for r in dataframe_to_rows(df, index=False, header=True): ws.append(r) begin_row = 3 end_row = len(df.index) + begin_row ws['D1'].font = Font(size=20) ws['D1'].alignment = Alignment(horizontal='center') ws['G2'].alignment = Alignment(horizontal='right') for cell in ws['A:A']: cell.number_format = 'YYYY/MM/DD' for cell in ws['G:G']: cell.number_format = '#,##0' ws.column_dimensions['A'].width = 12 ws.column_dimensions['B'].width = 12 ws.column_dimensions['C'].width = 12 ws.column_dimensions['D'].width = 12 ws.column_dimensions['E'].width = 12 ws.column_dimensions['F'].width = 12 ws.column_dimensions['G'].width = 12 # http://openpyxl.readthedocs.io/en/default/worksheet_tables.html tab = Table(displayName="Table1", ref="A" + str(begin_row) + ":G" + str(end_row)) # Add a default style with striped rows and banded columns style = TableStyleInfo(name="TableStyleLight15", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) tab.tableStyleInfo = style ws.add_table(tab) wb.save('data/一覧.xlsx')
""" 创建Excel文件 Version: 0.1 Author: 骆昊 Date: 2018-03-26 """ from openpyxl import Workbook from openpyxl.worksheet.table import Table, TableStyleInfo workbook = Workbook() sheet = workbook.active data = [ [1001, '白元芳', '男', '13123456789'], [1002, '白洁', '女', '13233445566'] ] sheet.append(['学号', '姓名', '性别', '电话']) for row in data: sheet.append(row) tab = Table(displayName="Table1", ref="A1:E5") tab.tableStyleInfo = TableStyleInfo( name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) sheet.add_table(tab) workbook.save('./res/全班学生数据.xlsx')
['Pears', 2000, 3000, 4000, 5000], ['Bananas', 6000, 6000, 6500, 6000], ['Oranges', 500, 300, 200, 700], ] # add column headings. NB. these must be strings ws.append(["Fruit", "2011", "2012", "2013", "2014"]) for row in data: ws.append(row) tab = Table(displayName="Table1", ref="A1:E5") # Add a default style with striped rows and banded columns style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style ws.add_table(tab) # wb.save("table.xlsx") # #Accessing a range called “my_range”: # my_range = wb.defined_names['my_range'] # # if this contains a range of cells then the destinations attribute is not None # dests = my_range.destinations # returns a generator of (worksheet title, cell range) tuples # cells = [] # for title, coord in dests: # ws = wb[title] # cells.append(ws[coord]) # wb.save("table.xlsx")