# 導(dǎo)入所需庫(kù)
from spire.xls import Workbook, FileFormat, BuiltInStyles
import json

# 讀取并解析JSON訂單數(shù)據(jù)
with open("E-Commerce Order Data.json", "r", encoding="utf-8") as f:
    jsonData = json.load(f)

# 定義Excel列標(biāo)題
headers = ["order_id", "customer", "order_date", "status", "total", "product", "quantity", "price"]

# 將嵌套的JSON結(jié)構(gòu)轉(zhuǎn)換為扁平化表格數(shù)據(jù)
rows = []
for order in jsonData:
    for item in order["items"]:
        # 合并訂單主數(shù)據(jù)和商品明細(xì)數(shù)據(jù)
        row = [
            order["order_id"], order["customer"], order["order_date"],
            order["status"], str(order["total"]), item["product"],
            str(item["quantity"]), str(item["price"])
        ]
        rows.append(row)

# 初始化Excel工作簿和工作表
workbook = Workbook()
workbook.Worksheets.Clear()
sheet = workbook.Worksheets.Add("Orders")

# 寫入表頭到首行
for col, header in enumerate(headers):
    sheet.Range[1, col + 1].Value = header

# 寫入數(shù)據(jù)行內(nèi)容
for row_idx, row_data in enumerate(rows):
    for col_idx, value in enumerate(row_data):
        sheet.Range[row_idx + 2, col_idx + 1].Value = value

# 設(shè)置表格樣式
sheet.Rows[0].BuiltInStyle = BuiltInStyles.Heading2  # 標(biāo)題行樣式
for row in range(1, sheet.Rows.Count):
    sheet.Rows[row].BuiltInStyle = BuiltInStyles.Accent2_40  # 數(shù)據(jù)行樣式

# 自動(dòng)調(diào)整列寬
for col in range(sheet.Columns.Count):
    sheet.AutoFitColumn(col + 1)

# 保存并釋放資源
workbook.SaveToFile("output/JSONToExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()

# 導(dǎo)入XML處理庫(kù)和Excel操作庫(kù)
import xml.etree.ElementTree as ET
from spire.xls import Workbook, FileFormat, BuiltInStyles

# 解析XML變更日志文件
tree = ET.parse("Software Manual Changelog.xml")
root = tree.getroot()

# 定義表格列標(biāo)題
headers = ["version", "date", "editor", "change"]
rows = []

# 提取并轉(zhuǎn)換XML數(shù)據(jù)結(jié)構(gòu)
for entry in root.findall("entry"):
    # 提取公共字段
    version = entry.findtext("version", "")
    date = entry.findtext("date", "")
    editor = entry.findtext("editor", "")

    # 展開多個(gè)變更條目為獨(dú)立行
    for change in entry.find("changes").findall("change"):
        rows.append([version, date, editor, change.text.strip()])

# 創(chuàng)建Excel工作簿
workbook = Workbook()
workbook.Worksheets.Clear()
sheet = workbook.Worksheets.Add("Changelog")

# 寫入表格標(biāo)題行
for col, header in enumerate(headers):
    sheet.Range[1, col + 1].Value = header

# 填充變更記錄數(shù)據(jù)
for row_idx, row_data in enumerate(rows):
    for col_idx, value in enumerate(row_data):
        sheet.Range[row_idx + 2, col_idx + 1].Value = value

# 應(yīng)用樣式模板
sheet.Rows[0].BuiltInStyle = BuiltInStyles.Heading1  # 主標(biāo)題樣式
for row in range(1, sheet.Rows.Count):
    sheet.Rows[row].BuiltInStyle = BuiltInStyles.Accent1_40  # 交替行底色

# 自適應(yīng)列寬設(shè)置
for col in range(sheet.Columns.Count):
    sheet.AutoFitColumn(col + 1)

# 輸出文件并釋放資源
workbook.SaveToFile("output/XMLToExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()

# 導(dǎo)入YAML處理庫(kù)和Excel操作庫(kù)
import yaml
from spire.xls import Workbook, FileFormat, BuiltInStyles

# 加載CI/CD流水線配置文件
with open("CI CD Pipeline Configuration.yaml", "r", encoding="utf-8") as f:
    yaml_data = yaml.safe_load(f)  # 安全解析YAML內(nèi)容

# 定義流水線分析報(bào)表列結(jié)構(gòu)
headers = ["stage", "command", "output_file", "coverage", "environment"]
rows = []

# 展開流水線階段的多維數(shù)據(jù)
for stage in yaml_data["stages"]:
    # 提取階段基礎(chǔ)信息
    name = stage.get("name", "")
    commands = stage.get("commands", [])
    coverage = str(stage.get("coverage", ""))  # 數(shù)值轉(zhuǎn)字符串
    environment = stage.get("environment", "")
    outputs = stage.get("artifacts", []) or [""]  # 處理空輸出文件情況

    # 按命令展開明細(xì)行
    for i, cmd in enumerate(commands):
        # 合并數(shù)據(jù)時(shí)保持指標(biāo)數(shù)據(jù)首行展示
        row = [
            name,
            cmd,
            outputs[i] if i < len(outputs) else "",  # 匹配命令與產(chǎn)出文件
            coverage if i == 0 else "",  # 覆蓋率僅首行保留
            environment if i == 0 else ""  # 環(huán)境信息僅首行保留
        ]
        rows.append(row)

# 創(chuàng)建報(bào)表工作簿
workbook = Workbook()
workbook.Worksheets.Clear()
sheet = workbook.Worksheets.Add("Pipeline")

# 構(gòu)建表頭結(jié)構(gòu)
for col, header in enumerate(headers):
    sheet.Range[1, col + 1].Value = header

# 填充動(dòng)態(tài)生成的流水線數(shù)據(jù)
for row_idx, row_data in enumerate(rows):
    for col_idx, value in enumerate(row_data):
        sheet.Range[row_idx + 2, col_idx + 1].Value = str(value)  # 強(qiáng)制轉(zhuǎn)為字符串格式

# 應(yīng)用階梯式樣式方案
sheet.Rows[0].BuiltInStyle = BuiltInStyles.Heading4  # 深色漸變標(biāo)題
for row in range(1, sheet.Rows.Count):
    sheet.Rows[row].BuiltInStyle = BuiltInStyles.Accent2_40  # 淺色交替行背景

# 優(yōu)化列顯示寬度
for col in range(sheet.Columns.Count):
    sheet.AutoFitColumn(col + 1)

# 持久化報(bào)表文件
workbook.SaveToFile("output/YAMLToExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()