odoo bom结构层级打印实现

bom结构打印最终实现效果

import logging

from odoo import _, models
from odoo.exceptions import CacheMiss

_logger = logging.getLogger(__name__)


class ExportBomStructureXlsx(models.AbstractModel):
    _name = "report.mrp_bom_structure_xlsx.export_bom_structure_xlsx"
    _description = "Export BOM Structure XLSX Report"
    _inherit = "report.report_xlsx.abstract"

    def compute_color(self, i):
        if i == 1:
            return "#669237"
        elif i == 2:
            return "#92d14f"
        elif i == 3:
            return "#bee395"
        elif i == 4:
            return "#d3edb9"
        elif i == 5:
            return "#f4faed"
        elif i >= 6:
            return "#fdfefc"

    def compute_qty(self, i):
        if i == 1:
            return "一"
        elif i == 2:
            return "二"
        elif i == 3:
            return "三"
        elif i == 4:
            return "四"
        elif i == 5:
            return "五"
        elif i == 6:
            return "六"
        elif i > 6:
            return i

    def _define_formats(self, workbook, sheet, z, i):
        color = self.compute_color(i)
        qty = self.compute_qty(i)
        align_center_b = workbook.add_format({'align': 'center', 'bold': True, "bg_color": color, 'border': 1})
        row = 0
        col = 8
        wz = col * i
        sheet.merge_range(row, wz, row, wz + 7, _("%s 级" % (qty)), align_center_b)
        # sheet.set_landscape()
        # sheet.fit_to_pages(1, 0)
        sheet.set_zoom(80)
        sheet.set_column(col * i + 0, col * i + 0, 30)
        sheet.set_column(col * i + 1, col * i + 2, 20)
        sheet.set_column(col * i + 3, col * i + 3, 20)
        sheet.set_column(col * i + 4, col * i + 6, 10)
        title_style = workbook.add_format(
            {"bold": True, "bg_color": color, "bottom": 1, 'left': 1, 'right': 1, 'top': 1}
        )
        sheet_title = [
            _("图号(型号)"),
            _("产品类别"),
            _("名称"),
            _("规格型号"),
            _("重量"),
            _("单位"),
            _("数量"),
            _("层级"),
        ]
        # sheet.set_row(0, None, None, {"collapsed": 1})
        sheet.write_row(1, wz, sheet_title, title_style)
        sheet.freeze_panes(2, 0)

    def print_bom_fill(self, ch, sheet, row, level, z, workbook):
        i, j = row, level
        i += 1
        j += 1
        qty = j
        z = z - 8
        bold = workbook.add_format({"bold": True})
        sheet.write(i, z + 0, ch.product_tmpl_id.default_code or "", bold)
        sheet.write(i, z + 1, ch.product_tmpl_id.categ_id.name or "", bold)
        sheet.write(i, z + 2, ch.product_tmpl_id.name or "", bold)
        sheet.write(i, z + 3, ch.product_tmpl_id.x_specifications or "", bold)
        sheet.write(i, z + 4, ch.product_tmpl_id.weight or "", bold)
        sheet.write(i, z + 5, ch.product_uom_id.name or "", bold)
        sheet.write(i, z + 6, ch.product_qty or "", bold)
        sheet.write(i, z + 7, qty or "", bold)
        try:
            # line = len(ch.mapped('child_line_ids'))
            # for r in range(len(ch.mapped('child_line_ids'))):
            #     self.print_bom_fill(ch, sheet, i, j, z, workbook)
                # return
            for fill in ch.child_line_ids.filtered(lambda m: m.product_tmpl_id.categ_id.name == 'Z分组'):
                i = self.print_bom_fill(fill, sheet, i, j, z, workbook)
        except CacheMiss:
            pass
        return i

    def print_bom_children(self, ch, sheet, row, level, z, workbook):
        i, j = row, level
        j += 1
        qty = j
        self._define_formats(workbook, sheet, z, qty)
        sheet.write(i, z + 0, ch.product_tmpl_id.default_code or "")
        sheet.write(i, z + 1, ch.product_tmpl_id.categ_id.name or "")
        sheet.write(i, z + 2, ch.product_tmpl_id.name or "")
        sheet.write(i, z + 3, ch.product_id.x_specifications or "")
        sheet.write(i, z + 4, ch.product_tmpl_id.weight or "")
        sheet.write(i, z + 5, ch.product_uom_id.name or "")
        sheet.write(i, z + 6, ch.product_qty or "")
        sheet.write(i, z + 7, qty or "")
        i += 1
        z += 8
        row = i
        # self.print_bom_fill(ch, sheet, row, level, z, workbook)
        try:
            for child in ch.child_line_ids:
                i = self.print_bom_children(child, sheet, i, j, z, workbook)
                row = self.print_bom_fill(ch, sheet, row, level, z, workbook)
        except CacheMiss:
            pass
        j -= 1
        z -= 8
        return i

    def generate_xlsx_report(self, workbook, data, objects):
        workbook.set_properties(
            {"comments": "Created with Python and XlsxWriter from Odoo 13.0"}
        )
        sheet = workbook.add_worksheet(_("BOM Structure"))
        align_center_b = workbook.add_format({'align': 'center', 'bold': True, "bg_color": "#FFFFCC", 'border': 1})
        row = 0
        col = 0
        sheet.merge_range(row, col, row, col + 7, 'Bom', align_center_b)
        sheet.set_landscape()
        sheet.fit_to_pages(1, 0)
        sheet.set_zoom(80)
        sheet.set_column(0, 0, 30)
        sheet.set_column(1, 2, 20)
        sheet.set_column(3, 3, 20)
        sheet.set_column(4, 7, 10)
        # bold = workbook.add_format({"bold": True})
        title_style = workbook.add_format(
            {"bold": True, "bg_color": "#FFFFCC", "bottom": 1, 'left': 1, 'right': 1, 'top': 1}
        )
        sheet_title = [
            _("图号(型号)"),
            _("产品类别"),
            _("名称"),
            _("规格型号"),
            _("重量"),
            _("单位"),
            _("数量"),
            _("层级"),
        ]
        sheet.set_row(0, None, None, {"collapsed": 1})

        sheet.write_row(1, 0, sheet_title, title_style)
        sheet.freeze_panes(2, 0)
        i = 2
        for o in objects:
            sheet.write(i, 0, o.product_tmpl_id.default_code or "")
            sheet.write(i, 1, o.product_tmpl_id.categ_id.name or "")
            sheet.write(i, 2, o.product_tmpl_id.name or "")
            sheet.write(i, 3, o.product_id.x_specifications or "")
            sheet.write(i, 4, o.product_tmpl_id.weight)
            sheet.write(i, 5, o.product_uom_id.name or "")
            sheet.write(i, 6, o.product_qty or "")
            sheet.write(i, 7, "Bom")
            i += 1
            j = 0
            z = 8
            for ch in o.bom_line_ids:
                i = self.print_bom_children(ch, sheet, i, j, z, workbook)