Home Blog How To Create An XLS Report In Odoo
How To Create An XLS Report In Odoo

How To Create Or Make An XLS Report In Odoo?

XLS reports are one of the most important reports for any business organization. The best representation of data can be done easily in an XLS report. XLS reports are clean & nice representation of data. In this blog, we’ll discuss a step-by-step procedure for create or make an XLS report in Odoo.

We’ll be creating this XLS report using a wizard in Odoo.

Odoo wizard is used to provide an interactive session to users through dynamic forms. Wizard is a model that will be created by inheriting ‘models. TransientModel’ class instead of Model class. We can use it to take input from the user and process it according to our business logic.


 Subscribe for Odoo tips, technical insights, and more!


So we’ll first create a wizard which helps in creating a menu for printing this XLS report. This wizard py file needs to be put inside a wizard folder inside your custom module & make sure that you have called the wizard folder inside the main__init__.py file & the .py file inside __init__.py file in the wizard folder.

Read Here: How To Create Custom Module In Odoo

Wizard:
As we have already discussed, the wizard is a transient model in Odoo. It can be created like this:

from odoo import api, fields, models

class WizardClassName(models.TransientModel):

    _name = 'wizard.wizard'

    _description = 'XLS Report'

This wizard is basically used to filter the values or data based on the information we enter in the fields that are created in the wizard. So first, we’ll create the fields that will be needed for filtering data in this wizard.

In this case, we’ll simply create 2 date fields to fetch the data in the reports between these two dates.

 

          date_from = fields.Date(string='Start Date')

          date_to = fields.Date(string='End Date')

Create an Excel/ XLS Report

In the above code first field is the start date field & the second field is the end date field.

Also create a .xml file for the wizard to show on user interface like this:

Create an Excel/ XLS Report
Create an Excel/ XLS Report

Now we’ll create a method to fetch data and then filter the data based on these fields.

#CODE#

 

            def _get_data(self):

              current_date = fields.Date.today()     
                        #used to get today’s date#

                 domain = []                                 

                       domain += [('date_from', '>=', current_date), (current_date, '<=', self.date_to)] 

                   #used to create a domain to filter based on the start & end date# 

              res = self.env['model.model'].search(domain)

#created an environment in a variable for the model from which we need to filter the data based on the domain#

              docargs = []

              docargs.append(

                             { ‘key’ : value }

                   )

                      #append the data from the model in the variable in a dictionary#
                        #return docargs#
                       #this dictionary is then returned at the end#

Now we’ll create our main method which will be called on the button & will generate our excel report.

#CODE#

 

          @api.multi

          def print_report_xls(self):

              workbook = xlwt.Workbook()  

                             #created an xls workbook using xlwt library of python in odoo this will generate a Workbook# 

                             #Make sure that you have imported this library above like this:#

                             #from odoo.tools.misc import xlwt #

                         Header_style =

                            format_common.font_style(position='center', bold=1, fontos='black',

                           font_height=220, color='grey')

#you can create & design your own style for the column of the excel report

In this case i have created a file format_common consisting of various fonts and there designs & then import that file inside this file & used the format_common to design font style for a particular column#   

                              sheet = workbook.add_sheet('My XLS Report')

#this will add a sheet named “My XLS Report” to the workbook created earlier#

                              sheet.row(3).height = 256 * 2

#we can also specify height/width for a particular row or column like this#

                             sheet.write_merge(3, 3, 0, 11, u'My XLS Report', Header_tstyle)

#This is a very important step to write data on to the columns & rows in the excel file. The syntax is as follows:

                                      *sheet = sheet on which data is to be added

*write_merge = used to write data on merged columns & rows mentioned in the parameters. In this case, (3,3 specify row 1 & row 2, which will be merged, and (0,11) specify column 1 & column that will be merged.

                                       Next we have added a static header which will be the header of the report and then specified the column style mentioned above.You can                                        also add your own style for the column here.)

                   **write = you can also use write function instead of write_merge if you want to write on a single column or row.

 

                                 sheet.write_merge(7, 7, 0, 1, u'Header 1', header_tstyle_c)

                                 sheet.write_merge(7, 7, 2, 2, u'Header 2', header_tstyle_c)

                                 sheet.write_merge(7, 7, 3, 4, u'Header 3', header_tstyle_c)

                                 sheet.write_merge(7, 7, 5, 6, u'Header 4', header_tstyle_c)

                                 sheet.write_merge(7, 7, 7, 7, u'Header 5', header_tstyle_c)    

#you can define as many headers required in your file statically like this# 

                             data = self._get_data()

                                                    #now,call the method in which we have called the data above in a variable#

                                                    #After that, you can then use this variable to get data into the report#

                              row = 8

                              for h in data:

                                        sheet.write_merge(row, row, 0, 0, h.get().date_from.strftime('%d/%B/%Y'), values)

                                        sheet.write_merge(row, row, 1, 1, h.get().date_to.strftime('%d/%B/%Y'), values)

                                   row += 1

#you can also define a particular row no. and then use this row variable#

#here in this case, the ‘data’ variable is iterated to get data from the method#

                                   #also the date filter is used to filter the data based on the dates and then the ‘values’ variable is used for defining the style of the                                                       column#

                          stream = io.BytesIO()

                          workbook.save(stream)

#this will finally append the data to the sheet & then you can return the data as an output#

              Classname('report.module_name.report_name.xlsx', 'model_name')

                                                    #Here report name is the name that’ll be printed for this report#

              return self.env.ref('module name.report_action').report_action(self)

                                        #return the report action for the output here.

Report action is the action view generated for creating an id as an action for the report. You can create a report action in your view file like this:

#CODE#

 

<report id="report_action_id"

          model="model.model"

          string="Custom XLS Report"

          report_type="xlsx"

          name="module_name.report_name.xlsx"

          file="module_name.report_name.xlsx"

          attachment_use="False"/>

            Here,

                   - Id:- Report’s external id

                   - string:- Report String Name

- model:- It is a mandatory field which represents the model of the  report

                   - report_type:- Type of report.  (in this case xlsx)

- Name:- It is a mandatory field. It is useful for description of the report when looking for one in a list of some sort.

                   - File:- File Name

 After returning the output, you just need to enter data to the wizard date fields & click on ‘Print Report’.

It’ll generate an XLS report like this:

Create an Excel/ XLS Report

In this case, I have created the XLS report for payment summary details of HR Payroll in Odoo.

You can follow the above steps to create an excel report in any module in Odoo. Just make sure to import the correct libraries. To make your excel reports more attractive, you can also use various styling and color for your headers & columns.

You can also use ‘time period’ as a filter for data in the reports or also print the summary for a particular employee by using employee_id as a domain in odoo.

This XLS report is generated in version 12 of Odoo. You can follow the same procedure to generate an excel report in other versions of ODOO also.

I hope this blog proves helpful to you. If you need any kind of help regarding Odoo Customization or Odoo module Development, feel free to Contact Us.


Read our other related blogs...

If you want to Generate Report in PDF format you can check the step by step process here: How To Develop a New Report In Odoo
Checkout our Odoo App for generating XLS reports in Accounting : Click Here
Explore the world of XLSX attachment previews in Odoo Chatter

Leave a Comment

Your email address will not be published.

Submit
Your comment is under review by our moderation team.