def generate(self): self.activate() repBook = Workbook() currentSheet = repBook.active for row in self.sheet.values: if row[0] == "Tiempo": currentSheet.append(["Tiempo", "Sensor 1" , "Sensor 2" , "Sensor 3" , "Sensor 4" ]) else: a = row[0].split()[0].split('-') print(self.fmes.get()) if int(a[0]) < int(self.fanio.get())-2000: pass elif int(a[0]) > int(self.sanio.get())-2000: break elif int(a[1]) < int(self.fmes.get()): pass elif int(a[1]) > int(self.smes.get()): break elif int(a[2]) < int(self.fdia.get()): pass elif int(a[2]) > int(self.sdia.get()): break else: currentSheet.append(row) repBook.save("Reporte.xlsx")
def write(self): self.workbook = Workbook() self.sheet = self.workbook.get_active_sheet() if not len(self.header): raise BRException("Header not provided") if self.data and len(self.header) != len(self.data[0]): raise BRException("Header and Data must be in same format") # Write headers row = 1 col = 1 for col_header in self.header: self.sheet.cell(row=row, column=col, value=col_header) col += 1 row += 1 # Now write data for data_row in self.data: col = 1 for col_value in data_row: col_value = col_value if col_value else '' self.sheet.cell(row=row, column=col, value=col_value) col += 1 row += 1 if self.response: self.workbook.save(self.response) else: if self.file_name: self.workbook.save(self.file_name)
def createOutputExcel(outputData): wb = Workbook() ws = wb.active ws.title = 'Budget Projection' columnNames = [ "Name", "SA Rate", "Deltek Rate", "Avg Run", "Hours", "Dollars" ] for w in range(1, nTotalNumWeeks + 1): nThYear = int(w / 54) nYear = 2020 + int(nThYear) nWeek = w - (53 * nThYear) columnNames.append( "Week " + str(nWeek) + " \n[" + date.fromisocalendar(nYear, nWeek, 7).strftime("%m/%d/%y") + "]") ws.freeze_panes = 'G3' headerCnt = len(columnNames) for cells in ws.iter_rows(1, 1, 1, headerCnt): for cell in cells: cell.value = columnNames[cell.column - 1] cell.alignment = Alignment(wrapText=True, horizontal='center') rowNum = 2 rowCnt = len(outputData) + 1 for name, stats in outputData.items(): for cells in ws.iter_rows(rowNum, rowNum, 1, headerCnt): cells[0].value = name ws.column_dimensions[cells[0].column_letter].width = 25 cells[1].value = stats['SpringAhead Rate'] cells[1].number_format = '"$"#' ws.column_dimensions[cells[1].column_letter].width = 8 cells[2].value = stats['Deltek Rate'] cells[2].number_format = '"$"#' ws.column_dimensions[cells[2].column_letter].width = 8 cells[3].value = stats['Avg Run'] cells[3].number_format = '0.00' ws.column_dimensions[cells[3].column_letter].width = 7 cells[4].value = stats['Hours'] cells[4].number_format = '#,##0.00' ws.column_dimensions[cells[4].column_letter].width = 9 cells[5].value = stats['Dollars'] cells[5].number_format = '"$"#,##0.00' ws.column_dimensions[cells[5].column_letter].width = 14 for w in range(1, nTotalNumWeeks + 1): cells[w + 5].value = float(stats[w]) if rowNum > rowCnt: cells[w + 5].number_format = '"$"#,##0.00' else: cells[w + 5].number_format = '#,##0.00' ws.column_dimensions[cells[w + 5].column_letter].width = 12 ws.column_dimensions[cells[w + 5].column_letter].height = 29 rowNum = rowNum + 1 setExcelFormulas(ws, rowCnt, headerCnt) wb.save(outputFile)
def updateScriptFileAndCaseResult(self, workB: Workbook, testCaseResult: TestCaseResult, caseResult): workB.save(self.CurrentScriptPath) logging.info('save scenario file: "{0}" successfully'.format( self.CurrentScriptPath)) testCaseResult.CaseResult = caseResult testCaseResult.EndTime = Tool.CurrentTime() self.testPlanResult.TestCaseResultCollection.append(testCaseResult)
def _xlsx_save(self, result: [dict], file_name: str): """data is saved in excel format""" wb = Workbook() sheet = wb.active for i, ldict in enumerate(result, 1): if i == 1: title_list: list = list(ldict.keys()) sheet.append(title_list) sheet.append(list(ldict.values())) wb.save(fr"{file_name}.xlsx")
def btnDownloadClicked(self): result_file = Workbook() sheet1 = result_file.active sheet1.title = 'result' idx = ["업무(결과값)", "수행 내용", "이름", "직급", "직급 NO."] for i in range(5): sheet1.cell(1, i+1).value = idx[i] for cell in self.result: sheet1.append(cell) result_file.save('./result' + datetime.today().strftime("%Y%m%d%H%M%S") + '.xlsx') QtWidgets.QMessageBox.about(self, "저장완료", "파일이 저장되었습니다.")
def checkHolds(port, driver): driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ0087_00000:_idsc00001:_idsc00003_6:_idsc00007").click() # driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ00Q4_00000:_idsc00001:portId").send_keys(port) driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ00Q4_00000:_idsc00001:_idsc00020").click() onHold = [] bonds = [] done = False j=2 driver.implicitly_wait(0) try: driver.find_element_by_css_selector("span[title='Page 1']").click() except: pass driver.implicitly_wait(60) while not done: cells = driver.find_elements_by_css_selector("table[class='datat']>tbody>tr>td") i=0 while i<min(1500, len(cells)): driver.execute_script("arguments[0].scrollIntoView();", cells[i]) # bonds.append(cells[i+3].text) # if not todaysDate in cells[i+10].text: if "Y" in cells[i+14].text: onHold.append(cells[i+3].text) # cells[i+3].find_element_by_tag_name("input").send_keys(port) i+=15 # else: # break driver.implicitly_wait(0) try: driver.find_element_by_css_selector("span[title='Page " +str(j)+ "']").click() j+=1 except: done=True driver.implicitly_wait(60) if len(bonds)>0: filepath = r"J:\INBOND CLOSURE TRACKING\Open Bonds - " + datetime.now().strftime("%Y-%m-%d %H-%M-%S") + ".xlsx" wb = Workbook() ws = wb.active for i in range(len(bonds)): ws.cell(row=i+1, column=1).value = bonds[i] wb.save(filepath) if len(onHold)>0: print("IN-BOND #S ON HOLD:") holdString = "IN-BOND #S ON HOLD:\n" for bond in onHold: print(bond) holdString = holdString+bond + "\n" HelperFunctions.popUpOK(holdString)
def __write_wb(self, wb_name, worksheet_data): dest_wb = Workbook(write_only=True) for ws_name, ws_data in worksheet_data.iteritems(): if ws_data == []: continue ws = dest_wb.create_sheet(title=ws_name) ws_data_transposed = zip(*ws_data) for row in ws_data_transposed: ws.append(row) dest_wb.save(os.path.join(self.output_dir, wb_name))
def convert_to_xlxs(content: Any) -> Workbook: """Convert old workbook formats xls into xlxs""" xlsBook = xlrd.open_workbook(file_contents=content) workbook = Workbook() for i in range(0, xlsBook.nsheets): xlsSheet = xlsBook.sheet_by_index(i) sheet = workbook.active if i == 0 else workbook.create_sheet() sheet.title = xlsSheet.name for row in range(0, xlsSheet.nrows): for col in range(0, xlsSheet.ncols): sheet.cell(row=row + 1, column=col + 1).value = xlsSheet.cell_value( row, col) return workbook
class ExcelPipeline(object): def __init__(self): self.wb = Workbook() self.ws = self.wb.active self.ws.append(["奖项类型", "获奖序号", "项目名称", "主要完成单位", "主要完成人", "获奖证书编号", "奖种", "等级"]) def process_item(self, item, spider): line = [ item.get("type"), item.get("asequence"), item.get("pname"), item.get("munit"), item.get("mperson"), item.get("anumber"), item.get("awardtype"), item.get("rank") ] self.ws.append(line) self.wb.save("landandresourceaward.xlsx") return item
def build_excel_response(field_names,data,filename): try: wb = Workbook(write_only=True) ws = wb.create_sheet() ws.append(field_names) for item in data: ws.append(item.values()) s = StringIO() wb.save(s) response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = u'attachment; filename={}.xlsx'.format(filename) response['Content-Length'] = s.len response.write(s.getvalue()) s.close() return response except Exception, exc: return build_exception_response()
def write_xlsx(self, file: Union[str, BinaryIO]) -> None: """ Write the contents in XLSX (Excel) format to a file. Args: file: filename or file-like object """ if XLSX_VIA_PYEXCEL: # use pyexcel_xlsx data = self._get_pyexcel_data(convert_for_openpyxl) pyexcel_xlsx.save_data(file, data) else: # use openpyxl # Marginal performance gain with write_only. Does not automatically # add a blank sheet wb = XLWorkbook(write_only=True) valid_name_dict = self.get_pages_with_valid_sheet_names() for page, title in valid_name_dict.items(): ws = wb.create_sheet(title=title) page.write_to_openpyxl_xlsx_worksheet(ws) wb.save(file)
def store2xlsx(self): ''' select company info and store to xlsx ''' odb = OperateDB() # current date datee = datetime.datetime.now().strftime("%Y-%m-%d") fileAbsPath = self.filePath.replace('.xlsx', datee + '.xlsx') wb = Workbook() sheet = wb.active # print sheet.max_row # 设置单元格所有框线 thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side( style='thin')) # 写标题 for i in range(self.__indexName.__len__()): _ = sheet.cell(column=i + 1, row=1, value=self.__indexName[i]) stockInfos = odb.selectFromCompany(MyUtil.selectCompany.replace('%s', MyUtil.indexs)) keyList = stockInfos.keys() # 按照公司代码排序 keyList.sort() # print keyList # 第一行为标题,从第二行开始为实际数据 rowNum = 2 for i in keyList: for j in range(len(stockInfos[i])): _ = sheet.cell(column=j + 1, row=rowNum, value=stockInfos[i][j]) sheet.cell(column=j + 1, row=rowNum).border = thin_border rowNum += 1 # 每100条保存一下 if int(i) % 100 == 0: wb.save(fileAbsPath) # 最后保存一下 wb.save(fileAbsPath)
def create(cls) -> ExcelSheet: """Creates an excel worksheet from nothing Factory method to initialize an object Returns ------- excel worksheet: an opened new and empty excel worksheet object """ wb = Workbook() return cls(wb)
def convert_to_xlxs(fh: BinaryIO) -> Optional[Workbook]: """Convert old workbook formats xls into xlxs""" if not _HAVE_XLRD: logger.error("xlrd module not installed. Cannot convert XLS file.") return None file_content = fh.read() fh.seek(0) xlsBook = xlrd.open_workbook(file_contents=file_content) workbook = Workbook() for i in range(0, xlsBook.nsheets): xlsSheet = xlsBook.sheet_by_index(i) sheet = workbook.active if i == 0 else workbook.create_sheet() sheet.title = xlsSheet.name for row in range(0, xlsSheet.nrows): for col in range(0, xlsSheet.ncols): sheet.cell(row=row + 1, column=col + 1).value = xlsSheet.cell_value(row, col) return workbook
class ExcelFileWriter(Writter): def __init__(self, data, header=[], file_path=None, *args, **kwargs): super(ExcelFileWriter, self).__init__(data=data, header=header, file_path=file_path, *args, **kwargs) def write(self): self.workbook = Workbook() self.sheet = self.workbook.get_active_sheet() if not len(self.header): raise BRException("Header not provided") if self.data and len(self.header) != len(self.data[0]): raise BRException("Header and Data must be in same format") # Write headers row = 1 col = 1 for col_header in self.header: self.sheet.cell(row=row, column=col, value=col_header) col += 1 row += 1 # Now write data for data_row in self.data: col = 1 for col_value in data_row: col_value = col_value if col_value else '' self.sheet.cell(row=row, column=col, value=col_value) col += 1 row += 1 if self.response: self.workbook.save(self.response) else: if self.file_name: self.workbook.save(self.file_name)
def store2xlsx(self): ''' select company info and store to xlsx ''' odb = OperateDB() # current date datee = datetime.datetime.now().strftime("%Y-%m-%d") fileAbsPath = self.filePath.replace('.xlsx', datee + '.xlsx') wb = Workbook() sheet = wb.active # print sheet.max_row # 设置单元格所有框线 thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) # 写标题 for i in range(self.__indexName.__len__()): _ = sheet.cell(column=i + 1, row=1, value=self.__indexName[i]) stockInfos = odb.selectFromCompany( MyUtil.selectCompany.replace('%s', MyUtil.indexs)) keyList = stockInfos.keys() # 按照公司代码排序 keyList.sort() # print keyList # 第一行为标题,从第二行开始为实际数据 rowNum = 2 for i in keyList: for j in range(len(stockInfos[i])): _ = sheet.cell(column=j + 1, row=rowNum, value=stockInfos[i][j]) sheet.cell(column=j + 1, row=rowNum).border = thin_border rowNum += 1 # 每100条保存一下 if int(i) % 100 == 0: wb.save(fileAbsPath) # 最后保存一下 wb.save(fileAbsPath)
directoryDestination.mkdir(exist_ok = True) bigDict = {} indexDictionary = {} columnHeaders = [] isListBuilt = False wb: Workbook ws: Worksheet workbook_filename: str = 'jsonData.xlsx' try: wb = openpyxl.load_workbook(workbook_filename) ws = wb['data'] except FileNotFoundError: print('Workbook not found, creating new workbook') wb = Workbook() ws = wb.active ws.title = 'data' wb.save('jsonData.xlsx') # program options options = ['movement','pull data','push data'] for index, operation in enumerate(options): print(str(index) + ': ' + operation) # input operationIndex: str = input("Pick operation (enter digit):") print("\n") print("\n") directoryToCrawl: Path
def __init__(self, name: str): self.name = name if not os.path.exists(name): save_workbook(Workbook(), name) self._wb = xl.load_workbook(name, keep_vba=True)
def createExcelFile(Path, sheetName): try: wb = Workbook(Path) ws = wb[sheetName] wb.close() except: wb = Workbook() ws = wb.create_sheet(sheetName) wb.save(Path) wb = openpyxl.load_workbook(Path) wb.remove(wb['Sheet']) wb.save(Path) wb.close()
def __init__(self): self.wb = Workbook() self.ws = self.wb.active self.ws.append(["奖项类型", "获奖序号", "项目名称", "主要完成单位", "主要完成人", "获奖证书编号", "奖种", "等级"])
for origin_file in origin_folder_glob: origin_file_name = origin_file[len(origin_folder) + 1:] input_file_name = locals()['origin_file_name'][:-4] + "_input.txt" input_file = origin_folder + "_inputs/" + input_file_name prepare_tbschema_file(origin_file, input_file) server_name = origin_file_name.split('-')[1] database_name = origin_file_name.split('-')[2][:-4] if server_name not in list(servers_dictionnary.keys()): servers_dictionnary[server_name] = Server(server_name, database_name, input_file) else: servers_dictionnary[server_name].add_database_to_dictionnary( database_name, input_file) excel_file = Workbook() ws = excel_file.active ws.title = "Summary" ws.cell(row=1, column=1, value='ACTION') ws.cell(row=1, column=2, value='COMPL.') ws.cell(row=1, column=3, value='DB') ws.cell(row=1, column=4, value='TABLE') tbl_cat_dict = {} idx_cat_dict = {} rvk_cat_dict = {} grt_cat_dict = {} server_column = 5 line_number = 2
def validate_sheet_names( *, wb: Workbook, filename: Union[Path, str], sheet_name: str, sheet_names: List[str], start_time: float, original_stdout: io.TextIOWrapper, output_url: str ) -> Workbook: """ Parameters ---------- wb : Workbook A workbook. filename : Union[Path, str] The file containing the workbook. sheet_name : str A sheet name in the workbook. sheet_names : List[str] The sheet names in the workbook. start_time : float The start time of the script. original_stdout : io.TextIOWrapper The buffered text stream for the html output. output_url : str The html filename. Returns ------- wb : Workbook The workbook with a revised sheetname. Example ------- >>> import datasense as ds >>> wb = validate_sheet_names( >>> wb=wb, >>> filename=filename, >>> sheet_name=sheet_name, >>> sheet_names=sheet_names, >>> start_time=start_time, >>> original_stdout=original_stdout, >>> output_url=output_url >>> ) """ if sheet_name not in sheet_names and len(sheet_names) != 1: print('Manually rename one of these sheets:') print(wb.sheetnames) print('Then re-run script') print('XXX File NOT OK XXX') stop_time = time.time() report_summary( start_time=start_time, stop_time=stop_time ) exit_script( original_stdout=original_stdout, output_url=output_url ) elif sheet_name not in sheet_names and len(sheet_names) == 1: print('One sheet found and it was re-named.') ws = wb.active ws.title = sheet_name wb.save(filename=filename) elif sheet_name in sheet_names and len(sheet_names) != 1: sheet_names_removed = [x for x in sheet_names if x != sheet_name] for sheet in sheet_names_removed: wb.remove(worksheet=wb[sheet]) print('Sheet names removed:') print(sheet_names_removed) wb.save(filename=filename) return wb
def __init__(self, archive): self.archive = archive self.wb = Workbook() self.sheets = [] self.rels = get_dependents(self.archive, ARC_WORKBOOK_RELS)
# if no from_loc given, assume last sheet if from_loc is None: from_loc = len(sheets) - 1 # if no to_loc given, assume first if to_loc is None: to_loc = 0 sheet = sheets.pop(from_loc) sheets.insert(to_loc, sheet) if __name__ == "__main__": try: ato_report = filedialog.askopenfilename(title="ato_report", filetypes=[("All files", "*")]) storage_report = filedialog.askopenfilename(title="storage_report", filetypes=[("All files", "*")]) summary_workbook = Workbook() ws = summary_workbook.active ws.title = "Summary" summary_workbook.save("Summary.xlsx") ato = ATO_check(ato_report, storage_report) ato.start_to_run() input("Press <Enter>") except Exception as err: messagebox.showerror("Warning!", err) with open(os.path.join(os.getcwd(), "error.txt"), "w+") as f: traceback.print_exc(file=f) print(err) exit()
except: print _u html = etree.HTML(content) _as = html.xpath("//table[@id='detail']//a") for _a in _as: title = _a.xpath('string(.)').strip() if title: _href = ''.join(_a.xpath('@href')) _store.append((title, _href, str(i) + '_'.encode('utf-8') + _u.encode('utf-8'))) print len(_store) wb = Workbook() sheet = wb.active sheet.cell(row=1, column=1, value='标题') sheet.cell(row=1, column=2, value='链接') sheet.cell(row=1, column=3, value='出处') _titles = map(lambda x: x[0], _store) _links = map(lambda x: urljoin('http://admission.ucas.ac.cn/', x[1]), _store) for i in range(len(_titles)): sheet.cell(row=i + 2, column=1, value=_titles[i]) sheet.cell(row=i + 2, column=2, value=_links[i]) sheet.cell(row=i + 2, column=3, value=_store[i][2].encode('utf-8')) wb.save('kejidaxue3.xlsx')
def write_infos(self, infos: List[Info]) -> None: 'atomically write all the infos' def clean(descrizione: str) -> str: for prefix in ('AD.COM.LE DA TR. NEL ', 'AD.REG.LE DA TR. NEL ', 'TICKET PASTO'): if descrizione.startswith(prefix): return f'{prefix}*' return descrizione # collect all details of all infos: details = list( sorted({ clean(additional_detail.descrizione) for info in infos for additional_detail in info.additional_details })) # there are 1 + len(keys)-1 + len(details) columns header: List[Tuple[E, str]] = [('month', NUMBER_FORMAT_TEXT)] for column_header in ColumnHeader: if column_header is not ColumnHeader.detail: header.append((column_header.name, NUMBER_FORMAT_TEXT)) else: for detail in details: header.append((detail, NUMBER_FORMAT_TEXT)) rows: List[List[Tuple[E, str]]] = [header] for info in infos: # group columns by column_header columns = {column.header: column for column in info.columns} # group additional_details by descrizione additional_details = { clean(additional_detail.descrizione): additional_detail for additional_detail in info.additional_details } row: List[Tuple[E, str]] = [(info.when, NUMBER_FORMAT_DATE)] for column_header in ColumnHeader: if column_header is not ColumnHeader.detail: row.append((columns[column_header].howmuch, VALUE_NUMBER_FORMAT[column_header] ) if column_header in columns else ( None, NUMBER_FORMAT_TEXT)) else: for detail in details: # cosa esportare nell'xls? row.append((additional_details[detail].competenze - additional_details[detail].trattenute, NUMBER_FORMAT_NUMBER ) if detail in additional_details else ( None, NUMBER_FORMAT_TEXT)) rows.append(row) widths: Dict[str, int] = {} for row in rows: for i, cell in enumerate(row, start=1): column_letter = get_column_letter(i) widths[column_letter] = max(widths.get(column_letter, 0), 2 * len(str(cell[0]))) workbook = Workbook(write_only=True) try: # create the main sheet sheet = workbook.create_sheet('main') # first set the width of the columns for column_letter, width in widths.items(): sheet.column_dimensions[column_letter].width = width # then add the data for row in rows: sheet.append([ self._cell(sheet, value, number_format) for value, number_format in row ]) finally: workbook.save(self.name)
def index(request): place = "McDonald's" state = StateProvince.objects.get(code="MG") # 1. Filtragem por precisão e duração. # Eliminar registros com precisão maior que 100m e duração maior que 90min. visits = Visit.objects.filter( precision__lt=100, total_time_in_min__lt=90, place__name=place, place__address__city__state__code=state) count_visits_total = visits.count() count_users_total = visits.values('user_id').annotate( count=Count('user_id')).count() infos = dict() series_days_of_week = [ {'name': 'Sunday', 'values':[]}, {'name': 'Monday', 'values':[]}, {'name': 'Tuesday', 'values':[]}, {'name': 'Wednesday', 'values':[]}, {'name': 'Thursday', 'values':[]}, {'name': 'Friday', 'values':[]}, {'name': 'Saturday', 'values':[]} ] series_day_periods = [ {'name': 'Manhã', 'values':[]}, {'name': 'Almoço', 'values':[]}, {'name': 'Tarde', 'values':[]}, {'name': 'Noite', 'values':[]}, {'name': 'Madrugada', 'values':[]}, ] for visit in visits: key = "{}-{}".format(visit.place, visit.place.address.zip_code) if not infos.get(key): _visits=visits.filter(place=visit.place) item = dict( visit=visit # 2. Contagem de visitas e usuários aos PDVs. ,count_visits=_visits.count() ,count_users=_visits.values( 'user_id').annotate(count=Count('user_id')).count() # 3. Criação de visualização dos dias da semana das visitas aos PDVs. ,count_days_of_week=[ {week_day[1]:_visits.filter(arrival__week_day=week_day[0]).count()} for week_day in Visit.DAYS_OF_WEEK ] # 4. Criação de visualização dos períodos do dia das visitas aos PDVs. (Manhã, Almoço, Tarde, Noite) ,count_day_periods=[ {day_period[0]:_visits.filter( arrival__time__range=(day_period[1], day_period[2])).count()} for day_period in Visit.DAY_PERIOD] # 5. Criação de visualização de visitas antes e visitas depois de visitas aos PDVs ) infos[key] = item # generate series to chart days of week if item.get('count_visits') > 100: for dayweek in item.get('count_days_of_week'): for serie in series_days_of_week: if serie['name'] in dayweek: _values = list(dayweek.items()) serie['values'].append(_values[0][1]) for dayperiod in item.get('count_day_periods'): for serie in series_day_periods: if serie['name'] in dayperiod: _values = list(dayperiod.items()) serie['values'].append(_values[0][1]) context = dict( infos=infos ,count_visits=count_visits_total ,count_users=count_users_total ,series_days_of_week=series_days_of_week ,series_day_periods=series_day_periods ) if 'reports' in request.POST: wb = Workbook() wb.remove_sheet(wb.worksheets[0]) sh = wb.create_sheet('vivists') sh.append([ '_ID' ,'EUID' ,'ARRIVAL' ,'DEPARTURE' ,'PDV' ,'STREET' ,'POSTCODE' ,'SUBURB' ,'CITY' ,'VISITS' ,'CUSTOMERS' ,'COUNT_DAY_PERIODS' ,'COUNT_DAYS_OF_WEEK' ]) for item in infos.values(): # import pdb ; pdb.set_trace() visit = item.get('visit') sh.append([ visit.code ,visit.user_id ,visit.arrival.strftime('%d/%m/%Y') ,visit.departure.strftime('%d/%m/%Y') ,visit.pdv ,visit.place.address.street or '' ,visit.place.address.zip_code or '' ,visit.place.address.suburb or '' ,visit.place.address.city.name ,item.get('count_visits') ,item.get('count_users') ,json.dumps(item.get('count_day_periods')) ,json.dumps(item.get('count_days_of_week')) ]) response = HttpResponse(save_virtual_workbook( wb), content_type="application/vnd.ms-excel") response['Content-Disposition'] = "attachment; filename=visits.xlsx" return response return render(request, 'index.html', context)
def cell_style( *, wb: Workbook, style_name: str = 'cell_style', font_name: Optional[str] = 'Lucida Sans', font_size: Optional[int] = 11, font_bold: Optional[bool] = True, font_colour: Optional[str] = '000000', horizontal_alignment: Optional[str] = 'center', vertical_alignment: Optional[str] = 'center', wrap_text: Union[str, bool] = None, fill_type: Union[str, bool] = 'solid', foreground_colour: Union[str, bool] = 'd9d9d9', border_style: Union[str, bool] = None, border_colour: Union[str, bool] = None, number_format: Union[str, bool] = None ) -> NamedStyle: """ Define a cell style Parameters ---------- wb : Workbook The workbook in which to define the cell style. style_name : str = 'cell_style' The name for the cell style. font_name : Optional[str] = 'Lucida Sans' The font name for the style. font_size : Optional[int] = 11 The font size for the style. font_bold : Optional[bool] = True A boolean or string to apply bold style. font_colour : Optional[str] = 'ffffff' The string for the font colour. horizontal_alignment : Optional[str] = 'center' The string for horizontal alignment. vertical_alignment : Optional[str] = 'center' The string for vertical alignment. wrap_text : Union[str, bool] = None A boolean or string to wrap text. fill_type : Optional[str] = 'solid' The string for the fill type. foreground_colour : Optional[str] = 'd9d9d9' The string for the foreground colour. border_style : Union[str, bool] = None A boolean or string to apply a border. border_colour : Union[str, bool] = None A boolean or string to apply a border colour. number_format : Union[str, bool] = None A boolean or string to apply a number format. Returns ------- row_style : NamedStyle The named style. Example ------- >>> red_cell_style = ds.cell_style( >>> style_name='red_cell_style', >>> font_colour='ffffff', >>> foreground_colour='c00000' >>> ) >>> wb.add_named_style(red_cell_style) >>> for cell in ['C1', 'D1', 'E1']: >>> ws[cell].style = red_cell_style """ cell_style = NamedStyle(name=style_name) cell_style.font = Font( name=font_name, size=font_size, bold=font_bold, color=font_colour ) cell_style.alignment = Alignment( horizontal=horizontal_alignment, vertical=vertical_alignment, wrap_text=wrap_text ) cell_style.fill = PatternFill( fill_type=fill_type, fgColor=foreground_colour ) cell_style.border = Border( bottom=Side( border_style=border_style, color=border_colour ) ) cell_style.number_format = number_format wb.add_named_style(cell_style) return (wb, cell_style)
driver.find_element(By.XPATH, ".//input[contains(@id,'search')]").send_keys("iphone") driver.find_element(By.XPATH, ".//input[contains(@value,'Go')]").click() sleep(3) phones = driver.find_elements(By.XPATH, ".//span[contains(@class,'a-color-base a-text-normal')]") prices = driver.find_elements(By.XPATH, ".//span[contains(@class,'a-price-whole')]") phone_names = [] phone_prices = [] for phone in phones: phone_names.append(phone.text) for price in prices: phone_prices.append(price.text) final_list = zip(phone_names, phone_prices) wb = Workbook() wb['Sheet'].title = 'iPhone Data' sheet = wb.active sheet.append(["Phone Name", "Price"]) for i in list(final_list): sheet.append(i) wb.save("iPhonesList.xlsx") driver.quit()
def __init__(self, archive, workbook_part_name): self.archive = archive self.workbook_part_name = workbook_part_name self.wb = Workbook() self.sheets = []
# from openpyxl import Workbook import openpyxl import os.path from openpyxl.workbook.workbook import Workbook xlsx_file = './Coin_Trading_Bot.xlsx' sheet = 'trading_bot_revenue' # workbook 생성 (덮어쓰기) # write_wb = Workbook() if os.path.isfile(xlsx_file): write_wb = openpyxl.load_workbook(xlsx_file) else: write_wb = Workbook() write_ws = write_wb.create_sheet(sheet) # Set write worksheet sheet_list = write_wb.sheetnames flag = 0 for sheet_name in sheet_list: if sheet_name == 'Sheet': # 기본 생성 시트 제거 write_wb.remove(write_wb[sheet_name]) if sheet_name == sheet: flag = 1 break if flag == 0: write_ws = write_wb.create_sheet('trading_bot_revenue') else: write_ws = write_wb['trading_bot_revenue']