Enable Dark Mode!
how-to-generate-xlsx-report-using-controller-in-odoo-16.jpg
By: Rosmy John

How to Generate XLSX Report Using Controller in Odoo 16

Technical Odoo 16

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:

 how-to-generate-xlsx-report-using-controller-in-odoo-16-cybrosys

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.

 how-to-generate-xlsx-report-using-controller-in-odoo-16-cybrosys

The output will look like the screenshot below.

 how-to-generate-xlsx-report-using-controller-in-odoo-16-cybrosys


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



1
Comments

Darshan Gajjar

Hello Team, this blog is very helpful for me. So Thank you so much, but I have got one problem when the wizard form is open and the action called wizard form was not closed so I know this below code is added in the js file but where I don't know __________________________________________________________ const onClose = options.onClose; if (action.close_on_report_download) { return doAction({ type: "ir.actions.act_window_close" }, { onClose }); } else if (onClose) { onClose(); } ______________________________ Thank You

09/01/2023

-

12:34PM



Leave a comment

 


whatsapp_icon
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