Odoo Excel报表的设计及实现

前言：

报表是ERP中必不可少的一个部分，很多ERP开发人员都经历过写报表和改报表，偶尔改报表改到最后不得不重构报表的情况。最近我接了两个改报表的任务（新增一种排序和合计规则），结果改原代码改到90%之后发现最后一个需求无法实现（也是自己的问题，没有一开始全盘考虑）。不得已我把这一千八百行代码重构，花费了大量的时间和精力。痛定思痛，我决定记录一下这次报表的设计及实现，既是分享交流，也是给即将新写的报表排点坑。

正文：

常见的报表格式有PDF,Excel,Word三种。这一次分享的是Excel报表设计及实现。

另外两种请关注传送门。

odoo本身不支持excel打印。我们需要使用XlsxWriter插件。传送门：

https://xlsxwriter.readthedocs.io/index.html

问题梳理：

新写报表的时候，往往因为考虑不全面，导致后来的迭代优化出现很多的问题，大致有以下几个方面。

（1）性能。由于报表筛选的数据基数不定，有些报表要从大量的数据做筛选，打印报表速度过慢后期需要优化。但因为最初没有考虑到相关问题，优化就成了重构。一般打印报表的速度主要受限于获取数据的速度。在初次设计时，对于获取数据的处理就要尤为注意，及时测试效率（可通过logging模块）。

（2）可拓展性。可拓展性低也导致这张报表一改就是重构。客户往往会因为自身业务改动提出新的需求，让我们加一个字段，减一个字段，或者增加一种合计规则，增加一种排序规则。这就意味着无论是纵向还是横向我们都不能写固定的（比如依据某个字段排序不可以用索引。），不然会牵一发而动全身。

（3）输出样式。比如更改字段后，原定位置的样式是否随之更改。客户想要修改某个位置的样式，比如负值变红，如何添加？如何给项目设计一套统一的样式模板？这些都是要考虑的问题。

总结：尽可能地降低代码耦合性，选取合适的数据结构，提高代码规范，是写好一张报表的关键，也是所有好代码的不二标准。

 

设计思路：

 

首先需要说明，每张报表的实际情况不一样，这篇文章既没有抽象出一个通用的模板，也没有提供可以直接复用的代码。只是提供一个普通报表的解决思路，供大家参考。

报表可以分为三个部分：获取数据，数据处理，数据输出。

获取数据包括基础字段的定义，和特殊字段的求值计算，将数据存储到数据结构中返回。

核心是数据存取和数据结构的选择。excel报表输出的结构多是二维数组。python基本数据结构有元组，列表，字典。元组不是一个可选项。如果把字典作为外层，排序将变得很困难。剩下的在列表嵌套列表和列表嵌套字典中，前者排序受到了限制，如果有特殊的排序要求，比如规定了一个中文顺序（预完成-进行中-已完成），排序的代码就无法很好的封装复用。而且，因为不能根据索引选取排序字段，列表嵌套列表的格式要在数据存储之前就对全量数据做排序。

因此，在数据存储和数据处理阶段。数据结构最合适的选择是：列表嵌套字典。datas = [ {},{},{} ]一个dict是一行。但是，因为字典是无序的。如何把一行数据按照我们想要的数据对应表头一一输出呢？这又需要给字典升个级，使用有序字典 OrderDcit。由此，确定了最后的数据结构：列表嵌套有序字典。

数据处理要注意封装和复用，否则一点需求的增加都可能会导致代码量翻倍。

这里我总结了四种常见的数据处理：

（1）字段约束：相对比较独立，只针对部分字段，比如限制截止日期。

（2）字段筛选：用户通过wizzard，外部添加字段条件，比如只打印某个部门的数据。这一块建议和获取数据对象放在一起。通过domain添加条件，直接search(domain)到数据中

（3）字段排序：字段排序写在一个方法里。python内置函数中有sort,和sorted两个方法可以用来列表排序。这里耗费的时间往往很少，但是需要注意内存的占用。如果数据量非常巨大，应该将排序key值提出来排序，防止内存耗尽。同时，该方法应该给一个不定长的形参，这样，当用户有新的排序需求时，可以直接修改或者添加排序字段。

（4）小计总计：小计总计是数据处理中比较繁琐的一块。我采用的办法是使用python的groupby函数，对排序好的数据进行分组。然后对每组分别求得小计。要注意的是数据要先排序再分组。

难点是如何把求出的小计和总计插入到原数据集合中。我用了一个取巧的办法，加入一个计数器，然后使用python列表的insert方法。这个方法又带来了一个伴生的问题，直接插入会破坏已经分好的组，产生很“神奇”的结果。（传送门建设中...）

如果大家有什么好的求小计总计的方法，欢迎留言。

数据输出：

XlsxWriter只支持输出二维列表的数据结合，所以我们要将之前处理完的数据集转换成一个二维列表。

数据输出主要是样式的设计。添加样式详见上方插件官网传送门。

在项目中，我们经常会遇到打印很多报表的情况，建议大家在参与项目开发时候，考虑能否把项目中报表样式封装起来，直接基于一个list[dicts]做输出，后期只需要对特定列做修改。这对每个项目来说都不一样，需要实际考虑。

对于目前插件提供的add_table方法，具有一定的局限性，特殊列需要我们根据索引定位，且每张报表字段数量和顺序不同，很难实现一套列样式适用所有报表。

 

代码架构：

两个类： 数据类和打印类

数据类包括：基础字段定义，字段约束方法，字段筛选和获取数据对象方法，字段排序方法，求小计总计方法，获取数据的方法， 返回一个列表嵌套有序字典的数据集合。还有一些本地化时间方法，二维列表转换的方法等。

打印类：调用获取数据的方法，调用字段排序，调用字段小计合计，调用转换列表的方法。定义样式，打印标题，打印表头，打印数据。

 

伪实现

数据类：

# -*- coding: utf-8 -*-
from openerp.osv import osv
from openerp.report import report_sxw
from openerp import models, fields, api, _
from openerp.addons.report_xlsx.report.report_xlsx import ReportXlsx
from operator import itemgetter, attrgetter
from openerp.exceptions import ValidationError
from itertools import groupby
from openerp.tools.float_utils import float_compare
from copy import deepcopy
from datetime import datetime, timedelta
import xlsxwriter
import time
import datetime
import pytz
import decimal
import calendar
import pytz
import collections


class HandleDatas(models.TransientModel):
    _name = 'demo.project.cost.excel'
    _description = 'The Demo Excel Of Odoo Report'
    #基础数据字段
    # 公司
    company_id = fields.Many2one(comodel_name='res.company', string='Company Name', help='Company Name',default=lambda self: self.env.user.company_id.id)
    # 部门
    department_id = fields.Many2one(comodel_name='hr.department', string='Department', help='Department')
    # 状态
    state = fields.Selection(selection=[('closed', 'Closed'),
                                        ('pre_shutdown','Pre-Shutdown'),
                                        ('completion_closed','Completion Closed'),
                                        ('doing', 'Doing')],
                             string='State', help='State')
    # 报表排序
    report_order = fields.Selection(selection=[('state','Project State'),
                                               ('department_id','Department'),],
                                    string='Report Order',help='Report Order',
                                    default='state')   
    # 年度
    fiscalyear_id = fields.Many2one(comodel_name='account.fiscalyear', string="Fiscal Year",
                                    help="Fiacal Year", default=lambda self: self.env['account.fiscalyear'].find())
    # 截止日期
    end_date = fields.Date(string='End Date', help='End Date')
    
    # 省略字段
    # fieldN = fields........
    
    #字段约束 限制截止日期不超过今天
    @api.constrains('end_date')
    def check_end_date(self):
        for rec in self:
            if rec.end_date < rec.fiscalyear_id.date_start:
                raise ValidationError(_('The end date can not be earlier than the fiscal year'))
            elif rec.end_date > fields.Date.today():
                raise ValidationError(_('The end date do not allow future dates'))

    # 获取数据
    @api.model
    def _get_datas(self):
        lines=[]
        project_obj = self.env['demo.project']
        #获取projects
        projects = self._get_project_ids(project_obj, move_line_obj, project_member_obj)
        map_state_dict = {
            #......
        }
        #对数据对象枚举，将需要的值存入 列表嵌套有序字典 的数据结构中
        for index, project in enumerate(projects):
            dict=collections.OrderedDict()
            # 精度值
            currency_id = project and project.company_id and project.company_id.currency_id or False
            decimal_places = currency_id and currency_id.decimal_places or 2
            # 用来为小计总计设置特殊样式
            dict['small_all'] = ''
            #取基础字段
            dict['company_id'] = project.company_id.name
            dict['department_id'] = project.department_id and project.department_id.name or '-'
            dict['project_leader_id'] = project.project_member_ids and project.project_member_ids[0].employee_id.name or '-'
            dict['project_number'] = project.project_number
            dict['project_name'] = project.name
            dict['project_type_id'] = project.project_type_id and project.project_type_id.name or '-'
            dict['space_type_id'] = project.space_type_id and project.space_type_id.name or '-'
            dict['state'] = map_state_dict.get(project.state, False)

            #取特殊字段
            # 例：以前年度收入
            the_account = project.company_id.income_account_b_id.id
            move_lines = move_line_obj.search([
                ('company_id', '=', project.company_id.id),
                ('account_id', '=', the_account),
                ('date', '<', self.fiscalyear_id.date_start),
                ('analysis_distribution_id', 'in', analytic_distribution_ids)])
            sum_ago_income = 0.0
            for move in move_lines:
                sum_ago_income += round(move.credit - move.debit, decimal_places)

            dict['sum_ago_income'] = round(sum_ago_income,  decimal_places)
            # 辅助 项目状态排序
            # 一种特殊情况，客户制定了一种特殊排序，比如按照状态（已关闭-预关闭-竣工结案）
            # 这时需要给一个键值辅助排序
            if project.state:
                if project.state == 'closed':
                    dict['temp_state'] = 1
                elif project.state == 'pre_shutdown':
                    dict['temp_state'] = 2
                elif project.state == 'completion_closed':
                    dict['temp_state'] = 3
                elif project.state == 'final_confirmed':
                    dict['temp_state'] = 4
                elif project.state == 'reconfirm':
                    dict['temp_state'] = 5
                elif project.state == 'confirmed':
                    dict['temp_state'] = 6
            lines.append(dict)
        return lines

    # 字段筛选，获取projects
    def _get_project_ids(self, project_obj, move_line_obj, project_member_obj):
        # 字段筛选
        domain = []
        # 公司 筛选条件
        if self.company_id:
            pass
        # 部门 筛选条件
        if self.department_id:
            pass  
        # 状态 筛选条件
        if self.state:
            pass
        # 年度 筛选条件
        if self.fiscalyear_id:
            pass
        projects = project_obj.search(domain)

    # 字段排序，通过不定长参数实现多级排序
    def sort_report(self,datas, *args):
        datas.sort(key=itemgetter(*args), reverse=False)
        return datas

    # 小计总计
    def total_report(self,datas,arg):
        gb = groupby(deepcopy(datas),itemgetter(arg))
        dict_all =collections.OrderedDict()
        # 总计字段定义
        all_fields = 0.0
        avg_num_all = 0  # 总个数，计算目标平均值
        all_n = 0  # 总个数，用于插入小计数据
        for key,group in gb:
            dict_small =collections.OrderedDict()
            # 小计字段定义
            small_fields = 0.0
            for g in group:
                small_n = 0 # 计算该组个数
                # 在当前分组内做循环累加求得小计
                small_fields+=g.get('small_fields',False)
                # 不清零累加求总计
                all_fields+=g.get('all_fields',False)
                # 计数
                small_n+=1
                all_n+=1
                # 非累计小计
                if small_current_contract != 0.0:
                    small_completion = small_amount_of_cost / small_n * 100
            dict_small['small_all'] = 'small_add'  # 用于输出时做一个区分     
            dict_small['small_fields'] = small_fields
            datas.insert(all_n,dict_small)
            all_n+=1 # 插入一条数据，其它数据就会依次往后移动一位
        # 特殊 总计
        if all_current_contract != 0.0:
            all_completion = small_amount_of_cost / small_n * 100
        dict_all['all_all'] = 'all_add'  # 用于输出时做一个区分     
        dict_all['all_fields'] = small_fields
        datas.append(dict_all)
        return datas

    # 把dict转换成list
    def to_list(self,datas):
        lines = []
        for data in datas:
            data = list(data.values())
            lines.append(data)
        return lines

    # 本地化时间方法
    def _get_context_tz_time(self, datetime):
        tz = self.env.context.get('tz', False)
        time_date = fields.Datetime.from_string(datetime)
        if tz:
            context_tz = pytz.timezone(tz)
            time_date = pytz.utc.localize(time_date).astimezone(context_tz)
        return time_date.strftime('%m/%d/%Y %X')

打印类：

class Project_cost_xlsx(ReportXlsx):
    def generate_xlsx_report(self, workbook, data, partners):
        for obj in partners:
            # One sheet by partner
            # 创建一张工作表 sheet
            sheet = workbook.add_worksheet(_('Project Cost Profit'))
            # 定义样式
            merge_format = workbook.add_format({
                'font_size': 11,
                'bold': True,
                'align': 'center',
                'valign': 'vcenter',
                'bg_color': '#5B9BD5',
                'font_color': '#FFFFFF',
                'border': 1
            })
            header_total_format = workbook.add_format({
                'font_size': 14,
                'align': 'center',
                'bold': True,
                'valign': 'vcenter',
                'font': 'DengXian'
            })
            header_format = workbook.add_format({
                'font_size': 11,
                'align': 'left',
                'bold':True,
                'valign': 'vcenter',
                'font': 'DengXian'
            })
            #formats
            
            wrap_format = workbook.add_format({'align': 'center','valign': 'vcenter', 'bg_color': '#DDEBF7',
                'font_color': '#404040','border': 1})
            str_company = ''
            str_fiscalyear = ''
            str_pirnter = (self.env.user.name and self.env.user.name or '')
            str_print_date = obj._get_context_tz_time(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
            if obj.company_id:
                str_company = obj.company_id.name
            if obj.fiscalyear_id:
                str_fiscalyear = obj.fiscalyear_id.name
            # 打印标题
            sheet.merge_range('A1:AF1', _('Project Cost Profit'), header_total_format)
            sheet.merge_range('A2:AF2','','')
            sheet.merge_range('A3:D3', _('The Company:') + str_company, header_format)
            sheet.merge_range('G3:AF3', _('Printer:') + str_pirnter, header_format)
            sheet.merge_range('A4:D4', _('The Fiscal Year:') + str_fiscalyear, header_format)
            sheet.merge_range('G4:AF4', _('Print Date:') + str_print_date, header_format)
            sheet.merge_range('A5:AF5', _('Note: The cost in this report refers to the “cost charged to the financial journal”. The business tax and surcharges and project tax liabilities of the project are recorded. Please refer to the project tax account details form under the accounting module.'), note_format)
            sheet.set_row(5,24)

            #获取数据
            datas = obj._get_datas()
            #客户要求可以自己指定不同排序顺序。由于良好的封装，大大简化了代码
            if obj.report_order == 'department_id':
                datas = obj.sort_report(datas,'department_id','project_leader_id','project_type_id')
                datas = obj.report_add(datas, 'department_id')
            elif obj.report_order == 'state':
                datas = obj.sort_report(datas, 'temp_state','department_id','project_number')
                datas = obj.report_add(datas,'state')
            # 删除用来满足辅助键
            for data in datas:
                del data['temp_state']
            #转换成二维列表
            datas = obj.to_list(datas)
            str_len = len(datas) + 7
            sheet.set_column('A:A', 24)
            sheet.set_column('B:D', 16)
            sheet.set_column('E:E', 24)
            sheet.set_column('F:H', 16)
            sheet.set_column('I:K', 18.5)
            sheet.set_column('L:AF', 16)
            #定义表头
            sheet.add_table('A7:AF' + str(str_len), {
                'columns': [{'header': _('Company Name'),'header_format':wrap_format},
                            {'header': _('Department Name'),'header_format':wrap_format},
                            {'header': _('Project Director'),'header_format':wrap_format},
                            {'header': _('Project Number'),'header_format':wrap_format},
                            {'header': _('Project Name'),'header_format':wrap_format},
                            {'header': _('Project Type'),'header_format':wrap_format},
                            {'header': _('Space Type'),'header_format':wrap_format},
                            {'header': _('Project State'),'header_format':wrap_format},
                            {'header': _('Current Contract Tax Amount'),'header_format':wrap_format},
                            {'header': _('Current Contract noTax Amount\nA'),'header_format':wrap_format},
                            {'header': _('Current Budget noTax Amount\nB'),'header_format':wrap_format},
                            {'header': _('Complete Work%\nL=J/B'),'header_format':wrap_format},
                            {'header': _('Ago Years Income\nC'),'header_format':wrap_format},
                            {'header': _('Ago Years Cost\nD'),'header_format':wrap_format},
                            {'header': _('Ago Years Profit\nE=C-D'),'header_format':wrap_format},
                            {'header': _('Current Years Income\nF'),'header_format':wrap_format},
                            {'header': _('Current Years Cost\nG'),'header_format':wrap_format},
                            {'header': _('Current Years Profit\nH=F-G'),'header_format':wrap_format},
                            {'header': _('Sum Income\nI=C+F'),'header_format':wrap_format},
                            {'header': _('Sum Cost\nJ=D+G'),'header_format':wrap_format},
                            {'header': _('Sum Profit\nK=E+H'),'header_format':wrap_format},
                            {'header': _('Current Budget%\nP=(A-B)/A'),'header_format':wrap_format},
                            {'header': _('Current Reality%\nQ=K/I'),'header_format':wrap_format},
                            {'header': _('Goal%\nR'),'header_format':wrap_format},
                            {'header': _('Budget Difference%\nS=Q-R'),'header_format':wrap_format},
                            {'header': _('Request Amount\nT'),'header_format':wrap_format},
                            {'header': _('Request Em%\nU=T/A'),'header_format':wrap_format},
                            {'header': _('Request Difference\nV=T-I'),'header_format':wrap_format},
                            {'header': _('Invoice Amount\nW'),'header_format':wrap_format},
                            {'header': _('Get Amount\nX'),'header_format':wrap_format},
                            {'header': _('Payment Amount\nY'),'header_format':wrap_format},
                            {'header': _('Project Cash Flow\nZ=X-Y'),'header_format':wrap_format},
                            ],
                'data':datas
                }
            )
            #输出数据
            row = 7
            for data in datas:
                if data[0] == 'small_add':
                    pass
                elif data[0] == 'all_add':
                    pass
                else:
                    sheet.write_row(row, 0, data[1:], add_line_format)
                    sheet.write(row, 26, data[27], add_line_request_format)
                    for i in [12, 25, 28, 32]:
                        if data[i] < 0:
                            sheet.write(row, i - 1, data[i], wrap_format_red)
                row += 1

Project_cost_xlsx('report.project.cost.xlsx',
                  'demo.project.cost.excel')

部分封装方法：

报表排序：

    # 字段排序，通过不定长参数实现多级排序
    def sort_report(self,datas, *args):
        datas.sort(key=itemgetter(*args), reverse=False)
        return datas

转换成二维列表：

# 把dict转换成list
    def to_list(self,datas):
        lines = []
        for data in datas:
            data = list(data.values())
            lines.append(data)
        return lines

小计总计：

# 小计总计
    def total_report(self,datas,arg):
        gb = groupby(deepcopy(datas),itemgetter(arg))
        dict_all =collections.OrderedDict()
        # 总计字段定义
        all_fields = 0.0
        avg_num_all = 0  # 总个数，计算目标平均值
        all_n = 0  # 总个数，用于插入小计数据
        for key,group in gb:
            dict_small =collections.OrderedDict()
            # 小计字段定义
            small_fields = 0.0
            for g in group:
                small_n = 0 # 计算该组个数
                # 在当前分组内做循环累加求得小计
                small_fields+=g.get('small_fields',False)
                # 不清零累加求总计
                all_fields+=g.get('all_fields',False)
                # 计数
                small_n+=1
                all_n+=1
                # 非累计小计
                if small_current_contract != 0.0:
                    small_completion = small_amount_of_cost / small_n * 100
            dict_small['small_all'] = 'small_add'  # 用于输出时做一个区分     
            dict_small['small_fields'] = small_fields
            datas.insert(all_n,dict_small)
            all_n+=1 # 插入一条数据，其它数据就会依次往后移动一位
        # 特殊 总计
        if all_current_contract != 0.0:
            all_completion = small_amount_of_cost / small_n * 100
        dict_all['all_all'] = 'all_add'  # 用于输出时做一个区分     
        dict_all['all_fields'] = small_fields
        datas.append(dict_all)
        return datas

 

总结

excel报表的设计和实现思路大抵如此，具体细节还要根据实际情况考虑分析。但是好报表的共性是一定的：用合适的结构存储报表数据，封装方法：如排序，总计合计等常用方法，规范样式，低耦合高拓展。

此外，本次报表的性能问题主要取决于get_datas方法里的枚举。做性能优化的时候考虑此处。

希望我的这次梳理可以给大家带来一定帮助，同时也希望各位前辈可以指点一下，分享交流，一起进步。

附：在小计合计处我用了deepcopy，这是因为插入小计时导致原有的索引变动，从而导致已分好的组发生了变动，然后发生了一些“神奇”的事情。具体的分析我将写在另一篇博客里，敬请期待。