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 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 output(self): # from xlrd import open_workbook from xlwt import Workbook, XFStyle, Borders, Alignment, Font, Pattern, Style, easyxf # from xlutils.copy import copy ''' rb = open_workbook(r"Templates\template.xls") wb = copy(rb) s = wb.get_sheet(0) s.write(0, 0, 'A1') wb.save(r"C:\Temp\nephro-planner\new.xls") ''' book = Workbook(encoding="utf-8") sheet = book.add_sheet(r"Feuille1") style_title = XFStyle() font_title = Font() font_title.name = "Comic Sans MS" font_title.height = 280 style_title.font = font_title style_title.alignment.horz = Alignment.HORZ_CENTER style_title.alignment.vert = Alignment.VERT_CENTER style_cell_bottom = XFStyle() borders_cell_bottom = Borders() borders_cell_bottom.bottom = Borders.MEDIUM style_cell_bottom.borders = borders_cell_bottom style_header = XFStyle() font_header = Font() font_header.bold = 1 font_header.name = "Arial Narrow" font_header.height = 240 style_header.font = font_header style_header.alignment.horz = Alignment.HORZ_CENTER style_header.alignment.vert = Alignment.VERT_CENTER borders_header = Borders() borders_header.top = Borders.MEDIUM borders_header.left = Borders.MEDIUM borders_header.bottom = Borders.MEDIUM borders_header.right = Borders.MEDIUM style_header.borders = borders_header style_sub_header = XFStyle() font_sub_header = Font() font_sub_header.name = "Arial Narrow" font_sub_header.height = 240 style_sub_header.font = font_sub_header style_sub_header.alignment.horz = Alignment.HORZ_CENTER style_sub_header.alignment.vert = Alignment.VERT_CENTER borders_sub_header = Borders() borders_sub_header.top = Borders.MEDIUM borders_sub_header.left = Borders.MEDIUM borders_sub_header.bottom = Borders.MEDIUM borders_sub_header.right = Borders.MEDIUM style_sub_header.borders = borders_sub_header style_date = XFStyle() font_date = Font() font_date.name = "Arial Narrow" font_date.height = 240 style_date.font = font_date style_date.alignment.horz = Alignment.HORZ_RIGHT style_date.alignment.vert = Alignment.VERT_CENTER borders_date = Borders() borders_date.left = Borders.MEDIUM style_date.borders = borders_date style_date_status = XFStyle() font_date_status = Font() font_date_status.name = "Arial Narrow" font_date_status.height = 240 style_date_status.font = font_date_status style_date_status.alignment.horz = Alignment.HORZ_LEFT style_date_status.alignment.vert = Alignment.VERT_CENTER borders_date_status = Borders() borders_date_status.right = Borders.MEDIUM style_date_status.borders = borders_date_status style_cell_normal = XFStyle() font_cell_normal = Font() font_cell_normal.name = "Arial Narrow" font_cell_normal.height = 220 style_cell_normal.font = font_cell_normal style_cell_normal.alignment.horz = Alignment.HORZ_CENTER style_cell_normal.alignment.vert = Alignment.VERT_CENTER style_cell_right = XFStyle() font_cell_right = Font() font_cell_right.name = "Arial Narrow" font_cell_right.height = 220 style_cell_right.font = font_cell_right style_cell_right.alignment.horz = Alignment.HORZ_CENTER style_cell_right.alignment.vert = Alignment.VERT_CENTER borders_cell_right = Borders() borders_cell_right.right = Borders.MEDIUM style_cell_right.borders = borders_cell_right style_cell_top = XFStyle() borders_cell_top = Borders() borders_cell_top.top = Borders.MEDIUM style_cell_top.borders = borders_cell_top column_offset = 1 date_column_offset = 2 row_offset = 1 table_width = 1 + 3 * len(Database.team()) # build titles sheet.write_merge(row_offset, row_offset, column_offset, table_width + 1, "POLE MEDECINE INTERNE", style_title) sheet.write_merge(row_offset + 1, row_offset + 1, column_offset, table_width + 1, "Service NEPHROLOGIE – HEMODIALYSE", style_title) sheet.write_merge( row_offset + 2, row_offset + 2, column_offset, table_width + 1, "Planning de {0} {1}".format( self.human_readable_months[self.month - 1], self.year), style_title) # patch date columns top borders sheet.write(row_offset + 5, column_offset, "", style_cell_bottom) sheet.write(row_offset + 5, column_offset + 1, "", style_cell_bottom) # build header and sub header for x in Database.team(): sheet.write_merge( row_offset + 4, row_offset + 4, column_offset + date_column_offset + 3 * (x.id - 1), column_offset + date_column_offset + 3 * x.id - 1, x.name, style_header) sheet.write(row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1), "M", style_sub_header) sheet.write( row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1) + 1, "AM", style_sub_header) sheet.write( row_offset + 5, column_offset + date_column_offset + 3 * (x.id - 1) + 2, "N", style_sub_header) ''' i = 40 for x in sorted(Style.colour_map): style = XFStyle() pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = Style.colour_map[x] style.pattern = pattern sheet.write(i, 1, x, style) i += 1 ''' pattern_pale_blue = Pattern() pattern_pale_blue.pattern = Pattern.SOLID_PATTERN pattern_pale_blue.pattern_fore_colour = Style.colour_map['pale_blue'] pattern_light_yellow = Pattern() pattern_light_yellow.pattern = Pattern.SOLID_PATTERN pattern_light_yellow.pattern_fore_colour = Style.colour_map[ 'light_yellow'] pattern_ice_blue = Pattern() pattern_ice_blue.pattern = Pattern.SOLID_PATTERN pattern_ice_blue.pattern_fore_colour = Style.colour_map['ice_blue'] pattern_light_green = Pattern() pattern_light_green.pattern = Pattern.SOLID_PATTERN pattern_light_green.pattern_fore_colour = Style.colour_map[ 'light_green'] pattern_ivory = Pattern() pattern_ivory.pattern = Pattern.SOLID_PATTERN pattern_ivory.pattern_fore_colour = Style.colour_map['ivory'] pattern_tan = Pattern() pattern_tan.pattern = Pattern.SOLID_PATTERN pattern_tan.pattern_fore_colour = Style.colour_map['tan'] pattern_gold = Pattern() pattern_gold.pattern = Pattern.SOLID_PATTERN pattern_gold.pattern_fore_colour = Style.colour_map['gold'] def __cell_colouration__(style, current_activity): if current_activity is Activity.CONSULTATION: style.pattern = pattern_pale_blue elif current_activity is Activity.DIALYSIS: style.pattern = pattern_ice_blue elif current_activity is Activity.NEPHROLOGY: style.pattern = pattern_light_green elif current_activity is Activity.OTHERS: style.pattern = pattern_tan elif current_activity is Activity.OBLIGATION: style.pattern = pattern_ivory elif current_activity is Activity.OBLIGATION_WEEKEND: style.pattern = pattern_light_yellow elif current_activity is Activity.OBLIGATION_HOLIDAY: style.pattern = pattern_gold else: style.pattern = Pattern() return style last_day = calendar.monthrange(self.year, self.month)[1] for x in range(1, last_day + 1): current_date = date(self.year, self.month, x) current_daily_planning = self.daily_plannings[current_date] # build date and date status columns sheet.write( row_offset + 5 + x, column_offset, "{0}. {1}".format( self.human_readable_days[current_daily_planning.weekday] [0:3].lower(), x), style_date) if current_daily_planning.weekday in [5, 6]: sheet.write(row_offset + 5 + x, column_offset + 1, "WK", style_date_status) elif not current_daily_planning.is_working_day: sheet.write(row_offset + 5 + x, column_offset + 1, "Férié", style_date_status) else: sheet.write(row_offset + 5 + x, column_offset + 1, "", style_date_status) ''' easyxf( 'font: bold 1, name Tahoma, height 160;' 'align: vertical center, horizontal center, wrap on;' 'borders: left thin, right thin, top thin, bottom thin;' 'pattern: pattern solid, pattern_fore_colour green, pattern_back_colour green' ) ''' # fill in month planning for y in Database.team(): current_activity = current_daily_planning.__get_activity__( TimeSlot.FIRST_SHIFT, y) sheet.write( row_offset + 5 + x, column_offset + date_column_offset + 3 * (y.id - 1), self.human_readable_activities[current_activity] if current_activity else "", __cell_colouration__(style_cell_normal, current_activity)) current_activity = current_daily_planning.__get_activity__( TimeSlot.SECOND_SHIFT, y) sheet.write( row_offset + 5 + x, column_offset + date_column_offset + 1 + 3 * (y.id - 1), self.human_readable_activities[current_activity] if current_activity else "", __cell_colouration__(style_cell_normal, current_activity)) current_activity = current_daily_planning.__get_activity__( TimeSlot.THIRD_SHIFT, y) sheet.write( row_offset + 5 + x, column_offset + date_column_offset + 2 + 3 * (y.id - 1), self.human_readable_activities[current_activity] if current_activity else "", __cell_colouration__(style_cell_right, current_activity)) # patch table bottom border for x in range(0, table_width + 1): sheet.write(row_offset + 5 + (last_day + 1), column_offset + x, "", style_cell_top) book.save(r"C:\Temp\nephro-planner\new.xls")
# Name: module1 # Purpose: # # Author: calebma # # Created: 15/04/2016 # Copyright: (c) calebma 2016 # Licence: <your licence> #------------------------------------------------------------------------------- from xlwt import Alignment, XFStyle, Borders, Font # ************************************************************************************** # generic styles # # default borders defaultBorders = Borders() defaultBorders.bottom = Borders.THIN defaultBorders.top = Borders.THIN defaultBorders.right = Borders.THIN defaultBorders.left = Borders.THIN # center align alignCenter = Alignment() alignCenter.horz = Alignment.HORZ_CENTER alignCenter.vert = Alignment.VERT_CENTER # Top alignment, should be used for all cells alignTop = Alignment() alignTop.vert = Alignment.VERT_TOP # header centered and wrapped