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
