Import xlsx file Odoo 11 – Sales orders

I will describe the basis in python to import xlsx file and how to handle the imported data, in this exemple sales orders is imported. Uploaded file is stored in the variable upload_file and is encoded in base 64, to read it we have to decode first the data by using b64decode function, then save it, for me i chose “/tmp” folder. Result import_data is an array of dictionary as follow [{'column_name_1':data_column_1_row_1, 'column_name_2':data_column_2_row_1,...},{'column_name_1':data_column_1_row_2, 'column_name_2':data_column_2_row_2,...},...]

import logging
import xlrd
import base64
import openerp.addons.decimal_precision as dp
from datetime import datetime, date, timedelta
from openerp.exceptions import UserError
from openerp import models, fields, api, _
_logger = logging.getLogger(__name__)

class Importation(models.TransientModel):
    _name = "module.importation"
    _description = "import xlsx"

    upload_file = fields.Binary(string="Upload File")
    file_name = fields.Char(string="File Name")
    
    #import xls file button
    @api.one
    def import_xls_action(self):
        #Decode data
        data = base64.b64decode(self.upload_file)
        #Save file
        with open('/tmp/' + self.file_name, 'wb') as file:
            file.write(data)
        xl_workbook = xlrd.open_workbook(file.name)
        sheet_names = xl_workbook.sheet_names()
        xl_sheet = xl_workbook.sheet_by_name(sheet_names[0])
        #Number of columns
        num_cols = xl_sheet.ncols
        #Extract headers from xls file
        headers = []
        for col_idx in range(0, num_cols):
            cell_obj = xl_sheet.cell(0, col_idx)
            headers.append(cell_obj.value)
        #Read xls file and build array of dictionary
        import_data = []
        for row_idx in range(1, xl_sheet.nrows):    # Iterate through rows
            row_dict = {}
            for col_idx in range(0, num_cols):  # Iterate through columns
                cell_obj = xl_sheet.cell(row_idx, col_idx)  # Get cell object by row, col
                row_dict[headers[col_idx]] = cell_obj.value
            import_data.append(row_dict)
        #Browse result and create instance of the sale.order model.
        for row in import_data:
            partner_id = self.env['res.partner'].search([('name','=',row['Customer'])])
            date_order = datetime.utcfromtimestamp((row['Order Date'] - 25569) * 86400.0)
            etd = datetime.utcfromtimestamp((row['ETD'] - 25569) * 86400.0)
            res_currency_id = self.env['res.currency'].search([('name','=',row['Currency'])])
            pricelist_id = self.env['product.pricelist'].search([('currency_id','=',res_currency_id.id)])
            self.env['sale.order'].create({
                'name':row['SO Number'],
                'partner_id':partner_id.id,
                'etd': etd,
                'date_order': date_order,
                'pricelist_id': pricelist_id.id
                })

Bellow XML view for the importation module.

<?xml version="1.0" encoding="utf-8"?>
<odoo>
	<data>
		<record id="importation_form_view" model="ir.ui.view">
			<field name="name">Importation module</field>
			<field name="model">module.importation</field>
			<field name="arch" type="xml">
				<form string="Importation">
					<sheet>
						<field name="upload_file" />
						<field name="file_name" invisible="1"/>
					</sheet>
				</form>
			</field>
		</record>
       </data>
</odoo>