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 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 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 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 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 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 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 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_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 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 get_table(self): self.logger.debug("Checking Table") if self.table and self.table.name == self.name: self.logger.info("Table exists, returning reference") return self.table self.logger.debug("Checking ws for table") tables = self.pyxlws._tables # Check all tables to see if the one we want exists for table in tables: if table.name == "My" + self.name: self.table = table if self.table is None: if len(self.pyxlws['A']) > 1: # If it doesn't, create it self.logger.info("Table does not exist, making") style = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True) self.table = Table(displayName="My" + self.name, ref=self.pyxlws.dimensions, tableStyleInfo=style) self.pyxlws.add_table(self.table) else: self.logger.warning( "Table is to small to be created, skipping") else: # if it does, set it's dimensions to the dimensions of the work sheet self.logger.info("Table already exists, updating dimensions") self.table.ref = self.pyxlws.dimensions return self.table
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 creationFichierResultat(): #nom = input("Nom du fichier:") if p.ELECTRICITE_ET_AUTRES and p.INTRANTS_ET_FRET and p.EMBALLAGES_ET_SACHERIE and p.FRET_AVAL: nom = "COMPLET "+str(int(100000*random.random())) else: nom = "test"+str(int(100000*random.random())) nom = "Fichier Resultat - "+str(nom)+ " - "+str(p.ANNEE)+".xlsx" fichierResultat = openpyxl.Workbook() feuille_principale = fichierResultat.active feuille_principale.title= "Résultats généraux" #Tout ce qu'il y a ci après est esthétique bd_dotted = Side(style="dotted", color="000000") bd_thin = Side(style="thin", color="FF000000") #Style du fond ffe2aa styleFond = NamedStyle(name="Fond") styleFond.fill = PatternFill("solid", fgColor= "ffe2aa") fichierResultat.add_named_style(styleFond) #Style du tete de tableau styleTeteTab = NamedStyle(name="TeteTab") styleTeteTab.border = Border(bottom = bd_dotted, top= bd_dotted) styleTeteTab.alignment = Alignment(wrapText = "true",horizontal = "center") styleTeteTab.fill = PatternFill("solid", fgColor= "FFFFFF") fichierResultat.add_named_style(styleTeteTab) #Style du valeurs de tableau styleVal = NamedStyle(name="Valeurs") styleVal.border = Border(left = bd_dotted, right = bd_dotted) styleVal.fill = PatternFill("solid", fgColor= "FFF0E1") fichierResultat.add_named_style(styleVal) #Style des titres styleTitre = NamedStyle(name="Titre") styleTitre.border = Border(top=bd_dotted, bottom = bd_dotted) styleTitre.fill = PatternFill("solid", fgColor= "EBEAF5") styleTitre.alignment = Alignment(horizontal = "center", vertical="center") styleTitre.font = Font(name = 'Calibri', size =36, italic = False, bold = True, color = 'FF000000') fichierResultat.add_named_style(styleTitre) #Style des entrees de tableau styleEntree = NamedStyle(name="Entree") styleEntree.border = Border(right = bd_thin) styleEntree.fill = PatternFill("solid", fgColor= "FFFFFF") fichierResultat.add_named_style(styleEntree) #Style des tableaux en général styleTableau = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) #On applique le fond à toutes les cellules for lin in range(1,200): for col in [x for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]: case = col+str(lin) feuille_principale[case].style=styleFond for col in [x+y for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ" for y in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]: case = col+str(lin) feuille_principale[case].style=styleFond
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 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 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 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 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 export_xls(request): message = "Effettuata la chiamata in modo errato." try: if request.method == 'POST': data = urllib.parse.unquote(request.POST.get("table", '\{\}')) table = json.loads(data) workbook = Workbook() worksheet = workbook.active worksheet.title = 'Registro' worksheet.append(['Partecipante', 'Inizio presenza', 'Fine presenza', 'Tempo di partecipazione']) for cell in worksheet["1:1"]: cell.font = Font(bold=True) i = 1 for t in table['participants']: i = i + 1 duration = t['duration'].replace(" ore e ", ":").replace(" minuti", "").split(":") worksheet.append([ t['name'], datetime.strptime(t['start'].split('.', 1)[0], '%Y-%m-%dT%H:%M:%S'), datetime.strptime(t['end'].split('.', 1)[0], '%Y-%m-%dT%H:%M:%S'), time(int(duration[0]), int(duration[1]), 0) ]) cell = worksheet.cell(i, 2) cell.number_format = 'dd/mm/yyyy hh:mm' cell = worksheet.cell(i, 3) cell.number_format = 'dd/mm/yyyy hh:mm' cell = worksheet.cell(i, 4) cell.number_format = 'h "ore e" mm "minuti"' mediumStyle = TableStyleInfo(name='TableStyleMedium2', showRowStripes=True) worksheet.add_table(Table(ref='A1:D%s'%i, displayName='RegistroPresenze', tableStyleInfo=mediumStyle)) worksheet.sheet_view.showGridLines = False column_widths = [30, 20, 20, 20] for i, column_width in enumerate(column_widths): worksheet.column_dimensions[get_column_letter(i+1)].width = column_width response = HttpResponse(content=save_virtual_workbook(workbook), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename="{0}.xlsx"'.format(table['descr'].replace(':', 'h')) return response except Exception as e: message = "%s" % e print("Error: %s" % message) return JsonResponse({ "esito": False, "message": message }, status=500)
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 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 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 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')
def output_xlsx_file(parsed_rules, errors, audit_type, audit, output_file): audit_bool = audit_type[0] or audit_type[1] or audit_type[2] wb = Workbook() ws1 = wb.active ws1.title = 'Parsed' ws2 = wb.create_sheet('Errors') ws1.append(generate_headers('rules')) ws2.append(generate_headers('errors')) if audit_bool: ws3 = wb.create_sheet('Audit') ws3.append(generate_headers('audit')) parsed_count = 0 err_count = 0 audit_count = 0 for acl in parsed_rules: for entry in acl: ws1.append(entry) parsed_count += 1 for acl_err in errors: for entry_err in acl_err: ws2.append([entry_err]) err_count += 1 if audit_bool: for entry_audit in audit: ws3.append(entry_audit) audit_count += 1 style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) parsed_range = "A1:R" + str(parsed_count + 1) errors_range = "A1:A" + str(err_count + 1) if audit_bool: audit_range = "A1:D" + str(audit_count + 1) parsed_tab = Table(displayName='Parsed_Rules', ref=parsed_range, tableStyleInfo=style) errors_tab = Table(displayName='Parsing_Errors', ref=errors_range, tableStyleInfo=style) if audit_bool: audit_tab = Table(displayName='Auditing_Results', ref=audit_range, tableStyleInfo=style) ws1.add_table(parsed_tab) ws2.add_table(errors_tab) if audit_bool: ws3.add_table(audit_tab) wb.save(output_file)