How to Generate XLSX Report Using Controller in 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)))

The representative image of xlsx report will look like below.