Import XLSX Files in Odoo Using Openpyxl

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

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.

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.

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.