Enable Dark Mode!
excel-report-using-xlwt-package.jpg
By: Sreenath K

Excel Report Using xlwt Package

Technical

An Excel report is one of the basic reporting tools especially used for monitoring business processes that help to analyze the content very easily. In Odoo we mostly create excel reports using the Xlsx Writer package which is mostly used for Specific Formats Processing and in MS Office.  Moreover, xlwt library is used to generate files that are compatible with Microsoft Excel version 95 to 2003 and is mostly used in Specific Formats Processing, Office, HTTP, Database, Financial, Internet, WWW, Dynamic Content, Business, Spreadsheet, and CGI Tools.
If you don't know how to create an xlsx report in Odoo without a dependent module, You can read our blog on how to generate xlsx Report Using Controller using the following link.
Furthermore, if you need to know about how to create an xlsx report with a dependent module such as Base Report xlsx, read the following blog: How to Create a XLS Report in Odoo
This blog will provide an insight on how to create an excel report using the xlwt package.
First of all, we should print an xlsx report from a wizard. Here I am going to create an xlsx wizard for filtering the report based on the data.
When you click on the generate report button the report generated and saved in a field with a file name. 
Further, then import the xlwt in the python file and when you click on the generate report button action_advance_salary_report function in the python file gets executed.

The following code depicts the generation of a Python File.
# -*- coding: utf-8 -*-
import xlwt
import base64
from io import BytesIO
from odoo import models, fields, api, _
from odoo.exceptions import UserError, ValidationError, Warning
from datetime import date
class AdvanceReport(models.TransientModel):
   _name = "advance.report"
   date = fields.Date(string='Date', required=True,  default=date.today().replace(day=11))
   company_id = fields.Many2one('res.company', string="Company")
   summary_data = fields.Char('Name', size=256)
   file_name = fields.Binary('Pay Slip Summary Report', readonly=True)
   state = fields.Selection([('choose', 'choose'), ('get', 'get')],
                            default='choose')
   _sql_constraints = [
       ('check', 'CHECK((start_date <= end_date))', "End date must be greater then sPFrt date")
   ]
   def action_xlwt_report(self):
       company_name = self.company_id.name
       file_name = 'Report.xls'
       workbook = xlwt.Workbook(encoding="UTF-8")
       format0 = xlwt.easyxf(
           'font:height 500,bold True;pattern: pattern solid, fore_colour gray25;align: horiz center; borders: top_color black, bottom_color black, right_color black, left_color black,\
                             left thin, right thin, top thin, bottom thin;')
       formathead2 = xlwt.easyxf(
           'font:height 250,bold True;pattern: pattern solid, fore_colour gray25;align: horiz center; borders: top_color black, bottom_color black, right_color black, left_color black,\
                             left thin, right thin, top thin, bottom thin;')
       format1 = xlwt.easyxf('font:bold True;pattern: pattern solid, fore_colour gray25;align: horiz left; borders: top_color black, bottom_color black, right_color black, left_color black,\
                             left thin, right thin, top thin, bottom thin;')
       format2 = xlwt.easyxf('font:bold True;align: horiz left')
       format3 = xlwt.easyxf('align: horiz left; borders: top_color black, bottom_color black, right_color black, left_color black,\
                             left thin, right thin, top thin, bottom thin;')
       sheet = workbook.add_sheet("Payslip Summary Report")
       sheet.col(0).width = int(7 * 260)
       sheet.col(1).width = int(30 * 260)
       sheet.col(2).width = int(40 * 260)
       sheet.col(3).width = int(20 * 260)
       sheet.row(0).height_mismatch = True
       sheet.row(0).height = 150 * 4
       sheet.row(1).height_mismatch = True
       sheet.row(1).height = 150 * 2
       sheet.row(2).height_mismatch = True
       sheet.row(2).height = 150 * 3
       sheet.write_merge(0, 0, 0, 3, 'Advance Bank Report', format0)
       sheet.write_merge(1, 1, 0, 3, 'Date:' + str(self.date), formathead2)
       sheet.write_merge(2, 2, 0, 3, 'Company : ' + company_name, formathead2)
       sheet.write(3, 0, 'Sl.No#', format1)
       sheet.write(3, 1, 'Employee Bank Account', format1)
       sheet.write(3, 2, 'Employee Name', format1)
       sheet.write(3, 3, 'Amount', format1)
       fp = BytesIO()
       workbook.save(fp)
       self.write(
           {'state': 'get', 'file_name': base64.encodestring(fp.getvalue()), 'summary_data': file_name})
       fp.close()
       return {
           'type': 'ir.actions.act_window',
           'res_model': 'advance.report',
           'view_mode': 'form',
           'view_type': 'form',
           'res_id': self.id,
           'target': 'new',
       }
Now let's move on to generating the XML file using the following code:
<?xml version="1.0" encoding="utf-8"?>
<odoo>
  <record id="salary_advance_wizard" model="ir.ui.view">
     <field name="name">salary.report.wizard</field>
     <field name="model">advance.report</field>
     <field name="arch" type="xml">
        <form string="Excel Report">
           <field name="state" invisible="1"/>
           <div states="get">
              <group>
                 <field name="summary_data" colspan="4" invisible="1"/>
                 <field name="file_name" filename="summary_data" colspan="4"/>
              </group>
           </div>
           <div states="choose">
              <group>
                 <group>
                    <field name="date" />
                    <field name="company_id" required="1"/>
                 </group>
              </group>
           </div>
           <footer states="choose">
              <button string="Generate Report"
                           class="btn-primary"
                           name="action_xlwt_report"
                           type="object"/>
              <button string="Cancel"
                           class="btn-default"
                           special="cancel" />
           </footer>
           <footer states="get">
              <button special="cancel" string="Cancel" class="oe_highlight" />
           </footer>
        </form>
     </field>
  </record>
  <record id="action_advance_xlwt" model="ir.actions.act_window">
     <field name="name">Excel Report</field>
     <field name="type">ir.actions.act_window</field>
     <field name="res_model">advance.report</field>
     <field name="view_mode">form</field>
     <field name="view_id" ref="salary_advance_wizard"/>
     <field name="target">new</field>
  </record>
  
  <menuitem id="child_menu_report"
             name="XLWT Report"
             parent="your_module_name.parent_menu_salary_advance"
             action="action_advance_xlwt"/>
</odoo>
Moreover, in the python file we are importing the xlwt package and created a Xls workbook using xlwt library to generate a workbook using the code as described below:
workbook = xlwt.Workbook(encoding="UTF-8")
In addition, if you want to create your own style you can create a file format consisting of various fonts and designs. After creating that you can import that file into the respective file as shown in the following command:
format0 = xlwt.easyxf(
                   'font:height 500,bold True;pattern: pattern solid, fore_colour gray25;align: horiz center; borders: top_color black, bottom_color black, right_color black, left_color black,\
                   left thin, right thin, top thin, bottom thin;'
                   )
sheet.write_merge(0, 0, 0, 3, 'Advance Bank Report', format0)
As the mentioned way above you can add your style in this report and upon you select the XLWT report menu a wizard as shown below:
excel-report-using-xlwt-package-cybrosys
After providing the details you can click the Generate Report button available as shown in the above image to generate xls report using xlwt package. Then it will save the file with the respective file name into the field.
excel-report-using-xlwt-package-cybrosys
When you click on that report.xls file the report will be downloaded. The description of the report will be as shown below:
excel-report-using-xlwt-package-cybrosys
The excel report generation using the xlwt packets is useful functionality for the report generations of the ongoing operations as well as the ones which have been completed in Odoo.


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



0
Comments



Leave a comment

 


whatsapp
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