Example #1
0
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
Example #2
0
    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
Example #3
0
    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
Example #4
0
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
Example #5
0
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
Example #6
0
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
Example #7
0
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
Example #8
0
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
Example #9
0
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')
Example #11
0
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
Example #12
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
Example #13
0
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
Example #14
0
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")
Example #15
0
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
Example #17
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
Example #18
0
File: fees.py Project: irfan007/SMS
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))
Example #19
0
    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
Example #20
0
    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
Example #21
0
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')
Example #22
0
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()
Example #23
0
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))