When managing financial or accounting aspects of business activities, Excel reports stand out for their simplicity and user-friendly nature. They provide users with a straightforward method to analyze content, facilitating informed
decision-making based on the presented data.
In this blog, we will explore the process of generating an XLSX report using a controller in Odoo, eliminating the need for dependent modules.
This approach introduces flexibility and customization options, empowering users to tailor their reports to meet specific business requirements.
Let's explore the step-by-step process of creating an XLSX report in Odoo using a controller. In the upcoming sections, we will guide you through the necessary steps, outlining the setup of a sample XLSX report module along with the required files and folders.
To initiate the generation of an XLSX report from a button in Odoo 17, the process commences with the creation of the button within the Sale Order module to capture essential data. This comprehensive procedure involves the integration of both Python and XML files.
Within the Python file, fields are explicitly declared to accumulate the requisite information, and the view for the button is meticulously crafted using XML. Importantly, the xlsxwriter module is incorporated into the Python file. The method print XLSX() within the Python code is seamlessly executed when users click the designated print button.
This streamlined two-step approach ensures efficient data entry through the button, paving the way for subsequent report generation based on the user-provided information. The button serves as the primary interface, facilitating user input, while the XLSX report is meticulously fashioned using the specified data.
In the sale_report_excel.py
import io
import json
import xlsxwriter
from odoo import models
from odoo.tools import date_utils
class SalesOrder(models.Model):
_inherit = 'sale.order'
def sale_report_excel(self):
products = self.mapped('order_line.product_id.name')
data = {
'model_id': self.id,
'date': self.date_order,
'customer': self.partner_id.name,
'products': products
}
return {
'type': 'ir.actions.report',
'data': {'model': 'sale.order',
'options': json.dumps(data,
default=date_utils.json_default),
'output_format': 'xlsx',
'report_name': 'Sales Excel Report',
},
'report_type': 'xlsx',
}
def get_xlsx_report(self, data, response):
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet()
cell_format = workbook.add_format(
{'font_size': '12px', 'align': 'center'})
head = workbook.add_format(
{'align': 'center', 'bold': True, 'font_size': '20px'})
txt = workbook.add_format({'font_size': '10px', 'align': 'center'})
sheet.merge_range('B2:I3', 'EXCEL REPORT', head)
sheet.merge_range('A4:B4', 'Customer:', cell_format)
sheet.merge_range('C4:D4', data['customer'],txt)
sheet.merge_range('A5:B5', 'Products', cell_format)
for i, product in enumerate(data['products'],
start=5): # Start at row 6 for products
sheet.merge_range(f'C{i}:D{i}', product, txt)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
XML File: sale_report_excel_views.xml
<?xml version="1.0" encoding="UTF-8" ?>
<odoo>
<record id="view_order_form" model="ir.ui.view">
<field name="name">sale.order.view.form.inherit.sale.execel.report</field>
<field name="model">sale.order</field>
<field name="inherit_id" ref="sale.view_order_form"/>
<field name="arch" type="xml">
<xpath expr="//button[@name='action_confirm']" position="after">
<button name="sale_report_excel" string="Print Sales Report Excel" type="object"/>
</xpath>
</field>
</record>
</odoo>
The Action Manager verifies the return report type "xlsx" specified in the Python file before initiating the report action. Following this, it becomes necessary to develop a JavaScript (JS) file for the action manager to ensure seamless execution.
/** @odoo-module **/
import { registry } from "@web/core/registry";
import { BlockUI } from "@web/core/ui/block_ui";
import { download } from "@web/core/network/download";
/**
This handler is responsible for generating XLSX reports.
*/
registry.category("ir.actions.report handlers").add("qwerty_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),
});
}
});
Within the action_manager JS file, the URL '/xlsx_reports' is mapped to controllers. Subsequently, the creation of a Python file for the controller becomes the next step in the process.
Controller: main.py
import json
from odoo import http
from odoo.http import content_disposition, request
from odoo.http import serialize_exception as _serialize_exception
from odoo.tools import html_escape
class XLSXReportController(http.Controller):
"""XlsxReport generating controller"""
@http.route('/xlsx_reports', type='http', auth='user', methods=['POST'], csrf=False)
def get_report_xlsx(self, model, options, output_format, **kw):
"""
Generate an XLSX report based on the provided data and return it as a
response.
"""
uid = request.session.uid
report_obj = request.env[model].with_user(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('Sale Excel Report' + '.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)))
When clicking the Print button, the controller calls get_xlsx_report() in the button python file.
The output will look like the screenshot below.
Concluding this blog, we have explored a methodical approach to implementing XLSX report generation in Odoo 17 using a controller. This guide equips developers with the tools to enhance reporting functionalities, allowing for the creation of tailored Excel reports that cater to specific business requirements. Through the seamless integration of Python, XML, and JavaScript, users can confidently deploy a robust and customizable reporting solution within the Odoo 17 framework.
To read more about generating XLSX reports using the controller in Odoo 16, refer to our blog How to Generate XLSX Report Using Controller in Odoo 16