odoo 导入execl动态删除列

2023-12-20 13:36:50

在导入execl时,客户要求模板并不是死模板,是可动态的删除某些列,以下实现
只需知道 要导入哪些重要信息接即可,
下面也可生成错误报告等

    def generate_error_excel(self, worksheet, workbook, row, row_line, error_reason):
        """ 生成错误数据报告excel """
        if row == 0:
            for col, column in enumerate(row_line):
                worksheet.write(0, col, column)
                worksheet.col(col).width = (15 * 367)
            worksheet.write(0, len(row_line), '错误提示')
        else:
            for line in range(len(row_line)):
                worksheet.write(row, line, row_line[line])
            worksheet.write(row, len(row_line), error_reason)
            # save
            buffer = io.BytesIO()
            workbook.save(buffer)
            self.update({'error_file': base64.encodebytes(buffer.getvalue()),
                         'error_name': '错误报告-{}'.format(self.file_name)})

    def btn_confirm(self):
        """确认导入"""
        workbook = xlwt.Workbook(encoding='utf-8')
        worksheet = workbook.add_sheet('导入数据问题', cell_overwrite_ok=True)

        wb = xlrd.open_workbook(file_contents=base64.decodebytes(self.file_upload or b''))
        sheets = wb.sheet_names()
        sheet1 = wb.sheet_by_name(sheets[0])
        row_num = sheet1.nrows  # 行数
        # col_num = sheet1.ncols  # 列数
        purchase_model = self.env['uoms.tracking.purchase'].sudo()
        purchase_line_model = self.env['uoms.tracking.purchase.line'].sudo()
        product_model = self.env['product.product'].sudo()
        abnormal_state_model = self.env['uoms.abnormal.state'].sudo()
        inventory_status_model = self.env['uoms.inventory.status'].sudo()
        execute_node_model = self.env['uoms.execute.node'].sudo()
        message = []
        try:
            error_row = 0
            sheet1_row = sheet1.row_values(0)
            if '采购订单号' not in sheet1_row:
                raise UserError(_("请添加列 采购订单号!"))
            if '货号' not in sheet1_row:
                raise UserError(_("请添加列 货号!"))
            if '行号' not in sheet1_row:
                raise UserError(_("请添加列 行号!"))
            sheet1_dict = {b: a for a,b in enumerate(sheet1_row)}
            field_data = self.judge_name_row(sheet1_dict)
            self.generate_error_excel(worksheet, workbook, error_row, sheet1.row_values(0), '')
            for row in range(1, row_num):
                field_value_dict = {}
                row_line = sheet1.row_values(row)
                for seq in field_data:
                    if not row_line[field_data[seq]]:
                        continue
                    field_value_dict[seq] = row_line[field_data[seq]]
                purchase_code = row_line[field_data['purchase_code']]
                product_code = int(row_line[field_data['product_code']]) if type(row_line[field_data['product_code']]) == float else row_line[field_data['product_code']]
                line_number = int(row_line[field_data['line_number']]) if type(row_line[field_data['line_number']]) == float else row_line[field_data['line_number']]
                purchase_id = purchase_model.search([('code', '=', str(purchase_code).strip())])
                if not purchase_id:
                    error_row += 1
                    self.generate_error_excel(worksheet, workbook, error_row, row_line,
                                              '未匹配到号为{}的采购订单号'.format(str(purchase_code).strip()))
                    continue
                product_id = product_model.search([('default_code', '=', str(product_code).strip())])
                if not product_id:
                    error_row += 1
                    self.generate_error_excel(worksheet, workbook, error_row, row_line,
                                              '未匹配货号为{}的物料'.format(str(product_code).strip()))
                    continue
                purchase_line_id = purchase_line_model.search(
                    [('kingee_purchase_number', '=', str(int(line_number)).strip()),
                     ('uoms_purchase_id', '=', purchase_id.id),
                     ('product_id', '=', product_id.id)])
                if not purchase_line_id:
                    error_row += 1
                    self.generate_error_excel(worksheet, workbook, error_row, row_line, '采购订单+行号+物料与系统内信息不一致')
                    continue
                field_value_dict['purchase_id'] = purchase_id.id
                field_value_dict['purchase_line_id'] = purchase_line_id.id
                field_value_dict['product_id'] = product_id.id
                foreign_planned_date, domestic_planned_date, next_date = self.get_date(field_data, row_line)
                if field_data.get('unusual'):
                    unusual_id = abnormal_state_model.search([('name', '=', str(row_line[field_data['unusual']]).strip())])
                    field_value_dict['unusual_id'] = unusual_id.id if unusual_id else False
                if field_data.get('inventory_id'):
                    inventory_id = inventory_status_model.search([('name', '=', str(row_line[field_data['inventory_id']]).strip())])
                    field_value_dict['inventory_id'] = inventory_id.id if inventory_id else False
                if foreign_planned_date:
                    field_value_dict['foreign_planned_date'] = foreign_planned_date
                if domestic_planned_date:
                    field_value_dict['domestic_planned_date'] = domestic_planned_date
                if next_date:
                    field_value_dict['next_date'] = next_date
                data = field_value_dict
                self.env['uoms.tracking.import'].with_context(from_interface=True).create(data)
        except Exception as e:
            message = str(e)
        if message:
            raise UserError('发生错误(%s), 请联系!!' % message)
        elif self.error_file:
            return {
                "name": _("导入问题向导"),
                "view_type": "form",
                "view_mode": "form",
                "res_model": "tracking.import.wizard",
                "res_id": self.id,
                "view_id": False,
                "views": [[self.env.ref("uoms_tracking.tracking_import_error_form").id, "form"]],
                "type": "ir.actions.act_window",
                "target": "new",
            }
        else:
            return {
                'type': 'ir.actions.client',
                'tag': 'display_notification',
                'params': {
                    'type': 'success',
                    'title': _('消息'),
                    'message': _('导入成功!'),
                    'sticky': False,
                    'next': {'type': 'ir.actions.act_window_close'},
                }
            }

    def judge_name_row(self, name_dict):
        """判断 模板名称 是否在列表里,"""
        field_data = {'purchase_code': name_dict['采购订单号'], 'product_code': name_dict['货号'], 'line_number': name_dict['行号']}
        if '供应商订单号' in name_dict:
            field_data['supplier_code'] = name_dict['供应商订单号']
        if '下次跟单日期' in name_dict:
            field_data['next_date'] = name_dict['下次跟单日期']
        if '异常情况' in name_dict:
            field_data['unusual'] = name_dict['异常情况']
        if '异常数量' in name_dict:
            field_data['unusual_qty'] = name_dict['异常数量']
        if '库存情况' in name_dict:
            field_data['inventory_id'] = name_dict['库存情况']
        if '厂家反馈' in name_dict:
            field_data['manufacturer_feedback'] = name_dict['厂家反馈']
        if '采购反馈' in name_dict:
            field_data['purchase_feedback'] = name_dict['采购反馈']
        if '国外-计划发货日期' in name_dict:
            field_data['foreign_planned_date'] = name_dict['国外-计划发货日期']
        if '国内-计划发货日期' in name_dict:
            field_data['domestic_planned_date'] = name_dict['国内-计划发货日期']
        if '计划发货数量' in name_dict:
            field_data['planned_shipment_quantity'] = name_dict['计划发货数量']
        return field_data

    def get_date(self, field_data, row_line):
        """返回 国内外 -计划发货日期"""
        foreign_date = row_line[field_data['foreign_planned_date']] if field_data.get('foreign_planned_date', False) else None
        domestic_date = row_line[field_data['domestic_planned_date']] if field_data.get('domestic_planned_date', False) else None
        next_date = row_line[field_data['next_date']] if field_data.get('next_date', False) else None
        if type(foreign_date) == float:
            foreign_planned_date = xldate_as_datetime(foreign_date, 0).date() if foreign_date else None
        else:
            foreign_planned_date = datetime.datetime.strptime(foreign_date, "%Y-%m-%d").date() if foreign_date else None
        if type(domestic_date) == float:
            domestic_planned_date = xldate_as_datetime(domestic_date, 0).date() if domestic_date else None
        else:
            domestic_planned_date = datetime.datetime.strptime(domestic_date, "%Y-%m-%d").date() if domestic_date else None
        if type(next_date) == float:
            next_date = xldate_as_datetime(next_date, 0).date() if next_date else None
        else:
            next_date = datetime.datetime.strptime(next_date, "%Y-%m-%d").date() if next_date else None
        return foreign_planned_date, domestic_planned_date, next_date

文章来源:https://blog.csdn.net/weixin_42464956/article/details/135104195
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。