def write_mismatch(filename, etalon, tocomp, mismatch): workbook = Workbook(join(SPREADSHEETS_DIR, get_filebase(filename)+'.xlsx')) cell_fmt = workbook.add_format() cell_fmt.set_bg_color('cyan') worksheet = workbook.add_worksheet(name='test') write_sheet(worksheet, cell_fmt, *tocomp, cells_to_mark=mismatch) worksheet = workbook.add_worksheet(name='etalon') write_sheet(worksheet, cell_fmt, *etalon) workbook.close()
def dispatch(dataset): buf = StringIO() wb = Workbook(buf, {'constant_memory': True, 'in_memory': True, 'default_date_format': 'yyyy/mm/dd hh:mm:ss'} ) format_ = wb.add_format(FORMAT) # dataset metadata ws = wb.add_worksheet('Global attributes') write_metadata(ws, dataset, 0, 0, format_) # 1D grids for grid in [g for g in walk(dataset, GridType) if len(g.shape) == 1]: logger.debug('Grid {}'.format(grid.name)) ws = wb.add_worksheet(grid.name) # headers ws.write(0, 0, grid.dimensions[0], format_) ws.write(0, 1, grid.name, format_) # data for j, data in enumerate(grid.data): for i, value in enumerate(numpy.asarray(data)): ws.write(i+1, 1-j, value) # add var metadata write_metadata(ws, grid, 0, 2, format_) # sequences for seq in walk(dataset, SequenceType): logger.debug('Sequence {}'.format(seq.name)) ws = wb.add_worksheet(seq.name) # add header across the first row for j, var_ in enumerate(seq.keys()): ws.write(0, j, var_, format_) # add data in the subsequent rows for i, row in enumerate(seq.data): for j, value in enumerate(row): ws.write(i+1, j, value) # add var metadata in columns to the right of the data n = 0 j = len(seq.keys())+1 for child in seq.children(): logger.debug("Child {}".format(child.name)) ws.merge_range(n, j, n, j+1, child.name, format_) n = write_metadata(ws, child, n+1, j, format_)+1 wb.close() return buf
def dump_info_xlsx(): from xlsxwriter import Workbook wb = Workbook(configuration['outputfile'] + '.xlsx') ws = wb.add_worksheet("CollatedInfo") ordered_list = 'Name,Matric,Team,MainLineCount,MainWordCount,TestLineCount,TestWordCount,DocsLineCount,DocsWordCount,MainFile,TestFile,DocsFile'.split(",") row = 0 for header in ordered_list: col = ordered_list.index(header) ws.write(row, col, header) row = 1 for matric in sorted(studentlist.keys()): for k , v in studentlist[matric].items(): if k == 'MainFile': pass elif k == 'Main': pass elif k == 'TestFile': pass elif k == 'Test': pass col = ordered_list.index(k) ws.write(row, col, v) row += 1 wb.close()
def write_cell_errors(xlsxname, header, lines, cells_to_mark): workbook = Workbook(join(SPREADSHEETS_DIR, xlsxname)) cell_fmt = workbook.add_format() cell_fmt.set_bg_color('cyan') worksheet = workbook.add_worksheet() write_sheet(worksheet, cell_fmt, header, lines, cells_to_mark) workbook.close()
def download_as_excel(data): output = StringIO.StringIO() wb = Workbook(output) ws = wb.add_worksheet("Sheet 1") first_row = 0 for header in data['table_header']: col = data['table_header'].index(header) # we are keeping order. if header in ['order__'+each for each in OrderHeader._meta.get_all_field_names()]: header=header.split('__')[1] header=header.replace('_',' ') header=header.capitalize() print 'header----',header else: header=header.capitalize() header=header.replace('_',' ') ws.write(first_row,col,header) # we have written first row which is the header of worksheet also. srow=1 for row in data['table_data']: for _key,_value in row.items(): col = data['table_header'].index(_key) try: ws.write(srow,col,_value) except Exception, e: ws.write(srow,col, json.dumps(_value)) srow += 1 #enter the next row
def exportExcelDirect(request, adict): try: l_parm = adict['ex_parm'] l_parm['title'] # : '文档标题', 第一行各列合并居中写入title l_parm['cols'] # :['列名1','列名2','列名3'], //列标题 l_parm['rows'] # :[ ['行1列1','行1列2','行1列3'], # ['行2列1','行2列2','行2列3'], import io from xlsxwriter import Workbook output = io.BytesIO() workbook = Workbook(output) worksheet = workbook.add_worksheet('export') worksheet.write('A1',l_parm['title']) # Text with formatting. .......... write( row, cols, content ) for i in range(len(l_parm['cols'])): worksheet.write(1, i, l_parm['cols'][i]) for i in range(len(l_parm['rows'] )): for j in range(len(l_parm['rows'][i])): worksheet.write(i + 2, j, l_parm['rows'][i][j]) workbook.close() output.seek(0) response = HttpResponse(output.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") except Exception as e: ls_err = str(e.args) response = HttpResponse(str("导出excel失败:" + ls_err), content_type="application/text") response['Content-Disposition'] = "attachment; filename=test.xlsx" return response
def verified(context, collaborator, test, outpath=None): """Export variants which have been verified for an institute and write them to an excel file. Args: collaborator(str): institute id test(bool): True if the function is called for testing purposes outpath(str): path to output file Returns: written_files(int): number of written or simulated files """ written_files = 0 collaborator = collaborator or 'cust000' LOG.info('Exporting verified variants for cust {}'.format(collaborator)) adapter = context.obj['adapter'] verified_vars = adapter.verified(institute_id=collaborator) LOG.info('FOUND {} verified variants for institute {}'.format(len(verified_vars), collaborator)) if not verified_vars: LOG.warning('There are no verified variants for institute {} in database!'.format(collaborator)) return None document_lines = export_verified_variants(verified_vars) today = datetime.datetime.now().strftime('%Y-%m-%d') document_name = '.'.join(['verified_variants', collaborator, today]) + '.xlsx' # If this was a test and lines are created return success if test and document_lines: written_files +=1 LOG.info('Success. Verified variants file contains {} lines'.format(len(document_lines))) return written_files # create workbook and new sheet # set up outfolder if not outpath: outpath = str(os.getcwd()) workbook = Workbook(os.path.join(outpath,document_name)) Report_Sheet = workbook.add_worksheet() # Write the column header row = 0 for col,field in enumerate(VERIFIED_VARIANTS_HEADER): Report_Sheet.write(row,col,field) # Write variant lines, after header (start at line 1) for row, line in enumerate(document_lines,1): # each line becomes a row in the document for col, field in enumerate(line): # each field in line becomes a cell Report_Sheet.write(row,col,field) workbook.close() if os.path.exists(os.path.join(outpath,document_name)): LOG.info('Success. Verified variants file of {} lines was written to disk'. format(len(document_lines))) written_files += 1 return written_files
def write_presentation(self, values, records, output): workbook = Workbook(output, {'constant_memory': True}) worksheet = workbook.add_worksheet() for column, value in enumerate(values): worksheet.write_string(0, column, value) for row, record in enumerate(records): value_iterator = enumerate([unicode(record[v]) for v in values]) for column, text in value_iterator: worksheet.write_string(row + 1, column, text) workbook.close()
def _open_worksheet(xlsx_file): """ Enable XlsxWriter Worksheet object to be opened, operated on, and then automatically closed within a `with` statement. A filename or stream object (such as a ``BytesIO`` instance) is expected as *xlsx_file*. """ workbook = Workbook(xlsx_file, {"in_memory": True}) worksheet = workbook.add_worksheet() yield workbook, worksheet workbook.close()
def genera_e_salva(self, nome='File.xlsx', scadenza=None, ordina_fogli=True, save_to_memory=False): """ Genera il file e lo salva su database. :param nome: Il nome del file da allegare (opzionale, default 'File.xlsx'). :param scadenza: Scadenza del file. Domani. :return: """ generatore = GeneratoreNomeFile('allegati/') zname = generatore(self, nome) self.prepara_cartelle(MEDIA_ROOT + zname) if save_to_memory: workbook = Workbook(self.output) else: workbook = Workbook(MEDIA_ROOT + zname) bold = workbook.add_format({'bold': True}) # Per ogni foglio for foglio in [x for x in self.fogli]: # Aggiunge il foglio worksheet = workbook.add_worksheet(foglio.nome) # Aggiunge l'intestazione for col, testo in enumerate(foglio.intestazione): worksheet.write(0, col, str(testo), bold) # Aggiunge il contenuto for riga, colonne in enumerate(foglio.contenuto): riga += 1 # Indice shiftato per intestazione for colonna, testo in enumerate(colonne): if isinstance(testo, datetime): testo = testo.strftime("%d/%m/%Y %H:%M") if isinstance(testo, date): testo = testo.strftime("%d/%m/%Y") if testo == ", ": # Rimuove campi ', ' testo = "" worksheet.write(riga, colonna, str(testo)) if ordina_fogli: workbook.worksheets_objs.sort(key=lambda x: x.name) workbook.close() if save_to_memory: self.output.seek(0) else: self.file = zname self.nome = nome self.scadenza = scadenza or domani() self.save()
def from_nested_iters(cls, data): """Builds an XlrdSheetData from nested iterables. param data: is nested iterables representing data for each cell. For example, `[('A1', 'B1'), ('A2', 'B2')]` represents two rows and two columns of data. """ workbook = Workbook(BytesIO()) sheet = workbook.add_worksheet('test') for row, row_data in enumerate(data): for column, cell_data in enumerate(row_data): sheet.write(row, column, cell_data) return XlrdSheetData(xlsx_to_reader(workbook).sheet_by_index(0))
def summary_result(all_bugs_file, summary_file): summary_file_dir = join_path(EXPERIMENT_RESULT_FOLDER, summary_file) wb = Workbook(summary_file_dir) sheet = wb.add_worksheet("sheet1") excel_data_df = pandas.read_excel(all_bugs_file, sheet_name=None) row = 0 header_column = [] for item in default_data_column: if item in excel_data_df[TARANTULA]: header_column.append(item) write_header_in_sumary_file(row, sheet, header_column) row += 1 comparison_data = calculate_average_in_a_file(excel_data_df, row, sheet, header_column) wb.close() return comparison_data
def writeKnowledgeAreaWorksheets(wb: xlsxwriter.Workbook) -> None: """ Iterate through the global collection of KnowledgeAreas, create a worksheet for each, and call method to populate the worksheet with courses and learning objectives. :param wb: Excel Workbook Object """ global knowledgeAreas for knowledgeArea in knowledgeAreas: ws = wb.add_worksheet() ws.name = knowledgeArea.getText()[0:31] writeWorksheet(ws, knowledgeArea)
def time_record(): players = [{ 'dailyWinners': 3, 'dailyFree': 2, 'user': '******', 'bank': 0.06 }, { 'dailyWinners': 3, 'dailyFree': 2, 'user': '******', 'bank': 4.0 }, { 'dailyWinners': 1, 'dailyFree': 2, 'user': '******', 'bank': 3.1 }, { 'dailyWinners': 3, 'dailyFree': 2, 'user': '******', 'bank': 0.32 }] ordered_list = [ "user", "dailyWinners", "dailyFree", "bank" ] # list object calls by index but dict object calls items randomly wb = Workbook("launch_time.xlsx") ws = wb.add_worksheet("launch_time_statistic" ) # or leave it blank, default name is "Sheet 1" first_row = 0 for header in ordered_list: col = ordered_list.index(header) # we are keeping order. ws.write( first_row, col, header ) # we have written first row which is the header of worksheet also. row = 1 for player in players: for _key, _value in player.items(): col = ordered_list.index(_key) ws.write(row, col, _value) row += 1 # enter the next row wb.close()
def mt_excel_files(store, case_obj, temp_excel_dir): """Collect MT variants and format line of a MT variant report to be exported in excel format Args: store(adapter.MongoAdapter) case_obj(models.Case) temp_excel_dir(os.Path): folder where the temp excel files are written to Returns: written_files(int): the number of files written to temp_excel_dir """ today = datetime.datetime.now().strftime('%Y-%m-%d') samples = case_obj.get('individuals') query = {'chrom':'MT'} mt_variants = list(store.variants(case_id=case_obj['_id'], query=query, nr_of_variants= -1, sort_key='position')) written_files = 0 for sample in samples: sample_id = sample['individual_id'] display_name = sample['display_name'] sample_lines = export_mt_variants(variants=mt_variants, sample_id=sample_id) # set up document name document_name = '.'.join([case_obj['display_name'], display_name, today]) + '.xlsx' workbook = Workbook(os.path.join(temp_excel_dir,document_name)) Report_Sheet = workbook.add_worksheet() # Write the column header row = 0 for col,field in enumerate(MT_EXPORT_HEADER): Report_Sheet.write(row,col,field) # Write variant lines, after header (start at line 1) for row, line in enumerate(sample_lines,1): # each line becomes a row in the document for col, field in enumerate(line): # each field in line becomes a cell Report_Sheet.write(row,col,field) workbook.close() if os.path.exists(os.path.join(temp_excel_dir,document_name)): written_files += 1 return written_files
def export_organizations(self, request): """ Export all organizations as XLSX. The exported file can be re-imported using the import-organizations command line command. """ output = BytesIO() workbook = Workbook(output) worksheet = workbook.add_worksheet() worksheet.name = request.translate(_("Organizations")) worksheet.write_row( 0, 0, (request.translate(_("ID")), request.translate(_("Name")), request.translate(_("Title")), request.translate( _("Active")), request.translate(_("External ID")), request.translate(_("Parent Organization")))) index = 0 for root in self.roots: index += 1 worksheet.write_row( index, 0, (root.id or '', root.name or '', root.title or '', root.active, root.external_name or '', root.parent_id or '')) for organization in root.children: index += 1 worksheet.write_row( index, 0, (organization.id or '', organization.name or '', organization.title or '', organization.active, organization.external_name or '', organization.parent_id or '')) workbook.close() output.seek(0) response = Response() response.content_type = ( 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response.content_disposition = 'inline; filename={}-{}.xlsx'.format( request.translate(_("Organizations")).lower(), datetime.utcnow().strftime('%Y%m%d%H%M')) response.body = output.read() return response
def excel_export_members_filter(request): response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename=Report.xlsx' output = BytesIO() workbook = Workbook(output) worksheet_s = workbook.add_worksheet(Config.vocabulary('member_pl')) worksheet_s.write_string(0, 0, str(_('Name'))) worksheet_s.write_string(0, 1, str(Config.vocabulary('assignment'))) worksheet_s.write_string( 0, 2, str(Config.vocabulary('assignment') + ' ' + _('Kernbereich'))) worksheet_s.write_string(0, 3, str(_('Taetigkeitsbereiche'))) worksheet_s.write_string(0, 4, str(_('Depot'))) worksheet_s.write_string(0, 5, str(_('Email'))) worksheet_s.write_string(0, 6, str(_('Telefon'))) worksheet_s.write_string(0, 7, str(_('Mobile'))) members = MemberDao.members_with_assignments_count() row = 1 for member in members: member.all_areas = '' for area in member.areas.all(): member.all_areas = member.all_areas + area.name + ' ' if member.all_areas == '': member.all_areas = str(_('-Kein Tätigkeitsbereich-')) member.depot_name = str(_('Kein Depot definiert')) if member.subscription_current is not None: member.depot_name = member.subscription_current.depot.name full_name = member.first_name + ' ' + member.last_name worksheet_s.write_string(row, 0, full_name) worksheet_s.write(row, 1, member.assignment_count) worksheet_s.write(row, 2, member.core_assignment_count) worksheet_s.write_string(row, 3, member.all_areas) worksheet_s.write_string(row, 4, member.depot_name) worksheet_s.write_string(row, 5, member.email) worksheet_s.write_string(row, 6, member.phone) if member.mobile_phone is not None: worksheet_s.write_string(row, 7, member.mobile_phone) row += 1 workbook.close() xlsx_data = output.getvalue() response.write(xlsx_data) return response
def write_caption_excel(self, images, caption_path): workbook = Workbook(os.path.join(caption_path, 'captions.xlsx')) worksheet = workbook.add_worksheet() row = 0 worksheet.write(row, 0, 'image_name') worksheet.write(row, 1, 'caption') row += 1 for index, image in enumerate(images): filename = 'image_' + str(index) + '.jpg' try: caption = image['alt'] except KeyError: caption = 'No caption found' worksheet.write(row, 0, filename) worksheet.write(row, 1, caption) row += 1 workbook.close()
def dict2worksheet(workbook: xlsxwriter.Workbook, worksheet_name, dict2print: dict, header: list, key_col_width=50): worksheet = workbook.add_worksheet(worksheet_name) col = 0 for h in header: worksheet.write(0, col, h) col += 1 row = 1 for k, v in dict2print.items(): worksheet.write(row, 0, k) worksheet.write(row, 1, v) row += 1 worksheet.set_column('A:A', key_col_width)
def write_captions_to_excel_file(self, images, caption_path): print('writing to excel') workbook = Workbook(os.path.join(caption_path, 'captions.xlsx')) worksheet = workbook.add_worksheet() row = 0 worksheet.write(row, 0, 'Image name') # 3 --> row number, column number, value worksheet.write(row, 1, 'Caption') row += 1 for index, image in enumerate(images): filename = 'image_' + str(index) + '.jpg' try: caption = image['alt'] except KeyError: caption = 'No caption exists' worksheet.write(row, 0, filename) worksheet.write(row, 1, caption) row += 1 workbook.close()
def BuildExcelTable(text_file_list, base_name): newName = base_name + '.xlsx' workbook = Workbook(newName, {'strings_to_numbers': True}) for i in range(len(text_file_list)): print(text_file_list[i]) worksheet_name = text_file_list[i][:12] worksheet = workbook.add_worksheet(worksheet_name) FileReader = csv.reader( (open(text_file_list[i], 'r', encoding='latin1')), delimiter='*', quotechar='"') for rowx, row in enumerate(FileReader): for colx, value in enumerate(row): worksheet.write(rowx, colx, value) workbook.close()
def verified_excel_file(store, institute_list, temp_excel_dir): """Collect all verified variants in a list on institutes and save them to file Args: store(adapter.MongoAdapter) institute_list(list): a list of institute ids temp_excel_dir(os.Path): folder where the temp excel files are written to Returns: written_files(int): the number of files written to temp_excel_dir """ document_lines = [] written_files = 0 today = datetime.datetime.now().strftime('%Y-%m-%d') LOG.info('Creating verified variant document..') for cust in institute_list: verif_vars = store.verified(institute_id=cust) LOG.info('Found {} verified variants for customer {}'.format(len(verif_vars), cust)) if not verif_vars: continue cust_verified = export_verified_variants(verif_vars) document_name = '.'.join([cust, '_verified_variants', today]) + '.xlsx' workbook = Workbook(os.path.join(temp_excel_dir,document_name)) Report_Sheet = workbook.add_worksheet() # Write the column header row = 0 for col,field in enumerate(VERIFIED_VARIANTS_HEADER): Report_Sheet.write(row,col,field) # Write variant lines, after header (start at line 1) for row, line in enumerate(cust_verified,1): # each line becomes a row in the document for col, field in enumerate(line): # each field in line becomes a cell Report_Sheet.write(row,col,field) workbook.close() if os.path.exists(os.path.join(temp_excel_dir,document_name)): written_files += 1 return written_files
def send_workbook(self): """Create and send the Excel version of the report. We're using the xlsxwriter package in order to support the requirement of inline rich text. """ stamp = datetime.now().strftime("%Y%m%d%H%M%S") filename = "standard-wording-{}.xlsx".format(stamp) output = BytesIO() book = Workbook(output, {"in_memory": True}) sheet = book.add_worksheet("Matches") formats = dict(header=book.add_format(dict(bold=True, align="center")), center=book.add_format( dict(align="center", valign="top")), bold=book.add_format(dict(bold=True)), term=book.add_format(dict(bold=True, color="red")), wrap=book.add_format(dict(text_wrap=True)), top=book.add_format(dict(valign="top"))) title, subtitle = self.caption sheet.merge_range("A1:E1", title, formats["header"]) sheet.merge_range("A2:E2", subtitle, formats["header"]) widths = 10, 50, 30, 80, 18 headers = "Doc ID", "Doc Title", "Match", "Context", "Standard Wording?" for i, width in enumerate(widths): sheet.set_column(i, i, width) sheet.write(3, i, headers[i], formats["header"]) row = 4 for summary in self.summaries: doc_id, title = summary.cdr_id, summary.title for match in summary.matches: row = match.excel_row(sheet, row, formats, doc_id, title) doc_id = title = "" book.close() output.seek(0) book_bytes = output.read() stdout.buffer.write(f"""\ Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet Content-Disposition: attachment; filename={filename} Content-length: {len(book_bytes)} """.encode("utf-8")) stdout.buffer.write(book_bytes) sysexit(0)
def generar_layout(datos, salida): wb = Workbook(salida) ws = wb.add_worksheet('Layout') encabezados = ''' Nombre Inicio Longitud Tipo de dato capturado '''.split('\n')[1:-1] # Escribir encabezados col_enc_bg = "#{:02x}{:02x}{:02x}".format(15, 36, 62).upper() col_ren2 = "#{:02x}{:02x}{:02x}".format(220, 230, 241).upper() format_enc = wb.add_format({ 'bold': True, 'font_color': 'white', 'bg_color': col_enc_bg }) format_ren1 = wb.add_format({'border': 1}) format_ren2 = wb.add_format({'border': 1, 'bg_color': col_ren2}) for col, encabezado in enumerate(encabezados): ws.write(0, col, encabezado, format_enc) # Escribir datos del diccionario for renglon, registro in enumerate(datos, 1): formato = format_ren1 if renglon % 2 == 0 else format_ren2 # Registros2 = namedtuple('Registros', 'etiqueta nombre inicio longitud tipo_cap') ws.write(renglon, 0, registro.etiqueta, formato) ws.write(renglon, 1, int(registro.inicio), formato) ws.write(renglon, 2, int(registro.longitud), formato) ws.write(renglon, 3, registro.tipo_cap, formato) # Aplicando formato a la hoja ws.freeze_panes(1, 0) ws.autofilter('A1:D1') ws.set_column(0, 0, 55) ws.set_column(1, 1, 8) ws.set_column(2, 2, 10) ws.set_column(3, 3, 25) ws.hide_gridlines(2) wb.close() click.launch(salida) print("Layout generado")
def mt_excel_files(store, case_obj, temp_excel_dir): """Collect MT variants and format line of a MT variant report to be exported in excel format Args: store(adapter.MongoAdapter) case_obj(models.Case) temp_excel_dir(os.Path): folder where the temp excel files are written to Returns: written_files(int): the number of files written to temp_excel_dir """ today = datetime.datetime.now().strftime('%Y-%m-%d') samples = case_obj.get('individuals') query = {'chrom':'MT'} mt_variants = list(store.variants(case_id=case_obj['_id'], query=query, nr_of_variants= -1, sort_key='position')) written_files = 0 for sample in samples: sample_id = sample['individual_id'] sample_lines = export_mt_variants(variants=mt_variants, sample_id=sample_id) # set up document name document_name = '.'.join([case_obj['display_name'], sample_id, today]) + '.xlsx' workbook = Workbook(os.path.join(temp_excel_dir,document_name)) Report_Sheet = workbook.add_worksheet() # Write the column header row = 0 for col,field in enumerate(MT_EXPORT_HEADER): Report_Sheet.write(row,col,field) # Write variant lines, after header (start at line 1) for row, line in enumerate(sample_lines,1): # each line becomes a row in the document for col, field in enumerate(line): # each field in line becomes a cell Report_Sheet.write(row,col,field) workbook.close() if os.path.exists(os.path.join(temp_excel_dir,document_name)): written_files += 1 return written_files
def format_data(): ''' 从全部数据中整理出需要的数据 ''' result = [] data=[] with open('/home/qmzb/nsql.txt') as data_info: for line in data_info: dataname=line.split(':')[0] slowsql=line.split(':')[1] result.append(dataname) data.append(slowsql) #定义xlsx表格的路径 filename = '/home/qmzb/mansql.xlsx' #生成xlsx表格 test_book = Workbook(filename) #添加sheet worksheet = test_book.add_worksheet('SlowSql') # 设置字体加粗、字体大小 format_title = test_book.add_format({'bold': True, 'font_size': 12}) # 设置水平对齐、垂直对齐 format_title.set_align('center') format_title.set_align('vcenter') # 定义表头 title = ( "库名", "慢SQL", "备注" ) row = 0 col = 0 # 表头写入文件,引用样式 for item in title: worksheet.write(row, col, item, format_title) col += 1 row = 1 col = 0 for sqlname,sql in zip(result,data): worksheet.write(row,col,sqlname) worksheet.write(row,col+1,sql) row += 1 test_book.close()
def savexls_ec_bact(dir, filename, lst_ec_bact, lst_abbr): ''' Save enzyme - bacteria relations into a .xlsx file. Parameters ---------- dir : string Directory to save the file End this string with '/' filename : string Desired name for the file lst_ec_bact : list of dictionaries List of EC - bacteria relations. Each dictinary contains keys: 'EC' and bacteria names. '1' means the enzyme is encoded by this bacteria. '0' means the enzyme is not encoded by this bacteria. lst_abbr : list of strings Serve as headers Returns ------- None ''' print('>>> Saving EC - bacteria relations... File name: ' + filename) header_lst = ['EC'] + lst_abbr wb_ec_bact = Workbook(dir + filename) ws_ec_bact = wb_ec_bact.add_worksheet("Sheet 1") header_row = 0 for col in range(0, len(header_lst)): ws_ec_bact.write(header_row, col, header_lst[col]) row = 1 for unit in lst_ec_bact: for _key, _value in unit.items(): col = header_lst.index(_key) ws_ec_bact.write(row, col, _value) row = row + 1 wb_ec_bact.close() return None
def Get_Data(): # Подключение БД conn = sqlite3.connect(adr) c = conn.cursor() # Создание книги workbook = Workbook('db_accel.xlsx') worksheet = [] # Создание листов по кол-ву элементов (причин) for x in range(len(ConcessionMass)): worksheet.append(workbook.add_worksheet(SheetName[x])) reason = ConcessionMass[x] sql = c.execute("SELECT * FROM comments WHERE typeconcession = '" + reason + "'") for i, row in enumerate(sql): fio = row[1] + ' ' + row[2] + ' ' + row[3] for j in range(4, len(row) - 1): # for j, value in enumerate(row): worksheet[x].write(i, 0, fio) worksheet[x].write(i, j - 3, row[j]) # mysel = c.execute("SELECT surname, name, lname, group2, number, typeconcession, gender, confirm FROM comments") conn.close() workbook.close() File_Path = os.path.abspath('db_accel.xlsx') fp = open(File_Path, "rb") response = HttpResponse(fp.read()) fp.close() file_type = mimetypes.guess_type(File_Path) if file_type is None: file_type = 'application/octet-stream' response['Content-Type'] = file_type response['Content-Length'] = str(os.stat(File_Path).st_size) response[ 'Content-Disposition'] = "attachment; filename=Spisok_Podavshix.xlsx" # Чистка временнойго файла os.remove(File_Path) return response
def create_excel(data, headers, file_name, sheet_name): wb = Workbook(file_name) ws = wb.add_worksheet(sheet_name) first_row = 0 for header in headers: col = headers.index(header) # we are keeping order. ws.write( first_row, col, header ) # we have written first row which is the header of worksheet also. row = 1 for line in data: for _key, _value in line.items(): col = headers.index(_key) ws.write(row, col, _value) row += 1 wb.close()
def export_xls(self, objects, columns): from xlsxwriter import Workbook xlsdata = StringIO.StringIO() book = Workbook(xlsdata) sheet = book.add_worksheet(self.model.__name__) colnames = [h for h, _, _ in columns] for c, h in enumerate(colnames): sheet.write(0, c, h) colfields = [c for _, c, _ in columns] data = (self.export_object(obj, columns) for obj in objects) for r, datum in enumerate(data): for c, f in enumerate(colfields): sheet.write(r + 1, c, datum.get(f, '')) book.close() return self.response_export(xlsdata.getvalue(), 'export.xlsx', 'application/msexcel')
def downloadCaptions(self, images, captionPath): workbook = Workbook(captionPath + 'captions.xlsx') worksheet = workbook.add_worksheet() row = 0 worksheet.write(row, 0, 'Image name') worksheet.write(row, 1, 'Caption') filename = 'captions.csv' row += 1 for index, image in enumerate(images): filename = 'image_' + str(index) + '.jpg' try: caption = image['alt'] except keyError: caption = 'No caption exists' worksheet.write(row, 0, filename) worksheet.write(row, 1, caption) row += 1 workbook.close()
def export_to_excel(customers): """Converts customer list data into a Microsoft Excel file and returns the file stream for downloading Args: customers (list): A list of customers with customer details on each line Returns: filestream: The excel file as a filestream for downloading """ # Create an Microsoft Excel workbook and sheet output = BytesIO() book = Workbook(output) sheet = book.add_worksheet('Customer List') fields = [] # Write the details of each customer in a row for row in range(len(customers)): customer = customers[row] if row == 0: fields = customer.keys() col = 0 for field in fields: sheet.write(0, col, field) col += 1 col = 0 for field in fields: sheet.write(row + 1, col, customer[field]) col += 1 # Set the columns width so it's easier to read. sheet.set_column('A:Z', 48) # Close the file and seek back to start of file book.close() output.seek(0) # Return the file stream return output
def downloadCaptionsToExcel(driver, all_images, path): workbook = Workbook(path + 'captions.xlsx') worksheet = workbook.add_worksheet() row = 0 worksheet.write(row, 0, 'Image name') worksheet.write(row, 1, 'Caption') filename = 'captions.csv' row += 1 for index, image in enumerate(all_images): filename = 'image_' + str(index) + '.jpg' try: caption = image['alt'] except Exception: caption = 'No caption exists' worksheet.write(row, 0, filename) worksheet.write(row, 1, caption) row += 1 workbook.close()
def _render_to_excel(doc: Document, file_name: Path) -> None: """WARN: This is untested.""" workbook = Workbook(str(file_name)) worksheet = workbook.add_worksheet() row_index = 0 def next_row() -> int: nonlocal row_index res = row_index row_index += 1 return res if doc.comment is not None: raise Exception("TODO: render doc comment to excel") for section in doc.sections: next_row() if section.name is not None: worksheet.write(next_row(), 0, section.name) if section.text is not None: raise Exception("TODO: render section text to excel") next_row() for table in section.tables: if table.name is not None: worksheet.write(next_row(), 0, table.name) if table.text is not None: raise Exception("TODO: render table text to excel") assert table.header_groups is None if table.headers is not None: for i, header in enumerate(table.headers): worksheet.write(row_index, i, header) next_row() for row in table.rows: for i, value in enumerate(row): if value.color is not None or value.bold is not None: raise Exception( "TODO: render to excel with bold or colored cells") worksheet.write(row_index, i, "" if value.value is None else value.value) workbook.close()
def download_as_excel(data): output = StringIO.StringIO() wb = Workbook(output) ws = wb.add_worksheet("Sheet 1") first_row = 0 for header in data['table_header']: col = data['table_header'].index(header) # we are keeping order. ws.write(first_row,col,header) # we have written first row which is the header of worksheet also. srow=1 for row in data['table_data']: for _key,_value in row.items(): col = data['table_header'].index(_key) try: ws.write(srow,col,_value) except Exception, e: ws.write(srow,col, json.dumps(_value)) srow += 1 #enter the next row
class ExcelHelper(object): def __init__(self, filepath): self.filepath = filepath self._workbook = Workbook(filepath, {'constant_memory': True}) def write_header(self, work_sheet, header_columns): for idx, column in enumerate(header_columns): # 컬럼 Header 셋팅 if isinstance(header_columns[column], TermExcelColumn): work_sheet.write(0, idx, header_columns[column].column_name) else: work_sheet.write(0, idx, header_columns[column]) def write_row(self, work_sheet, row_num, data, ordered_columns): for i, column_name in enumerate(ordered_columns): column = ordered_columns[column_name] column_value = data.get(column_name, '') if isinstance(column, TermExcelColumn) and column_value: if column.data_type == TermExcelColumn.LIST: work_sheet.write( row_num, i, getattr(column, column.data_type.lower())(column_value)) else: work_sheet.write( row_num, i, column.cached_data(column_value, column.data_type.lower())) else: work_sheet.write(row_num, i, column_value) def write_sheet(self, ordered_columns, rows, title=None): work_sheet = self._workbook.add_worksheet(title) self.write_header(work_sheet, ordered_columns) for idx, data in enumerate(rows): row_num = idx + 1 self.write_row(work_sheet, row_num, data, ordered_columns) def save(self): self._workbook.close()
def dict2excel(dts): keys = list({key for data in dts for key in data.keys()}) file_object = io.BytesIO() wb = Workbook(file_object) ws = wb.add_worksheet() first_row = 0 for header in keys: col = keys.index(header) ws.write(first_row, col, header) row = 1 for dt in dts: for _key, _value in dt.items(): col = keys.index(_key) ws.write(row, col, _value) row += 1 wb.close() file_object.seek(0) return file_object
def writeTimeInModalWorksheet(wb: xlsxwriter.Workbook) -> None: """ Write a separate worksheet for all times in modal. :param wb: Excel Workbook Object """ global timeInModals global timeInModalWsRow ws = wb.add_worksheet() ws.name = "TimeInModals" row = timeInModalWsRow col = 0 ws.write(row, col, 'UserId') col += 1 ws.write(row, col, 'Time Entered Modal') col += 1 ws.write(row, col, 'Time Closed Modal') col += 1 ws.write(row, col, 'Time In Modal') col += 1 ws.write(row, col, 'Disposition') col += 1 ws.write(row, col, 'participantID') col += 1 ws.write(row, col, 'group') timeInModalWsRow += 1 col = 0 writeTimeInModalRows(ws)
def generate_excel_file(self, doc_obj, city_info_data): ordered_list = ['address', 'latitude', 'longitude'] file_path = '/tmp/prashant/{}.xlsx'.format(doc_obj.file_name) os.makedirs(os.path.dirname(file_path), exist_ok=True) wb = Workbook(file_path) ws = wb.add_worksheet('New Sheet') first_row = 0 for header in ordered_list: col = ordered_list.index(header) ws.write(first_row, col, header) row = 1 for data in city_info_data: for _key, _value in data.items(): col = ordered_list.index(_key) ws.write(row, col, _value) row += 1 wb.close()
def save(self, sa): """ :param sa: """ ext = os.path.splitext(sa)[1][1:].strip().lower() if self.nparray is None: raise ValueError t = self.nparray[:, 0] r_a = self.nparray[:, 1] if sa: if ext == 'dat': r_n = r_a / 10 n_nparray = np.column_stack((t, r_n)) np.savetxt(sa, n_nparray, delimiter='\t', fmt=['%d', '%.3f']) elif ext == 'xslx': try: from xlsxwriter import Workbook except ImportError: raise XLSWImportError wb = Workbook(sa) ws = wb.add_worksheet("summary_distances") ws.write_row(0, 0, ('Time, ps', 'COM, \u212b')) ws.write_column(1, 0, t) ws.write_column(1, 1, r_a) wb.close() elif ext == 'xls': try: from xlwt import Workbook as Workbook except ImportError: raise XLWTImportError wb = Workbook() ws = wb.add_sheet("summary_distances") ws.write(0, 0, 'Time, ps') ws.write(0, 1, 'COM, \u212b') for i, t_i in enumerate(t, start=1): ws.write(i, 0, t_i) for j, r_a_j in enumerate(r_a, start=1): ws.write(j, 1, r_a_j) wb.save(sa) else: raise BadExtError
def createXlsxFile(): ''' Create excel workbook ''' print('\nCreating excel file...') workbook = Workbook(excelFile) # Fill spreadsheet with logfile contents for arrayName, logArrayList in sorted(logArrays.items()): worksheet = workbook.add_worksheet(arrayName) print('Creating worksheet for {:s}...'.format(arrayName)) row = 0 col = 0 for logArray in logArrayList: for logData in logArray: if arrayName == 'antennaDivPreambleMatch': worksheet.write(row, col, logData[0] / ANTENNA_DIV_PERCENT_DIV) worksheet.write(row + 22, col, logData[1] / ANTENNA_DIV_PERCENT_DIV) worksheet.write(row + 44, col, logData[2] / ANTENNA_DIV_PERCENT_DIV) worksheet.write(row + 66, col, logData[3] / ANTENNA_DIV_PERCENT_DIV) elif arrayName == 'antennaDivFailedRx': # Compute for error rate of failed RX worksheet.write(row, col, logData / ANTENNA_DIV_PERCENT_DIV) else: worksheet.write(row, col, logData) row = row + 1 row = 0 col = col + 1 workbook.close()
def get_query_result_excel(self, sql, excelFile, header='True'): ''' Build an excel file from result set of the sql command \n Example: get query result excel select * from customer test.xlsx ''' cur = self._dbconnection.cursor() cur.execute(sql + ';') rows = cur.fetchall() print(cur.description) workbook = Workbook(excelFile) sheet = workbook.add_worksheet() format = workbook.add_format({'bold': True}) if header.lower() == 'true': for i, val in enumerate([column[0] for column in cur.description]): sheet.write(0, i, self._f(val), format) #Write header column for r, row in enumerate(rows): for c, s in enumerate(row): sheet.write(r+1, c, self._f(s)) # Write table data workbook.close()
def write_sheet(workbook: Workbook, sheet_name: str, header: List[str], rows: List[Dict]): """Write given header and rows in a workbook sheet.""" s = workbook.add_worksheet(sheet_name) s.set_column(0, len(header) - 1, width=10) # Some arbitrary width for the columns # Write header for i in range(len(header)): s.write(0, i, header[i]) # Write rows row_nr = 1 for row in rows: for i in range(len(header)): # Note, from the xlsxwriter docs: # # "When written to an Excel file numbers are converted to IEEE-754 64-bit double-precision floating point. # This means that, in most cases, the maximum number of digits that can be stored in Excel without losing # precision is 15." s.write(row_nr, i, row[header[i]]) row_nr += 1
def write_captions_to_excel_file(self, images, caption_path): print('Guardando en Excel') workbook = Workbook(os.path.join(caption_path, 'Pie De Foto.xlsx')) worksheet = workbook.add_worksheet() row = 0 worksheet.write( row, 0, 'Nombre De Imagen') # 3 --> row number, column number, value worksheet.write(row, 1, 'Pie De Foto') row += 1 for index, image in enumerate(images): filename = 'image_' + str(index) + '.jpg' try: caption = image['alt'] except KeyError: caption = 'No Existe Pie De Foto' worksheet.write(row, 0, filename) worksheet.write(row, 1, caption) row += 1 workbook.close()
def generar_layout(datos, salida): wb = Workbook(salida) ws = wb.add_worksheet('Layout') encabezados = ''' Nombre Inicio Longitud Tipo de dato capturado '''.split('\n')[1:-1] # Escribir encabezados col_enc_bg = "#{:02x}{:02x}{:02x}".format(15, 36, 62).upper() col_ren2 = "#{:02x}{:02x}{:02x}".format(220, 230, 241).upper() format_enc = wb.add_format({'bold': True, 'font_color': 'white', 'bg_color': col_enc_bg}) format_ren1 = wb.add_format({'border': 1}) format_ren2 = wb.add_format({'border': 1, 'bg_color': col_ren2}) for col, encabezado in enumerate(encabezados): ws.write(0, col, encabezado, format_enc) # Escribir datos del diccionario for renglon, registro in enumerate(datos, 1): formato = format_ren1 if renglon % 2 == 0 else format_ren2 # Registros2 = namedtuple('Registros', 'etiqueta nombre inicio longitud tipo_cap') ws.write(renglon, 0, registro.etiqueta, formato) ws.write(renglon, 1, int(registro.inicio), formato) ws.write(renglon, 2, int(registro.longitud), formato) ws.write(renglon, 3, registro.tipo_cap, formato) # Aplicando formato a la hoja ws.freeze_panes(1, 0) ws.autofilter('A1:D1') ws.set_column(0, 0, 55) ws.set_column(1, 1, 8) ws.set_column(2, 2, 10) ws.set_column(3, 3, 25) ws.hide_gridlines(2) wb.close() click.launch(salida) print("Layout generado")
def export_as_excel(request): category_id = request.GET['category'] if 'category' in request.GET else None data = get_master_product({'category_id': category_id}) output = StringIO.StringIO() wb = Workbook(output) ws = wb.add_worksheet("Sheet 1") first_row = 0 for header in data['table_header']: col = data['table_header'].index(header) # we are keeping order. ws.write(first_row,col,header) # we have written first row which is the header of worksheet also. srow=1 for row in data['table_data']: for _key,_value in row.items(): col = data['table_header'].index(_key) try: ws.write(srow,col,_value) except Exception, e: ws.write(srow,col, json.dumps(_value)) srow += 1 #enter the next row
def medicamentos_topOrganizacionesPorPedidoExcel(request): datos = request.session['estadistica']['excel']['datos'] medicamento = request.session['estadistica']['excel']['medicamento'] excel = io.BytesIO() workbook = Workbook(excel, {'in_memory': True}) worksheet = workbook.add_worksheet() titulo = workbook.add_format({ 'font_name':'Arial', 'font_size': 12, 'font_color': 'navy', 'bold': True }) encabezado = workbook.add_format({ 'font_name': 'Arial', 'bold': True }) alignLeft = workbook.add_format({ 'align': 'left', }) worksheet.write('A1:B1', 'Organizaciones mas demandantes del medicamento '+medicamento+' (por pedido)', titulo) worksheet.set_column('B:B', 40) worksheet.set_column('C:C', 20) worksheet.write('A2', '#', encabezado) worksheet.write('B2', 'Organizacion', encabezado) worksheet.write('C2', 'Cantidad', encabezado) fila = 2 tope = len(datos) for i in range(0, tope): worksheet.write(fila, 0, i + 1, alignLeft) worksheet.write(fila, 1, datos[i]['organizacion'], alignLeft) worksheet.write(fila, 2, datos[i]['cantidad'], alignLeft) fila += 1 workbook.close() excel.seek(0) response = HttpResponse(excel.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") response['Content-Disposition'] = "attachment; filename=OrganizacionesMasDemandantesDeMedicamentoPorPedido.xlsx" return response
def save_as_excel(folder, vb_dict, header): """Save to an Excel file.""" wb = Workbook(folder + "/combinedcsvs.xlsx") ws = wb.add_worksheet("Sheety") print(header) row = 0 col = 0 for title in header: ws.write(row, col, title) col += 1 row += 1 for vb in vb_dict.keys(): col = 0 ws.write(row, col, vb) col += 1 for i in range(1, len(vb_dict[vb])): ws.write(row, col, vb_dict[vb][i]) col += 1 row += 1 wb.close()
def pedidosAlaboratorio_topLabConMasSolicitudesPedidosExcel(request): datos = request.session['estadistica']['excel'] excel = io.BytesIO() workbook = Workbook(excel, {'in_memory': True}) worksheet = workbook.add_worksheet() titulo = workbook.add_format({ 'font_name':'Arial', 'font_size': 12, 'font_color': 'navy', 'bold': True }) encabezado = workbook.add_format({ 'font_name':'Arial', 'bold': True }) alignLeft = workbook.add_format({ 'align':'left', }) worksheet.write('A1:B1', 'Laboratorios con mas solicitudes (por medicamento)', titulo) worksheet.set_column('B:B', 40) worksheet.set_column('C:C', 20) worksheet.write('A2', '#', encabezado) worksheet.write('B2', 'Laboratorio', encabezado) worksheet.write('C2', 'Cantidad', encabezado) fila = 2 tope = len(datos) for i in range(0, tope): worksheet.write(fila, 0, i + 1, alignLeft) worksheet.write(fila, 1, datos[i]['laboratorio'], alignLeft) worksheet.write(fila, 2, datos[i]['cantidad'], alignLeft) fila += 1 workbook.close() excel.seek(0) response = HttpResponse(excel.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") response['Content-Disposition'] = "attachment; filename=LaboratoriosConMasSolicitudesDeMedicamentos.xlsx" return response
def write_excel(date, name, names=None, shift=1, id_col=0, chart_cell='D1', val_col=1): workbook = Workbook(name) sheet_name = 'TextTiling' worksheet = workbook.add_worksheet(sheet_name) i = 0 for value in date: if names == None: value_name = "%d - %d" % (i + shift, i + shift + 1) else: value_name = names[i] worksheet.write(i, id_col, value_name) worksheet.write(i, val_col, value) i += 1 chart = workbook.add_chart({ 'type': 'column'}) chart.add_series({ 'name': 'Cosines', 'categories': [sheet_name, 0, id_col, i - 1, id_col], 'values': [sheet_name, 0, val_col, i - 1, val_col], }) chart.set_size({'width': 920, 'height': 776}) worksheet.insert_chart(chart_cell, chart) workbook.close()
def generate_change_template(request): stream = BytesIO() book = Workbook(stream) bold = book.add_format({'bold': True}) # create sheet for user input sheet = book.add_worksheet('Updates') # set headers sheet.write('A1', 'category (like destID)', bold) sheet.write('B1', 'title (like input cat)', bold) sheet.write('C1', 'url (like output cat)', bold) rows = 400 # no idead sheet.data_validation(1, 1, rows, 1, { 'validate': 'list', 'source': 'Valid_Categories' }) # set width sheet.set_column('A:A', 25) sheet.set_column('B:B', 25) sheet.set_column('C:C', 25) # Create sheet containing data for user validations # sheet = book.add_worksheet('Validations') # sheet.write('A1', 'Categories', bold) categories = Category.objects.all() for i, cat in enumerate(categories): sheet.write(i + 1, 0, cat.slug) # book.define_name('Valid_Categories', '=Validations!$A$2:$A${}'.format(1 + categories.count())) # sheet.set_column('A:A', 10) # sheet.set_column('B:B', 2) book.close() stream.seek(0) response = HttpResponse(stream, content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachement; filename="page_add_template.xlsx"' return response
def xlsx_generate(file_name, sheets): log.info(' -> %s', file_name) with open(file_name, 'w') as fh: workbook = Workbook(fh) header_format = workbook.add_format({ 'bold': True, 'border': 1 }) for title, rows in sheets: if not len(rows): continue headers = get_headers(rows) if not len(headers): continue worksheet = workbook.add_worksheet(title) worksheet.strings_to_numbers = False worksheet.strings_to_urls = False worksheet.strings_to_formulas = False row_idx = 0 headers = None for row in rows: if headers is None: headers = row.keys() for c, cell in enumerate(headers): worksheet.write(row_idx, c, cell, header_format) row_idx += 1 col_idx = 0 for cell in headers: worksheet.write(row_idx, col_idx, row.get(cell)) col_idx += 1 row_idx += 1 worksheet.freeze_panes(1, 0) workbook.close()
def wr_xlsx(fout_xlsx, xlsx_data, **kws): """Write a spreadsheet into a xlsx file.""" if xlsx_data: from xlsxwriter import Workbook workbook = Workbook(fout_xlsx) worksheet = workbook.add_worksheet() # Cell formatting fmt_hdr = workbook.add_format({'top':1, 'bottom':1, 'left':0, 'right':0, 'bold':True}) fmt_txt = workbook.add_format({'border':0}) flds = xlsx_data[0]._fields if 'fld2col_widths' in kws: _set_xlsx_colwidths(worksheet, kws['fld2col_widths'], flds) # Print header hdrs = flds if 'hdrs' not in kws else kws['hdrs'] for col_idx, hdr in enumerate(hdrs): worksheet.write(0, col_idx, hdr, fmt_hdr) row_data_start = 1 # Print data row_idx = _wrxlsxdata(xlsx_data, row_data_start, worksheet, fmt_txt, **kws) workbook.close() sys.stdout.write(" {:>5} items WROTE: {}\n".format(row_idx-row_data_start, fout_xlsx)) else: sys.stdout.write(" 0 items. NOT WRITING {}\n".format(fout_xlsx))
def toExcel(self, outFileName): """ Write the distribution fitter results to an Excel spreadsheet :param outFileName: output spreadsheet name :type outFileName: str :return: Nothing :rtype: None """ workbook = Workbook(outFileName, {'constant_memory': True}) workbook.use_zip64() # allow large size Excels just in case wks = workbook.add_worksheet('Distribution Fitting') hdrFmt = workbook.add_format({'bold' : True, 'underline' : True, 'align' : 'center'}) resultFormats = [workbook.add_format({'num_format' : fmtStr}) \ for fmtStr in ['0.000000', '0.0000%']] row = 0 wks.set_column(0, 0, 11) wks.set_column(1, 1, 8, resultFormats[0]) wks.set_column(2, 2, 10.6, resultFormats[1]) for col, headerName in enumerate(self.getHeaderList()): wks.write_string(row, col, headerName, hdrFmt) for distrName, (results, params) in self.result.iteritems(): row += 1 col = 0 wks.write_string(row, col, distrName) for col, (result, outFormat) in \ enumerate(itertools.izip(results, resultFormats), col+1): wks.write_number(row, col, result, outFormat) for col, paramValue in enumerate(params, col+1): wks.write_number(row, col, paramValue) workbook.close()
def make_xlsx_response(sheets, basename): output = StringIO() workbook = Workbook(output) header_format = workbook.add_format({ 'bold': True, 'border': 1 }) for title, rows in sheets.items(): if not len(rows): continue worksheet = workbook.add_worksheet(title) row_idx = 0 headers = None for row in rows: if headers is None: headers = row.keys() for c, cell in enumerate(headers): worksheet.write(row_idx, c, cell, header_format) row_idx += 1 col_idx = 0 for cell in headers: worksheet.write(row_idx, col_idx, row.get(cell)) col_idx += 1 row_idx += 1 worksheet.freeze_panes(1, 0) workbook.close() output.seek(0) fn = basename + '.xlsx' headers = {'Content-Disposition': 'attachment; filename=%s' % fn} return Response(output, mimetype=XLSX_MIME, headers=headers)
def compound_clfsheet(simulorname: str, statistics: dict, workbook: writer.Workbook): sheetname, stats = simulorname, statistics keys = stats.keys() msmt_table = [] clf_smry = {} for key in keys: msmtdict = stats[key] msmt_attr, ave = attr_listup(msmtdict, key) msmt_table.append(msmt_attr) clf_smry[key] = ave msmt_table.sort(key=lambda x: x[0]) foldattr = ['fold'] for i in range(1, len(msmt_table[0]) - 1): foldattr.append('#%02d' % i) foldattr.append('ave') msmt_table.insert(0, foldattr) msmt_table = list(zip(*msmt_table)) worksheet = workbook.add_worksheet(sheetname) r, c = 0, 0 for line in msmt_table: r, c = worksheet_writeline(worksheet, r, c, line) return clf_smry
def testYieldCurveFwdsGeneration(): # ========================================================================== # A_Auto: USD_IRS_LIBOR_3M # Test based on Auto Zero rates # zeroRates=[0.00233531255143132, # 0.00233531255143297, # 0.00233531255143352, # 0.00233531255143333, # 0.00297455023730465, # 0.00319723236979883, # 0.00330765830399476, # 0.00691586090809845, # 0.01085698247648050, # 0.01394073189902450, # 0.01633917521905550, # 0.02000659612723410, # 0.02346660204636610, # 0.02685361531988290, # 0.02854781579990930, # 0.02975553109452430] # Test based on Manual Zero rates zeroRates = [ 0.00236843676021893, 0.00236821394576394, 0.00236797582662841, 0.00236773777132554, 0.00290042675432344, 0.00308558288679761, 0.00330592988104989, 0.00691281016439806, 0.01085462048320750, 0.01393891008113780, 0.01633775879077160, 0.02000467532244330, 0.02346505198474200, 0.02685243199737300, 0.02854687951971270, 0.02975479160795850 ] yieldCurve = FYC.yieldCurveFactory(times=ZERO_TIMES, zeros=zeroRates, type='linearFwd') runTime = dt.strptime('20141128.230000', dtFmtLog) runDate = runTime.date() # same as outDates # make sure the first out date is the end of the following month nextDate = Cal.NextEndOfMonth(runDate) startDate = nextDate if nextDate.month == runDate.month else runDate dfltFwdDates = SortedList(unfoldGen(func = Cal.NextEndOfMonth, initArg = startDate, numSteps = NUMBER_FWD_MONTHS)) cal = Cal() fwdDates = [[cal.AddSplitTenor(baseDate = baseDate, tenorSize = x[0], tenorUnit = x[1]) for x in [[0,'D']] + TENORS] for baseDate in dfltFwdDates] fwdTimes = [cal.DateTimesToFloats(dateTimes = fwdDateList, baseDate = runDate) for fwdDateList in fwdDates] outFwds = np.empty((len(fwdTimes[0])-1,NUMBER_FWD_MONTHS)) for idx, fwdTimesAtDate in enumerate(fwdTimes): outFwds[:,idx] \ = yieldCurve.getFwds( times = fwdTimesAtDate[1:], baseTime = fwdTimesAtDate[0]) rateTable = np.column_stack((zeroRates, outFwds)) # outFwds = np.maximum(0., outFwds) workbook = Workbook('tests/test_YieldCurve.xlsx',{'constant_memory': True}) headerFormat = workbook.add_format({'bold' : True, 'font_name': 'Arial', 'font_size': 8}) rateFormat = workbook.add_format({'num_format': '0.000%', 'font_name' : 'Arial', 'font_size' : 8, 'border' : 1}) stringFormat = workbook.add_format({'font_name' : 'Arial', 'font_size' : 8, 'border' : 1}) dateFormat = workbook.add_format({'bold' : False, 'num_format': 'mm/dd/yyyy', 'font_name' : 'Arial', 'font_size' : 8, 'border' : 1}) headerDateFormat = workbook.add_format({'bold' : True, 'num_format': 'mm/dd/yyyy', 'font_name' : 'Arial', 'font_size' : 8, 'border' : 1}) # ========================================================================== wks = workbook.add_worksheet("Forwards") row = 1 initCol = 2 col = initCol wks.write_string(row, col, 'Maturities/Dates', headerFormat) col +=1 wks.write_datetime(row, col, runDate, headerDateFormat) for outDate in fwdDates: col +=1 wks.write_datetime(row, col, outDate[0], headerDateFormat) # write values for tenor, rateRow in zip(TENORS, rateTable): row += 1 col = initCol # print row, col, tenor wks.write_string(row, col, "%d%s"%(tenor[0],tenor[1]), headerFormat) col += 1 # print rateRow for rate in rateRow: # print row, col, rate wks.write_number(row, col, rate,rateFormat) col += 1 # ========================================================================== # Format desired output crvName = "USD_IRS_LIBOR_3M" scenarioName = "A_Base" # for baseDate in dfltFwdDates # for tenor in TENORS wks = workbook.add_worksheet("Comparision") initRow = 0 initCol = 0 headerString = ['Curve','Scenario','Base Date', 'Tenor', 'Rate'] wks.write_row(initRow, initCol, headerString, headerFormat) col = initCol row = initRow + 1 for tenor, rateRow in zip(TENORS, outFwds): for baseDate, rate in zip (dfltFwdDates, rateRow): col=initCol wks.write_string(row, col, crvName, stringFormat) col +=1 wks.write_string(row, col, scenarioName, stringFormat) col +=1 wks.write_datetime(row, col, baseDate, dateFormat) col +=1 wks.write_string(row, col, "%d%s"%(tenor[0],tenor[1]), stringFormat) col +=1 wks.write_number(row, col, rate, rateFormat) row +=1 workbook.close()
def generate_report(self): wb = Workbook(BytesIO()) ws = wb.add_worksheet(CarDealerSheet.sheet_name) CarDealerSheet(wb, worksheet=ws) book = xlsx_to_reader(wb) return book.sheet_by_name('Dealership Summary')
def generate_report(self, reportcls=CarSheet): wb = Workbook(BytesIO()) ws = wb.add_worksheet(reportcls.sheet_name) reportcls(wb, worksheet=ws) book = xlsx_to_reader(wb) return book.sheet_by_name('Car Info')
def process(self, files): # Get the filename from the already-open file handle filename = self.fout.name # Close the existing file as the XlsxWriter library handles that for us self.fout.close() # Create the new workbook workbook = Workbook(filename) # Define some common styles/ formats bold = workbook.add_format({ 'bold': 1, }) # Create the main worksheet where all the raw data is shown main_sheet = workbook.add_worksheet('Raw Data') # Write the header main_sheet.write(0, 0, 'IP', bold) # The IP field can be either ip_str or ipv6 so we treat it differently main_sheet.set_column(0, 0, 20) row = 0 col = 1 for field in self.fields: name = self.field_names.get(field, field.capitalize()) main_sheet.write(row, col, name, bold) col += 1 row += 1 total = 0 ports = defaultdict(int) for banner in iterate_files(files): try: # Build the list that contains all the relevant values data = [] for field in self.fields: value = self.banner_field(banner, field) data.append(value) # Write those values to the main workbook # Starting off w/ the special "IP" property main_sheet.write_string(row, 0, get_ip(banner)) col = 1 for value in data: main_sheet.write(row, col, value) col += 1 row += 1 except Exception: pass # Aggregate summary information total += 1 ports[banner['port']] += 1 summary_sheet = workbook.add_worksheet('Summary') summary_sheet.write(0, 0, 'Total', bold) summary_sheet.write(0, 1, total) # Ports Distribution summary_sheet.write(0, 3, 'Ports Distribution', bold) row = 1 col = 3 for key, value in sorted(ports.items(), reverse=True, key=lambda kv: (kv[1], kv[0])): summary_sheet.write(row, col, key) summary_sheet.write(row, col + 1, value) row += 1