An Excel report is one of the basic reporting tools especially used for monitoring business processes that help to analyze the content very easily. In Odoo we mostly create excel reports using the Xlsx Writer package which is mostly used for Specific Formats Processing and in MS Office. Moreover, xlwt library is used to generate files that are compatible with Microsoft Excel version 95 to 2003 and is mostly used in Specific Formats Processing, Office, HTTP, Database, Financial, Internet, WWW, Dynamic Content, Business, Spreadsheet, and CGI Tools.
If you don't know how to create an xlsx report in Odoo without a dependent module, You can read our blog on how to generate xlsx Report Using Controller using the following link.
Furthermore, if you need to know about how to create an xlsx report with a dependent module such as Base Report xlsx, read the following blog:
How to Create a XLS Report in OdooThis blog will provide an insight on how to create an excel report using the xlwt package.
First of all, we should print an xlsx report from a wizard. Here I am going to create an xlsx wizard for filtering the report based on the data.
When you click on the generate report button the report generated and saved in a field with a file name.
Further, then import the xlwt in the python file and when you click on the generate report button action_advance_salary_report function in the python file gets executed.
The following code depicts the generation of a Python File.
# -*- coding: utf-8 -*-
import xlwt
import base64
from io import BytesIO
from odoo import models, fields, api, _
from odoo.exceptions import UserError, ValidationError, Warning
from datetime import date
class AdvanceReport(models.TransientModel):
_name = "advance.report"
date = fields.Date(string='Date', required=True, default=date.today().replace(day=11))
company_id = fields.Many2one('res.company', string="Company")
summary_data = fields.Char('Name', size=256)
file_name = fields.Binary('Pay Slip Summary Report', readonly=True)
state = fields.Selection([('choose', 'choose'), ('get', 'get')],
default='choose')
_sql_constraints = [
('check', 'CHECK((start_date <= end_date))', "End date must be greater then sPFrt date")
]
def action_xlwt_report(self):
company_name = self.company_id.name
file_name = 'Report.xls'
workbook = xlwt.Workbook(encoding="UTF-8")
format0 = xlwt.easyxf(
'font:height 500,bold True;pattern: pattern solid, fore_colour gray25;align: horiz center; borders: top_color black, bottom_color black, right_color black, left_color black,\
left thin, right thin, top thin, bottom thin;')
formathead2 = xlwt.easyxf(
'font:height 250,bold True;pattern: pattern solid, fore_colour gray25;align: horiz center; borders: top_color black, bottom_color black, right_color black, left_color black,\
left thin, right thin, top thin, bottom thin;')
format1 = xlwt.easyxf('font:bold True;pattern: pattern solid, fore_colour gray25;align: horiz left; borders: top_color black, bottom_color black, right_color black, left_color black,\
left thin, right thin, top thin, bottom thin;')
format2 = xlwt.easyxf('font:bold True;align: horiz left')
format3 = xlwt.easyxf('align: horiz left; borders: top_color black, bottom_color black, right_color black, left_color black,\
left thin, right thin, top thin, bottom thin;')
sheet = workbook.add_sheet("Payslip Summary Report")
sheet.col(0).width = int(7 * 260)
sheet.col(1).width = int(30 * 260)
sheet.col(2).width = int(40 * 260)
sheet.col(3).width = int(20 * 260)
sheet.row(0).height_mismatch = True
sheet.row(0).height = 150 * 4
sheet.row(1).height_mismatch = True
sheet.row(1).height = 150 * 2
sheet.row(2).height_mismatch = True
sheet.row(2).height = 150 * 3
sheet.write_merge(0, 0, 0, 3, 'Advance Bank Report', format0)
sheet.write_merge(1, 1, 0, 3, 'Date:' + str(self.date), formathead2)
sheet.write_merge(2, 2, 0, 3, 'Company : ' + company_name, formathead2)
sheet.write(3, 0, 'Sl.No#', format1)
sheet.write(3, 1, 'Employee Bank Account', format1)
sheet.write(3, 2, 'Employee Name', format1)
sheet.write(3, 3, 'Amount', format1)
fp = BytesIO()
workbook.save(fp)
self.write(
{'state': 'get', 'file_name': base64.encodestring(fp.getvalue()), 'summary_data': file_name})
fp.close()
return {
'type': 'ir.actions.act_window',
'res_model': 'advance.report',
'view_mode': 'form',
'view_type': 'form',
'res_id': self.id,
'target': 'new',
}
Now let's move on to generating the XML file using the following code:
<?xml version="1.0" encoding="utf-8"?>
<odoo>
<record id="salary_advance_wizard" model="ir.ui.view">
<field name="name">salary.report.wizard</field>
<field name="model">advance.report</field>
<field name="arch" type="xml">
<form string="Excel Report">
<field name="state" invisible="1"/>
<div states="get">
<group>
<field name="summary_data" colspan="4" invisible="1"/>
<field name="file_name" filename="summary_data" colspan="4"/>
</group>
</div>
<div states="choose">
<group>
<group>
<field name="date" />
<field name="company_id" required="1"/>
</group>
</group>
</div>
<footer states="choose">
<button string="Generate Report"
class="btn-primary"
name="action_xlwt_report"
type="object"/>
<button string="Cancel"
class="btn-default"
special="cancel" />
</footer>
<footer states="get">
<button special="cancel" string="Cancel" class="oe_highlight" />
</footer>
</form>
</field>
</record>
<record id="action_advance_xlwt" model="ir.actions.act_window">
<field name="name">Excel Report</field>
<field name="type">ir.actions.act_window</field>
<field name="res_model">advance.report</field>
<field name="view_mode">form</field>
<field name="view_id" ref="salary_advance_wizard"/>
<field name="target">new</field>
</record>
<menuitem id="child_menu_report"
name="XLWT Report"
parent="your_module_name.parent_menu_salary_advance"
action="action_advance_xlwt"/>
</odoo>
Moreover, in the python file we are importing the xlwt package and created a Xls workbook using xlwt library to generate a workbook using the code as described below:
workbook = xlwt.Workbook(encoding="UTF-8")
In addition, if you want to create your own style you can create a file format consisting of various fonts and designs. After creating that you can import that file into the respective file as shown in the following command:
format0 = xlwt.easyxf(
'font:height 500,bold True;pattern: pattern solid, fore_colour gray25;align: horiz center; borders: top_color black, bottom_color black, right_color black, left_color black,\
left thin, right thin, top thin, bottom thin;'
)
sheet.write_merge(0, 0, 0, 3, 'Advance Bank Report', format0)
As the mentioned way above you can add your style in this report and upon you select the XLWT report menu a wizard as shown below:
After providing the details you can click the Generate Report button available as shown in the above image to generate xls report using xlwt package. Then it will save the file with the respective file name into the field.
When you click on that report.xls file the report will be downloaded. The description of the report will be as shown below:
The excel report generation using the xlwt packets is useful functionality for the report generations of the ongoing operations as well as the ones which have been completed in Odoo.