Exporting Data to XLS/XLSX File Using Wizard Without Database Persistence

[aIexnder]

Hi everyone,

I'm looking for a solution to export data from an Odoo model to an XLS or XLSX file through a wizard without storing the information in the database. My goal is to extract the data directly from the model without persisting it in the database.

Could someone guide me on how I can achieve this? Are there specific modules or methods that I can use to implement this functionality?

I appreciate any help or suggestions you can provide.

[fraineralex]

Hi @aIexnder,

To achieve this, you can create a wizard model to persist dynamic fields, then generate an Excel report using the xlwt library. Save the workbook to BytesIO, encode in base64, attach it to the wizard record, and return an action for the user to download.

Important

You need to instale the xlwt python library

For example:

1. Create the Excel Workbook and Worksheet:

• Utilize the xlwt library to create the workbook and worksheet.
• Set up column widths and styles, including making headers bold.

# Import necessary libraries
from xlwt import Workbook, easyxf
import base64
from werkzeug import urls
import io

# Define headers for the Excel sheet
headers = ['Header1', 'Header2', 'Header3', ...]  # Add your headers as strings
title = 'ExportedData'  # Provide a title for the Excel sheet

# Create the Excel workbook and worksheet
workbook = Workbook()
worksheet = workbook.add_sheet(title)
excel_units = 256
column_width = 20 * excel_units
header_style = easyxf('font: bold on')

# Set column widths and write headers
for col_num, header in enumerate(headers):
    worksheet.col(col_num).width = column_width
    worksheet.write(0, col_num, header, header_style)

2. Populate the Worksheet with Data:

• Iterate through the rows of the selected model and fill in the worksheet.

for row_num, value in enumerate(selected_model_rows, start=1):
    # Implement validations
    # ...

    # Write data to the Excel worksheet
    worksheet.write(row_num, col_num, value)

3. Save Excel File to BytesIO and Encode in Base64:

• Save the workbook data to a BytesIO object and encode it in base64.

# Save workbook data to BytesIO and encode in base64
workbook_data = io.BytesIO()
workbook.save(workbook_data)
workbook_data.seek(0)
excel_base64 = base64.b64encode(workbook_data.getvalue())

4. Attach the File to the Wizard Record:

• Create an attachment in Odoo and associate it with the wizard record.

# Create an attachment in Odoo and associate it with the wizard record
attachment = self.env['ir.attachment'].create({
    'name': title + '.xls',
    'datas': excel_base64,
    'mimetype': 'application/vnd.ms-excel',
    'res_model': self._name,
    'res_id': self.id,
})

5. Return the Action for File Download:

• Provide an action to allow the user to download the attached Excel file.

# Return an action for the user to download the attached Excel file
return {
    'name': 'Download',
    'type': 'ir.actions.act_url',
    'url': '/web/content/%s?download=true' % attachment.id,
    'target': 'self',
}

By following these steps, you can implement the desired functionality for exporting data without storing it in the database.

Note

I have a repository where I do something similar: I export the payments selected by the user from the account.payment model to an xls file using a wizard, without storing anything permanently in the database, just as you want. Take a look at the batch_payment_export repository on my profile and feel free to use it as you wish.

I hope this information is helpful. Good luck!

[aIexnder]

Hi Frainer!

This is exactly what I wanted to know. I took a look at your batch_payment_export repository, and it closely resembles what I need to implement. Thank you so much.