Import xls file Odoo 11 – Sales orders

odoo import xls

I will describe the basis in python to import xls file and how to handle the imported data. This exemple is importing sales orders, but it should work the same way with other models. Uploaded file is encoded in base 64, to be able to read it, we have to decode first data by using b64decode function. import_action function will write the 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 *

This site uses Akismet to reduce spam. Learn how your comment data is processed.