def _get_header_styles(self):
s1 = XFStyle()
al = Alignment()
al.horz = Alignment.HORZ_CENTER
s1.alignment = al
s2 = XFStyle()
borders = Borders()
borders.bottom = 2
s2.borders = borders
return s1, s2
def get_default_style():
"""get the default style.
Args:
Example:
Return:
Author: cai, yong
IsInterface: False
ChangeInfo: cai, yong 2019-08-26
"""
style = XFStyle()
return style
#实际在这里开始for，循环的取excel中的动态值
if sheet_read_only.cell_value(6, 2) == 'form':
content_type = 'application/x-www-form-urlencoded'
else:
content_type = 'application/json'
#把取出的值进行转字典
params = eval(sheet_read_only.cell_value(6, 3))
response = request(request_type, url, params=params)
rs = eval(response.text) # 仅仅获得所有的返回值
if response.status_code == sheet_read_only.cell_value(
6, 4) and rs['error_code'] == sheet_read_only.cell_value(6, 5):
original_wb = open_workbook(r'd:\聚合数据.xls', formatting_info=True)
new_wb = copy(original_wb)
sheet = new_wb.get_sheet(0)
border = Borders()
border.left = border.THIN
border.right = border.THIN
border.top = border.THIN
border.bottom = border.THIN
style = XFStyle()
style.borders = border
sheet.write(6, 6, '通过', style)
new_wb.save(r'd:\聚合数据测试报告.xls')
# -*- coding: utf-8 -*-
from xlwt.Formatting import Borders, Font, Alignment, Pattern
from xlwt.Style import XFStyle
from xlwt.Workbook import Workbook
wb = Workbook('utf-8')
sheet = wb.add_sheet('测试报告')
title = ['编号', '姓名', '职业', '上级', '入职日期']
title_style = XFStyle()
border = Borders()
border.left = border.THIN
border.right = border.THIN
border.top = border.THIN
border.bottom = border.THIN
font = Font()
font.bold = True
alignment = Alignment()
alignment.horz = alignment.HORZ_CENTER
pattern = Pattern()
pattern.pattern = pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x35
title_style.borders = border
title_style.font = font
title_style.alignment = alignment
title_style.pattern = pattern
def style_rd2wt(self, rdbook, rdxf):
wtxf = XFStyle()
#
# number format
#
wtxf.num_format_str = rdbook.format_map[rdxf.format_key].format_str
#
# font
#
wtf = wtxf.font
rdf = rdbook.font_list[rdxf.font_index]
wtf.height = rdf.height
wtf.italic = rdf.italic
wtf.struck_out = rdf.struck_out
wtf.outline = rdf.outline
wtf.shadow = rdf.outline
wtf.colour_index = rdf.colour_index
wtf.bold = rdf.bold #### This attribute is redundant, should be driven by weight
wtf._weight = rdf.weight #### Why "private"?
wtf.escapement = rdf.escapement
wtf.underline = rdf.underline_type ####
# wtf.???? = rdf.underline #### redundant attribute, set on the fly when writing
wtf.family = rdf.family
wtf.charset = rdf.character_set
wtf.name = rdf.name
#
# protection
#
wtp = wtxf.protection
rdp = rdxf.protection
wtp.cell_locked = rdp.cell_locked
wtp.formula_hidden = rdp.formula_hidden
#
# border(s) (rename ????)
#
wtb = wtxf.borders
rdb = rdxf.border
# wtb.left = rdb.left_line_style
# wtb.right = rdb.right_line_style
# wtb.top = rdb.top_line_style
# wtb.bottom = rdb.bottom_line_style
wtb.left = 1
wtb.right = 1
wtb.top = 1
wtb.bottom = 1
wtb.diag = rdb.diag_line_style
wtb.left_colour = rdb.left_colour_index
wtb.right_colour = rdb.right_colour_index
wtb.top_colour = rdb.top_colour_index
wtb.bottom_colour = rdb.bottom_colour_index
wtb.diag_colour = rdb.diag_colour_index
wtb.need_diag1 = rdb.diag_down
wtb.need_diag2 = rdb.diag_up
#
# background / pattern (rename???)
#
wtpat = wtxf.pattern
rdbg = rdxf.background
wtpat.pattern = rdbg.fill_pattern
wtpat.pattern_fore_colour = rdbg.pattern_colour_index
wtpat.pattern_back_colour = rdbg.background_colour_index
#
# alignment
#
wta = wtxf.alignment
rda = rdxf.alignment
wta.horz = rda.hor_align
wta.vert = rda.vert_align
wta.dire = rda.text_direction
# wta.orie # orientation doesn't occur in BIFF8! Superceded by rotation ("rota").
wta.rota = rda.rotation
wta.wrap = rda.text_wrapped
wta.shri = rda.shrink_to_fit
wta.inde = rda.indent_level
# wta.merg = ????
#
return wtxf
def _style_factory(self):
return XFStyle()
# -*- coding: utf-8 -*-
from requests.api import request
from xlrd import open_workbook
from xlutils.copy import copy
from xlwt.Formatting import Borders
from xlwt.Style import XFStyle
wb = open_workbook(r'd:\emp.xls',formatting_info=True)
sheet = wb.sheet_by_name('手机号码归属地查询')
row_value = sheet.row_values(1,1)
print(row_value)
content_style = XFStyle()
border = Borders()
border.left = border.THIN
border.right = border.THIN
border.top = border.THIN
border.bottom = border.THIN
content_style.borders = border
url = row_value[1]
params = row_value[3]
response = request(method=row_value[2],url=url, params=params)
print(response.status_code)
print(response.text)
if response.status_code==row_value[5]:
#{"resultcode":"101","reason":"错误的请求KEY","result":null,"error_code":10001}写入对应的单元格
new_wb = copy(wb)
def template_download(request):
"""
导出模板数据到excel
"""
pk = request.GET.get('pk', '')
business_name = request.GET.get('business_name', '')
template_name = request.GET.get('template_name', '')
flag = request.GET.get('flag', '')
res_json = {'result': True, 'isNotPass': True, 'data': u""}
if pk or flag == Flag.EDIT or flag == Flag.TASK or flag == 'False':
business = BUSINESS_MODEL['template']
cc_name = business_name
elif flag == Flag.BUSINESS:
business = BUSINESS_MODEL['instance']
cc_name = business_name.split('_')[0]
else:
res_json['data'] = u'参数错误'
return render_mako_context(request, '/manager/403.html', res_json)
# 获取对应的模版并校验权限
try:
if pk:
template = business.objects.get(id=pk)
Application.objects.get(cc_name=template.business_name,
operator__username=request.user.username)
else:
template = business.objects.get(business_name=business_name,
template_name=template_name)
Application.objects.get(cc_name=cc_name,
operator__username=request.user.username)
except ObjectDoesNotExist:
res_json['data'] = u'您无操作权限,请联系系统管理员'
return render_mako_context(request, '/manager/403.html', res_json)
# 表格初始化
response = HttpResponse(content_type="application/vnd.ms-excel")
response['Content-Disposition'] = 'attachment; filename="%s_%s.xls"' % (
template.business_name.encode('utf-8'),
template.business_type.encode('utf-8'))
wbk = xlwt.Workbook(encoding='utf-8')
sheet = wbk.add_sheet('sheet1')
sheet.col(2).width = 20000
sheet.col(3).width = 20000
# 枚举参数初始化
if flag == Flag.EDIT or flag == Flag.TASK or flag == 'False':
head_list = HeadList.TEMPLATE
excel_list = ExcelList.TEMPLATE
business_step = BUSINESS_STEP_MODEL["template"]
business_detail = BUSINESS_DETAIL_MODEL['template']
q = Q()
else:
q = ~Q(stat=1)
head_list = HeadList.INSTANCE
excel_list = ExcelList.INSTANCE
business_step = BUSINESS_STEP_MODEL['instance']
business_detail = BUSINESS_DETAIL_MODEL['instance']
try:
for i, data in enumerate(head_list):
sheet.write(0, i, data)
row = 1
algn_center_hv = Alignment()
algn_center_hv.horz = Alignment.HORZ_CENTER
algn_center_hv.vert = Alignment.VERT_CENTER
style_cdata = XFStyle()
style_cdata.alignment = algn_center_hv
# 获取步骤详情并填充表格
steps = business_step.objects.filter(business=template).order_by('id')
for step in steps:
details = business_detail.objects.filter(
q, business_step=step).order_by('step_xh')
sheet.write_merge(row, row + details.count() - 1, 0, 0, step.name,
style_cdata)
details = business_detail.to_dict(details)
for detail in details:
for i, value_key in enumerate(excel_list):
sheet.write(row, i + 1, detail[value_key])
row += 1
wbk.save(response)
return response
except Exception, e:
logger.error(u'下载模版失败%s' % e)
res_json['data'] = u'下载模版失败'
return render_mako_context(request, '/manager/403.html', res_json)
def conv_xf_xfstyle(self, xf):
wtxf = XFStyle()
wtf = wtxf.font
rdf = self.rbook.font_list[xf.font_index]
wtf.height = rdf.height
wtf.italic = rdf.italic
wtf.struck_out = rdf.struck_out
wtf.outline = rdf.outline
wtf.shadow = rdf.outline
wtf.colour_index = rdf.colour_index
wtf.bold = rdf.bold #### This attribute is redundant, should be driven by weight
wtf._weight = rdf.weight #### Why "private"?
wtf.escapement = rdf.escapement
wtf.underline = rdf.underline_type ####
# wtf.???? = rdf.underline #### redundant attribute, set on the fly when writing
wtf.family = rdf.family
wtf.charset = rdf.character_set
wtf.name = rdf.name
#
# protection
#
wtp = wtxf.protection
rdp = xf.protection
wtp.cell_locked = rdp.cell_locked
wtp.formula_hidden = rdp.formula_hidden
#
# border(s) (rename ????)
#
wtb = wtxf.borders
rdb = xf.border
wtb.left = rdb.left_line_style
wtb.right = rdb.right_line_style
wtb.top = rdb.top_line_style
wtb.bottom = rdb.bottom_line_style
wtb.diag = rdb.diag_line_style
wtb.left_colour = rdb.left_colour_index
wtb.right_colour = rdb.right_colour_index
wtb.top_colour = rdb.top_colour_index
wtb.bottom_colour = rdb.bottom_colour_index
wtb.diag_colour = rdb.diag_colour_index
wtb.need_diag1 = rdb.diag_down
wtb.need_diag2 = rdb.diag_up
#
# background / pattern (rename???)
#
wtpat = wtxf.pattern
rdbg = xf.background
wtpat.pattern = rdbg.fill_pattern
wtpat.pattern_fore_colour = rdbg.pattern_colour_index
wtpat.pattern_back_colour = rdbg.background_colour_index
wta = wtxf.alignment
rda = xf.alignment
wta.horz = rda.hor_align
wta.vert = rda.vert_align
wta.dire = rda.text_direction
wta.rota = rda.rotation
wta.wrap = rda.text_wrapped
wta.shri = rda.shrink_to_fit
wta.inde = rda.indent_level
return wtxf
# -*-coding:UTF-8 -*-
from xlwt.Workbook import Workbook
from xlwt.Style import XFStyle
from importlib.resources import contents
Workbook
wb=Workbook('utf-8')
sheet=wb.add_sheet('测试')
title = ['编号', '姓名', '职业', '上级', '入职日期']
title_style = XFStyle()
for i in range(len(title)):
sheet.write(4,i + 4,title[i],title_style)
content = [7369,'SMITH','CLERK',7902,12/17/1980] ,[7499,'ALLEN','SALESMAN',7698,2/20/1981]
for i in range(len(content)):
for j in range(len(content[i])):
# -*-coding:UTF-8 -*- from xlrd import open_workbook from xlutils.copy import copy from xlwt.Formatting import Borders from xlwt.Style import XFStyle yuanshi_excel=open_workbook(r'd:\自动化测试.xls',formatting_info=True) new_excel=copy(yuanshi_excel) sheet=new_excel.get_sheet(0) new_content=XFStyle() border=Borders() border.left=border.THIN border.right=border.THIN border.top=border.THIN border.bottom=border.THIN new_content.borders=border sheet.write(3,5,5,new_content) sheet.write(3,8,'mingyue',new_content) new_excel.save(r'd:\新表副本.xls')
def create(self, params, filename=None):
"""
IN
params <dict>
filename <str> (opcional)
OUT
El XML del informe
<str>
"""
util = self.Util()
self.tmpl.globals['util'] = util
self.tmpl.globals['xlrd'] = xlrd
informe_xml = self.tmpl.render(**params).encode('utf-8')
root = etree.fromstring(informe_xml)
wb = Workbook()
estilos = {}
xfs0 = XFStyle()
n_sheet = 0
for sheet in root.iter('sheet'):
n_sheet += 1
cols_width = None
# title
title = sheet.attrib.get('title', 'Sheet-%d' % n_sheet)
# auto-width
sheet_width = sheet.attrib.get('width')
cols_width = {}
ws = wb.add_sheet(title, True)
util.init_count()
for item in sheet:
# style
if item.tag == 'style':
estilos[item.attrib['name']] = easyxf(';'.join(self.calcular_estilo(item)))
# image
if item.tag == 'image':
bm_path = item.find('path').text
col = int(item.attrib.get('col', 0))
scale_x = float(item.attrib.get('scale_x', '1'))
scale_y = float(item.attrib.get('scale_y', '1'))
ws.insert_bitmap(bm_path, util.current_line(), col, scale_x=scale_x, scale_y=scale_y)
# cell
if item.tag == 'cell':
col = int(item.attrib['col'])
num_format = None
# width
width = item.attrib.get('width', sheet_width)
if width is not None:
if not cols_width.get(col):
cols_width[col] = dict(auto=width == 'auto',
width=int(width) if width != 'auto' else 0)
# value
value = item.find('value')
dato = value.text
# type: aplicar una conversión (str, int, float, ...)
type_ = item.attrib.get('type')
if type_:
tipo = type_.split(';')
# date
if tipo[0] == 'date':
try:
dato = strtodate(dato, fmt='%Y-%m-%d')
except:
dato = None
# time
elif tipo[0] == 'time':
try:
dato = strtotime(dato)
except:
dato = None
# formula
elif tipo[0] == 'formula':
dato = Formula(dato)
# resto de tipos
else:
if dato != '':
try:
f = lambda t,v: t(v)
dato = f(eval(tipo[0]), dato)
except:
dato = None
# date;dd/mm/yyyy
# date;dd/mmm/yyyy
# date;NN D MMMM YYYY
# time;hh:mm
# float;#,##0.00
# float;+#,##0.00
# float;#,##0.00;[RED]-#,##0.00
if len(tipo) > 1:
num_format = ';'.join(tipo[1:])
#print dato, tipo, num_format
# style: aplicar un estilo
estilo = item.find('style')
xfs = xfs0
if estilo != None:
if not estilos.has_key(estilo.attrib['name']):
xfs = easyxf(';'.join(self.calcular_estilo(estilo)))
estilos[estilo.attrib['name']] = xfs
else:
xfs = estilos[estilo.attrib['name']]
xfs.num_format_str = 'General'
if num_format:
if not xfs:
xfs = XFStyle()
xfs.num_format_str = num_format
# merge
if item.attrib.has_key('merge') and item.attrib['merge']:
try:
_merge = re.search(r'(\d+)\s+(\d+)', item.attrib['merge'], re.U)
if _merge:
hg = int(_merge.group(1))
wd = int(_merge.group(2))
_current_line = util.current_line()
ws.write_merge(_current_line, _current_line + hg - 1,
col, col + wd -1,
dato, xfs)
else:
raise Exception(u'merge: Invalid format')
except Exception:
ws.write(util.current_line(), col, dato, xfs)
else:
ws.write(util.current_line(), col, dato, xfs)
# width
colw = cols_width.get(col, None)
if colw is not None:
if colw['auto']:
try:
width_ = len(str(dato))
except:
width_ = len(unicode(dato))
if width_ > colw['width']:
cols_width[col]['width'] = width_
elif item.tag == 'line_feed':
n = int(item.attrib.get('n', 1))
util.line_feed(n)
elif item.tag == 'bookmark':
util.add_bookmark(item.attrib['name'])
# width
if cols_width is not None:
for col, colw in cols_width.iteritems():
if colw['width'] < 10:
colw['width'] = colw['width'] + 2
ws.col(col).width = colw['width']*256
if filename:
wb.save(filename)
return informe_xml
