+91 9818211679, +91 9998040067       [email protected]

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 XLS report. XLS report’s 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 create this XLS report using a wizard in Odoo.

Odoo wizard is used to provide an interactive session to user 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 user and process it according to our business logic.

So we’ll first create a wizard which’ll help 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, 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-xls-report-in-odoo

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-xls-report-in-odoo
create-xls-report-in-odoo

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 the row 1 & row 2 which will be merged and (0,11) specify the 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-xls-report-in-odoo

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 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.

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

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

There are no comments for now.


There are no comments for now.