odoo16/addons_main/archives/controllers/excel_export.py

from odoo import http
from odoo.http import serialize_exception, request, content_disposition
from odoo.utils.export_xlsx_writer import ExportXlsxWriter
import json
import operator

CONTENT_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'


class ExcelExport(http.Controller):
    @http.route('/web/export/analyse/xlsx', type='http', auth="user")
    # @serialize_exception
    def export_excel(self, data, token):
        params = json.loads(data)
        model_name = operator.itemgetter('model')(params)
        model = request.env[model_name]
        file_name = ExcelExport._get_file_name(model._description)
        result = model.get_result(request.session.uid)
        head = result[0]
        contents = result[1]
        sum_row = result[2]
        response_data = ExcelExport._from_data(head, contents, sum_row)
        return request.make_response(response_data,
                                     headers=[('Content-Disposition', content_disposition(file_name)),
                                              ('Content-Type', CONTENT_TYPE)],
                                     cookies={'fileToken': token})

    @staticmethod
    def _get_file_name(model_description):
        if '：' in model_description:
            name = model_description.split('：')[1]
        elif ':' in model_description:
            name = model_description.split(':')[1]
        else:
            name = model_description
        return '%s分析.xlsx' % name

    @staticmethod
    def _from_data(head, rows, sum_row):
        freeze_column_count = ExcelExport._get_freeze_column_count(head)
        number_column_index_list = ExcelExport._get_number_column_index_list(sum_row)
        with ExportXlsxWriter(head, len(rows)) as xlsx_writer:
            for row_index, row in enumerate(rows):
                for cell_index, data in enumerate(row):
                    cell_value = data[0]
                    color = ExcelExport._get_cell_color(data, xlsx_writer, cell_index, freeze_column_count)
                    if cell_index in number_column_index_list and cell_value:
                        cell_value = ExcelExport._get_number(cell_value)
                        xlsx_writer.worksheet.write_number(row_index + 1, cell_index, cell_value, color)
                    else:
                        xlsx_writer.write_cell(row_index + 1, cell_index, cell_value, color)
            ExcelExport._write_sum_row(row_index, sum_row, xlsx_writer)
            xlsx_writer.worksheet.freeze_panes(1, freeze_column_count)
        return xlsx_writer.value

    @staticmethod
    def _write_sum_row(row_index, sum_row, xlsx_writer):
        if sum_row:
            row_index += 2
            diff = 0
            for cell_index, data in enumerate(sum_row):
                cell_value, cell_span = data
                if cell_span > 1:
                    xlsx_writer.worksheet.merge_range(row_index, cell_index + diff, row_index,
                                                      cell_index + diff + cell_span - 1, cell_value,
                                                      xlsx_writer.sum_style)
                    diff += cell_span - 1
                else:
                    xlsx_writer.write_cell(row_index, cell_index + diff, cell_value)

    @staticmethod
    def _get_cell_color(data, xlsx_writer, cell_index, freeze_column_count):
        if freeze_column_count and cell_index < freeze_column_count:
            return xlsx_writer.get_background_color('#FFC0CB')
        color = None
        if len(data) > 2:
            font_color = data[1]
            background_color = data[2]
            if font_color and background_color:
                color = xlsx_writer.get_font_and_background_color(font_color, background_color)
            elif font_color:
                color = xlsx_writer.get_font_color(font_color)
            else:
                color = xlsx_writer.get_background_color(background_color)
        return color

    @staticmethod
    def _get_freeze_column_count(head):
        is_find = False
        for index, data in enumerate(head):
            if not is_find and data[2] == -1:
                is_find = True
            if is_find:
                return index
        return 0

    @staticmethod
    def _get_number_column_index_list(sum_row):
        _list = []
        diff = 0
        for index, data in enumerate(sum_row):
            if data[1] > 1:
                diff += data[1] - 1
            if isinstance(data[0], (int, float)):
                _list.append(index + diff)
        return _list

    @staticmethod
    def _get_number(str_value):
        if '.' in str_value:
            return float(str_value)
        return int(str_value)