def setupFormat(self):
headFont = Font()
headFont.bold = True
alignmentSetup = Alignment()
alignmentSetup.wrap = True
borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
self.header_style = XFStyle()
self.header_style.font = headFont
self.table_style = XFStyle()
self.table_style.borders = borders
self.table_style.alignment = alignmentSetup
def setupFormat(self):
headFont = Font()
headFont.bold = True
alignmentSetup = Alignment()
alignmentSetup.wrap = True
borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
self.header_style = XFStyle()
self.header_style.font = headFont
self.table_style = XFStyle()
self.table_style.borders = borders
self.table_style.alignment = alignmentSetup
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 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 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;
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;
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
borders_thin.right = Borders.THIN
borders_thin.bottom = Borders.THIN
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 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)
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' # headers font fntHeaders = Font() fntHeaders.height = 200 fntHeaders.name = 'Arial' # header styles styleHeaders = XFStyle() styleHeaders.font = fntHeaders styleHeaders.alignment.wrap = True styleHeaders.alignment = alignCenter
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'
}
}
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 alignWrap = Alignment() alignWrap.horz = Alignment.HORZ_CENTER alignWrap.wrap = Alignment.WRAP_AT_RIGHT # generic font, 11 point Calibri defaultFont = Font() defaultFont.height = 220 defaultFont.name = 'Arial' # default heades font headerFont = Font() headerFont.height = 220 headerFont.name = 'Arial' headerFont.bold = True # header styles defaultStyleHeaders = XFStyle() defaultStyleHeaders.font = headerFont