统计员工完成的任务和python的总价

Question

所以我需要excel文件来统计有多少员工完成了他的具体任务(在这种情况下，洗车)。我想用Python中的一个包清洗特定车辆的价格总额。这是我的excel文件代码。

    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)   

这是当前的excel文件：

​

​

我想从excel文件中得到的是洗衣机不应该重复，清洗的总车辆应该是计算一个雇员在左边桌子上清洗的次数(例如: sicuci = 2，radi = 1)，而总挣的是价格*整车清洗，在这种情况下，sicuci将是110，radi将是55。

Answer 1

一种方法是向电子表格中添加公式，并让Excel计算总计和其他信息。这样做的有用函数是'SUM()‘、'COUNTIF()’和'SUMIF()‘，尽管还有其他方法可以做到这一点。下面是电子表格数据编写部分代码的修改版本。我添加了一个dict来跟踪独特的员工，但是根据您的模型，可能有一种更干净的方法来实现这一点。

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

输出：

​

​

由于您使用的是LibreOffice，您可能不得不强制重新计算公式。