Excel Reports are an inevitable part of a business to keep and manipulate business data. Odoo helps users to download data as excel documents. To create and download custom excel reports in Odoo, we usually require a ‘Base Report xlsx’ module. If you want to generate excel reports using ‘Base Report xlsx’ module, please refer to the following App link https://apps.odoo.com/apps/modules/12.0/report_xlsx/
If you want to generate excel reports without the support of other modules, you can use the ‘Xlwt’ python library. You can refer to the following blog for a detailed description of how to use xlwt package to generate an excel report.
If you don’t want to use a supporter module or an additional package, you can generate excel reports with the help of the controller function and javascript function. To know more, please refer to the following blog.
This blog discusses how you can create excel reports in Odoo without using the ‘Base report xlsx’ module or other python packages or javascript functions. The URL action called from the button action function renders the download link for the excel report.
Add a button in the form view of the model (here ‘invoice.reports’) from where the report is to be downloaded.
XML file
<button name="print_xlsx_report" type="object" string="Print Excel Report" class="btn-primary"/>
The button function ‘print_xlsx_report’ is as shown below. It will redirect to the controller function with the specified URL.
def print_xlsx_report(self):
return {
'type': 'ir.actions.act_url',
'url': '/invoicing/excel_report/%s' % (self.id),
'target': 'new',
}
In the controller.py file, add the function to redirect to the excel report download link. Mention the rout as specified in the ‘print_xlsx_reports’. Here, ‘report_id’ is the parameter that holds the id of the current record.
class InvoiceExcelReportController(http.Controller):
@http.route([
'/invoicing/excel_report/<model("invoice.reports"):report_id>',
], type='http', auth="user", csrf=False)
def get_sale_excel_report(self, report_id=None, **args):
response = request.make_response(
None,
headers=[
('Content-Type', 'application/vnd.ms-excel'),
('Content-Disposition', content_disposition('Invoice_report' + '.xlsx'))
]
)
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
#get data for the report.
report_lines = report_id.get_report_lines()
# prepare excel sheet styles and formats
sheet = workbook.add_worksheet("invoices")
sheet.write(1, 0, 'No.', header_style)
sheet.write(1, 1, 'Invoice Reference', header_style)
sheet.write(1, 2, 'Customer', header_style)
row = 2
number = 1
# write the report lines to the excel document
for line in report_lines:
sheet.set_row(row, 20)
sheet.write(row, 0, number, text_style)
sheet.write(row, 1, line['move_id'], text_style)
sheet.write(row, 2, line['partner_id'], text_style)
row += 1
number += 1
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
return response
Add the function ‘get_report_lines’ in the model ‘invoice.reports’ to return data to be displayed in the report. This function is called from the controller function to download excel documents.
def get_report_lines(self):
invoice_list = []
for move in self.env['account.move'].search([]):
results = self.env['account.move.line'].search(
[('move_id', '=', move.id), ('product_id', '=', self.product_id.id)])
if results:
line = {'move_name': move.name,
'partner_id': move.partner_id.name,
}
invoice_list.append(line)
return invoice_list
So, when clicking on the print excel report button, this controller function will redirect to the download link, and the excel report will be downloaded.