How to use Python to convert files saved in a database into Excel?

[yao-big]

I am currently on the backend of Odoo and want to convert JSON files into Excel
I am using OpenPyXL to write to Excel, but I am unable to write cell styles
Is there any good way?
This is my code

from openpyxl.styles import Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter, range_boundaries
from io import BytesIO
import json
import re
from openpyxl import Workbook

json_str = {
    'version': 22,
    'sheets': [{
        'id': '保温筒',
        'name': '保温筒',
        'colNumber': 30,
        'rowNumber': 100,
        'rows': {
            '0': {'size': 89}, '1': {'size': 36}, '2': {'size': 24}, '3': {'size': 40}, '4': {'size': 40},
            '5': {'size': 65}, '6': {'size': 40}, '7': {'size': 40}, '8': {'size': 40}, '9': {'size': 40},
            '10': {'size': 37}, '11': {'size': 37}, '12': {'size': 37}, '13': {'size': 37}, '14': {'size': 37},
            '15': {'size': 37}, '16': {'size': 37}, '17': {'size': 36}, '18': {'size': 36}, '19': {'size': 36},
            '20': {'size': 36}, '21': {'size': 36}, '22': {'size': 36}, '23': {'size': 36}, '24': {'size': 36},
            '25': {'size': 36}, '26': {'size': 36}, '27': {'size': 36}, '28': {'size': 36}, '29': {'size': 36},
            '30': {'size': 36}, '31': {'size': 36}, '32': {'size': 36}, '33': {'size': 36}, '34': {'size': 36},
            '35': {'size': 36}, '36': {'size': 36}, '37': {'size': 36}, '38': {'size': 36}, '39': {'size': 36},
            '40': {'size': 36}, '41': {'size': 36}, '42': {'size': 36}, '43': {'size': 36}, '44': {'size': 36},
            '45': {'size': 36}, '46': {'size': 36}, '47': {'size': 36}, '48': {'size': 36}, '49': {'size': 36},
            '50': {'size': 36}, '51': {'size': 36}, '52': {'size': 36}, '53': {'size': 36}, '54': {'size': 36},
            '55': {'size': 36}, '56': {'size': 36}, '57': {'size': 36}, '58': {'size': 36}, '59': {'size': 36},
            '60': {'size': 36}, '61': {'size': 36}, '62': {'size': 36}, '63': {'size': 36}, '64': {'size': 36},
            '65': {'size': 36}, '66': {'size': 36}, '67': {'size': 36}, '68': {'size': 36}, '69': {'size': 36},
            '70': {'size': 36}, '71': {'size': 36}, '72': {'size': 36}, '73': {'size': 36}, '74': {'size': 36},
            '75': {'size': 36}, '76': {'size': 36}, '77': {'size': 36}, '78': {'size': 36}, '79': {'size': 36},
            '80': {'size': 36}, '81': {'size': 36}, '82': {'size': 36}, '83': {'size': 36}, '84': {'size': 36},
            '85': {'size': 36}, '86': {'size': 36}, '87': {'size': 36}, '88': {'size': 36}, '89': {'size': 36},
            '90': {'size': 36}, '91': {'size': 36}, '92': {'size': 36}, '93': {'size': 36}, '94': {'size': 36},
            '95': {'size': 36}, '96': {'size': 36}, '97': {'size': 36}, '98': {'size': 36}, '99': {'size': 36}
        },
        'cols': {
            '0': {'size': 166}, '1': {'size': 166}, '2': {'size': 155}, '3': {'size': 152}, '4': {'size': 155},
            '5': {'size': 150}, '6': {'size': 220}, '7': {'size': 220}, '8': {'size': 169}, '9': {'size': 174},
            '10': {'size': 168}, '11': {'size': 157}, '12': {'size': 157}, '13': {'size': 147}, '14': {'size': 147},
            '15': {'size': 125}, '16': {'size': 121}, '17': {'size': 161}, '18': {'size': 161}, '19': {'size': 161},
            '20': {'size': 161}, '21': {'size': 166}, '22': {'size': 163}, '23': {'size': 138}, '24': {'size': 138},
            '25': {'size': 149}, '26': {'size': 158}, '27': {'size': 154}, '28': {'size': 435}, '29': {'size': 83}
        },
        'merges': [
            'A1:AB1', 'A2:AB2', 'A3:AB3', 'B4:D4', 'F4:G4', 'I4:K4', 'M4:O4', 'P4:P10', 'Q4:Q10',
            'R4:R10', 'S4:S10', 'T4:T10', 'U4:U10', 'V4:V10', 'W4:W10', 'X4:X10', 'Y4:Y10', 'Z4:Z10',
            'AA4:AA10', 'AB4:AB10', 'B5:O5', 'B6:O6', 'B7:B10', 'C7:D7', 'E7:F7', 'J7:J10', 'L7:M8',
            'N7:O10', 'C8:D8', 'E8:F8', 'K8:K10', 'C9:D9', 'E9:F9', 'L9:M10', 'C10:D10', 'E10:F10',
            'A11:A16', 'B11:B16', 'D11:D13', 'F11:F13', 'G11:G12', 'H11:H12', 'I11:I17', 'J11:J17',
            'K11:K17', 'N11:N13', 'O11:O13', 'P11:P13', 'Q11:P13', 'R11:R17', 'S11:S17', 'T11:T17',
            'U11:U17', 'V11:V17', 'W11:W17', 'X11:X17', 'Y11:Y17', 'Z11:Z17', 'AA11:AA17', 'AB11:AB17',
            'G13:G14', 'H13:H14', 'D14:D16', 'F14:F16', 'L14:M17', 'N14:N17', 'O14:O17',
            'P14:P17', 'Q14:Q17', 'G15:G16', 'H15:H16', 'A17:B17'
        ],
        'cells': {
            'A1': {'content': '222222'},
            'A2': {'content': '4444444'},
            'A3': {'content': '编号:fffffff'},
            'A4': {'content': '产品名称'},
            'A5': {'content': '检验依据'},
            'A6': {'content': '外观'},
            'A7': {'content': '检验项点'},
            'A8': {'content': '检验标准'},
            'A9': {'content': '检验工具'},
            'A10': {'content': '工具gggg'},
            'A17': {'content': '平均值/差值'},
            'B5': {'content': '图ggggg'},
            'B6': {'content': 'ttttt针'},
            'B7': {'content': '矫形备注'},
            'C7': {'content': '外径（+2/0）'},
            'C8': {'content': '1082'},
            'C11': {'content': '1084.3'},
            'C12': {'content': '1083.2'},
            'C13': {'content': '1085.1'},
            'C14': {'content': '1085.1'},
            'C15': {'content': '1084.6'},
            'C16': {'content': '1084.6'},
            'C17': {'content': '=AVERAGE(C11:C16)'},
            'D11': {'content': '=MAX(C11:C16)'},
            'D14': {'content': '=MIN(C11:C16)'},
            'D17': {'content': '=D11-D14'},
            'E4': {'content': '生产批号'},
            'E7': {'content': '内径（0/-2）'},
            'E8': {'content': '1031'},
            'E11': {'content': '1028.5'},
            'E12': {'content': '1030.9'},
            'E13': {'content': '1031.5'},
            'E14': {'content': '1028.6'},
            'E15': {'content': '1029'},
            'E16': {'content': '1028.6'},
            'E17': {'content': '=AVERAGE(E11:E16)'},
            'F11': {'content': '=MAX(E11:E16)'},
            'F14': {'content': '=MIN(E11:E16)'},
            'F17': {'content': '=F11-F14'},
            'G7': {'content': '高度（+2/0）'},
            'G8': {'content': '548'},
            'G11': {'content': '568'},
            'G13': {'content': '565'},
            'G15': {'content': '568'},
            'G17': {'content': '=AVERAGE(G11:G16)'},
            'H4': {'content': '产品规格'},
            'H7': {'content': '壁厚（+2/0）'},
            'H8': {'content': '25.5'},
            'H11': {'content': '27.9'},
            'H13': {'content': '26.8'},
            'H15': {'content': '27.8'},
            'H17': {'content': '=AVERAGE(H11:H16)'},
            'I7': {'content': '重量（kg)'},
            'I8': {'content': '21.34'},
            'I11': {'content': '25.2'},
            'J7': {'content': '体积\ncm³'},
            'J11': {'content': '=(C17+E17)*PI()*H17*G17/2/1000'},
            'K7': {'content': '密度g/cm³'},
            'K8': {'content': '0.42(±0.02)'},
            'K11': {'content': '=ROUND((I11)*1000/(J11),2)'},
            'L4': {'content': '产品数量'},
            'L7': {'content': '外观（合格“√”，不合格尺寸需描述)  '},
            'L9': {'content': '目测'},
            'L11': {'content': '外径超下差'},
            'L12': {'content': '内径超上差'},
            'L13': {'content': '高度超上差'},
            'L14': {'content': '1'},
            'M11': {'content': '=$C$8-D14'},
            'M12': {'content': '=F11-$E$8'},
            'M13': {'content': '=G17-$G$8'},
            'N7': {'content': '内、外径余量'},
            'N11': {'content': '外径最小单边余量'},
            'N14': {'content': '内径最小单边余量'},
            'O11': {'content': '=(D14-($C$8-9))/2'},
            'O14': {'content': '=ABS(F11-($E$8+12))/2'},
            'P4': {'content': '圆度'},
            'P11': {'content': '=D17/2'},
            'P14': {'content': '=F17/2'},
            'Q4': {'content': '差值'},
            'Q11': {'content': '=O11-P11'},
            'Q14': {'content': '=O14-P14'},
            'R4': {'content': '设备编号'},
            'S4': {'content': '工艺层数'},
            'S11': {'content': '24'},
            'T4': {'content': '实际层数'},
            'T11': {'content': '29'},
            'U4': {'content': '层间密度'},
            'U11': {'content': '=T11*10/H17'},
            'V4': {'content': '尺寸分类'},
            'V11': {'content': '=IF(AND(D14>=$C$8,F11<=$E$8,MIN(G11:G16)>=$G$8),"A",IF(AND((O14-P14)>=3.5,(O11-P11)>=3,MIN(G11:G16)>=($G$8-3)),"B",IF(AND((O14-P14)>=2.5,(O11-P11)>=2,MIN(G11:G16)>=($G$8-3)),"C",IF(AND(O11>0,O14>0,MIN(G11:G16)>($G$8-8)),"D","E"))))'},
            'W4': {'content': '外观分类'},
            'X4': {'content': '高度分类'},
            'X11': {'content': '=IF(MIN(G11:G16)<$G$8,"H",(IF(MIN(G11:G16)>=$G$8,"/")))'},
            'Y4': {'content': '处理结果'},
            'Y11': {'content': '=IF(AND(K11>=0.4,OR(V11="A",V11="B"),W11=""),"合格",IF(AND(K11>=0.4,V11="C",W11=""),"让步放行",""))'},
            'Z4': {'content': '检验时间'},
            'AA4': {'content': '操作员'},
            'AB4': {'content': '检验员'}
        }
    }],
    'styles': {
        '1': {'bold': True, 'verticalAlign': 'middle', 'align': 'center', 'fontSize': 24, 'wrapping': 'wrap'},
        '2': {'bold': True, 'verticalAlign': 'middle', 'align': 'center', 'fontSize': 28},
        '3': {'align': 'right', 'fontSize': 12},
        '4': {'verticalAlign': 'middle', 'align': 'center', 'fontSize': 18},
        '5': {'verticalAlign': 'middle', 'align': 'center', 'fontSize': 18, 'wrapping': 'wrap'},
        '6': {'verticalAlign': 'middle', 'align': 'center', 'fontSize': 15},
        '7': {'align': 'center', 'fontSize': 11},
        '8': {'verticalAlign': 'middle', 'align': 'center', 'fontSize': 15, 'wrapping': 'wrap'},
        '9': {'verticalAlign': 'middle', 'fontSize': 18},
        '10': {'verticalAlign': 'middle', 'fontSize': 18, 'wrapping': 'wrap'},
        '11': {'align': 'center', 'fontSize': 15},
        '12': {'align': 'center', 'fontSize': 22}
    },
    'formats': {'1': '@', '2': '0.00'},
    'borders': {
        '1': {'bottom': {'style': 'thin', 'color': '#000000'}},
        '2': {'top': {'style': 'thin', 'color': '#000000'}, 'bottom': {'style': 'thin', 'color': '#000000'},
              'left': {'style': 'thin', 'color': '#000000'}, 'right': {'style': 'thin', 'color': '#000000'}},
        '3': {'top': {'style': 'thin', 'color': '#000000'}, 'left': {'style': 'thin', 'color': '#000000'},
              'right': {'style': 'thin', 'color': '#000000'}},
        '4': {'left': {'style': 'thin', 'color': '#000000'}, 'right': {'style': 'thin', 'color': '#000000'}},
        '5': {'bottom': {'style': 'thin', 'color': '#000000'}, 'left': {'style': 'thin', 'color': '#000000'},
              'right': {'style': 'thin', 'color': '#000000'}},
        '6': {'top': {'style': 'thin', 'color': '#000000'},
              'bottom': {'style': 'thin', 'color': '#000000'},
              'left': {'style': 'thin', 'color': '#000000'}},
        '7': {'top': {'style': 'thin', 'color': '#000000'}},
        '8': {'top': {'style': 'thin', 'color': '#000000'},
              'bottom': {'style': 'thin', 'color': '#000000'},
              'right': {'style': 'thin', 'color': '#000000'}},
        '9': {'top': {'style': 'thin', 'color': '#000000'},
              'bottom': {'style': 'thin', 'color': '#000000'}},
        '10': {'top': {'style': 'thin', 'color': '#000000'},
               'left': {'style': 'thin', 'color': '#000000'}},
        '11': {'bottom': {'style': 'thin', 'color': '#000000'},
               'left': {'style': 'thin', 'color': '#000000'}},
        '12': {'left': {'style': 'thin', 'color': '#000000'}},
        '13': {'top': {'style': 'thin', 'color': '#000000'},
               'right': {'style': 'thin', 'color': '#000000'}},
        '14': {'bottom': {'style': 'thin', 'color': '#000000'},
               'right': {'style': 'thin', 'color': '#000000'}},
        '15': {'right': {'style': 'thin', 'color': '#000000'}}
    },
    'revisionId': '2b04b166-6121-42ee-ba91-8d997e820649',
    'uniqueFigureIds': True,
    'settings': {
        'locale': {'name': 'English (US)', 'code': 'en_US', 'thousandsSeparator': ',', 'decimalSeparator': '.',
                   'weekStart': 7, 'dateFormat': 'm/d/yyyy', 'timeFormat': 'hh:mm:ss a', 'formulaArgSeparator': ','}
    },
    'pivots': {},
    'pivotNextId': 1,
    'customTableStyles': {},
    'odooVersion': 12,
    'globalFilters': [],
    'lists': {},
    'listNextId': 1,
    'chartOdooMenusReferences': {}
}


class SpreadsheetConverter:
    def __init__(self, spreadsheet_json):
        """
        初始化转换器，传入的 JSON 数据需包含:
         - sheets: [ { name, rows, cols, merges, cells }, ... ]
         - styles: { '1': {...}, '2': {...}, ... }    # 行级样式
         - formats: { '1': '0.00', '2': '@', ... }    # 行级数字格式
         - borders: { '1': {...}, '2': {...}, ... }   # 行级边框
        """
        self.data = spreadsheet_json

    @staticmethod
    def fix_color(color_value):
        """
        确保颜色值为 aRGB 格式，如果颜色值只有6位，则在前面加上 'FF'
        """
        if not color_value:
            return None
        color_value = color_value.strip()
        if color_value.startswith("#"):
            color_value = color_value[1:]
        if len(color_value) == 6:
            color_value = "FF" + color_value
        return color_value

    @staticmethod
    def convert_alignment(style_dict):
        """
        将 style_dict 中 'align' / 'verticalAlign' 转换为 openpyxl 的 Alignment
         - 如果 style_dict['align'] == 'middle'，则用 'center'
         - 如果 style_dict['verticalAlign'] == 'middle'，也用 'center'
        """
        horizontal = style_dict.get('align', 'general')
        if horizontal == 'middle':
            horizontal = 'center'
        if horizontal not in {'general', 'left', 'center', 'right', 'fill',
                              'justify', 'centerContinuous', 'distributed'}:
            horizontal = 'general'
        vertical = style_dict.get('verticalAlign', 'bottom')
        if vertical == 'middle':
            vertical = 'center'
        if vertical not in {'top', 'center', 'bottom', 'justify', 'distributed'}:
            vertical = 'bottom'
        wrap_text = (style_dict.get('wrapping') == 'wrap')
        return Alignment(horizontal=horizontal, vertical=vertical, wrap_text=wrap_text)

    def convert(self):
        """
        将 JSON 数据转换为 Excel 文件，返回二进制流
        """
        if not self.data:
            return None

        wb = Workbook()
        if wb.active:
            wb.remove(wb.active)

        # 从顶层取出行级的样式、格式和边框定义
        row_styles = self.data.get("styles", {})
        row_formats = self.data.get("formats", {})
        row_borders = self.data.get("borders", {})

        # 遍历每个 sheet
        for sheet_info in self.data.get("sheets", []):
            sheet_name = sheet_info.get("name", "Sheet1")
            ws = wb.create_sheet(title=sheet_name)

            # 1) 设置行高
            for row_key, row_info in sheet_info.get("rows", {}).items():
                row_index = int(row_key) + 1
                ws.row_dimensions[row_index].height = row_info.get("size", 15)

            # 2) 设置列宽
            for col_key, col_info in sheet_info.get("cols", {}).items():
                col_index = int(col_key) + 1
                col_letter = get_column_letter(col_index)
                ws.column_dimensions[col_letter].width = col_info.get("size", 10) / 7

            # 3) 填充单元格数据
            for cell_ref, cell_data in sheet_info.get("cells", {}).items():
                content = cell_data.get("content", "")
                if isinstance(content, str) and content.startswith('='):
                    ws[cell_ref].value = content
                else:
                    try:
                        ws[cell_ref].value = float(content)
                    except:
                        ws[cell_ref].value = content

            # 4) 合并单元格
            for merge_range in sheet_info.get("merges", []):
                try:
                    ws.merge_cells(merge_range)
                except ValueError:
                    min_col, min_row, max_col, max_row = range_boundaries(merge_range)
                    if max_col < min_col:
                        min_col, max_col = max_col, min_col
                    if max_row < min_row:
                        min_row, max_row = max_row, min_row
                    fixed_range = f"{get_column_letter(min_col)}{min_row}:{get_column_letter(max_col)}{max_row}"
                    ws.merge_cells(fixed_range)

            # 5) 行级样式应用
            max_sheet_row = sheet_info.get("rowNumber", 100)
            for row_idx in range(1, max_sheet_row + 1):
                # (a) 行级样式
                style_dict = row_styles.get(str(row_idx), {})
                if style_dict:
                    font = Font(bold=style_dict.get('bold', False),
                                size=style_dict.get('fontSize', 11))
                    alignment = self.convert_alignment(style_dict)
                else:
                    font = None
                    alignment = None

                # (b) 行级数字格式
                number_format = row_formats.get(str(row_idx), None)

                # (c) 行级边框
                border_dict = row_borders.get(str(row_idx), {})
                border_obj = None
                if border_dict:
                    border_obj = Border(
                        left=Side(
                            style=border_dict.get('left', {}).get('style'),
                            color=self.fix_color(border_dict.get('left', {}).get('color'))
                        ),
                        right=Side(
                            style=border_dict.get('right', {}).get('style'),
                            color=self.fix_color(border_dict.get('right', {}).get('color'))
                        ),
                        top=Side(
                            style=border_dict.get('top', {}).get('style'),
                            color=self.fix_color(border_dict.get('top', {}).get('color'))
                        ),
                        bottom=Side(
                            style=border_dict.get('bottom', {}).get('style'),
                            color=self.fix_color(border_dict.get('bottom', {}).get('color'))
                        )
                    )
                # 应用样式到整行所有单元格
                row_cells = ws[row_idx]
                for cell in row_cells:
                    if font:
                        cell.font = font
                    if alignment:
                        cell.alignment = alignment
                    if border_obj:
                        cell.border = border_obj
                    if number_format:
                        cell.number_format = number_format

        stream = BytesIO()
        wb.save(stream)
        stream.seek(0)
        return stream.getvalue()


if __name__ == "__main__":
    converter = SpreadsheetConverter(json_str)
    excel_bytes = converter.convert()
    if excel_bytes:
        with open("output.xlsx", "wb") as f:
            f.write(excel_bytes)
        print("Excel 文件已生成：output.xlsx")
    else:
        print("未找到有效的 spreadsheet 数据。")