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.
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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', | |
} | |
0 Comments