Import xls file Odoo 11 – Sales orders

odoo import xls

Bellow code will describe the basis to import xls file and work on the imported data, in this exemple I choose to add some sales orders but it could be done with any other model/class. Uploaded files are encoded in base 64, to be able to save and read it, it needs to be decoded first by using b64decode function. import_action function will write the chosen file inside the tmp folder, xls file headers (1st row of the sheet) will be stored in the variable headers array and all rows in import_data array.

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 xls"

    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):
        data = base64.b64decode(self.upload_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
        #header
        headers = []
        for col_idx in range(0, num_cols):
            cell_obj = xl_sheet.cell(0, col_idx)
            headers.append(cell_obj.value)
        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)
        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
                })

Leave a Reply

Your email address will not be published. Required fields are marked *