[IMP] web: Export list view as XLS file

Purpose
=======

Have a quick export feature.

The current export wizard is quite complex.
We want to be able to quickly export a tree view,
with the group by correctly represented, depending on the fields
visible in the list view.

Specification
=============

Add an "EXPORT (XLS)" button in the list view (a little download icon)
- export only columns visible in list view
- export all the records
- while respecting the group by

This should be a generic feature but should be applied to all
the account.move.lines list views.
account.view_move_line_tree_grouped_sales_purchases
account.view_move_line_tree_grouped_bank_cash
account.view_move_line_tree_grouped_misc

Task 2031835

closes odoo/odoo#36943

Signed-off-by: Yannick Tivisse (yti) <yti@odoo.com>

7 files changed, 459 insertions, 1 deletions

diff --git a/addons/web/controllers/__init__.py b/addons/web/controllers/__init__.py
index 48cc37266c6..2113dfa39c9 100644
--- a/addons/web/controllers/__init__.py
+++ b/addons/web/controllers/__init__.py
@@ -1,4 +1,4 @@
 # -*- coding: utf-8 -*-
 # Part of Odoo. See LICENSE file for full copyright and licensing details.
 
-from . import main, pivot
+from . import main, pivot, list_controller
diff --git a/addons/web/controllers/list_controller.py b/addons/web/controllers/list_controller.py
new file mode 100644
index 00000000000..7ac98b79220
--- /dev/null
+++ b/addons/web/controllers/list_controller.py
@@ -0,0 +1,98 @@
+# -*- coding: utf-8 -*-
+# Part of Odoo. See LICENSE file for full copyright and licensing details.
+
+import json
+from datetime import datetime
+
+from odoo import http
+from odoo.http import request
+from odoo.tools.misc import xlwt
+
+
+HEADER_BOLD = xlwt.easyxf("font: bold on; pattern: pattern solid, fore_colour gray25;")
+
+class TableListExporter(http.Controller):
+
+    def _write_group_header(self, worksheet, x, y, group, columns, group_depth=0):
+        aggregates = group.get('aggregateValues', {})
+        title = group.get('value')
+
+        if group.get('hideHeader'):
+            return x, y
+
+        title = '%s%s (%s)' % ('>' * group_depth, title, group.get('count', 0))
+        worksheet.write(x, y, title, HEADER_BOLD)
+        for column in columns[1:]: # No aggregates allowed in the first column
+            y += 1
+            worksheet.write(x, y, aggregates.get(column['field'], ''), HEADER_BOLD)
+        return x + 1, 0
+
+    def _write_record(self, worksheet, x, y, record, columns):
+        for column in columns:
+            worksheet.write(x, y, record[column['field']])
+            y += 1
+        return x + 1, 0
+
+    def _write_groups(self, worksheet, x, y, groups, columns, group_depth=0):
+        """
+        Write groups to the xls worksheet. Recursive if groups are nested (chained groupby)
+        :param worksheet:
+        :param x: start position where to write the group (vertical axis, pointing downward)
+        :param y: start position where to write the group (horizontal axis, pointing to the right)
+        :param groups:  list of groups. Each group is a dict containing either records' data
+                        or sub-groups. A group has the following structure:
+                            'data': list of records or list of sub-groups
+                            'isGrouped': True if data contains sub-groups
+                            'value': title of the group
+                            'count': number of records in the group
+                            'aggregateValues': dict container aggregated values of fields {field: aggregatedValue, ...}
+                            'hideHeader': self explanatory, used when the records are not grouped at all.
+        :param columns: list of displayed columns. Each column is a dict with the following keys:
+                            'field': field name
+                            'aggregateValue': aggregate for all records
+                            'string':
+        """
+        for group in groups:
+            x, y = self._write_group_header(worksheet, x, y, group, columns, group_depth)
+            if group['isGrouped']:
+                # Recursively write sub-groups
+                x, y = self._write_groups(worksheet, x, y, group['data'], columns, group_depth + 1)
+            else:
+                for record in group['data']:
+                    x, y = self._write_record(worksheet, x, y, record, columns)
+        return x, y
+
+    def _write_worksheet(self, worksheet, data):
+        bold = xlwt.easyxf("font: bold on;")
+
+        columns = data['columns']
+        groups = data['groups']
+
+        # Write main header
+        for y, field in enumerate(columns):
+            worksheet.write(0, y, field['string'], bold)
+
+        # Write data
+        x, y = 1, 0
+        x, y = self._write_groups(worksheet, x, y, groups, columns)
+
+        # Write column aggregates
+        for y, field in enumerate(columns):
+            worksheet.write(x, y, field.get('aggregateValue', ''), bold)
+
+    @http.route('/web/list/export_xls', type='http', auth="user")
+    def export_xls(self, data, token):
+        data = json.loads(data)
+        title = data.get('title', 'export')
+        workbook = xlwt.Workbook()
+        worksheet = workbook.add_sheet(title)
+
+        self._write_worksheet(worksheet, data)
+
+        response = request.make_response(None,
+            headers=[('Content-Type', 'application/vnd.ms-excel'),
+                    ('Content-Disposition', 'attachment; filename="%s - %s.xls"' % (title, datetime.now()))],
+            cookies={'fileToken': token})
+        workbook.save(response.stream)
+
+        return response
diff --git a/addons/web/static/src/js/views/list/list_controller.js b/addons/web/static/src/js/views/list/list_controller.js
index 5ec42156000..dc26a750eb4 100644
--- a/addons/web/static/src/js/views/list/list_controller.js
+++ b/addons/web/static/src/js/views/list/list_controller.js
@@ -8,20 +8,78 @@ odoo.define('web.ListController', function (require) {
  */
 
 var core = require('web.core');
+var session = require('web.session');
+var framework = require('web.framework');
 var BasicController = require('web.BasicController');
 var DataExport = require('web.DataExport');
 var Dialog = require('web.Dialog');
+var fieldUtils = require('web.field_utils');
 var Sidebar = require('web.Sidebar');
 
 var _t = core._t;
 var qweb = core.qweb;
 
+/**
+ * Return data required for exporting the current list to a xls file.
+ * Recursive if some groups are nested.
+ * Note: this function is also used when the records are not grouped.
+ * In this scenario, an artificial single group with every record in
+ * the list is built.
+ * @param {Array} groups
+ * @param {Array} displayedFields
+ * @param {Array} allFields
+ * @returns {Array} list of groups with the following structure:
+ *                      'data': list of records or list of sub-groups
+ *                      'isGrouped': true if data contains sub-groups
+ *                      'value': title of the group
+ *                      'count': number of records in the group
+ *                      'aggregateValues': dict container aggregated values of fields {field: aggregatedValue, ...}
+ *                      'hideHeader': self explanatory, used when the records are not grouped at all.
+ */
+var processGroups = (groups, displayedFields, allFields) => {
+    let formatRecord = record => {
+        let data = _.pick(record.data, displayedFields)
+        let formattedData = {}
+
+        Object.entries(data).forEach(([field_name, value]) => {
+            let field = allFields[field_name]
+            let formattedValue = fieldUtils.format[field.type](value, field, {data: record.data, forceString: true})
+            formattedData[field_name] = formattedValue.replace(' ', '')
+        });
+        return formattedData
+    }
+
+    if (groups.length && groups[0].groupedBy && groups[0].groupedBy.length) {
+        // Recursively process sub-group
+        return groups.map(group => ({
+            hideHeader: !!group.hideHeader,
+            isGrouped: true,
+            value: group.value !== undefined && group.value.toString() || _t("Undefined"),
+            count: group.count,
+            aggregateValues: _.pick(group.aggregateValues, displayedFields),
+            data: processGroups(group.data, displayedFields, allFields),
+        }))
+    }
+    // process records
+    return groups.map(group => ({
+        hideHeader: !!group.hideHeader,
+        isGrouped: false,
+        value: group.value !== undefined && group.value.toString() || _t("Undefined"),
+        count: group.count,
+        aggregateValues: _.pick(group.aggregateValues, displayedFields),
+        data: group.data.map(formatRecord)
+    }))
+}
+
 var ListController = BasicController.extend({
     /**
      * This key contains the name of the buttons template to render on top of
      * the list view. It can be overridden to add buttons in specific child views.
      */
     buttons_template: 'ListView.buttons',
+    events: _.extend({}, BasicController.prototype.events, {
+        'click .o_list_download': '_onExportRecords',
+    }),
     custom_events: _.extend({}, BasicController.prototype.custom_events, {
         activate_next_widget: '_onActivateNextWidget',
         add_record: '_onAddRecord',
@@ -50,6 +108,9 @@ var ListController = BasicController.extend({
         this.noLeaf = params.noLeaf;
         this.selectedRecords = params.selectedRecords || [];
         this.multipleRecordsSavingPromise = null;
+        this.activeActions = _.extend({}, this.activeActions, {
+            download: this.renderer.arch.attrs.download ? !!JSON.parse(this.renderer.arch.attrs.download) : true,
+        });
     },
 
     //--------------------------------------------------------------------------
@@ -339,6 +400,31 @@ var ListController = BasicController.extend({
         });
     },
     /**
+     * Export the current list data in a xls file.
+     *
+     * @private
+     */
+    _downloadList() {
+        let groups = this.renderer.state.data
+        groups = this.renderer.isGrouped ? groups : [{data: groups, count: groups.length, hideHeader: true}] // Artificial single group
+
+        let allFields = this.renderer.state.fields
+        let columns = this.renderer.columns.map(column => ({
+            field: column.attrs.name,
+            aggregateValue: column.aggregate && column.aggregate.value,
+            string: allFields[column.attrs.name].string,
+        }))
+
+        groups = processGroups(groups, columns.map(c => c.field), allFields)
+
+        return session.get_file({
+            url: '/web/list/export_xls',
+            data: {data: JSON.stringify({columns, groups, title: this._title})},
+            complete: framework.unblockUI,
+            error: (error) => this.call('crash_manager', 'rpc_error', error),
+        });
+    },
+    /**
      * @override
      * @private
      */
@@ -614,6 +700,14 @@ var ListController = BasicController.extend({
         new DataExport(this, record, defaultExportFields).open();
     },
     /**
+     * Export Records in a xls file
+     *
+     * @private
+     */
+    _onExportRecords() {
+        this._downloadList()
+    },
+    /**
      * Opens the related form view.
      *
      * @private
diff --git a/addons/web/static/src/xml/base.xml b/addons/web/static/src/xml/base.xml
index 300aff10c00..733bd37c211 100644
--- a/addons/web/static/src/xml/base.xml
+++ b/addons/web/static/src/xml/base.xml
@@ -271,6 +271,9 @@
                 Create
             </button>
         </t>
+        <t t-if="widget.is_action_enabled('download')">
+            <button type="button" class="btn btn-secondary fa fa-download o_list_download" title="Download xls" aria-label="Download xls"/>
+        </t>
     </div>
 </t>
 
diff --git a/addons/web/static/tests/views/list_tests.js b/addons/web/static/tests/views/list_tests.js
index c97b1916265..6724540a23f 100644
--- a/addons/web/static/tests/views/list_tests.js
+++ b/addons/web/static/tests/views/list_tests.js
@@ -3399,6 +3399,193 @@ QUnit.module('Views', {
         list.destroy();
     });
 
+    QUnit.test('export list view to xls with nested groups', async function (assert) {
+        assert.expect(5);
+
+        let currencies = {};
+        _.each(this.data.res_currency.records, function (currency) {
+            currencies[currency.id] = currency;
+        });
+
+        let list = await createView({
+            View: ListView,
+            model: 'foo',
+            data: this.data,
+            arch: `
+                <tree download="1">
+                    <field name="foo"/>
+                    <field name="bar"/>
+                    <field name="int_field" sum="Total"/>
+                    <field name="datetime"/>
+                    <field name="amount" widget="monetary"/>
+                    <field name="currency_id" invisible="1"/>
+                    <field name="o2m"/>
+                    <field name="m2o"/>
+                    <field name="m2m"/>
+                </tree>`,
+            groupBy: ['foo', 'bar'],
+            session: {
+                currencies: currencies,
+                get_file(args) {
+                    let data = JSON.parse(args.data.data);
+                    assert.strictEqual(args.url, '/web/list/export_xls',
+                        "should call get_file with the correct url");
+                    assert.deepEqual(data.columns, [
+                        {field: "foo", string: "Foo"},
+                        {field: "bar", string: "Bar"},
+                        {field: "int_field", aggregateValue: 32, string: "int_field"},
+                        {field: "datetime", string: "Datetime Field"},
+                        {field: "amount", string: "Monetary field"},
+                        {field: "o2m", string: "O2M field"},
+                        {field: "m2o", string: "M2O field"},
+                        {field: "m2m", string: "M2M field"},
+                    ], "columns should be in the correct order with the string and aggregate value")
+                    assert.deepEqual(data.groups[0], {
+                        // Group "yop"
+                        isGrouped: true,
+                        count: 1,
+                        aggregateValues: {int_field: 10},
+                        hideHeader: false,
+                        value: "yop",
+                        data: [{
+                            // Group "yop > true"
+                            isGrouped: false,
+                            count: 1,
+                            aggregateValues: {int_field: 10},
+                            hideHeader: false,
+                            value: "true",
+                            data: [{
+                                // Record "yop > true"
+                                foo: "yop",
+                                bar: "True",
+                                int_field: "10",
+                                datetime: "12/12/2016 10:55:05",
+                                amount: "1200.00€",
+                                m2m: "2 records",
+                                m2o: "Value 1",
+                                o2m: "No records",
+                            }]
+                        }]
+                    });
+                    assert.deepEqual(data.groups[1], {
+                        // Group "blip"
+                        isGrouped: true,
+                        count: 2,
+                        aggregateValues: {int_field: 5},
+                        hideHeader: false,
+                        value: "blip",
+                        data: [{
+                            // Group "blip > true"
+                            isGrouped: false,
+                            count: 1,
+                            aggregateValues: {int_field: 9},
+                            hideHeader: false,
+                            value: "true",
+                            data: [],
+                        }, {
+                            // Group "blip > false"
+                            isGrouped: false,
+                            count: 1,
+                            aggregateValues: {int_field: -4},
+                            hideHeader: false,
+                            value: "false",
+                            data: [],
+                        }]
+                    });
+                    assert.deepEqual(data.groups[2], {
+                        // Group "gnap"
+                        isGrouped: true,
+                        count: 1,
+                        aggregateValues: {int_field: 17},
+                        hideHeader: false,
+                        value: "gnap",
+                        data: [],
+                    });
+                    args.complete();
+                },
+            },
+        });
+
+        // open the first group
+        await testUtils.dom.click(list.$('.o_group_header:first'));
+
+        // open subgroup
+        let $openGroup = list.$('tbody:nth(1)');
+        await testUtils.dom.click($openGroup.find('.o_group_header:first'));
+
+        // open the second group
+        await testUtils.dom.click(list.$('.o_group_header:nth(2)'));
+
+        // Download
+        await testUtils.dom.click(list.$buttons.find('.o_list_download'));
+
+        list.destroy();
+    });
+
+    QUnit.test('export list view to xls', async function (assert) {
+        assert.expect(3);
+
+        let currencies = {};
+        _.each(this.data.res_currency.records, function (currency) {
+            currencies[currency.id] = currency;
+        });
+
+        let list = await createView({
+            View: ListView,
+            model: 'foo',
+            data: this.data,
+            arch: `
+                <tree>
+                    <field name="foo"/>
+                    <field name="bar"/>
+                    <field name="int_field" sum="Total"/>
+                    <field name="datetime"/>
+                    <field name="amount" widget="monetary"/>
+                    <field name="currency_id" invisible="1"/>
+                    <field name="o2m"/>
+                    <field name="m2o"/>
+                    <field name="m2m"/>
+                </tree>`,
+            session: {
+                currencies: currencies,
+                get_file(args) {
+                    let data = JSON.parse(args.data.data);
+                    assert.strictEqual(args.url, '/web/list/export_xls',
+                        "should call get_file with the correct url");
+                    assert.deepEqual(data.columns, [
+                        {field: "foo", string: "Foo"},
+                        {field: "bar", string: "Bar"},
+                        {field: "int_field", aggregateValue: 32, string: "int_field"},
+                        {field: "datetime", string: "Datetime Field"},
+                        {field: "amount", string: "Monetary field"},
+                        {field: "o2m", string: "O2M field"},
+                        {field: "m2o", string: "M2O field"},
+                        {field: "m2m", string: "M2M field"},
+                    ], "columns should be in the correct order with the string and aggregate value")
+                    assert.deepEqual(data.groups[0], {
+                        isGrouped: false,
+                        count: 4,
+                        aggregateValues: {},
+                        hideHeader: true,
+                        value: "Undefined",
+                        data: [
+                            {foo: "yop", bar: "True", int_field: "10", datetime: "12/12/2016 10:55:05", amount: "1200.00€", m2m: "2 records", m2o: "Value 1", o2m: "No records"},
+                            {foo: "blip", bar: "True", int_field: "9", datetime: "", amount: "$500.00", m2m: "3 records", m2o: "Value 2", o2m: "No records"},
+                            {foo: "gnap", bar: "True", int_field: "17", datetime: "", amount: "$300.00", m2m: "No records", m2o: "Value 1", o2m: "No records"},
+                            {foo: "blip", bar: "False", int_field: "-4", datetime: "", amount: "$0.00", m2m: "1 record", m2o: "Value 1", o2m: "No records"},
+                        ],
+                    });
+                    args.complete();
+                },
+            },
+        });
+
+        // Download
+        await testUtils.dom.click(list.$buttons.find('.o_list_download'));
+
+        list.destroy();
+    });
+
     QUnit.test('grouped list on selection field at level 2', async function (assert) {
         assert.expect(4);
 
diff --git a/addons/web/tests/__init__.py b/addons/web/tests/__init__.py
index 1ed329bafe1..690fab494d3 100644
--- a/addons/web/tests/__init__.py
+++ b/addons/web/tests/__init__.py
@@ -6,3 +6,4 @@ from . import test_js
 from . import test_menu
 from . import test_serving_base
 from . import test_click_everywhere
+from . import test_list_controller
diff --git a/addons/web/tests/test_list_controller.py b/addons/web/tests/test_list_controller.py
new file mode 100644
index 00000000000..311def2989f
--- /dev/null
+++ b/addons/web/tests/test_list_controller.py
@@ -0,0 +1,75 @@
+# -*- coding: utf-8 -*-
+# Part of Odoo. See LICENSE file for full copyright and licensing details.
+
+from ..controllers.list_controller import TableListExporter
+from odoo.tests.common import HttpCase
+
+class WorksheetMock(dict):
+
+    def write(self, x, y, value, style=None):
+        if (x, y) in self:
+            raise Exception("Cannot rewrite cell [{}, {}]".format(x, y))
+        self[x, y] = value
+
+class ListController(HttpCase):
+
+    def test_list_download(self):
+        controller = TableListExporter()
+
+        columns = [
+            {'field': "foo", 'string': "Foo"},
+            {'field': "bar", 'string': "Bar"},
+            {'field': "int_field", 'aggregateValue': 19, 'string': "int_field"},
+        ]
+        groups = [{
+            # Group "yop"
+            'isGrouped': True,
+            'count': 1,
+            'aggregateValues': {'int_field': 19},
+            'hideHeader': False,
+            'value': "yop",
+            'data': [{
+                # Group "yop > true"
+                'isGrouped': False,
+                'count': 1,
+                'aggregateValues': {'int_field': 10},
+                'hideHeader': False,
+                'value': "true",
+                'data': [{'foo': "yop", 'bar': "True", 'int_field': 10}],
+            }, {
+                # Group "yop > false"
+                'isGrouped': False,
+                'count': 10,
+                'aggregateValues': {'int_field': 9},
+                'hideHeader': False,
+                'value': "false",
+                'data': [],
+            }],
+        }]
+
+        worksheet = WorksheetMock()
+        controller._write_worksheet(worksheet, {'columns': columns, 'groups': groups})
+
+        # Header
+        self.assertEqual(worksheet[0, 0], 'Foo')
+        self.assertEqual(worksheet[0, 1], 'Bar')
+        self.assertEqual(worksheet[0, 2], 'int_field')
+
+        # Main group
+        self.assertEqual(worksheet[1, 0], 'yop (1)', "Group header")
+        self.assertEqual(worksheet[1, 2], 19, "int_field aggregate value")
+
+        # First sub-group (open)
+        self.assertEqual(worksheet[2, 0], '>true (1)', "subgroup header")
+        self.assertEqual(worksheet[2, 2], 10, "int_field aggregate value")
+
+        # data
+        self.assertEqual(worksheet[3, 0], 'yop')
+        self.assertEqual(worksheet[3, 1], 'True')
+        self.assertEqual(worksheet[3, 2], 10)
+
+        # second sub-group (closed)
+        self.assertEqual(worksheet[4, 0], '>false (10)', "second sub-group header")
+        self.assertEqual(worksheet[4, 2], 9, "int_field aggregate value")
+
+        self.assertEqual(worksheet[5, 2], 19, "main list int_field aggregate value")