When it comes to keeping track of business activities that are related to finances or accounts, Excel reports are straightforward to understand and use. It makes content analysis simple for the user.
By default, Odoo does not support creating reports from XLSX files. Therefore, in order to produce the XLSX report, we make use of a dependent module. Many people are unaware that we can produce the report without utilizing this dependent module.
I’ll show you how to create an XLSX report without using a dependent module like Base report XLSX (https://apps.odoo.com/apps/modules/16.0/report_xlsx/) in this blog post.
In this section, we’ll talk about using a controller to print an XLSX report from a wizard.
In Odoo, let’s make a sample XLSX report. I’m going to create a report module here with the following files and folders:
It creates an XLSX report from wizardData is entered using a wizard, and the report can then be filtered based on that data. So, we can start by making a wizard.
To develop a wizard, we need both python and XML files.
Fields are declared in the python code, and the wizard view is constructed by utilizing XML. In the Python file, don’t forget to import xlsxwriter. The print XLSX() method in the python code is called when the print button is pressed.
Python File: report.py
import time
import datetime
from dateutil.relativedelta import relativedelta
from odoo import fields, models, api, _
from odoo.tools import float_is_zero
from odoo.tools import date_utils
import io
import json
try:
from odoo.tools.misc import xlsxwriter
except ImportError:
import xlsxwriter
class ExcelWizard(models.TransientModel):
_name = "example.xlsx.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.report',
'data': {'model': 'example.xlsx.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):
from_date = data['from_date']
to_date = data['to_date']
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('A6:B6', 'From Date:', cell_format)
sheet.merge_range('C6:D6', from_date, txt)
sheet.write('F6', 'To Date:', cell_format)
sheet.merge_range('G6:H6', to_date, txt)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
XML File: report_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.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_repoort" model="ir.actions.act_window">
<field name="name">Excel</field>
<field name="res_model">example.xlsx.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_reprort" name="Excel Report"
parent="stock.menu_stock_warehouse_mgmt" action="action_xlsx_repoort" sequence="20" groups="stock.group_stock_manager,stock.group_stock_user"/>
</odoo>
The Action Manager will check the return report type “xlsx” in the Python file before executing the report action.
Next, a JS file for the action manager needs to be created.
JS File: action_manager.js
/** @odoo-module */
import { registry } from "@web/core/registry";
import { download } from "@web/core/network/download";
import framework from 'web.framework';
import session from 'web.session';
registry.category("ir.actions.report handlers").add("xlsx", async (action) => {
if (action.report_type === 'xlsx') {
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;
}
});
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
# -*- coding: utf-8 -*-
import json
from odoo import http
from odoo.http import content_disposition, request
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, report_name, **kw):
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(report_name + '.xlsx'))
]
)
report_obj.get_xlsx_report(options, response)
response.set_cookie('fileToken', token)
return response
except Exception as e:
se = http.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 the screenshot below.
