How to Generate & Download Excel report in Odoo
Most Odoo developers face the hassle of generating and downloading excel reports in odoo. In this article, i will provide code steps on how to achieve this without stress.
Step 1:
Create a python file and Import the necessary libraries to use:
import io
import xlwt
from datetime import datetime, timedelta
import base64
from odoo.exceptions import ValidationError
from odoo import fields, models, api, _
Step 2:
Define the class
class ExportRecords(models.TransientModel):
….._name = “excel.report”
…..excel_file = fields.Binary(‘Download Excel file’, filename=’filename’, readonly=True)
…..filename = fields.Char(‘Excel File’, size=64)
…..filter_by = fields.Selection([(‘all’, ‘All records’),(‘date’, ‘Date’),], required=True, default=’date’)
…..start_date = fields.Date(‘Start Date’)
…..end_date = fields.Date(‘End Date’)
…..def validate_date_filters(self):
.….…..if self.end_date and self.end_date < self.start_date:
……..…..self.end_date = False
.….……..raise ValidationError(“End date must be greater than Start date”)
.….def export_data(self):
.…..….self.validate_date_filters()
.…..….domain = []
.…..….if self.filter_by == “date”:
.…..….….domain = [(‘create_date’, ‘>=’, self.start_date),(‘create_date’, ‘<=’, self.end_date)]
.….….headers = [‘Evaluation #’, ‘Date’, ‘Care Provider’, ‘Evaluation Type’, ‘Patient Age’, ‘Sex’, ‘State’]
….…..style0 = xlwt.easyxf(‘font: name Times New Roman, color-index red, bold on’,num_format_str=’#,##0.00')
……….style1 = xlwt.easyxf(num_format_str=’DD-MMM-YYYY’)
……….evaluations = Eval.search(domain)
……….wb = xlwt.Workbook()
……….ws = wb.add_sheet(‘REPORT’)
…..…..colh = 0
……….if evaluations:
…..……….ws.write(0, 6, ‘ RECORDS GENERATED FOR %s — %s’ %(self.start_date if self.start_date else ‘’, self.end_date if self.end_date else datetime.strftime(fields.Date.today(), ‘%Y-%m-%d’)), style0)
………..….for head in headers:
…..…..……….ws.write(1, colh, head)
…..……….…..colh += 1
…..…..….row = 3
………….for records in evaluations:
………..……..col = 0
………..……..evaluation_date = records.create_date.strftime(“%Y-%m-%d, %H:%M:%S”)
………..……..evaluation_number = records.name
………..……..eval_care_provider = records.care_provider.partner_id.name
………..……..eval_type = records.evaluation_type
………..……..eval_patient_age = records.patient.age_int
………..……..eval_patient_sex = records.patient.sex
………..……..eval_state = records.state
………..……..ws.write(row, col, evaluation_number)
………..……..ws.write(row, col + 1, evaluation_date, style1)
………..……..ws.write(row, col + 2, eval_care_provider)
………..……..ws.write(row, col + 3, eval_type)
………..……..ws.write(row, col + 4, eval_patient_age)
………..……..ws.write(row, col + 5, eval_patient_sex)
………..……..ws.write(row, col + 6, eval_state)
………..……..row += 1
………….fp = io.BytesIO()
………….wb.save(fp)
………….filename = “REPORT {}.xls”.format(datetime.strftime(fields.Date.today(), ‘%Y-%m-%d’), style0)
………….self.excel_file = base64.encodestring(fp.getvalue())
………….self.filename = filename
………….fp.close()
……….else:
…………..raise ValidationError(‘No record found to Export’)
……….def button_export_and_download(self):
…..……….self.export_data()
…..……….return {
‘type’: ‘ir.actions.act_url’, ‘url’: ‘/web/content/model=excel.report&download=true&field=excel_file&id={}&filename={}’.format(self.id, self.filename),‘target’: ‘new’,‘nodestroy’: False,
}
Step 3:
Create an xml file for the wizard
<?xml version=”1.0" encoding=”utf-8"?>
<odoo>
<data noupdate=”0">
<record id=”view_report_export_form” model=”ir.ui.view”>
<field name=”name”>excel.report.form</field>
<field name=”model”>excel.report</field>
<field name=”arch” type=”xml”>
<form string=”Export Records As XLS”>
<group>
<group>
<! — <separator string=”Options” colspan=”4"/> →
<field name=”filter_by”/>
<field name=”start_date” string=”Start Date”
attrs=”{‘invisible’: [(‘filter_by’, ‘!=’, ‘date’)],
‘required’: [(‘filter_by’, ‘=’, ‘date’)]}”/>
<field name=”end_date” string=”End Date”
attrs=”{‘invisible’: [(‘filter_by’, ‘!=’, ‘date’)],
‘required’: [(‘filter_by’, ‘=’, ‘date’)]}”/>
</group>
<group>
<! — <separator string=”File” colspan=”2"/> →
<field name=”excel_file” nolabel=”0" filename=”filename”/>
<field name=”filename” invisible=”1"/>
</group>
</group>
<footer>
<button name=”button_export_and_download” type=’object’ string=”Export & Download” class=”oe_highlight”/>
or
<button string=”Cancel” class=”oe_link” special=”cancel”/>
</footer>
</form>
</field>
</record>
<record model=”ir.actions.act_window” id=”action_report_export”>
<field name=”name”>Export Report as XLS</field>
<field name=”res_model”>excel.report</field>
<field name=”view_type”>form</field>
<field name=”view_mode”>form</field>
<field name=”target”>new</field>
<field name=”view_id” ref=”view_report_export_form”/>
</record>
<menuitem id=”menu_report_data_export” name=”Export Report” action=”action_report_export” parent=”module.parent_menu” sequence=”101"/>
</data>
</odoo>
Step 4:
Go to the menu, click and a wizard will pop out, filter the start and end date, click on the Export & download button-Above code was replicated from a feature developed by Maduka Sopulu Christopher
