Enable Dark Mode!
import-xlsx-files-in-odoo-using-openpyxl.jpg
By: Arunima

Import XLSX Files in Odoo Using Openpyxl

Technical

To import data into Odoo, we need to select the file in .csv or .xlsx format and upload it. To know how to import data into Odoo, check out this blog: How to import data in Odoo

In this blog I am going to share a simple way to import an XLSX file to Odoo via Python code using Openpyxl  tool.

Here I am going to import customer’s data to Odoo. Let’s take the XLSX file below as an example.


customers.xlsx

import-xlsx-files-in-odoo-using-openpyxl-cybrosys

Reference - Customer Internal Reference

Customer - Customer Name

Address - Street

State - State Name

Country - Country Code

Zip - Zip Code

Phone - Phone

Email - Email


Now Let’s create a wizard to upload the file and a button to perform the python function.

Add below code to your python file.

from odoo import models, fields,_
import openpyxl
import base64
from io import BytesIO
from odoo.exceptions import UserError
class ImportCustomerWizard(models.TransientModel):
   _name = "import.customer.wizard"
   file = fields.Binary(string="File", required=True)

Now add the following code to your xml file.

<?xml version="1.0" encoding="utf-8" ?>
<odoo>
   <record id="import_customer_wizard" model="ir.ui.view">
       <field name="name">import.customer.wizard</field>
       <field name="model">import.customer.wizard</field>
       <field name="arch" type="xml">
           <form string="Import Customers">
               <group>
                   <group>
                       <field name="file"/>
                   </group>
               </group>
               <footer>
                   <button
                       name="import_customer"
                       string="Import"
                       type="object"
                       class="btn-primary"
                   />
               </footer>
           </form>
       </field>
   </record>
   <record id="import_customer_action" model="ir.actions.act_window">
       <field name="name">Import Customer</field>
       <field name="res_model">import.customer.wizard</field>
       <field name="view_mode">form</field>
       <field name="view_id" ref="import_customer_wizard" />
       <field name="target">new</field>
   </record>
   <menuitem id="customer_import_menu"
       name="Import Customers"
       action="import_customer_action"
       parent="contacts.menu_contacts"/>
</odoo>

This will create a menu ’Import Customers’ in the Contacts module. Clicking on the menu will open a wizard shown below.

import-xlsx-files-in-odoo-using-openpyxl-cybrosys

Openpyxl is a Python library to read/write Excel files. First we need to import the openpyxl library inorder to read the excel sheet.

Let’s take a look into the function of the import button in the wizard.

def import_customer(self):
try:
wb = openpyxl.load_workbook(
    filename=BytesIO(base64.b64decode(self.file)), read_only=True
)
ws = wb.active
for record in ws.iter_rows(min_row=2, max_row=None, min_col=None,
                            max_col=None, values_only=True):
    # search if the customer exist else create
    search = self.env['res.partner'].search([
        ('name', '=', record[1]), ('customer_rank', '=', True)])
    if not search:
        self.env['res.partner'].create({
            'ref': record[0],
            'name': record[1],
            'street': record[2],
            'state_id': self.env['res.country.state'].search([
                ('name', '=', record[3])]).id,
            'country_id': self.env['res.country'].search([
                ('code', '=', record[4])]).id,
            'zip': record[5],
            'phone': record[6],
            'email': record[7],
            'customer_rank': True
        })
except:
raise UserError(
    _('Please insert a valid file'))

load_workbook() - Open the file and return the workbook.

Parameters:

    filename  – the path to open or a file-like object

    read_only (bool) – for reading the file, content cannot be edited

ws = wb.active - Get workbook active sheet object from the active attribute

iter_rows(min_row=None, max_row=None, min_col=None,max_col=None, values_only=False) - It will return tuple of cells by row.


Each of the iteration records will return datas from each row. The row values ??n be read by  using  index  v?lues i.e, re??rd[0] is the first ??lumn ?f the ex?el sheet.

Parameters:

min_col (int) – smallest column index.

min_row (int) – smallest row index.

max_col (int) – largest column index.

max_row (int) – largest row index.

values_only (bool) – if only the cell values to be returned.

Here the record will give a tuple of cells in each row in each iteration.

Now click on the Import button, in the Customers menu in Odoo you can view the customers you have imported.

import-xlsx-files-in-odoo-using-openpyxl-cybrosys

This example provides insight on importing customers to the Odoo platform. In this manner you will be able to import XLSX files into Odoo using the Openpyxl.



If you need any assistance in odoo, we are online, please chat with us.



1
Comments

Mor Tall Seck

Hi Hello I am using Odoo 14 community and I am trying to import the openpyxl package without success. Outside of Odoo it works very well but in Odoo this message ModuleNotFoundError: No module named 'openpyxl'. I install them globally on the machine but also in the development environment I remind you that I use pycharm as IDE. Please help me

01/12/2021

-

10:45AM

reply REPLY

Cybrosys

This error is coming because the package not installed successfully.Please try, pip3 install openpyxl If you have installed it already please try this in pycharm, Settings --> Python Interpreter --> Select your Environment ,Then install openpyxl



Leave a comment

 


whatsapp_icon
location

Calicut

Cybrosys Technologies Pvt. Ltd.
Neospace, Kinfra Techno Park
Kakkancherry, Calicut
Kerala, India - 673635

location

Kochi

Cybrosys Technologies Pvt. Ltd.
1st Floor, Thapasya Building,
Infopark, Kakkanad,
Kochi, India - 682030.

location

Bangalore

Cybrosys Techno Solutions
The Estate, 8th Floor,
Dickenson Road,
Bangalore, India - 560042

Send Us A Message