在python中將excel轉換為XML

我正在嘗試將 excel 資料庫轉換為 python。我有一個交易資料，需要以 xml 格式匯入系統。

我的代碼如下：

df = pd.read_excel("C:/Users/junag/Documents/XML/Portfolio2.xlsx", sheet_name="Sheet1", dtype=object)
root = ET.Element('trading-data')
root.set('xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance')
tree = ET.ElementTree(root)
Portfolios = ET.SubElement(root, "Portfolios")
Defaults = ET.SubElement(Portfolios, "Defaults", BaseCurrency="USD")

for row in df.itertuples():
Portfolio = ET.SubElement(Portfolios, "Portfolio", Name=row.Name, BaseCurrency=row.BaseCurrency2, TradingPower=str(row.TradingPower),
                          ValidationProfile=row.ValidationProfile, CommissionProfile=row.CommissionProfile)
PortfolioPositions = ET.SubElement(Portfolio, "PortfolioPositions")
if row.Type == "Cash":
    PortfolioPosition = ET.SubElement(PortfolioPositions, "PortfolioPosition", Type=row.Type, Volume=str(row.Volume))
    Cash = ET.SubElement(PortfolioPosition, 'Cash', Currency=str(row.Currency))
else:
    PortfolioPosition = ET.SubElement(PortfolioPositions, "PortfolioPosition", Type=row.Type, Volume=str(row.Volume),
                                      Invested=str(row.Invested), BaseInvested=str(row.BaseInvested))
    Instrument = ET.SubElement(PortfolioPosition, 'Instrument', Ticker=str(row.Ticker), ISIN=str(row.ISIN), Market=str(row.Market),
                               Currency=str(row.Currency2), CFI=str(row.CFI))


ET.indent(tree, space="\t", level=0)
tree.write("Portfolios_converted2.xml", encoding="utf-8")

輸出如下所示：

uj5u.com熱心網友回復：

由于您需要單個<Portfolio>和<PortfolioPositions>作為父分組，請考慮通過遍歷資料框串列來嵌套回圈。然后，在每個資料框中回圈遍歷其行：

import xml.etree.ElementTree as ET
import pandas as pd
import xml.dom.minidom as md

df = pd.read_excel("Input.xlsx", sheet_name="Sheet1", dtype=object)

# LIST OF DATA FRAME SPLITS
df_list = [g for i,g in df.groupby(
    ["Name", "BaseCurrency2", "TradingPower", "ValidationProfile", "CommissionProfile"]
)]

# ROOT LEVEL
root = ET.Element('trading-data')
root.set('xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance')

# ROOT CHILD LEVEL
Portfolios = ET.SubElement(root, "Portfolios")
Defaults = ET.SubElement(Portfolios, "Defaults", BaseCurrency="USD")

# GROUP LEVEL ITERATION
for df in df_list:
    Portfolio = ET.SubElement(
        Portfolios, 
        "Portfolio", 
        Name = df["Name"][0],
        BaseCurrency = df["BaseCurrency2"][0], 
        TradingPower = str(df["TradingPower"][0]),
        ValidationProfile = df["ValidationProfile"][0], 
        CommissionProfile = df["CommissionProfile"][0]
    )

    PortfolioPositions = ET.SubElement(Portfolio, "PortfolioPositions")

    # ROW LEVEL ITERATION
    for row in df.itertuples():
        if row.Type == "Cash":
            PortfolioPosition = ET.SubElement(
                PortfolioPositions, 
                "PortfolioPosition", 
                Type = row.Type, 
                Volume = str(row.Volume)
            )
            Cash = ET.SubElement(
                PortfolioPosition, 
                "Cash", 
                Currency = str(row.Currency)
            )
        else:
            PortfolioPosition = ET.SubElement(
                 PortfolioPositions, 
                 "PortfolioPosition", 
                 Type = row.Type,
                 Volume = str(row.Volume),
                 Invested = str(row.Invested), 
                 BaseInvested = str(row.BaseInvested)
            )
            Instrument = ET.SubElement(
                 PortfolioPosition, 
                 "Instrument", 
                 Ticker = str(row.Ticker),
                 ISIN = str(row.ISIN),
                 Market = str(row.Market),
                 Currency = str(row.Currency2),
                 CFI = str(row.CFI)
            )

# SAVE PRETTY PRINT OUTPUT
with open("Output.xml", "wb") as f:
    dom = md.parseString(ET.tostring(root))
    f.write(dom.toprettyxml().encode("utf-8"))

uj5u.com熱心網友回復：

在python中將excel轉換為XML

import openpyxl
import xml.etree.ElementTree as ET

def convert_excel_to_xml(file_name, sheet_name):
    wb = openpyxl.load_workbook(file_name)
    sheet = wb[sheet_name]
    root = ET.Element("root")
    for row in sheet.rows:
        for cell in row:
            ET.SubElement(root, "cell", value=cell.value)
    tree = ET.ElementTree(root)
    tree.write("{}.xml".format(sheet_name))

運行函式

convert_excel_to_xml("test.xlsx", "Sheet1")

標籤：Python 擅长 xml xsd

上一篇：xmlstarlet：通過它的兄弟值選擇元素

下一篇：操作欄內容沒有隱藏