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 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 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 action(args):
book = Workbook()
filetype = args.type
(infiles, ) = args.infiles
if filetype == 'Analysis':
tabs = ['0_QC', '1_QC_Metrics', '2_QC_by_Gene', '3_QC_by_Exon',
'4_SV_Gridss', '5_SV_Breakdancer', '6_SV_Pindel',
'7_CNV_Gene', '8_CNV_Exon', '9_Clinically_Flagged',
'10_SNP','11_INDEL', '12_MSI', '13_Amplicons', '14_PolyHunter']
# for each tab, find its file and process.
for tab in tabs:
try:
#Find file in infiles
sheet_name, fname = process_files(infiles, tab, filetype)
print sheet_name, fname
write_workbook(sheet_name, book, fname)
except TypeError:
print "Tab %s not processed" % tab
elif filetype == 'Combined':
for fname in infiles:
(f_path, f_name) = os.path.split(fname)
if re.search(str(filetype), f_name):
(f_short_name, f_extension) = os.path.splitext(f_name)
sheet_name = f_short_name.split('Combined_')
sheet_name = '_'.join(sheet_name[1:30])
print sheet_name, fname
write_workbook(sheet_name, book, fname)
book.filename=args.outfile
book.close()
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 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 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 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 excel_export_members_filter(request):
response = HttpResponse(content_type='application/vnd.ms-excel')
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 is not None:
member.depot_name = member.subscription.depot.name
looco_full_name = member.first_name + ' ' + member.last_name
worksheet_s.write_string(row, 0, looco_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 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 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 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 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 __init__(self, uploaded_data, filename=None, response=None):
"""
Формирует объект для выгрузки в файл
:param response: респонс (если отдаем на скачивание)
:param filename: имя файла (если пишем в ФС)
:param uploaded_data: данные для выгрузки
"""
if filename is not None:
self._workbook = Workbook(filename)
elif response is not None:
self._workbook = Workbook(response, {'in_memory': True})
else:
raise RuntimeError('work book must be save to file or response')
self._worksheet = self._workbook.add_worksheet('Получено с сайта')
self._uploaded_data = uploaded_data
self._init_formats()
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 __init__(self):
self.output = io.BytesIO()
self.workbook = Workbook(
self.output, {
'constant_memory': True,
'default_date_format': 'yyyy-mm-dd',
'strings_to_urls': False
})
self.worksheets = {}
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 get(self, match_id, state):
match = Match.get_or_404(id=match_id)
if state == "normal":
# 查询正常参赛成员
members = MatchMember\
.query_all_members(match_id)\
.where(MatchMember.state == MatchMember.MatchMemberState.normal)
else:
members = MatchMember.query_all_members(match_id)
# 排除文件和图片项
option_info_list = [
option_info for option_info in match.option_info_list
if not (option_info.is_photo() or option_info.is_file() or
option_info.is_avatar() or option_info.is_idcard_photo())
]
option_name_list = [
option_info.option_name for option_info in option_info_list
]
option_name_list.extend(["报名时间", "状态"])
file_name = "{match_name}成员列表.xlsx"\
.format(match_name=match.title)
# temp_file_name = str(uuid.uuid4())
o = io.BytesIO()
with Workbook(o) as workbook:
worksheet = workbook.add_worksheet()
row, col = 0, 0
worksheet.write_row(row, col, option_name_list)
for member in members:
row, col = row + 1, 0
assert isinstance(member, MatchMember)
for option_info in option_info_list:
option_value = option_info.get_option_value(member)
# 处理布尔值显示
if option_info.is_leader_check() or option_info.is_boolean(
):
option_value = "是" if option_value else "否"
if option_info.is_gender():
option_value = member.display_gender()
worksheet.write(row, col, option_value)
col += 1
worksheet.write(row, col, member.created.strftime('%Y.%m.%d'))
worksheet.write(row, col + 1, member.state_name)
data = o.getvalue()
o.close()
self.set_header("Content-Type", "application/octet-stream")
self.set_header("Content-Disposition",
"attachment; filename={0}".format(file_name))
self.add_header("Content-Length", len(data))
self.set_header("Content-Transfer-Encoding", "binary")
self.write(data)
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 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 open(self, output):
self.workbook = Workbook(output)
# Header styles
self.styles["query"] = self.workbook.add_format({
"bold": True,
"font_size": 14
})
self.styles["section"] = self.workbook.add_format({
"bold": True,
"font_size": 12
})
self.styles["highlight"] = self.workbook.add_format({
"underline": True,
"font_color": "blue",
"font_size": 10.5
})
self.styles["highlight-noref"] = self.workbook.add_format(
{"font_size": 10.5})
self.styles["header"] = self.workbook.add_format({
"bold": True,
"underline": True,
"bg_color": "#cccccc"
})
# Common style definitions
common = {"text_wrap": True, "align": "vcenter", "font_size": 10.5}
url = {"font_color": "blue", "underline": True}
even = {"bg_color": "#f5f5f5"}
# Column styles
self.styles["default"] = self.workbook.add_format(common)
self.styles["url"] = self.workbook.add_format({**common, **url})
# Even row column styles
self.styles["default-even"] = self.workbook.add_format({
**common,
**even
})
self.styles["url-even"] = self.workbook.add_format({
**common,
**url,
**even
})
def save_xlsx(self, filename: str) -> bool:
try:
import xlsxwriter
from xlsxwriter import Workbook
from xlsxwriter.format import Format
from xlsxwriter.worksheet import Worksheet
except ImportError as ex:
self.status_bar.showMessage(' '.join(repr(a) for a in ex.args))
return False
visible_column_indices: List[int] = [
index for index, title in enumerate(self.table_model.header)
if self.settings.is_visible(title)
]
visible_column_names: List[str] = list(
filter(self.settings.is_visible, self.table_model.header))
try:
workbook: Workbook = Workbook(
filename, {
'default_date_format': 'dd.mm.yyyy hh:mm:ss',
'nan_inf_to_errors': True
})
header_format: Format = workbook.add_format({'bold': True})
worksheet: Worksheet = workbook.add_worksheet(
str(Path(self._opened_file_name).with_suffix('').name))
worksheet.freeze_panes(1, 0) # freeze first row
col: int = 0
_col: int
row: int
for _col in range(self.table_model.columnCount()):
if _col not in visible_column_indices:
continue
worksheet.write_string(0, col, visible_column_names[col],
header_format)
if visible_column_names[col].endswith(('(s)', '(secs)')):
for row in range(
self.table_model.rowCount(available_count=True)):
worksheet.write_datetime(
row + 1, col,
datetime.fromtimestamp(
self.table_model.item(row, _col)))
else:
for row in range(
self.table_model.rowCount(available_count=True)):
worksheet.write_number(
row + 1, col, self.table_model.item(row, _col))
col += 1
workbook.close()
except IOError as ex:
self.status_bar.showMessage(' '.join(ex.args))
return False
else:
self._exported_file_name = filename
self.status_bar.showMessage(
self.tr('Saved to {0}').format(filename))
return True
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()
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 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 format_data_as_xlsx(data: List[List[str]]) -> BytesIO:
output = BytesIO()
with Workbook(output) as workbook:
worksheet = workbook.add_worksheet()
for row_num, row in enumerate(data):
for col_num, col in enumerate(row):
worksheet.write(row_num, col_num, col)
output.seek(0)
return output
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
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 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 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 write_anon_books(self, ddir):
if not os.path.isdir(ddir):
if os.path.exists(ddir):
raise ValueError
os.makedirs(ddir, exist_ok=True)
for i, b in enumerate(self._books):
newbook = os.path.join(ddir, 'anonymized_%d.xlsx' % i)
with Workbook(newbook) as wb:
bold = wb.add_format({'bold': True})
self._write_next_book(b, wb, bold)
def get_writer_format(workbook: xlsxwriter.Workbook,
style: ColumnStyle) -> Union[Tuple[Format, int], None]:
if style:
wb_format = workbook.add_format({
'text_wrap': 1,
'valign': 'top'
}) if style['wrap_text'] else None
return wb_format, style['width']
else:
return None
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 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 generate_pricelist(**options):
output = BytesIO()
book = Workbook(output)
sheet = book.add_worksheet()
row = 0
col = 0
merge_format = book.add_format({
'align':'center',
'valign':'vcenter',
'bold': 1,
})
url_format = book.add_format({
'font_color': 'blue',
'underline': 1
})
user = options['user'][0] if options.get('user') else None
if options.get('filters'):
filters = options.get('filters')
for product in Product.objects.filter(**filters):
sheet.write(row, col, product.model)
# sheet.write(row, col + 1, product.name)
sheet.write_url(row, col + 1, "http://%s/%s" % (BASE_URL,product.slug), url_format, product.names(lang='ru'))
sheet.write(row, col + 2, product.big_opt_price)
row += 1
else:
for cat in Category.objects.filter(active=True):
if cat.is_leaf_node():
sheet.merge_range(row,col,row,2,cat.names(lang='ru'),merge_format)
sheet.write(row, col, cat.names(lang='ru'))
row +=1
if not user:
products = Product.objects.filter(category=cat,is_available=True)
else:
products = Product.objects.filter(category=cat,is_available=True,storage=1)
for product in products:
sheet.write(row, col, product.model)
# sheet.write(row, col + 1, product.name)
sheet.write_url(row, col + 1, "http://%s/%s" % (BASE_URL,product.slug), url_format, product.names(lang='ru'))
if not user:
sheet.write(row, col + 2, product.retail_price)
else:
sheet.write(row, col + 2, product.big_opt_price)
row += 1
book.close()
output.seek(0)
if options.get('name'):
path = STATIC_ROOT + options.get('name') + '.xlsx'
else:
path = 'static/pricelist_opt.xlsx' if user else 'static/pricelist.xlsx'
with open(path,'wb') as f:
f.write(output.read())
print('Завершено.')
def create_excel(self, submission_list):
workbook_file = tempfile.NamedTemporaryFile(suffix=".xlsx",
delete=False)
workbook = Workbook(workbook_file, options={'constant_memory': True})
excel_headers = AdvancedQuestionnaireSubmissionExportHeaderCreator(
self.columns, self.form_model).create_headers()
create_multi_sheet_excel_headers(excel_headers, workbook)
sheet_names_index_map = dict([
(sheet_name, index)
for index, sheet_name in enumerate(excel_headers.iterkeys())
])
sheet_name_row_count_map = dict([
(sheet_name, 0) for sheet_name in sheet_names_index_map.iterkeys()
])
formatter = AdvanceSubmissionFormatter(self.columns, self.form_model,
self.local_time_delta)
for row_number, row_dict in enumerate(submission_list):
formatted_values, formatted_repeats = [], {}
if row_number == 20000:
# export limit set to 20K after performance exercise
#since scan & scroll API does not support result set size the workaround is to handle it this way
break
result = formatter.format_row(row_dict['_source'], row_number,
formatted_repeats)
if self.form_model.has_nested_fields:
result.append(row_number + 1)
formatted_values.append(result)
formatted_repeats.update({'main': formatted_values})
create_multi_sheet_entries(formatted_repeats, workbook,
sheet_names_index_map,
sheet_name_row_count_map)
workbook.close()
return workbook_file
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 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 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 __init__(self, fout_xlsx, nt_flds, **kws):
# KEYWORDS FOR WRITING DATA:
self.vars = WrXlsxParams(nt_flds, **kws)
# Workbook
from xlsxwriter import Workbook
self.workbook = Workbook(fout_xlsx)
self.fmt_hdr = self.workbook.add_format(self.dflt_fmt_hdr)
self.fmt_txt = {
'plain': self.workbook.add_format(self.vars.fmt_txt_arg[0]),
'plain bold': self.workbook.add_format(self.vars.fmt_txt_arg[3]),
'very light grey' : self.workbook.add_format(self.vars.fmt_txt_arg[1]),
'light grey' :self.workbook.add_format(self.vars.fmt_txt_arg[2])}
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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')