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
