generate-xlsx-report-using-controller-odoo-13.png
Blogger_636881918488007730.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 comparing to other kinds. Especially in carrying out and monitoring accounts/ finance related business activities. 


Excel report’s 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 controller.


Let’s create an example for xlsx report in Odoo. Here, I am going to create a module named ‘example_xlsx’ which contains the following folders and files.


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


It creates an xlsx report from the wizard. Wizard is used to entering 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, 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




cybrosys youtube

Comments

1

Muhammed Aslam

21/11/2019 - 5:30AM

is this method faster compared to using depend module report_xlsx




Leave a comment

 
Calicut

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

London

Cybrosys Limited
Alpha House,
100 Borough High Street, London,
SE1 1LB, United Kingdom

Kochi

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

Bangalore

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

Send Us A Message