Enable Dark Mode!
generate-xlsx-report-using-controller-odoo-13.png
By: Varsha Vivek K

How to Generate XLSX Report Using Controller in Odoo 13

Technical Odoo 12 Odoo 13

When it comes to the working of a business, an Excel report has a high prominence in offering a precise and accurate source of data pertaining to different business operations. Indeed, an Excel report is considered to be the most helpful doc compared to other kinds. Especially, in carrying out and monitoring accounts/finance-related business activities. 


Excel report reports ’ simple interface makes them prominent for these activity types. It allows the user to easily understand and analyze the contents. 


We know by default Odoo doesn’t support xlsx file creation of a report. So in Odoo, basically we make use of depending module to print the xlsx report. Many have no idea about how to print an xlsx report in Odoo without using any depend module. 


In this blog, I am going to show you how to print an xlsx report without taking the aid of others to depend on modules such as Base report xlsx (https://www.odoo.com/apps/modules/12.0/report_xlsx/). 


Here we will discuss printing an xlsx report from a wizard in an Odoo module using a controller.


Let’s create an example for xlsx report in Odoo. Here, I am going to create a module named ‘example_xlsx’     



It creates an xlsx report from the wizard. Wizard is used to enter the data and based on that data we can filter the report. So first, we have to look at how to create a wizard. In a wizard, we can add some fields to filter the report contents. 


To create a wizard, we need both python and XML files. The fields are declared in the python file and the XML file creates a wizard view. One of the important things in the python file we need to import is xlswriter. Upon clicking the print button, the print_xlsx() function in the python file gets executed.


Python File : wizard.py


import time
import json
import datetime
import io
from odoo import fields, models, _
from odoo.exceptions import ValidationError
from odoo.tools import date_utils
try:
    from odoo.tools.misc import xlsxwriter
except ImportError:
import xlsxwriter
class ExcelWizard(models.TransientModel):
 _name = "example.xlsx.report.wizard"
    start_date = fields.Datetime(string="Start Date", default=time.strftime('%Y-%m-01'), required=True)
    end_date = fields.Datetime(string="End Date", default=datetime.datetime.now(), required=True)
    def print_xlsx(self):
        if self.start_date > self.end_date:
            raise ValidationError('Start Date must be less than End Date')
        data = {
            'start_date': self.start_date,
            'end_date': self.end_date,
        }
        return {
            'type': 'ir_actions_xlsx_download',
            'data': {'model': 'example.xlsx.report.wizard',
                     'options': json.dumps(data, default=date_utils.json_default),
                     'output_format': 'xlsx',
                     'report_name': 'Excel Report',
                    }
        }
    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'})
        head = workbook.add_format({'align': 'center', 'bold': True,'font_size':'20px'})
       txt = workbook.add_format({'font_size': '10px'})
        sheet.merge_range('B2:I3', 'EXCEL REPORT', head)
       sheet.write('B6', 'From:', cell_format)
        sheet.merge_range('C6:D6', data['start_date'],txt)
        sheet.write('F6', 'To:', cell_format)
        sheet.merge_range('G6:H6', data['end_date'],txt)
        workbook.close()
        output.seek(0)
        response.stream.write(output.read())
        output.close()

XML File: wizard_view.xml


<?xml version="1.0" encoding="utf-8"?>
<odoo>
    <record id="example_xlsx_report_view" model="ir.ui.view">
        <field name="name">Example xlsx Report</field>
        <field name="model">example.xlsx.report.wizard</field>
        <field name="arch" type="xml">
            <form string="Report Options">
                <separator string="Excel Report"/>
                <group col="4">
                    <field name="start_date"/>
                    <field name="end_date"/>
                </group>
                <footer>
                    <button name="print_xlsx" string="PRINT" type="object"
                             default_focus="1" class="oe_highlight"/>
                    <button string="Cancel" class="btn btn-default" special="cancel"/>
                </footer>
            </form>
        </field>
    </record>
    <record id="action_xlsx_report" model="ir.actions.act_window">
        <field name="name">Excel</field>
        <field name="res_model">example.xlsx.report.wizard</field>
        <field name="type">ir.actions.act_window</field>
        <field name="view_mode">form</field>
        <field name="target">new</field>
    </record>
    <menuitem id="excel_report" name="Excel Report"
               parent="stock.menu_warehouse_report" action="action_xlsx_report"/>
</odoo>


In the python file, the return type 'ir_actions_xlsx_download' will be checked by the Action manager to execute the report action. So next we have to create a JS file for the action manager.


Js File: action_manager.js


odoo.define('example_xlsx.action_manager', function (require) { "use strict"; /** * The purpose of this file is to add the actions of type * 'ir_actions_xlsx_download' to the ActionManager. */ var ActionManager = require('web.ActionManager'); var framework = require('web.framework'); var session = require('web.session'); ActionManager.include({ _executexlsxReportDownloadAction: function (action) { framework.blockUI(); var def = $.Deferred(); session.get_file({ url: '/xlsx_reports', data: action.data, success: def.resolve.bind(def), error: (error) => this.call('crash_manager', 'rpc_error', error), complete: framework.unblockUI, }); return def; }, _handleAction: function (action, options) { if (action.type === 'ir_actions_xlsx_download') { return this._executexlsxReportDownloadAction(action, options); } return this._super.apply(this, arguments); }, }); });


To add the path of the JS file, we need to create an XML file.


XML File: action_manager.xml


<odoo> <data> <template id="assets_backend" name="xls_assets" inherit_id="web.assets_backend"> <xpath expr="." position="inside"> <scripttype="text/javascript" src="/example_xlsx/static/src/js/action_manager.js"/> </xpath> </template> </data> </odoo>


In action_manager js file, the URL '/xlsx_reports' is directed to controllers. So next we have to create a python file for controller.


Controller: main.py


import json from odoo import http from odoo.http import content_disposition, request from odoo.addons.web.controllers.main import _serialize_exception from odoo.tools import html_escape class XLSXReportController(http.Controller): @http.route('/xlsx_reports', type='http', auth='user', methods=['POST'], csrf=False) def get_report_xlsx(self, model, options, output_format, token, report_name, **kw): uid = request.session.uid report_obj = request.env[model].sudo(uid) options = json.loads(options) 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)))


When clicking on the PRINT button, the controller calls  get_xlsx_report() function in the wizard python file. 


print-xlsx-report-using-controller-odoo-13-cybrosys


The representative image of xlsx report will look like below.


print-xlsx-report-using-controller-odoo-13-cybrosys


XML File: wizard_view.xml


If you need any assistance in odoo, we are online, please chat with us.



5
Comments

Andres

hello and thanks for this guide. Nevertheless i have followed all steps but i keep receiving error. Any hekp? thanks Traceback (most recent call last): File "/usr/lib/python3/dist-packages/odoo/http.py", line 639, in _handle_exception return super(JsonRequest, self)._handle_exception(exception) File "/usr/lib/python3/dist-packages/odoo/http.py", line 315, in _handle_exception raise exception.with_traceback(None) from new_cause KeyError: 'ir_actions_xlsx_download' -/controller/main.py and __init__.py fiiles were created -/static/lib/js/action_manager.js was created -/static/xml/action_manager.xml file was created 'qweb': ['static/src/xml/action_manager.xml',] in __manifest__.py was created from . import controllers in _init__.py fiile was updated . what I am missing?? I am using ver14e Thanks

31/05/2022

-

3:56PM

Manaf

I want to generate excel report on button click, can anyone help me out how this can be done. I'm looking for an experienced < a href="https://www.odooconsultant.com/">Odoo Consultant to complete the task. Thank you

30/04/2020

-

4:36AM

Alfie

I could not refrain from commenting. Exceptionally well written!

28/08/2021

-

4:33PM

Rodrick

Thanks in favor of sharing such a pleasant opinion, piece of writing is nice, thats why i have read it entirely

25/07/2021

-

8:35PM

Muhammed Aslam

is this method faster compared to using depend module report_xlsx

21/11/2019

-

5:30AM



Leave a comment

 


whatsapp
location

Calicut

Cybrosys Technologies Pvt. Ltd.
Neospace, Kinfra Techno Park
Kakkancherry, Calicut
Kerala, India - 673635

location

Kochi

Cybrosys Technologies Pvt. Ltd.
1st Floor, Thapasya Building,
Infopark, Kakkanad,
Kochi, India - 682030.

location

Bangalore

Cybrosys Techno Solutions
The Estate, 8th Floor,
Dickenson Road,
Bangalore, India - 560042

Send Us A Message