def do_panel_output(args): #import schema_data.inputs as schema_data_inputs _validator = get_validator(files('schema_data.inputs').joinpath('panel.json')) _schema = _validator.schema wb = Workbook() default_names = wb.sheetnames wb.add_named_style(highlight) _oname = args.panel_output # Start with the Metadata. Write the header and the value names ws1 = wb.create_sheet(_schema['properties']['parameters']['title']) _write_parameters(ws1,_schema['properties']['parameters']) _fix_width(ws1) # Now lets make the Panel. Write the header only. ws2 = wb.create_sheet(_schema['properties']['markers']['title']) _write_repeating(ws2,_schema['properties']['markers']) _fix_width(ws2) # cleanup workbook deleting default sheet name for _sheet_name in default_names: #print(_sheet_name) del wb[_sheet_name] wb.save(filename = _oname)
def main(): parser = argparse.ArgumentParser() parser.add_argument("target_dir") args = parser.parse_args() target_dir = args.target_dir file_list = [(f.name, f.stat()) for f in os.scandir(target_dir)] content_list = [(f[0], format_size(f[1].st_size), format_datetime(f[1].st_ctime), format_datetime(f[1].st_mtime)) for f in file_list] content_list.sort(key=lambda a: a[0]) excelpath = os.path.join(target_dir, "Directory List.xlsx") wb = Workbook() ws = wb.active ws.title = os.path.split(target_dir)[1] ws.page_setup.fitToWidth = 1 ws.append(("Directory",)) ws.append((target_dir,)) ws.append(()) ws.append(("Name", "(Size (KB)", "Date Created", "Date Modified")) date_style = NamedStyle(name='date_style', number_format='DD/MM/YYYY HH:MM:MM') wb.add_named_style(date_style) for i in ('A', 'B', 'C', 'D'): ws.column_dimensions[i].width = '20' for i in range(len(content_list)): for j in range(4): cell = ws.cell(i+5, j+1) cell.value = content_list[i][j] wb.save(excelpath)
def do_report_output(output_path): _validator = get_validator(files('schema_data.inputs').joinpath('report_definition.json')) _schema = _validator.schema wb = Workbook() default_names = wb.sheetnames wb.add_named_style(highlight) # Start with the Metadata. Write the header and the value names ws0 = wb.create_sheet(_schema['properties']['parameters']['title']) _write_parameters(ws0,_schema['properties']['parameters']) _fix_width(ws0) ws1 = wb.create_sheet(_schema['properties']['population_percentages']['title']) _write_repeating(ws1,_schema['properties']['population_percentages']) _fix_width(ws1) ws2 = wb.create_sheet(_schema['properties']['population_densities']['title']) _write_repeating(ws2,_schema['properties']['population_densities']) _fix_width(ws2) # cleanup workbook deleting default sheet name for _sheet_name in default_names: #print(_sheet_name) del wb[_sheet_name] wb.save(filename = output_path) return
def join_Database(): xlsx_to_formated_xlsx = 'media/qbreports/uploads/xlsx_to_formated_xlsx.xlsx' downlaod_file_name = 'media/qbreports/uploads/custom_qb_report.xlsx' joined_table_file_name = pandas_df_join() csv_to_xlsx, google_sheet = finial_file_creation(joined_table_file_name) dfUpdated = pd.read_csv(csv_to_xlsx, encoding='latin-1') dfgdoc = pd.read_csv(google_sheet, encoding='latin-1') print("creating xlsx files") wb = Workbook() ws = wb.active ws.title = 'Matching_Updated' ws2 = wb.create_sheet() ws2.title = 'Vendor Price Check Gdoc' bold = NamedStyle(name='bold') bold.font = Font(bold=True) wb.add_named_style(bold) cell = WriteOnlyCell(ws) cell.style = 'bold' greenFill = PatternFill(start_color='32CD32', end_color='32CD32', fill_type='solid') for row in dataframe_to_rows(dfUpdated, index=False, header=True): ws.append(row) for row in dataframe_to_rows(dfgdoc, index=False, header=True): ws2.append(row) wb.save(xlsx_to_formated_xlsx) wb = load_workbook(filename=xlsx_to_formated_xlsx) ws = wb.active for row in ws.iter_rows(): if row[1].value == "Vendor Name": for cell in row: ws[str(cell.coordinate)].font = Font(bold=True) if row[1].value == None: for cell in row: if cell.value != None: ws[str(cell.coordinate)].fill = greenFill if row[1].value != None: for cell in row: ws[str(cell.coordinate)].font = Font(bold=True) wb.save(downlaod_file_name) print("created xlsx files") qb_Import_File_delete_everything() return downlaod_file_name
def do_project_folder_output(output_file): # For now lets keep this with InForm only _validator = get_validator(files('schema_data.inputs.platforms.InForm').joinpath('project.json')) _schema = _validator.schema wb = Workbook() default_names = wb.sheetnames wb.add_named_style(highlight) # Start with the Metadata. Write the header and the value names ws1 = wb.create_sheet(_schema['properties']['parameters']['title']) _write_parameters(ws1,_schema['properties']['parameters']) _fix_width(ws1) # Now lets make the Panel. Write the header only. ws2 = wb.create_sheet(_schema['properties']['samples']['title']) _write_repeating(ws2,_schema['properties']['samples']) _fix_width(ws2) # cleanup workbook deleting default sheet name for _sheet_name in default_names: #print(_sheet_name) del wb[_sheet_name] wb.save(filename = output_file)
def write_to_spreadsheet(self): '''Formats and writes data to spreadsheet.''' wb = Workbook() highlight = NamedStyle(name="highlight") highlight.font = Font(bold=False, size=11) bd = Side(style='thin', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) wb.add_named_style(highlight) # Register named style sh1 = wb.active sh1.title = 'Purchasing Exceptions' sh1.append(['Part Number']) sh1['A1'].font = Font(bold=True, size=11) sh1['A1'].border = Border(left=bd, top=bd, right=bd, bottom=bd) sh1['A1'].alignment = Alignment(horizontal='center') rnum = 2 sh1.column_dimensions['A'].width = (30) # col_width2 = 0 for item in self.part_list: # sh1_tool_list_data = item sh1.cell(row=rnum, column=1).value = item sh1.cell(row=rnum, column=1).style = 'highlight' # if len(str(sh1_description)) > col_width2: # col_width2 = len(str(sh1_description)) rnum += 1 # sh1.column_dimensions['A'].width = (col_width2 * 1.125) save_name = (('{}/Purchasing Exception Report.xlsx').format(self.idir)) self.saved_as_string = ('Results file: {}').format(save_name) self.file_listbox.insert(tk.END, self.count_string) self.file_listbox.insert(tk.END, self.saved_as_string) self.file_listbox.see(tk.END) wb.save(save_name) os.startfile(save_name)
def export_xlsx(self, filename): um = self.units.description data = [(i+1,v,um) for (i,v) in enumerate(self.values)] wb = Workbook(write_only = True) ws = wb.create_sheet() # create some styles cellstyle = NamedStyle(name="highlight") headerstyle = NamedStyle(name='headercell') wb.add_named_style(cellstyle) wb.add_named_style(headerstyle) cellstyle.font = Font(name='Calibri', size=11) headerstyle.font = Font(name='Calibri', size=11, bold=True) bd = Side(border_style='thin') cellstyle.border = Border(bottom=bd, right=bd, top=bd, left=bd) headerstyle.border = Border(bottom=bd, right=bd, top=bd, left=bd) header_labels = ['#', 'Value', 'Units'] # write header header = [] for el in header_labels: cell = WriteOnlyCell(ws, value=el) cell.style = 'headercell' header.append(cell) ws.append(header) # write data for t in data: row = [] for el in t: cell = WriteOnlyCell(ws, value=el) cell.style = 'highlight' row.append(cell) ws.append(row) wb.save(filename) # doctest: +SKIP
def generar_codigos_excel(self): response = HttpResponse( content_type= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', ) response[ 'Content-Disposition'] = 'attachment; filename={date}-codigos.xlsx'.format( date=datetime.now().strftime('%Y%m%d'), ) titulo = NamedStyle(name="titulo") titulo.font = Font(bold=True, size=10, color='ffffff') titulo.alignment = Alignment( horizontal="center", vertical="center", wrap_text=True, ) titulo.fill = PatternFill( start_color='244062', end_color='244062', fill_type='solid', ) wb = Workbook() wb.add_named_style(titulo) sheet = wb.active column_dimensions = sheet.column_dimensions['A'] column_dimensions.width = 20 column_dimensions = sheet.column_dimensions['B'] column_dimensions.width = 20 column_dimensions = sheet.column_dimensions['C'] column_dimensions.width = 20 column_dimensions = sheet.column_dimensions['D'] column_dimensions.width = 20 column_dimensions = sheet.column_dimensions['E'] column_dimensions.width = 20 sheet['A1'] = "CODIGO PRINCIPAL" sheet['A1'].style = titulo sheet['B1'] = "CODIGO EXTERNO" sheet['B1'].style = titulo sheet['C1'] = "CODIGO PROVEEDOR" sheet['C1'].style = titulo sheet['D1'] = "CODIGO TESTIGO" sheet['D1'].style = titulo sheet['E1'] = "CODIGO BOLSA" sheet['E1'].style = titulo for codigo in self.__codigos: sheet.append( (codigo.numero, codigo.cod_externo, codigo.cod_proveedor, codigo.cod_testigo, codigo.cod_bolsa)) wb.save(response) return response
def create_template(template_path, mylogger): logger = mylogger logger.info("Reading and validating panel.json") _validator1 = get_validator(files('schemas').joinpath('panel.json')) _schema1 = _validator1.schema logger.info("Reading and validating samples.json") _validator2 = get_validator(files('schemas').joinpath('samples.json')) _schema2 = _validator2.schema logger.info("Reading and validating pipeline.json") _validator3 = get_validator(files('schemas').joinpath('pipeline.json')) _schema3 = _validator3.schema logger.info("Creating the Workbook") wb = Workbook() default_names = wb.sheetnames wb.add_named_style(highlight) logger.info("Start with Panel Metadata") ws0 = wb.create_sheet("Panel Parameters") _write_parameters(ws0, [_schema1['properties']['parameters']]) _fix_width(ws0) logger.info("Set the panel definition") ws1 = wb.create_sheet("Panel Definition") _write_repeating(ws1, _schema1['properties']['markers']) _fix_width(ws1) logger.info( "Create the sample manifest and add some example annotation columns") example_annotations = ["Batch", "Timepoint", "Response"] ws2 = wb.create_sheet("Sample Manifest") ncols = _write_repeating(ws2, _schema2['properties']['samples'], exclude=['sample_annotations']) _write_annotations(ws2, example_annotations, start_idx=ncols) _fix_width(ws2) logger.info("Make a table to define the sample annotations") ws3 = wb.create_sheet("Sample Annotations") _write_repeating(ws3, _schema2['definitions']['annotation_level']) _fix_width(ws3) logger.info("End with the pipeline version") ws4 = wb.create_sheet("Meta") _write_parameters(ws4, [_schema3]) _fix_width(ws4) # Manually set the version ws4.cell(row=2, column=2).value = get_version() # cleanup workbook deleting default sheet name for _sheet_name in default_names: del wb[_sheet_name] wb.save(filename=template_path) return
def csv_to_xlsx(export_datas): wb = Workbook() my_style = NamedStyle(name="style") my_style.border = Border(left=Side(style='dashed'), right=Side(style='dashed'), top=Side(style='dashed'), bottom=Side(style='dashed')) my_style.font = Font(name='맑은 고딕', size=11, bold=False, italic=False, underline='none', strike=False, color='FF000000') wb.add_named_style(my_style) for export_data in export_datas: print(f"export_data:{export_data}") page = str(export_data['page']) index = str(export_data['index']) sheet_name = f'page-{page.zfill(4)}-table-{index.zfill(2)}' export_data['sheet_name'] = sheet_name ws = wb.create_sheet() ws.title = sheet_name with open(export_data['csv_path'], 'r', encoding='utf8') as f: next(f) for row in csv.reader(f): ws.append(row) cols = ws.max_column rows = ws.max_row header_fill = PatternFill("solid", fgColor="667b68") body_fill = PatternFill("solid", fgColor="f7f9f1") for row in ws.iter_rows(min_row=1, max_row=rows, min_col=1, max_col=cols): for cell in row: cell.style = my_style if (cell.row > 1): cell.fill = body_fill else: cell.fill = header_fill cell.font = Font(b=True, color="ead253") output_path = os.path.dirname(export_datas[0]['csv_path']) output_path += f'\{os.path.basename(output_path)}.xlsx' wb.save(output_path) wb.close() process_table(output_path, export_datas)
def write_xlsx(xlsxname, nameset): wb = Workbook() wb.add_named_style(colhead) # Use first work sheet named 'All' to collect all patches from all banks ws_all = wb.active ws_all.title = "All" ws_all_row = 1 ws_all.freeze_panes = "A2" # Set column header styles for 'All' worksheet for col, (value, width, headstyle, _) in COLUMNS_ALL.items(): ws_all[col + "1"] = value ws_all[col + "1"].style = headstyle ws_all.column_dimensions[col].width = width # Create one work sheet for each patch bank for pbnum, patchbank in enumerate(nameset): if not patchbank.patchlist: continue ws = wb.create_sheet(title=sanitize(patchbank.name)) ws.freeze_panes = "A2" # Set column header styles for patch bank worksheet for col, (value, width, headstyle, _) in COLUMNS_BANK.items(): ws[col + "1"] = value ws[col + "1"].style = headstyle ws.column_dimensions[col].width = width for row, patch in enumerate(patchbank.patchlist, start=2): if patchbank.name != "GM": ws_all.append((patchbank.name, patchbank.msb, patchbank.lsb, patch.program, patch.category, patch.name)) ws_all_row += 1 # Set cell styles for row in 'All' work sheet for column in COLUMNS_ALL: ws_all["%s%i" % (column, ws_all_row)].style = COLUMNS_ALL[column][3] ws.append((patch.program, patch.category, patch.name)) # Set cell styles for row in patch bank work sheet for column in COLUMNS_BANK: ws["%s%i" % (column, row)].style = COLUMNS_BANK[column][3] # Set auto filter for 'All' work sheet ws_all.auto_filter.ref = ws_all.dimensions log.info("Writing '%s'...", xlsxname) wb.save(xlsxname)
def healthcheckreport(healthchecklist, exportlocation): wb = Workbook() dest_filename = 'Health-Check-Report.xlsx' dest_path = exportlocation + '\\' + dest_filename ws1 = wb.active # Continue on with work ws1.title = "Health Check" ws1.append(['Hostname', 'Error', 'Description']) startrow = 2 for row in healthchecklist: ws1['A' + str(startrow)] = row.get('Hostname') ws1['B' + str(startrow)] = row.get('Error') ws1['C' + str(startrow)] = row.get('Description') startrow = startrow + 1 wb.add_named_style(HeaderStyle) # Set styles on header row for cell in ws1["1:1"]: cell.style = 'BoldHeader' # Set Column Width for col in ws1.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 + 2) * 1.2 ws1.column_dimensions[column].width = adjusted_width # Save File try: wb.save(filename=dest_path) except: print 'Error creating the report: ' + dest_path + '. File might be currently in use.' return # Sorting try: excel = win32com.client.Dispatch("Excel.Application") wb = excel.Workbooks.Open(dest_path) ws = wb.Worksheets('Health Check') ws.Range('A2:C50000').Sort(Key1=ws.Range('A1'), Order1=1, Orientation=1) ws.Range('A1:C1').AutoFilter(1) wb.Save() excel.Application.Quit() except: # Throw no error pass print 'Successfully created Health Check Report'
def _generate_spreadsheet_data(cls, request, out, report, *args, **kwargs): # Create a workbook wb = Workbook(write_only=True) ws = wb.create_sheet(title=report.name) # Create a named style for the header row readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle") readlonlyheaderstyle.fill = PatternFill(fill_type="solid", fgColor="d0ebfb") wb.add_named_style(readlonlyheaderstyle) # Run the query conn = None try: conn = create_connection(request.database) comment = CellComment(force_text(_("Read only")), "Author", height=20, width=80) with conn.cursor() as cursor: sqlrole = settings.DATABASES[request.database].get( "SQL_ROLE", "report_role") if sqlrole: cursor.execute("set role %s" % (sqlrole, )) cursor.execute(sql=cls.getSQL(report.sql)) if cursor.description: # Write header row header = [] for f in cursor.description: cell = WriteOnlyCell(ws, value=f[0]) cell.style = "readlonlyheaderstyle" cell.comment = comment header.append(cell) ws.append(header) # Add an auto-filter to the table ws.auto_filter.ref = "A1:%s1048576" % get_column_letter( len(header)) # Write all output rows for result in cursor.fetchall(): ws.append( [_getCellValue(i, request=request) for i in result]) # Write the spreadsheet wb.save(out) finally: if conn: conn.close()
def write_bmon_spreadsheet(sensors, filename): """Saves an Excel spreadsheet to 'filename' that contains the neede sensor information to import into BMON. """ columns = [ ('Building', 23), ('Sensor ID', 32), ('Title', 28), ('Unit Label', 11), ('Sensor Group', 29), ('Sort Order', 8), ('Is Calc Field', 8), ('Calc or Transform Function', 24), ('Function Parameters', 33), ] # Make Workbook and get the worksheet wb = Workbook() ws = wb.active ws.title = 'Main' ws.append(list(zip(*columns))[0]) # create all the sensor rows for s in sensors: for param, unit in s['params']: rec = [ s['bldg'], f"{s['dev_eui']}_{param}", f"{s['name']} {config.param_label(param)}", unit, ] ws.append(rec) title = NamedStyle(name='title') title.font = Font(bold=True) bd = Side(style='thin', color='000000') title.border = Border(bottom=bd) title.alignment = Alignment(horizontal='center', wrap_text=True) wb.add_named_style(title) for col in 'ABCDEFGHI': ws[f'{col}1'].style = 'title' column_widths = list(zip(*columns))[1] for i, column_width in enumerate(column_widths): ws.column_dimensions[get_column_letter(i + 1)].width = column_width wb.save(filename)
def do_analysis_output(output_file): _validator1 = get_validator(files('schema_data.inputs').joinpath('panel.json')) _validator2 = get_validator(files('schema_data.inputs.platforms.InForm').joinpath('analysis.json')) _schema1 = _validator1.schema _schema2 = _validator2.schema #_schema1 = json.loads(files('schema_data.inputs').joinpath('panel.json').read_text()) #_schema2 = json.loads(files('schema_data.inputs.platforms.InForm').joinpath('analysis.json').read_text()) wb = Workbook() default_names = wb.sheetnames wb.add_named_style(highlight) # Start with the Metadata. Write the header and the value names ws0 = wb.create_sheet(_schema2['properties']['parameters']['title']) _write_parameters(ws0,[_schema1['properties']['parameters'],_schema2['properties']['parameters']]) _fix_width(ws0) ws1 = wb.create_sheet(_schema1['properties']['markers']['title']) _write_repeating(ws1,_schema1['properties']['markers']) _fix_width(ws1) ws2 = wb.create_sheet(_schema2['properties']['inform_exports']['title']) _write_repeating(ws2,_schema2['properties']['inform_exports']) _fix_width(ws2) ws3 = wb.create_sheet(_schema2['properties']['mutually_exclusive_phenotypes']['title']) _write_repeating(ws3,_schema2['properties']['mutually_exclusive_phenotypes']) _fix_width(ws3) ws4 = wb.create_sheet(_schema2['properties']['binary_phenotypes']['title']) _write_repeating(ws4,_schema2['properties']['binary_phenotypes']) _fix_width(ws4) ws5 = wb.create_sheet(_schema2['properties']['regions']['title']) _write_repeating(ws5,_schema2['properties']['regions']) _fix_width(ws5) # cleanup workbook deleting default sheet name for _sheet_name in default_names: #print(_sheet_name) del wb[_sheet_name] wb.save(filename = output_file) return
def update_workbook_with_new_record(wb: Workbook, report_date: date, report_time: time): column_title_to_value_dict = {} column_title_to_value_dict[ sitrep_column_constants.DATE_COLUMN] = report_date column_title_to_value_dict[ sitrep_column_constants.TIME_COLUMN] = report_time # csse data csse_data_dict = get_csse_data(report_date) column_title_to_value_dict.update(csse_data_dict) # vdh Data vdh_dict = get_vdh_data(report_date) column_title_to_value_dict.update(vdh_dict) # Write in No Data for column_title in NO_DATA_COLUMNS: column_title_to_value_dict[column_title] = NO_DATA # https://openpyxl.readthedocs.io/en/stable/api/openpyxl.workbook.workbook.html # check if style names are registered, and if not, register workbook_style_names = wb.style_names for style_name in STYLE_NAME_TO_STYLE_DICT.keys(): if style_name not in workbook_style_names: wb.add_named_style(STYLE_NAME_TO_STYLE_DICT[style_name]) # Pull the sheet sheet = wb[file_constants.SHEET_NAME] # # Add a row at the end # last_row_number = get_last_row(sheet) + 1 # sheet.insert_rows(last_row_number) last_row_number = file_constants.get_row_number(report_date) # With the formulas, now we don't look fro the last row number, we have a hardcoded map. # Update the last row with values for report_date for column_title, column_letter in column_title_to_letter_dicts.items(): cell = sheet[get_cell(column_letter, last_row_number)] if column_title in column_title_to_value_dict: cell.value = column_title_to_value_dict[column_title] if column_title in COLUMN_NAME_TO_STYLE_NAME_DICT: cell.style = COLUMN_NAME_TO_STYLE_NAME_DICT[column_title]
wrap_text="True") wsh2.border = Border(left=bd2, top=bd2, right=bd2, bottom=bd2) wsh2.fill = PatternFill("solid", fgColor="305496") wsh2.font = Font(bold=True, size=15, color="FFFFFF") ws_odd = NamedStyle(name="ws_odd") ws_odd.alignment = Alignment(horizontal="center", vertical="center") ws_odd.border = Border(left=bd2, top=bd2, right=bd2, bottom=bd2) ws_odd.fill = PatternFill("solid", fgColor="D9E1F2") ws_odd.font = Font(bold=False, size=12, color="44546A") ws_even = NamedStyle(name="ws_even") ws_even.alignment = Alignment(horizontal="center", vertical="center") ws_even.border = Border(left=bd2, top=bd2, right=bd2, bottom=bd2) ws_even.font = Font(bold=False, size=12, color="44546A") wb = Workbook() wb.add_named_style(wsh1) wb.add_named_style(wsh2) wb.add_named_style(ws_odd) wb.add_named_style(ws_even) dest_file = 'export.xlsx' ws1 = wb.active ws1.title = "Tenant" ws2 = wb.create_sheet(title="VRF") ws3 = wb.create_sheet(title="Bridge Domain") ws4 = wb.create_sheet(title="Subnet") ws5 = wb.create_sheet(title="DHCP Relay") ws6 = wb.create_sheet(title="Access Interfaces") ws7 = wb.create_sheet(title="Static Port Mappings") ws2 = wb["VRF"]
def export_to_excel(self, bag, export_path=None): def as_text(value): if value is None: return "" return str(value) if len(bag) == 0: return None try: # create workbook wb = Workbook() # create header style highlight = NamedStyle(name="highlight") highlight.font = Font(name='DejaVu Sans', bold=True, size=10) bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) wb.add_named_style(highlight) # create data style normal = NamedStyle(name="normal") normal.font = Font(name='DejaVu Sans', bold=False, size=10) wb.add_named_style(normal) # Get worksheet ws = wb.active # build header header = [] for field in HEADER_FIELDS: header.append(str(HEADER[field])) ws.append(header) # Set data data = [] for sid in bag: row = [] metadata = self.srvdtb.get_sapnote_metadata(sid) for field in HEADER_FIELDS: if field == 'collections': cols = ', '.join([self.srvclt.get_name_by_cid(col) for col in metadata[field]]) row.append(cols) elif field == 'releasedon': excel_date = self.srvutl.get_excel_date(metadata[field]) row.append(excel_date) else: row.append(str(metadata[field])) ws.append(row) data.append(row) # assign style to header for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']: cell = col + '1' ws[cell].style = 'highlight' # assign style to data for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']: for i in range(len(bag)): cell = col + '%d' % (i + 2) ws[cell].style = 'normal' # adjust columns width try: for column_cells in ws.columns: length = max(len(as_text(cell.value)) for cell in column_cells) ws.column_dimensions[COL[column_cells[0].column]].width = length except Exception as error: self.log.error(error) self.log.error(self.get_traceback()) self.log.error("This piece of code isn't working on Windows...") # header autofilter and sorting ws.auto_filter.ref = "A1:J1" ws.auto_filter.add_sort_condition("I2:I%d" % len(bag)) # save to export path wb.save(export_path) return True except Exception as error: self.log.error(error) self.log.error(self.get_traceback()) return False
def exportWorkbook(request): # Create a workbook wb = Workbook(write_only=True) # Create a named style for the header row headerstyle = NamedStyle(name="headerstyle") headerstyle.fill = PatternFill(fill_type="solid", fgColor="70c4f4") wb.add_named_style(headerstyle) readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle") readlonlyheaderstyle.fill = PatternFill(fill_type="solid", fgColor="d0ebfb") wb.add_named_style(readlonlyheaderstyle) # Loop over all selected entity types exportConfig = {"anonymous": request.POST.get("anonymous", False)} ok = False for entity_name in request.POST.getlist("entities"): try: # Initialize (app_label, model_label) = entity_name.split(".") model = apps.get_model(app_label, model_label) # Verify access rights permname = get_permission_codename("change", model._meta) if not request.user.has_perm("%s.%s" % (app_label, permname)): continue # Never export some special administrative models if model in EXCLUDE_FROM_BULK_OPERATIONS: continue # Create sheet ok = True ws = wb.create_sheet(title=force_text(model._meta.verbose_name)) # Build a list of fields and properties fields = [] modelfields = [] header = [] source = False lastmodified = False owner = False comment = None try: # The admin model of the class can define some fields to exclude from the export exclude = data_site._registry[model].exclude except Exception: exclude = None for i in model._meta.fields: if i.name in ["lft", "rght", "lvl"]: continue # Skip some fields of HierarchyModel elif i.name == "source": source = i # Put the source field at the end elif i.name == "lastmodified": lastmodified = i # Put the last-modified field at the very end elif not (exclude and i.name in exclude): fields.append(i.column) modelfields.append(i) cell = WriteOnlyCell(ws, value=force_text( i.verbose_name).title()) if i.editable: cell.style = "headerstyle" if isinstance(i, ForeignKey): cell.comment = CellComment( force_text( _("Values in this field must exist in the %s table" ) % force_text(i.remote_field.model. _meta.verbose_name)), "Author", ) elif i.choices: cell.comment = CellComment( force_text( _("Accepted values are: %s") % ", ".join([c[0] for c in i.choices])), "Author", ) else: cell.style = "readlonlyheaderstyle" if not comment: comment = CellComment( force_text(_("Read only")), "Author", height=20, width=80, ) cell.comment = comment header.append(cell) if i.name == "owner": owner = True if hasattr(model, "propertyFields"): if callable(model.propertyFields): props = model.propertyFields(request) else: props = model.propertyFields for i in props: if i.export: fields.append(i.name) cell = WriteOnlyCell(ws, value=force_text( i.verbose_name).title()) if i.editable: cell.style = "headerstyle" if isinstance(i, ForeignKey): cell.comment = CellComment( force_text( _("Values in this field must exist in the %s table" ) % force_text(i.remote_field.model. _meta.verbose_name)), "Author", ) elif i.choices: cell.comment = CellComment( force_text( _("Accepted values are: %s") % ", ".join([c[0] for c in i.choices])), "Author", ) else: cell.style = "readlonlyheaderstyle" if not comment: comment = CellComment( force_text(_("Read only")), "Author", height=20, width=80, ) cell.comment = comment header.append(cell) modelfields.append(i) if source: fields.append("source") cell = WriteOnlyCell(ws, value=force_text(_("source")).title()) cell.style = "headerstyle" header.append(cell) modelfields.append(source) if lastmodified: fields.append("lastmodified") cell = WriteOnlyCell(ws, value=force_text( _("last modified")).title()) cell.style = "readlonlyheaderstyle" if not comment: comment = CellComment(force_text(_("Read only")), "Author", height=20, width=80) cell.comment = comment header.append(cell) modelfields.append(lastmodified) # Write a formatted header row ws.append(header) # Add an auto-filter to the table ws.auto_filter.ref = "A1:%s1048576" % get_column_letter( len(header)) # Use the default manager if issubclass(model, HierarchyModel): model.rebuildHierarchy(database=request.database) query = (model.objects.all().using(request.database).order_by( "lvl", "pk")) elif owner: # First export records with empty owner field query = (model.objects.all().using(request.database).order_by( "-owner", "pk")) else: query = model.objects.all().using( request.database).order_by("pk") # Special annotation of the export query if hasattr(model, "export_objects"): query = model.export_objects(query, request) # Loop over all records for rec in query.values_list(*fields): cells = [] fld = 0 for f in rec: cells.append( _getCellValue(f, field=modelfields[fld], exportConfig=exportConfig)) fld += 1 ws.append(cells) except Exception: pass # Silently ignore the error and move on to the next entity. # Not a single entity to export if not ok: raise Exception(_("Nothing to export")) # Write the excel from memory to a string and then to a HTTP response output = BytesIO() wb.save(output) response = HttpResponse( content_type= "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", content=output.getvalue(), ) response["Content-Disposition"] = 'attachment; filename="frepple.xlsx"' response["Cache-Control"] = "no-cache, no-store" return response
def create_groups_xlsx(day): activate("de") day = GroupAssignment.objects.monday(day) days = [day + timedelta(days=i) for i in range(5)] wb = Workbook() ws = wb.active thin_border = Side(border_style="thin", color="00000000") medium_border = Side(border_style="medium", color="00000000") font = Font(name="Calibri", size=14) centered = NamedStyle("centered") centered.font = font centered.alignment = Alignment(horizontal="center", vertical="center") wb.add_named_style(centered) dark = NamedStyle("dark") dark.font = font dark.fill = PatternFill("solid", "cccccc") dark.border = Border( top=thin_border, right=thin_border, bottom=thin_border, left=thin_border ) wb.add_named_style(dark) darker = NamedStyle("darker") darker.border = Border(top=thin_border, bottom=thin_border) darker.font = Font(name="Calibri", size=14, bold=True) darker.fill = PatternFill("solid", "aaaaaa") wb.add_named_style(darker) darker_border_left = NamedStyle("darkerBorderLeft") darker_border_left.border = Border( top=thin_border, bottom=thin_border, left=medium_border ) darker_border_left.font = darker.font darker_border_left.fill = darker.fill wb.add_named_style(darker_border_left) border = NamedStyle("borderThickLeft") border.border = Border( top=thin_border, right=thin_border, bottom=thin_border, left=medium_border ) border.font = font wb.add_named_style(border) border = NamedStyle("borderThickBottom") border.border = Border(bottom=medium_border) border.font = font wb.add_named_style(border) border = NamedStyle("borderThinLeft") border.border = Border(left=thin_border) border.font = font wb.add_named_style(border) border = NamedStyle("borderThinBottom") border.border = Border(bottom=thin_border) border.font = font wb.add_named_style(border) borderThin = NamedStyle("borderThin") borderThin.border = Border( top=thin_border, right=thin_border, bottom=thin_border, left=thin_border ) borderThin.font = font wb.add_named_style(borderThin) vertical_text = Alignment(text_rotation=90) def day_column(weekday): return 2 + 9 * weekday def style_row(row, style): ws[c(0, row)].style = style ws[c(1, row)].style = style ws[c(day_column(5), row)].style = ( "darkerBorderLeft" if style == "darker" else style ) for i in range(5): for j in range(9): ws[c(day_column(i) + j, row)].style = style if style == "darker": ws[c(day_column(i), row)].style = "darkerBorderLeft" def column_width(column, width): ws.column_dimensions[columns[column]].width = width def row_height(row, height): ws.row_dimensions[row + 1].height = height ws[c(0, 1)].style = "borderThickBottom" ws[c(1, 1)].style = "borderThickBottom" ws[c(day_column(5), 1)].style = "borderThickBottom" for i, cell in enumerate( [ date_format(day, "F y"), "Woche %s" % date_format(day, "W"), "Auftragsnummer Arbeit", "LEITUNG", "ZIVIS", ] ): ws[c(0, i + 1)] = cell ws[c(day_column(5), i + 1)] = cell ws[c(0, i + 1)].style = "borderThinBottom" ws[c(1, i + 1)].style = "borderThinBottom" if i > 0: ws[c(day_column(5), i + 1)].style = "borderThickLeft" if i < 2: ws[c(0, i + 1)].style = centered ws[c(day_column(5), i + 1)].style = centered column_width(0, 35) column_width(1, 15) column_width(day_column(5), 35) ws[c(0, 1)].style = "borderThickBottom" ws[c(1, 1)].style = "borderThickBottom" ws[c(0, 1)].alignment = centered.alignment ws[c(1, 1)].alignment = centered.alignment ws[c(0, 2)].style = "borderThinBottom" ws[c(0, 2)].alignment = centered.alignment ws[c(day_column(5), 1)].style = "borderThickBottom" ws[c(day_column(5), 1)].alignment = centered.alignment ws[c(day_column(5), 2)].style = "borderThickLeft" ws[c(day_column(5), 2)].alignment = centered.alignment for i, current in enumerate(days): ws[c(day_column(i), 0)] = date_format(current, "l") ws[c(day_column(i), 1)] = date_format(current, "d.m.y") ws[c(day_column(i), 0)].style = centered ws[c(day_column(i), 1)].style = "borderThickBottom" ws[c(day_column(i), 1)].alignment = centered.alignment ws.merge_cells("%s:%s" % (c(day_column(i), 0), c(day_column(i + 1) - 1, 0))) ws.merge_cells("%s:%s" % (c(day_column(i), 1), c(day_column(i + 1) - 1, 1))) ws[c(day_column(i), 2)] = "Absenz" for k in range(2, 499): ws[c(day_column(i), k)].style = "borderThickLeft" ws[c(day_column(i) + 1, k)].style = "borderThin" for j in range(1, 9): ws[c(day_column(i) + j, 2)] = "%s)" % j style = "borderThin" if j % 2 else "dark" for k in range(2, 499): ws[c(day_column(i) + j, k)].style = style ws[c(day_column(i) + j, 2)].alignment = vertical_text column_width(day_column(i) + j, 7) ws[c(day_column(i), 2)].alignment = vertical_text column_width(day_column(i), 7) row_height(2, 250) row_height(3, 60) row_height(4, 60) # ZIVIS line style_row(5, "darker") assignments = defaultdict(list) seen_assignments = set() for ga in GroupAssignment.objects.filter(week=day).select_related( "assignment__drudge__user" ): assignments[ga.group_id].append(ga.assignment) seen_assignments.add(ga.assignment_id) free_assignments = ( Assignment.objects.for_date(day) .exclude(pk__in=seen_assignments) .select_related("drudge__user") ) absences = defaultdict(dict) for absence in Absence.objects.filter(days__overlap=days): for day in absence.days: absences[absence.assignment_id][day] = absence def add_group(row, group_name, assignments): ws[c(0, row)] = group_name ws[c(day_column(5), row)] = group_name style_row(row, "darker") # TODO courses (UNA/MSK) for assignment in assignments: row += 1 ws[c(0, row)] = assignment.drudge.user.get_full_name() ws[c(day_column(5), row)] = assignment.drudge.user.get_full_name() ws[c(0, row)].style = "borderThinBottom" ws[c(1, row)].style = "borderThinBottom" ws[c(day_column(5), row)].style = "borderThickLeft" row_height(row, 35) if assignment.date_from in days: ws[c(1, row)] = "NEU" elif assignment.determine_date_until() in days: ws[c(1, row)] = "ENDE" else: ws[c(1, row)] = date_format(assignment.determine_date_until(), "d.m.y") for i, current in enumerate(days): if current < assignment.date_from: ws[c(day_column(i), row)] = "Vor Beginn" elif current > assignment.determine_date_until(): ws[c(day_column(i), row)] = "Nach Ende" elif current in absences[assignment.id]: ws[c(day_column(i), row)] = absences[assignment.id][ current ].pretty_reason() # Skip some lines for i in range(0, max(3, 6 - len(assignments))): row += 1 row_height(row, 35) ws[c(0, row)].style = "borderThinBottom" ws[c(1, row)].style = "borderThinBottom" ws[c(day_column(5), row)].style = "borderThickLeft" row += 1 return row row = 6 for group in Group.objects.active(): row = add_group(row, group.name, assignments[group.id]) row = add_group(row, "Nicht zugeteilt", free_assignments) return wb
def write_dest(xlsx_name, schema_name): border = Border( left=Side(border_style=borders.BORDER_THIN, color='FF000000'), right=Side(border_style=borders.BORDER_THIN, color='FF000000'), top=Side(border_style=borders.BORDER_THIN, color='FF000000'), bottom=Side(border_style=borders.BORDER_THIN, color='FF000000') ) alignment = Alignment(horizontal='justify', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=True, indent=0) fill = PatternFill(fill_type=None, start_color='FFFFFFFF') # 基本的样式 basic_style = NamedStyle(name="basic_style", font=Font(name='Microsoft YaHei') , border=border, alignment=alignment, fill=fill) title_style = copy(basic_style) title_style.name = 'title_style' title_style.font = Font(name='Microsoft YaHei', b=True, size=20, color='00215757') title_style.alignment = Alignment(horizontal='center', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=True, indent=0) title_style.fill = PatternFill(fill_type=fills.FILL_SOLID, start_color='00B2CBED') header_style = copy(basic_style) header_style.name='header_style' header_style.font = Font(name='Microsoft YaHei', b=True, size=15, color='00215757') header_style.fill = PatternFill(fill_type=fills.FILL_SOLID, start_color='00BAA87F') common_style = copy(basic_style) common_style.name = 'common_style' link_style = copy(basic_style) link_style.name = 'link_style' link_style.font = Font(name='Microsoft YaHei', color=colors.BLUE, underline='single') table_data = load_schema(schema_name) wb = Workbook() wb.add_named_style(basic_style) wb.add_named_style(title_style) wb.add_named_style(header_style) wb.add_named_style(common_style) bstyle = NamedStyle(name='bstyle', border=Border( bottom=Side(border_style=borders.BORDER_THIN, color='FF000000'))) wb.add_named_style(bstyle) wb.active.title = "首页列表" for table in table_data: ws = wb.create_sheet(title=table[0]) ws.merge_cells('E3:I3') # 合并单元格 ws['E3'].style = title_style ws['F2'].style = bstyle ws['G2'].style = bstyle ws['H2'].style = bstyle ws['I2'].style = bstyle ws['J3'].style = bstyle ws['E3'] = table[0] ws['E4'].style = header_style ws['E4'] = '列名' ws['F4'].style = header_style ws['F4'] = '类型' ws['G4'].style = header_style ws['G4'] = '空值约束' ws['H4'].style = header_style ws['H4'] = '默认值' ws['I4'].style = header_style ws['I4'] = '备注' ws.column_dimensions['E'].width = 30 ws.column_dimensions['F'].width = 20 ws.column_dimensions['G'].width = 12 ws.column_dimensions['H'].width = 25 ws.column_dimensions['I'].width = 40 for idx, each_column in enumerate(table[2:]): ws['E{}'.format(idx + 5)].style = common_style ws['E{}'.format(idx + 5)] = each_column[0] ws['F{}'.format(idx + 5)].style = common_style ws['F{}'.format(idx + 5)] = each_column[1] ws['G{}'.format(idx + 5)].style = common_style ws['G{}'.format(idx + 5)] = each_column[2] ws['H{}'.format(idx + 5)].style = common_style ws['H{}'.format(idx + 5)] = each_column[3] ws['I{}'.format(idx + 5)].style = common_style ws['I{}'.format(idx + 5)] = each_column[4] ws = wb['首页列表'] ws.merge_cells('D3:F3') ws['D3'].style = title_style ws['E2'].style = bstyle ws['F2'].style = bstyle ws['G3'].style = bstyle ws['D3'] = 'MySQL数据库系统表' ws['D4'].style = header_style ws['D4'] = '编号' ws['E4'].style = header_style ws['E4'] = '表名' ws['F4'].style = header_style ws['F4'] = '详情链接' ws.column_dimensions['D'].width = 15 ws.column_dimensions['E'].width = 35 ws.column_dimensions['F'].width = 45 for inx, val in enumerate(table_data): ws['D{}'.format(inx + 5)].style = common_style ws['D{}'.format(inx + 5)] = inx + 1 ws['E{}'.format(inx + 5)].style = common_style ws['E{}'.format(inx + 5)] = val[1] linkcell = ws['F{}'.format(inx + 5)] linkcell.style = link_style linkcell.value = val[0] linkcell.hyperlink = '#{0}!{1}'.format(val[0], 'E3') wb.save(filename=xlsx_name)
def create_excel_headword_file(letter): cf = config.ConfigFile() text_files_path = (cf.configfile[cf.computername]['original_files_path']) # sections_and_pbs = [] # open the specific html file file_name = letter + ".html" text_file_path = text_files_path + file_name with open(text_file_path, 'r') as f: soup = BeautifulSoup(f) sections_and_pbs = soup.select(".section,[title='page break']") #pb = page break # get page numbers and headwords page_numbers_and_headwords = get_page_numbers_and_headwords(letter, sections_and_pbs) # od = OrderedDict(sorted(Counter(page_numbers_and_headwords).items())) pprint.pprint(page_numbers_and_headwords) # write the Excel file wb = Workbook() excel_files_path = (cf.configfile[cf.computername]['excel_files_path']) excel_file_name = letter + ".xlsx" excel_file_path = excel_files_path + excel_file_name page_numbers = list(set(x[0] for x in page_numbers_and_headwords)) for page_counter, page_number in enumerate(page_numbers, 0): headwords_for_page = [x[1] for x in page_numbers_and_headwords if x[0] == page_number] #get worksheet title if page_counter == 0: active_worksheet = wb.active active_worksheet.title = str(start_pages[letter]) else: active_worksheet = wb.create_sheet(title=str(start_pages[letter] + page_counter)) # data validation dv = DataValidation(type="list", formula1='"yes,no,adjust"', allow_blank=True) active_worksheet.add_data_validation(dv) #named styles style_title = NamedStyle(name="style_title") style_title.font = Font(bold=True, color=colours[letter], italic=True) # had to wrap this in try / except because sometimes getting that style_title already existed # when creating the workbook! try: wb.add_named_style(style_title) except ValueError: pass # titles active_worksheet["A1"] = "Entry" active_worksheet["A1"].style = 'style_title' active_worksheet["B1"] = "Headword" active_worksheet["B1"].style = 'style_title' active_worksheet["C1"] = "Status" active_worksheet["C1"].style = 'style_title' active_worksheet["D1"] = "Adjusted" active_worksheet["D1"].style = 'style_title' for counter, headword in enumerate(headwords_for_page, 1): active_worksheet.cell(row=counter + 1, column=1, value=counter) active_worksheet.cell(row=counter + 1, column=1).font = Font(bold=True) active_worksheet.cell(row=counter + 1, column=2, value=headword) dv.add("C"+str(counter + 1)) # couldn't use row column syntax rows = range(1, len(headwords_for_page) + 1 + 1) columns = range(1, 4 + 1) for row in rows: for col in columns: active_worksheet.cell(row, col).alignment = Alignment(horizontal='center', vertical='center') active_worksheet.sheet_format.defaultRowHeight = 22 active_worksheet.sheet_format.baseColWidth = 12 active_worksheet.sheet_view.zoomScale = 140 active_worksheet.sheet_view.showGridLines = False active_worksheet.sheet_properties.tabColor = colours[letter] wb.save(filename = excel_file_path) return True
str_style.alignment = Alignment(horizontal='center', vertical='center') str_style.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) str_style_fill = NamedStyle(name='str_style_fill') str_style_fill.font = Font(name='微软雅黑', size=11) str_style_fill.alignment = Alignment(horizontal='center', vertical='center') str_style_fill.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) str_style_fill.fill = PatternFill('solid', fgColor='E5E5E5') wb.add_named_style(title_style) wb.add_named_style(num_style) wb.add_named_style(num_style_fill) wb.add_named_style(str_style) wb.add_named_style(str_style_fill) rens = rens_list(cur, '前线') write(wb, rens, '整体') write(wb, rens, '车险') write(wb, rens, '非车险') write_zhong_zhi(wb, cur, rens, '分公司营业一部') write_zhong_zhi(wb, cur, rens, '曲靖') write_zhong_zhi(wb, cur, rens, '文山') write_zhong_zhi(wb, cur, rens, '大理')
def join_Database(): joined_table_file_name = 'media/qbreports/uploads/qb_omx_joined_tables.csv' csv_file_to_xlsx = 'media/qbreports/uploads/csv_file_to_xlsx.csv' xlsx_to_formated_xlsx = 'media/qbreports/uploads/xlsx_to_formated_xlsx.xlsx' downlaod_file_name = 'media/qbreports/uploads/custom_qb_report.xlsx' google_doc_sheet = 'media/qbreports/uploads/google_doc_details.csv' df = pd.DataFrame( list(QBImportFile.objects.all().values( 'notes', 'customID', 'vendorName', 'invoiceDate', 'invoiceNumber', 'poNumb', 'invocieTotal', 'dropShipFee', 'shippingFee', 'omxPO', 'omxTotal', 'totalDifference', 'discrepancy', 'memo'))) df = df[[ 'notes', 'customID', 'vendorName', 'invoiceDate', 'invoiceNumber', 'poNumb', 'invocieTotal', 'dropShipFee', 'shippingFee', 'omxPO', 'omxTotal', 'totalDifference', 'discrepancy', 'memo' ]] df2 = pd.DataFrame( list(OMXOrderHistory.objects.all().values('customID', 'itemCode', 'description', 'dropShipCOG', 'qty', 'total'))) df2 = df2[[ 'customID', 'itemCode', 'description', 'dropShipCOG', 'qty', 'total' ]] df3 = pd.merge(df, df2, on='customID', how='left') df3.to_csv(joined_table_file_name, sep=',', encoding='utf-8') writeFileList = [] writeFileList.append([ "Notes", 'Vendor Name', 'Invoice Date', 'Invoice Number', 'PO Num', 'Invoice Total', 'Drop Ship Fee', 'Shipping Fee', 'OMX PO', 'OMX Total', 'Total Difference', 'Discrepancy', 'Memo' ]) itemHeader = [ "", "", "", "", "", "", 'Code', 'Item Name', 'QTY', 'OMX Unit Price (Drop Ship)', 'Drop Ship Total', "", "" ] moreThanOneItem = [] google_doc_headers = [ 'Vendor Name', 'Invoice Date', 'Invoice Number', 'OMX Order Number', 'Quantity', "Item Code", 'OMX Price', 'Invoice Price' ] with open(joined_table_file_name, 'rt', encoding='utf8') as f, open(csv_file_to_xlsx, 'w', newline='', encoding='utf8') as writeCSV, open( google_doc_sheet, 'w', newline='', encoding='utf8') as gdocCSV: write = csv.writer(writeCSV, delimiter=',') gwrite = csv.writer(gdocCSV, delimiter=',') readCSV = csv.DictReader(f, delimiter=',') fileList = [] newList = [] google_doc_list = [] google_doc_list.append(google_doc_headers) for row in readCSV: fileList.append(row) for i in range(len(fileList)): google_doc_format = [ fileList[i]['vendorName'], fileList[i]['invoiceDate'], fileList[i]['invoiceNumber'], fileList[i]['omxPO'], fileList[i]['qty'], fileList[i]['itemCode'], fileList[i]['dropShipCOG'], fileList[i]['invocieTotal'] ] invoiceDetails = [ fileList[i]['notes'], fileList[i]['vendorName'], fileList[i]['invoiceDate'], fileList[i]['invoiceNumber'], fileList[i]['poNumb'], fileList[i]['invocieTotal'], fileList[i]['dropShipFee'], fileList[i]['shippingFee'], fileList[i]['omxPO'], fileList[i]['omxTotal'], fileList[i]['totalDifference'], fileList[i]['discrepancy'], fileList[i]['memo'] ] productDetails = [ "", "", "", fileList[i]['invoiceNumber'], fileList[i]['poNumb'], "", fileList[i]['itemCode'], fileList[i]['description'], fileList[i]['qty'], fileList[i]['dropShipCOG'], fileList[i]['total'], "", "" ] try: if fileList[i]['omxPO'] in moreThanOneItem and fileList[i][ 'invoiceNumber'] == fileList[i - 1]['invoiceNumber']: writeFileList.append(productDetails) google_doc_list.append(google_doc_format) elif fileList[i]['discrepancy'] == None or fileList[i][ 'discrepancy'] == 'Duplicate - NOT UPLOADED' or fileList[ i]['discrepancy'] == '': if fileList[i]['omxPO'] == fileList[i + 1]['omxPO']: continue else: writeFileList.append(invoiceDetails) elif fileList[i]['omxPO'] == '0': writeFileList.append(invoiceDetails) elif fileList[i]['omxPO'] == fileList[i + 1][ 'omxPO'] and fileList[i]['invoiceNumber'] == fileList[ i + 1]['invoiceNumber']: moreThanOneItem.append(fileList[i]['omxPO']) writeFileList.append(invoiceDetails) writeFileList.append(itemHeader) writeFileList.append(productDetails) google_doc_list.append(google_doc_format) else: writeFileList.append(invoiceDetails) writeFileList.append(itemHeader) writeFileList.append(productDetails) google_doc_list.append(google_doc_format) except IndexError: print(newList.append(invoiceDetails)) for x in writeFileList: write.writerow(x) for x in google_doc_list: gwrite.writerow(x) dfUpdated = pd.read_csv(csv_file_to_xlsx, encoding='latin-1') dfgdoc = pd.read_csv(google_doc_sheet, encoding='latin-1') wb = Workbook() ws = wb.active ws.title = 'Matching_Updated' ws2 = wb.create_sheet() ws2.title = 'Vendor Price Check Gdoc' bold = NamedStyle(name='bold') bold.font = Font(bold=True) wb.add_named_style(bold) cell = WriteOnlyCell(ws) cell.style = 'bold' greenFill = PatternFill(start_color='32CD32', end_color='32CD32', fill_type='solid') for row in dataframe_to_rows(dfUpdated, index=False, header=True): ws.append(row) for row in dataframe_to_rows(dfgdoc, index=False, header=True): ws2.append(row) wb.save(xlsx_to_formated_xlsx) wb = load_workbook(filename=xlsx_to_formated_xlsx) ws = wb.active for row in ws.iter_rows(): if row[1].value == "Vendor Name": for cell in row: ws[str(cell.coordinate)].font = Font(bold=True) if row[1].value == None: for cell in row: if cell.value != None: ws[str(cell.coordinate)].fill = greenFill if row[1].value != None: for cell in row: ws[str(cell.coordinate)].font = Font(bold=True) wb.save(downlaod_file_name) qb_Import_File_delete_everything() return downlaod_file_name
minor_style = NamedStyle(name='minor_style') thin = Side(border_style='thin', color='000000') main_style.border = Border(top=thin, left=thin, right=thin, bottom=thin) minor_style.border = Border(top=thin, left=thin, right=thin, bottom=thin) main_style.fill = PatternFill('solid', fgColor='6fb1bd') minor_style.fill = PatternFill('solid', fgColor='b3dee7') main_style.font = Font(size=12, name='Calibri') minor_style.font = Font(size=12, name='Calibri') # open workbook, rename title wb = Workbook() ws = wb.active ws.title = file_name + '_spec_list' ws.sheet_properties.tabColor = "20FF20" wb.add_named_style(main_style) wb.add_named_style(minor_style) # change width of column (convert 2.85points=1mm) ws.column_dimensions["A"].width = 20 ws.column_dimensions["B"].width = 130 ws.column_dimensions["C"].width = 60 ws.column_dimensions["D"].width = 35 ws.column_dimensions["E"].width = 45 ws.column_dimensions["F"].width = 20 ws.column_dimensions["G"].width = 20 ws.column_dimensions["H"].width = 25 ws.column_dimensions["I"].width = 40 ws.row_dimensions[1].height = 50
class ExcelCreator: def __init__(self, json_path, out_put_path): self.json_path = json_path self.out_put_path = out_put_path self.wb = Workbook() try: f = open(json_path, 'r') self.data = json.load(f) f.close() except Exception: raise Exception( f"Please make sure your test json file exist in path {out_put_path}" ) self._create_test_sheet() self.style = self._set_style() def _create_test_sheet(self): self.ws_summary = self.wb.create_sheet("DashBoard", 0) # self.ws_test_details = self.wb.create_sheet("TestDetails", 1) def _set_style(self): my_style = NamedStyle(name="my_style") my_style.font = Font(name='Calibri') bd = Side(style='thin') my_style.border = Border(left=bd, top=bd, right=bd, bottom=bd) self.wb.add_named_style(my_style) return "my_style" # Write the summary details in Sheet: DashBoard def _write_summary(self): # write config summary to 1 - 2 self._single_line_dict(self.ws_summary, 1, self.data['testConfig']) # write feature summary from row 4 self._multiple_lines_list(self.ws_summary, 4, self.data['featureSummary']) # write test details summary from current max row + 2 from_row = self.ws_summary.max_row + 2 self._multiple_lines_list(self.ws_summary, from_row, self.data['testDetails'], ignore_text="steps") # write cases total from current max row + 2 from_row = self.ws_summary.max_row + 2 self._single_line_dict(self.ws_summary, from_row, self.data['casesChart']) # write steps total from current max row + 2 from_row = self.ws_summary.max_row + 2 self._single_line_dict(self.ws_summary, from_row, self.data['stepsChart']) self._auto_adjust_width(self.ws_summary) # Write the test details in Sheet: TestDetails # Suggest you to see the test details in html report # This method is disable default def _write_details(self): tests = self.data['testDetails'] for i in range(len(tests)): steps = tests[i]['steps'] test = tests[i] del test['steps'] if i == 0: self._single_line_dict(self.ws_test_details, 1, test) else: from_row = self.ws_test_details.max_row + 2 self._single_line_dict(self.ws_test_details, from_row, test) from_row = self.ws_test_details.max_row + 1 self._multiple_lines_list(self.ws_test_details, from_row, steps) self._auto_adjust_width(self.ws_test_details) def _single_line_dict(self, sheet, from_row, dict_data): index = 1 for key, value in dict_data.items(): sheet.cell(row=from_row, column=index, value=key.title()) sheet.cell(row=from_row, column=index).style = self.style sheet.cell(row=from_row, column=index).fill = PatternFill('solid', fgColor="95B3D7") sheet.cell(row=from_row + 1, column=index, value=str(value)) sheet.cell(row=from_row + 1, column=index).style = self.style index += 1 def _multiple_lines_list(self, sheet, from_row, list_data, ignore_text=None): index = 1 for k in list_data[0].keys(): if ignore_text and ignore_text == k: continue sheet.cell(row=from_row, column=index, value=k.title()) sheet.cell(row=from_row, column=index).style = self.style sheet.cell(row=from_row, column=index).fill = PatternFill('solid', fgColor="95B3D7") index += 1 for index in range(len(list_data)): dict_data = list_data[index] v_index = 1 row = from_row + index + 1 for k, v in dict_data.items(): if ignore_text and ignore_text == k: continue v = str(v) sheet.cell(row=row, column=v_index, value=v) sheet.cell(row=row, column=v_index).style = self.style v_index += 1 def _auto_adjust_width(self, sheet): # auto adjust the width for col in sheet.columns: max_length = 0 column = col[0].column_letter 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 Exception: pass sheet.column_dimensions[column].width = max_length + 2 def save(self): try: self._write_summary() # self._write_details() self.wb.save(self.out_put_path) except Exception as e: raise e
def export_to_excel(self, bag, export_path=None): def as_text(value): if value is None: return "" return str(value) if len(bag) == 0: return None try: # create workbook wb = Workbook() # create header style highlight = NamedStyle(name="highlight") highlight.font = Font(name='DejaVu Sans', bold=True, size=10) bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) wb.add_named_style(highlight) # create data style normal = NamedStyle(name="normal") normal.font = Font(name='DejaVu Sans', bold=False, size=10) wb.add_named_style(normal) # Get worksheet ws = wb.active # build header header = [] for field in HEADER_FIELDS: header.append(str(HEADER[field])) ws.append(header) # Set data data = [] for sid in bag: row = [] metadata = self.srvdtb.get_sapnote_metadata(sid) for field in HEADER_FIELDS: if field == 'collections': cols = ', '.join([ self.srvclt.get_name_by_cid(col) for col in metadata[field] ]) row.append(cols) elif field == 'releasedon': excel_date = self.srvutl.get_excel_date( metadata[field]) row.append(excel_date) else: row.append(str(metadata[field])) ws.append(row) data.append(row) # assign style to header for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']: cell = col + '1' ws[cell].style = 'highlight' # assign style to data for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']: for i in range(len(bag)): cell = col + '%d' % (i + 2) ws[cell].style = 'normal' # adjust columns width try: for column_cells in ws.columns: length = max( len(as_text(cell.value)) for cell in column_cells) ws.column_dimensions[COL[ column_cells[0].column]].width = length except Exception as error: self.log.error(error) self.log.error(self.get_traceback()) self.log.error( "This piece of code isn't working on Windows...") # header autofilter and sorting ws.auto_filter.ref = "A1:J1" ws.auto_filter.add_sort_condition("I2:I%d" % len(bag)) # save to export path wb.save(export_path) return True except Exception as error: self.log.error(error) self.log.error(self.get_traceback()) return False
def make_excel(data_out: pd.DataFrame) -> None: wb = Workbook() wb.add_named_style(headers_style) wb.add_named_style(base_style) wb.add_named_style(text_wrap_style) ws = wb.active ws.auto_filter.ref = 'A1:M1' # Add data for r in dataframe_to_rows(data_out, index=False, header=True): ws.append(r) # Set Base style for columns in ws['A:M']: for cell in columns[1:]: cell.style = base_style # Set style for Headers cells = ws['A1:M1'] for cell in cells[0]: cell.style = headers_style # Set style for Hyperlink for cell in ws['B:B'][1:]: cell.hyperlink = cell.value cell.style = 'Hyperlink' for column in ['D', 'E', 'J']: for cell in ws[f'{column}:{column}'][1:]: cell.style = text_wrap_style # Set width for columns columns = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M'] for column in columns: ws.column_dimensions[column].width = 13 for row in range(len(ws['A'])): ws.row_dimensions[row].height = 25.5 start = 2 list_to_merge = [] for i in data_out['Дата добавления'].value_counts().sort_index( ascending=False).values: list_to_merge.append([start, start + i - 1]) start += i flag = 2 for i, j in list_to_merge: ws.merge_cells(f'A{i}:A{j}') if flag % 2 == 0: ws[f'A{i}'].fill = headers_style.fill ws[f'A{i}'].font = headers_style.font ws[f'A{i}'].alignment = headers_style.alignment flag += 1 ws.conditional_formatting.add( f'M{i}:M{j}', ColorScaleRule(start_type='max', end_type='min', start_color='CAFFBF', end_color='FFD6A5')) wb.save(BASE_PATH.joinpath('report.xlsx'))
starter_throws.append(result[0]) ######################## # Zip up all the lists # ######################## iter_data = list( zip(opp_list, opp_names, starters_id, starters_names, date_list, starter_throws)) ######################### # Excel Workbook Stuff # ######################### wb = Workbook() # Worksheet Styles wb.add_named_style(tableBody) wb.add_named_style(tableHeader) wb.add_named_style(sheet_header) ############################ # Create tabs for each day # ############################ for i in range(0, len(iter_data)): opp_team_id = iter_data[i][0] opp_team_name = iter_data[i][1] starter_id = iter_data[i][2] starter_name = iter_data[i][3] date = iter_data[i][4] throws = iter_data[i][5]
def process_boards(self, boards): """Creates a series of worksheets in a workbook based on Trello board names.""" highlight = NamedStyle(name='highlight') highlight.font = Font(bold=True, size=20) tabletop = NamedStyle(name='tabletop') tabletop.font = Font(bold=True, color='FFFFFF') bd = Side(style='thin', color="000000", border_style='thin') tabletop.border = Border(left=bd, right=bd, top=bd, bottom=bd) tabletop.fill = PatternFill("solid", bgColor="333333") wb = Workbook() wb.add_named_style(highlight) lookup = trello.Trello() current_row = 6 # Dump the default worksheet from the document. # TODO: (PS) Is there a better way to handle this? for sheet in wb: if "Sheet" in sheet.title: wb.remove_sheet(sheet) for board in boards: if "Projects" in board['name']: ws = wb.create_sheet(title="{0}".format(board['name']), index=0) ws.sheet_properties.tabColor = "0000FF" elif "Break Fix" in board['name']: ws = wb.create_sheet(title="{0}".format(board['name']), index=1) ws.sheet_properties.tabColor = "FF0000" elif "Change Control" in board['name']: ws = wb.create_sheet(title="{0}".format(board['name']), index=2) ws.sheet_properties.tabColor = "228B22" else: ws = wb.create_sheet(title="{0}".format(board['name'][0:30]), index=None) ws['A1'].style = 'highlight' ws['A1'] = "{0}".format(board['name']) ws['A2'] = "" # was going to contain board descriptions. Trello have deprecated these, just not from the API ws['A3'] = "{0}".format(board['url']) ws['A3'].style = 'Hyperlink' ws['A4'] = "" headings = [ "Name", "Description", "Status", "Due Date", "Complete", "Perc", "Members" ] ws.append(headings) header_row = ws[5] for cell in header_row: cell.style = tabletop cards = lookup.cards(board['shortLink']) # Apply some default column widths to each worksheet ws.column_dimensions["A"].width = 40 ws.column_dimensions["B"].width = 100 ws.column_dimensions["C"].width = 10 ws.column_dimensions["D"].width = 22 ws.column_dimensions["G"].width = 45 for card in cards: # TODO: Pretty slow to iterate like this. Improve. listname = lookup.lists(card['idList']) member_list = "" for member in card['members']: member_list += "{0},".format(member['fullName']) member_list.replace(',', ', ') ws["A{0}".format(current_row)] = card['name'] ws["A{0}".format(current_row)].style = 'Output' ws["B{0}".format(current_row)] = card['desc'] ws["C{0}".format(current_row)] = listname['name'] if 'Conceptual' in listname['name']: ws["C{0}".format(current_row)].style = 'Accent5' elif 'Backlog' in listname['name']: ws["C{0}".format(current_row)].style = 'Accent4' elif 'In Progress' in listname['name']: ws["C{0}".format(current_row)].style = 'Accent1' elif 'Impeded' in listname['name']: ws["C{0}".format(current_row)].style = 'Bad' elif 'Completed' in listname['name']: ws["C{0}".format(current_row)].style = 'Good' elif 'Stopped' in listname['name']: ws["C{0}".format(current_row)].style = 'Accent2' elif 'Planned' in listname['name']: ws["C{0}".format(current_row)].style = 'Accent4' elif 'Successful' in listname['name']: ws["C{0}".format(current_row)].style = 'Good' elif 'Failed' in listname['name']: ws["C{0}".format(current_row)].style = 'Bad' elif 'Cancelled' in listname['name']: ws["C{0}".format(current_row)].style = 'Neutral' elif 'Course Development' in listname['name']: ws["C{0}".format(current_row)].style = 'Neutral' elif 'On Training' in listname['name']: ws["C{0}".format(current_row)].style = 'Accent5' elif 'Once Off Scheduled' in listname['name']: ws["C{0}".format(current_row)].style = 'Accent5' else: ws["C{0}".format(current_row)] = listname['name'] ws["D{0}".format(current_row)] = card['due'] ws["E{0}".format(current_row)] = card['dueComplete'] # ws["F{0}".format(current_row)] = card['closed'] tasks = 0 complete = 0 checklists = lookup.checklists(card['shortLink']) for checklist in checklists: for cl in checklist['checkItems']: tasks += 1 if cl['state'] == 'complete': complete += 1 if tasks > 0: perc = 100 * complete / tasks else: perc = 0 ws["F{0}".format(current_row)] = "{0}%".format(int(perc)) if perc < 25: ws["F{0}".format(current_row)].style = 'Bad' elif perc < 100: ws["F{0}".format(current_row)].style = 'Neutral' else: ws["F{0}".format(current_row)].style = 'Good' ws["G{0}".format(current_row)] = member_list[:-1] current_row += 1 current_row = 6 wb.save(self.filename)
class TestReport(object): def __init__(self, datetimenow=datetime.datetime.now()): dirname = "Logs/%s" % datetimenow.strftime("%Y-%m-%d_%H_%M_%S") if not os.path.exists(dirname): os.mkdir(dirname) self.wbname = "%s/Report.xlsx" % dirname self.wb = Workbook() ft = Font(name=u'Courier New', size=16, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') fill = PatternFill(fill_type="solid", start_color='FF88FFFF', end_color='FF008800') cellfill = PatternFill(fill_type="solid", start_color='FFFFFFFF', end_color='FF000000') bd = Border(left=Side(border_style="thin", color='FF001000'), right=Side(border_style="thin", color='FF110000'), top=Side(border_style="thin", color='FF110000'), bottom=Side(border_style="thin", color='FF110000'), diagonal=Side(border_style=None, color='FF000000'), diagonal_direction=0, outline=Side(border_style=None, color='FF000000'), vertical=Side(border_style=None, color='FF000000'), horizontal=Side(border_style=None, color='FF110000') ) alignment = Alignment(horizontal='general', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) headerstyle = NamedStyle(name="header") headerstyle.font = ft headerstyle.fill = fill headerstyle.border = bd headerstyle.alignment = alignment self.wb.add_named_style(headerstyle) cellstyle = NamedStyle(name="cell") cellstyle.font = ft cellstyle.fill = cellfill cellstyle.border = bd cellstyle.alignment = alignment self.wb.add_named_style(cellstyle) self.current_row = 0 self.chart_row = 0 self.linkbd = bd pass def save(self): self.wb.remove(self.wb.active) self.wb.save(self.wbname) def add_sheet(self, sheetname, headerlist): ws = self.wb.create_sheet(sheetname) ws.title = sheetname self.current_row = 1 self.chart_row = 0 ws.append(headerlist) for col in range(1, len(headerlist) + 1): ws.cell(self.current_row, col).style = "header" for col in ws.columns: column = col[0].column ws.column_dimensions[column].width = 28 self.current_row = self.current_row + 1 def add_reccord(self, sheetname, valuelist, scrshot=None): ws = self.wb[sheetname] ws.append(valuelist) for col in range(1, len(valuelist) + 1): ws.cell(self.current_row, col).style = "cell" if scrshot is not None: link = worksheet.hyperlink.Hyperlink(ref="", target="./%s" % scrshot) ws.cell(self.current_row, col + 1).value = "screenshot" ws.cell(self.current_row, col + 1).hyperlink = link ws.cell(self.current_row, col + 1).style = "Hyperlink" ws.cell(self.current_row, col + 1).border = self.linkbd self.current_row = self.current_row + 1 def add_chart(self, sheetname, x_col, y_col): ws = self.wb[sheetname] xtitle = ws.cell(1, x_col).value ytitle = ws.cell(1, y_col).value chart = LineChart() chart.title = "%s - %s" % (ytitle, xtitle) chart.style = 13 chart.x_axis.title = xtitle chart.y_axis.title = ytitle chart.marker = True chart.smooth = True y_data = Reference(ws, min_col=y_col, min_row=1, max_col=y_col, max_row=self.current_row) chart.add_data(y_data, titles_from_data=True) x_data = Reference(ws, min_col=x_col, min_row=2, max_col=x_col, max_row=self.current_row) chart.set_categories(x_data) s0 = chart.series[0] s0.marker.symbol = "circle" s0.smooth = True if 0 == self.chart_row: self.chart_row = self.current_row ws.add_chart(chart, "A%d" % self.chart_row) # row height is 7.5/16 cm by default self.chart_row = self.chart_row + chart.height / 7.5 * 16 + 2
class NavXlsxFile: """ navigation xlsx file """ def __init__(self): # self.cfg_file = cfg_file self.work_book = Workbook() self.current_sheet = self.work_book.active self.current_sheet.title = 'navLog' self.cursor = {'row': 1, 'col': 1} self.sheet_style = NamedStyle(name='sheet_style') self.sheet_style.alignment = Alignment(horizontal='center', vertical='center') bd = Side(style='thin', color="000000") # self.sheet_style.border = Border(left=bd, top=bd, right=bd, bottom=bd) self.border = Border(left=bd, top=bd, right=bd, bottom=bd) self.work_book.add_named_style(self.sheet_style) def createSheet(self, sheet_name): work_sheet = self.work_book.create_sheet(sheet_name, 0) self.selectSheet(sheet_name) return work_sheet def selectSheet(self, sheet_name): self.current_sheet = self.work_book[sheet_name] return self.current_sheet def setCursor(self, row, col): if row >= 1 and col >= 1: self.cursor['row'] = row self.cursor['col'] = col def writeCell(self, row, col, value): work_sheet = self.current_sheet work_sheet.cell(row, col, value).style = self.sheet_style def resize(self, sheet_name): """ resize sheet unit automotive """ try: for col in range(self.current_sheet.max_column): col_width = 0 for row in range(self.current_sheet.max_row): cell_value = self.current_sheet.cell(row + 1, col + 1).value if len(str(cell_value)) > col_width: col_width = len(str(cell_value)) self.current_sheet.column_dimensions[get_column_letter(col+1)].width\ = float(col_width + 3.0) except TypeError: print(self.current_sheet.max_column) def create(self, file_path): """ create .xlsx file in file system """ try: self.work_book.save(file_path) except: print('create xlsx file faild!') sys.exit() def setCellColor(self, sheet_name, row, col, color): ws = self.work_book[sheet_name] cell = ws.cell(row, col) fill = PatternFill("solid", fgColor=color) cell.fill = fill def setCellBorder(self, sheet_name, begin_row, begin_col, end_row, end_col): ws = self.work_book[sheet_name] cells = [(cell_row, cell_col) for cell_row in range(begin_row, end_row + 1) for cell_col in range(begin_col, end_col + 1)] for cur_cell in cells: ws.cell(cur_cell[0], cur_cell[1]).border = self.border def mergeCell(self, sheet_name, begin_row, end_row, begin_col=1, end_col=3): ws = self.work_book[sheet_name] cells = [(cell_row, cell_col) for cell_col in range(begin_col, end_col + 1) for cell_row in range(begin_row, end_row + 1)] for cur_col in range(begin_col, end_col + 1): cells = [(cell_row, cur_col) for cell_row in range(begin_row, end_row + 1)] merge_flag = 0 blank_index = 0 cell_index = 0 while cell_index < len(cells): cur_cell = cells[cell_index] if ws.cell(cur_cell[0], cur_cell[1]).value != None: pass if merge_flag == 0: merge_flag = 1 else: merge_flag = 0 if blank_index > 0: ws.merge_cells(start_row=cur_cell[0] - (blank_index + 1), start_column=cur_cell[1], end_row=cur_cell[0] - 1, end_column=cur_cell[1]) cell_index -= 1 blank_index = 0 else: cell_index -= 1 else: pass blank_index += 1 if cell_index == len(cells) - 1: if merge_flag > 0: ws.merge_cells(start_row=cur_cell[0] - blank_index, start_column=cur_cell[1], end_row=cur_cell[0], end_column=cur_cell[1]) cell_index += 1
def analyseReport(self): data = [] wb = Workbook() ws = wb.active ws.title = "Polarity" ws.append(["Category", "Positive Polarity", "Positive", "Negative"]) for k, v in self.dictionary.items(): # print("Key : ", k, "\t Values : " , v) data.append(self.calculatePolarity(k)) ws.append(self.calculatePolarity(k)) for d in data: print(d) # heading style heading_style = NamedStyle(name="heading_style") heading_style.font = Font(bold=True, color='FF000000') heading_style.alignment = Alignment(horizontal='center', vertical='top', wrap_text=True) heading_style.fill = PatternFill(patternType='solid', start_color='FFF300', end_color='FFF300') heading_style.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) wb.add_named_style(heading_style) # first column style data_style = NamedStyle(name="data_style") data_style.font = Font(bold=True, color='FF000000') data_style.alignment = Alignment(horizontal='left', vertical='top', wrap_text=True) data_style.fill = PatternFill(patternType='solid', start_color='FFBD4A', end_color='FFBD4A') data_style.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) wb.add_named_style(data_style) # cell style common_style = NamedStyle(name="common_style") common_style.alignment = Alignment(horizontal='center', vertical='top', wrap_text=True) common_style.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) wb.add_named_style(common_style) # setting column width ws.column_dimensions["A"].width = 20 ws.column_dimensions["B"].width = 20 ws.column_dimensions["C"].width = 20 ws.column_dimensions["D"].width = 20 # applying style i, j = 0, 0 for row in ws.rows: j = 0 for cell in row: cell_name = re.findall('\w+\d+', str(cell))[0] if i == 0: ws[cell_name].style = heading_style elif i > 0 and j == 0: ws[cell_name].style = data_style else: ws[cell_name].style = common_style j += 1 i += 1 newfilename = "Polarity_Analysis_" + self.modelname + ".xlsx" wb.save(newfilename) os.startfile(newfilename)
def generate_excel(slots=None, query_course_list=None, query_teacher_list=None, query_section_list=None): if not slots: return None day_list = [ "MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY" ] wb = Workbook() wb.remove(wb['Sheet']) wb.add_named_style(STYLE_SLOT_NORMAL) wb.add_named_style(STYLE_SLOT_VENUE) wb.add_named_style(STYLE_SLOT_TIMESLOT) for daynum in range(len(day_list)): current_sheet = wb.create_sheet(day_list[daynum]) # layout current_sheet.print_options.horizontalCentered = True current_sheet.print_options.verticalCentered = True current_sheet.page_setup.orientation = current_sheet.ORIENTATION_LANDSCAPE current_sheet.page_setup.paperSize = current_sheet.PAPERSIZE_LETTER current_sheet.page_setup.fitToPage = True current_sheet.page_setup.fitToWidth = 1 current_sheet.page_setup.fitToHeight = 1 # margins current_sheet.page_margins.bottom = current_sheet.page_margins.left = current_sheet.page_margins.right = current_sheet.page_margins.header = 0.25 current_sheet.page_margins.top = 0.5 # headers current_sheet.oddHeader.center.text = "&[Tab]" current_sheet.oddHeader.center.size = 20 current_sheet.oddHeader.center.font = "Arial" current_sheet.oddHeader.left.text = "ModularTable" current_sheet.oddHeader.left.size = 20 current_sheet.oddHeader.left.font = "Arial" current_sheet.oddHeader.right.text = "Tarun Kumar" current_sheet.oddHeader.right.size = 20 current_sheet.oddHeader.right.font = "Arial" current_slots = slots.filter(daynum=daynum + 1) timeslot_list = current_slots.values( 'timeslot__starttime', 'timeslot__endtime').distinct().order_by('timeslot') venue_list = current_slots.values( 'venuenum', 'venuenum__venuename').distinct().order_by('venuenum') for row in current_sheet.iter_rows(min_col=2, max_col=len(timeslot_list) + 1, max_row=1): for i, cell in enumerate(row): cell.style = 'TIMESLOT' cell.value = f"{timeslot_list[i]['timeslot__starttime']} - {timeslot_list[i]['timeslot__endtime']}" for row_count, row in enumerate( current_sheet.iter_rows(min_col=1, max_col=len(timeslot_list) + 1, min_row=2, max_row=len(venue_list) + 1)): row[0].style = 'VENUE' row[0].value = current_slots[ row_count * (len(timeslot_list))].venuenum.venuename for cell_count, cell in enumerate(row[1:]): current_slot = current_slots[(row_count * (len(timeslot_list))) + cell_count] cell.style = 'NORMAL' if not current_slot.teachernum: continue if (query_teacher_list is None or current_slot.teachernum.teachernum in query_teacher_list) and ( query_course_list is None or current_slot.coursenum.coursenum in query_course_list) and ( query_section_list is None or current_slot.sectionnum.sectionnum in query_section_list): cell.value = f"{current_slot.teachernum.teachername}\n{current_slot.sectionnum.semester}{current_slot.sectionnum.section}\n{current_slot.coursenum.coursecode}" for i in range(2, len(timeslot_list) + 2): current_sheet.column_dimensions[get_column_letter(i)].width = 24 for i in range(2, len(venue_list) + 2): current_sheet.row_dimensions[i].height = 60 current_sheet.print_area = f'A1:{get_column_letter(len(timeslot_list)+1)}{len(venue_list)+1}' return save_virtual_workbook(wb)
# ws3 = wb.create_sheet(title="Data") # # for row in range(10, 20): # # for col in range(27, 54): # # _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col))) # print(ws2['AA10'].value) # wb.save(filename = '3sheet.xlsx') # wb = load_workbook(filename = '3sheet.xlsx') # sheet_ranges = wb['first'] # print(sheet_ranges['B1'].value) # #Edit Page Setup wb=Workbook() ws=wb.active ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID ws.page_setup.fitToHeight = 0 ws.page_setup.fitToWidth = 1 #Creating a Named Style highlight = NamedStyle(name="highlight") highlight.font = Font(bold=True, size=20) bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) #use st yle wb.add_named_style(highlight) ws['A1'].style = highlight ws['D5'].style = 'highlight' wb.save(filename = 'stylename1.xlsx')