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
def genAttendanceSheet(lis):
""" lis is UserRecord list """
sheet2 = wb.add_sheet(ATTENDANCE_SHEET_NAME, True)
percentStyle = XFStyle()
percentStyle.num_format_str = '0%'
integerStyle = XFStyle()
integerStyle.num_format_str = '0'
titles = ('姓名', '总工作日（天）', '总工时（小时）', '个人总出勤（小时）', '个人日均（小时）', '个人出勤率')
row = 0
for i, item in enumerate(titles):
sheet2.write(row, i, item)
row += 1
workdays = workdayIntVar.get()
workhours = workdays * 8
lis.sort(key=attrgetter('totalTime'), reverse=True)
for u in lis:
hours = round(u.totalTime / 3600.0)
sheet2.write(row, 0, u.userName)
sheet2.write(row, 1, workdays)
sheet2.write(row, 2, workhours, integerStyle)
sheet2.write(row, 3, hours, integerStyle)
sheet2.write(row, 4, hours / workdays, integerStyle)
sheet2.write(row, 5, round(hours / workhours, 2), percentStyle)
row += 1
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
# -*- 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
# -*- 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 add_ideogram_table(self, analyses,
widths=None,
# configure_table=True,
add_title=False, add_header=False, tablenum=1, **kw):
sh = self._wb.add_sheet(analyses[0].labnumber)
# 'sheet{:02n}'.format(self._sheet_count))
self._sheet_count += 1
cols = ['Status', 'N', 'Power', 'Moles_40Ar',
'Ar40', 'Ar40Er',
'Ar39', 'Ar39Er',
'Ar38', 'Ar38Er',
'Ar37', 'Ar37Er',
'Ar36', 'Ar36Er',
'Blank',
'Ar40', 'Ar40Er',
'Ar39', 'Ar39Er',
'Ar38', 'Ar38Er',
'Ar37', 'Ar37Er',
'Ar36', 'Ar36Er'
]
# write header row
hrow = 0
borders = Borders()
borders.bottom = 2
style = XFStyle()
style.borders = borders
for i, ci in enumerate(cols):
sh.write(hrow, i, ci, style=style)
numstyle = XFStyle()
numstyle.num_format_str = '0.00'
from operator import attrgetter
def getnominal_value(attr):
def get(x):
v = getattr(x, attr)
return float(v.nominal_value)
return get
def getstd_dev(attr):
def get(x):
v = getattr(x, attr)
return float(v.std_dev)
return get
def getfloat(attr):
def get(x):
v = getattr(x, attr)
return float(v)
return get
def null_value():
return lambda x: ''
attrs = [(attrgetter('step'),),
(getfloat('extract_value'),),
(null_value(),),
(getnominal_value('Ar40'),),
(getstd_dev('Ar40'),),
(getnominal_value('Ar39'),),
(getstd_dev('Ar39'),),
(getnominal_value('Ar38'),),
(getstd_dev('Ar38'),),
(getnominal_value('Ar37'),),
(getstd_dev('Ar37'),),
(getnominal_value('Ar36'),),
(getstd_dev('Ar36'),),
(null_value(),),
(getnominal_value('Ar40_blank'),),
(getstd_dev('Ar40_blank'),),
(getnominal_value('Ar39_blank'),),
(getstd_dev('Ar39_blank'),),
(getnominal_value('Ar38_blank'),),
(getstd_dev('Ar38_blank'),),
(getnominal_value('Ar37_blank'),),
(getstd_dev('Ar37_blank'),),
(getnominal_value('Ar36_blank'),),
(getstd_dev('Ar36_blank'),),
]
for i, ai in enumerate(analyses):
for j, arg in enumerate(attrs):
if len(arg) == 2:
func, style = arg
else:
func, style = arg[0], Style.default_style
sh.write(i + hrow + 1, 1 + j, func(ai),
style
)
for row in csv.reader(f, dialect='excel', quoting=csv.QUOTE_NONNUMERIC):
sheet.append(row)
wb.save(output_file)
print(".csv convert to .xls")
#获取xls数据
data = xlrd.open_workbook(output_file)
sheet1 = data.sheets()[0]
#新建excel并导入数据
new_excel = xlwt.Workbook(encoding='utf-8')
new_sheet = new_excel.add_sheet("test")
#设置列宽
for i in range(13):
style = XFStyle()
style.alignment
new_sheet.col(i).width = 256 * 16
#获取所需列数据
c = 0
for col in range(5, 39, 3):
cols = sheet1.col_values(col)
for i in range(sheet1.nrows): #
if 0 == i:
continue
new_sheet.write(i, c, (cols[i] / 1024 / 1024 / 1024 * 100))
c += 1
new_excel.save(output_file)
#求和，填充数据
def genTotalSheet(lis):
""" lis is UserRecord list """
global wb
wb = Workbook(encoding='gbk')
timeStyle = XFStyle()
timeStyle.num_format_str = 'h:mm'
lateStyle = XFStyle()
lateStyle.num_format_str = 'h:mm'
lateStyle.font = Font()
### when isLate = false, set colour_index = 0 will still get red font in Excel.
lateStyle.font.colour_index = 2
earlyStyle = XFStyle()
earlyStyle.num_format_str = 'h:mm'
earlyStyle.font = Font()
earlyStyle.font.colour_index = 7
sheet1 = wb.add_sheet(TOTAL_SHEET_NAME)
rowNum = 0
for i, s in enumerate(totalSheetTitles): # write header
sheet1.write(0, i, str(s))
rowNum += 1
for a in lis:
for colNum, colName in enumerate(totalSheetTitles):
if(colNum == 0):
sheet1.write_merge(rowNum, rowNum + 1, colNum, colNum, a.userId)
elif(colNum == 1):
sheet1.write_merge(rowNum, rowNum + 1, colNum, colNum, a.userName)
else:
onTime = ''
offTime = ''
if(colName in a.recDict):
times = a.recDict[colName]
if(len(times) >= 2):
if(len(times) > 2):
print '! ' + a.userName + ' ' + monthStr + str(colName) + ' 两条以上打卡记录.'
onTime = times[0]
offTime = times[len(times) - 1]
elif(len(times) == 1):
onlyTime = strToTime(times[0])
if(onlyTime.hour < divider):
onTime = times[0]
else:
offTime = times[0]
onTime = strToTime(onTime)
offTime = strToTime(offTime)
# if(onTime and offTime):
# a.totalTime += (offTime - onTime).seconds / 3600
isLate, isEarly = checkTime(onTime, offTime, colName)
if(isLate): # late
# a.late += 1
sheet1.write(rowNum, colNum, onTime, lateStyle)
else:
sheet1.write(rowNum, colNum, onTime, timeStyle)
if(isEarly): # early
# a.early += 1
sheet1.write(rowNum + 1, colNum, offTime, earlyStyle)
else:
sheet1.write(rowNum + 1, colNum, offTime, timeStyle)
rowNum += 2
getTargetFileName()
try:
wb.save(targetFileName)
addLog('"' + targetFileName + '" is in the same directory of the .txt file.')
except IOError: #IOError: [Errno 13] Permission denied: '***.xls'
addLog('please close ' + targetFileName + ' and retry')
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
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')
# f = file('/home/leon/informe.xml', 'wb')
# f.write(informe_xml)
# f.close()
root = etree.fromstring(informe_xml)
wb = Workbook()
estilos = {}
xfs0 = XFStyle()
n_sheet = 0
for sheet in root.iter('sheet'):
n_sheet += 1
# title
title = 'Sheet-%d' % n_sheet
if sheet.attrib.has_key('title') and sheet.attrib['title']:
title = sheet.attrib['title']
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
value = item.find('value')
dato = value.text
# type: aplicar una conversión (str, int, float, ...)
if item.attrib.has_key('type') and item.attrib['type']:
tipo = item.attrib['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 = 'ERROR! (%s)' % dato
# 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)
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'])
if filename:
wb.save(filename)
return informe_xml
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 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()
#实际在这里开始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')
from xlwt.Style import XFStyle
from xlwt.Workbook import Workbook
connection = connect(host='192.168.1.4',
user='******',
password='******',
database='cms',
port=3306)
cursor = connection.cursor()
cursor.execute('select * from qin_student')
result = cursor.fetchall()
wb = Workbook('utf-8')
sheet = wb.add_sheet('学生信息')
title_style = XFStyle()
content_style = XFStyle()
alignment = Alignment()
alignment.horz = alignment.HORZ_CENTER
title_style.alignment = alignment
content_style.alignment = alignment
title = ['编号', '姓名', '生日', '性别']
for i in range(len(title)):
sheet.write(0, i, title[i], title_style)
content = result
for i in range(len(content)):
for j in range(len(content[i])):
sheet.write(1 + i, 0 + j, content[i][j], content_style)