An Excel report is very easy to understand and simple to use especially in monitoring accounts/finance related business activities. It helps the user to analyze the content very easily.
Odoo doesn’t support xlsx file creation of a report by default. So we make use of a depending module to create the xlsx report. We can also create the report without using this depending module, Many have no idea about it.
In this blog, I am going to show you how to create an xlsx report without using a dependent module 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 using a 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.
It creates an xlsx report from wizard.Wizard is used for entering data and based on that data we can filter the report.So first,we can create a wizard.
To create a wizard we need both python and xml file.Fields are declared in the python file and the wizard view is created by using xml. Don’t forget to import xlsxwriter in the python file.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',
},
'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'})
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 report_type “xlsx” 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.report_type === 'xlsx') {
return this._executexlsxReportDownloadAction(action, options);
}
return this._super.apply(this, arguments);
},
});
});Create a XML file to add the path of the JS file.
XML File: action_manager.xml
<odoo>
<data>
<template id="assets_backend" name="xls_assets" inherit_id="web.assets_backend">
<xpath expr="." position="inside">
<script type="text/javascript"
src="/example_xlsx/static/src/js/action_manager.js"/>
</xpath>
</template>
</data>
</odoo>
In the action_manager JS file,the URL ‘/xlsx_reports’ is directed to controllers.So next,we have to create a python file for the 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 the Print button the controller calls get_xlsx_report() in the wizard python file.
The output will look like below.
