def get_styles():
bold = xlwt.easyfont('bold true, height 220, name Helvetica Neue')
brown = xlwt.easyfont('color_index brown, name Helvetica Neue, height 220')
italic = xlwt.easyfont(
'color_index gray50, italic true, name Helvetica Neue, height 220')
gray = xlwt.easyfont('color_index gray50, name Helvetica Neue, height 220')
return bold, brown, italic, gray
def model_to_excel(query_set: QuerySet, field_names=None) -> BytesIO:
workbook = xlwt.Workbook()
sheet = workbook.add_sheet("Сертификаты")
title_style = xlwt.XFStyle()
borders = xlwt.Borders()
borders.bottom, borders.right, borders.left = xlwt.Borders.MEDIUM, xlwt.Borders.MEDIUM, xlwt.Borders.MEDIUM
title_style.borders = borders
title_style.font = xlwt.easyfont(f"bold on, height {12 * 20};")
font_style = xlwt.XFStyle()
font_style.font = xlwt.easyfont(f"height {12 * 20};")
attrs = {}
model = query_set.model
if field_names is None:
for field in model._meta.fields:
attrs[field] = True
else:
for name in field_names:
try:
attrs[model._meta.get_field(name)] = True
except:
attr = getattr(model, name)
field_name = re.search(r"get_\w+_display", name)
if field_name:
field_name = field_name.string.replace("get_", "").replace("_display", "")
field = model._meta.get_field(field_name)
attr.short_description = str(field.verbose_name if hasattr(field, 'verbose_name') else field.name)
attrs[attr] = False
record_list = list(query_set)
for c, (attr, is_field) in enumerate(attrs.items()):
if is_field:
name = str(attr.verbose_name if hasattr(attr, 'verbose_name') else attr.name)
else:
name = str(attr.short_description if hasattr(attr, 'short_description') else attr.__name__)
sheet.write(0, c, name, title_style)
width = len(name) if len(name) > 4 else 4
for r, record in enumerate(record_list):
if is_field:
value = getattr(record, attr.name)
else:
value = attr(record)
value = pretty_string(value)
sheet.write(r+1, c, value, font_style)
width = len(value) if len(value) > width else width
sheet.col(c).width = width*440
file = BytesIO()
workbook.save(file)
return file
def build_certificate_excel(queryset):
workbook = xlwt.Workbook()
sheet = workbook.add_sheet("Сертификаты")
title_style = XFStyle()
borders = Borders()
borders.bottom = Borders.MEDIUM
borders.right = Borders.MEDIUM
borders.left = Borders.MEDIUM
title_style.borders = borders
title_style.font = easyfont(f"bold on, height {12*20};")
font_style = XFStyle()
font_style.font = easyfont(f"height {12*20};")
sheet.write(0, 1, "ФИО", title_style)
sheet.write(0, 2, "№ Договора", title_style)
sheet.write(0, 3, "ИНН", title_style)
sheet.write(0, 4, "День", title_style)
sheet.write(0, 5, "Месяц", title_style)
sheet.write(0, 6, "Год", title_style)
sheet.write(0, 7, "Сертификат №", title_style)
for r, obj in enumerate(queryset):
n = r + 1
sheet.write(n, 0, str(n), font_style)
sheet.write(n, 1, str(obj.full_name), font_style)
sheet.write(n, 2, str(obj.contract_n), font_style)
sheet.write(n, 3, str(obj.inn), font_style)
sheet.write(n, 4, str(obj.date_received.year), font_style)
sheet.write(n, 5, str(obj.date_received.month), font_style)
sheet.write(n, 6, str(obj.date_received.day), font_style)
sheet.write(n, 7, str(obj.certificate_n), font_style)
sheet.col(0).width = 1400
sheet.col(1).width = 14000
sheet.col(2).width = 4500
sheet.col(3).width = 4000
sheet.col(7).width = 5500
file = BytesIO()
workbook.save(file)
return file
# -*- coding: utf-8 -*-
"""
Created on Mon Nov 06 22:13:26 2017
@author: Administrator
问题：如何读写excel文件
使用第三方库xlrd和xlwt，这两个库分别用于excel读和写
"""
import xlrd, xlwt
rbook = xlrd.open_workbook('demo.xlsx')
rsheet = rbook.sheet_by_index(0) # 获取sheet
nc = rsheet.ncols
rsheet.put_cell(0, nc, xlrd.XL_CELL_TEXT, u'总分', None) # 写入单元格
for row in range(1, rsheet.nrows):
t = sum(rsheet.row_values(row, 1)) # 求和算总分
rsheet.put_cell(row, nc, xlrd.XL_CELL_NUMBER, t, None)
wbook = xlwt.Workbook()
wsheet = wbook.add_sheet(rsheet.name)
style = xlwt.easyfont('aligh: vertical center, horizontal center') #设置字体的格式
for r in range(rsheet.nrows):
for c in range(rsheet.ncols):
wsheet.write(r, c, rsheet.cell_value(r, c), style)
wbook.save('output.xlsx') # 保存新表格
def report_routes(self, reportname, docids=None, converter=None, **data):
report_obj = request.env['ir.actions.report']._get_report_from_name(
reportname)
context = dict(request.env.context)
if docids:
docids = [int(i) for i in docids.split(',')]
if data.get('options'):
data.update(json.loads(data.pop('options')))
if data.get('context'):
# Ignore 'lang' here, because the context in data is the one from the webclient *but* if
# the user explicitely wants to change the lang, this mechanism overwrites it.
data['context'] = json.loads(data['context'])
if data['context'].get('lang'):
del data['context']['lang']
context.update(data['context'])
if ('skit_report_type' in data.keys()
and data['skit_report_type'] == 'XLS'):
html = report_obj.with_context(context).render_qweb_html(
docids, data=data)[0]
wb = xlwt.Workbook(encoding="UTF-8", style_compression=2)
ws = wb.add_sheet('report')
# f = urllib.urlopen(
# "http://www.ebi.ac.uk/Tools/services/web/blastresult.ebi?tool=ncbiblast&jobId=ncbiblast-I20120714-161017-0108-80986175-pg&context=nucleotide")
# html = f.read()
soup = BeautifulSoup(html, 'html.parser')
# print soup.prettify()
# print soup
x = 0
h2 = soup.find(lambda elm: elm.name == "h2")
h3 = soup.find(lambda elm: elm.name == "h3")
if (h2):
ws.write_merge(
x, 0, 0, 7, str(h2.text),
xlwt.easyxf(
'font:height 300, bold True, name Arial; align: horiz center, vert center;borders: top medium,right medium,bottom medium,left medium'
))
elif (h3):
ws.write_merge(
x, 0, 0, 7, str(h3.text),
xlwt.easyxf(
'font:height 300, bold True, name Arial; align: horiz center, vert center;borders: top medium,right medium,bottom medium,left medium'
))
font2 = xlwt.easyfont('bold true')
currency_style = xlwt.XFStyle()
currency_style.num_format_str = "#,##0.00"
# Set font style to Bold only for TH tag
currency_style_bold = xlwt.XFStyle()
currency_style_bold.num_format_str = "#,##0.00"
borders = xlwt.Borders()
borders.top = xlwt.Borders.THICK
borders.bottom = xlwt.Borders.THICK
#num_format = xlwt.easyxf("", "#,##0.00")
# num_format.alignment = "right"
# num_format.num_format = "#,###.00"
div_row = soup.findAll("div", {"class": "row"})
if (
h2 != None and h2.text == 'Profit and Loss'
) or reportname == 'skit_financial_report.report_agedpartnerbalance_detail':
report_div = soup.findAll("span", {"class": "Reportrundate"})
for report_datetime in report_div:
txt = 'As of ' + report_datetime.text
ws.write(1, 3, txt, xlwt.easyxf('font:bold True'))
for div in div_row:
left_col = div.findAll(
"div",
{"class": ["col-xs-4", "col-xs-3", "col-4", "col-3"]})
x = x + 1
y = 0
for div_col in left_col:
strong = div_col.findAll("strong")
p = div_col.findAll("p")
span = div_col.findAll("span")
for i in range(len(strong)):
if (strong[i] != None and len(p) > 0
and len(span) == 0):
ws.write_rich_text(
x, y,
((strong[i].text, font2), " ", p[i].text))
y = y + 1
elif (strong[i] != None and len(span) > 0):
ws.write_rich_text(
x, y,
((strong[i].text, font2), " ", span[i].text))
y = y + 1
# print(div_col.find("strong").text)
# print(div_col.find("p"))
# y = y + 1
table = soup.find("table")
rows = table.findAll("tr")
x = x + 2
h = 0
for tr in rows:
style = xlwt.easyxf('font:bold False')
if "style" in tr.attrs:
if (tr['style'] == 'font-weight: bold;'):
style = xlwt.easyxf('font:bold True')
header = tr.findAll("th")
y = 0
if (len(header) > 0):
if (h == 0):
for th in header:
if reportname == 'skit_financial_report.report_agedpartnerbalance_detail':
# Set font style to Bold only for TH tag
currency_style_bold.font = font2
texte_bu = th.text
if (texte_bu == 'Total '.decode('utf-8')):
texte_bu = '.....Total'
# To print currency value in right side of a column
if th.has_attr('class') and th['class'][
0] == 'display_currency_format':
if th.has_attr('groups') and th[
'groups'] == "base.group_multi_currency":
span_curr = th.findAll('span')
texte_bu = span_curr[0].text
texte_bu = texte_bu.replace(
data['skit_currency'], '')
if (len(texte_bu) > 0):
ws.write(
x, y,
float(
float(
re.sub(
r'[^0-9.-]', '',
texte_bu))),
currency_style_bold)
else:
ws.write(x, y, texte_bu,
currency_style_bold)
else:
if (texte_bu == 'Total'):
ws.write(
x, y, texte_bu,
xlwt.easyxf(
'font:bold True; align: horiz right'
))
else:
ws.write(x, y, texte_bu,
xlwt.easyxf('font:bold True'))
else:
ws.write(x, y, th.text,
xlwt.easyxf('font:bold True'))
h = h + 1
y = y + 1
x = x + 1
y = 0
cols = tr.findAll("td")
if not cols:
# when we hit an empty row, we should not print anything to the workbook
continue
for td in cols:
border_line_style = xlwt.XFStyle()
if "style" in tr.attrs:
if (tr['style'] == 'font-weight: bold;'):
border_line_style.font = font2
texte_bu = td.text
if (
reportname ==
'skit_website_deity_lights.report_print_dl_birthdayreport'
) or (reportname
== 'skit_event_report.report_deity_event') or (
reportname
== 'skit_event_report.report_cny_event'
) or (reportname
== 'skit_event_report.report_puja_event') or (
reportname
== 'skit_event_report.report_gr_event'
) or (reportname
== 'skit_event_report.report_hf_event'):
texte_bu = td.text
else:
texte_bu = texte_bu.replace("\n", "").replace("‑", "-")
if (("style" in td.attrs) and
(td['style'] ==
'border-bottom: 2px solid; border-top: 2px solid;')):
border_line_style.borders = borders
currency_style.borders = borders
else:
borders1 = xlwt.Borders()
borders1.top = xlwt.Borders.NO_LINE
borders1.bottom = xlwt.Borders.NO_LINE
border_line_style.borders = borders1
currency_style.borders = borders1
texte_bu = texte_bu.strip()
if td.has_attr('class') and td['class'][0] == 'text-right':
span_curr1 = td.findAll('span')
if span_curr1[0].has_attr('class') and span_curr1[0][
'class'][0] == 'set_bracket':
currency_style.num_format_str = "(#,##0.00)"
else:
currency_style.num_format_str = "#,##0.00"
if td.has_attr('groups') and td[
'groups'] == "base.group_multi_currency":
span_curr = td.findAll('span')
texte_bu = span_curr[0].text
texte_bu = texte_bu.replace(data['skit_currency'], '')
if (len(texte_bu) > 0):
ws.write(
x, y,
float(float(re.sub(r'[^0-9.-]', '',
texte_bu))), currency_style)
else:
ws.write(x, y, texte_bu, currency_style)
else:
ws.write(x, y, texte_bu, border_line_style)
# print(x, y, td.text)
if "colspan" in td.attrs:
y = y + int(td['colspan']) - 1
y = y + 1
# update the row pointer AFTER a row has been printed
# this avoids the blank row at the top of your table
x = x + 1
fp = BytesIO()
wb.save(fp)
fp.seek(0)
data = fp.read()
fp.close()
pdfhttpheaders = [('Content-Type', 'application/vnd.ms-excel')]
return request.make_response(data, headers=pdfhttpheaders)
elif converter == 'html':
html = report_obj.with_context(context).render_qweb_html(
docids, data=data)[0]
return request.make_response(html)
elif converter == 'pdf':
pdf = report_obj.with_context(context).render_qweb_pdf(
docids, data=data)[0]
pdfhttpheaders = [('Content-Type', 'application/pdf'),
('Content-Length', len(pdf))]
return request.make_response(pdf, headers=pdfhttpheaders)
else:
raise exceptions.HTTPException(
description='Converter %s not implemented.' % converter)
def retrieve_pos(args, input_file):
sucess = False
contig = ""
homology_seq = ""
insertion_seq = ""
deletions = 0
homology = 0
insertions = 0
for line in open(input_file):
if line[0] == "@":
continue
content = line.strip().split("\t")
contig = content[9]
AB = True
if not content[2] == args.chrA and not content[2] == args.chrB:
continue
pos = int(content[3])
if pos >= args.posA - args.padding and pos <= args.posA + args.padding:
pass
elif pos >= args.posB - args.padding and pos <= args.posB + args.padding:
AB = False
else:
continue
if not "SA:Z:" in line:
continue
SA_line = line.strip().split("SA:Z:")[-1].split("\t")[0]
SA_fields = SA_line.strip(";").split(";")
found = False
for SA in SA_fields:
deletions = 0
insertions = 0
#print SA
split_read = SA.split(",")
#print split_read
pos = int(split_read[1])
cigar_del, cigar_ins, length, clip_after, range_secondary = read_cigar(
split_read[3], len(contig))
SA_orientation = split_read[2]
SA_start = pos
SA_end = length + pos - 1
deletions += cigar_del
insertions += cigar_ins
SA_clip_after = clip_after
SA_len = length
#print "{} {} {}".format(pos,startB,stopB)
if split_read[
0] == args.chrB and pos >= args.posB - args.padding and pos <= args.posB + args.padding and AB:
found = True
break
elif split_read[
0] == args.chrA and pos >= args.posA - args.padding and pos <= args.posA + args.padding and not AB:
found = True
break
if not found:
continue
flag = "{0:012b}".format(int(content[1]))
#the read is only accepted if it is a primary alignemnt, not a secondary alignment, and if it is only split in two parts
if not int(flag[-9]) and not int(flag[0]) and len(SA_fields) == 1:
orientationA = "+"
orientationB = SA_orientation
if int(flag[-5]):
orientationA = "-"
cigar_del, cigar_ins, length, clip_after, range_primary = read_cigar(
content[5], len(contig))
deletions += cigar_del
insertions += cigar_ins
start_primary = min(range_primary)
start_secondary = min(range_secondary)
if orientationB != orientationA:
start_secondary = len(contig) - max(range_secondary) + 1
if start_primary < start_secondary:
posA = int(content[3]) + length - 1
else:
posA = int(content[3])
start_primary = min(range_primary)
start_secondary = min(range_secondary)
if orientationB != orientationA:
start_primary = len(contig) - max(range_primary) + 1
if start_secondary < start_primary:
posB = SA_end
else:
posB = SA_start
if orientationB != orientationA:
for i in range(0, len(range_secondary)):
range_secondary[i] = len(contig) + 1 - range_secondary[i]
range_secondary = sorted(range_secondary)
homology = len(
set(range_primary).intersection(set(range_secondary)))
homology_seq = ""
homologous_pos = sorted(
list(set(range_primary).intersection(set(range_secondary))))
for i in range(0, len(homologous_pos)):
if i == len(homologous_pos) - 1:
homology_seq += contig[homologous_pos[i] - 1]
else:
if homologous_pos[i] + 1 == homologous_pos[i + 1]:
homology_seq += contig[homologous_pos[i] - 1]
else:
homology_seq += contig[homologous_pos[i] - 1] + ","
insertion_seq = ""
insertion_range = sorted(
list(
set(range(1,
len(contig) + 1)).difference(
set(range_secondary).union(
set(range_primary)))))
if insertion_range:
insertions = len(insertion_range)
for i in range(0, len(insertion_range)):
if i == len(insertion_range) - 1:
insertion_seq += contig[insertion_range[i] - 1]
else:
if insertion_range[i] + 1 == insertion_range[i + 1]:
insertion_seq += contig[insertion_range[i] - 1]
else:
insertion_seq += contig[insertion_range[i] - 1] + ","
contigA = ""
for i in range(0, len(range_primary)):
contigA += contig[range_primary[i] - 1]
contigB = ""
reverse_comp = {
"A": "T",
"a": "t",
"T": "A",
"t": "a",
"G": "C",
"g": "c",
"C": "G",
"c": "g"
}
for i in range(0, len(range_secondary)):
contigB += contig[range_secondary[i] - 1]
if orientationA == "-":
tmpA = ""
for i in range(0, len(contigA)):
tmpA += reverse_comp[contigA[len(contigA) - i - 1]]
contigA = tmpA
tmpContig = ""
tmphomology = ""
if not "," in homology_seq:
for i in range(0, len(homology_seq)):
tmphomology += reverse_comp[homology_seq[
len(homology_seq) - i - 1]]
else:
homologous_sequences = homology_seq.split(",")
for seq in homologous_sequences:
for i in range(0, len(seq)):
if not i == 0:
tmphomology += ","
tmphomology += reverse_comp[seq[len(seq) - i - 1]]
homology_seq = tmphomology
for i in range(0, len(contig)):
tmpContig += reverse_comp[contig[len(contig) - i - 1]]
contig = tmpContig
if orientationB == "-" and orientationA == "-":
tmpB = ""
for i in range(0, len(contigB)):
tmpB += reverse_comp[contigB[len(contigB) - i - 1]]
contigB = tmpB
fontseq = xlwt.easyfont('')
fontA = xlwt.easyfont('color_index green')
fontHOM = xlwt.easyfont('color_index red')
fontSEQ = xlwt.easyfont('color_index orange')
fontB = xlwt.easyfont('color_index blue')
seq_norm = ""
tupleB = []
tupleA = []
tupleCtg = []
tupleH = []
for i in range(0, len(contigA)):
pos = range_primary[i]
if orientationA == "-":
pos = range_primary[len(range_primary) - 1 - i]
if pos in homologous_pos:
tupleA.append((contigA[i], fontHOM))
else:
tupleA.append((contigA[i], fontA))
for i in range(0, len(contigB)):
pos = range_secondary[i]
if orientationB == "-" and orientationA == "-":
pos = range_secondary[len(range_secondary) - 1 - i]
if pos in homologous_pos:
tupleB.append((contigB[i], fontHOM))
else:
tupleB.append((contigB[i], fontB))
for i in range(0, len(contig)):
pos = i + 1
if orientationA == "-":
pos = len(contig) - i
if pos in homologous_pos:
tupleCtg.append((contig[i], fontHOM))
elif pos in range_primary:
tupleCtg.append((contig[i], fontA))
elif pos in range_secondary:
tupleCtg.append((contig[i], fontB))
else:
tupleCtg.append((contig[i], fontSEQ))
for i in range(0, len(homology_seq)):
tupleH.append((homology_seq[i], fontHOM))
sucess = True
break
if sucess:
if AB:
args.posA = posA
args.orientationA = orientationA
args.posB = posB
args.orientationB = orientationB
args.lengthA = length
args.lengthB = SA_len
args.regionA = contigA
args.regionB = contigB
args.regionAsegments = tuple(tupleA)
args.regionBsegments = tuple(tupleB)
args.contigSegments = tuple(tupleCtg)
else:
args.posB = posA
args.orientationB = orientationA
args.posA = posB
args.orientationA = orientationB
args.lengthA = SA_len
args.lengthB = length
args.regionA = contigB
args.regionB = contigA
args.regionAsegments = tuple(tupleB)
args.regionBsegments = tuple(tupleA)
args.contigSegments = tuple(tupleCtg)
args.HomologySegments = tuple(tupleH)
return (args, sucess, contig, homology, homology_seq, insertions,
insertion_seq, deletions)