Python: 如何調整輸出的xlsx檔,套用最適col width / row height?

# -*- coding: utf-8 -*-
"""
Created on Fri Nov 28 19:53:53 2024

@author: SavingKing
"""


import pandas as pd
import json
from openpyxl import load_workbook, Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment

#創建一個含有 JSON 字符串的 DataFrame
data = {
    'ID': [1, 2],
    'Data': [
        json.dumps({"x": 1, "y": 2, "info": {"a": "hello", 
                                             "b": "world"}}, 
                                             indent=2),
        
        json.dumps({"x": 10, "y": 20, "info": {"a": "test", 
                                               "b": "example", 
                                               "c": "demo"}}, 
                                               indent=2)
    ]
}
df = pd.DataFrame(data)

# 指定保存的 Excel 文件路徑
path = "complex_data_sequence.xlsx"

# 保存 DataFrame 到 Excel
df.to_excel(path, sheet_name='Data Sequence', 
index=False, engine='openpyxl')

# 加載工作簿以調整col寬和row高
workbook = load_workbook(path)
worksheet = workbook['Data Sequence']

# 調整col寬，並為包含 JSON 的列啟用文本自動換行
for col in worksheet.columns:
    max_length = 0
    column = col[0].column  # 獲取col number: 1->A ; 2->B ; 3->C
    column_letter = get_column_letter(column)
    for cell in col:
        if cell.value:  # 检查单元格是否有内容
            cell.alignment = Alignment(wrap_text=True)
            cell_length = max(len(line) for line in str(cell.value).split('\n'))
            max_length = max(max_length, cell_length)
    worksheet.column_dimensions[column_letter].width = max_length + 2

# 調整row高，考慮到 JSON 字符串的row數
for row in worksheet.iter_rows(min_row=2, 
                               max_col=worksheet.max_column, 
                               max_row=worksheet.max_row):
    for cell in row:
        if cell.column == 2:  # 假設 JSON 數據在第二col
            formatted_json = json.dumps(json.loads(cell.value), indent=2)
            line_count = formatted_json.count('\n') + 1
            worksheet.row_dimensions[cell.row].height = line_count * 15  
            # 假設每row高度15

# 保存調整後的 Excel 文件
workbook.save(path)
print(f"Excel 文件已經調整並保存到'{path}'。")