def output(file_or_stream, sheetname, headers, values, encoding = 'utf8', footer_text = None, footer_link = None): import xlwt book = xlwt.Workbook(encoding) sh = book.add_sheet(sheetname) datestyle = XFStyle() datestyle.num_format_str = 'DD/MM/YYYY' timestyle = XFStyle() timestyle.num_format_str = 'HH:MM:SS' header_font = Font() header_font.bold = True al = Alignment() al.horz = Alignment.HORZ_CENTER header_style = XFStyle() header_style.font = header_font header_style.alignment = al for i,header in enumerate(headers): sh.write(0, i, header, header_style) sh.set_panes_frozen(True) # frozen headings instead of split panes sh.set_horz_split_pos(1) # in general, freeze after last heading row sh.set_remove_splits(True) # if user does unfreeze, don't leave a split there for j, row in enumerate(values): for i, value in enumerate(row): if value.__class__ == date: sh.write(j+1, i, value, datestyle) elif value.__class__ == time: sh.write(j+1, i, value, timestyle) else: sh.write(j+1, i, value) if footer_link and footer_text: link_font = Font() link_font.name = 'Verdana' link_font.colour_index = 4 link_font.height = 20*8 al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_BOTTOM link_style = XFStyle() link_style.font = link_font link_style.alignment = al row = len(values) + 1 sh.write_merge(row, row, 0, len(headers)-1, Formula('HYPERLINK("' + footer_link + '";"' + footer_text + '")'), link_style) book.save(file_or_stream)
def queryExcel(filename, dategroup, response): filename += u'.xls' response['Content-Disposition'] = (u'attachment;filename=%s' % filename).encode('utf-8') import xlwt from xlwt import Font, Alignment style1 = xlwt.XFStyle() font1 = Font() font1.height = 360 font1.name = u'仿宋' style1.font = font1 algn = Alignment() algn.horz = Alignment.HORZ_LEFT style1.alignment = algn style1.font = font1 style0 = xlwt.XFStyle() algn0 = Alignment() algn0.horz = Alignment.HORZ_CENTER font = Font() font.height = 320 font.bold = False font.name = u'仿宋' style0.alignment = algn0 style0.font = font wb = xlwt.Workbook() ws = wb.add_sheet(u"销售报表", cell_overwrite_ok=True) rownum = 0 ws.write_merge(rownum, rownum, 0, 0, u'序号', style0) ws.write_merge(rownum, rownum, 1, 1, u'品牌', style0) ws.write_merge(rownum, rownum, 2, 2, u'型号', style0) ws.write_merge(rownum, rownum, 3, 3, u'类型', style0) ws.write_merge(rownum, rownum, 4, 4, u'数量', style0) ws.write_merge(rownum, rownum, 5, 5, u'账户', style0) ws.write_merge(rownum, rownum, 6, 6, u'姓名', style0) ws.write_merge(rownum, rownum, 7, 7, u'主管', style0) rownum += 1 datanum = 1 for data in dategroup: ws.write_merge(rownum, rownum, 0, 7, u'日期:%s 厅台:%s 总计:%s 台' % (data['date'], data['officename'],data['totalnum']), style1) rownum += 1 for i, row in enumerate(data['query']): ws.write_merge(rownum, rownum, 0, 0, datanum, style0) ws.write_merge(rownum, rownum, 1, 1, row['productbrandsname'], style0) ws.write_merge(rownum, rownum, 2, 2, row['productname'], style0) ws.write_merge(rownum, rownum, 3, 3, row['ordertypename'], style0) ws.write_merge(rownum, rownum, 4, 4, row['ordernum'], style0) ws.write_merge(rownum, rownum, 5, 5, row['username'], style0) ws.write_merge(rownum, rownum, 6, 6, row['get_full_name'], style0) ws.write_merge(rownum, rownum, 7, 7, row['managername'], style0) datanum += 1 rownum += 1 for i in range(8): ws.col(i).width = 256 * 20 wb.save(response)
def queryExcel(filename, dategroup, response): ''' 导出excel ''' filename += u'.xls' response['Content-Disposition'] = (u'attachment;filename=%s' % filename).encode('utf-8') import xlwt from xlwt import Font, Alignment style1 = xlwt.XFStyle() font1 = Font() font1.height = 260 font1.name = u'仿宋' style1.font = font1 algn = Alignment() algn.horz = Alignment.HORZ_LEFT style1.alignment = algn style1.font = font1 style0 = xlwt.XFStyle() algn0 = Alignment() algn0.horz = Alignment.HORZ_CENTER font = Font() font.height = 220 font.bold = False font.name = u'仿宋' style0.alignment = algn0 style0.font = font wb = xlwt.Workbook() ws = wb.add_sheet(u"考试报表", cell_overwrite_ok=True) rownum = 0 ws.write_merge(rownum, rownum, 0, 0, u'序号', style0) ws.write_merge(rownum, rownum, 1, 1, u'员工ID', style0) ws.write_merge(rownum, rownum, 2, 2, u'姓名', style0) ws.write_merge(rownum, rownum, 3, 3, u'得分', style0) ws.write_merge(rownum, rownum, 4, 4, u'主管', style0) rownum += 1 datanum = 1 for data in dategroup: ws.write_merge(rownum, rownum, 0, 4, u'考试:%s 参考人数:%s 旷考人数:%s' % (data['name'],data['totalnum'],data['untotalnum']), style1) rownum += 1 for i, row in enumerate(data['query']): ws.write_merge(rownum, rownum, 0, 0, datanum, style0) ws.write_merge(rownum, rownum, 1, 1, row['username'], style0) ws.write_merge(rownum, rownum, 2, 2, row['get_full_name'], style0) ws.write_merge(rownum, rownum, 3, 3, row['score'], style0) ws.write_merge(rownum, rownum, 4, 4, row['managername'], style0) datanum += 1 rownum += 1 for i in range(5): ws.col(i).width = 256 * 10 wb.save(response)
def export_to_excel(self, **kwargs): work_book = Workbook(encoding="ascii") center_alignment = Alignment() center_alignment.horz = Alignment.HORZ_RIGHT center_alignment.vert = Alignment.VERT_CENTER border = Borders() border.top = Borders.THIN border.left = Borders.THIN border.bottom = Borders.THIN border.right = Borders.THIN head_style = XFStyle() head_pattern = Pattern() head_pattern.pattern = Pattern.SOLID_PATTERN head_pattern.pattern_fore_colour = Style.colour_map['gray25'] head_style.pattern = head_pattern head_font = Font() head_font.bold = True head_style.font = head_font head_style.alignment = center_alignment head_style.borders = border data_style = XFStyle() data_style.alignment = center_alignment data_style.borders = border work_sheet = work_book.add_sheet("Sheet") limit = 10000 count = self.get_data_scale(**kwargs)['count'] times = (count + limit - 1) // limit add_header = False for i in range(1): kwargs["startIndex"] = i * limit + 1 kwargs['endIndex'] = (i + 1) * limit + 1 records = self.get_data(**kwargs) column_names = [] y = 0 for data in records: if not add_header: x = 0 for column_name in data.keys(): work_sheet.write(y, x, column_name, head_style) column_names.append(column_name) x += 1 add_header = True y += 1 x = 0 for column_name in column_names: work_sheet.write(y, x, data[column_name], data_style) x += 1 y += 1 x = 0 work_book.save(BASE_WIN_OUTPUT_PATH + "result.xls")
def xls_headers(self, text, title): u""" """ # headers style header1 = easyxf() header2 = easyxf() # font font1 = Font() font1.name = 'Times New Roman' font1.bold = True # alignment alig1 = Alignment() alig1.horz = 2 # assign header1.font = font1 header1.alignment = alig1 header2.font = font1 # headers 1st sheet self.feuy1.write_merge(0, 0, 0, 1, text.get('titre') + title, header1) self.feuy1.write(1, 0, text.get('nomfic'), header2) self.feuy1.write(2, 0, text.get('mtcthem'), header2) self.feuy1.write(3, 0, text.get('mtcgeo'), header2) self.feuy1.write(4, 0, text.get('description'), header2) self.feuy1.write(5, 0, text.get('cadre'), header2) self.feuy1.write(6, 0, text.get('num_objets'), header2) self.feuy1.write(7, 0, text.get('num_attrib'), header2) self.feuy1.write(8, 0, text.get('date_crea'), header2) self.feuy1.write(9, 0, text.get('date_actu'), header2) self.feuy1.write(10, 0, text.get('source'), header2) self.feuy1.write(11, 0, text.get('diffusion'), header2) self.feuy1.write(12, 0, text.get('responsable'), header2) self.feuy1.write(13, 0, text.get('ptcontact'), header2) self.feuy1.write(14, 0, text.get('siteweb'), header2) self.feuy1.write(15, 0, text.get('geometrie'), header2) self.feuy1.write(16, 0, text.get('echelle'), header2) self.feuy1.write(17, 0, text.get('precision'), header2) self.feuy1.write(18, 0, text.get('srs'), header2) self.feuy1.write(19, 0, text.get('emprise'), header2) # headers 2nd sheet self.feuy2.write(0, 0, text.get('numero'), header2) self.feuy2.write(0, 1, text.get('nom'), header2) self.feuy2.write(0, 2, text.get('type'), header2) self.feuy2.write(0, 3, text.get('longueur'), header2) self.feuy2.write(0, 4, text.get('precision'), header2) self.feuy2.write(0, 5, text.get('description'), header2) self.feuy2.write(0, 6, text.get('somme'), header2) self.feuy2.write(0, 7, text.get('moyenne'), header2) self.feuy2.write(0, 8, text.get('mediane'), header2) self.feuy2.write(0, 9, text.get('min'), header2) self.feuy2.write(0, 10, text.get('max'), header2) self.feuy2.write(0, 11, text.get('ecartype'), header2) # End of function return self.feuy1, self.feuy2
def _get_header_styles(self): s1 = XFStyle() al = Alignment() al.horz = Alignment.HORZ_CENTER s1.alignment = al s2 = XFStyle() borders = Borders() borders.bottom = 2 s2.borders = borders return s1, s2
def excel_writer(filenames): style2 = XFStyle() style2.num_format_str = '####.##0' al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_BOTTOM style2.alignment = al style3 = XFStyle() style3.num_format_str = '0.00%' style3.alignment = al wb = Workbook() sheet1 = wb.add_sheet("Sheet 1") index = 0 columns_list = ['c', 'e', 'f', 'g', 'h', 'i', 'k', 'l', 'm'] if True: print(f"{bcolors.WARNING}PLEASE INPUT THE 9 COLUMN LETTERS YOU NEED SEPARATED WITH A COMMA{bcolors.ENDC}") print(f"{bcolors.WARNING}Example: D,A,B,C,F,G,E,K,W{bcolors.ENDC}") columns = input() while invalid(columns): print(f"{bcolors.FAIL}INVALID INPUT{bcolors.ENDC}") print(f"{bcolors.WARNING}Example: D,A,B,C{bcolors.ENDC}") columns = input() columns_list = re.split(',', columns) sheet1.write(index, letters_to_indexes(columns_list[0]), "Concurrent Users") sheet1.write(index, letters_to_indexes(columns_list[1]), "Average Total Execution Time (sec)") sheet1.write(index, letters_to_indexes(columns_list[2]), "90% Total Execution Time (sec)") sheet1.write(index, letters_to_indexes(columns_list[3]), "Min Total Execution Time (sec)") sheet1.write(index, letters_to_indexes(columns_list[4]), "Max Total Execution Time (sec)") sheet1.write(index, letters_to_indexes(columns_list[5]), "Number of Calls") sheet1.write(index, letters_to_indexes(columns_list[6]), "Error Rate (%)") sheet1.write(index, letters_to_indexes(columns_list[7]), "Date") sheet1.write(index, letters_to_indexes(columns_list[8]), "Start Time") index = 1 for file in filenames: print(file) numtuple = getnumbers(file) print(numtuple) sheet1.write(index, letters_to_indexes(columns_list[0]), numtuple[6]) sheet1.write(index, letters_to_indexes(columns_list[1]), numtuple[0], style2) sheet1.write(index, letters_to_indexes(columns_list[2]), numtuple[1], style2) sheet1.write(index, letters_to_indexes(columns_list[3]), numtuple[2], style2) sheet1.write(index, letters_to_indexes(columns_list[4]), numtuple[3], style2) sheet1.write(index, letters_to_indexes(columns_list[5]), numtuple[4]) sheet1.write(index, letters_to_indexes(columns_list[6]), numtuple[5], style3) sheet1.write(index, letters_to_indexes(columns_list[7]), str(date.today())) sheet1.write(index, letters_to_indexes(columns_list[8]), "4:42:00") index += 1 wb.save("example.xls")
def _get_header_style(self): # s1 = self._style_factory() al = Alignment() al.horz = Alignment.HORZ_CENTER # s1.alignment = al s2 = self._style_factory() borders = Borders() borders.bottom = 2 s2.borders = borders s2.alignment = al return s2
def get_sheet_title_style(bg_color=0x39, font_color=0x0, font_size=300, has_pattern=True, horz_center=True): fnt = Font() fnt.name = 'Arial' fnt.colour_index = font_color fnt.bold = True fnt.height = font_size borders = Borders() borders.left = 1 borders.right = 1 borders.top = 1 borders.bottom = 1 al = Alignment() if horz_center: al.horz = Alignment.HORZ_CENTER else: al.horz = Alignment.HORZ_LEFT al.vert = Alignment.VERT_CENTER pattern = None if has_pattern: pattern = Pattern() pattern.pattern = 1 pattern.pattern_fore_colour = bg_color pattern.pattern_back_colour = bg_color style = XFStyle() style.font = fnt style.borders = borders style.alignment = al if pattern: style.pattern = pattern return style
def def_style(): style = XFStyle() font = Font() # 这部分设置字体 font.name = 'Times New Roman' # 或者换成外面传进来的参数,这样可以使一个函数定义所有style # font.bold = 'True' # font.height = 24 # font.size = 2000 # font.colour_index = 3 style.font = font alignment = Alignment() # 这部分设置居中格式 alignment.horz = Alignment.HORZ_CENTER # 水平居中 alignment.vert = Alignment.VERT_CENTER # 垂直居中 style.alignment = alignment return style
def __init__(self): self.style = XFStyle() base_font = Font() # 设置基本字体 base_font.height = FONT_SIZE_UNIT * 12 self.style.font = base_font alignment = Alignment() # 设置对齐 alignment.horz = Alignment.HORZ_CENTER alignment.vert = Alignment.VERT_CENTER self.style.alignment = alignment borders = Borders() # 设置边框 borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN borders.left_colour = COLOUR_BLACK borders.right_colour = COLOUR_BLACK borders.top_colour = COLOUR_BLACK borders.bottom_colour = COLOUR_BLACK self.style.borders = borders self.style.num_format_str = '#,##0' # 设置数字格式
def downloadTrue(request): response = HttpResponse(mimetype=u'application/ms-excel') filename = u'错误实名制列表.xls' response['Content-Disposition'] = (u'attachment;filename=%s' % filename).encode('utf-8') import xlwt from xlwt import Font, Alignment style1 = xlwt.XFStyle() font1 = Font() font1.height = 250 font1.name = u'仿宋' style1.font = font1 algn = Alignment() algn.horz = Alignment.HORZ_LEFT style1.alignment = algn style1.font = font1 wb = xlwt.Workbook() ws = wb.add_sheet(u"错误实名制列表", cell_overwrite_ok=True) rownum = 0 ws.write_merge(rownum, rownum, 0, 0, u'手机号', style1) ws.write_merge(rownum, rownum, 1, 1, u'姓名', style1) ws.write_merge(rownum, rownum, 2, 2, u'身份证号', style1) ws.write_merge(rownum, rownum, 3, 3, u'地址', style1) ws.write_merge(rownum, rownum, 4, 4, u'错误原因', style1) rownum += 1 for o in Truename.objects.filter(status=2).order_by('datetime'): ws.write_merge(rownum, rownum, 0, 0, o.tel, style1) ws.write_merge(rownum, rownum, 1, 1, o.name, style1) ws.write_merge(rownum, rownum, 2, 2, o.number, style1) ws.write_merge(rownum, rownum, 3, 3, o.address, style1) ws.write_merge(rownum, rownum, 4, 4, o.help, style1) rownum += 1 for i in range(5): ws.col(i).width = 256 * 20 wb.save(response) return response
def main(): lst = [] path = r'./Homework/03/' with open(path + 'test.txt', 'r') as fpin: title = fpin.readline().strip().replace('\n', '').split(',') while True: s = fpin.readline().strip().replace('\n', '').split(',') if s == ['']: break lst.append(s) fpin.close() book = Workbook() sheet1 = book.add_sheet('data') style = XFStyle() font = Font() font.name = '宋体' font.height = 280 style.font = font alignment = Alignment() alignment.horz = Alignment.HORZ_CENTER alignment.vert = Alignment.VERT_CENTER style.alignment = alignment row = sheet1.row(0) for i, j in zip(range(len(title)), title): row.write(i, j, style=style) for i, j in zip(range(1, len(lst) + 1), lst): row = sheet1.row(i) for x, y in zip(range(len(j)), j): row.write(x, y, style=style) book.save(path + 'data.xls')
from xlwt import XFStyle, Alignment left = XFStyle() left_alignment = Alignment() left_alignment.horz = Alignment.HORZ_LEFT left_alignment.dire = Alignment.DIRECTION_RL left.alignment = left_alignment right = XFStyle() right_alignment = Alignment() right_alignment.horz = Alignment.HORZ_RIGHT right_alignment.dire = Alignment.DIRECTION_RL right.alignment = right_alignment center = XFStyle() center_alignment = Alignment() center_alignment.horz = Alignment.HORZ_CENTER center_alignment.direction = Alignment.DIRECTION_RL center.alignment = center_alignment
def orders_report_export(request): response = HttpResponse(content_type='application/ms-excel') response['Content-Disposition'] = 'attachment; filename="orders.xls"' wb = xlwt.Workbook(encoding='utf-8') ws = wb.add_sheet('گزاش درخواست') # Sheet header, first row row_num = 0 base_style = xlwt.XFStyle() al = Alignment() al.horz = Alignment.HORZ_CENTER b_nazanin = xlwt.Font() b_nazanin.name = "B Nazanin" b_nazanin.height = 220 num_format_str = "#,##0" base_style.alignment = al base_style.font = b_nazanin import copy header_style = copy.deepcopy(base_style) font_style = copy.deepcopy(base_style) number_style = copy.deepcopy(base_style) footer_style = copy.deepcopy(base_style) header_style.font.bold = True header_style.font.height = 240 number_style.num_format_str = num_format_str footer_style.num_format_str = num_format_str footer_style.font.height = 240 columns = ( 'ردیف', 'درخواست', 'مشتری', 'صنعت', 'تاریخ', 'تعداد دستگاه', 'کیلووات', 'پیش فاکتور', 'تأخیر ثبت', 'زمان پاسخ دهی', 'واریزی', 'زمان اولین واریز', 'سررسید', 'مجوز', 'زمان اولین مجوز', 'مبلغ کل', 'مبلغ قابل دریافت', 'زمان تسویه', 'کارشناس', 'تاریخ', 'وضعیت', ) for col_num in range(len(columns)): ws.write(row_num, col_num, columns[col_num], header_style) # context = get_filtered_orders(request) request_get_url = request.session.get('order-report-filters', request.GET) order_filtered = OrderFilter(request_get_url, request=request) for order in order_filtered.qs: row_num += 1 exportables = [] exportables.append({'style': font_style, 'value': row_num}) exportables.append({'style': font_style, 'value': order.number}) exportables.append({'style': font_style, 'value': order.customer.name}) exportables.append({ 'style': font_style, 'value': order.customer.type.name }) exportables.append({'style': font_style, 'value': str(order.date_fa)}) exportables.append({'style': number_style, 'value': order.qty_total()}) exportables.append({'style': number_style, 'value': order.total_kw()}) exportables.append({ 'style': font_style, 'value': order.get_proformas().count() }) exportables.append({ 'style': font_style, 'value': order.submit_delay() }) exportables.append({ 'style': font_style, 'value': order.first_response_time() }) exportables.append({ 'style': font_style, 'value': order.get_incomes().count() }) exportables.append({ 'style': font_style, 'value': order.first_payment_time() }) exportables.append({ 'style': font_style, 'value': str(order.last_cheque().due_date) if order.last_cheque() else '' }) exportables.append({ 'style': font_style, 'value': order.get_permits().count() }) exportables.append({ 'style': font_style, 'value': order.first_permit_time() }) exportables.append({ 'style': number_style, 'value': order.permit_total_after_tax() }) exportables.append({ 'style': number_style, 'value': order.income_receivable() }) exportables.append({ 'style': font_style, 'value': order.settlement_time() }) exportables.append({ 'style': font_style, 'value': order.owner.last_name }) exportables.append({ 'style': font_style, 'value': str(order.orderfollowup_set.last().date_fa) if order.orderfollowup_set.all().exists() else '', }) exportables.append({ 'style': font_style, 'value': order.orderfollowup_set.last().summary if order.orderfollowup_set.all().exists() else '', }) for col_num in range(len(exportables)): ws.write(row_num, col_num, exportables[col_num]['value'], exportables[col_num]['style']) # row_num += 1 # ws.write_merge(row_num, row_num, 0, 5, 'جمع', footer_style) # ws.write(row_num, 6, context['qty'], footer_style) # ws.write(row_num, 9, context['kw'], footer_style) # ws.write(row_num, 11, context['amount'], footer_style) ws.cols_right_to_left = True wb.save(response) return response
# 操作Excel2003 或者更低 from xlwt import Workbook, Alignment, Borders, XFStyle, Formula import xlrd book = Workbook() sheet1 = book.add_sheet('first sheet') a1 = Alignment() a1.horz = Alignment.HORZ_CENTER a1.vert = Alignment.HORZ_CENTER borders = Borders() borders.bottom = Borders.THICK style = XFStyle() style.alignment = a1 style.borders = borders row = sheet1.row(0) row.write(0, 'test', style=style) row = sheet1.row(1) for i in range(5): row.write(i, i, style=style) row.write(5, Formula('SUM(A2:E2)'), style=style) book.save('./test2003.xls') book = xlrd.open_workbook('./test2003.xls') sheet1 = book.sheet_by_name('first sheet') row = sheet1.row(0) print(row[0].value) # 读不到公式,无解 print(sheet1.row(1)[5].value) # print(sheet1.cell(1, 5).value)
def exportToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1') sheet1.col(1).width = 256*20; sheet1.col(2).width = 256*80; sheet1.col(3).width = 256*10; sheet1.col(4).width = 256*20; default_book_style = book.default_style default_book_style.font.height = 20 * 36 # 36pt fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 algn1 = Alignment(); algn1.wrap = 1; #algn1.horz = Alignment.HORZ_CENTER #algn1.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP print "export"; if( objectProject): i=0; print "start" ; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; row1 = sheet1.row(i) ; row1.write(0, ('risk id').decode('UTF8'),styleHead ); sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ,styleHead ); # row1.write(1, ('รายละเอียด').decode('UTF8')); row1.write(3, ('วันที่รายงาน').decode('UTF8'), styleHead ); row1.write(4, ('หน่วยที่รายงาน').decode('UTF8'), styleHead ); i=i+1; style1 = XFStyle(); style1.alignment = algn1; #style0 = xlwt.easyxf('font: name Times New Roman size 20, color-index black, bold on') for value in objectProject: row1 = sheet1.row(i) ; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = algn1; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; row1.write(0, value.get('risk_management_id'),styleRowDetail ); #row1.write(1, value.get('risk_detail').decode('UTF8') , style1); sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap ); row1.write(3, value.get('report_date') ,styleDate); row1.write(4, value.get('report').decode('UTF8') ,styleRowDetail ); i=i+1; row1 = sheet1.row(i) ; row1.write(0," " ); row1.write(1,('หน่วยที่เกี่ยวข้อง').decode('UTF8') ,styleHead ); sheet1.write_merge(i, i, 2, 3,('รายละเอียดการตอบ').decode('UTF8') , styleHead ); i=i+1; for sub in value.get('response') : row1 = sheet1.row(i) ; row1.write(0," " ); row1.write(1,sub.get('risk_team').decode('UTF8') , styleRowDetail ); sheet1.write_merge(i, i, 2, 3,sub.get('result').decode('UTF8') , StyleRowDetailWrap ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simple.xls'); print dirTempFile; book.save(dirTempFile);
def detail_budget(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) if int(month) in [1,3,5,7,8,10,12]: join_date=year +'-'+month+'-'+'31' if int(month) in [4,6,9,11]: join_date=year +'-'+month+'-'+'30' if int(month) in [2]: if int(year) % 4 == 0: join_date=year +'-'+month+'-'+'29' else: join_date=year +'-'+month+'-'+'28' #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'RO of HOD',style_header) ws.col(2).width = 8000 ws.write(0,3,'Employee Name',style_header) ws.col(3).width = 9000 ws.write(0,4,'Designation',style_header) ws.col(4).width = 8000 ws.write(0,5,'Working Days',style_header) ws.col(5).width = 5000 ws.write(0,6,'Working Hours',style_header) ws.col(6).width = 5000 ws.write(0,7,'Working Month',style_header) ws.col(7).width = 5000 ws.write(0,8,'Salary Amount',style_header) ws.col(8).width = 5000 ws.write(0,9,month_name,style_header) ws.col(9).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource "\ "as res on (emp.resource_id=res.id) where emp.department_id = "\ "'"+str(this.dept_id.id)+"' and emp.department_id is not null and "\ "res.active=True and emp.joining_date <'"+str(join_date)+"' order by emp.department_id, (substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource "\ "as res on (emp.resource_id=res.id) where res.active=True and emp.joining_date < '"+str(join_date)+"' order by emp.department_id, "\ "(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=0 dept_dict = {} grand = total = pay_total = pay_grand = budget = 0.0 pay_data = False flag = True for each in emp_obj.browse(cr, uid, emp_ids): tolal_months = 0 if not each.joining_date: continue dt1 = datetime.strptime(each.joining_date,'%Y-%m-%d') dt2 = datetime.strptime(time.strftime(DEFAULT_SERVER_DATE_FORMAT),'%Y-%m-%d') start_month=dt1.month end_months=(dt2.year-dt1.year)*12 + dt2.month+1 dates=[datetime(year=yr, month=mn, day=1) for (yr, mn) in ( ((m - 1) / 12 + dt1.year, (m - 1) % 12 + 1) for m in range(start_month, end_months) )] for val in dates: tolal_months += 1 pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) i+=1 if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days or '0.0') ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: salary = 0.0 if flag: ws.write(i,0, 'Allocated Budget',style_header) ws.write(i,1, 0.0,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, '',style_header) ws.write(i,4, '',style_header) ws.write(i,5, '',style_header) ws.write(i,6, '',style_header) ws.write(i,7, '',style_header) ws.write(i,8, total,style_header) if pay_total: ws.write(i,9,pay_total,style_header) else: ws.write(i,9, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,10, diff,style_header1) flag = False i += 2 total = pay_total = budget = 0.0 name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,0, 'X Department') ws.write(i,1,'X Reporting Officer') ws.write(i,2,'X Reporting Officer') ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days or '0.0') ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 else: dept_dict[str(each.department_id.id)] = '' if i != 1: ws.write(i,0, 'Allocated Budget',style_header) if budget: ws.write(i,1, budget,style_header) else: budget = 0.0 ws.write(i,1,budget,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, '',style_header) ws.write(i,4, '',style_header) ws.write(i,5, '',style_header) ws.write(i,6, '',style_header) ws.write(i,7, '',style_header) ws.write(i,8, total,style_header) if pay_total: ws.write(i,9,pay_total,style_header) else: ws.write(i,9, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,10, diff,style_header1) i += 2 total = pay_total = budget = 0.0 if each.department_id: salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,3, name) ws.write(i,4, each.designation_id and each.designation_id.name or '') ws.write(i,5, pay_data.days) ws.write(i,6, pay_data.over_time) ws.write(i,7, str(tolal_months) + ' Month') if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic ws.write(i,8, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,9,pay_data.total_amount or 0.0) else: ws.write(i,9, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+1,7, 'Total',style_header) ws.write(i+1,8, total,style_header) ws.write(i+3,7, 'Grand Total',style_header) ws.write(i+3,8, grand,style_header) ws.write(i+1,9, pay_total,style_header) ws.write(i+3,9, pay_grand,style_header) diff_pay = pay_total - total diff_grand = pay_grand - grand ws.write(i+1,10, diff_pay,style_header1) ws.write(i+3,10, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def performance_register_report(self, cr, uid, ids, data, context=None): obj = self.browse(cr, uid, ids) emp_obj = self.pool.get('hr.employee') f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('Payment Bonus') total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0 fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x00 borders1.right = 0x00 borders1.top = 0x00 borders1.bottom = 0x00 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x1F style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x00 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt7 = Font() fnt7.name = 'Arial' fnt7.height = 275 content_fnt7 = Font() content_fnt7.name = 'Arial' content_fnt7.height = 150 align_content7 = Alignment() align_content7.horz = Alignment.HORZ_CENTER borders7 = Borders() borders7.left = 0x02 borders7.right = 0x02 borders7.top = 0x02 borders7.bottom = 0x02 align7 = Alignment() align7.horz = Alignment.HORZ_CENTER align7.vert = Alignment.VERT_CENTER pattern7 = Pattern() pattern7.pattern7 = Pattern.SOLID_PATTERN pattern7.pattern7_fore_colour = 0x1F style_header7 = XFStyle() style_header7.font = fnt style_header7.pattern = pattern style_header7.borders = borders style_header7.alignment = align fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 275 content_fnt2 = Font() content_fnt2.name = 'Arial' content_fnt2.height = 150 fnt2.bold = True align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x00 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x1F style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 300 fnt3.bold = True align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x00 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x1F style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt6 = Font() fnt6.name = 'Arial' fnt6.height = 275 content_fnt6 = Font() content_fnt6.name = 'Arial' content_fnt6.height = 150 align_content6 = Alignment() align_content6.horz = Alignment.HORZ_CENTER borders6 = Borders() borders6.left = 0x02 borders6.right = 0x02 # borders6.top = 0x00 # borders6.bottom = 0x00 align6 = Alignment() align6.horz = Alignment.HORZ_CENTER align6.vert = Alignment.VERT_CENTER pattern6 = Pattern() pattern6.pattern = Pattern.SOLID_PATTERN pattern6.pattern_fore_colour = 0x1F style_header6 = XFStyle() style_header6.font = fnt style_header6.pattern = pattern style_header6.borders = borders6 style_header6.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 200 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() align_content5.horz = Alignment.HORZ_CENTER borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() align5.horz = Alignment.HORZ_CENTER align5.vert = Alignment.VERT_CENTER pattern5 = Pattern() # pattern5.pattern = Pattern.SOLID_PATTERN # pattern5.pattern_fore_colour = 0x1F style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 if obj.company_id: get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip else: get_name = obj.employee_id.resource_id.company_id.name + ' ' + obj.employee_id.resource_id.company_id.street + ' ' + ',' + obj.employee_id.resource_id.company_id.city + ' ' + '-' + obj.employee_id.resource_id.company_id.zip date1 = datetime.strptime(obj.from_date, "%Y-%m-%d").timetuple().tm_year date2 = datetime.strptime(obj.till_date, "%Y-%m-%d").timetuple().tm_year if date1 == date2: d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' - ' + str( date1) + ' ' + '[See Rule 4(b)]' else: d_name = 'BONUS PAID TO EMPLOYEES FOR THE ACCOUNTING YEAR ON THE' + ' ' + str( date1) + ' ' + '-' + ' ' + str(date2) + ' ' + '[See Rule 4(b)]' ws.row(0).height = 500 ws.row(1).height = 500 ws.write_merge(0, 0, 0, 2, 'FORM C', style_header1) ws.write_merge(0, 0, 3, 10, d_name, style_header1) ws.write_merge(1, 1, 0, 2, 'Name of the Establishment', style_header1) ws.write_merge(1, 1, 3, 10, get_name, style_header1) ws.write_merge(1, 1, 11, 19, 'No. of Working days in the Year', style_header1) ws.col(0).width = 5000 ws.col(1).width = 7500 ws.col(2).width = 5000 ws.col(3).width = 7000 ws.col(4).width = 5000 ws.col(5).width = 8000 ws.col(6).width = 6000 ws.col(7).width = 8000 ws.col(8).width = 8000 ws.col(9).width = 9000 ws.col(10).width = 8000 ws.col(11).width = 9000 ws.col(12).width = 7000 ws.col(13).width = 7000 ws.col(14).width = 5000 ws.col(15).width = 5000 ws.col(16).width = 7000 ws.col(17).width = 3000 ws.col(18).width = 3000 ws.col(19).width = 3000 ws.col(20).width = 3000 ws.col(21).width = 3000 ws.col(22).width = 3000 ws.col(23).width = 3000 ws.col(24).width = 3000 ws.col(25).width = 3000 ws.col(26).width = 3000 ws.col(27).width = 3000 ws.col(28).width = 3000 ws.col(29).width = 4000 ws.row(2).height = 400 ws.write(2, 0, 'EMP. CODE', style_header) ws.write(2, 1, 'NAME', style_header) ws.write(2, 2, 'JOINING DATE', style_header) ws.write(2, 3, 'Father Name', style_header) ws.write(2, 4, 'Designation', style_header) ws.write(2, 5, 'Whether he has ', style_header) ws.write(2, 6, 'No.of days', style_header) ws.write(2, 7, 'Total Salary or wages', style_header) ws.write(2, 8, 'Account of bonus payable', style_header) ws.write_merge(2, 2, 9, 12, 'Deduction', style_header7) ws.write(2, 13, 'Net amount payable', style_header) ws.write(2, 14, 'Amount actualy', style_header) ws.write(2, 15, 'Date on which ', style_header) ws.write(2, 16, 'Signature/ Thumb ', style_header) ws.write(2, 17, 'Remarks', style_header) ws.write_merge(2, 2, 18, 19, 'APRIL', style_header7) ws.write_merge(2, 2, 20, 21, 'MAY', style_header7) ws.write_merge(2, 2, 22, 23, 'JUNE', style_header7) ws.write_merge(2, 2, 24, 25, 'JULY', style_header7) ws.write_merge(2, 2, 26, 27, 'AUGUST', style_header7) ws.write_merge(2, 2, 28, 29, 'SEPTEMBER', style_header7) ws.write_merge(2, 2, 30, 31, 'OCTOBER', style_header7) ws.write_merge(2, 2, 32, 33, 'NOVEMBER', style_header7) ws.write_merge(2, 2, 34, 35, 'DECEMBER', style_header7) ws.write_merge(2, 2, 36, 37, 'JANUARY', style_header7) ws.write_merge(2, 2, 38, 39, 'FEBRUARY', style_header7) ws.write_merge(2, 2, 40, 41, 'MARCH', style_header7) ws.write_merge(2, 2, 42, 43, 'TOTAL', style_header7) ws.write(2, 44, 'BONUS', style_header) ws.row(3).height = 400 ws.write(3, 0, '', style_header6) ws.write(3, 1, '', style_header6) ws.write(3, 2, '', style_header6) ws.write(3, 3, '', style_header6) ws.write(3, 4, '', style_header6) ws.write(3, 5, 'completed 15 year of', style_header6) ws.write(3, 6, 'worked in the', style_header6) ws.write(3, 7, 'in respect of', style_header6) ws.write(3, 8, 'under section 10', style_header6) ws.write(3, 9, 'Puja bonus or other customary', style_header7) ws.write(3, 10, 'Interim bonus', style_header7) ws.write(3, 11, 'Deduction on account of financial', style_header7) ws.write(3, 12, 'Total sum deducted', style_header7) ws.write(3, 13, '(Col.8 minus Col.12)', style_header6) ws.write(3, 14, 'paid', style_header6) ws.write(3, 15, 'paid', style_header6) ws.write(3, 16, 'impression', style_header6) ws.write(3, 17, '', style_header6) ws.write(3, 18, '', style_header7) ws.write(3, 19, '', style_header7) ws.write(3, 20, '', style_header7) ws.write(3, 21, '', style_header7) ws.write(3, 22, '', style_header7) ws.write(3, 23, '', style_header7) ws.write(3, 24, '', style_header7) ws.write(3, 25, '', style_header7) ws.write(3, 26, '', style_header7) ws.write(3, 27, '', style_header7) ws.write(3, 28, '', style_header7) ws.write(3, 29, '', style_header7) ws.write(3, 30, '', style_header7) ws.write(3, 31, '', style_header7) ws.write(3, 32, '', style_header7) ws.write(3, 33, '', style_header7) ws.write(3, 34, '', style_header7) ws.write(3, 35, '', style_header7) ws.write(3, 36, '', style_header7) ws.write(3, 37, '', style_header7) ws.write(3, 38, '', style_header7) ws.write(3, 39, '', style_header7) ws.write(3, 40, '', style_header7) ws.write(3, 41, '', style_header7) ws.write(3, 42, '', style_header7) ws.write(3, 43, '', style_header7) ws.write(3, 44, '', style_header7) ws.row(4).height = 400 ws.write(4, 0, '', style_header6) ws.write(4, 1, '', style_header6) ws.write(4, 2, '', style_header6) ws.write(4, 3, '', style_header6) ws.write(4, 4, '', style_header6) ws.write(4, 5, 'age at the beginning', style_header6) ws.write(4, 6, 'Establishment', style_header6) ws.write(4, 7, 'the accounting year', style_header6) ws.write(4, 8, 'or section 11', style_header6) ws.write(4, 9, 'bonus paid during', style_header6) ws.write(4, 10, 'or bonus paid in ', style_header6) ws.write(4, 11, 'loss if any caused by', style_header6) ws.write(4, 12, '(Col.9,10 and 11)', style_header6) ws.write(4, 13, '', style_header6) ws.write(4, 14, '', style_header6) ws.write(4, 15, '', style_header6) ws.write(4, 16, 'of the employee', style_header6) ws.write(4, 17, '', style_header6) ws.write(4, 18, '', style_header6) ws.write(4, 19, '', style_header6) ws.write(4, 20, '', style_header6) ws.write(4, 21, '', style_header6) ws.write(4, 22, '', style_header6) ws.write(4, 23, '', style_header6) ws.write(4, 24, '', style_header6) ws.write(4, 25, '', style_header6) ws.write(4, 26, '', style_header6) ws.write(4, 27, '', style_header6) ws.write(4, 28, '', style_header6) ws.write(4, 29, '', style_header6) ws.write(4, 30, '', style_header6) ws.write(4, 31, '', style_header6) ws.write(4, 32, '', style_header6) ws.write(4, 33, '', style_header6) ws.write(4, 34, '', style_header6) ws.write(4, 35, '', style_header6) ws.write(4, 36, '', style_header6) ws.write(4, 37, '', style_header6) ws.write(4, 38, '', style_header6) ws.write(4, 39, '', style_header6) ws.write(4, 40, '', style_header6) ws.write(4, 41, '', style_header6) ws.write(4, 42, '', style_header6) ws.write(4, 43, '', style_header6) ws.write(4, 44, '', style_header6) ws.row(5).height = 400 ws.write(5, 0, '', style_header6) ws.write(5, 1, '', style_header6) ws.write(5, 2, '', style_header6) ws.write(5, 3, '', style_header6) ws.write(5, 4, '', style_header6) ws.write(5, 5, 'of the accounting year', style_header6) ws.write(5, 6, '', style_header6) ws.write(5, 7, '', style_header6) ws.write(5, 8, 'as the case may be', style_header6) ws.write(5, 9, 'the accounting year', style_header6) ws.write(5, 10, 'advance', style_header6) ws.write(5, 11, 'misconduct of the employee', style_header6) ws.write(5, 12, '', style_header6) ws.write(5, 13, '', style_header6) ws.write(5, 14, '', style_header6) ws.write(5, 15, '', style_header6) ws.write(5, 16, '', style_header6) ws.write(5, 17, '', style_header6) ws.write(5, 18, 'DAYS', style_header6) ws.write(5, 19, 'SALARY', style_header6) ws.write(5, 20, 'DAYS', style_header6) ws.write(5, 21, 'SALARY', style_header6) ws.write(5, 22, 'DAYS', style_header6) ws.write(5, 23, 'SALARY', style_header6) ws.write(5, 24, 'DAYS', style_header6) ws.write(5, 25, 'SALARY', style_header6) ws.write(5, 26, 'DAYS', style_header6) ws.write(5, 27, 'SALARY', style_header6) ws.write(5, 28, 'DAYS', style_header6) ws.write(5, 29, 'SALARY', style_header6) ws.write(5, 30, 'DAYS', style_header6) ws.write(5, 31, 'SALARY', style_header6) ws.write(5, 32, 'DAYS', style_header6) ws.write(5, 33, 'SALARY', style_header6) ws.write(5, 34, 'DAYS', style_header6) ws.write(5, 35, 'SALARY', style_header6) ws.write(5, 36, 'DAYS', style_header6) ws.write(5, 37, 'SALARY', style_header6) ws.write(5, 38, 'DAYS', style_header6) ws.write(5, 39, 'SALARY', style_header6) ws.write(5, 40, 'DAYS', style_header6) ws.write(5, 41, 'SALARY', style_header6) ws.write(5, 42, 'DAYS', style_header6) ws.write(5, 43, 'SALARY', style_header6) ws.write(5, 44, '', style_header6) if obj.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', True), ('type', '=', 'Employee')]) list_ids1 = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', False), ('type', '=', 'Employee')]) list_ids = list_ids + list_ids1 elif obj.employee_id: list_ids = emp_obj.search(cr, uid, [('id', '=', obj.employee_id.id), ('active', '=', True), ('type', '=', 'Employee')]) else: raise osv.except_osv( _('Warning !'), _("Please Select Atleast Company Or Employee.")) if len(list_ids) == 1: query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\ "where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid " cr.execute(query) temp = cr.fetchall() else: query ="select hr.sinid,rr.name,hr.doj,job.name,hr.id,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id left join hr_job as job on hr.job_id=job.id "\ "where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj,job.name,hr.id order by hr.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) columnno = 6 no = 6 for val in temp: father_name = '' if val[4]: father_qry = "select name from family where relation='Father' and employee_id='" + str( val[4]) + "' " cr.execute(father_qry) father_temp = cr.fetchall() if father_temp: father_name = father_temp[0][0] else: father_name = ' ' ws.row(no).height = 500 doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y") ws.write(columnno, 0, val[0], style_header5) ws.write(columnno, 1, val[1], style_header5) ws.write(columnno, 2, doj, style_header5) ws.write(columnno, 3, father_name, style_header5) ws.write(columnno, 4, val[3], style_header5) ws.write(columnno, 5, 'Yes', style_header5) ws.write(columnno, 6, '', style_header5) ws.write(columnno, 7, '', style_header5) ws.write(columnno, 8, '', style_header5) ws.write(columnno, 9, '', style_header5) ws.write(columnno, 10, '', style_header5) ws.write(columnno, 11, '', style_header5) ws.write(columnno, 12, '', style_header5) ws.write(columnno, 13, '', style_header5) ws.write(columnno, 14, '', style_header5) ws.write(columnno, 15, '', style_header5) ws.write(columnno, 16, '', style_header5) ws.write(columnno, 17, '', style_header5) ws.write(columnno, 18, val[5], style_header5) ws.write(columnno, 19, val[19], style_header5) ws.write(columnno, 20, val[6], style_header5) ws.write(columnno, 21, val[20], style_header5) ws.write(columnno, 22, val[7], style_header5) ws.write(columnno, 23, val[21], style_header5) ws.write(columnno, 24, val[8], style_header5) ws.write(columnno, 25, val[22], style_header5) ws.write(columnno, 26, val[9], style_header5) ws.write(columnno, 27, val[23], style_header5) ws.write(columnno, 28, val[10], style_header5) ws.write(columnno, 29, val[24], style_header5) ws.write(columnno, 30, val[11], style_header5) ws.write(columnno, 31, val[25], style_header5) ws.write(columnno, 32, val[12], style_header5) ws.write(columnno, 33, val[26], style_header5) ws.write(columnno, 34, val[13], style_header5) ws.write(columnno, 35, val[27], style_header5) ws.write(columnno, 36, val[14], style_header5) ws.write(columnno, 37, val[28], style_header5) ws.write(columnno, 38, val[15], style_header5) ws.write(columnno, 39, val[29], style_header5) ws.write(columnno, 40, val[16], style_header5) ws.write(columnno, 41, val[30], style_header5) ws.write(columnno, 42, val[17], style_header5) ws.write(columnno, 43, val[31], style_header5) ws.write(columnno, 44, val[18], style_header5) columnno += 1 no += 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Payment Bonus.xls' }, context=context)
def exportReport1ToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1'); sheet1.col(1).width = 256*20; sheet1.col(2).width = 256*80; sheet1.col(3).width = 256*10; sheet1.col(4).width = 256*10; sheet1.col(5).width = 256*20; sheet1.col(6).width = 256*20; borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 wrap = Alignment(); wrap.wrap = 1; wrap.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True styleWrap = XFStyle(); styleWrap.alignment = wrap; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; styleDate.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = wrap; if( objectProject): i=0; row1 = sheet1.row(i) ; row1.write(0, ('ลำดับที่').decode('UTF8') ,styleHead); #sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ); row1.write(1, ('เลขความเสี่ยง').decode('UTF8'),styleHead ); row1.write(2, ('อุบัติการณ์/ภาวะไม่พึงประสงค์').decode('UTF8'),styleHead); row1.write(3, ('วันที่รายงาน').decode('UTF8'),styleHead ); row1.write(4, ('ความรุนแรง').decode('UTF8'),styleHead ); row1.write(5, ('ด้าน/โปรแกรม').decode('UTF8') ,styleHead); row1.write(6, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead); i=i+1; for value in objectProject: row1 = sheet1.row(i) ; row1.write(0, value.get('row') ,styleRowDetail ); row1.write(1, str(value.get('risk_id')).decode('UTF8'),styleRowDetail ); row1.write(2, value.get('detail').decode('UTF8'),StyleRowDetailWrap ); row1.write(3, value.get('report_date') ,styleDate ); row1.write(4, value.get('level').decode('UTF8') ,styleRowDetail ); row1.write(5, value.get('pro').decode('UTF8') ,styleRowDetail ); row1.write(6, value.get('reporter').decode('UTF8') ,styleRowDetail ); i=i+1; row2 = sheet1.row(i) ; row2.write(2, ('รายละเอียด').decode('UTF8'),styleHead); row2.write(3, ('หน่วยที่ตอบ').decode('UTF8'),styleHead ); row2.write(4, ('ระยะเวลาตอบ').decode('UTF8'),styleHead ); i=i+1; for resp in value.get('responsible'): row2 = sheet1.row(i) ; row2.write(2, str(resp.get('detail')).decode('UTF8'),StyleRowDetailWrap); row2.write(3, str(resp.get('service_name')).decode('UTF8'),styleRowDetail ); row2.write(4, str(resp.get('report_date')).decode('UTF8'),styleRowDetail ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simpleReport1.xls'); book.save(dirTempFile); return dirTempFile;
def makeXLS(self, id_relatorio, id_promocao=None): """ cria o arquivo xls a var index é utilizada para controlar em qual linha entrara o conteudo """ timename = str(util.dtnow('%d/%m/%Y %H:%M%S')).replace("/", "") timename = timename.replace(":", "") timename = "_" + timename.replace(" ", "") site = getDadosSite(id_site=self.id_site, request=self.request) base = site["base_app"] if not base.endswith("/"): base = base + "/" if int(id_relatorio) == 1: titulo = 'Relacao_usuario_participacao' elif int(id_relatorio) == 2: titulo = 'Relacao_participantes_contemplados' elif int(id_relatorio) == 4: titulo = 'Dados_usuarios_por_promocao' else: titulo = 'Relacao_participantes_promocao' saveplace = ("{0}/ns{1}/arquivos/tmp/" "{2}{3}.xls").format(str(settings.PATH_FILES), str(self.id_site), titulo, timename) saveurl = "{0}tmp/{1}{2}.xls".format( base, titulo, timename) book = Workbook() font = Font() font.bold = True style = XFStyle() style.font = font style0 = XFStyle() style0.font = font alignment = Alignment() alignment.horz = Alignment.HORZ_CENTER style0.alignment = alignment pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 22 style0.pattern = pattern index = 0 sheet = book.add_sheet('promocoes') # sheet if int(id_relatorio) == 1 or int(id_relatorio) == 3: promocoes = self._getConteudo() if int(id_relatorio) == 1: campos_s1 = [{"nome": "Nome"}, {"email": "email"}, {"cpf": "CPF"}, {"quantidade de participacoes": u"Quantidade de participações"}, {"status":"status"}, {"bloqueio":"bloqueado"}] sheet.write_merge(0, 0, 0, 5, u"Relação usuário participação por promoção", style0) else: campos_s1 = [{"nome":"Nome"}, {"email":"email"}, {"cpf":"CPF"}, {"telefone":"Telefone"}, {"endereco":u"Endereço"}, {"data_hora":u"Hora participação"}] sheet.write_merge(0, 0, 0, 5, u"Dados de usuários por promoção", style0) total_geral = 0 count = 0 for i in promocoes: count_participacoes = self._getCountParticipacoes(i['id_conteudo']) count += count_participacoes index += 1 titulo = i['titulo'] sheet.write_merge(index, index, 0, 5, titulo.decode("latin1"), style0) index += 1 for j in range(len(campos_s1)): sheet.write(index, j, campos_s1[j].values()[0], style) sheet.col(j).width = 30 * 256 cont = 0 if int(id_relatorio) == 1: for y in self.execSql("select_usuarios_promocoes_all", id_conteudo=int(i['id_conteudo'])): sheet.write(index + 1, 0, self.dec(y['nome'])) sheet.write(index + 1, 1, self.dec(y['email'])) sheet.write(index + 1, 2, self.dec(y['cpf'])) sheet.write(index + 1, 3, self.dec(y['total'])) sheet.write(index + 1, 4, self.dec(y['status'])) if y['bloqueio']: bloqueio = 'Sim' else: bloqueio = 'Não' sheet.write(index + 1, 5, self.dec(bloqueio)) index += 1 cont += 1 if int(id_relatorio) == 3: for y in self.execSql("select_usuarios_promocoes_all2", id_conteudo=int(i['id_conteudo'])): endereco = "{0}, {1}, {2}, {3}, {4}". format(y['endereco'], y['numero'], y['bairro'], y['estado'], y['cep']) sheet.write(index + 1, 0, self.dec(y['nome'])) sheet.write(index + 1, 1, self.dec(y['email'])) sheet.write(index + 1, 2, self.dec(y['cpf'])) sheet.write(index + 1, 3, self.dec(y['telefone'])) sheet.write(index + 1, 4, self.dec(endereco)) sheet.write(index + 1, 5, y['dhora_participacao']) index += 1 cont += 1 if cont: index += 2 sheet.write(index, 0, 'Total', style) sheet.write(index, 1, cont) total_geral += cont index += 1 sheet.write(index, 0, u'Total Participações', style) sheet.write(index, 1, count_participacoes) else: index += 2 sheet.write(index, 0, 'Total', style) sheet.write(index, 1, cont) index += 2 index += 6 sheet.write_merge(index, index, 0, 1, "Total Geral", style0) index += 1 sheet.write(index, 0, "Total de Participantes", style) sheet.write(index, 1, self.dec(total_geral), style) index += 1 sheet.write(index, 0, u"Total de Participações Geral", style) sheet.write(index, 1, self.dec(count), style) else: if int(id_relatorio) == 4: cont = 0 titulo_promo = self._getConteudo(id_promocao)['titulo'] sheet.write_merge(0, 0, 0, 5, u"Relação de partcipantes da promoção " + titulo_promo.decode('latin1'), style0) campos_s1 = [{"nome":"Nome"}, {"email":"email"}, {"cpf":"CPF"}, {"telefone":"Telefone"}, {"endereco":u"Endereço"}, {"status":"Status"} ] saveplace = ("{0}/ns{1}/arquivos/tmp/{2}{3}.xls").format(str(settings.PATH_FILES), str(self.id_site), unicode(titulo + '_' + titulo_promo, errors='ignore'), timename) saveurl = "{0}tmp/{1}{2}.xls".format(base, unicode(titulo + '_' + titulo_promo, errors='ignore'), timename) index += 1 for j in range(len(campos_s1)): sheet.write(index, j, campos_s1[j].values()[0], style) sheet.col(j).width = 30 * 256 for i in self.execSql("select_usuarios_promocoes_all2", id_conteudo=int(id_promocao)): endereco = "{0}, {1}, {2}, {3}, {4}". format(i['endereco'], i['numero'], i['bairro'], i['estado'], i['cep']) sheet.write(index + 1, 0, self.dec(i['nome'])) sheet.write(index + 1, 1, self.dec(i['email'])) sheet.write(index + 1, 2, self.dec(i['cpf'])) sheet.write(index + 1, 3, self.dec(i['telefone'])) sheet.write(index + 1, 4, self.dec(endereco)) sheet.write(index + 1, 5, self.dec(i['status'])) index += 1 cont += 1 else: cont = 0 sheet.write_merge(0, 0, 0, 5, u"Relação de usuários contemplados", style0) campos_s1 = [{"nome":"Nome"}, {"email":"email"}, {"cpf":"CPF"}, {"telefone":"Telefone"}, {"endereco":u"Endereço"}, {"Contemplações":u"Contemplações"}] index += 1 for j in range(len(campos_s1)): sheet.write(index, j, campos_s1[j].values()[0], style) sheet.col(j).width = 30 * 256 for i in self.execSql("select_contemplados"): endereco = "{0}, {1}, {2}, {3}, {4}". format(i['endereco'], i['numero'], i['bairro'], i['estado'], i['cep']) sheet.write(index + 1, 0, self.dec(i['nome'])) sheet.write(index + 1, 1, self.dec(i['email'])) sheet.write(index + 1, 2, self.dec(i['cpf'])) sheet.write(index + 1, 3, self.dec(i['telefone'])) sheet.write(index + 1, 4, self.dec(endereco)) sheet.write(index + 1, 5, self.dec(i['total'])) index += 1 cont += 1 index += 6 sheet.write(index, 0, u"Total de usuários", style) sheet.write(index, 1, self.dec(cont), style) book.save(saveplace) return saveurl
def write_sheet(self, data, sheet_name, print_to_screen=False): '''Write a very simple table to a new sheet in a spreadsheet, Optionally, print the table to the screen''' # most cells al = Alignment() al.horz = Alignment.HORZ_RIGHT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.height = 9 * 20 # 9 pt style = XFStyle() style.font = font style.alignment = al # tops cells al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.height = 9 * 20 # 9 pt style_top = XFStyle() style_top.font = font style_top.alignment = al # left cells al = Alignment() al.horz = Alignment.HORZ_LEFT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.italic = True font.height = 9 * 20 # 9 pt style_left = XFStyle() style_left.font = font style_left.alignment = al ws = self.add_sheet(sheet_name) for i, row in enumerate(data): for j, cell in enumerate(row): borders = Borders() if i == 0: borders.top = 1 borders.bottom = 2 if i == len(row) - 1: borders.bottom = 1 if j == 0: borders.left = 1 borders.right = 1 if j == len(row) - 1: borders.right = 1 if j == 0: _style = style_left elif i == 0: _style = style_top else: _style = style _style.borders = borders # one of the libraries can get confused with plain strings if isinstance(cell, str): cell = unicode(cell, 'utf-8') ws.write(i + 1, j + 1, cell, _style) if print_to_screen: print print_table(data, sheet_name, bold=True)
def report_get(self, cr, uid, ids, context=None): this = self.browse(cr, uid, ids) #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height = 275 #Define the font attributes for header content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 220 align_content = Alignment() align_content.horz = Alignment.HORZ_LEFT borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_LEFT align.vert = Alignment.VERT_TOP align.wrap = Alignment.WRAP_AT_RIGHT #The text should be right aligned align1 = Alignment() align1.horz = Alignment.HORZ_RIGHT align1.vert = Alignment.VERT_TOP align1.wrap = Alignment.WRAP_AT_RIGHT #The content should be left aligned align2 = Alignment() align2.horz = Alignment.HORZ_LEFT align2.vert = Alignment.VERT_TOP align2.wrap = Alignment.WRAP_AT_RIGHT #The content should be right aligned align3 = Alignment() align3.horz = Alignment.HORZ_RIGHT align3.vert = Alignment.VERT_TOP align3.wrap = Alignment.WRAP_AT_RIGHT #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x17 #We set the backgroundcolour here pattern2 = Pattern() pattern2.pattern = Pattern.SOLID_PATTERN pattern2.pattern_fore_colour = 0xFF #We set the backgroundcolour here pattern3 = Pattern() pattern3.pattern = Pattern.SOLID_PATTERN pattern3.pattern_fore_colour = 0xFF #apply the above settings to the row(0) header style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align #apply the above settings to the row(1) header style_header1 = XFStyle() style_header1.font = fnt style_header1.pattern = pattern1 style_header1.borders = borders style_header1.alignment = align1 #apply the above settings to the content style_content_left = XFStyle() style_content_left.font = fnt style_content_left.pattern = pattern2 style_content_left.borders = borders style_content_left.alignment = align2 style_content_right = XFStyle() style_content_right.font = fnt style_content_right.pattern = pattern3 style_content_right.borders = borders style_content_right.alignment = align3 style_content = XFStyle() style_content.alignment = align_content style_content.font = content_fnt wb = Workbook() ws = wb.add_sheet("Sheet 1") ws.row(0).height = 3500 ws.write(0, 0, "(CO1)", style_header) ws.col(0).width = 2000 ws.write(0, 1, "Month & Year (CO2)", style_header) ws.col(1).width = 4500 ws.write(0, 2, "Buyer's TIN (CO3)", style_header) ws.col(2).width = 4500 ws.write(0, 3, "Buyer's Name (CO4)", style_header) ws.col(3).width = 10000 ws.write(0, 4, "Interstate Branch/Consignment Transfer (CO5)", style_header) ws.col(4).width = 4500 ws.write(0, 5, "Export Out Of India (CO6)", style_header) ws.col(5).width = 4500 ws.write(0, 6, "High Sea Sales (CO7)", style_header) ws.col(6).width = 4500 ws.write(0, 7, "ISS - Goods Type (CO8)", style_header) ws.col(7).width = 4500 ws.write(0, 8, "ISS - Form Type (I) (CO9)", style_header) ws.col(8).width = 4500 ws.write(0, 9, "ISS - Rate Of Tax (C1O)", style_header) ws.col(9).width = 4500 ws.write(0, 10, "ISS - Sales Price (Excluding CST) (C11)", style_header) ws.col(10).width = 4500 ws.write(0, 11, "ISS - Central Sales Tax (C12)", style_header) ws.col(11).width = 4500 ws.write(0, 12, "ISS - Total (C13)", style_header) ws.col(12).width = 4500 ws.write(0, 13, "Local Sale - Type Of Sale (C14)", style_header) ws.col(13).width = 4500 ws.write(0, 14, "Local Sale - Rate Of Tax (C15)", style_header) ws.col(14).width = 4500 ws.write(0, 15, "Local Sale - Sale Price (Excluding VAT) (C16)", style_header) ws.col(15).width = 4500 ws.write(0, 16, "Local Sale - OutPut Tax (C17)", style_header) ws.col(16).width = 4500 ws.write(0, 17, "Local Sale - Total (Including VAT) (C18)", style_header) ws.col(17).width = 4500 ws.row(1).height = 400 ws.write(1, 0, "0", style_header1) ws.write(1, 1, "0", style_header1) ws.write(1, 2, "0", style_header1) ws.write(1, 3, "", style_header1) ws.write(1, 4, "0.00", style_header1) ws.write(1, 5, "0.00", style_header1) ws.write(1, 6, "0.00", style_header1) ws.write(1, 7, "", style_header1) ws.write(1, 8, "", style_header1) ws.write(1, 9, "0.00", style_header1) ws.write(1, 10, "0.00", style_header1) ws.write(1, 11, "0.00", style_header1) ws.write(1, 12, "0.00", style_header1) ws.write(1, 13, "", style_header1) ws.write(1, 14, "0.00", style_header1) ws.write(1, 15, "0.00", style_header1) ws.write(1, 16, "0.00", style_header1) ws.write(1, 17, "0.00", style_header1) inv_obj = self.pool.get('account.invoice') line_obj = self.pool.get('account.invoice.line') row = 2 count = 1 value = 0.0 sum1 = sum2 = sum3 = sum4 = sum5 = sum6 = 0.0 period = [] for each in this: if each.start_period_id.name > each.end_period_id.name: raise osv.except_osv( _('Warning !'), _("Start period must be less than end period !")) cr.execute("select id from account_period where name between '" + str(each.start_period_id.name) + "' and '" + str(each.end_period_id.name) + "'") temp = cr.fetchall() for val in temp: if val: period.append(val[0]) inv_ids = inv_obj.search(cr, uid, [('type', '=', 'out_invoice'), ('state', 'not in', ['draft', 'cancel']), ('period_id', 'in', period)]) line_ids = line_obj.search(cr, uid, [('invoice_id', 'in', inv_ids)]) for line in line_obj.browse(cr, uid, line_ids): ws.row(row).height = 400 ws.write(row, 0, count, style_content_right) ws.write( row, 1, line.invoice_id.period_id and line.invoice_id.period_id.name or False, style_content_right) ws.write(row, 2, line.partner_id.tin_no or '', style_content_right) ws.write(row, 3, line.partner_id.name, style_content_left) for tax in line.invoice_line_tax_id: value = tax.amount * 100 if line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.city and line.invoice_id.address_invoice_id.city.lower( ) == 'new delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.city and line.invoice_id.address_invoice_id.city.lower( ) == 'delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.state_id and line.invoice_id.address_invoice_id.state_id.name.lower( ) == 'new delhi' or line.invoice_id.address_invoice_id and line.invoice_id.address_invoice_id.state_id and line.invoice_id.address_invoice_id.state_id.name.lower( ) == 'delhi': ws.write(row, 14, round(value, 2), style_content_right) ws.write(row, 15, round(line.price_subtotal, 2), style_content_right) sum1 += round(line.price_subtotal, 2) ws.write(row, 16, round(line.price_subtotal * tax.amount, 2), style_content_right) sum2 += round(line.price_subtotal * tax.amount, 2) ws.write( row, 17, round( line.price_subtotal + (line.price_subtotal * tax.amount), 2), style_content_right) sum3 += round( line.price_subtotal + (line.price_subtotal * tax.amount), 2) else: ws.write(row, 9, round(value, 2), style_content_right) ws.write(row, 10, round(line.price_subtotal, 2), style_content_right) sum4 += round(line.price_subtotal, 2) ws.write(row, 11, round(line.price_subtotal * tax.amount, 2), style_content_right) sum5 += round(line.price_subtotal * tax.amount, 2) ws.write( row, 12, round( line.price_subtotal + (line.price_subtotal * tax.amount), 2), style_content_right) sum6 += round( line.price_subtotal + (line.price_subtotal * tax.amount), 2) row += 1 count += 1 row += 2 ws.row(row).height = 400 ws.write(row, 0, "", style_header1) ws.write(row, 1, "", style_header1) ws.write(row, 2, "", style_header1) ws.write(row, 3, "", style_header1) ws.write(row, 4, "", style_header1) ws.write(row, 5, "", style_header1) ws.write(row, 6, "", style_header1) ws.write(row, 7, "", style_header1) ws.write(row, 8, "", style_header1) ws.write(row, 9, "", style_header1) ws.write(row, 10, sum4, style_header1) ws.write(row, 11, sum5, style_header1) ws.write(row, 12, sum6, style_header1) ws.write(row, 13, "", style_header1) ws.write(row, 14, "", style_header1) ws.write(row, 15, sum1, style_header1) ws.write(row, 16, sum2, style_header1) ws.write(row, 17, sum3, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'data': out, 'name': 'MARAnnexure2B.xls' }, context=context)
def ot_register_report(self, cr, uid, ids, context=None): fnt = Font() fnt.name = 'Ubuntu Medium' fnt.size = 16 fnt.style = 'Regular' #Define the font attributes for header content_fnt = Font() content_fnt.name = 'Ubuntu Medium' content_fnt.size = 16 content_fnt.style = 'Regular' align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x01 borders.right = 0x01 borders.top = 0x01 borders.bottom = 0x01 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Ubuntu Medium' fnt1.size = 10 fnt1.style = 'Regular' content_fnt1 = Font() content_fnt1.name = 'Ubuntu Medium' content_fnt1.style = 'Regular' align_content1 = Alignment() align_content1.horz = Alignment.HORZ_LEFT borders1 = Borders() borders1.left = 0x1 borders1.right = 0x1 borders1.top = 0x1 borders1.bottom = 0x1 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_TOP #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x1F #apply the above settings to the row(0) header style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 #Define the font attributes for Content fnt3 = Font() fnt3.name = 'Arial' fnt3.size = '10' fnt3.style = 'Regular' content_fnt3 = Font() content_fnt3.name = 'Arial' content_fnt3.style = 'Regular' align_content3 = Alignment() align_content3.horz = Alignment.HORZ_LEFT borders3 = Borders() borders3.left = 0x0 borders3.right = 0x0 borders3.top = 0x0 borders3.bottom = 0x0 #The text should be centrally aligned align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_TOP #We set the backgroundcolour here pattern3 = Pattern() #apply the above settings to the row(0) header style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 wb = Workbook() ws = wb.add_sheet('OT Register Report') this = self.browse(cr, uid, ids[0], context=context) year = this.month.year_id.id month = this.month.month emp_id = this.employee_id.id company_id = this.company_id.id ws.row(0).height = 300 ws.row(1).height = 300 ws.row(2).height = 300 ws.col(20).width = 6000 ws.write_merge( 0, 0, 4, 10, 'COMPANY : ' + this.company_id.name + ' ' + this.company_id.street, style_header) ws.write_merge(1, 1, 4, 10, ('SALARY REPORT FOR THE MONTH OF : ', this.month.name), style_header) ws.write(2, 0, 'PCard', style_header) ws.col(0).width = 4000 ws.write(2, 1, 'Employee Name', style_header) ws.col(1).width = 8000 ws.write(2, 2, 'Department Name', style_header) ws.col(2).width = 5000 ws.write(2, 3, 'Designation Name', style_header) ws.col(3).width = 5000 ws.write(2, 4, 'Total Working Day', style_header) ws.col(4).width = 5000 ws.write(2, 5, 'CL + EL', style_header) ws.col(5).width = 3000 ws.write(2, 6, 'Compensatory Leave', style_header) ws.col(6).width = 5000 ws.write(2, 7, 'Sick Leave', style_header) ws.col(7).width = 4000 ws.write(2, 8, 'Absent', style_header) ws.col(8).width = 4000 ws.write(2, 9, 'Shift Hours Worked', style_header) ws.col(9).width = 5000 ws.write(2, 10, 'Overtime Hours', style_header) ws.col(10).width = 5000 ws.write(2, 11, 'Total Working Hours', style_header) ws.col(11).width = 5000 ws.write(2, 12, 'Avg Weekly Working Hour', style_header) ws.col(12).width = 6000 ws.write(2, 13, 'Avg Daily Working Hour', style_header) ws.col(13).width = 6000 ws.write(2, 14, 'Total Monthly Gross', style_header) ws.col(14).width = 5000 ws.write(2, 15, 'Gross Salary Paid', style_header) ws.col(15).width = 5000 ws.write(2, 16, 'OT Salary', style_header) ws.col(16).width = 5000 ws.write(2, 17, 'Total Gross Salary', style_header) ws.col(17).width = 5000 i = 3 total_days = 0.0 total_cl_el = 0.0 total_compensatory = 0.0 total_sick = 0.0 total_absent = 0.0 total_shift_hours = 0.0 total_ot_hours = 0.0 total_working_hours = 0.0 total_weekly_working_hour = 0.0 total_daily_working_hour = 0.0 total_monthly_gross = 0.0 total_gross_salary_paid = 0.0 total_ot_salary = 0.0 total_gross_salary = 0.0 emp_obj = self.pool.get('hr.employee') if this.employee_id and this.company_id: list_ids = emp_obj.search(cr, uid, [('id', '=', emp_id), ('company_id', '=', company_id), ('type', '=', 'Employee'), ('active', '=', True)]) elif this.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('type', '=', 'Employee'), ('active', '=', True)]) list_ids1 = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('type', '=', 'Employee'), ('active', '=', False)]) list_ids = list_ids + list_ids1 elif this.employee_id: list_ids = emp_obj.search(cr, uid, [('id', '=', emp_id), ('type', '=', 'Employee'), ('active', '=', True)]) if len(list_ids) == 1: query ="select spl.sinid,spl.employee_name,spl.department_name,spl.job_name,(spl.work_day+spl.factory_work),(spl.casual_leave+spl.earned_leave),spl.compensatory_leave,spl.sick_leave,(spl.month_days-(spl.work_day+spl.factory_work+spl.casual_leave+spl.earned_leave+spl.compensatory_leave+spl.sick_leave+spl.holiday_leave+spl.week_leave)),"\ "((spl.work_day+spl.factory_work) * 8),(spl.over_time + spl.sun_over_time),(((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time)),"\ "spl.month_days,"\ "round(((((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time))/(spl.work_day+spl.factory_work)),2) ,(spl.basic + spl.other_salary),(spl.days_amount+spl.other_salary_amount),spl.total_overtime_amount,"\ "(spl.days_amount+spl.other_salary_amount+spl.total_overtime_amount) from salary_payment_line as spl where spl.employee_id = '"+str(list_ids[0])+"' and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' order by spl.sinid" cr.execute(query) temp = cr.fetchall() else: query ="select spl.sinid,spl.employee_name,spl.department_name,spl.job_name,(spl.work_day+spl.factory_work),(spl.casual_leave+spl.earned_leave),spl.compensatory_leave,spl.sick_leave,(spl.month_days-(spl.work_day+spl.factory_work+spl.casual_leave+spl.earned_leave+spl.compensatory_leave+spl.sick_leave+spl.holiday_leave+spl.week_leave)),"\ "((spl.work_day+spl.factory_work) * 8),(spl.over_time + spl.sun_over_time),(((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time)),"\ "spl.month_days,"\ "round(((((spl.work_day+spl.factory_work) * 8)+(spl.over_time + spl.sun_over_time))/(spl.work_day+spl.factory_work)),2) ,(spl.basic + spl.other_salary),(spl.days_amount+spl.other_salary_amount),spl.total_overtime_amount,"\ "(spl.days_amount+spl.other_salary_amount+spl.total_overtime_amount) from salary_payment_line as spl where spl.employee_id in "+str(tuple(list_ids))+" and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' order by spl.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) for val in temp: if val[4] >= 7: working_hour = round((val[11] / (val[12] - val[8])) * 7, 2) else: working_hour = 0.00 total_days += val[4] total_cl_el += val[5] total_compensatory += val[6] total_sick += val[7] total_absent += val[8] total_shift_hours += val[9] total_ot_hours += val[10] total_working_hours += val[11] total_daily_working_hour += val[13] total_monthly_gross += val[14] total_gross_salary_paid += val[15] total_ot_salary += val[16] total_gross_salary += val[17] total_weekly_working_hour += working_hour ws.write(i, 0, val[0], style_header3) ws.write(i, 1, val[1], style_header3) ws.write(i, 2, val[2], style_header3) ws.write(i, 3, val[3], style_header3) ws.write(i, 4, val[4], style_header3) ws.write(i, 5, val[5], style_header3) ws.write(i, 6, val[6], style_header3) ws.write(i, 7, val[7], style_header3) ws.write(i, 8, val[8], style_header3) ws.write(i, 9, val[9], style_header3) ws.write(i, 10, val[10], style_header3) ws.write(i, 11, val[11], style_header3) ws.write(i, 12, working_hour, style_header3) ws.write(i, 13, val[13], style_header3) ws.write(i, 14, val[14], style_header3) ws.write(i, 15, val[15], style_header3) ws.write(i, 16, val[16], style_header3) ws.write(i, 17, val[17], style_header3) i = i + 1 ws.write(i, 0, '', style_header3) ws.write(i, 1, '', style_header3) ws.write(i, 2, '', style_header3) ws.write(i, 3, 'TOTAL', style_header1) ws.write(i, 4, total_days, style_header1) ws.write(i, 5, total_cl_el, style_header1) ws.write(i, 6, total_compensatory, style_header1) ws.write(i, 7, total_sick, style_header1) ws.write(i, 8, total_absent, style_header1) ws.write(i, 9, total_shift_hours, style_header1) ws.write(i, 10, total_ot_hours, style_header1) ws.write(i, 11, total_working_hours, style_header1) ws.write(i, 12, total_weekly_working_hour, style_header1) ws.write(i, 13, total_daily_working_hour, style_header1) ws.write(i, 14, total_monthly_gross, style_header1) ws.write(i, 15, total_gross_salary_paid, style_header1) ws.write(i, 16, total_ot_salary, style_header1) ws.write(i, 17, total_gross_salary, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) ot_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'OT Register Report.xls' }, context=context) return ot_report
def print_report(self, cr, uid, ids, context=None): wb = Workbook() ws = wb.add_sheet('Earn Leave Report') fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x17 style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 250 fnt2.bold = False align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x09 style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 275 fnt3.bold = False align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x02 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x09 style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x16 style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 275 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 ws.row(0).height = 1000 ws.col(0).width = 4000 ws.col(1).width = 5000 ws.col(2).width = 4000 ws.col(3).width = 5000 ws.col(4).width = 5000 ws.col(5).width = 6500 ws.col(6).width = 4000 ws.col(7).width = 4000 ws.col(8).width = 4000 ws.write(0, 0, 'Employee ID', style_header) ws.write(0, 1, 'Employee Code', style_header) ws.write(0, 2, 'Earn Date', style_header) ws.write(0, 3, 'Total Work Day', style_header) ws.write(0, 4, 'Allocation EL', style_header) ws.write(0, 5, 'Opening Earn Leave', style_header) ws.write(0, 6, 'Total EL Paid', style_header) ws.write(0, 7, 'Balance EL', style_header) ws.write(0, 8, 'Import EL', style_header) this = self.browse(cr, uid, ids[0], context=context) company_id = this.company_id.id employee_id = this.employee_id.id emp_obj = self.pool.get('hr.employee') year = this.month.year_id.id balance1 = 0 balance = 0 import_el = 0 i = 1 if this.company_id and this.employee_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('id', '=', employee_id), ('active', '=', True), ('type', '=', 'Employee')]) elif this.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', company_id), ('active', '=', True), ('type', '=', 'Employee')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if len(list_ids) == 1: query = "select spl.sinid,sum(spl.work_day),hr.earn_date,sum(spl.earned_leave),hr.earn_open,hr.id from salary_payment_line as spl left join hr_employee as hr on spl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id where spl.employee_id = '" + str( list_ids[0] ) + "' and spl.year_id='" + str( year ) + "' and rr.active = 'True' group by spl.sinid,hr.earn_date,hr.earn_open,hr.id order by spl.sinid" cr.execute(query) temp = cr.fetchall() else: query = "select spl.sinid,sum(spl.work_day),hr.earn_date,sum(spl.earned_leave),hr.earn_open,hr.id from salary_payment_line as spl left join hr_employee as hr on spl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id where spl.employee_id in " + str( tuple(list_ids) ) + " and spl.year_id='" + str( year ) + "' and rr.active = 'True' group by spl.sinid,hr.earn_date,hr.earn_open,hr.id order by spl.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) for val in temp: day = format((val[1] / 20), '.2f') b = str(day) c = b.split('.') d = c[0] e = c[1][0:2] if int(e) < 50: s = d + '.' + str(0) work_day = float(s) else: s = d + '.' + e t = float(s) work_day = math.ceil(t) if val[4] != None: balance1 = (work_day + val[4] - val[3]) import_el = work_day + val[4] else: balance1 = (work_day - val[3]) import_el = work_day if balance1 >= 30: balance = 30 else: balance = balance1 ws.row(i).height = 500 ws.write(i, 0, val[5], style_header2) ws.write(i, 1, val[0], style_header2) ws.write(i, 2, val[2], style_header2) ws.write(i, 3, val[1], style_header2) ws.write(i, 4, work_day, style_header2) ws.write(i, 5, val[4], style_header2) ws.write(i, 6, val[3], style_header2) ws.write(i, 7, balance, style_header2) ws.write(i, 8, import_el, style_header2) i = i + 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) pf_upload_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Earn Leave Report.xls' }, context=context) return pf_upload_report
def generate_report(root, filename, attend_staff, absent_staff, meeting_direct, record_staff, reports, issues): book = Workbook(encoding='utf-8') sheet1 = book.add_sheet('Fota Security Knox Week Report') line_counter = 0 today = datetime.date.today().strftime('%Y/%m/%d') # header head_str = [ 'Meeting Name', 'SW 3 Group Part 5 Fota/ Security/Knox/Encription TG WeekReport', '', '', 'Meeting Date', today, 'Meeting Address', '8F Meeting Room', '', '', 'Attend Staff', attend_staff, 'Absent Staff', absent_staff, '', '', 'Meeting Direct', meeting_direct, 'Record Staff', record_staff, ] for i in range(len(head_str) / 2): sheet1.row(i).write(0, head_str[2 * i]) sheet1.row(i).write(1, head_str[2 * i + 1]) line_counter = len(head_str) / 2 + 1 # font bold font = Font() font.bold = True # pattern yellow pattern_yellow = Pattern() pattern_yellow.pattern = Pattern.SOLID_PATTERN pattern_yellow.pattern_fore_colour = 0x0D # yellow # pattern gray pattern_gray = Pattern() pattern_gray.pattern = Pattern.SOLID_PATTERN pattern_gray.pattern_fore_colour = 0x17 # gray # borders thin borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN # alignment horizontal center alig_hc = Alignment() # alig.horizontal = Alignment.HORZ_CENTER #no effect, why? f**k!!! alig_hc.horz = Alignment.HORZ_CENTER # title style style_title = XFStyle() style_title.font = font style_title.pattern = pattern_yellow style_title.alignment = alig_hc sheet1.write_merge( line_counter, line_counter, 0, 3, 'summary', style_title) line_counter += 1 # table header style sytle_tb_header = XFStyle() sytle_tb_header.font = font sytle_tb_header.pattern = pattern_gray sytle_tb_header.borders = borders sytle_tb_header.alignment = alig_hc sheet1.row(line_counter).write(0, 'Member', sytle_tb_header) sheet1.row(line_counter).write(1, 'Week Jobs', sytle_tb_header) sheet1.row(line_counter).write(2, 'Risk', sytle_tb_header) sheet1.row(line_counter).write(3, 'Next Week Plan', sytle_tb_header) line_counter += 1 # content # for i in range(line_counter,11+line_counter): i = line_counter for report in reports: # alignment alig = Alignment() alig.horz = Alignment.HORZ_CENTER alig.vert = Alignment.VERT_CENTER alig.wrap = 1 # alignment2 alig2 = Alignment() alig2.vert = Alignment.VERT_CENTER alig2.wrap = 1 # borders borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN # colors pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x2F style_content = XFStyle() style_content.alignment = alig style_content.borders = borders style_content2 = XFStyle() style_content2.alignment = alig2 style_content2.borders = borders style_content2.pattern = pattern style_content3 = XFStyle() style_content3.alignment = alig2 style_content3.borders = borders sheet1.row(i).write(0, report.who, style_content) sheet1.row(i).write(1, report.job, style_content2) sheet1.row(i).write(2, report.risk, style_content2) sheet1.row(i).write(3, report.plan, style_content3) sheet1.row(i).height_mismatch = True sheet1.row(i).height = 1500 i += 1 line_counter += len(reports) line_counter += 1 sheet1.write_merge( line_counter, line_counter, 0, 3, 'Main Issues List', style_title) line_counter += 1 sheet1.row(line_counter).write(0, 'Items', sytle_tb_header) sheet1.write_merge( line_counter, line_counter, 1, 2, 'Deatail', sytle_tb_header) sheet1.row(line_counter).write(3, 'Status', sytle_tb_header) line_counter += 1 sheet1.col(0).width = 256 * 16 sheet1.col(1).width = 256 * 50 sheet1.col(2).width = 256 * 31 sheet1.col(3).width = 256 * 46 book.save(root + '/' + filename)
def salary_deduction_category(self, cr, uid, ids, context=None): #============ ========= =============== =========================# fnt = Font() fnt.name = 'Ubuntu Medium' fnt.size = 16 fnt.style = 'Regular' #============ ======== ============= ============================# #Define the font attributes for header content_fnt = Font() content_fnt.name = 'Ubuntu Medium' content_fnt.size = 16 content_fnt.style = 'Regular' align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x01 borders.right = 0x01 borders.top = 0x01 borders.bottom = 0x01 #==============================================================# #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Ubuntu Medium' fnt1.size = 10 fnt1.style = 'Regular' content_fnt1 = Font() content_fnt1.name = 'Ubuntu Medium' content_fnt1.style = 'Regular' align_content1 = Alignment() align_content1.horz = Alignment.HORZ_LEFT borders1 = Borders() borders1.left = 0x1 borders1.right = 0x1 borders1.top = 0x1 borders1.bottom = 0x1 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_TOP #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x16 #apply the above settings to the row(0) header style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 #Define the font attributes for Content fnt3 = Font() fnt3.name = 'Arial' fnt3.size = '10' fnt3.style = 'Regular' content_fnt3 = Font() content_fnt3.name = 'Arial' content_fnt3.style = 'Regular' align_content3 = Alignment() align_content3.horz = Alignment.HORZ_LEFT borders3 = Borders() borders3.left = 0x0 borders3.right = 0x0 borders3.top = 0x0 borders3.bottom = 0x0 #The text should be centrally aligned align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_TOP #We set the backgroundcolour here pattern3 = Pattern() #apply the above settings to the row(0) header style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt5 = Font() fnt5.name = 'Arial' fnt.size = 10 content_fnt5 = Font() content_fnt5.name = 'Arial' align_content5 = Alignment() # align_content5.horz= Alignment.HORZ_JUSTIFIED borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() # align5.horz = Alignment.HORZ_JUSTIFIED align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 fnt6 = Font() fnt6.name = 'Arial' fnt6.height = 300 fnt6.bold = True align_content6 = Alignment() align_content6.horz = Alignment.HORZ_CENTER borders6 = Borders() borders6.left = 0x02 borders6.right = 0x02 borders6.top = 0x02 borders6.bottom = 0x02 align6 = Alignment() align6.horz = Alignment.HORZ_CENTER align6.vert = Alignment.VERT_CENTER pattern6 = Pattern() pattern6.pattern6 = Pattern.SOLID_PATTERN pattern6.pattern6_fore_colour = 0x16 style_header6 = XFStyle() style_header6.font = fnt6 style_header6.pattern = pattern6 style_header6.borders = borders6 style_header6.alignment = align6 wb = Workbook() ws = wb.add_sheet('Category Wise Deduction') this = self.browse(cr, uid, ids[0], context=context) year = this.month.year_id.id month = this.month.month emp_id = this.employee_id.id company_id = this.company_id.id employee_type = this.employee_type employment_type = this.employment_type ws.row(0).height = 300 ws.row(1).height = 300 ws.row(2).height = 300 ws.col(1).width = 8000 ws.col(2).width = 6000 ws.col(3).width = 6000 ws.col(5).width = 4000 ws.col(6).width = 4000 ws.col(7).width = 4000 ws.col(8).width = 4000 ws.col(9).width = 5000 ws.col(10).width = 5000 ws.write_merge( 0, 0, 0, 11, 'COMPANY : ' + this.company_id.name + ' ' + this.company_id.street, style_header) ws.write_merge(1, 1, 0, 11, ('SALARY CHART FOR THE MONTH OF :', this.month.name), style_header) ws.write_merge(2, 2, 4, 5, 'Income', style_header1) ws.write_merge(2, 2, 6, 10, 'Deductions', style_header1) ws.write(2, 11, 'Net Amount', style_header1) ws.write(3, 0, 'PCard', style_header) ws.write(3, 1, 'Employee Name', style_header) ws.write(3, 2, 'Department Name', style_header) ws.write(3, 3, 'Designation Name', style_header) ws.write(3, 4, 'Total Salary', style_header) ws.write(3, 5, 'OT Amount', style_header) ws.write(3, 6, 'PF Deducted', style_header) ws.write(3, 7, 'TDS Deducted', style_header) ws.write(3, 8, 'ESI Deducted', style_header) ws.write(3, 9, 'Professional Tax', style_header) ws.write(3, 10, 'ADVANCE Deducted', style_header) ws.write(3, 11, 'Net Amount', style_header) i = 4 total_epf = 0.0 total_tds = 0.0 total_esi = 0.0 total_kharcha = 0.0 total_ot_salary = 0.0 total_a_gross = 0.0 total_grand_payment = 0.0 total_professional_tax = 0.0 list_val = [] list_ids = [] emp_obj = self.pool.get('hr.employee') if this.employee_id and this.employee_type: ws.write_merge(2, 2, 1, 2, ('Employee Type : ', this.employee_type), style_header1) list_ids = emp_obj.search(cr, uid, [('id', '=', emp_id), ('active', '=', True), ('employee_type', '=', employee_type), ('type', '=', 'Employee')]) elif this.employee_id and this.employment_type: ws.write_merge(2, 2, 1, 2, ('Employment Type : ', this.employment_type), style_header1) list_ids = emp_obj.search( cr, uid, [('id', '=', emp_id), ('active', '=', True), ('employment_type', '=', employment_type), ('type', '=', 'Employee')]) elif this.company_ids and this.employee_type: ws.write_merge(2, 2, 1, 2, ('Employee Type : ', this.employee_type), style_header1) for val in this.company_ids: list_val = emp_obj.search( cr, uid, [('active', '=', True), ('company_id', '=', val.id), ('employee_type', '=', employee_type), ('type', '=', 'Employee')]) list_ids = list_val + list_ids elif this.company_ids and this.employment_type: ws.write_merge(2, 2, 1, 2, ('Employment Type : ', this.employment_type), style_header1) for val in this.company_ids: list_val = emp_obj.search( cr, uid, [('active', '=', True), ('company_id', '=', val.id), ('employment_type', '=', employment_type), ('type', '=', 'Employee')]) list_ids = list_val + list_ids elif not this.employee_type and not this.employment_type: raise osv.except_osv( ('Warning !'), ("Please Select Employee or Employment Type !!!")) elif this.employee_type: ws.write_merge(2, 2, 1, 2, ('Employee Type : ', this.employee_type), style_header1) list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('employee_type', '=', employee_type), ('type', '=', 'Employee')]) elif this.employment_type: ws.write_merge(2, 2, 1, 2, ('Employment Type : ', this.employment_type), style_header1) list_ids = emp_obj.search( cr, uid, [('active', '=', True), ('employment_type', '=', employment_type), ('type', '=', 'Employee')]) else: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('type', '=', 'Employee')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if len(list_ids) == 1: query ="select spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,sum(spl.overtime_amount+spl.sun_overtime_amount),sum(spl.days_amount+spl.other_salary_amount),spl.grand_total,spl.pro_tax_amt"\ " from salary_payment_line as spl where spl.employee_id = '"+str(list_ids[0])+"' and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' group by "\ " spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,spl.grand_total,spl.pro_tax_amt order by spl.sinid " cr.execute(query) temp = cr.fetchall() else: query ="select spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,sum(spl.overtime_amount+spl.sun_overtime_amount),sum(spl.days_amount+spl.other_salary_amount),spl.grand_total,spl.pro_tax_amt"\ " from salary_payment_line as spl where spl.employee_id in "+str(tuple(list_ids))+" and spl.month='"+str(month)+"' and spl.year_id='"+str(year)+"' group by " \ " spl.employee_name,spl.sinid,spl.department_name,spl.job_name,spl.epf,spl.tds,spl.esi,spl.kharcha,spl.grand_total,spl.pro_tax_amt order by spl.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) for val in temp: total_epf += val[4] total_tds += val[5] total_esi += val[6] total_kharcha += val[7] total_ot_salary += val[8] total_a_gross += val[9] total_grand_payment += val[10] total_professional_tax += val[11] ws.write(i, 0, (val[1]), style_header3) ws.write(i, 1, (val[0]), style_header3) ws.write(i, 2, (val[2]), style_header3) ws.write(i, 3, (val[3]), style_header3) ws.write(i, 4, (val[9]), style_header3) ws.write(i, 5, (val[8]), style_header3) ws.write(i, 6, (val[4]), style_header3) ws.write(i, 7, (val[5]), style_header3) ws.write(i, 8, (val[6]), style_header3) ws.write(i, 9, (val[11]), style_header3) ws.write(i, 10, (val[7]), style_header3) ws.write(i, 11, (val[10]), style_header3) i = i + 1 ws.write(i, 3, 'TOTAL', style_header5) ws.write(i, 4, total_a_gross, style_header5) ws.write(i, 5, total_ot_salary, style_header5) ws.write(i, 6, total_epf, style_header5) ws.write(i, 7, total_tds, style_header5) ws.write(i, 8, total_esi, style_header5) ws.write(i, 9, total_professional_tax, style_header5) ws.write(i, 10, total_kharcha, style_header5) ws.write(i, 11, total_grand_payment, style_header5) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) ot_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Salary CHART.xls' }, context=context) return ot_report
def neem_trainee_stipend_register_report(self, cr, uid, ids, context=None): #Define the font attributes for header fnt = Font() fnt.name = 'Ubuntu Medium' fnt.size = 15 fnt.Style = 'Regular' content_fnt = Font() content_fnt.name = 'Ubuntu Medium' content_fnt.size = 15 content_fnt.Style = 'Regular' align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_TOP #We set the backgroundcolour here pattern = Pattern() #apply the above settings to the row(0) header style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.size = 15 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x00 borders1.right = 0x00 borders1.top = 0x00 borders1.bottom = 0x00 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x1F style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 #Define the font attributes for header fnt2 = Font() fnt2.name = 'Ubuntu Medium' fnt2.size = 10 fnt2.style = 'Regular' content_fnt2 = Font() content_fnt2.name = 'Ubuntu Medium' content_fnt2.style = 'Regular' align_content2 = Alignment() align_content2.horz = Alignment.HORZ_LEFT borders2 = Borders() borders2.left = 0x0 borders2.right = 0x0 borders2.top = 0x0 borders2.bottom = 0x0 #The text should be centrally aligned align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_TOP #We set the backgroundcolour here pattern2 = Pattern() #apply the above settings to the row(0) header style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 wb = Workbook() ws = wb.add_sheet('Neem Trainee Stipend Register') # style_pass = xlwt.easyxf('pattern: pattern solid, Font.name:Arial ,Bold: True, fore_colour black;') # ws.row(0).height=500 ws.col(0).width = 1800 ws.col(1).width = 6000 ws.col(2).width = 6000 ws.col(3).width = 6000 ws.col(4).width = 6000 ws.col(5).width = 7200 ws.col(6).width = 6000 ws.col(7).width = 7200 ws.col(8).width = 6000 ws.col(9).width = 6000 ws.col(10).width = 2200 ws.col(11).width = 2200 ws.col(12).width = 2200 ws.col(13).width = 2200 ws.col(14).width = 2200 ws.col(15).width = 2200 ws.col(16).width = 2200 ws.col(17).width = 2500 ws.col(18).width = 2500 ws.col(19).width = 3200 ws.col(20).width = 2500 ws.col(21).width = 2500 ws.col(22).width = 2500 ws.col(23).width = 3500 i = 3 this = self.browse(cr, uid, ids) from_date = this.from_date from_date1 = datetime.strptime(from_date, "%Y-%m-%d") from_date1 = from_date1.strftime('%d-%m-%Y') till_date = this.till_date till_date1 = datetime.strptime(till_date, "%Y-%m-%d") till_date1 = till_date1.strftime('%d-%m-%Y') partner_id = this.partner_id emp_id = this.employee_id ws.write_merge( 0, 1, 8, 12, 'NEEM TRAINEE STIPEND REGISTER FOR' + this.month_id.name + ' ( ' + from_date1 + ' to ' + till_date1 + ' ) ', style_header) ws.write(i, 0, 'S.No.', style_header1) ws.write(i, 1, 'Emp Code', style_header1) ws.write(i, 2, 'Punch Code', style_header1) ws.write(i, 3, 'Emp Name', style_header1) ws.write(i, 4, 'Department', style_header1) ws.write(i, 5, 'Designation', style_header1) ws.write(i, 6, 'DOJ', style_header1) ws.write(i, 7, 'Bank Name', style_header1) ws.write(i, 8, 'Bank Acc. Number', style_header1) ws.write(i, 9, 'IFS Code', style_header1) ws.write(i, 10, 'Stipend \n Rate', style_header1) ws.write(i, 11, 'Total \n Days', style_header1) ws.write(i, 12, 'Working \n Days', style_header1) ws.write(i, 13, 'Casual \n Leave', style_header1) ws.write(i, 14, 'Earn \n Leave', style_header1) ws.write(i, 15, 'Holiday', style_header1) ws.write(i, 16, 'Absent \n Days', style_header1) ws.write(i, 17, 'Payable \n Days', style_header1) ws.write(i, 18, 'Stipend \n Payable', style_header1) ws.write(i, 19, 'Performance \n Incentive', style_header1) ws.write(i, 20, 'Total \n Earnings', style_header1) ws.write(i, 21, 'Deduction \n If Any', style_header1) ws.write(i, 22, 'Net \n Payable', style_header1) ws.write(i, 23, 'Signature \n Thumb \n Impression', style_header1) ws.row(23).height = 500 ws.row(22).height = 500 ws.row(21).height = 500 ws.row(20).height = 500 ws.row(19).height = 500 ws.row(18).height = 500 ws.row(17).height = 500 ws.row(16).height = 500 ws.row(15).height = 500 ws.row(14).height = 500 ws.row(13).height = 500 ws.row(12).height = 500 ws.row(11).height = 500 ws.row(10).height = 500 ws.row(9).height = 500 ws.row(8).height = 500 ws.row(7).height = 500 ws.row(6).height = 500 ws.row(4).height = 500 ws.row(4).height = 500 ws.row(3).height = 500 ws.row(2).height = 500 ws.row(1).height = 500 ws.row(0).height = 500 i += 1 emp_obj = self.pool.get('hr.employee') if this.employee_id: list_ids = emp_obj.search(cr, uid, [('partner_id', '=', partner_id.id), ('id', '=', emp_id.id), ('active', '=', True), ('doj', '<=', till_date), ('employment_type', '=', 'Trainee')]) else: list_ids = emp_obj.search(cr, uid, [('partner_id', '=', partner_id.id), ('active', '=', True), ('doj', '<=', till_date), ('employment_type', '=', 'Trainee')]) if not list_ids: raise osv.except_osv(_('Warning !'), _("No Record Found !!!")) i1 = 0 for emp in list_ids: i1 += 1 bank_name = '' acc_no = '' ifs_code = '' month_days = work_day = casual_leave = earned_leave = holiday = absent_days = days = stipend_pay = other_earns = total_amt = deduction = net_pay = 0.0 emp_browse = emp_obj.browse(cr, uid, emp) sinid = emp_browse.sinid name = emp_browse.name dept = emp_browse.department_id.name desg = emp_browse.job_id.name doj = datetime.strptime(emp_browse.doj, "%Y-%m-%d") doj = doj.strftime('%d-%m-%Y') salary = emp_browse.total_salary punch_code = emp_browse.paycode query1 = "select bank_name,id_no,ifsc_code from verification where employee_id='"+str(emp)+"' " \ "and proof_id = 'Bank_ Account_ No' " cr.execute(query1) temp1 = cr.fetchall() bank_name = '' if temp1: bank_name = temp1[0][0] acc_no = temp1[0][1] ifs_code = self.pool.get('res.bank').browse( cr, uid, temp1[0][2]).bic if bank_name: paid_msg = 'Paid In Bank' else: paid_msg = '' ws.write(i, 0, i1, style_header) ws.write(i, 1, sinid, style_header) ws.write(i, 2, punch_code, style_header) ws.write(i, 3, name, style_header) ws.write(i, 4, dept, style_header) ws.write(i, 5, desg, style_header) ws.write(i, 6, doj, style_header) ws.write(i, 7, bank_name, style_header) ws.write(i, 8, acc_no, style_header) ws.write(i, 9, ifs_code, style_header) ws.write(i, 10, salary, style_header) sal_line_search = self.pool.get('salary.payment.line').search( cr, uid, [('employee_id', '=', emp), ('month', '=', this.month_id.month), ('year_id', '=', this.month_id.year_id.id)]) if sal_line_search: sal_line = self.pool.get('salary.payment.line').browse( cr, uid, sal_line_search[0]) month_days = sal_line.month_days work_day = sal_line.work_day + sal_line.factory_work casual_leave = sal_line.casual_leave earned_leave = sal_line.earned_leave holiday = sal_line.week_leave + sal_line.holiday_leave absent_days = sal_line.month_days - sal_line.days days = sal_line.days stipend_pay = sal_line.days_amount + sal_line.other_salary_amount other_earns = sal_line.overtime_amount + sal_line.sun_overtime_amount total_amt = stipend_pay + other_earns deduction = sal_line.kharcha + sal_line.loan net_pay = total_amt - deduction ws.write(i, 11, month_days, style_header) ws.write(i, 12, work_day, style_header) ws.write(i, 13, casual_leave, style_header) ws.write(i, 14, earned_leave, style_header) ws.write(i, 15, holiday, style_header) ws.write(i, 16, absent_days, style_header) ws.write(i, 17, days, style_header) ws.write(i, 18, stipend_pay, style_header) ws.write(i, 19, other_earns, style_header) ws.write(i, 20, total_amt, style_header) ws.write(i, 21, deduction, style_header) ws.write(i, 22, net_pay, style_header) ws.write(i, 23, paid_msg, style_header) ws.row(i).height = 500 i += 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Neem Trainee Stipend Register.xls' }, context=context)
def salary_payment_report(self,cr,uid,ids,context=None): #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.size=16 fnt.style= 'Regular' #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.size=16 content_fnt.style= 'Regular' align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x01 borders.right = 0x01 borders.top = 0x01 borders.bottom = 0x01 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt3 = Font() fnt3.name = 'Arial' fnt3.size=16 fnt3.style= 'Regular' #Define the font attributes for header content_fnt3 = Font() content_fnt3.name ='Arial' content_fnt3.size=16 content_fnt3.style= 'Regular' align_content3 = Alignment() align_content3.horz= Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x01 borders3.right = 0x01 borders3.top = 0x01 borders3.bottom = 0x01 #The text should be centrally aligned align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern3 = Pattern() #apply the above settings to the row(0) header style_header3= XFStyle() style_header3.font= fnt3 style_header3.pattern= pattern3 style_header3.borders = borders3 style_header3.alignment=align3 #Define the font attributes for header fnt4 = Font() fnt4.name = 'Arial' #Define the font attributes for header content_fnt4 = Font() content_fnt4.name ='Arial' align_content4 = Alignment() align_content4.horz= Alignment.HORZ_LEFT borders4 = Borders() borders4.left = 0x01 borders4.right = 0x01 borders4.top = 0x01 borders4.bottom = 0x01 #The text should be centrally aligned align4 = Alignment() align4.horz = Alignment.HORZ_LEFT align4.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern4 = Pattern() #apply the above settings to the row(0) header style_header4= XFStyle() style_header4.font= fnt4 style_header4.pattern= pattern4 style_header4.borders = borders4 style_header4.alignment=align4 #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.size=10 fnt1.Style= 'Regular' #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.size=10 content_fnt1.Style= 'Regular' align_content1 = Alignment() align_content1.horz= Alignment.HORZ_RIGHT borders1 = Borders() borders1.left = 0x01 borders1.right = 0x01 borders1.top = 0x01 borders1.bottom = 0x01 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_RIGHT align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 #Define the font attributes for header fnt2 = Font() fnt2.name = 'Arial' fnt2.size=16 fnt2.Style= 'Regular' #Define the font attributes for header content_fnt2 = Font() content_fnt2.name ='Arial' content_fnt2.size=16 content_fnt2.Style= 'Regular' align_content2 = Alignment() align_content2.horz= Alignment.HORZ_RIGHT borders2 = Borders() borders2.left = 0x01 borders2.right = 0x01 borders2.top = 0x01 borders2.bottom = 0x01 #The text should be centrally aligned align2 = Alignment() align2.horz = Alignment.HORZ_RIGHT align2.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern2 = Pattern() #apply the above settings to the row(0) header style_header2= XFStyle() style_header2.font= fnt2 style_header2.pattern= pattern style_header2.borders = borders2 style_header2.alignment=align2 wb = Workbook() ws = wb.add_sheet('Salary Report') this=self.browse(cr,uid,ids[0],context=context) ws.row(0).height=300 ws.row(1).height=300 ws.row(2).height=300 # ws.col(0).width = 5000 # ws.col(0).width = 5000 # ws.col(0).width = 5000 # ws.col(0).width = 5000 ws.write_merge(0,0,0,5, 'SALARY REPORT',style_header) ws.write_merge(1,1,0,5, ('DEPARTMENT : ',this.department_id.name),style_header) ws.write_merge(2,2,0,1, 'Month',style_header) ws.write_merge(2,2,2,3, 'Salary Amount',style_header) ws.write_merge(2,2,4,5, 'Remark',style_header) l=[] lst=[] for val in this.month_ids: i = 3 query="""select hy.name,spl.month, sum(spl.total_amount) from salary_payment_line as spl left join holiday_year as hy on spl.year_id = hy.id where month = '"""+str(val.month)+"""' and year_id = '"""+str(val.year_id.id)+"""' and curr_department = '"""+str(this.department_id.id)+"""' and salary_type='Salary' group by spl.month, hy.name""" cr.execute(query) temp = cr.fetchall() for year,month, total_amount in temp: t=() mon = int(month) t = (year,mon, total_amount) lst.append(t) total = 0.0 lst.sort() for year,month, total_amount in lst: if month == 1: month1 = 'January' elif month == 2: month1 = 'February' elif month == 3: month1 = 'March' elif month == 4: month1 = 'April' elif month == 5: month1 = 'May' elif month == 6: month1 = 'June' elif month == 7: month1 = 'July' elif month == 8: month1 = 'August' elif month == 9: month1 = 'September' elif month == 10: month1 = 'October' elif month == 11: month1 = 'November' else: month1 = 'December' total += total_amount ws.write_merge(i,i,0,1, (month1,' ',year),style_header4) ws.write_merge(i,i,2,3, round(total_amount,2),style_header1) ws.write_merge(i,i,4,5, ' ',style_header3) i += 1 ws.write_merge(i,i,0,1, 'Total Amount',style_header2) ws.write_merge(i,i,2,3, round(total,2),style_header2) ws.write_merge(i,i,4,5, ' ',style_header) if len(lst) < 1: raise osv.except_osv(_('Warning!'),_('No Record found!')) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) sal_report = self.write(cr, uid, ids, {'export_data':out, 'filename':'Salary Report.xls'}, context=context) return sal_report
def get_attendance_report(self): try: import xlwt except: raise Warning(_('User Error'), _('Please Install xlwt Library.!')) filename = 'Attendance Register.xls' fp = BytesIO() wb = xlwt.Workbook(encoding='utf-8') worksheet = wb.add_sheet('PC_EXAM_ATTENDANCE_REGISTER') current_obj = self # ----------------------- # Excel Font Style & Sizes # ---------------------- fnt1 = Font() fnt1.name = 'TimesNewRoman' fnt1.bold = True fnt1.height = 16 * 0x14 fnt2 = Font() fnt2.name = 'verdana' fnt2.bold = True fnt2.height = 12 * 0x14 fnt3 = Font() fnt3.name = 'TimesNewRoman' fnt3.bold = True fnt3.height = 13 * 0x12 fnt4 = Font() fnt4.name = 'TimesNewRoman' fnt4.height = 16 * 0x14 # ----------------- # Excel Alignment # ---------------- al3 = Alignment() al3.horz = Alignment.HORZ_CENTER al3.vert = Alignment.VERT_CENTER al4 = Alignment() al4.horz = Alignment.HORZ_LEFT al4.vert = Alignment.VERT_CENTER al5 = Alignment() al5.horz = Alignment.HORZ_LEFT al5.vert = Alignment.VERT_CENTER # ----------------------- # Excel Style # ---------------------- style1 = XFStyle() style1.alignment = al3 style1.font = fnt1 style2 = XFStyle() style2.font = fnt2 style2.alignment = al4 style3 = XFStyle() style3.alignment = al3 style3.font = fnt3 style4 = XFStyle() style4.alignment = al5 style4.font = fnt4 # ------------------------------------------------------------------------- # HEADER In Excel # ------------------------------------------------------------------------- lst = [] fields = ['CIMA STUDENT NUMBER', 'SURNAME', 'NAME', 'SIGNATURE'] for i in [1, 2, 3, 4]: first_col = worksheet.col(i) first_col.width = 500 * 20 date_begin = str( datetime.strptime(current_obj.date_begin, "%Y-%m-%d %H:%M:%S") + timedelta(hours=2)) date_end = str( datetime.strptime(current_obj.date_end, "%Y-%m-%d %H:%M:%S") + timedelta(hours=2)) worksheet.merge(0, 16, 1, 4) try: worksheet.insert_bitmap( '/opt/custom_modules/event_price_kt/images/charterquest.bmp', 0, 1, 9, 3) except: pass worksheet.write_merge(17, 18, 1, 1, 'SUBJECT:', style1) worksheet.write_merge(17, 18, 2, 4, current_obj.name, style1) k = 19 pc_exam = True if current_obj.pc_exam == False: pc_exam = False worksheet.write_merge(k, k + 1, 1, 1, 'LECTURER:', style1) # if current_obj.main_speaker_id: # worksheet.write_merge(k,k+1,2, 4,current_obj.main_speaker_id.name , style1) # else: # worksheet.write_merge(k,k+1,2, 4,"", style1) k = k + 2 worksheet.write_merge(k, k + 1, 1, 1, 'DATE:', style1) worksheet.write_merge(k, k + 1, 2, 4, (date_begin).split(' ')[0], style1) worksheet.write_merge(k + 2, k + 3, 1, 1, 'TIME:', style1) worksheet.write_merge(k + 2, k + 3, 2, 4, (date_begin)[11:16] + '-' + (date_end)[11:16], style1) if not pc_exam: worksheet.write_merge(k + 4, k + 5, 1, 1, 'COURSE STUDY OPTION:', style1) # worksheet.write_merge(k+4,k+5,2, 4,current_obj.study.name, style1) k = k + 4 else: k = k + 2 worksheet.write_merge(k + 2, k + 3, 1, 1, 'CAMPUS:', style1) worksheet.write_merge(k + 2, k + 3, 2, 4, current_obj.address_id.name, style1) worksheet.write_merge( k + 4, k + 5, 1, 4, "PLEASE SIGN ATTENDANCE REGISTER & ENTER CIMA STUDENT NUMBER", style1) i = 1 for field in fields: worksheet.write_merge(k + 6, k + 7, i, i, field, style1) i += 1 i = k + 8 for obj in current_obj.registration_ids: partner_name = obj.partner_id.name name = partner_name.split(' ') if len(name) == 1: name = partner_name.split(' ') if obj.partner_id.prof_body_id: worksheet.write_merge(i, i + 1, 1, 1, obj.partner_id.prof_body_id, style4) else: worksheet.write_merge(i, i + 1, 1, 1, "", style4) try: worksheet.write_merge(i, i + 1, 2, 2, name[1], style4) except: worksheet.write_merge(i, i + 1, 2, 2, '', style4) worksheet.write_merge(i, i + 1, 3, 3, name[0], style4) worksheet.write_merge(i, i + 1, 4, 4, '', style4) i += 2 wb.save(fp) out = base64.encodestring(fp.getvalue()) final_arr_data = {} final_arr_data['file_stream'] = out final_arr_data['name'] = filename pl_report_id = self.env['attendance.sheet.report'].create( final_arr_data) vals = { 'name': 'Attendance Sheet Report', 'datas': out, 'datas_fname': filename } return { 'res_id': pl_report_id.id, 'name': filename, 'view_type': 'form', 'view_mode': 'form', 'res_model': 'attendance.sheet.report', 'type': 'ir.actions.act_window', }
def report_get(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'Employee Name',style_header) ws.col(2).width = 8000 ws.write(0,3,'Salary Amount',style_header) ws.col(3).width = 5000 ws.write(0,4,month_name,style_header) ws.col(4).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource\ as res on (emp.resource_id=res.id) where emp.department_id = \ '"+str(this.dept_id.id)+"' and emp.department_id is not null and \ res.active=True order by emp.department_id") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource \ as res on (emp.resource_id=res.id) where res.active=True order by emp.department_id") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=0 dept_dict = {} grand = total = pay_total = pay_grand = budget = 0.0 pay_data = False flag = True for each in emp_obj.browse(cr, uid, emp_ids): pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id)]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) i+=1 if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: salary = 0.0 if flag: ws.write(i,0, 'Allocated Budget',style_header) ws.write(i,1, 0.0,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, total,style_header) if pay_total: ws.write(i,4,pay_total,style_header) else: ws.write(i,4, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,5, diff,style_header1) flag = False i += 2 total = pay_total = budget = 0.0 name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,0, 'X Department') ws.write(i,1,'X Reporting Officer') ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 else: dept_dict[str(each.department_id.id)] = '' if i != 1: ws.write(i,0, 'Allocated Budget',style_header) if budget: ws.write(i,1, budget,style_header) else: budget = 0.0 ws.write(i,1,budget,style_header) ws.write(i,2, 'Total',style_header) ws.write(i,3, total,style_header) if pay_total: ws.write(i,4,pay_total,style_header) else: ws.write(i,4, 0.0,style_header) if budget: diff = pay_total - float(budget) else: diff = pay_total ws.write(i,5, diff,style_header1) i += 2 total = pay_total = budget = 0.0 if each.department_id: salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') name = '[' + str(each.sinid) +'] '+ str(each.name) ws.write(i,2, name) if each.daily: salary = each.salary * working_day else: salary = each.salary ws.write(i,3, salary) if pay_data and pay_data.employee_id.id == each.id: ws.write(i,4,pay_data.total_amount or 0.0) else: ws.write(i,4, 0.0) total += salary grand += salary if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+1,2, 'Total',style_header) ws.write(i+1,3, total,style_header) ws.write(i+3,2, 'Grand Total',style_header) ws.write(i+3,3, grand,style_header) ws.write(i+1,4, pay_total,style_header) ws.write(i+3,4, pay_grand,style_header) diff_pay = pay_total - total diff_grand = pay_grand - grand ws.write(i+1,5, diff_pay,style_header1) ws.write(i+3,5, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def performance_register_report(self, cr, uid, ids, data, context=None): obj = self.browse(cr, uid, ids) emp_obj = self.pool.get('hr.employee') f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('Contractor Payment Bonus') total_salary = apr_salary = may_salary = june_salary = july_salary = aug_salary = sep_salary = oct_salary = nov_salary = dec_salary = jan_salary = feb_salary = mar_salary = 0 fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x00 borders1.right = 0x00 borders1.top = 0x00 borders1.bottom = 0x00 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x1F style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 300 fnt2.bold = True align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x00 borders2.right = 0x00 borders2.top = 0x00 borders2.bottom = 0x00 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x1F style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 300 fnt3.bold = True align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x00 borders3.right = 0x00 borders3.top = 0x00 borders3.bottom = 0x00 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x1F style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 200 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() align_content5.horz = Alignment.HORZ_CENTER borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() align5.horz = Alignment.HORZ_CENTER align5.vert = Alignment.VERT_CENTER pattern5 = Pattern() # pattern5.pattern = Pattern.SOLID_PATTERN # pattern5.pattern_fore_colour = 0x1F style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 if obj.partner_id: get_name = obj.partner_id.name elif obj.company_id: get_name = obj.company_id.name + ' ' + obj.company_id.street + ' ' + ',' + obj.company_id.city + ' ' + '-' + obj.company_id.zip else: get_name = obj.employee_id.partner_id.name ws.row(0).height = 500 ws.write_merge(0, 0, 0, 19, get_name, style_header1) date1 = datetime.strptime(obj.from_date, "%Y-%m-%d").timetuple().tm_year date2 = datetime.strptime(obj.till_date, "%Y-%m-%d").timetuple().tm_year if date1 == date2: d_name = 'BONUS' + ' - ' + str(date1) else: d_name = 'BONUS' + ' ' + str(date1) + ' ' + '-' + ' ' + str(date2) ws.row(1).height = 500 ws.write_merge(1, 1, 0, 19, d_name, style_header2) ws.col(0).width = 5000 ws.col(1).width = 7500 ws.col(2).width = 5000 ws.col(3).width = 3000 ws.col(4).width = 3000 ws.col(5).width = 3000 ws.col(6).width = 3000 ws.col(7).width = 3000 ws.col(8).width = 3000 ws.col(9).width = 3000 ws.col(10).width = 3000 ws.col(11).width = 3000 ws.col(12).width = 3000 ws.col(13).width = 3000 ws.col(14).width = 3000 ws.col(15).width = 3000 ws.col(16).width = 3000 ws.col(17).width = 3000 ws.col(18).width = 3000 ws.col(19).width = 3000 ws.col(20).width = 3000 ws.col(21).width = 3000 ws.col(22).width = 3000 ws.col(23).width = 3000 ws.col(24).width = 3000 ws.col(25).width = 3000 ws.col(26).width = 3000 ws.col(27).width = 3000 ws.col(28).width = 3000 ws.col(29).width = 4000 ws.row(2).height = 400 ws.write(2, 0, 'EMP. CODE', style_header) ws.write(2, 1, 'NAME', style_header) ws.write(2, 2, 'JOINING DATE', style_header) ws.write_merge(2, 2, 3, 4, 'APRIL', style_header) ws.write_merge(2, 2, 5, 6, 'MAY', style_header) ws.write_merge(2, 2, 7, 8, 'JUNE', style_header) ws.write_merge(2, 2, 9, 10, 'JULY', style_header) ws.write_merge(2, 2, 11, 12, 'AUGUST', style_header) ws.write_merge(2, 2, 13, 14, 'SEPTEMBER', style_header) ws.write_merge(2, 2, 15, 16, 'OCTOBER', style_header) ws.write_merge(2, 2, 17, 18, 'NOVEMBER', style_header) ws.write_merge(2, 2, 19, 20, 'DECEMBER', style_header) ws.write_merge(2, 2, 21, 22, 'JANUARY', style_header) ws.write_merge(2, 2, 23, 24, 'FEBRUARY', style_header) ws.write_merge(2, 2, 25, 26, 'MARCH', style_header) ws.write_merge(2, 2, 27, 28, 'TOTAL', style_header) ws.write(2, 29, 'BONUS', style_header) ws.row(3).height = 400 ws.write(3, 0, '', style_header) ws.write(3, 1, '', style_header) ws.write(3, 2, '', style_header) ws.write(3, 3, 'DAYS', style_header) ws.write(3, 4, 'SALARY', style_header) ws.write(3, 5, 'DAYS', style_header) ws.write(3, 6, 'SALARY', style_header) ws.write(3, 7, 'DAYS', style_header) ws.write(3, 8, 'SALARY', style_header) ws.write(3, 9, 'DAYS', style_header) ws.write(3, 10, 'SALARY', style_header) ws.write(3, 11, 'DAYS', style_header) ws.write(3, 12, 'SALARY', style_header) ws.write(3, 13, 'DAYS', style_header) ws.write(3, 14, 'SALARY', style_header) ws.write(3, 15, 'DAYS', style_header) ws.write(3, 16, 'SALARY', style_header) ws.write(3, 17, 'DAYS', style_header) ws.write(3, 18, 'SALARY', style_header) ws.write(3, 19, 'DAYS', style_header) ws.write(3, 20, 'SALARY', style_header) ws.write(3, 21, 'DAYS', style_header) ws.write(3, 22, 'SALARY', style_header) ws.write(3, 23, 'DAYS', style_header) ws.write(3, 24, 'SALARY', style_header) ws.write(3, 25, 'DAYS', style_header) ws.write(3, 26, 'SALARY', style_header) ws.write(3, 27, 'DAYS', style_header) ws.write(3, 28, 'SALARY', style_header) ws.write(3, 29, '', style_header) if obj.partner_id: list_ids = emp_obj.search(cr, uid, [('partner_id', '=', obj.partner_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) list_ids1 = emp_obj.search(cr, uid, [('partner_id', '=', obj.partner_id.id), ('active', '=', False), ('type', '=', 'Contractor')]) list_ids = list_ids + list_ids1 elif obj.employee_id: list_ids = emp_obj.search(cr, uid, [('id', '=', obj.employee_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) elif obj.company_id: list_ids = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', True), ('type', '=', 'Contractor')]) list_ids1 = emp_obj.search(cr, uid, [('company_id', '=', obj.company_id.id), ('active', '=', False), ('type', '=', 'Contractor')]) list_ids = list_ids + list_ids1 else: raise osv.except_osv( _('Warning !'), _("Please Select Atleast Company Or Employee.")) if len(list_ids) == 1: query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\ "where pmbl.employee_id = '"+str(list_ids[0])+"' and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid " cr.execute(query) temp = cr.fetchall() else: query ="select hr.sinid,rr.name,hr.doj,sum(pmbl.apr),sum(pmbl.may),sum(pmbl.june),sum(pmbl.july),sum(pmbl.aug),sum(pmbl.sep),sum(pmbl.oct),"\ "sum(pmbl.nov),sum(pmbl.dec),sum(pmbl.jan),sum(pmbl.feb),sum(pmbl.mar),sum(pmbl.total_day),sum(pmbl.bonus),sum(pmbl.apr_salary),"\ "sum(pmbl.may_salary),sum(pmbl.june_salary),sum(pmbl.july_salary),sum(pmbl.aug_salary),sum(pmbl.sep_salary),sum(pmbl.oct_salary),"\ "sum(pmbl.nov_salary),sum(pmbl.dec_salary),sum(pmbl.jan_salary),sum(pmbl.feb_salary),sum(pmbl.mar_salary),sum(pmbl.total_salary)"\ "from payment_management_bonus_line as pmbl left join hr_employee as hr on pmbl.employee_id = hr.id left join resource_resource as rr on hr.resource_id = rr.id "\ "where pmbl.employee_id in "+str(tuple(list_ids))+" and pmbl.bonus_from >= '"+str(obj.from_date)+"' and pmbl.bonus_till <= '"+str(obj.till_date)+"' group by hr.sinid,rr.name,hr.doj order by hr.sinid" cr.execute(query) temp = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) columnno = 4 for val in temp: doj = datetime.strptime(val[2], "%Y-%m-%d").strftime("%d-%m-%Y") ws.write(columnno, 0, val[0], style_header5) ws.write(columnno, 1, val[1], style_header5) ws.write(columnno, 2, doj, style_header5) ws.write(columnno, 3, val[3], style_header5) ws.write(columnno, 4, val[17], style_header5) ws.write(columnno, 5, val[4], style_header5) ws.write(columnno, 6, val[18], style_header5) ws.write(columnno, 7, val[5], style_header5) ws.write(columnno, 8, val[19], style_header5) ws.write(columnno, 9, val[6], style_header5) ws.write(columnno, 10, val[20], style_header5) ws.write(columnno, 11, val[7], style_header5) ws.write(columnno, 12, val[21], style_header5) ws.write(columnno, 13, val[8], style_header5) ws.write(columnno, 14, val[22], style_header5) ws.write(columnno, 15, val[9], style_header5) ws.write(columnno, 16, val[23], style_header5) ws.write(columnno, 17, val[10], style_header5) ws.write(columnno, 18, val[24], style_header5) ws.write(columnno, 19, val[11], style_header5) ws.write(columnno, 20, val[25], style_header5) ws.write(columnno, 21, val[12], style_header5) ws.write(columnno, 22, val[26], style_header5) ws.write(columnno, 23, val[13], style_header5) ws.write(columnno, 24, val[27], style_header5) ws.write(columnno, 25, val[14], style_header5) ws.write(columnno, 26, val[28], style_header5) ws.write(columnno, 27, val[15], style_header5) ws.write(columnno, 28, val[29], style_header5) ws.write(columnno, 29, val[16], style_header5) columnno += 1 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, { 'export_data': out, 'filename': 'Contractor Payment Bonus.xls' }, context=context)
def order_specs_report_export(request): response = HttpResponse(content_type='application/ms-excel') response['Content-Disposition'] = 'attachment; filename="order_specs.xls"' wb = xlwt.Workbook(encoding='utf-8') ws = wb.add_sheet('کزارش ردیف درخواست') # Sheet header, first row row_num = 0 base_style = xlwt.XFStyle() al = Alignment() al.horz = Alignment.HORZ_CENTER b_nazanin = xlwt.Font() b_nazanin.name = "B Nazanin" b_nazanin.height = 220 num_format_str = "#,##0" base_style.alignment = al base_style.font = b_nazanin import copy header_style = copy.deepcopy(base_style) font_style = copy.deepcopy(base_style) number_style = copy.deepcopy(base_style) footer_style = copy.deepcopy(base_style) header_style.font.bold = True header_style.font.height = 240 number_style.num_format_str = num_format_str footer_style.num_format_str = num_format_str footer_style.font.height = 240 columns = ( 'ردیف', 'درخواست', 'مشتری', 'صنعت', 'تاریخ', 'نوع', 'کیلووات', 'دور', 'ولتاژ', 'تعداد دستگاه', 'IM', 'IP', 'IC', 'IE', 'پیش فاکتور', 'تأخیر ثبت', 'زمان پاسخ دهی', 'واریزی', 'زمان اولین واریز', 'سررسید', 'مجوز', 'زمان اولین مجوز', 'زمان تسویه', 'کارشناس' ) for col_num in range(len(columns)): ws.write(row_num, col_num, columns[col_num], header_style) # context = get_filtered_orders(request) request_get_url = request.session.get('order-spec-report-filters', request.GET) order_spec_filtered = OrderSpecFilter(request_get_url, request=request) for spec in order_spec_filtered.qs: row_num += 1 exportables = [] exportables.append({ 'style': font_style, 'value': row_num }) exportables.append({ 'style': font_style, 'value': spec.req_id.number }) exportables.append({ 'style': font_style, 'value': spec.req_id.customer.name }) exportables.append({ 'style': font_style, 'value': spec.req_id.customer.type.name }) exportables.append({ 'style': font_style, 'value': str(spec.req_id.date_fa) }) exportables.append({ 'style': font_style, 'value': str(spec.type) }) exportables.append({ 'style': number_style, 'value': spec.kw }) try: speed = spec.rpm_new.rpm except: speed = '' exportables.append({ 'style': font_style, 'value': speed }) exportables.append({ 'style': font_style, 'value': spec.voltage }) exportables.append({ 'style': font_style, 'value': spec.qty }) exportables.append({ 'style': font_style, 'value': spec.im.title if spec.im else '' }) exportables.append({ 'style': font_style, 'value': spec.ip.title if spec.ip else '' }) exportables.append({ 'style': font_style, 'value': spec.ic.title if spec.ic else '' }) exportables.append({ 'style': font_style, 'value': spec.ie.title if spec.ie else '' }) exportables.append({ 'style': font_style, 'value': spec.req_id.get_proformas().count() }) exportables.append({ 'style': font_style, 'value': spec.req_id.submit_delay() }) exportables.append({ 'style': font_style, 'value': spec.req_id.first_response_time() }) exportables.append({ 'style': font_style, 'value': spec.req_id.get_payments().count() }) exportables.append({ 'style': font_style, 'value': spec.req_id.first_payment_time() }) exportables.append({ 'style': font_style, 'value': str(spec.req_id.last_cheque().due_date) if spec.req_id.last_cheque() else '' }) exportables.append({ 'style': font_style, 'value': spec.req_id.get_permits().count() }) exportables.append({ 'style': font_style, 'value': spec.req_id.first_permit_time() }) exportables.append({ 'style': font_style, 'value': spec.req_id.settlement_time() }) exportables.append({ 'style': font_style, 'value': spec.req_id.owner.last_name }) for col_num in range(len(exportables)): ws.write(row_num, col_num, exportables[col_num]['value'], exportables[col_num]['style']) # row_num += 1 # ws.write_merge(row_num, row_num, 0, 5, 'جمع', footer_style) # ws.write(row_num, 6, context['qty'], footer_style) # ws.write(row_num, 9, context['kw'], footer_style) # ws.write(row_num, 11, context['amount'], footer_style) ws.cols_right_to_left = True wb.save(response) return response
def exportReport5ToExcel(self,objectProject): book = Workbook(); sheet1 = book.add_sheet('Sheet 1'); sheet1.col(1).width = 256*80; sheet1.col(2).width = 256*10; sheet1.col(3).width = 256*20; borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN pattern = Pattern(); pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 23 wrap = Alignment(); wrap.wrap = 1; wrap.vert = Alignment.VERT_TOP alignHeader = Alignment(); alignHeader.horz = Alignment.HORZ_CENTER; alignTop = Alignment(); alignTop.vert = Alignment.VERT_TOP fnt = Font() fnt.name = 'Arial' fnt.colour_index = 4 fnt.bold = True styleWrap = XFStyle(); styleWrap.alignment = wrap; styleHead = XFStyle(); styleHead.font = fnt; styleHead.borders = borders; styleHead.pattern = pattern; styleHead.alignment = alignHeader; styleRowDetail = XFStyle(); styleRowDetail.borders = borders; styleRowDetail.alignment = alignTop; styleDate = XFStyle() styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY'; styleDate.borders = borders; styleDate.alignment = alignTop; StyleRowDetailWrap = styleRowDetail ; StyleRowDetailWrap.alignment = wrap; if( objectProject): i=0; row1 = sheet1.row(i) ; row1.write(0, ('risk id').decode('UTF8'),styleHead ); #sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ); row1.write(1, ('รายละเอียด').decode('UTF8'),styleHead); row1.write(2, ('วันที่รายงาน').decode('UTF8'),styleHead ); row1.write(3, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead); i=i+1; for value in objectProject: row1 = sheet1.row(i) ; row1.write(0, value.get('risk_management_id') ,styleRowDetail ); row1.write(1, value.get('risk_detail').decode('UTF8'),StyleRowDetailWrap ); #sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap ); row1.write(2, value.get('report_date') ,styleDate ); row1.write(3, value.get('report').decode('UTF8') ,styleRowDetail ); i=i+1; for sub in value.get('response') : row1 = sheet1.row(i) ; row1.write(0," " ); text = "(" + sub.get('risk_team').decode('UTF8') + " ) " + sub.get('result').decode('UTF8'); row1.write(1, text ,StyleRowDetailWrap ); i=i+1; dirTempFile = gettempdir() + _os.sep + str('simpleReport5.xls'); book.save(dirTempFile); return dirTempFile;
# Licence: <your licence> #------------------------------------------------------------------------------- from xlwt import Alignment, XFStyle, Borders, Font # ************************************************************************************** # styles # # year break dashed border yearBreak = XFStyle() yearBorders = Borders() yearBorders.bottom = Borders.DASHED yearBreak.borders = yearBorders # center align alignCenter = Alignment() alignCenter.horz = Alignment.HORZ_CENTER alignCenter.vert = Alignment.VERT_CENTER # Top alignment, should be used for all cells topAlign = Alignment() topAlign.vert = Alignment.VERT_TOP # header centered and wrapped alignWrap = Alignment() alignWrap.horz = Alignment.HORZ_CENTER alignWrap.wrap = Alignment.WRAP_AT_RIGHT # normal font normFont = Font() normFont.name = 'Arial'
def account_budget(self,cr,uid,ids,context=None): this=self.browse(cr,uid,ids[0]) year = this.year_id.name year_id = this.year_id.id month = this.month if int(month) in [1,3,5,7,8,10,12]: join_date=year +'-'+month+'-'+'31' if int(month) in [4,6,9,11]: join_date=year +'-'+month+'-'+'30' if int(month) in [2]: if int(year) % 4 == 0: join_date=year +'-'+month+'-'+'29' else: join_date=year +'-'+month+'-'+'28' if month == '1': month_name = 'January' elif month == '2': month_name = 'February' elif month == '3': month_name = 'March' elif month == '4': month_name = 'April' elif month == '5': month_name = 'May' elif month == '6': month_name = 'June' elif month == '7': month_name = 'July' elif month == '8': month_name = 'August' elif month == '9': month_name = 'September' elif month == '10': month_name = 'October' elif month == '11': month_name = 'November' elif month == '12': month_name = 'December' else: raise osv.except_osv(_('Warning !'),_("Specify month correctly. ")) #Define the font attributes for header fnt = Font() fnt.name = 'Arial' fnt.height= 275 #Define the font attributes for header content_fnt = Font() content_fnt.name ='Arial' content_fnt.height =220 align_content = Alignment() align_content.horz= Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 #The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F #apply the above settings to the row(0) header style_header= XFStyle() style_header.font= fnt style_header.pattern= pattern style_header.borders = borders style_header.alignment=align #Define the font attributes for header fnt1 = Font() fnt1.name = 'Arial' fnt1.height= 275 #Define the font attributes for header content_fnt1 = Font() content_fnt1.name ='Arial' content_fnt1.height =220 align_content1 = Alignment() align_content1.horz= Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 #The text should be centrally aligned align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x32 #apply the above settings to the row(0) header style_header1= XFStyle() style_header1.font= fnt1 style_header1.pattern= pattern1 style_header1.borders = borders1 style_header1.alignment=align1 #Define the font attributes for header fnt2 = Font() fnt2.name = 'Arial' fnt2.height= 275 #Define the font attributes for header content_fnt2 = Font() content_fnt2.name ='Arial' content_fnt2.height =220 align_content2 = Alignment() align_content2.horz= Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 #The text should be centrally aligned align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER #We set the backgroundcolour here pattern2 = Pattern() pattern2.pattern = Pattern.SOLID_PATTERN pattern2.pattern_fore_colour = 0x0A #apply the above settings to the row(0) header style_header2= XFStyle() style_header2.font= fnt2 style_header2.pattern= pattern2 style_header2.borders = borders2 style_header2.alignment=align2 style_content= XFStyle() style_content.alignment = align_content style_content.font = content_fnt month_name = 'Payment ('+str(month_name)+')' wb = Workbook() ws = wb.add_sheet('Budget') ws.row(0).height=500 ws.write(0,0,'Department Name',style_header) ws.col(0).width = 8000 ws.write(0,1,'Department HoD',style_header) ws.col(1).width = 8000 ws.write(0,2,'RO of HOD',style_header) ws.col(2).width = 8000 ws.write(0,3,'Allocated Budget',style_header) ws.col(3).width = 8000 ws.write(0,4,'Salary Amount',style_header) ws.col(4).width = 5000 ws.write(0,5,month_name,style_header) ws.col(5).width = 8000 ws.write(0,6,'Difference',style_header) ws.col(6).width = 8000 ws.write(0,7,'No Of Emp',style_header) ws.col(7).width = 8000 # ws.write(0,5,'O.T. Amount',style_header) # ws.col(5).width = 4400 # ws.write(0,6,'Total Amount',style_header) # ws.col(6).width = 4400 # ws.write(0,7,'Insentive Amount',style_header) # ws.col(7).width = 4400 # ws.write(0,8,'Deduction Amount',style_header) # ws.col(8).width = 5000 # ws.write(0,9,'Percentage',style_header) # ws.col(9).width = 4400 emp_ids = [] emp_obj=self.pool.get('hr.employee') pay_obj=self.pool.get('salary.payment.line') if this.dept_id: cr.execute("select emp.id from hr_employee as emp left join resource_resource " \ "as res on (emp.resource_id=res.id) left join hr_department as dept on " \ "(emp.department_id=dept.id) where emp.department_id = '"+str(this.dept_id.id)+"' and " \ "emp.department_id is not null and res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code, " \ "(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) else: cr.execute("select emp.id from hr_employee as emp left join resource_resource " \ "as res on (emp.resource_id=res.id) left join hr_department as dept on (emp.department_id=dept.id) " \ "where res.active=True and emp.joining_date <'"+str(join_date)+"' order by dept.dept_code,(substring(emp.sinid, '^[0-9]+'))::int ,substring(emp.sinid, '[^0-9_].*$')") temp = cr.fetchall() for data in temp: if len(data)>0 and data[0] != None: emp_ids.append(data[0]) holiday_obj = self.pool.get('holiday.list') if int(month) in [1,3,5,7,8,10,12]: month = 31 if int(month) in [4,6,9,11]: month = 30 if int(month) in [2]: if int(year) % 4 == 0: month = 29 else: month = 28 off_day = working_day = 0 holiday_ids = holiday_obj.search(cr, uid, [('month','=',this.month),('year_id','=',year_id)]) for line in holiday_obj.browse(cr, uid, holiday_ids): off_day = line.holiday working_day = month - off_day i=1 dept_dict = {} grand = total = pay_total = pay_grand = budget = total_budget = 0.0 pay_data = False flag = True count1=0.0 for each in emp_obj.browse(cr, uid, emp_ids): pay_ids = pay_obj.search(cr, uid, [('employee_id','=',each.id),('month','=',this.month),('year_id','=',this.year_id.id),('salary_type','=','Salary')]) if pay_ids: pay_data = pay_obj.browse(cr, uid, pay_ids[0]) if dept_dict.has_key(str(each.department_id.id)): if each.department_id: salary = 0.0 if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic total += salary grand += salary # if pay_data.employee_id.id == each.id: # print"ssssssssssssssssssss minssssssssss",count1 # count1=count1+1 if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 count1=count1+1 else: pay_total += 0.0 pay_grand += 0.0 elif not each.department_id: if flag: i -= 1 ws.write(i,3, total) ws.write(i,4, pay_total) if budget: diff = float(budget) - pay_total else: diff = pay_total if budget and diff > 0: ws.write(i,5, diff,style_header1) elif budget and diff < 0: ws.write(i,5, diff,style_header2) else: ws.write(i,5, diff,style_header2) ws.write(i,6, count1) count1=0.0 flag = False i += 1 # salary = 0.0 # name = '[' + str(each.sinid) +'] '+ str(each.name) # # ws.write(i,0, 'X Department') # ws.write(i,1,'X Reporting Officer') # ws.write(i,2, name) # # if each.daily: # salary = each.salary * working_day # else: # salary = each.salary # ws.write(i,3, salary) # if pay_data and pay_data.employee_id.id == each.id: # ws.write(i,4,pay_data.total_amount or 0.0) # else: # ws.write(i,4, 0.0) else: dept_dict[str(each.department_id.id)] = '' if i != 1: i -= 1 ws.write(i,4, total) ws.write(i,5, pay_total) if budget: diff = float(budget) - pay_total else: diff = pay_total if budget and diff > 0: ws.write(i,6, diff,style_header1) elif budget and diff < 0: ws.write(i,6, diff,style_header2) else: ws.write(i,6, diff,style_header2) ws.write(i,7, count1) count1=0.0 i += 1 if each.department_id: total = pay_total = budget = 0.0 salary = 0.0 budget = each.department_id.dept_budget dept = '[' + str(each.department_id.dept_code) +'] '+ str(each.department_id.name) ws.write(i,0, dept) ws.write(i,1, each.department_id.manager_id and each.department_id.manager_id.name or '') ws.write(i,2, each.department_id.manager_id.parent_id and each.department_id.manager_id.parent_id.name or '') ws.write(i,3, budget) total_budget += budget if pay_data.employee_id.daily: salary = pay_data.basic * working_day else: salary = pay_data.basic total += salary grand += salary # if pay_data.employee_id.id == each.id: # print"saaaaaaaaaaaaaallllllllllllllllllllllllllll",count1 # count1=count1+1 if pay_data and pay_data.employee_id.id == each.id: pay_total += pay_data.total_amount or 0.0 pay_grand += pay_data.total_amount or 0.0 count1=count1+1 else: pay_total += 0.0 pay_grand += 0.0 i += 1 ws.write(i+3,1, 'Grand Total',style_header) ws.write(i+3,3, total_budget,style_header) ws.write(i+3,4, grand,style_header) ws.write(i+3,5, pay_grand,style_header) diff_grand = total_budget - pay_grand ws.write(i+3,6, diff_grand,style_header1) f = cStringIO.StringIO() wb.save(f) out=base64.encodestring(f.getvalue()) return self.write(cr, uid, ids, {'export_data':out, 'filename':'export.xls'}, context=context)
def qiandaoXls(filename,qiandao,dategroup,mi,response): import uuid filename+=u'.xls' response['Content-Disposition'] = (u'attachment;filename=%s'%filename).encode('utf-8') import xlwt from xlwt import Font,Alignment style1=xlwt.XFStyle() font1=Font() font1.height=320 font1.name=u'仿宋' style1.font=font1 algn=Alignment() algn.horz=Alignment.HORZ_RIGHT style1.alignment=algn style0=xlwt.XFStyle() algn0=Alignment() algn0.horz=Alignment.HORZ_CENTER algn0.vert=Alignment.VERT_CENTER font=Font() font.height=320 font.bold=False font.name=u'仿宋' style0.alignment=algn0 style0.font=font style3=xlwt.XFStyle() algn3=Alignment() algn3.horz=Alignment.HORZ_CENTER font3=Font() font3.height=320 font3.bold=False font1.name=u'仿宋' style3.alignment=algn3 style3.font=font3 pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour =2 style3.pattern=pattern wb=xlwt.Workbook() style2=xlwt.XFStyle() algn2=Alignment() algn2.horz=Alignment.HORZ_LEFT font2=Font() font2.height=350 font2.bold=True font1.name=u'仿宋' style2.alignment=algn2 style2.font=font2 wb=xlwt.Workbook() ws=wb.add_sheet(u"签到报表",cell_overwrite_ok=True) rownum=2 usernum=1 ws.write_merge(0,1,0,0,u'序号',style2) ws.write_merge(0,1,1,1,u'员工ID',style2) ws.col(1).width=0x0d00 +3000 ws.write_merge(0,1,2,2,u'姓名',style2) ws.col(2).width=0x0d00 +3000 for i,q in enumerate(qiandao): ws.write_merge(0,0,5*i+3,5*i+6,q.name,style2) ws.write_merge(1,1,5*i+3,5*i+3,u'厅台',style2) ws.col(5*i+3).width=0x0d00 + 7000 ws.write_merge(1,1,5*i+4,5*i+4,u'地址',style2) ws.col(5*i+4).width=0x0d00 + 9000 ws.write_merge(1,1,5*i+5,5*i+5,u'时间',style2) ws.write_merge(1,1,5*i+6,5*i+6,u'位置判断',style2) ws.col(6).width=0x0d00 +3000 ws.write_merge(1,1,5*i+7,5*i+7,u'时间判读',style2) ws.col(7).width=0x0d00 +3000 for data in dategroup: ws.write_merge(rownum,rownum,0,data['rowspan2'],u'日期:%s'%(data['date'],),style2) rownum+=1 tempnum=0 for query in data['query']: for i,rows in enumerate(query['qiandaolist']): for j,row in enumerate(rows): ws.write_merge(rownum+j,rownum+j,3+i*5+0,3+i*5+0,row['officename'],style0) ws.write_merge(rownum+j,rownum+j,3+i*5+1,3+i*5+1,row['address'],style0) ws.write_merge(rownum+j,rownum+j,3+i*5+2,3+i*5+2,row['dateTime'],style0) if row['officegps']: if row['gpsdistance']<mi: ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'合格',style0) else: ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'不合格',style3) else: ws.write_merge(rownum+j,rownum+j,3+i*5+3,3+i*5+3,u'0',style0) if row['time']: ws.write_merge(rownum+j,rownum+j,3+i*5+4,3+i*5+4,u'合格',style0) else: ws.write_merge(rownum+j,rownum+j,3+i*5+4,3+i*5+4,u'不合格',style3) if tempnum<j: tempnum=j ws.write_merge(rownum,rownum+tempnum,0,0,usernum,style0) ws.write_merge(rownum,rownum+tempnum,1,1,query['user']['username'],style0) ws.write_merge(rownum,rownum+tempnum,2,2,query['user']['get_full_name'],style0) rownum+=tempnum+1 usernum+=1 wb.save(response)
q = q.filter(Q(site=site)) q = q.filter(Q(activity_time_to__gt=start) & Q(activity_time_from__lt=end)) if ignore is not None: if isinstance(ignore, Iterable): q = q.exclude(uid__in=ignore) else: q = q.exclude(uid=ignore) cnt = q.count() return cnt != 0 thick_border = easyxf('border: left thick, top thick, ' 'bottom thick, right thick') align_hc_vc = Alignment() align_hc_vc.horz = Alignment.HORZ_CENTER align_hc_vc.vert = Alignment.VERT_CENTER align_hc_vc.wrap = True align_hl_vt = Alignment() align_hl_vt.horz = Alignment.HORZ_LEFT align_hl_vt.vert = Alignment.VERT_TOP align_hl_vt.wrap = True align_hc_vb = Alignment() align_hc_vb.horz = Alignment.HORZ_CENTER align_hc_vb.vert = Alignment.VERT_BOTTOM align_hc_vb.wrap = True borders_thin = Borders() borders_thin.left = Borders.THIN
def create_excel(work_excel, withdraw_info=[]): title_fnt = Font() title_fnt.height = 0x0140 title_fnt.name = u'宋体' title_fnt.bold = True brd = Borders() brd.bottom = 1 brd.top = 1 brd.left = 1 brd.right = 1 title_location = Alignment() title_location.horz = Alignment.HORZ_CENTER title_location.vert = Alignment.VERT_CENTER title_style = XFStyle() title_style.font = title_fnt title_style.alignment = title_location style = XFStyle() style.font.height = 0x00E0 style.font.name = u'宋体' style.font.bold = False style.alignment.horz = Alignment.HORZ_CENTER style.alignment.vert = Alignment.VERT_CENTER content_title_style = deepcopy(style) content_title_style.alignment.horz = Alignment.HORZ_LEFT content_title_style.font.height = 0x00E0 content_style = deepcopy(style) content_style.alignment.horz = Alignment.HORZ_LEFT center_content_style = deepcopy(style) center_content_style.alignment.horz = Alignment.HORZ_CENTER center_content_style.borders = brd style.borders = brd content_style.borders = brd merge_up_style = deepcopy(style) merge_up_style.borders.bottom = 0 content_up_style = deepcopy(merge_up_style) content_up_style.alignment.horz = Alignment.HORZ_CENTER merge_down_style = deepcopy(style) merge_down_style.borders.top = 0 content_down_style = deepcopy(merge_down_style) content_down_style.alignment.horz = Alignment.HORZ_RIGHT content_down_style.font.height = 0x00CA for i, info in enumerate(withdraw_info): width = i * 25 work_excel.write_merge(2 + width, 2 + width, 0, 16, u'商户提现付款申请单', title_style) work_excel.write_merge(3 + width, 3 + width, 1, 5, info['supplier_type'], content_title_style) work_excel.write_merge(3 + width, 3 + width, 6, 16, info['supplier'], content_title_style) work_excel.write(4 + width, 0, u'收款单位名称', style) work_excel.write_merge(4 + width, 4 + width, 1, 5, info['company'], content_style) work_excel.write(4 + width, 6, u'申请提现帐号', style) work_excel.write_merge(4 + width, 4 + width, 7, 16, info['applier'], center_content_style) work_excel.write(5 + width, 0, u'开户银行', style) work_excel.write_merge(5 + width, 5 + width, 1, 5, info['bank'], content_style) work_excel.write(5 + width, 6, u'申请提现日期', style) work_excel.write_merge(5 + width, 5 + width, 7, 16, info['apply_time'], center_content_style) work_excel.write(6 + width, 0, u'银行帐号', style) work_excel.write_merge(6 + width, 6 + width, 1, 5, info['account'], content_style) work_excel.write(6 + width, 6, u'合同账期', style) work_excel.write_merge(6 + width, 6 + width, 7, 16, info['payment_day_type'], center_content_style) work_excel.write(7 + width, 0, u'付款用途', style) work_excel.write_merge(7 + width, 7 + width, 1, 5, u'商户提现', content_style) work_excel.write(7 + width, 6, u'付款日期', style) work_excel.write_merge(7 + width, 7 + width, 7, 16, info['date'], center_content_style) units = [u'千', u'百', u'十', u'万', u'千', u'百', u'十', u'元', u'角', u'分'] digit = list(str(info['money'])) digit.remove('.') digit.reverse() for j, unit in enumerate(units): work_excel.write(8 + width, 7 + j, unit, content_style) for index, value in enumerate(digit): work_excel.write(9 + width, 16 - index, value, content_style) work_excel.write(9 + width, 16 - index - 1, '¥', content_style) work_excel.write_merge(10 + width, 11 + width, 0, 0, u'备注事项', style) work_excel.write_merge(10 + width, 10 + width, 1, 6, '', content_style) work_excel.write_merge(10 + width, 10 + width, 7, 16, '', style) work_excel.write_merge(11 + width, 11 + width, 1, 16, info['print_info'], content_down_style) work_excel.write(8 + width, 0, u'付款金额', merge_up_style) work_excel.write(9 + width, 0, u'人民币(大写)', merge_down_style) work_excel.write_merge(8 + width, 9 + width, 1, 6, info['china_money'], content_style) work_excel.write_merge(13 + width, 13 + width, 0, 2, u'部门主管:', content_title_style) work_excel.write_merge(13 + width, 13 + width, 3, 5, u'财务经理:', content_title_style) work_excel.write_merge(13 + width, 13 + width, 6, 16, u'公司总经理:', content_title_style) for row in range(2, 12): work_excel.row(row + width).height_mismatch = 1 work_excel.row(row + width).height = 478 work_excel.row(2 + width).height_mismatch = 1 work_excel.row(2 + width).height = 1000 work_excel.col(0).width = 3871 work_excel.col(1).width = 1771 work_excel.col(2).width = 1509 work_excel.col(3).width = 2348 work_excel.col(4).width = 840 work_excel.col(5).width = 3241 work_excel.col(6).width = 3441 for col in range(7, 17): work_excel.col(col).width = 709
def generate_leave_record_report_file(employees, start_date, end_date, prefix=''): queryset = get_reqs_in_period(start_date, end_date) template = open_workbook(settings.REPORT_TEMPLATE + r'leave record report.xls', formatting_info=True) wb = copy(template) lrws = wb.get_sheet(0) left_center = Alignment() left_center.horz = Alignment.HORZ_LEFT left_center.vert = Alignment.VERT_CENTER center_center = Alignment() center_center.horz = Alignment.HORZ_CENTER center_center.vert = Alignment.VERT_CENTER style = easyxf( 'font: name Arial;' 'borders: left thin, right thin, top thin, bottom thin;' ) style_bg = easyxf( 'font: name Arial;' 'borders: left thin, right thin, top thin, bottom thin;' ) style_bg.alignment = left_center style.alignment = left_center pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x34 pattern2 = Pattern() pattern2.pattern = Pattern.SOLID_PATTERN pattern2.pattern_fore_colour = 0x50 style_bg.pattern = pattern1 original_index = start_index = curr_index = 2 for employee in employees: name_rows = queryset.filter(employee=employee).count() if name_rows > 0: leavetypes = list(LeaveType.objects.all()) for leavetype in leavetypes: leaverequests = queryset.filter(employee=employee, leave_type=leavetype) leavetype_rows = len(leaverequests) #print 'leavetype_rows', leavetype_rows if leavetype_rows > 0: if style_bg.pattern == pattern1: style_bg.pattern = pattern2 else: style_bg.pattern = pattern1 duration = 0.0 for l in leaverequests: periods = l.period_set.all().filter( Q(start__range=(start_date, end_date))| Q(end__range=(start_date, end_date))) for p in periods: p_start = max(p.start, start_date) p_end = min(p.end, end_date) lrws.write( curr_index, 2, \ Period(leave_request=l, start=p_start, end=p_end).__unicode__(), \ style_bg) curr_index += 1 duration += duration_days(p_start, p_end) style_bg.alignment = center_center lrws.write_merge(start_index, curr_index - 1, 3, 3, duration, style_bg) style_bg.alignment = left_center lrws.write_merge(start_index, curr_index - 1, 1, 1, leavetype.name, style_bg) start_index = curr_index lrws.write_merge(original_index, curr_index - 1, 0, 0, employee.display_name, style) original_index = start_index = curr_index filename = settings.REPORT_FILES + prefix + 'leaverecordreport-%s-%s.xls' % (start_date.strftime('%Y_%m_%d'), end_date.strftime('%Y_%m_%d')) wb.save(filename) return filename
def pf_upload_report(self, cr, uid, ids, context=None): # req = urllib2.Request('http://www.voidspace.org.uk') # response = urllib2.urlopen(req) # the_page = response.read() # print " html===========================",the_page f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('PF UPLOAD') fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x17 style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 250 fnt2.bold = False align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x09 style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 275 fnt3.bold = False align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x02 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x09 style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x16 style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 275 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() # align_content5.horz= Alignment.HORZ_JUSTIFIED borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() # align5.horz = Alignment.HORZ_JUSTIFIED align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 ws.row(0).height = 1500 ws.col(0).width = 6500 ws.col(1).width = 5000 ws.col(2).width = 10000 ws.col(3).width = 4000 ws.col(4).width = 4000 ws.col(5).width = 4500 ws.col(6).width = 4000 ws.col(7).width = 5500 ws.col(8).width = 4000 ws.col(9).width = 4500 ws.col(10).width = 4000 ws.col(11).width = 4000 # ws.col(12).width = 6500 # ws.col(13).width = 6500 # ws.col(14).width = 6500 # ws.col(15).width = 6500 # ws.col(16).width = 4500 # ws.col(17).width = 7500 # ws.col(18).width = 3000 # ws.col(19).width = 4000 # ws.col(20).width = 3000 # ws.col(21).width = 6500 # ws.col(22).width = 6500 # ws.col(23).width = 7500 # ws.col(24).width = 7500 # ws.col(25).width = 2500 # ws.col(26).width = 5000 # ws.col(27).width = 4000 ws.write(0, 0, 'Employee Pay Code', style_header) ws.write(0, 1, 'UAN Number', style_header) ws.write(0, 2, 'Member Name', style_header) ws.write(0, 3, 'Gross Wages', style_header) ws.write(0, 4, 'EPF Wages', style_header) ws.write(0, 5, 'EPS Wages', style_header) ws.write(0, 6, 'EDLI Wages', style_header) ws.write(0, 7, 'EE Share', style_header) ws.write(0, 8, 'EPS Contribution', style_header) ws.write(0, 9, 'ER Share', style_header) ws.write(0, 10, 'NCP Days', style_header) ws.write(0, 11, 'Refund', style_header) # ws.write(0,1,'Member ID',style_header) # ws.write(0,6,'EPF Contribution (EE Share) being remitted',style_header5) # ws.write(0,8,'EPS Contribution being remitted',style_header5) # ws.write(0,10,'Diff EPF and EPS Contribution (ER Share) being remitted',style_header5) # ws.write(0,15,'Arrear EPF Wages',style_header) # ws.write(0,16,'Arrear EPF EE Share',style_header) # ws.write(0,17,'Arrear EPF ER Share',style_header) # ws.write(0,18,'Arrear EPS',style_header) # ws.write(0,19,'Father / Husband Name',style_header) # ws.write(0,20,'Relationship with the Member',style_header5) # ws.write(0,21,'Date of Birth',style_header) # ws.write(0,22,'Gender',style_header) # ws.write(0,23,'Date of Joining EPF',style_header) # ws.write(0,24,'Date of Joining EPS',style_header) # ws.write(0,25,'Date of Exit From EPF',style_header) # ws.write(0,26,'Date of Exit From EPS',style_header) # ws.write(0,27,'Reason for Leaving',style_header5) this = self.browse(cr, uid, ids[0], context=context) month = this.month.month company_id = this.company_id.id year = this.month.year_id.id year_name = this.month.year_id.name emp_obj = self.pool.get('hr.employee') # pf_no=' ' emp_name = ' ' epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 diff_eps_epf = 0 full_date_month = [] total_epf_wages = 0 total_epf_cont = 0 total_eps_cont = 0 total_diff_calc = 0 total_diff_diff = 0 total_gross = 0 holiday_list = [] inact_list = [] inact_list1 = [] hr_list = [] i = 1 val_zero = 0 # epf_date_start='' if len(str(month)) == 1: start_date = str(year_name) + '-' + '0' + str(month) + '-' + '01' month_val = '0' + str(month) else: start_date = str(year_name) + '-' + str(month) + '-' + '01' month_val = str(month) if int(this.month.month) in [1, 3, 5, 7, 8, 10, 12]: month_tup = 31 if int(this.month.month) in [4, 6, 9, 11]: month_tup = 30 if int(this.month.month) in [2]: if int(this.month.year_id.name) % 4 == 0: month_tup = 29 else: month_tup = 28 month_val = int(month_val) # month_tup = calendar.monthrange(year,month_val) end_date = str(year_name) + '-' + str(month_val) + '-' + str(month_tup) for month_date in rrule.rrule( rrule.DAILY, dtstart=datetime.strptime(start_date, '%Y-%m-%d'), until=datetime.strptime(end_date, '%Y-%m-%d')): month_date = datetime.strftime(month_date, "%Y-%m-%d") full_date_month.append(month_date) if this.company_id: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('type', '=', 'Employee')]) inact_ids = emp_obj.search(cr, uid, [('active', '=', False), ('company_id', '=', company_id), ('epf_tick', '=', True), ('epf_end_date', '>=', start_date), ('epf_end_date', '<=', end_date), ('type', '=', 'Employee')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if inact_ids: for val in inact_ids: inact_emp_browse = emp_obj.browse(cr, uid, val) if inact_emp_browse: inact_id = val inact_emp_name = inact_emp_browse.name inact_emp_sinid = inact_emp_browse.sinid inact_days_amount = 0 tup = (inact_id, inact_emp_name, inact_days_amount, inact_emp_sinid) inact_list.append(tup) inact_list1.append(val) if len(list_ids) == 1: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() else: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) temp_add_value1 = temp1 + inact_list1 emp_list = emp_obj.search(cr, uid, [('id', 'not in', temp_add_value1), ('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('doj', '<=', end_date), ('type', '=', 'Employee')]) if emp_list: for valll in emp_list: emp_list_ids = emp_obj.browse(cr, uid, valll) hr_id = valll hr_name = emp_list_ids.name hr_sinid = emp_list_ids.sinid hr_days_amount = 0 tup = (hr_id, hr_name, hr_days_amount, hr_sinid) hr_list.append(tup) if temp: temp_add_value = temp + inact_list + hr_list temp_add_value = sorted(temp_add_value, key=lambda x: x[3]) for val in temp_add_value: epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 epf_gross_wages = 0 ws.row(i).height = 500 emp_browse = emp_obj.browse(cr, uid, val[0]) # emp_pf=emp_browse.pf_number uan_number = emp_browse.uan if val[2]: gross = val[7] + val[8] else: gross = 0 # if '/' in emp_pf : # emp_pf=emp_pf.upper().split('/') # emp_pf_len=len(emp_pf)-1 # if emp_pf : # pf_no=emp_pf[emp_pf_len] if len(val) > 5: epf_cont = val[4] eps_cont = val[5] calc_eps_epf = val[6] epf_gross_wages = round(val[2], 0) diff_eps_epf = (epf_cont - eps_cont) date_of_join_epf = emp_browse.epf_start_date date_of_exit_epf = emp_browse.epf_end_date # f_name='' # relation_member = '' # date_of_birth='' # gender= '' # epf_date_start='' # leave_reason=' ' # epf_end_date=' ' # if date_of_join_epf : # if date_of_join_epf in full_date_month : # epf_date_start=datetime.strptime(date_of_join_epf,'%Y-%m-%d') # epf_date_start=epf_date_start.strftime('%d-%m-%Y') # query1 = cr.execute("select name from family where employee_id='"+str(val[0])+"' and relation='Father' ") # temp1 = cr.fetchall() # if temp1 : # f_name = temp1[0][0] # relation_member='F' # date_of_birth=emp_browse.birthday # date_of_birth=datetime.strptime(date_of_birth,'%Y-%m-%d') # date_of_birth=date_of_birth.strftime('%d-%m-%Y') # gender=emp_browse.gender # gender=gender.upper()[0] # if date_of_exit_epf : # leave_reason=emp_browse.leaving_reason[0] # epf_end_date=datetime.strptime(date_of_exit_epf,'%Y-%m-%d') # epf_end_date=epf_end_date.strftime('%d-%m-%Y') # if date_of_join_epf and not date_of_exit_epf : # leave_reason='&' # epf_end_date='&' # if not date_of_join_epf and date_of_exit_epf : # epf_date_start='&' total_epf_wages += val[2] total_epf_cont += epf_cont total_eps_cont += eps_cont total_diff_calc += calc_eps_epf total_diff_diff += diff_eps_epf total_gross += gross ws.write(i, 0, (val[3]), style_header2) ws.write(i, 1, uan_number, style_header2) # ws.write(i,1,pf_no,style_header2) ws.write(i, 2, (val[1]), style_header2) ws.write(i, 3, gross, style_header2) ws.write(i, 4, epf_gross_wages, style_header2) ws.write(i, 5, epf_gross_wages, style_header2) ws.write(i, 6, epf_gross_wages, style_header2) ws.write(i, 7, epf_cont, style_header2) # ws.write(i,6,epf_cont,style_header2) ws.write(i, 8, eps_cont, style_header2) # ws.write(i,8,eps_cont,style_header2) ws.write(i, 9, calc_eps_epf, style_header2) # ws.write(i,10,diff_eps_epf,style_header2) ws.write(i, 10, val_zero, style_header2) ws.write(i, 11, val_zero, style_header2) # ws.write(i,15,val_zero,style_header2) # ws.write(i,16,val_zero,style_header2) # ws.write(i,17,val_zero,style_header2) # ws.write(i,18,val_zero,style_header2) # ws.write(i,19,f_name and f_name or '&',style_header2) # ws.write(i,20,relation_member and relation_member or '&',style_header2) # ws.write(i,21,date_of_birth and date_of_birth or '&',style_header2) # ws.write(i,22,gender and gender or '&',style_header2) # ws.write(i,23,epf_date_start and epf_date_start or '&',style_header2) # ws.write(i,24,epf_date_start and epf_date_start or '&',style_header2) # ws.write(i,25,epf_end_date,style_header2) # ws.write(i,26,epf_end_date,style_header2) # ws.write(i,27,leave_reason,style_header2) i = i + 1 ws.row(i + 1).height = 500 ws.write(i + 1, 2, 'TOTAL', style_header1) ws.write(i + 1, 3, total_gross, style_header1) ws.write(i + 1, 4, total_epf_wages, style_header1) ws.write(i + 1, 5, total_epf_wages, style_header1) ws.write(i + 1, 6, total_epf_wages, style_header1) ws.write(i + 1, 7, total_epf_cont, style_header1) ws.write(i + 1, 8, total_eps_cont, style_header1) ws.write(i + 1, 9, total_diff_calc, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) pf_upload_report = self.write(cr, uid, ids, { 'export_data': out, 'filename': 'employee_pf_upload.xls' }, context=context) return pf_upload_report
def print_report_excel(self, cr, uid, ids, context=None): if context is None: context = {} asset_obj = self.pool.get('account.asset.asset') asset_categ_obj = self.pool.get('account.asset.category') company_obj = self.pool.get('res.company') year_obj = self.pool.get('account.fiscalyear') period_obj = self.pool.get('account.period') asset_depreciation_line_obj = self.pool.get( 'account.asset.depreciation.line') fnt = Font() fnt.name = 'Arial' fnt.height = 220 fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 220 fnt1.bold = 'on' # Define the font attributes for header content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 220 align_content = Alignment() align_content.horz = Alignment.HORZ_LEFT borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 # The text should be centrally aligned align = Alignment() align.horz = Alignment.HORZ_LEFT align.vert = Alignment.VERT_TOP align.wrap = Alignment.WRAP_AT_RIGHT # The text should be right aligned align1 = Alignment() align1.horz = Alignment.HORZ_RIGHT align1.vert = Alignment.VERT_TOP align1.wrap = Alignment.WRAP_AT_RIGHT # The content should be left aligned align2 = Alignment() align2.horz = Alignment.HORZ_LEFT align2.vert = Alignment.VERT_TOP align2.wrap = Alignment.WRAP_AT_RIGHT # The content should be right aligned align3 = Alignment() align3.horz = Alignment.HORZ_RIGHT align3.vert = Alignment.VERT_TOP align3.wrap = Alignment.WRAP_AT_RIGHT # We set the backgroundcolour here pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x1F # We set the backgroundcolour here pattern1 = Pattern() pattern1.pattern = Pattern.SOLID_PATTERN pattern1.pattern_fore_colour = 0x17 # We set the backgroundcolour here pattern2 = Pattern() pattern2.pattern = Pattern.SOLID_PATTERN pattern2.pattern_fore_colour = 0xFF # We set the backgroundcolour here pattern3 = Pattern() pattern3.pattern = Pattern.SOLID_PATTERN pattern3.pattern_fore_colour = 0xFF # apply the above settings to the row(0) header style_header = XFStyle() style_header.font = fnt1 style_header.pattern = pattern style_header.borders = borders style_header.alignment = align style_header_right = XFStyle() style_header_right.font = fnt1 style_header_right.pattern = pattern style_header_right.borders = borders style_header_right.alignment = align3 # apply the above settings to the row(1) header style_header1 = XFStyle() style_header1.font = fnt style_header1.pattern = pattern1 style_header1.borders = borders style_header1.alignment = align1 # apply the above settings to the content style_content_left = XFStyle() style_content_left.font = fnt style_content_left.pattern = pattern2 style_content_left.borders = borders style_content_left.alignment = align2 style_content_right = XFStyle() style_content_right.font = fnt style_content_right.pattern = pattern3 style_content_right.borders = borders style_content_right.alignment = align3 style_content = XFStyle() style_content.alignment = align_content style_content.font = content_fnt wb = Workbook() ws = wb.add_sheet('Sheet 1') ws.row(0).height = 500 ws.col(0).width = 6500 ws.col(1).width = 6500 ws.col(2).width = 6500 ws.col(3).width = 6500 ws.col(4).width = 6500 ws.col(5).width = 6500 ws.col(6).width = 6500 ws.col(7).width = 6500 ws.col(8).width = 6500 ws.col(9).width = 6500 ws.col(10).width = 6500 ws.col(11).width = 6500 ws.col(12).width = 6500 ws.col(13).width = 6500 style = xlwt.easyxf('font: bold on,height 240,color_index 0X36;' 'align: horiz center;') ws.write(0, 2, 'Asset Report', style) data = self.read(cr, uid, ids, [], context=context)[0] company = company_obj.browse(cr, uid, data['company_id'][0]) if data['fiscalyear_id']: year = year_obj.browse(cr, uid, data['fiscalyear_id'][0]).name else: year = '' filter = '' if data['filter'] == 'filter_date': filter = 'Dates' elif data['filter'] == 'filter_period': filter = 'Periods' else: filter = 'No Filters' from_date = False to_date = False ws.row(2).height = 500 ws.write(2, 0, 'Company Name', style_header) ws.write(2, 1, company.name, style_header) ws.row(3).height = 500 ws.write(3, 0, 'Report Run', style_header) ws.write(3, 1, time.strftime('%Y-%m-%d %H:%M:%S'), style_header) ws.row(4).height = 500 ws.write(4, 0, 'Fiscal Year', style_header) ws.write(4, 1, year, style_header) ws.row(5).height = 500 ws.write(5, 0, 'Filters', style_header) ws.write(5, 1, filter, style_header) ws.row(6).height = 500 if data['filter'] == 'filter_period': from_period_id = data['period_from'][0] to_period_id = data['period_to'][0] if from_period_id and to_period_id: from_date = period_obj.browse(cr, uid, from_period_id).date_start to_date = period_obj.browse(cr, uid, to_period_id).date_stop elif from_period_id and to_period_id: from_date = period_obj.browse(cr, uid, from_period_id).date_start to_date = period_obj.browse(cr, uid, from_period_id).date_stop ws.write(6, 0, 'Start Period', style_header) ws.write(6, 1, data['period_from'][1], style_header) ws.write(7, 0, 'End Period', style_header) ws.write(7, 1, data['period_to'][1], style_header) elif data['filter'] == 'filter_date': from_date = data['date_from'] to_date = data['date_to'] ws.write(6, 0, 'Start Date', style_header) ws.write( 6, 1, datetime.strptime(data['date_from'], '%Y-%m-%d').strftime('%m/%d/%Y'), style_header) ws.write(7, 0, 'End Date', style_header) ws.write( 7, 1, datetime.strptime(data['date_to'], '%Y-%m-%d').strftime('%m/%d/%Y'), style_header) row = 10 from_date = False to_date = False if data['filter'] == 'filter_period': from_period_id = data['period_from'][0] to_period_id = data['period_to'][0] if from_period_id and to_period_id: from_date = period_obj.browse(cr, uid, from_period_id).date_start to_date = period_obj.browse(cr, uid, to_period_id).date_stop elif from_period_id and to_period_id: from_date = period_obj.browse(cr, uid, from_period_id).date_start to_date = period_obj.browse(cr, uid, from_period_id).date_stop elif data['filter'] == 'filter_date': from_date = data['date_from'] to_date = data['date_to'] else: if data['fiscalyear_id']: from_date = year_obj.browse( cr, uid, data['fiscalyear_id'][0]).date_start to_date = year_obj.browse(cr, uid, data['fiscalyear_id'][0]).date_stop else: pass if data['asset_categ_ids']: categories_ids = data['asset_categ_ids'] else: categories_ids = asset_categ_obj.search(cr, uid, []) if data['company_id']: company_ids = [data['company_id'][0]] else: company_ids = company_obj.search(cr, uid, []) for categ_id in categories_ids: ws.row(row).height = 500 domain = [] if from_date and to_date: #domain.append(('purchase_date', '>=', from_date)) #domain.append(('purchase_date', '<=', to_date)) pass domain.append(('category_id', '=', categ_id)) domain.append(('company_id', 'in', company_ids)) domain.append(('state', '=', 'open')) asset_ids = asset_obj.search(cr, uid, domain) asset_categ = asset_categ_obj.browse(cr, uid, categ_id) count = 1 ws.row(row).height = 800 ws.write(row, 0, 'Asset Category:', style_header) ws.write(row, 1, asset_categ.name, style_header) row += 1 ws.row(row).height = 700 ws.write(row, 0, 'No', style_header) ws.write(row, 1, 'Asset Tag No', style_header) ws.write(row, 2, 'Asset Description', style_header) ws.write(row, 3, 'Location', style_header) ws.write(row, 4, 'Document Reference', style_header) ws.write(row, 5, 'Requisition Date', style_header) ws.write(row, 6, 'Requisition Value', style_header) ws.write(row, 7, 'Salvage Value', style_header) ws.write(row, 8, 'Depreciation Method', style_header) ws.write(row, 9, 'Number of Usage', style_header) ws.write(row, 10, 'B/F Accumulated Depreciation', style_header) ws.write(row, 11, 'Depreciation', style_header) ws.write(row, 12, 'Accumulated Depreciation', style_header) ws.write(row, 13, 'Net Book Value', style_header) row += 1 total_req_val = 0.0 total_sal_val = 0.0 total_bf_accum_depr = 0.0 total_next_depr = 0.0 total_accum_depr = 0.0 total_net_book = 0.0 for asset in asset_obj.browse(cr, uid, asset_ids): bf_accum_depr = 0.0 next_amount_depr = 0.0 accum_depr_val = 0.0 net_book_val = 0.0 method = '' if asset.method == 'linear': method = 'Linear' elif asset.method == 'degressive': method = 'Degressive' # location = '' # # if asset.move_id and asset.move_id.location_dest_id: # location = asset.move_id.location_dest_id.name ws.row(row).height = 500 ws.write(row, 0, count, style_content_left) ws.write(row, 1, asset.code or '', style_content_left) ws.write(row, 2, asset.product_desc or '', style_content_left) ws.write(row, 3, asset.asset_location or '', style_content_left) ws.write(row, 4, asset.picking_id.name or '', style_content_left) row_date = datetime.strptime( asset.purchase_date, '%Y-%m-%d').strftime('%m/%d/%Y') or '' ws.write(row, 5, row_date, style_content_left) ws.write(row, 6, asset.purchase_value or 0.0, style_content_right) ws.write(row, 7, asset.salvage_value or 0.0, style_content_right) ws.write(row, 8, method or '', style_content_left) ws.write(row, 9, asset.method_number or '', style_content_left) if from_date and to_date: period_id = period_obj.find(cr, uid, dt=from_date) bf_line_ids = asset_depreciation_line_obj.search( cr, uid, [('asset_id', '=', asset.id), ('effective_period_id', '=', period_id[0])]) if bf_line_ids: asset_bf_line = asset_depreciation_line_obj.browse( cr, uid, bf_line_ids[0]) bf_accum_depr = asset_bf_line.depreciated_value else: bf_line_ids = asset_depreciation_line_obj.search( cr, uid, [('asset_id', '=', asset.id), ('move_check', '=', True)], order='effective_date desc', limit=1) if bf_line_ids: asset_bf_line = asset_depreciation_line_obj.browse( cr, uid, bf_line_ids[0]) bf_accum_depr = asset_bf_line.depreciated_value #To find current depreciation value: Need to search depreciation line on depreciation board for respected "Amount already depreciated". # Here we will match "Amount already depreciated" (bf_accum_depr) in depreciation line and matched depreciation line's current depreciation will be taken. if from_date and to_date: period_id = period_obj.find(cr, uid, dt=from_date) bf_line_ids = asset_depreciation_line_obj.search( cr, uid, [('asset_id', '=', asset.id), ('effective_date', '>=', from_date), ('effective_date', '<=', to_date), ('move_check', '=', True)]) if bf_line_ids: for asset_bf_line in asset_depreciation_line_obj.browse( cr, uid, bf_line_ids): next_amount_depr += asset_bf_line.amount else: period_id = period_obj.find(cr, uid, dt=from_date) bf_line_ids = asset_depreciation_line_obj.search( cr, uid, [('asset_id', '=', asset.id), ('move_check', '=', True)]) if bf_line_ids: for asset_bf_line in asset_depreciation_line_obj.browse( cr, uid, bf_line_ids): next_amount_depr += asset_bf_line.amount ws.write(row, 10, bf_accum_depr, style_content_right) ws.write(row, 11, next_amount_depr, style_content_right) accum_depr_val = (bf_accum_depr + next_amount_depr) ws.write(row, 12, accum_depr_val, style_content_right) net_book_val = (asset.purchase_value - accum_depr_val) ws.write(row, 13, net_book_val, style_content_right) total_req_val += asset.purchase_value total_sal_val += asset.salvage_value total_bf_accum_depr += bf_accum_depr total_next_depr += next_amount_depr total_accum_depr += accum_depr_val total_net_book += net_book_val row += 1 count += 1 ws.row(row).height = 500 ws.write(row, 0, '', style_header_right) ws.write(row, 1, '', style_header_right) ws.write(row, 2, 'Total', style_header_right) ws.write(row, 3, '', style_header_right) ws.write(row, 4, '', style_header_right) ws.write(row, 5, '', style_header_right) ws.write(row, 6, total_req_val, style_header_right) ws.write(row, 7, total_sal_val, style_header_right) ws.write(row, 8, '', style_header_right) ws.write(row, 9, '', style_header_right) ws.write(row, 10, total_bf_accum_depr, style_header_right) ws.write(row, 11, total_next_depr, style_header_right) ws.write(row, 12, total_accum_depr, style_header_right) ws.write(row, 13, total_net_book, style_header_right) row += 3 f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) return { 'name': 'Assets Register Reports', 'res_model': 'xls.report.wizard', 'type': 'ir.actions.act_window', 'view_type': 'form', 'view_mode': 'form', 'target': 'new', 'nodestroy': True, 'context': { 'data': out, 'name': 'Asset Register Report.xls' } }
def write_sheet(self, data, sheet_name, print_to_screen=False): '''Write a very simple table to a new sheet in a spreadsheet, Optionally, print the table to the screen''' # most cells al = Alignment() al.horz = Alignment.HORZ_RIGHT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.height = 9 * 20 # 9 pt style = XFStyle() style.font = font style.alignment = al # tops cells al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.height = 9 * 20 # 9 pt style_top = XFStyle() style_top.font = font style_top.alignment = al # left cells al = Alignment() al.horz = Alignment.HORZ_LEFT al.vert = Alignment.VERT_CENTER font = Font() font.name = 'Arial' font.bold = True font.italic = True font.height = 9 * 20 # 9 pt style_left = XFStyle() style_left.font = font style_left.alignment = al ws = self.add_sheet(sheet_name) for i, row in enumerate(data): for j, cell in enumerate(row): borders = Borders() if i == 0: borders.top = 1 borders.bottom = 2 if i == len(row) - 1: borders.bottom = 1 if j == 0: borders.left = 1 borders.right = 1 if j == len(row) - 1: borders.right = 1 if j == 0: _style = style_left elif i == 0: _style = style_top else: _style = style _style.borders = borders ws.write(i + 1, j + 1, cell, _style) if print_to_screen: print print_table(data, sheet_name, bold=True)
def pf_upload_report(self, cr, uid, ids, context=None): f_name = '' d_name = '' wb = Workbook() ws = wb.add_sheet('CONTRACTOR PF UPLOAD') fnt1 = Font() fnt1.name = 'Arial' fnt1.height = 300 fnt1.bold = True align_content1 = Alignment() align_content1.horz = Alignment.HORZ_CENTER borders1 = Borders() borders1.left = 0x02 borders1.right = 0x02 borders1.top = 0x02 borders1.bottom = 0x02 align1 = Alignment() align1.horz = Alignment.HORZ_CENTER align1.vert = Alignment.VERT_CENTER pattern1 = Pattern() pattern1.pattern1 = Pattern.SOLID_PATTERN pattern1.pattern1_fore_colour = 0x17 style_header1 = XFStyle() style_header1.font = fnt1 style_header1.pattern = pattern1 style_header1.borders = borders1 style_header1.alignment = align1 fnt2 = Font() fnt2.name = 'Arial' fnt2.height = 250 fnt2.bold = False align_content2 = Alignment() align_content2.horz = Alignment.HORZ_CENTER borders2 = Borders() borders2.left = 0x02 borders2.right = 0x02 borders2.top = 0x02 borders2.bottom = 0x02 align2 = Alignment() align2.horz = Alignment.HORZ_CENTER align2.vert = Alignment.VERT_CENTER pattern2 = Pattern() pattern2.pattern2 = Pattern.SOLID_PATTERN pattern2.pattern2_fore_colour = 0x09 style_header2 = XFStyle() style_header2.font = fnt2 style_header2.pattern = pattern2 style_header2.borders = borders2 style_header2.alignment = align2 fnt3 = Font() fnt3.name = 'Arial' fnt3.height = 275 fnt3.bold = False align_content3 = Alignment() align_content3.horz = Alignment.HORZ_CENTER borders3 = Borders() borders3.left = 0x02 borders3.right = 0x02 borders3.top = 0x02 borders3.bottom = 0x02 align3 = Alignment() align3.horz = Alignment.HORZ_CENTER align3.vert = Alignment.VERT_CENTER pattern3 = Pattern() pattern3.pattern3 = Pattern.SOLID_PATTERN pattern3.pattern3_fore_colour = 0x09 style_header3 = XFStyle() style_header3.font = fnt3 style_header3.pattern = pattern3 style_header3.borders = borders3 style_header3.alignment = align3 fnt = Font() fnt.name = 'Arial' fnt.height = 275 content_fnt = Font() content_fnt.name = 'Arial' content_fnt.height = 150 align_content = Alignment() align_content.horz = Alignment.HORZ_CENTER borders = Borders() borders.left = 0x02 borders.right = 0x02 borders.top = 0x02 borders.bottom = 0x02 align = Alignment() align.horz = Alignment.HORZ_CENTER align.vert = Alignment.VERT_CENTER pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0x16 style_header = XFStyle() style_header.font = fnt style_header.pattern = pattern style_header.borders = borders style_header.alignment = align fnt5 = Font() fnt5.name = 'Arial' fnt5.height = 275 content_fnt5 = Font() content_fnt5.name = 'Arial' content_fnt5.height = 150 align_content5 = Alignment() # align_content5.horz= Alignment.HORZ_JUSTIFIED borders5 = Borders() borders5.left = 0x02 borders5.right = 0x02 borders5.top = 0x02 borders5.bottom = 0x02 align5 = Alignment() # align5.horz = Alignment.HORZ_JUSTIFIED align5.vert = Alignment.VERT_JUSTIFIED pattern5 = Pattern() pattern5.pattern = Pattern.SOLID_PATTERN pattern5.pattern_fore_colour = 0x16 style_header5 = XFStyle() style_header5.font = fnt5 style_header5.pattern = pattern5 style_header5.borders = borders5 style_header5.alignment = align5 ws.row(0).height = 1500 ws.col(0).width = 6500 ws.col(1).width = 5000 ws.col(2).width = 10000 ws.col(3).width = 4000 ws.col(4).width = 4000 ws.col(5).width = 4500 ws.col(6).width = 4000 ws.col(7).width = 5500 ws.col(8).width = 4000 ws.col(9).width = 4500 ws.col(10).width = 4000 ws.col(11).width = 4000 ws.write(0, 0, 'Employee Pay Code', style_header) ws.write(0, 1, 'UAN Number', style_header) ws.write(0, 2, 'Member Name', style_header) ws.write(0, 3, 'Gross Wages', style_header) ws.write(0, 4, 'EPF Wages', style_header) ws.write(0, 5, 'EPS Wages', style_header) ws.write(0, 6, 'EDLI Wages', style_header) ws.write(0, 7, 'EE Share', style_header) ws.write(0, 8, 'EPS Contribution', style_header) ws.write(0, 9, 'ER Share', style_header) ws.write(0, 10, 'NCP Days', style_header) ws.write(0, 11, 'Refund', style_header) this = self.browse(cr, uid, ids[0], context=context) month = this.month.month company_id = this.company_id.id year = this.month.year_id.id year_name = this.month.year_id.name partner_id = this.partner_id.id emp_obj = self.pool.get('hr.employee') # pf_no=' ' emp_name = ' ' epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 diff_eps_epf = 0 full_date_month = [] total_epf_wages = 0 total_epf_cont = 0 total_eps_cont = 0 total_diff_calc = 0 total_diff_diff = 0 total_gross = 0 holiday_list = [] inact_list = [] inact_list1 = [] hr_list = [] i = 1 val_zero = 0 # epf_date_start='' if len(str(month)) == 1: start_date = str(year_name) + '-' + '0' + str(month) + '-' + '01' month_val = '0' + str(month) else: start_date = str(year_name) + '-' + str(month) + '-' + '01' month_val = str(month) if int(this.month.month) in [1, 3, 5, 7, 8, 10, 12]: month_tup = 31 if int(this.month.month) in [4, 6, 9, 11]: month_tup = 30 if int(this.month.month) in [2]: if int(this.month.year_id.name) % 4 == 0: month_tup = 29 else: month_tup = 28 month_val = int(month_val) # month_tup = calendar.monthrange(year,month_val) end_date = str(year_name) + '-' + str(month_val) + '-' + str(month_tup) # month_val=int(month_val) # month_tup = calendar.monthrange(year,month_val) # end_date = str(year_name) + '-' + str(month_val) + '-' + str(month_tup[1]) for month_date in rrule.rrule( rrule.DAILY, dtstart=datetime.strptime(start_date, '%Y-%m-%d'), until=datetime.strptime(end_date, '%Y-%m-%d')): month_date = datetime.strftime(month_date, "%Y-%m-%d") full_date_month.append(month_date) if this.company_id: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('type', '=', 'Contractor')]) inact_ids = emp_obj.search(cr, uid, [('active', '=', False), ('company_id', '=', company_id), ('epf_tick', '=', True), ('epf_end_date', '>=', start_date), ('epf_end_date', '<=', end_date), ('type', '=', 'Contractor')]) elif this.partner_id: list_ids = emp_obj.search(cr, uid, [('active', '=', True), ('partner_id', '=', partner_id), ('epf_tick', '=', True), ('type', '=', 'Contractor')]) inact_ids = emp_obj.search(cr, uid, [('active', '=', False), ('partner_id', '=', partner_id), ('epf_tick', '=', True), ('epf_end_date', '>=', start_date), ('epf_end_date', '<=', end_date), ('type', '=', 'Contractor')]) if len(list_ids) == 0: raise osv.except_osv(('Warning !'), ("Record Not Found !!!")) if inact_ids: for val in inact_ids: inact_emp_browse = emp_obj.browse(cr, uid, val) if inact_emp_browse: inact_id = val inact_emp_name = inact_emp_browse.name inact_emp_sinid = inact_emp_browse.sinid inact_days_amount = 0 tup = (inact_id, inact_emp_name, inact_days_amount, inact_emp_sinid) inact_list.append(tup) inact_list1.append(val) if len(list_ids) == 1: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id = '" + str( list_ids[0]) + "' and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() else: query = "select spl.employee_id,spl.employee_name,spl.gross,spl.sinid,spl.epf,spl.epf1,spl.epf2,spl.days_amount,spl.other_salary_amount from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query) temp = cr.fetchall() query1 = "select spl.employee_id from salary_payment_line as spl where spl.employee_id in " + str( tuple(list_ids)) + " and spl.month='" + str( month) + "' and spl.year_id='" + str( year) + "' and spl.epf <> 0.00 order by spl.sinid " cr.execute(query1) temp1 = cr.fetchall() if not temp: raise osv.except_osv(_('Warning !'), _("Record Not Found !!!")) temp_add_value1 = temp1 + inact_list1 emp_list = emp_obj.search(cr, uid, [('id', 'not in', temp_add_value1), ('active', '=', True), ('company_id', '=', company_id), ('epf_tick', '=', True), ('doj', '<=', end_date), ('type', '=', 'Contractor')]) if emp_list: for valll in emp_list: emp_list_ids = emp_obj.browse(cr, uid, valll) hr_id = valll hr_name = emp_list_ids.name hr_sinid = emp_list_ids.sinid hr_days_amount = 0 tup = (hr_id, hr_name, hr_days_amount, hr_sinid) hr_list.append(tup) if temp: temp_add_value = temp + inact_list + hr_list temp_add_value = sorted(temp_add_value, key=lambda x: x[3]) for val in temp_add_value: epf_cont = 0 eps_cont = 0 calc_eps_epf = 0 epf_gross_wages = 0 ws.row(i).height = 500 emp_browse = emp_obj.browse(cr, uid, val[0]) uan_number = emp_browse.uan if val[2]: gross = val[7] + val[8] else: gross = 0 if len(val) > 5: epf_cont = val[4] eps_cont = val[5] calc_eps_epf = val[6] epf_gross_wages = round(val[2], 0) diff_eps_epf = (epf_cont - eps_cont) date_of_join_epf = emp_browse.epf_start_date date_of_exit_epf = emp_browse.epf_end_date total_epf_wages += val[2] total_epf_cont += epf_cont total_eps_cont += eps_cont total_diff_calc += calc_eps_epf total_diff_diff += diff_eps_epf total_gross += gross ws.write(i, 0, (val[3]), style_header2) ws.write(i, 1, uan_number, style_header2) ws.write(i, 2, (val[1]), style_header2) ws.write(i, 3, gross, style_header2) ws.write(i, 4, epf_gross_wages, style_header2) ws.write(i, 5, epf_gross_wages, style_header2) ws.write(i, 6, epf_gross_wages, style_header2) ws.write(i, 7, epf_cont, style_header2) ws.write(i, 8, eps_cont, style_header2) ws.write(i, 9, calc_eps_epf, style_header2) ws.write(i, 10, val_zero, style_header2) ws.write(i, 11, val_zero, style_header2) i = i + 1 ws.row(i + 1).height = 500 ws.write(i + 1, 2, 'TOTAL', style_header1) ws.write(i + 1, 3, total_gross, style_header1) ws.write(i + 1, 4, total_epf_wages, style_header1) ws.write(i + 1, 5, total_epf_wages, style_header1) ws.write(i + 1, 6, total_epf_wages, style_header1) ws.write(i + 1, 7, total_epf_cont, style_header1) ws.write(i + 1, 8, total_eps_cont, style_header1) ws.write(i + 1, 9, total_diff_calc, style_header1) f = cStringIO.StringIO() wb.save(f) out = base64.encodestring(f.getvalue()) pf_upload_report = self.write( cr, uid, ids, { 'export_data': out, 'filename': 'contractor_pf_upload.xls' }, context=context) return pf_upload_report
if station in stations: continue stations.append(station) if len(stations) == 0: print "No stations to process. Exiting..." sys.exit(0) wb = Workbook() ws = wb.add_sheet('Certificate Data', cell_overwrite_ok=True) ws.write(PERIOD_START - 1,0,"Period") for i in range(0, len(periods)): ws.write(PERIOD_START + i, 0, periods[i]) al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER title_style = XFStyle() title_style.alignment = al print "\nTotal of %d stations to process\n" % len(stations) for i in range(0, len(stations)): s = stations[i] col = 1 + 5 * i print " ", s # add headers ws.write(PERIOD_START - 1, col, "Installed Capacity") ws.write(PERIOD_START - 1, col + 1, "RO Certificates") ws.write(PERIOD_START - 1, col + 2, "RO Factor")
def test(theseargs): parser = argparse.ArgumentParser(description='Download bulk information from Ofgem to produce an Excel spreadsheet') parser.add_argument('--start', action='store', required=True, help='Period to start from (MMM-YYYY)') parser.add_argument('--end', action='store', required=True, help='Period to finish on (MMM-YYYY)') parser.add_argument('--scheme', action='store', default='RO', help='Scheme to get certificates for') parser.add_argument('--filename', action='store', default='certificates.xls', help='Filename to export to') parser.add_argument('--name', action='store', default=None, help='Part of a name of generation station (or name fragments for several stations, separated by commas)') args = parser.parse_args(args=theseargs) (start_year, start_month) = get_period(args.start) (end_year, end_month) = get_period(args.end) if not args.filename.endswith('.xls'): args.filename += '.xls' periods = [] for yy in range(start_year, end_year + 1): mm = start_month if start_year == yy else 1 mm2 = end_month if end_year == yy else 12 for m in range(mm, mm2+1): periods.append(date(yy,m,1).strftime("%b-%Y")) station = args.name if station is None: stations = [] while (True): station = input("Enter a station name (or blank to finish)") if station.strip() == '': break if ',' in station: for s in station.strip().split(','): s = s.strip() if s in stations: continue stations.append(s) else: station = station.strip() if station in stations: continue stations.append(station) else: stations = station.strip().split(',') if len(stations) == 0: print("No stations to process. Exiting...") sys.exit(0) wb = Workbook() ws = wb.add_sheet('Certificate Data', cell_overwrite_ok=True) ws.write(PERIOD_START - 1,0,"Period") for i in range(0, len(periods)): ws.write(PERIOD_START + i, 0, periods[i]) al = Alignment() al.horz = Alignment.HORZ_CENTER al.vert = Alignment.VERT_CENTER title_style = XFStyle() title_style.alignment = al print("\nTotal of %d stations to process\n" % len(stations)) for i in range(0, len(stations)): s = stations[i] col = 1 + 5 * i print(" "+ s) # add headers ws.write(PERIOD_START - 1, col, "Installed Capacity") ws.write(PERIOD_START - 1, col + 1, "RO Certificates") ws.write(PERIOD_START - 1, col + 2, "RO Factor") ws.write(PERIOD_START - 1, col + 3, "REGO Certificates") ws.write(PERIOD_START - 1, col + 4, "REGO Factor") capacity = {} for scheme in ['RO','REGO']: offset = 1 if scheme == 'RO' else 3 ss = StationSearch() ss.filter_scheme(scheme) ss.filter_name(s) if not ss.get_data(): print("Unable to find any station with a name %s" % s) continue station = None # if more than one generator has matched, use the first WIND one if len(ss.stations) > 1: print('Several stations found that match %s:' % s) print(list(st.name for st in ss.stations)) print('The first wind station will be selected') for st in ss.stations: if 'wind' in st.technology.lower(): station = st break else: station = ss.stations[0] if station is None: print("Unable to get station data for '%s'" % s) continue # Write name ws.write_merge(PERIOD_START - 4, PERIOD_START - 4, col, col + 4, station.name, title_style) # add accreditation # if scheme == 'RO': ws.write_merge(PERIOD_START - 2, PERIOD_START - 2, col, col + 4, 'RO: ' + station.accreditation + ' [' + station.commission_dt.strftime("%d %b %Y") + ']', title_style) elif scheme == 'REGO': ws.write_merge(PERIOD_START - 3, PERIOD_START - 3, col, col + 4, 'REGO: ' + station.accreditation + ' [' + station.commission_dt.strftime("%d %b %Y") + ']', title_style) cs = CertificateSearch() cs.set_start_month(start_month) cs.set_start_year(start_year) cs.set_finish_month(end_month) cs.set_finish_year(end_year) cs.filter_accreditation(station.accreditation) #cs.filter_scheme(scheme) # seems to work ok without this, and REGOs break with it #cs.filter_status(['Issued','Redeemed','Expired']) # this doesn't work. And arguable whether expired should count if not cs.get_data(): print("Unable to get any certificate data :-(") continue data = {} for c in cs.certificates: # print(c.as_string()) if c.status in ['Revoked','Retired','Expired']: continue row = periods.index(c.period) + PERIOD_START if not c.period in capacity: ws.write(row, col, c.capacity) capacity[c.period] = True data[c.period] = data.get(c.period,0) + c.certs ws.write(row, col + offset + 1, c.factor) for p,val in viewitems(data): row = periods.index(p) + PERIOD_START ws.write(row, col + offset, val) print("\nComplete. Excel spreadsheet %s" % args.filename) wb.save(args.filename)
def styles(self): """ Style definitions for pivot tables (lazy property) @returns: dict of named XFStyle instances """ styles = self._styles if styles is None: from xlwt import Alignment, XFStyle # Alignments center = Alignment() center.horz = Alignment.HORZ_CENTER center.vert = Alignment.VERT_CENTER center.wrap = 1 centerleft = Alignment() centerleft.horz = Alignment.HORZ_LEFT centerleft.vert = Alignment.VERT_CENTER centerleft.wrap = 1 bottomcentered = Alignment() bottomcentered.horz = Alignment.HORZ_CENTER bottomcentered.vert = Alignment.VERT_BOTTOM bottomcentered.wrap = 1 bottomleft = Alignment() bottomleft.horz = Alignment.HORZ_LEFT bottomleft.vert = Alignment.VERT_BOTTOM bottomleft.wrap = 1 bottomright = Alignment() bottomright.horz = Alignment.HORZ_RIGHT bottomright.vert = Alignment.VERT_BOTTOM bottomright.wrap = 1 topleft = Alignment() topleft.horz = Alignment.HORZ_LEFT topleft.vert = Alignment.VERT_TOP topleft.wrap = 1 topright = Alignment() topright.horz = Alignment.HORZ_RIGHT topright.vert = Alignment.VERT_TOP topright.wrap = 1 # Styles twips = lambda pt: 20 * pt # Points to Twips def style(fontsize=10, bold=False, italic=False, align=None): """ XFStyle builder helper """ style = XFStyle() style.font.height = twips(fontsize) style.font.bold = bold style.font.italic = italic if align is not None: style.alignment = align return style self._styles = styles = { "default": style(align=topleft), "numeric": style(align=bottomright), "title": style(fontsize=14, bold=True, align=bottomleft), "subheader": style(fontsize=8, italic=True, align=bottomleft), "row_label": style(bold=True, align=topleft), "col_label": style(bold=True, align=bottomcentered), "fact_label": style(fontsize=13, bold=True, align=centerleft), "axis_title": style(fontsize=11, bold=True, align=center), "total": style(fontsize=11, bold=True, italic=True, align=topright), "total_left": style(fontsize=11, bold=True, italic=True, align=topleft), "total_right": style(fontsize=11, bold=True, italic=True, align=center), "grand_total": style(fontsize=12, bold=True, italic=True, align=topright), } return styles
def toupiaoExcel(request): subjectid = request.REQUEST.get('subjectid') optionid = request.REQUEST.get('optionid') subject = Subject.objects.get(pk=subjectid) if subject.isNoName: return HttpResponse(u'匿名投票不可导出') filename=u'%s'%subject.title if optionid: option = Option.objects.get(pk=optionid) if option.subject.pk!=subject.pk: raise Http404 filename+=u'——%s'%option.content response = HttpResponse(mimetype=u'application/ms-excel') filenames = u'%s.xls'%filename response['Content-Disposition'] = (u'attachment;filename=%s' % filenames).encode('utf-8') import xlwt from xlwt import Font, Alignment style1 = xlwt.XFStyle() font1 = Font() font1.height = 260 font1.name = u'仿宋' style1.font = font1 algn = Alignment() algn.horz = Alignment.HORZ_LEFT style1.alignment = algn style1.font = font1 style0 = xlwt.XFStyle() algn0 = Alignment() algn0.horz = Alignment.HORZ_CENTER font = Font() font.height = 220 font.bold = False font.name = u'仿宋' style0.alignment = algn0 style0.font = font wb = xlwt.Workbook() ws = wb.add_sheet(u"人员名单", cell_overwrite_ok=True) ws.header_str = filename ws.footer_str ='' rownum = 0 num=1 query=Toupiao.objects.filter(subject=subject) if optionid: query=query.filter(options=option) ws.write_merge(rownum,rownum,0,0,u'序号',style0) ws.write_merge(rownum,rownum,1,1,u'部门',style0) ws.write_merge(rownum,rownum,2,2,u'人员',style0) rownum+=1 for toupiao in query: ws.write_merge(rownum,rownum,0,0,num,style0) ws.write_merge(rownum,rownum,1,1,unicode(getattr(toupiao.user.person,'depate',u'无')),style0) ws.write_merge(rownum,rownum,2,2,toupiao.user.person.truename,style0) rownum+=1 num+=1 else: for opt in Option.objects.filter(subject=subject): ws.write_merge(rownum,rownum,0,2,u'选项:%s'%opt.content,style1) rownum+=1 ws.write_merge(rownum,rownum,0,0,u'序号',style0) ws.write_merge(rownum,rownum,1,1,u'部门',style0) ws.write_merge(rownum,rownum,2,2,u'人员',style0) rownum+=1 for toupiao in query.filter(options=opt): ws.write_merge(rownum,rownum,0,0,num,style0) ws.write_merge(rownum,rownum,1,1,unicode(getattr(toupiao.user.person,'depate',u'无')),style0) ws.write_merge(rownum,rownum,2,2,toupiao.user.person.truename,style0) rownum+=1 num+=1 width=256*10 ws.col(0).width = width ws.col(1).width = width*3 ws.col(2).width = width*2 wb.save(response) return response