so i need excel file that count how many employee finished his specific task (in this case, washing vehicle). i would like to total the amount of price with the specific vehicle washed in a package in Python. this is my code for excel file

    import base64
    import io
    from odoo import models


    class InvoiceXlsx(models.AbstractModel):
        _name = 'report.car_wash.report_invoice_xlsx'
        _inherit = 'report.report_xlsx.abstract'

        def generate_xlsx_report(self, workbook, data, invoice):

            sheet = workbook.add_worksheet('')
            bold = workbook.add_format({'bold': True, 'border': 2, 'bg_color': '#FFC900', 
            'align': 'center'})
            border = workbook.add_format({'border': 1})

            sheet.set_column('C:C', 20)
            sheet.set_column('D:D', 20)
            sheet.set_column('E:E', 20)
            sheet.set_column('I:I', 20)
            sheet.set_column('J:J', 20)
            sheet.set_column('K:K', 20)

            row = 2
            col = 2
            total = 0
            vehicle_washed = 0
            total_earned = 0

            sheet.write(row, col, "Vehicle Type", bold)
            sheet.write(row, col   1, "Package", bold)
            sheet.write(row, col   2, 'Order Time', bold)
            sheet.write(row, col   3, "Washer", bold)
            sheet.write(row, col   4, 'Price', bold)
            sheet.write(row, col   6, "Washer", bold)
            sheet.write(row, col   7, "Total Vehicle Washed", bold)
            sheet.write(row, col   8, "Total Earned", bold)

            invoices = self.env['order.transaction'].browse(data['invoices'])
            for invoice in invoices:
                row  = 1

                sheet.write(row, col, invoice.vehicle_id.car_type, border)
                sheet.write(row, col   1, invoice.package_id.package, border)
                sheet.write(row, col   2, invoice.order_id.order_time, border)
                sheet.write(row, col   3, invoice.employee_id.employee_name, border)
                sheet.write(row, col   4, invoice.price, border)
                sheet.write(row, col   6, invoice.employee_id.employee_name, border)
                total  = invoice.price
                vehicle_washed = invoice.employee_id.employee_name 
                # total_earned = len(vehicle_washed) * invoice.price

            sheet.write(row   1, col   4, total, border)
            sheet.write(row, col   7, vehicle_washed, border)
            sheet.write(row, col   8, total_earned, border)   

this is the current excel file :

what i wanted from the excel file is the washer should not be repetitive, the total vehicle washed should be counting how many time a employee washed in the left table (ex: sicuci = 2, radi = 1) and total earned is price * total vehicle washed, in this case, sicuci would be 110 and radi would be 55.

CodePudding user response：

One way to do this is to add formulas to the spreadsheet and have Excel calculate the totals and other information. Useful functions to do this would be 'SUM()', 'COUNTIF()' and 'SUMIF()' although there are other ways to do it as well. Here is a modified version of the spreadsheet data writing part of your code. I've added a dict to track unique employees but there is probably a cleaner way to do that based on your model.

employees = {}
for invoice in invoices:
    row  = 1

    sheet.write(row, col, invoice.vehicle_id.car_type, border)
    sheet.write(row, col   1, invoice.package_id.package, border)
    sheet.write(row, col   2, invoice.order_id.order_time, border)
    sheet.write(row, col   3, invoice.employee_id.employee_name, border)
    sheet.write(row, col   4, invoice.price, border)

    # Track unique employees.
    employees[invoice.employee_id.employee_name] = True

# Write the price summary.
last_row = row   1
sheet.write(last_row, col   4, f'=SUM(G4:G{last_row})', border)

# Write the employee summaries.
row = 3
for employee in employees.keys():
    sheet.write(row, 8, employee, border)
    sheet.write(row, 9, f'=COUNTIF($F$4:$F${last_row},I{row   1})', border)
    sheet.write(row, 10, f'=SUMIF($F$4:$F${last_row},I{row   1},$G$4:$G${last_row})', border)

    row  = 1

Output:

Since you are using LibreOffice you may have to force the formulas to recalculate.