from odoo import api, models
import datetime


class CategoryReportXls(models.AbstractModel):
    _name = 'report.category_inventory_report.category_wise_report_xls'
    _inherit = 'report.report_xlsx.abstract'

    def generate_xlsx_report(self, workbook, data, products):
        domain = []
        if data.get('date_from'):
            domain.append(('create_date', '>=', data.get('date_from')))
        if data.get('date_to'):
            domain.append(('create_date', '<=', data.get('date_to')))
        if data.get('categ_ids'):
            domain.append(('categ_id', 'in', data.get('categ_ids')))

        report_name = 'Category Wise Inventory Report'
        sheet = workbook.add_worksheet(report_name)
        bold = workbook.add_format({'bold': True})
        title = workbook.add_format({'bold': True, 'align': 'center', 'font_size': 20})
        sub_heading = workbook.add_format(
            {'align': 'center', 'bold': True})
        txt = workbook.add_format({'align': 'left'})
        num = workbook.add_format({'align': 'right'})
        head = workbook.add_format({'align': 'center', 'bold': True})
        sheet.merge_range('A1:F1', 'Category Wise Inventory Report', title)
        sheet.merge_range('B2:E2', 'Company Name' + ':' + self.env.user.company_id.name, head)

        products = self.env['product.template'].search(domain)
        col = 0
        row = 3
        sheet.write(row, col, 'Category', sub_heading)
        row = 3
        col = 1
        sheet.set_column(0, 0, 20)
        sheet.set_column(1, 1, 35)
        sheet.set_column(4, 4, 15)
        sheet.set_column(5, 5, 15)

        sheet.write(row, col, 'Product', sub_heading)
        sheet.write(row, col + 1, 'Qty', sub_heading)
        sheet.write(row, col + 2, 'Unit', sub_heading)
        sheet.write(row, col + 3, 'Avrg. Unit Cost', sub_heading)
        sheet.write(row, col + 4, 'Stock Value', sub_heading)
        row += 2
        
        total_subtotal = 0
        for cat in data.get('categ_ids'):
            cat_id = self.env['product.category'].search([('id', '=', cat)])
            sheet.merge_range(f"A{row}:F{row}", cat_id.name)
            cat_subtotal = 0
            for product in products:
                if product.categ_id == cat_id:
                    # < !--
                    # for rec in products: -->
                    sheet.write(row, col, product.name, txt)
                    sheet.write(row, col + 1, product.qty_available, num)
                    sheet.write(row, col + 2, product.uom_id.name, txt)
                    sheet.write(row, col + 3, product.avarage_cost, num)
                    sheet.write(row, col + 4, product.qty_available * product.avarage_cost, num)
                    total_subtotal = total_subtotal + product.qty_available * product.avarage_cost
                    cat_subtotal += product.qty_available * product.avarage_cost
                    row += 1
                # sheet.write(row, col + 3, 'Category Total', bold)
                # sheet.write(row, col + 4, '{:,.2f}'.format(cat_subtotal), bold)


                # < !--
                # if product.categ_id: -->
                # row += 1

            sheet.write(row, col + 3, 'Grand Total', bold)
            sheet.write(row, col + 4, '{:,.2f}'.format(total_subtotal), bold)

How can I show Category total in the end of every category in excel report. Please help me