Search Here

Import/Export/Download Data from Table into Excel File in Odoo14

 In this article, you will see how to import data coming from different tables into an excel file with a button click. To achieve this we are going to use pandas python library.

Import/Export/Download Data from Table into Excel File in Odoo14

How to Import/Export Data in Excel File From Odoo

We are going to import data from the PostgreSQL table into Excel File on button click. To import or download data into a spreadsheet (excel) from odoo14 we are going to use the pandas library. 

Read More: How to use self.env.cr.copy_from function to save data in database

The idea is first we fetch data from database using SQL query, then using pandas we will write the data (coming from the query) into excel file.

Read More: Write binary data into zip file and download it on button click in odoo

How to Read SQL Query using Pandas

df = pd.read_sql(query, self.env.cr.connection, index_col=['Student'])

How to Write and Prepare Data into Excel File using Pandas

io = BytesIO()
writer = pd.ExcelWriter(io)
df.to_excel(writer, "Student Info")
writer.save()
io.seek(0)
read_data = io.read()
io.close()
encoded_data = base64.encodestring(read_data)

Read More: Integration and Syncing of Data from Odoo 8 to Odoo 13

Import/Export Data from Table into Excel File in Odoo14


import pandas as pd
import numpy as np
import os
import re
def download_button(self):
query = """select registration_no as "Registration No.", name as "Student", father_name as "Father Name", login as "Login"
from some_table
where id = %s
order by registration_no
""" % (self.id)
self.env.cr.execute(query)
data = self.env.cr.fetchall()
if data and data[0]:
pass
else:
raise ValidationError('No Data found')
filename = "Student Record for %s" % (self.name)
filename = re.sub('[^A-Za-z0-9]+', '_', filename)
pd.set_option("display.precision", 8)
# read from sql query
df = pd.read_sql(query, self.env.cr.connection, index_col=['Student'])
# writing and preparing data
io = BytesIO()
writer = pd.ExcelWriter(io)
df.to_excel(writer, "Student Info")
writer.save()
io.seek(0)
read_data = io.read()
io.close()
encoded_data = base64.encodestring(read_data)
output = 'xls'
attach_vals = {
'name': '%s.%s' % (filename, output),
'db_datas': encoded_data,
'store_fname': '%s.%s' % (filename, output),
'is_temporary': True
}
doc_id = self.env['ir.attachment'].create(attach_vals)
return {
'type': 'ir.actions.act_url',
'url': '/web/report/download_report?model=ir.attachment&field=datas&id=%s&filename=%s.%s' % (doc_id.id, filename, output),
'target': 'new',
}

Post a Comment

0 Comments