When handling financial or accounting aspects of business operations, Excel reports excel due to their simplicity and user-friendly interface. They offer users a straightforward approach to analyze content, enabling informed decisions.
making decisions based on the provided 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 begin generating an XLSX report from a button in Odoo 17, the process starts with creating the button within the Sale Order module to gather crucial data. This detailed process entails incorporating both Python and XML files.
In the Python script, fields are explicitly defined to gather the necessary information, and the button's view is meticulously constructed using XML. Crucially, the xlsxwriter module is integrated into the Python script. When users click the designated print button, the print XLSX() method within the Python code is seamlessly executed.
This simplified two-step method guarantees efficient data input via the button, enabling subsequent report generation based on the user-provided information. The button acts as the main interface, facilitating user input, while the XLSX report is meticulously crafted using the specified data.
In the file named https://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 validates the specified return report type "xlsx" in the Python script before triggering the report action. Subsequently, it's imperative to create a JavaScript (JS) file for the action manager to ensure smooth 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("https://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),
}); }
});
In the action_manager JS file, the URL '/xlsx_reports' is linked to controllers. Consequently, the next step in the process involves creating a Python file for the controller.
Controller: https://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)))
Upon clicking the Print button, the controller invokes get_xlsx_report() in the button Python file.
The resulting output will resemble the screenshot provided below.
Wrapping up this blog, we've examined a systematic method for implementing XLSX report generation in Odoo 17 utilizing a controller. This guide provides developers with the necessary tools to improve reporting capabilities, enabling the creation of customized Excel reports tailored to meet specific business needs. With the seamless integration of Python, XML, and JavaScript, users can confidently deploy a robust and adaptable reporting solution within the Odoo 17 framework.