To create reports in PDF or HTML file odoo has provided its own model, namely ir.actions.report. With this model, we can create PDF and HTML reports quite easily, without having to think about how to retrieve data from the database or how to download the reports. Unfortunately, to create reports in an excel file, we have to create them manually. Although it is not very difficult, there is a lot of work to do.
To create a report in an excel file we need the python library. We can use the xlsxwriter or xlwt. Odoo itself (v12) uses both of them. In the web addon, to handle export and import odoo uses xlwt, while in the account_reports the enterprise addon odoo uses xlsxwriter. I myself have used xlsxwriter and I think it is quite easy, so I am not interested to use xlwt.
Starting from odoo v13, they don’t use the xlwt library.
As a case study, we will create a sales order report per salesperson in the form of an excel file. Later the user can choose which salesperson that he wants to see their report. Of course, the user must be able to select the start date and end date. Each salesperson report will be created in a different worksheet or tab so that it is not mixed with other salespersons. The contents of the report are simple, just the document number, date, customer name, and amount total.
First, let’s install the xlsxwriter library with the command below.
sudo pip3 install xlsxwriter
Then create a Transient Model or Wizard to receive user input regarding which salesperson that he wants to see their report. As well as the start date and end date of the sales order. In this wizard we also need to include a button, to redirect the user to a specific URL to download the report.
# -*- coding: utf-8 -*-
from odoo import models, fields, api, exceptions, _
class SaleWizard(models.TransientModel):
_name = 'ng.sale.wizard'
user_id = fields.Many2many('res.users', string='Sales Person')
start_date = fields.Date("Start Date")
end_date = fields.Date("End Date")
def get_excel_report(self):
# redirect to /sale/excel_report controller to generate the excel file
return {
'type': 'ir.actions.act_url',
'url': '/sale/excel_report/%s' % (self.id),
'target': 'new',
}
The model above is only an intermediary, to accept user input. The actual process of creating a report is in the /sale/excel_report controller, when user click a button, the button will invoke the get_excel_report method in the above model, and the user will be redirected to the /sale/excel_report controller to download the actual report. We need to add a wizard id to the URL, so in the controller, we can know the salesperson, start date, and end date that selected by the user. If you don’t know about controllers please read the following posts.
Next, create the form, action, and menu item to accept user input.
<?xml version="1.0" encoding="utf-8"?>
<odoo>
<data>
<!-- Form to select the salesperson, start date, and end date -->
<record id="ng_sale_wizard_form" model="ir.ui.view">
<field name="name">Sales Report in Excel Format</field>
<field name="model">ng.sale.wizard</field>
<field name="arch" type="xml">
<form string="Sales Report in Excel Format">
<group>
<group>
<field name="user_id" required="1" widget="many2many_tags"/>
</group>
<group>
<field name="start_date" required="1"/>
<field name="end_date" required="1"/>
</group>
</group>
<footer>
<!-- a button to start the download process -->
<button name="get_excel_report" string="Export (XLS)" type="object"
class="btn-primary"/>
<!-- a cancel button to close the form -->
<button string="Cancel" class="btn-default" special="cancel"/>
</footer>
</form>
</field>
</record>
<!-- the action to open the form above -->
<record model="ir.actions.act_window" id="action_open_ng_sale_wizard_form">
<field name="name">Sales Report in Excel Format</field>
<field name="type">ir.actions.act_window</field>
<field name="res_model">ng.sale.wizard</field>
<field name="view_mode">form</field>
<field name="target">new</field>
</record>
<!-- The menu item in Sale >> Reporting menu, to invoke the above action, so the user can see the form -->
<menuitem name="Sales Report in Excel Format"
id="menu_sale_report_in_excel"
parent="sale.menu_sale_report"
action="action_open_ng_sale_wizard_form" />
</data>
</odoo>
Finally, create a controller to start generating the excel file. Please read the comments in the code below to find out the usability of some methods in the xlsxwriter library.
# -*- coding: utf-8 -*-
from odoo import http
from odoo.http import content_disposition, request
import io
import xlsxwriter
class SaleExcelReportController(http.Controller):
@http.route([
'/sale/excel_report/<model("ng.sale.wizard"):wizard>',
], type='http', auth="user", csrf=False)
def get_sale_excel_report(self,wizard=None,**args):
# the wizard parameter is the primary key that odoo sent
# with the get_excel_report method in the ng.sale.wizard model
# contains salesperson, start date, and end date
# create a response with a header in the form of an excel file
# so the browser will immediately download it
# The Content-Disposition header is the file name fill as needed
response = request.make_response(
None,
headers=[
('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', content_disposition('Sales Report in Excel Format' + '.xlsx'))
]
)
# create workbook object from xlsxwriter library
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
# create some style to set up the font type, the font size, the border, and the aligment
title_style = workbook.add_format({'font_name': 'Times', 'font_size': 14, 'bold': True, 'align': 'center'})
header_style = workbook.add_format({'font_name': 'Times', 'bold': True, 'left': 1, 'bottom':1, 'right':1, 'top':1, 'align': 'center'})
text_style = workbook.add_format({'font_name': 'Times', 'left': 1, 'bottom':1, 'right':1, 'top':1, 'align': 'left'})
number_style = workbook.add_format({'font_name': 'Times', 'left': 1, 'bottom':1, 'right':1, 'top':1, 'align': 'right'})
# loop all selected user/salesperson
for user in wizard.user_id:
# create worksheet/tab per salesperson
sheet = workbook.add_worksheet(user.name)
# set the orientation to landscape
sheet.set_landscape()
# set up the paper size, 9 means A4
sheet.set_paper(9)
# set up the margin in inch
sheet.set_margins(0.5,0.5,0.5,0.5)
# set up the column width
sheet.set_column('A:A', 5)
sheet.set_column('B:E', 15)
# the report title
# merge the A1 to E1 cell and apply the style font size : 14, font weight : bold
sheet.merge_range('A1:E1', 'Sales Report in Excel Format', title_style)
# table title
sheet.write(1, 0, 'No.', header_style)
sheet.write(1, 1, 'Document', header_style)
sheet.write(1, 2, 'Date', header_style)
sheet.write(1, 3, 'Customer', header_style)
sheet.write(1, 4, 'Total', header_style)
row = 2
number = 1
# search the sales order
orders = request.env['sale.order'].search([('user_id','=',user.id), ('date_order','>=', wizard.start_date), ('date_order','<=', wizard.end_date)])
for order in orders:
# the report content
sheet.write(row, 0, number, text_style)
sheet.write(row, 1, order.name, text_style)
sheet.write(row, 2, str(order.date_order), text_style)
sheet.write(row, 3, order.partner_id.name, text_style)
sheet.write(row, 4, order.amount_total, number_style)
row += 1
number += 1
# create a formula to sum the total sales
sheet.merge_range('A' + str(row+1) + ':D' + str(row+1), 'Total', text_style)
sheet.write_formula(row, 4, '=SUM(E3:E' + str(row) + ')', number_style)
# return the excel file as a response, so the browser can download it
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
return response
When create an excel file with xlsxwriter library, what we need to pay attention is the index of the row or column. If we use the write or write_formula method the index starts at 0. So the first line or first column is 0. The code below means that we add the ‘No.’ text to the ‘A2’ cell. Why ? Because the row with index of ‘1’ is the second row, while the column with index of ‘0’ is the first column, which is (‘A’) column.
sheet.write(1, 0, 'No.', header_style)
Whereas if we use the merge_range method the index starts at 1, because we write the cell directly, like the code below.
sheet.merge_range('A1:E1', 'Sales Report in Excel Format', title_style)
For more details please read the xlsxwriter documentation here.
This is the appearance of the form to accept the user input.
And this is the appearance of the downloaded file which is opened in the Libre Office.
Please note that the formulas which is written in the xlsxwriter library are not executed immediately. So we need to recalculate the formula. In Libre Office Calc we can use the SHIFT + CTRL + F9 command.
