def fill_excel_template_net(template_filename, sd,ed, res, tel):
w = prepare_excel_template(template_filename, tel)
response = HttpResponse(mimetype='application/vnd.ms-excel')
filename = tel.get('FILENAME','report')
response['Content-Disposition'] = 'attachment; filename=' + filename + '.xls'
maxr = tel['max_row']
top_row = maxr
maxr += 1
wtsheet = w.get_sheet(0)
pieces = set()
pieces_indexes = dict()
di_pi = dict()
simple = easyxf('align: wrap on; font:name Arial, height 160; border: top thin, left thin, bottom thin, right thin')
bad = easyxf('align: wrap on; font:name Arial, height 160, italic 1; border: top thin, left thick, bottom thin, right thin')
wtsheet.write_merge(top_row,top_row+1, 0,0,"Номер", simple)
std = sd
td = timedelta(days=1)
i = 1
while std<=ed:
wtsheet.write_merge(top_row,top_row+1, i,i,std.day, simple)
std += td
i +=1
top_row +=2
for i in xrange(0,len(res)):
room, room_info = res[i]
h = 0
if len(room_info)>1:
h = len(room_info)-1
wtsheet.write_merge(top_row,top_row+h, 0,0,room.name, simple)
z = 0
for end_ddd,busy_array in room_info:
for name, start_date, end_date in busy_array:
td1 = start_date-sd
if td1.days<0:
td1d = 0
else:
td1d = td1.days
td2 = end_date-sd
if z>room.room_type.places:
stl = bad
else:
stl = simple
wtsheet.write_merge(top_row+z,top_row+z, td1d+1,td2.days+1,name, stl)
z +=1
top_row += h+1
left = easyxf('align: wrap on; font:name Arial, height 160; border: top thin, left thick, bottom thin, right thin')
right = easyxf('align: wrap on; font:name Arial, height 160; border: top thin, left thin, bottom thin, right thick')
bottom = easyxf('align: wrap on; font:name Arial, height 160; border: top thin, left thin, bottom thick, right thin')
w.save(response)
return response
def get_style(cls, style):
try:
return cls._styles[style]
except KeyError:
pass
if style == CELL:
_xf_style = easyxf(_CELL_FORMAT)
cls._styles[CELL] = _xf_style
elif style == HEADER:
_xf_style = easyxf(_HEADER_FORMAT)
cls._styles[HEADER] = _xf_style
else:
raise ValueError('Allowed styles are "head", "cell". Got {}.'.format(style))
return _xf_style
def export_investdata_excel(self, request):
item_list = self.filter_queryset(self.get_queryset())
data = []
for con in item_list:
project = con.project
id = con.id
project_id = project.id
project_name = project.name
if con.is_futou:
is_futou = "复投"
else:
is_futou = "首投"
invest_time = con.invest_time
invest_mobile = con.invest_mobile
invest_amount = con.invest_amount
invest_term = con.invest_term
settle_amount = con.settle_amount
return_amount = ''
result = ''
if con.state == '0':
result = '否'
#return_amount = con.return_amount
elif con.state == '1':
result = '是'
return_amount = con.return_amount
source = con.get_source_display()
remark = con.remark
data.append([
id, project_id, project_name, is_futou, invest_time,
invest_mobile, invest_amount, invest_term, settle_amount,
result, return_amount, source, remark
])
w = Workbook() # 创建一个工作簿
ws = w.add_sheet(u'待审核记录') # 创建一个工作表
title_row = [
u'记录ID', u'项目编号', u'项目名称', u'首投/复投', u'投资日期', u'提交手机号', u'投资金额',
u'投资标期', u'预估消耗', u'审核状态', u'返现金额', u'投资来源', u'备注'
]
for i in range(len(title_row)):
ws.write(0, i, title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j == 4:
ws.write(i + 1, j, lis[j], style1)
else:
ws.write(i + 1, j, lis[j])
sio = BytesIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=导出表格.xls'
response.write(sio.getvalue())
return response
def export_project_statis(request):
item_list = []
item_list = ProjectStatis.objects.all()
project_state = request.GET.get("project_state", None)
if project_state == 'start' or project_state == 'finish':
item_list = item_list.filter(project__state=project_state)
data = []
for con in item_list:
id = con.project_id
time = con.project.time.strftime("%Y-%m-%d")
finish_time = con.project.finish_time.strftime(
"%Y-%m-%d") if con.project.finish_time else ''
title = con.project.name
topay_amount = con.project.topay_amount
consume = con.consume()
ret = con.ret()
site_consume = con.site_consume
site_ret = con.site_return
channel_consume = con.channel_consume
channel_ret = con.channel_return
state = con.project.get_state_display()
data.append([
id, time, finish_time, title, topay_amount, consume, ret,
channel_consume, channel_ret, site_consume, site_ret, state
])
w = Workbook() #创建一个工作簿
ws = w.add_sheet(u'账目明细') #创建一个工作表
title_row = [
u'项目编号', u'立项日期', u'结项日期', u'项目名称', u'预计待收/待消耗', u'预计总消耗', u'总返现金额',
u'预估渠道消耗', u'渠道返现金额', u'预估网站消耗', u'网站返现金额', u'项目状态'
]
for i in range(len(title_row)):
ws.write(0, i, title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j == 1:
ws.write(i + 1, j, lis[j], style1)
elif j == 2:
if lis[j]:
ws.write(i + 1, j, lis[j], style1)
else:
ws.write(i + 1, j, lis[j])
else:
ws.write(i + 1, j, lis[j])
sio = StringIO.StringIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=导出表格.xls'
response.write(sio.getvalue())
return response
def fill_excel_template_with_many_tp(template_filename, tel, tps):
w = prepare_excel_template(template_filename, tel)
response = HttpResponse(mimetype='application/vnd.ms-excel')
filename = tel.get('FILENAME','report')
response['Content-Disposition'] = 'attachment; filename=' + filename + '.xls'
maxr = tel['max_row']
wtsheet = w.get_sheet(0)
cols = ['IDX','PUTEVKA','FIO','D','KEM','SUMM','SROK','DAYS','SUMMC','DAYSN','SUMMN']
for tp in tps:
maxr += 3
wtsheet.write_merge(maxr-2, maxr, 0, 10, tp[0], easyxf('align: wrap on;border: left no_line, right no_line'))
for record in tp[1]:
maxr += 1
i = 0
for col in cols:
wtsheet.write(maxr-1, i, record.get(col,""),easyxf('align: wrap on; border: top thin, left thin, bottom thin, right thin'))
i += 1
w.save(response)
return response
def export_audit_result(request):
user = request.user
fid = request.GET.get("fid")
if fid == '0': #jzy
finance = Finance.objects.all()
item_list = UserEvent.objects.filter(user=user, finance=finance).order_by("-time")
else:
finance = Finance.objects.get(id=fid)
item_list = UserEvent.objects.filter(user=user, finance=finance).order_by("-time")
data = []
for con in item_list:
# project_name=finance.title
project = con.content_object #jzy
project_name=project.title #jzy
mobile_sub=con.invest_account
# time_sub=con.time
time_sub=con.invest_time #jzy
id=con.id
remark= con.remark
invest_amount= con.invest_amount
term=con.invest_term
result = con.get_audit_state_display(),
return_amount = '' if con.audit_state!='0' or not con.translist.exists() else str(con.translist.first().transAmount/100.0),
reason = '' if con.audit_state!='2' or not con.audited_logs.exists() else con.audited_logs.first().reason,
data.append([id, project_name, time_sub, mobile_sub, term, invest_amount, remark, result, return_amount, reason])
w = Workbook() #创建一个工作簿
ws = w.add_sheet(u'待审核记录') #创建一个工作表
title_row = [u'记录ID',u'项目名称',u'投资日期', u'注册手机号' ,u'投资期限' ,u'投资金额', u'备注', u'审核结果',u'返现金额',u'拒绝原因']
for i in range(len(title_row)):
ws.write(0,i,title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j==2:
ws.write(i+1,j,lis[j],style1)
else:
ws.write(i+1,j,lis[j])
sio = StringIO.StringIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=审核结果.xls'
response.write(sio.getvalue())
return response
def fill_excel_template_s_gavnom(template_filename, tel, fields, records):
w = prepare_excel_template(template_filename, tel)
response = HttpResponse(mimetype='application/vnd.ms-excel')
filename = tel.get('FILENAME','report')
response['Content-Disposition'] = 'attachment; filename=' + filename + '.xls'
maxr = tel['max_row']
wtsheet = w.get_sheet(0)
for field in fields:
maxr += 2
wtsheet.write_merge(maxr-1, maxr, 0, 8, field.ill_history_field.description+": "+ field.value, easyxf('align: wrap on'))
maxr += 1
wtsheet.write_merge(
maxr, maxr,
0, 8,
'Записи истории болезни')
for record in records:
maxr += 2
wtsheet.write(maxr-1, 0, record.datetime.strftime('%d.%m.%Y'))
wtsheet.write_merge(maxr-1, maxr, 1, 8, record.text, easyxf('align: wrap on'))
w.save(response)
return response
def export_coupon_excel(request):
user = request.user
item_list = UserEvent.objects
startTime = request.GET.get("startTime", None)
endTime = request.GET.get("endTime", None)
startTime2 = request.GET.get("startTime2", None)
endTime2 = request.GET.get("endTime2", None)
state = request.GET.get("state", '1')
projecttype = request.GET.get("projecttype", '0')
if startTime and endTime:
s = datetime.datetime.strptime(startTime, '%Y-%m-%dT%H:%M')
e = datetime.datetime.strptime(endTime, '%Y-%m-%dT%H:%M')
item_list = item_list.filter(time__range=(s, e))
if startTime2 and endTime2:
s = datetime.datetime.strptime(startTime2, '%Y-%m-%dT%H:%M')
e = datetime.datetime.strptime(endTime2, '%Y-%m-%dT%H:%M')
item_list = item_list.filter(audit_time__range=(s, e))
username = request.GET.get("username", None)
if username:
item_list = item_list.filter(user__username=username)
mobile = request.GET.get("mobile", None)
if mobile:
item_list = item_list.filter(user__mobile=mobile)
mobile_sub = request.GET.get("mobile_sub", None)
if mobile_sub:
item_list = item_list.filter(invest_account=mobile_sub)
companyname = request.GET.get("companyname", None)
if companyname:
item_list = item_list.filter(
coupon__project__provider__contains=companyname)
projectname = request.GET.get("projectname", None)
if projectname:
item_list = item_list.filter(
coupon__project__title__contains=projectname)
adminname = request.GET.get("adminname", None)
if adminname:
item_list = item_list.filter(audited_logs__user__username=adminname)
if projecttype == '1':
item_list = item_list.filter(coupon__project__ctype='0')
if projecttype == '2':
item_list = item_list.filter(coupon__project__ctype='1')
item_list = item_list.filter(
event_type='4',
audit_state=state).select_related('user').order_by('time')
data = []
for con in item_list:
coupon = con.content_object
id = str(con.id)
user_type = u"普通用户" if not con.user.is_channel else u"渠道：" + con.user.channel.level
user_mobile = con.user.mobile if not con.user.is_channel else con.user.channel.qq_number
time_sub = con.time.strftime("%Y-%m-%d %H:%M")
title = coupon.project.title
zhifubao = con.user.zhifubao
mobile_sub = con.invest_account
term = con.invest_term
invest_amount = con.invest_amount
remark = con.remark
result = ''
return_amount = ''
reason = ''
if con.audit_state == '0':
result = u'是'
if con.translist.exists():
return_amount = str(con.translist.first().transAmount / 100.0)
elif con.audit_state == '2':
result = u'否'
if con.audited_logs.exists():
reason = con.audited_logs.first().reason
data.append([
id, user_type, user_mobile, time_sub, title, zhifubao, mobile_sub,
term, invest_amount, remark, result, return_amount, reason
])
w = Workbook() #创建一个工作簿
ws = w.add_sheet(u'待审核记录') #创建一个工作表
title_row = [
u'记录ID', u'用户类型', u'挖福利账号', u'提交时间', u'项目名称', u'支付宝', u'注册手机号',
u'投资期限', u'投资金额', u'备注', u'审核结果（0通过，1待审核，2拒绝）', u'返现金额', u'拒绝原因'
]
for i in range(len(title_row)):
ws.write(0, i, title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j == 3:
ws.write(i + 1, j, lis[j], style1)
else:
ws.write(i + 1, j, lis[j])
sio = StringIO.StringIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=优惠券待审核记录.xls'
response.write(sio.getvalue())
return response
def export_coupon_excel(request):
user = request.user
item_list = UserEvent.objects
startTime = request.GET.get("startTime", None)
endTime = request.GET.get("endTime", None)
startTime2 = request.GET.get("startTime2", None)
endTime2 = request.GET.get("endTime2", None)
state = request.GET.get("state",'1')
projecttype = request.GET.get("projecttype",'0')
if startTime and endTime:
s = datetime.datetime.strptime(startTime,'%Y-%m-%dT%H:%M')
e = datetime.datetime.strptime(endTime,'%Y-%m-%dT%H:%M')
item_list = item_list.filter(time__range=(s,e))
if startTime2 and endTime2:
s = datetime.datetime.strptime(startTime2,'%Y-%m-%dT%H:%M')
e = datetime.datetime.strptime(endTime2,'%Y-%m-%dT%H:%M')
item_list = item_list.filter(audit_time__range=(s,e))
username = request.GET.get("username", None)
if username:
item_list = item_list.filter(user__username=username)
mobile = request.GET.get("mobile", None)
if mobile:
item_list = item_list.filter(user__mobile=mobile)
mobile_sub = request.GET.get("mobile_sub", None)
if mobile_sub:
item_list = item_list.filter(invest_account=mobile_sub)
companyname = request.GET.get("companyname", None)
if companyname:
item_list = item_list.filter(coupon__project__provider__contains=companyname)
projectname = request.GET.get("projectname", None)
if projectname:
item_list = item_list.filter(coupon__project__title__contains=projectname)
adminname = request.GET.get("adminname", None)
if adminname:
item_list = item_list.filter(audited_logs__user__username=adminname)
if projecttype=='1':
item_list = item_list.filter(coupon__project__ctype = '0')
if projecttype=='2':
item_list = item_list.filter(coupon__project__ctype = '1')
item_list = item_list.filter(event_type='4', audit_state=state).select_related('user').order_by('time')
data = []
for con in item_list:
coupon = con.content_object
id=str(con.id)
user_type = u"普通用户" if not con.user.is_channel else u"渠道："+ con.user.channel.level
user_mobile = con.user.mobile if not con.user.is_channel else con.user.channel.qq_number
time_sub=con.time.strftime("%Y-%m-%d %H:%M")
title=coupon.project.title
zhifubao=con.user.zhifubao
mobile_sub=con.invest_account
term=con.invest_term
invest_amount= con.invest_amount
remark= con.remark
result = ''
return_amount = ''
reason = ''
if con.audit_state=='0':
result = u'是'
if con.translist.exists():
return_amount = str(con.translist.first().transAmount/100.0)
elif con.audit_state=='2':
result = u'否'
if con.audited_logs.exists():
reason = con.audited_logs.first().reason
data.append([id,user_type,user_mobile, time_sub,title, zhifubao,mobile_sub, term,invest_amount, remark, result, return_amount, reason])
w = Workbook() #创建一个工作簿
ws = w.add_sheet(u'待审核记录') #创建一个工作表
title_row = [u'记录ID',u'用户类型',u'挖福利账号',u'提交时间',u'项目名称',u'支付宝', u'注册手机号' ,u'投资期限' ,u'投资金额', u'备注', u'审核结果（0通过，1待审核，2拒绝）',u'返现金额',u'拒绝原因']
for i in range(len(title_row)):
ws.write(0,i,title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j==3:
ws.write(i+1,j,lis[j],style1)
else:
ws.write(i+1,j,lis[j])
sio = StringIO.StringIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=优惠券待审核记录.xls'
response.write(sio.getvalue())
return response
from xlwt.Style import easyxf
__author__ = 'thacdu'
FIRST_COL = 6000
SECOND_COL = 10000
h1 = easyxf(
'font:name Arial, bold on,height 1000 ;align: vert centre, horz center')
h2 = easyxf(
'font:name Arial, bold on,height 1000 ;align: vert centre, horz center')
h3 = easyxf(
'font:name Arial, bold on,height 400 ;align: vert centre, horz center')
h4 = easyxf(
'font :name Arial, bold on,height 260 ;align:wrap on, vert centre, horz center;'
'borders : top thin ,right thin, left thin, bottom thin')
h5 = easyxf(
'font:name Arial, bold on,height 240 ;align: wrap on, vert centre, horz center;'
'borders : top thin ,right thin, left thin, bottom thin;'
'pattern: pattern solid, fore_colour light_green;')
f1 = easyxf(
'font: name Arial, height 220; align: wrap on, vert centre, horz center;'
)
f2 = easyxf(
'font :name Arial ,height 220 ;align:wrap on, vert centre, horz center;'
'borders : top thin ,right thin, left thin, bottom thin')
def main():
parser = ArgumentParser()
parser.add_argument('--reportdir',
default='reports',
metavar='D',
help='directory containing report files')
parser.add_argument('--partfile',
default=None,
metavar='F',
help='csv file containing program participant report')
parser.add_argument('--merchfile',
default=None,
metavar='F',
help='csv file containing merchandise orders report')
parser.add_argument('--reffile',
default=None,
metavar='F',
help='file to use as reference for last run')
parser.add_argument('--refdt',
default=None,
metavar='T',
help='datetime to use as reference for last run')
parser.add_argument('--notouch',
action='store_true',
help='do not touch the reference file')
parser.add_argument('--basename',
default='-',
metavar='N',
help='basename of output file (- = stdout)')
parser.add_argument('--asxls',
action='store_true',
help='output excel data')
parser.add_argument('--email',
action='store_true',
help='print a list of email addresses')
parser.add_argument('--verbose',
'-v',
action='store_true',
help='print verbose messages')
args = parser.parse_args()
reportdir = args.reportdir
if not os.path.isdir(reportdir):
reportdir = os.path.join(clinicdir, args.reportdir)
if not os.path.isdir(reportdir):
raise RuntimeError('cannot locate reports directory!')
if args.verbose:
print('[reports found in directory {} (realpath={})]'.format(
reportdir, os.path.realpath(reportdir)),
file=sys.stderr)
partfile = args.partfile
if partfile is None:
partfile, _ = latest_report('program_participant',
reportdir,
verbose=args.verbose)
if partfile is None:
raise RuntimeError('cannot locate program participant file!')
if args.verbose:
print('[program participant report file = {} (realpath={})]'.format(
partfile, os.path.realpath(partfile)),
file=sys.stderr)
merchfile = args.merchfile
if merchfile is None:
merchfile, _ = latest_report(
'merchandiseorders', reportdir,
r'^merchandiseorders_(\d{8})\.csv$',
lambda m: datetime.strptime(m.group(1), '%Y%m%d'), args.verbose)
if merchfile is None:
raise RuntimeError('cannot locate merchandise order file!')
if args.verbose:
print('[merchandise orders report file = {} (realpath={})]'.format(
merchfile, os.path.realpath(merchfile)),
file=sys.stderr)
reffile = args.reffile
if reffile is None:
reffile = '.reffile'
if not os.path.exists(reffile):
reffile = os.path.join(clinicdir, reffile)
if args.verbose:
print('[reference file = {} (realpath={})]'.format(
reffile, os.path.realpath(reffile)),
file=sys.stderr)
if args.refdt is not None:
refdt = dateutil_parse(args.refdt, dayfirst=True, fuzzy=True)
else:
if os.path.exists(reffile):
refdt = datetime.fromtimestamp(os.stat(reffile).st_mtime)
else:
refdt = None
if args.verbose:
if refdt is not None:
print('[reference datetime = {}]'.format(refdt), file=sys.stderr)
else:
print('[No reference datetime available!]', file=sys.stderr)
config = load_config(prefix=clinicdir)
with open(partfile, 'r', newline='') as infile:
reader = DictReader(infile)
orecs = {}
for inrec in reader:
if inrec['role'] != 'Player' or inrec['status'] != 'Active':
if args.verbose:
print(
'ignore Non-Player or Inactive rec: {}'.format(inrec),
file=sys.stderr)
continue
school_term = inrec['season']
if school_term != config['label']:
raise RuntimeError('School Term mismatch! ({}!={})'.format(
school_term, config['label']))
name = inrec['first name'] + ' ' + inrec['last name']
date_of_birth = to_date(inrec['date of birth'], '%d/%m/%Y')
email = inrec['email']
if not email:
email = inrec['parent/guardian1 email']
phone = inrec['mobile number']
if not phone:
phone = inrec['parent/guardian1 mobile number']
parent = inrec['parent/guardian1 first name'] + ' ' + \
inrec['parent/guardian1 last name']
regodt = to_datetime(inrec['registration date'], '%d/%m/%Y')
if refdt is not None and refdt < regodt:
new = '*'
else:
new = ''
orecs[name] = dict(
new=new,
name=name,
date_of_birth=date_of_birth,
parent=parent,
email=email,
phone=make_phone(phone),
prepaid=[],
paid=' ',
)
if len(orecs) == 0:
print('No CSV records in "{}"'.format(partfile), file=sys.stderr)
sys.exit(0)
with open(merchfile, 'r', newline='') as infile:
reader = DictReader(infile)
inrecs = []
for inrec in reader:
orderdt = to_datetime(inrec['Order Date'], '%d/%m/%Y')
name = inrec['First Name'] + ' ' + inrec['Last Name']
quantity = int(inrec['Quantity'])
sku = inrec['Merchandise SKU']
inrecs.append((orderdt, name, quantity, sku))
for data in sorted(inrecs, key=lambda t: t[0]):
orderdt, name, quantity, sku = data
if name not in orecs:
print('unknown participant {}!'.format(name), file=sys.stderr)
continue
if sku == 'FULLTERM':
if quantity != 1:
raise RuntimeError(
'quantity for FULLTERM is not 1 ({:d})'.format(
quantity))
quantity = len(config['dates'])
orecs[name]['paid'] = 'Full'
elif sku != 'SINGLE':
raise RuntimeError('Unknown SKU {}!'.format(sku))
if refdt is not None and refdt < orderdt:
val = 'new'
else:
val = 'old'
for _ in range(quantity):
orecs[name]['prepaid'].append(val)
if args.email:
emails = set() # using a set() will remove duplicates
for outrec in orecs.values():
emails.add(outrec['email'].strip().lower())
for email in sorted(emails):
print(email, file=sys.stderr)
if args.asxls:
from xlwt import Workbook
from xlwt.Style import easyxf
headings = [
'#',
'Paid',
'Parent/Guardian Contact Details',
'DoB/Mobile',
'Name',
]
headings.extend(config['dates'])
styles = {
'heading':
easyxf(
'font: name Arial, height 280, bold on; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'normal':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal left; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'centred':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'right':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'currency':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='$#,##0.00',
),
'date':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD',
),
'datetime':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD HH:MM:SS AM/PM',
),
'normal_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal left; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'centred_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'right_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'currency_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='$#,##0.00',
),
'date_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD',
),
'datetime_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD HH:MM:SS AM/PM',
),
}
# Paid Parent/Guardian Contact Details Mobile Name
col1styles = {
'parent': 'normal',
'date_of_birth': 'date',
'name': 'normal',
}
col2styles = {
'email': 'normal',
'phone': 'centred',
}
book = Workbook()
sheet = book.add_sheet(config['label'])
r = 0
for c, v in enumerate(headings):
sheet.write(r, c, ensure_str(v), styles['heading'])
sheet.set_panes_frozen(True)
sheet.set_horz_split_pos(1)
sheet.set_remove_splits(True)
ndates = len(config['dates'])
pnum = 0
for outrec in sorted(orecs.values(), key=lambda d: d['name'].lower()):
pnum += 1
r += 1
if outrec['new'] == '*':
normal_style = styles['normal_highlighted']
centred_style = styles['centred_highlighted']
right_style = styles['right_highlighted']
ssuf = '_highlighted'
else:
normal_style = styles['normal']
centred_style = styles['centred']
right_style = styles['right']
ssuf = ''
sheet.write(r, 0, str(pnum), right_style)
sheet.write(r, 1, outrec['paid'], centred_style)
for c, (k, s) in enumerate(col1styles.items()):
v = outrec[k]
s = col1styles[k]
sheet.write(r, 2 + c, v, styles[s + ssuf])
i = 0
for v in outrec['prepaid']:
if v == 'old':
ppstyle = styles['centred']
else:
ppstyle = styles['centred_highlighted']
sheet.write(r, 2 + c + 1 + i, 'PP', ppstyle)
i += 1
while i < ndates - 1:
sheet.write(r, 2 + c + 2 + i, ' ', centred_style)
i += 1
r += 1
sheet.write(r, 0, ' ', normal_style)
sheet.write(r, 1, ' ', normal_style)
for c, (k, s) in enumerate(col2styles.items()):
v = outrec[k]
s = col2styles[k]
sheet.write(r, 2 + c, v, styles[s + ssuf])
c += 1
sheet.write(r, 2 + c, ' ', normal_style)
c += 1
for i in range(len(config['dates'])):
sheet.write(r, 2 + c + i, ' ', normal_style)
book.save(sys.stdout.buffer)
if not args.notouch:
Path(reffile).touch()
return 0
def export_account_bill_excel(request):
user = request.user
item_list = []
item_list = AccountBill.objects.all()
name = request.GET.get("name", None)
if name:
item_list = item_list.filter(account__name=name)
account = request.GET.get("account", None)
if account:
item_list = item_list.filter(account_id=account)
account_type = request.GET.get("account_type", None)
if account_type:
item_list = item_list.filter(account__type=account_type)
type = request.GET.get("type", None)
if type:
item_list = item_list.filter(type=type)
subtype = request.GET.get("subtype", None)
if subtype:
item_list = item_list.filter(subtype=subtype)
target = request.GET.get("target", None)
if target:
item_list = item_list.filter(target__contains=target)
timeft_0 = request.GET.get("timeft_0", None)
timeft_1 = request.GET.get("timeft_1", None)
if timeft_0 and timeft_1:
s = datetime.datetime.strptime(timeft_0, '%Y-%m-%d')
e = datetime.datetime.strptime(timeft_1, '%Y-%m-%d')
e += timedelta(days=1)
item_list = item_list.filter(time__range=(s, e))
data = []
for con in item_list:
time = con.time.strftime("%Y-%m-%d %H:%M")
account_id = con.account_id
account_type = con.account.get_type_display()
account_name = con.account.name
bill_type = con.get_type_display()
subtype = con.get_subtype_display()
target = con.target
amount = con.amount
balance = con.account.balance
remark = con.remark
data.append([
time, account_id, account_type, account_name, bill_type, subtype,
target, amount, balance, remark
])
w = Workbook() #创建一个工作簿
ws = w.add_sheet(u'账目明细') #创建一个工作表
title_row = [
u'账单时间', u'账户ID', u'账户类型', u'账户名称', u'账单类型', u'收/支类型', u'交易对象',
u'交易金额', u'账户余额', u'备注'
]
for i in range(len(title_row)):
ws.write(0, i, title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD HH:mm')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j == 0:
ws.write(i + 1, j, lis[j], style1)
else:
ws.write(i + 1, j, lis[j])
sio = StringIO.StringIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=导出表格.xls'
response.write(sio.getvalue())
return response
def export_investdata_excel(request):
user = request.user
item_list = ProjectInvestData.objects
is_futou = request.GET.get("is_futou", None)
if is_futou == "true":
item_list = item_list.filter(is_futou=True)
elif is_futou == "false":
item_list = item_list.filter(is_futou=False)
state = request.GET.get("state", None)
if state:
item_list = item_list.filter(state=state)
source = request.GET.get("source", None)
if source:
item_list = item_list.filter(source=source)
invest_mobile = request.GET.get("invest_mobile", None)
if invest_mobile:
item_list = item_list.filter(invest_mobile=invest_mobile)
name__contains = request.GET.get("name__contains", None)
if name__contains:
item_list = item_list.filter(project__name__contains=name__contains)
project = request.GET.get("project_id", None)
if project:
item_list = item_list.filter(project_id=project)
investtime_0 = request.GET.get("investtime_0", None)
investtime_1 = request.GET.get("investtime_1", None)
audittime_0 = request.GET.get("audittime_0", None)
audittime_1 = request.GET.get("audittime_1", None)
if investtime_0 and investtime_1:
s = datetime.datetime.strptime(investtime_0, '%Y-%m-%d')
e = datetime.datetime.strptime(investtime_1, '%Y-%m-%d')
item_list = item_list.filter(invest_time__range=(s, e))
if audittime_0 and audittime_1:
s = datetime.datetime.strptime(audittime_0, '%Y-%m-%d')
e = datetime.datetime.strptime(audittime_1, '%Y-%m-%d')
item_list = item_list.filter(audit_time__range=(s, e))
item_list = item_list.select_related('project').order_by('invest_time')
data = []
for con in item_list:
project = con.project
id = con.id
project_id = project.id
project_name = project.name
if con.is_futou:
is_futou = u"复投"
else:
is_futou = u"首投"
invest_time = con.invest_time
invest_mobile = con.invest_mobile
invest_amount = con.invest_amount
invest_term = con.invest_term
settle_amount = con.settle_amount
return_amount = ''
result = ''
if con.state == '0':
result = u'是'
return_amount = con.return_amount
elif con.state == '2':
result = u'否'
source = con.get_source_display()
remark = con.remark
data.append([
id, project_id, project_name, is_futou, invest_time, invest_mobile,
invest_amount, invest_term, settle_amount, result, return_amount,
source, remark
])
w = Workbook() #创建一个工作簿
ws = w.add_sheet(u'待审核记录') #创建一个工作表
title_row = [
u'记录ID', u'项目编号', u'项目名称', u'首投/复投', u'投资日期', u'提交手机号', u'投资金额',
u'投资标期', u'预估消耗', u'审核状态', u'返现金额', u'投资来源', u'备注'
]
for i in range(len(title_row)):
ws.write(0, i, title_row[i])
row = len(data)
style1 = easyxf(num_format_str='YY/MM/DD')
for i in range(row):
lis = data[i]
col = len(lis)
for j in range(col):
if j == 4:
ws.write(i + 1, j, lis[j], style1)
else:
ws.write(i + 1, j, lis[j])
sio = StringIO.StringIO()
w.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=导出表格.xls'
response.write(sio.getvalue())
return response
def main():
description = []
mainLith = True
os.chdir(r"C:\Projects\temp")
filename = r"M:\DATA FILES\PROPERTIES\ANOKI\Temp\Historic Anoki McBean Logs - Master Database - Surpac Database\57325-0.IN"
while not filename:
filename = raw_input("LogII file name (.in) : ")
#create new ms excel workbook and populate with worksheets
try:
xls = xlwt.Workbook()
sheets = [xls.add_sheet(ws, cell_overwrite_ok=True) for ws in ['Index','Coords','Survey','Assay','Lithology','Sublithology']]
# write index headers
sheets[0].write(0,0,'Project')
sheets[0].write(0,1,'HoleID')
# write collar headers
for i in enumerate(['Project','HoleID','Northing','Easting','Elevation','Length','Contractor','Core Size','Start Date','End Date','Author','Location']):
sheets[1].write(0,i[0],i[1])
# write survey headers
for i in enumerate(['Project','HoleID','Depth','Azimuth','Dip']):
sheets[2].write(0,i[0],i[1])
# write assay headers
for i in enumerate(['Project','HoleID','SampleID','From','To','Pyrite','AU','AU1','AU2','Comments']):
sheets[3].write(0,i[0],i[1])
# write lithology headers
for i in enumerate(['Project','HoleID','From','To','Code','Description']):
sheets[4].write(0,i[0],i[1])
sheets[5].write(0,i[0],i[1])
except:
print "Could not create Excel workbook"
# parse datafile and write data to worksheets
with open(filename,'rb') as logIIfile:
while True:
firstline = logIIfile.readline()
if firstline[0] == '1' or firstline[0] == '0':
processFirst(firstline)
break
for l in logIIfile.readlines():
if l[0] == "1":
processHeader(l[1:].strip())
elif l[0] == "2":
data = processSurveys(l[1:].strip())
r2 = sheets[2].last_used_row + 1
sheets[2].write(r2,0,project)
sheets[2].write(r2,1,holeID or None)
sheets[2].write(r2,2,data[0])
sheets[2].write(r2,3,data[1])
sheets[2].write(r2,4,data[2])
elif l[0] == "3":
data = processAssays(l[1:].strip())
r3 = sheets[3].last_used_row + 1
sheets[3].write(r3,0,project)
sheets[3].write(r3,1,holeID)
for var in enumerate(data):
if not var[1].isalpha() and var[1][0].isdigit():
var = (var[0],float(var[1]))
sheets[3].write(r3,var[0]+2,var[1])
elif l[0] == "4":
sheets[4].flush_row_data()
sheets[5].flush_row_data()
mainLith = True
data = processDesc(l)
row = sheets[4].last_used_row + 1
sheets[4].write(row,0,project)
sheets[4].write(row,1,holeID)
for item in enumerate(data):
sheets[4].write(row,item[0]+2,item[1])
description = []
elif l[0] == "5" and l.find("\"\"") == -1:
if [x for x in list(l[2:]) if x <> " "][0].isdigit():
mainLith = False
row = sheets[5].last_used_row + 1
data = processSubDesc(l)
description = []
sheets[5].write(row,0,project)
sheets[5].write(row,1,holeID)
for item in enumerate(data[:3]):
sheets[5].write(row,item[0]+2,item[1])
if len(data) > 3:
description.append(data[3])
else:
description.append(processDesc(l))
sheets[4 if mainLith else 5].write(row, 5, " ".join(description))
# write index and collar data to worksheets
r = sheets[0].last_used_row + 1
sheets[0].write(r,0,project)
sheets[0].write(r,1,holeID)
r1 = sheets[1].last_used_row + 1
sheets[1].write(r1,0,project)
sheets[1].write(r1,1,holeID)
sheets[1].write(r1,2,Easting)
sheets[1].write(r1,3,Northing)
sheets[1].write(r1,4,Elevation)
sheets[1].write(r1,5,Length)
sheets[1].write(r1,6,drill)
sheets[1].write(r1,7,coresize)
sheets[1].write(r1,8,drillStart,easyxf(num_format_str='YYYY-MM-DD'))
sheets[1].write(r1,9,drillEnd,easyxf(num_format_str='YYYY-MM-DD'))
sheets[1].write(r1,10,geo)
sheets[1].write(r1,11,location)
# save file and end
xls.save(holeID + ".xls")
class SignatureHandler(AbstractResultHandler):
"""Failures signatures result handler.
Matches the tests' exceptions with a given pattern,
and reports it to the user.
"""
NAME = 'signature'
LINK = 'Link'
TEST = 'Test'
PATTERN = 'Pattern'
ISSUE_NAME = 'Issue name'
HEADERS = (TEST, ISSUE_NAME, LINK, PATTERN)
BLACK_COLOR = "black"
WHITE_COLOR = "white"
DEFAULT_COLOR = WHITE_COLOR
CHAR_LENGTH = 256 # Character length is in units of 1/256
COL_WIDTH = CHAR_LENGTH * 30
ROW_HEIGHT = 20 * 13 # 13pt
FONT_COLOR = "font:colour %s;"
HEIGHT_STYLE = 'font:height %s, bold on;' % ROW_HEIGHT
COLOR_PATTERN = 'pattern: pattern solid, fore_colour %s;'
THIN_CELL_BORDERS = ('borders: left thin, right thin, '
'top thin, bottom thin;')
THICK_CELL_BORDERS = ('borders: left thick, right thick, '
'top thick, bottom thick;')
CELL_STYLE = COLOR_PATTERN + THIN_CELL_BORDERS + FONT_COLOR
BOLDED_CELL_STYLE = easyxf(THICK_CELL_BORDERS + HEIGHT_STYLE)
DEFAULT_CELL_STYLE = easyxf(CELL_STYLE % (DEFAULT_COLOR, BLACK_COLOR))
EXCEL_FILE_ENCODING = "utf-8"
EXCEL_SHEET_NAME = "MatchingSignatures"
EXCEL_WORKBOOK_NAME = "signatures.xls"
def __init__(self, main_test=None, *args, **kwargs):
"""Initialize the result handler.
Note:
Loads the signatures from the DB.
Args:
main_test (object): the main test instance.
"""
super(SignatureHandler, self).__init__(main_test, *args, **kwargs)
self.row_number = 0
self.signatures = SignatureData.objects.all()
self.output_file_path = os.path.join(self.main_test.work_dir,
self.EXCEL_WORKBOOK_NAME)
self.workbook = xlwt.Workbook(encoding=self.EXCEL_FILE_ENCODING)
self.sheet = self.workbook.add_sheet(self.EXCEL_SHEET_NAME,
cell_overwrite_ok=True)
self._prepare_excel_file()
def _write_to_cell(self, header, style, content):
"""Write content to a specific cell.
Args:
header (str): header of the cell's column.
style (xlwt.Style): representation of an Excel format.
content (str): content to be written to the cell.
"""
self.sheet.row(self.row_number).write(self.HEADERS.index(header),
content, style)
def _write_headers(self):
"""Write the column headers."""
for header in self.HEADERS:
self._write_to_cell(header, self.BOLDED_CELL_STYLE, header)
self.row_number += 1
def _align_columns(self):
"""Align the columns width."""
for header in self.HEADERS:
self.sheet.col(self.HEADERS.index(header)).width = self.COL_WIDTH
def _prepare_excel_file(self):
"""Prepare the excel file before writing any data to it."""
self._write_headers()
self._align_columns()
self.workbook.save(self.output_file_path)
def _match_signatures(self, exception_str):
"""Return the name of the matched signature.
Args:
exception_str (str): exception traceback string.
Returns:
SignatureData. the signature of the given exception.
"""
for signature in self.signatures:
signature_reg = re.compile(signature.pattern,
re.DOTALL | re.MULTILINE)
if signature_reg.match(exception_str):
return signature
def _register_result_of_known_issues(self, test, exception_str):
"""Register the name of the matched signature.
Args:
test (object): test item instance.
exception_str (str): exception traceback string.
"""
signature_match = self._match_signatures(exception_str)
if signature_match is not None:
# Inserting test name
self._write_to_cell(header=self.TEST,
content=test.data.name,
style=self.DEFAULT_CELL_STYLE)
# Inserting signature/issue name
self._write_to_cell(header=self.ISSUE_NAME,
content=signature_match.name,
style=self.DEFAULT_CELL_STYLE)
# Inserting link
self._write_to_cell(header=self.LINK,
content=signature_match.link,
style=self.DEFAULT_CELL_STYLE)
# Inserting pattern
self._write_to_cell(header=self.PATTERN,
content=signature_match.pattern,
style=self.DEFAULT_CELL_STYLE)
self.row_number += 1
self.workbook.save(self.output_file_path)
def add_error(self, test, exception_str):
"""Check if the test error matches any known issues.
Args:
test (object): test item instance.
exception_str (str): exception traceback string.
"""
self._register_result_of_known_issues(test, exception_str)
def add_failure(self, test, exception_str):
"""Check if the test failure matches any known issues.
Args:
test (object): test item instance.
exception_str (str): exception traceback string.
"""
self._register_result_of_known_issues(test, exception_str)
def main():
parser = ArgumentParser()
parser.add_argument('--csvfile',
default=None,
metavar='F',
help='csv file containing trybooking report')
parser.add_argument('--reffile',
default=None,
metavar='F',
help='file to use as reference for last run')
parser.add_argument('--refdt',
default=None,
metavar='D',
help='datetime to use as reference for last run')
parser.add_argument('--basename',
default='-',
metavar='S',
help='basename of output file (- = stdout)')
parser.add_argument('--ascsv',
action='store_true',
help='output csv data (no highlighting)')
parser.add_argument('--ashtml',
action='store_true',
help='output html data')
parser.add_argument('--asxls',
action='store_true',
help='output excel data')
parser.add_argument('--email',
action='store_true',
help='print a list of email addresses')
parser.add_argument('--verbose',
'-v',
action='store_true',
help='print verbose messages')
args = parser.parse_args()
if args.csvfile is not None:
csvfile = args.csvfile
reffile = args.reference
else:
def repkey(e):
_, m = e
dstr, suff = m.groups()
dt = datetime.strptime(dstr, '%d%m%Y')
if suff is not None:
dt += datetime.timedelta(seconds=int(suff[1:]))
return dt
rlist = sorted(
get_reports(None, clinicdir, r'^(\d{8})(-\d)?.csv$', args.verbose),
key=repkey,
)
try:
csvfile = os.path.join(clinicdir, rlist.pop()[0])
except IndexError:
raise RuntimeError('No Trybooking reports found!')
try:
reffile = os.path.join(clinicdir, rlist.pop()[0])
except IndexError:
reffile = None
if args.verbose:
print('[trybooking report file: {}]'.format(csvfile), file=sys.stderr)
if reffile is not None:
print('[reference datetime file: {}]'.format(reffile),
file=sys.stderr)
if args.refdt is not None:
refdt = dateutil_parse(args.refdt, dayfirst=True, fuzzy=True)
elif reffile is not None:
refdt = datetime.fromtimestamp(os.stat(reffile).st_mtime)
else:
refdt = None
if refdt is not None and args.verbose:
print('[reference datetime: {}]'.format(refdt), file=sys.stderr)
config = load_config(prefix=clinicdir)
with open(csvfile, 'r', newline='') as infile:
_ = infile.read(1)
reader = DictReader(infile)
orecs = []
for inrec in reader:
if to_bool(inrec['Void']):
if args.verbose:
print('ignore VOID record: {}'.format(inrec),
file=sys.stderr)
continue
school_term = inrec['Ticket Data: School Term']
if school_term != config['label']:
raise RuntimeError('School Term mismatch! ({}!={})'.format(
school_term, clinicterm))
name = inrec['Ticket Data: Player\'s First Name'] + ' ' + \
inrec['Ticket Data: Player\'s Surname']
date_of_birth = to_date(
inrec['Ticket Data: Player\'s Date-of-Birth'], '%Y-%m-%d')
paid = float(inrec['Net Booking'])
medical = inrec[
'Ticket Data: Special Requirements/Medical Conditions'].strip(
)
isparent = to_bool(
inrec['Ticket Data: Is Purchaser the child\'s Parent/Guardian']
)
if isparent:
parent_data = booking_data(inrec)
else:
parent_data = ticket_data(inrec)
if not any(parent_data):
# they answered No to isparent, but did not fill in
# parent ticket data - use the booking data instead ...
parent_data = booking_data(inrec)
print('empty ticket data - using booking data ({})'.format(
parent_data),
file=sys.stderr)
parent, address, phone, email = parent_data
# "27Apr21","1:58:48 PM"
dbdt = to_datetime(inrec['Date Booked (UTC+10)'], '%d%b%y')
tbt = to_time(inrec['Time Booked'], '%I:%M:%S %p')
booked = dbdt + timedelta(
hours=tbt.hour, minutes=tbt.minute, seconds=tbt.second)
if refdt is None or refdt < booked:
new = '*'
else:
new = ''
orecs.append(
OrderedDict(
new=new,
paid=paid,
name=name,
date_of_birth=date_of_birth,
parent=parent,
email=email,
phone=make_phone(phone),
address=address.title().replace('\n', ', '),
medical=medical,
booked=booked,
))
if args.email:
emails = set()
for outrec in orecs:
emails.add(outrec['email'].strip().lower())
for email in sorted(emails):
print(email)
if len(orecs) == 0:
print('No CSV records in "{}"'.format(csvfile))
sys.exit(0)
if args.ascsv:
from csv import DictWriter
with TextIOWrapper(sys.stdout.buffer, newline='') as outfile:
writer = DictWriter(outfile, fieldnames=orecs[0].keys())
writer.writeheader()
for outrec in orecs:
writer.writerow(outrec)
if args.ashtml:
raise NotImplementedError('html output not implemented!')
if args.asxls:
from xlwt import Workbook
from xlwt.Style import easyxf
styles = {
'heading':
easyxf(
'font: name Arial, height 280, bold on; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'normal':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal left; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'centred':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'currency':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='$#,##0.00',
),
'date':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD',
),
'datetime':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD HH:MM:SS AM/PM',
),
'normal_highlighted':
easyxf(
'font: name Arial, height 280; '
'pattern: pattern solid, back_colour light_yellow; '
'align: wrap off, vertical centre, horizontal left; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'centred_highlighted':
easyxf(
'font: name Arial, height 280; '
'pattern: pattern solid, back_colour light_yellow; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'currency_highlighted':
easyxf(
'font: name Arial, height 280; '
'pattern: pattern solid, back_colour light_yellow; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='$#,##0.00',
),
'date_highlighted':
easyxf(
'font: name Arial, height 280; '
'pattern: pattern solid, back_colour light_yellow; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD',
),
'datetime_highlighted':
easyxf(
'font: name Arial, height 280; '
'pattern: pattern solid, back_colour light_yellow; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD HH:MM:SS AM/PM',
),
}
colstyles = {
'new': 'centred',
'paid': 'currency',
'name': 'normal',
'date_of_birth': 'date',
'parent': 'normal',
'email': 'normal',
'phone': 'centred',
'address': 'normal',
'medical': 'normal',
'booked': 'datetime',
}
book = Workbook()
sheet = book.add_sheet(config['label'])
r = 0
for c, v in enumerate(orecs[0].keys()):
sheet.write(r, c, ensure_str(v), styles['heading'])
sheet.set_panes_frozen(True)
sheet.set_horz_split_pos(1)
sheet.set_remove_splits(True)
for outrec in orecs:
r += 1
is_new = outrec['new'] == '*'
for c, (k, v) in enumerate(outrec.items()):
if k == 'address':
v = v.replace('\n', ', ')
s = colstyles[k]
if is_new:
s += '_highlighted'
sheet.write(r, c, v, styles[s])
book.save(sys.stdout.buffer)
return 0
def fill_excel_template_porcii(template_filename, tel, res):
w = prepare_excel_template(template_filename, tel)
response = HttpResponse(mimetype='application/vnd.ms-excel')
filename = tel.get('FILENAME','report')
response['Content-Disposition'] = 'attachment; filename=' + filename + '.xls'
maxr = tel['max_row']
top_row = maxr
maxr += 1
wtsheet = w.get_sheet(0)
pieces = set()
pieces_indexes = dict()
di_pi = dict()
wtsheet.write_merge(top_row,top_row+1, 0,0,"Продукты\nБлюда", easyxf('align: wrap on, horiz center, vert center; border: top thick, left thick, bottom thin, right thin'))
wtsheet.write_merge(top_row,top_row+1, 1,1,"Кол-во", easyxf('align: wrap on, horiz center, vert center, rota 90; border: top thick, left thin, bottom thin, right thin'))
wtsheet.write_merge(top_row,top_row+1, 2,2,"Выход", easyxf('align: wrap on, horiz center, vert center, rota 90; border: top thick, left thin, bottom thin, right thin'))
wtsheet.write_merge(top_row,top_row, 3,3,"", easyxf('align: wrap on, horiz center, vert center; border: top thick, left thin, bottom thin, right thin'))
wtsheet.write_merge(top_row+1,top_row+1, 3,3,"", easyxf('align: wrap on, horiz center, vert center; border: top thin, left thin, bottom thin, right thin'))
for id,qty,di in res:
for itempiece in di.item.itempiece_set.all():
if not itempiece.piece in pieces:
pieces.add(itempiece.piece)
pieces_indexes[itempiece.piece]=(len(pieces),0)
wtsheet.write_merge(top_row,top_row, 2+2*len(pieces)-1,2+2*len(pieces),itempiece.piece.name, easyxf('align: wrap on, horiz center, vert center; border: top thick, left thick, bottom thin, right thick'))
wtsheet.write_merge(top_row+1,top_row+1,2+2*len(pieces)-1,2+2*len(pieces)-1, 'норма', easyxf('align: wrap on; alignment:rota 90; border: top thin, left thick, bottom thin, right thin'))
wtsheet.write_merge(top_row+1,top_row+1,2+2*len(pieces),2+2*len(pieces), 'кол-во', easyxf('align: wrap on; alignment:rota 90; border: top thin, left thin, bottom thin, right thick'))
col = wtsheet.col(1+2*len(pieces))
col.width = 850
col = wtsheet.col(2+2*len(pieces))
col.width = 850
(idx,total) = pieces_indexes[itempiece.piece]
l = di_pi.get(di,dict())
l[idx] = itempiece.weight
di_pi[di] = l
pieces_indexes[itempiece.piece] = (idx,total+qty*itempiece.weight)
simple = easyxf('align: wrap on; font:name Arial, height 160; border: top thin, left thin, bottom thin, right thin')
left = easyxf('align: wrap on; font:name Arial, height 160; border: top thin, left thick, bottom thin, right thin')
right = easyxf('align: wrap on; font:name Arial, height 160; border: top thin, left thin, bottom thin, right thick')
bottom = easyxf('align: wrap on; font:name Arial, height 160; border: top thin, left thin, bottom thick, right thin')
for id,qty,di in res:
maxr += 1
wtsheet.write_merge(maxr, maxr, 0, 0, di.item.name, easyxf('align: wrap on; border: top thin, left thick, bottom thin, right thin'))
wtsheet.write_merge(maxr, maxr, 1, 1, qty, easyxf('align: wrap on; border: top thin, left thin, bottom thin, right thin'))
wtsheet.write_merge(maxr, maxr, 2, 2, di.item.weight, easyxf('align: wrap on; border: top thin, left thin, bottom thin, right thin'))
wtsheet.write_merge(maxr, maxr, 3, 3, 0, easyxf('align: wrap on; border: top thin, left thin, bottom thin, right thin'))
row = wtsheet.row(maxr)
row.height=500
row.height_mismatch = True
d = di_pi.get(di,dict())
cnt = len(pieces)+1
for i in xrange(1,cnt):
weight = d.get(i,0)
if weight>0:
wtsheet.write_merge(maxr, maxr, 1+2*i, 1+2*i, weight, left)
wtsheet.write_merge(maxr, maxr, 2+2*i, 2+2*i, qty, right)
else:
wtsheet.write_merge(maxr, maxr, 1+2*i, 1+2*i, "", left)
wtsheet.write_merge(maxr, maxr, 2+2*i, 2+2*i, "", right)
# for id,qty,di in res:
# maxr += 1
# wtsheet.write_merge(maxr, maxr, 0, 0, di.item.name, easyxf('align: wrap on'))
# wtsheet.write_merge(maxr, maxr, 1, 1, qty, easyxf('align: wrap on'))
# wtsheet.write_merge(maxr, maxr, 2, 2, di.item.weight, easyxf('align: wrap on'))
# for itempiece in di.item.itempiece_set.all():
# if not itempiece.piece in pieces:
# pieces.add(itempiece.piece)
# pieces_indexes[itempiece.piece]=(len(pieces),0)
# wtsheet.write_merge(top_row,top_row, 3+2*len(pieces)-1,3+2*len(pieces),itempiece.piece.name, easyxf('align: wrap on, horiz center, vert center'))
# wtsheet.write_merge(top_row+1,top_row+1,3+2*len(pieces)-1,3+2*len(pieces)-1, 'норма', easyxf('align: wrap on; alignment:rota 90'))
# wtsheet.write_merge(top_row+1,top_row+1,3+2*len(pieces),3+2*len(pieces), 'кол-во', easyxf('align: wrap on; alignment:rota 90'))
# col = wtsheet.col(2+2*len(pieces))
# col.width = 900
# col = wtsheet.col(3+2*len(pieces))
# col.width = 900
# (idx,total) = pieces_indexes[itempiece.piece]
# pieces_indexes[itempiece.piece] = (idx,total+qty*itempiece.weight)
# wtsheet.write_merge(maxr, maxr, 2+2*idx, 2+2*idx, itempiece.weight, easyxf('align: wrap on; font:name Arial, height 160'))
# wtsheet.write_merge(maxr, maxr, 3+2*idx, 3+2*idx, qty, easyxf('align: wrap on; font:name Arial, height 160'))
# row = wtsheet.row(maxr)
# row.height=500
# row.height_mismatch = True
maxr += 1
wtsheet.write_merge(maxr, maxr, 0,0, 'Итого продуктов', easyxf('align: wrap on; font:name Arial, height 240; border: top thin, left thick, bottom thick, right thin'))
wtsheet.write_merge(maxr, maxr, 1,1, '', bottom)
wtsheet.write_merge(maxr, maxr, 2,2, '', bottom)
wtsheet.write_merge(maxr, maxr, 3,3, '', bottom)
for key,value in pieces_indexes.items():
wtsheet.write_merge(maxr, maxr, 1+2*value[0], 2+2*value[0], value[1], easyxf('align: wrap on; font:name Arial, height 220; border: top thin, left thick, bottom thick, right thick'))
row = wtsheet.row(top_row)
row.height=1500
row.height_mismatch = True
row = wtsheet.row(top_row+1)
row.height=1000
row.height_mismatch = True
row = wtsheet.row(maxr)
row.height=400
row.height_mismatch = True
w.save(response)
return response
def v_feePending(req):
'''
this function helps to see and generate excel file of fee defaulters student in tabular format
'''
if i_hasPermission(tbl_systemUser.objects.get(username=req.session.get('username')),'fees','v'):
errors=[]
months=['Jan','Feb','Mar','Apr','May','June','July','Aug','Sept','Oct','Nov','Dec']
schoolobj=tbl_school.objects.filter()
if not schoolobj:
errors.append("Please specify the school details in setting of configuration!")
return render_to_response('latefeePayment.html',locals(),context_instance=RequestContext(req))
mondict={1:'Jan',2:'Feb',3:'Mar',4:'Apr',5:'May',6:'June',7:'July',8:'Aug',9:'Sept',10:'Oct',11:'Nov',12:'Dec'}
allMSS=tbl_MSS.objects.filter(isActive=True)
if req.POST.get('exportLateFee',''):# this is used to generate report
year=req.POST.get('year','')
month=int(req.POST.get('month',''))
listing=[]
if not year:
errors.append("Please enter the valid year!")
return render_to_response('latefeePayment.html',locals(),context_instance=RequestContext(req))
listing=getFeeDefaulterList(month, year)
if not listing:
return HttpResponse('<script>alert("No fee Defaulter at present");location.href="/pendingfee/"</script>')
else:
#creating excel file
w = Workbook()
ws = w.add_sheet('AttendenceReport')
ws.col(1).width=30*256
styletoprow=easyxf('align: vertical center, horizontal center;'
'font: name Arial;'
'border:bottom thin,right thin,top thin;'
)
styletoprow1=easyxf('align: vertical center, horizontal center;'
'font: name Arial,bold true;'
'border:bottom thin,right thin,top thin;'
)
ws.write_merge(0,0,0,4,"Fee Defaulters Report for "+mondict[month]+" "+str(year),styletoprow1)
ws.write(1,0,'S.No',styletoprow1)
ws.write(1,1,'Student Name',styletoprow1)
ws.write(1,2,'Medium',styletoprow1)
ws.write(1,3,'Standard',styletoprow1)
ws.write(1,4,'Section',styletoprow1)
row=2
for data in listing:
ws.write(row,0,row-1,styletoprow)
ws.write(row,1,data.getName(),styletoprow)
ws.write(row,2,data.standard.medium.name,styletoprow)
ws.write(row,3,data.standard.name,styletoprow)
ws.write(row,4,data.section.name,styletoprow)
row=row+1
w.save(MEDIA_ROOT+'feedefaulterreport.xls')
myfile=open(MEDIA_ROOT+'feedefaulterreport.xls',"r")
response = HttpResponse(FileWrapper(myfile), content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename=feedefaulterReport.xls'
return response
if req.POST.get('viewLateFee',''):
'''
this is to view the fee defaulter listing
'''
listing=[]
month=int(req.POST.get('month',''))
getst=int(req.POST.get('getmss',''))
showtables=True
if month!=-1:
if getst!=-1:
mssobj=tbl_MSS.objects.get(id=getst)
getstudent=tbl_student.objects.filter(standard=mssobj.standard,section=mssobj.section)
for data in getstudent:
rows=tbl_feePayment.objects.filter(month=month,studid=data,session1=schoolobj[0].getSession())
if not rows:
listing.append(data)
listing=set(listing)
return render_to_response('latefeePayment.html',locals(),context_instance=RequestContext(req))
else:
getclass=tbl_MSS.objects.filter(isActive=True)
for gc in getclass:
getstudent=tbl_student.objects.filter(standard=gc.standard,section=gc.section)
for data in getstudent:
rows=tbl_feePayment.objects.filter(month=month,studid=data,session1=schoolobj[0].getSession())
if not rows:
listing.append(data)
listing=set(listing)
return render_to_response('latefeePayment.html',locals(),context_instance=RequestContext(req))
else:
if getst==-1:
getclass=tbl_MSS.objects.filter(isActive=True)
for gc in getclass:
getstudent=tbl_student.objects.filter(standard=gc.standard,section=gc.section)
i=1
for mon in months:
for data in getstudent:
rows=tbl_feePayment.objects.filter(month=i,studid=data,session1=schoolobj[0].getSession())
if not rows:
listing.append(data)
i=i+1
listing=set(listing)
return render_to_response('latefeePayment.html',locals(),context_instance=RequestContext(req))
else:
gc=tbl_MSS.objects.get(id=getst)
getstudent=tbl_student.objects.filter(standard=gc.standard,section=gc.section)
i=1
for mon in months:
for data in getstudent:
rows=tbl_feePayment.objects.filter(month=i,studid=data,session1=schoolobj[0].getSession())
if not rows:
listing.append(data)
i=i+1
listing=set(listing)
return render_to_response('latefeePayment.html',locals(),context_instance=RequestContext(req))
'''listing=[]
if not year:
errors.append("Please enter the valid year!")
return render_to_response('latefeePayment.html',locals(),context_instance=RequestContext(req))
showtables=True
listing=getFeeDefaulterList(month,year)'''
return render_to_response('latefeePayment.html',locals(),context_instance=RequestContext(req))
return render_to_response('latefeePayment.html',locals(),context_instance=RequestContext(req))
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 v_excel_for_competitors(overall,month,curyear):
if len(overall)<1:
return HttpResponse("<script>alert('no records found !');location.href='/competitors/'</script>")
w = Workbook()
ws = w.add_sheet('Source_Subscription_Report')
ws.col(1).width=25*256
ws.col(2).width=25*256
ws.col(9).width=35*256
styletoprow1=easyxf('align: vertical center, horizontal center;'
'font: name Arial,bold true;'
'border:bottom thin,right thin,top thin;'
)
styletoprow=easyxf('align: vertical top, horizontal left;'
'font: name Arial;'
'border:bottom thin,right thin,top thin;'
)
dadic={1:'Jan',2:'Feb',3:'Mar',4:'Apr',5:'May',6:'June',7:'July',8:'Aug',9:'Sept',10:'Oct',11:'Nov',12:'Dec'}
ws.write_merge(0,0,0,9,"Competition Report"+str(dadic[month])+str(curyear),styletoprow1)
ws.write(1,0,'SrNo',styletoprow1)
ws.write(1,1,'Company',styletoprow1)
ws.write(1,2,'Magazine',styletoprow1)
ws.write(1,3,'CoverPrice',styletoprow1)
ws.write(1,4,'IssueType',styletoprow1)
ws.write(1,5,'Duration',styletoprow1)
ws.write(1,6,'Mrp',styletoprow1)
ws.write(1,7,'Issues',styletoprow1)
ws.write(1,8,'SubsPrice',styletoprow1)
ws.write(1,9,'Offers',styletoprow1)
lowerlimit=2
x=1
row=2
for item in overall:
y=0
upperlimit=lowerlimit+item[0]-1
ws.write_merge(lowerlimit,upperlimit,0,0,x,styletoprow)
ws.write_merge(lowerlimit,upperlimit,1,1,item[1],styletoprow)
ws.write_merge(lowerlimit,upperlimit,2,2,item[2],styletoprow)
ws.write_merge(lowerlimit,upperlimit,3,3,item[3],styletoprow)
ws.write_merge(lowerlimit,upperlimit,4,4,item[4],styletoprow)
for data in item[5]:
ws.write(row,5,item[5][y].duration,styletoprow)
ws.write(row,6,item[5][y].mrp,styletoprow)
ws.write(row,7,item[5][y].noofIssues,styletoprow)
ws.write(row,8,item[5][y].subsPrice,styletoprow)
ws.write(row,9,item[5][y].offers,styletoprow)
row=row+1
y=y+1
lowerlimit=upperlimit+1
x=x+1
w.save(MEDIA_ROOT+'Competitors_Report.xls')
myfile=open(MEDIA_ROOT+'Competitors_Report.xls',"r")
response = HttpResponse(FileWrapper(myfile), content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename=Competitors_Report.xls'
return response
''''w.save(MEDIA_ROOT+'competitor.xls')
class ExcelHandler(AbstractResultHandler):
"""Excel result handler.
Overrides result handler's methods to generate an Excel result file.
Attributes:
row_number (num): current Excel sheet row number to write to.
test_to_row (dict): match between test name to the row number to
which it was written.
verbosity (num): whether to include traceback in the Excel report.
output_file_path (str): the Excel report path.
workbook (xlwt.Workbook): Excel workbook object.
sheet (xlwt.Sheet): Excel sheet object.
"""
NAME = 'excel'
SPACES = " "
EXCEL_FILE_ENCODING = "utf-8"
EXCEL_SHEET_NAME = "TestResults"
EXCEL_WORKBOOK_NAME = "results.xls"
RESULT_CHOICES = CaseData.RESULT_CHOICES
BLOCK_PREFIX = "Block "
IN_PROGRESS = "In Progress"
DID_NOT_RUN = "Did Not Run"
PASSED = "Previously Passed"
ERROR = RESULT_CHOICES[TestOutcome.ERROR]
FAILED = RESULT_CHOICES[TestOutcome.FAILED]
SUCCESS = RESULT_CHOICES[TestOutcome.SUCCESS]
SKIPPED = RESULT_CHOICES[TestOutcome.SKIPPED]
EXPECTED_FAILURE = RESULT_CHOICES[TestOutcome.EXPECTED_FAILURE]
UNEXPECTED_SUCCESS = RESULT_CHOICES[TestOutcome.UNEXPECTED_SUCCESS]
BLACK_COLOR = "black"
WHITE_COLOR = "white"
FAILED_COLOR = "red"
PASSED_COLOR = "lime"
ERROR_COLOR = "dark_red"
SKIPPED_COLOR = "yellow"
IN_PROGRESS_COLOR = "brown"
DEFAULT_COLOR = WHITE_COLOR
DID_NOT_RUN_COLOR = "grey25"
SUCCESS_COLOR = "bright_green"
EXPECTED_FAILURE_COLOR = "turquoise"
UNEXPECTED_SUCCESS_COLOR = "turquoise"
RESULT = 'Result'
NAME_HEADER = 'Name'
ASSIGNEE = 'Assignee'
COMMENTS = 'Comments'
END_TIME = 'End time'
RESOURCES = 'Resources'
TRACEBACK = 'Traceback'
START_TIME = 'Start time'
DESCRIPTION = 'Description'
HEADERS = (NAME_HEADER, RESULT, START_TIME, END_TIME, TRACEBACK,
DESCRIPTION, RESOURCES, ASSIGNEE, COMMENTS)
CHAR_LENGTH = 256 # Character length is in units of 1/256
MAX_TRACEBACK_LENGTH = 32767 # Max length of Excel cell content
HEADER_TO_WIDTH = {
RESULT: CHAR_LENGTH * 22,
ASSIGNEE: CHAR_LENGTH * 13,
COMMENTS: CHAR_LENGTH * 82,
END_TIME: CHAR_LENGTH * 26,
RESOURCES: CHAR_LENGTH * 48,
TRACEBACK: CHAR_LENGTH * 82,
START_TIME: CHAR_LENGTH * 26,
DESCRIPTION: CHAR_LENGTH * 82,
NAME_HEADER: CHAR_LENGTH * 75
}
ROW_HEIGHT = 20 * 13 # 13pt
FONT_COLOR = "font:colour %s;"
HEIGHT_STYLE = 'font:height %s, bold on;' % ROW_HEIGHT
COLOR_PATTERN = 'pattern: pattern solid, fore_colour %s;'
THIN_CELL_BORDERS = ('borders: left thin, right thin, '
'top thin, bottom thin;')
THICK_CELL_BORDERS = ('borders: left thick, right thick, '
'top thick, bottom thick;')
CELL_STYLE = COLOR_PATTERN + THIN_CELL_BORDERS + FONT_COLOR
DEFAULT_CELL_STYLE = easyxf(CELL_STYLE % (DEFAULT_COLOR, BLACK_COLOR))
BOLDED_CELL_STYLE = easyxf(THICK_CELL_BORDERS + HEIGHT_STYLE)
CONTENT_TO_STYLE = OrderedDict([
(IN_PROGRESS, easyxf(CELL_STYLE % (IN_PROGRESS_COLOR, WHITE_COLOR))),
(DID_NOT_RUN, easyxf(CELL_STYLE % (DID_NOT_RUN_COLOR, BLACK_COLOR))),
(SUCCESS, easyxf(CELL_STYLE % (SUCCESS_COLOR, BLACK_COLOR))),
(PASSED, easyxf(CELL_STYLE % (PASSED_COLOR, BLACK_COLOR))),
(FAILED, easyxf(CELL_STYLE % (FAILED_COLOR, WHITE_COLOR))),
(ERROR, easyxf(CELL_STYLE % (ERROR_COLOR, WHITE_COLOR))),
(SKIPPED, easyxf(CELL_STYLE % (SKIPPED_COLOR, BLACK_COLOR))),
(EXPECTED_FAILURE,
easyxf(CELL_STYLE % (EXPECTED_FAILURE_COLOR, BLACK_COLOR))),
(UNEXPECTED_SUCCESS,
easyxf(CELL_STYLE % (UNEXPECTED_SUCCESS_COLOR, BLACK_COLOR)))
])
TAGS_SKIP_MESSAGE = TagsHandler.SKIP_MESSAGE
LOCAL_DB_SKIP_MESSAGE = DBHandler.SKIP_DELTA_MESSAGE
REMOTE_DB_SKIP_MESSAGE = RemoteDBHandler.SKIP_DELTA_MESSAGE
PASSED_MESSAGES = (LOCAL_DB_SKIP_MESSAGE, REMOTE_DB_SKIP_MESSAGE)
SKIPPED_SUMMARY_PATTERN = " Skipped: %s"
ROWS_TO_SKIP = 3
SUMMARY_RESULT_TYPE_COLUMN = HEADERS[0]
SUMMARY_RESULT_COUNTER_COLUMN = HEADERS[1]
RESULT_COLUMN = "B"
TRACEBACK_COLUMN = "E"
FORMULA_PATTERN = 'COUNTIF(%s1:%s%d,"%s")'
MAX_SUMMERIZE_SIZE = len(CONTENT_TO_STYLE) + ROWS_TO_SKIP + 1
def __init__(self, main_test, output_file_path=None, *args, **kwargs):
"""Initialize Excel workbook and Sheet.
Args:
main_test (object): the main test instance (e.g. TestSuite instance
or TestFlow instance).
output_file_path (str): path to create the excel file in. Leave
None to create at the test's working dir with the default name.
"""
super(ExcelHandler, self).__init__(main_test)
self.row_number = 0
self.test_to_row = {}
self.output_file_path = output_file_path
if self.output_file_path is None:
self.output_file_path = os.path.join(self.main_test.work_dir,
self.EXCEL_WORKBOOK_NAME)
self.workbook = xlwt.Workbook(encoding=self.EXCEL_FILE_ENCODING)
self.sheet = self.workbook.add_sheet(self.EXCEL_SHEET_NAME,
cell_overwrite_ok=True)
def start_test(self, test):
"""Update the Excel that a test case starts.
Args:
test (object): test item instance.
"""
self._write_test_result(test)
self.workbook.save(self.output_file_path)
def stop_test(self, test):
"""Called when the given test has been run.
Args:
test (object): test item instance.
"""
self._write_test_result(test)
self.workbook.save(self.output_file_path)
def start_test_run(self):
"""Generate initial Excel report according to the root test.
Creates a primary report containing all sub test of the root test.
All tests will start with "Did Not Run" status.
"""
self._write_headers()
self._generate_initial_excel(self.main_test)
self._create_result_summary()
self._align_columns()
self.workbook.save(self.output_file_path)
self.row_number += 1
def update_resources(self, test):
"""Write the test's resources to the Excel file.
Args:
test (object): test item instance.
"""
# write resources
if test.locked_resources is not None:
resources = '\n'.join(
"%s:%s" % (request_name, resource.name)
for (request_name,
resource) in test.locked_resources.iteritems())
else:
resources = ''
self._write_to_cell(self.test_to_row[test.identifier], self.RESOURCES,
self.DEFAULT_CELL_STYLE, resources)
def add_success(self, test):
"""Update the test Excel entry's result to success.
Args:
test (object): test item instance.
"""
self._write_test_result(test)
self.workbook.save(self.output_file_path)
def add_skip(self, test, reason):
"""Update the test Excel entry's result to skip.
Args:
test (object): test item instance.
reason (str): skip reason description.
"""
self._write_test_result(test)
self.workbook.save(self.output_file_path)
def add_failure(self, test, exception_str):
"""Update the test Excel entry's result to failure.
Args:
test (object): test item instance.
exception_str (str): exception traceback string.
"""
self._write_test_result(test)
self.workbook.save(self.output_file_path)
def add_error(self, test, exception_str):
"""Update the test Excel entry's result to error.
Args:
test (object): test item instance.
exception_str (str): exception traceback string.
"""
self._write_test_result(test)
self.workbook.save(self.output_file_path)
def add_expected_failure(self, test, exception_str):
"""Update the test Excel entry's result to expected failure.
Args:
test (object): test item instance.
exception_str (str): exception traceback string.
"""
self._write_test_result(test)
self.workbook.save(self.output_file_path)
def add_unexpected_success(self, test):
"""Update the test Excel entry's result to unexpected success.
Args:
test (object): test item instance.
"""
self._write_test_result(test)
self.workbook.save(self.output_file_path)
def _generate_initial_excel(self, test):
"""Create an initial Excel test result.
Generate test entries by the root test's
Args:
test (object): test item instance.
"""
self.row_number += 1
self._write_to_cell(
self.row_number, self.NAME_HEADER, self.DEFAULT_CELL_STYLE,
(test.parents_count * self.SPACES) + test.data.name)
self._write_to_cell(self.row_number, self.DESCRIPTION,
self.DEFAULT_CELL_STYLE, test.__doc__)
status = self.DID_NOT_RUN
status_desc = status
if isinstance(test, AbstractFlowComponent) and not test.is_main:
status_desc = self.BLOCK_PREFIX + status
if not isinstance(test, TestSuite):
self._write_to_cell(self.row_number, self.RESULT,
self.CONTENT_TO_STYLE[status], status_desc)
self.test_to_row[test.identifier] = self.row_number
if test.IS_COMPLEX:
for sub_test in test:
self._generate_initial_excel(sub_test)
def _write_test_result(self, test):
"""Write a single test entry to the Excel file.
Args:
test (object): test item instance.
"""
# write result status
row_num = self.test_to_row[test.identifier]
if test.data.exception_type is None:
status = self.IN_PROGRESS
else:
status = self.RESULT_CHOICES[test.data.exception_type]
status_desc = status
tb_str = test.data.traceback
if status == self.SKIPPED and tb_str in self.PASSED_MESSAGES:
status = status_desc = self.PASSED
if isinstance(test, AbstractFlowComponent) and not test.is_main:
status_desc = self.BLOCK_PREFIX + status
if test.data.start_time is not None:
self._write_to_cell(row_num, self.START_TIME,
self.DEFAULT_CELL_STYLE,
str(test.data.start_time))
if test.data.end_time is not None:
self._write_to_cell(row_num,
self.END_TIME, self.DEFAULT_CELL_STYLE,
str(test.data.end_time))
self._write_to_cell(row_num, self.RESULT,
self.CONTENT_TO_STYLE[status], status_desc)
if tb_str is not None and len(tb_str) > self.MAX_TRACEBACK_LENGTH:
tb_str = tb_str[-1 * self.MAX_TRACEBACK_LENGTH:]
self._write_to_cell(row_num, self.TRACEBACK, self.DEFAULT_CELL_STYLE,
tb_str)
# set row's height
self.sheet.row(row_num).height_mismatch = True
self.sheet.row(row_num).height = self.ROW_HEIGHT
def _write_to_cell(self, row_number, header, style, content):
"""Write content to a specific cell.
Args:
row_number (number): cell's row number.
header (str): header of the cell's column.
style (xlwt.Style): representation of an Excel format.
content (str): content to be written to the cell.
"""
self.sheet.row(row_number).write(self.HEADERS.index(header), content,
style)
def _write_headers(self):
"""Write the column headers."""
for header in self.HEADERS:
self._write_to_cell(self.row_number, header,
self.BOLDED_CELL_STYLE, header)
def _align_columns(self):
"""Align the columns width."""
for header, col_width in self.HEADER_TO_WIDTH.items():
self.sheet.col(self.HEADERS.index(header)).width = col_width
def _create_skipped_reason_summary(self):
"""Create Skipped tests summary.
Describe amount of tests skipped since they were filtered by tags,
or noted as 'Already Passed' in the DB.
"""
for skip_reason in (self.TAGS_SKIP_MESSAGE, ):
self._write_to_cell(self.row_number,
self.SUMMARY_RESULT_TYPE_COLUMN,
self.DEFAULT_CELL_STYLE,
self.SKIPPED_SUMMARY_PATTERN % skip_reason)
skip_count_formula = xlwt.Formula(
self.FORMULA_PATTERN %
(self.TRACEBACK_COLUMN, self.TRACEBACK_COLUMN, self.row_number,
skip_reason))
self._write_to_cell(self.row_number,
self.SUMMARY_RESULT_COUNTER_COLUMN,
self.DEFAULT_CELL_STYLE, skip_count_formula)
self.row_number += 1
def _create_result_summary(self):
"""Create result summary at the end of the Excel report."""
self.row_number += self.ROWS_TO_SKIP
for result_type in self.CONTENT_TO_STYLE:
self._write_to_cell(self.row_number,
self.SUMMARY_RESULT_TYPE_COLUMN,
self.CONTENT_TO_STYLE[result_type],
result_type)
result_type_count = xlwt.Formula(
self.FORMULA_PATTERN % (self.RESULT_COLUMN, self.RESULT_COLUMN,
self.row_number, result_type))
self._write_to_cell(self.row_number,
self.SUMMARY_RESULT_COUNTER_COLUMN,
self.DEFAULT_CELL_STYLE, result_type_count)
self.row_number += 1
if result_type == self.SKIPPED:
self._create_skipped_reason_summary()
def v_generateAttendenceReport(req):
'''
this method is used to show employee/staff attendence in excel form (based on year,month specified)
'''
months=['Jan','Feb','Mar','Apr','May','June','July','Aug','Sept','Oct','Nov','Dec']
mondict={1:'Jan',2:'Feb',3:'Mar',4:'Apr',5:'May',6:'June',7:'July',8:'Aug',9:'Sept',10:'Oct',11:'Nov',12:'Dec'}
month=datetime.datetime.today().month
year=datetime.datetime.today().year
errors=[]
if req.POST.get('attendenceExcel',''):
month=int(req.POST.get('month',''))
year=req.POST.get('year','').strip()
if not year:
errors.append("Please enter the year!")
return render_to_response('attendenceReport.html',locals(),context_instance=RequestContext(req))
'''
arranging data,to genarate result in desired tabular format
'''
allrows=tbl_attendence.objects.filter(date__month=month,date__year=int(year)).order_by('date')
emps=tbl_employee.objects.filter()
temp=[]
overall=[]
temp1=[]
if not allrows:
return HttpResponse('<script>alert("No data exist");location.href="/report/attendence/"</script>')
for e in emps:
temp.append(e.perDetail.fName)
for data in allrows:
obj=data.marking.filter(employee=e)
if obj:
for v in obj:
if v.employee==e:
temp1.append(v.gettime())
else:
temp1.append("---")
temp.append(temp1)
overall.append(temp)
temp=[]
temp1=[]
'''
creating excel sheet
'''
w = Workbook()
ws = w.add_sheet('AttendenceReport')
ws.col(0).width=30*256
styletoprow=easyxf('align: vertical center, horizontal center;'
'font: name Arial;'
'border:bottom thin,right thin,top thin;'
)
styletoprow1=easyxf('align: vertical center, horizontal center;'
'font: name Arial,bold true;'
'border:bottom thin,right thin,top thin;'
)
ws.write(0,0,'Attendence Report '+mondict[month]+' '+str(year),styletoprow1)
ws.write(1,0,'Employee',styletoprow1)
x=1
for data in allrows:
ws.write(1,x,data.getday(),styletoprow1)
x=x+1
y=2
for v in overall:
z=1
ws.write(y,0,v[0],styletoprow1)
for ddd in v[1]:
ws.write(y,z,ddd,styletoprow)
z=z+1
y=y+1
w.save(MEDIA_ROOT+'attendence report.xls')
myfile=open(MEDIA_ROOT+'attendence report.xls',"r")
response = HttpResponse(FileWrapper(myfile), content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename=attendenceReport.xls'
return response
return render_to_response('attendenceReport.html',locals(),context_instance=RequestContext(req))
