Generating Excel reports is a common requirement in enterprise applications. Odoo 19 provides powerful tools to create dynamic XLSX reports using controllers. With controllers, you can directly handle requests, generate reports, and return them to the user for download. This guide walks you through the process of creating a fully functional XLSX report in Odoo 19, using a real-world example of Employee Attendance Reports.
Step 1: Setting Up the Controller
Controllers in Odoo handle HTTP requests. For XLSX reports, we’ll create a controller that receives parameters from the frontend, generates an Excel file, and returns it as a downloadable response.
Create a new file in your module:
controllers/xlsx_report_controller.py
import json
from odoo import http
from odoo.http import content_disposition, request, serialize_exception
from odoo.tools import html_escape
class XLSXReportController(http.Controller):
"""Xlsx Report controller"""
@http.route('/xlsx_reports', type='http', auth='user', methods=['POST'],
csrf=False)
def get_report_xlsx(self, model, options, output_format, report_name):
"""xlsx report"""
report_obj = request.env[model].with_user(request.session.uid)
options = json.loads(options)
token = 'dummy-because-api-expects-one'
try:
if output_format == 'xlsx':
response = request.make_response(
None,
headers=[
('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', content_disposition(report_name + '.xlsx'))
]
)
report_obj.get_xlsx_report(options, response)
response.set_cookie('fileToken', token)
return response
except Exception as e:
se = serialize_exception(e)
error = {
'code': 200,
'message': 'Odoo Server Error',
'data': se
}
return request.make_response(html_escape(json.dumps(error)))
csrf=False is important to allow JS-triggered downloads.
content_disposition ensures the file is downloaded instead of displayed. All heavy lifting of Excel creation is delegated to the model (get_xlsx_report).
Step 2: Creating the Frontend Interface
To allow users to trigger reports, we’ll create a wizard with a simple form. This wizard lets users select filters (like employee and date range) and click “Print” to generate the XLSX report.
Create a view file: views/report_wizard.xml
<?xml version="1.0" encoding="UTF-8"?>
<odoo>
<!--Form View For Wizard-->
<record id="employee_attendance_report_view_form" model="ir.ui.view">
<field name="name">employee.attendance.report.view.form</field>
<field name="model">employee.attendance.report</field>
<field name="arch" type="xml">
<form string="Attendance Report">
<group>
<group>
<field name="from_date"/>
<field name="to_date"/>
</group>
<group>
<field name="employee_ids" widget="many2many_tags"/>
</group>
</group>
<footer>
<button name="action_print_xlsx" string="Print"
type="object" class="btn-primary"/>
<button string="CANCEL" class="btn-secondary"
special="cancel"/>
</footer>
</form>
</field>
</record>
<!-- Action -->
<record id="employee_attendance_report_action"
model="ir.actions.act_window">
<field name="name">Attendance Report</field>
<field name="type">ir.actions.act_window</field>
<field name="res_model">employee.attendance.report</field>
<field name="view_mode">form</field>
<field name="view_id" ref="employee_attendance_report_view_form"/>
<field name="target">new</field>
</record>
<!-- Menu -->
<menuitem id="employee_attendance_report_menu"
name="Reports"
parent="hr_attendance.menu_hr_attendance_root"/>
<menuitem id="employee_attendance_report_menu_action"
name="Attendance Report"
parent="employee_attendance_report_menu"
action="employee_attendance_report_action"/>
</odoo>
Step 3: Creating the Wizard Model
The wizard model handles user input, validates data, and delegates report generation.
Create models/xlsx_report_wizard.py:
import io
import json
from datetime import datetime, date
from dateutil.rrule import rrule, DAILY
from odoo import fields, models, _
from odoo.exceptions import ValidationError
from odoo.tools import date_utils, json_default
try:
from odoo.tools.misc import xlsxwriter
except ImportError:
import xlsxwriter
class EmployeeAttendanceReport(models.TransientModel):
""" Wizard for Employee Attendance Report """
_name = 'employee.attendance.report'
_description = 'Employee Attendance Report Wizard'
from_date = fields.Date('From Date', help="Starting date for report")
to_date = fields.Date('To Date', help="Ending date for report")
employee_ids = fields.Many2many('hr.employee', string='Employee',
help='Name of Employee')
def action_print_xlsx(self):
"""
Returns report action for the XLSX Attendance report
Raises: ValidationError: if From Date > To Date
Raises: ValidationError: if there is no attendance records
Returns:
dict: the XLSX report action
"""
if not self.from_date:
self.from_date = date.today()
if not self.to_date:
self.to_date = date.today()
if self.from_date > self.to_date:
raise ValidationError(_('From Date must be earlier than To Date.'))
attendances = self.env['hr.attendance'].search(
[('employee_id', 'in', self.employee_ids.ids)])
data = {
'from_date': self.from_date,
'to_date': self.to_date,
'employee_ids': self.employee_ids.ids
}
if self.employee_ids and not attendances:
raise ValidationError(
_("There is no attendance records for the employee"))
if self.from_date and self.to_date:
return {
'type': 'ir.actions.report',
'data': {'model': 'employee.attendance.report',
'options': json.dumps(data, default=json_default),
'output_format': 'xlsx',
'report_name': 'Attendance Report',
},
'report_type': 'xlsx',
}
def get_xlsx_report(self, data, response):
"""
Print the XLSX report
Returns: None
"""
query = """select hr_e.name,date(hr_at.check_in),
SUM(hr_at.worked_hours) from hr_attendance hr_at LEFT JOIN
hr_employee hr_e ON hr_at.employee_id = hr_e.id"""
if not data['employee_ids']:
query += """ GROUP BY date(check_in), hr_e.name"""
else:
query += """ WHERE hr_e.id in (%s) GROUP BY date(check_in),
hr_e.name""" % (', '.join(str(employee_id)
for employee_id in data['employee_ids']))
self.env.cr.execute(query)
docs = self.env.cr.dictfetchall()
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet('docs')
start_date = datetime.strptime(data['from_date'], '%Y-%m-%d').date()
end_date = datetime.strptime(data['to_date'], '%Y-%m-%d').date()
date_range = rrule(DAILY, dtstart=start_date, until=end_date)
sheet.set_column(1, 1, 15)
sheet.set_column(2, 2, 15)
border = workbook.add_format({'border': 1})
green = workbook.add_format({'bg_color': '#28A828', 'border': 1})
red = workbook.add_format({'bg_color': '#ff3333', 'border': 1})
rose = workbook.add_format({'bg_color': '#DA70D6', 'border': 1})
head = workbook.add_format(
{'bold': True, 'font_size': 30, 'align': 'center'})
date_size = workbook.add_format(
{'font_size': 12, 'bold': True, 'align': 'center'})
sheet.merge_range('C3:K6', 'Attendance Report', head)
sheet.merge_range('B8:C9', 'From Date: ' + data['from_date'], date_size)
sheet.merge_range('B10:C11', 'To Date: ' + data['to_date'], date_size)
sheet.write(2, 12, '', green)
sheet.write(2, 13, 'Present')
sheet.write(4, 12, '', red)
sheet.write(4, 13, 'Absent')
sheet.write(6, 12, '', rose)
sheet.write(6, 13, 'Half Day')
sheet.merge_range('B16:B17', 'Sl.No', border)
sheet.merge_range('C16:C17', 'Employee', border)
row = 15
col = 2
for date_data in date_range:
col += 1
sheet.write(row, col, date_data.strftime('%Y-%m-%d'), border)
row = 16
col = 2
for date_data in date_range:
col += 1
sheet.write(row, col, date_data.strftime('%a'), border)
employee_names = []
attendance_list = []
for doc in docs:
if doc['name'] not in employee_names:
date_sum_list = []
employee_names.append(doc['name'])
for date_data in date_range:
date_out = date_data.strftime('%Y-%m-%d')
record_list = list(
filter(
lambda x: x['name'] == doc['name'] and x[
'date'].strftime(
'%Y-%m-%d') == date_out, docs))
if record_list:
date_sum_list.append(record_list[0])
else:
date_sum_list.append({
'name': '',
'date': '',
'sum': 0
})
attendance_list.append(
{'name': doc['name'], 'items': date_sum_list})
work = self.env.ref('resource.resource_calendar_std')
row = 17
i = 0
for rec in attendance_list:
row += 1
col = 1
i += 1
sheet.write(row, col, i, border)
col += 1
sheet.write(row, col, rec['name'], border)
for item in rec['items']:
col += 1
if item['sum'] >= work.hours_per_day:
sheet.write(row, col, item['sum'], green)
elif 1 <= item['sum'] <= 4 or 4 <= item['sum'] <= \
work.hours_per_day:
sheet.write(row, col, item['sum'], rose)
else:
sheet.write(row, col, item['sum'], red)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
Step 4: Security Access
Add access rights in security/ir.model.access.csv:
id,name,model_id:id,group_id:id,perm_read,perm_write,perm_create,perm_unlink
access_employee_attendance_report,access.employee.attendance.report,model_employee_attendance_report,base.group_user,1,1,1,1
Step 5: Frontend JS Integration
To handle XLSX report downloads, Odoo uses Action Handlers. Add this JS in static/src/js/action_manager.js:
/** @odoo-module **/
import { registry } from "@web/core/registry";
import { BlockUI } from "@web/core/ui/block_ui";
import { download } from "@web/core/network/download";
/**
XLSX Handler
This handler is responsible for generating XLSX reports.
It sends a request to the server to generate the report in XLSX format
and downloads the generated file.
@param {Object} action - The action object containing the report details.
@returns {Promise} - A promise that resolves when the report generation is complete.
*/
registry.category("ir.actions.report handlers").add("xlsx", async function (action) {
if (action.report_type === 'xlsx') {
BlockUI;
await download({
url: '/xlsx_reports',
data: action.data,
complete: () => unblockUI,
error: (error) => self.call('crash_manager', 'rpc_error', error),
});
}
});
Conclusion
By following these steps, you can create fully dynamic XLSX reports in Odoo 19:
- The controller handles HTTP requests and serves the Excel file.
- The wizard gathers user input.
- The model fetches data, formats it, and generates the Excel workbook.
- The JS handler triggers the download seamlessly in the frontend.
To read more about How to Generate XLSX Report Using Controller in Odoo 18, refer to our blog How to Generate XLSX Report Using Controller in Odoo 18.