For a Business, analyzing and understanding the activities in a venture is an important thing. For that, we can depend on the Excel report. Using this, users can easily understand business activities in a venture.
Odoo doesn’t support XLSX report generation from reports. We can generate XLSX reports using the depending module. For printing XLSX reports, you must know about XLSX writers.
What is an XLSX writer?
A Python module can write numbers, text, hyperlinks, and formulas into an excel worksheet. Also, it supports features like images, page setup, charts, formatting, conditional formatting, and auto filters.
What is JSON?
It is read as a quoted string, which contains contents in Key-value mapping within the dictionary. Also accessible as a dictionary object.
What is IO?
We can specify the modes on opening a file. We can read r, append a, and write w to the file. BytesIO is a method that manipulates bytes of data in memory. It is used for binary data.
What is ValidationError?
Sometimes we need to prevent the program’s continuing execution; we can do this by popping error messages by raising exceptions.
Let’s create an example for the XLSX report in Odoo.
We need an XML file and a python file for creating a wizard, fields are declared in the python file, and wizard view is created using XML. Must import XLSX writer in the python file.When clicking on the print button. print_XLSX() function will be executed.
Python File:
While clicking on the print button ‘print_XLSX’ function will be executed. Needs to compare start and end date and returns a dictionary having type as ‘ir.actions.report’, report type as ‘XLSX’, and data including the model name,output_format,report_name, and date.
return {'type': 'ir.actions.report','report_type': 'XLSX','data': {'model': 'Wizard model','output_format': 'XLSX','options': json.dumps(data, default=date_utils.json_default),'report_name': 'Excel Report Name',},}
Initializing a buffer ‘output’ for writing data into excel. Also, you can define font styles and merge columns in an Excel sheet. Don’t forget to close the buffer after the write operation.
output = io.BytesIO()
workbook = XLSXwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet()
head = workbook.add_format({'align': 'center', 'bold': True, 'font_size': '20px'})
txt = workbook.add_format({'font_size': '10px'})
sheet.merge_range('B2:I3', 'Report heading', head)
output.close()
JS File:
report_type is added into a registry and checks report_type is ‘XLSX.’Then call the corresponding controller function.
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;
}
})
Controller:
Content-type for Excel2007 and above .XLSX files is ‘application/vnd.ms-excel’ and Content-Disposition’ is additional information such as filename are put in the header part of the response.
token = 'dummy-because-api-expects-one'response = request.make_response( None, headers=[('Content-Type', 'application/vnd.ms-excel'), ('Content-Disposition', content_disposition(report_name + '.XLSX')) ] )response.set_cookie('fileToken', token)
Path of ‘action_manager.js’ is added in the manifest file of the ‘example_XLSX’ module. No need to add it in the XML file.
'assets': { 'web.assets_backend': [ 'example_xlsx/static/src/js/action_manager.js', ]' }'
The Excel report generated will look like the below:
