转换XML到数据集或Excel表

本文关键字：Excel 数据集 XML 转换 | 更新日期: 2023-09-27 18:17:16

我有以下xml文件

    <rootTag Type="TheRootType" CollectionTime="2016-02-06T" secretNumber="12345">
  <Toyota Type="Car">
    <item Name="EToyotaID" Valuetype="System.Int32">1</item>
    <item Name="EIdentifier" Valuetype="System.String">KKPP</item>
    <item Name="THEID" Valuetype="System.Int32">0</item>
    <item Name="TheValue" Valuetype="System.Int32">5040</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Toyota>
  <Toyota Type="Car">
    <item Name="EToyotaID" Valuetype="System.Int32">2</item>
    <item Name="EIdentifier" Valuetype="System.String">PPLL</item>
    <item Name="THEID" Valuetype="System.Int32">0</item>
    <item Name="TheValue" Valuetype="System.Int32">3230</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Toyota>
  <Mazda Type="Car">
    <item Name="EMazdaID" Valuetype="System.Int32">1130</item>
    <item Name="EIdentifier" Valuetype="System.String">RRR</item>
    <item Name="TheValue" Valuetype="System.Int32">10</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Mazda>
  <Mazda Type="Car">
    <item Name="EMazdaID" Valuetype="System.Int32">1131</item>
    <item Name="EIdentifier" Valuetype="System.String">SetTemp</item>
    <item Name="TheValue" Valuetype="System.Int32">7</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Mazda>
  <Honda Type="Car">
    <item Name="EHondaID" Valuetype="System.Int32">1726</item>
    <item Name="EIdentifier" Valuetype="System.String">SetUp</item>
    <item Name="THEID" Valuetype="System.Int32">11</item>
    <item Name="IDNum" Valuetype="System.Int32">13</item>
    <item Name="TheValue" Valuetype="System.Int32">327</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Honda>
  <Honda Type="Car">
    <item Name="EHondaID" Valuetype="System.Int32">1727</item>
    <item Name="EIdentifier" Valuetype="System.String">SetUp</item>
    <item Name="THEID" Valuetype="System.Int32">11</item>
    <item Name="IDNum" Valuetype="System.Int32">14</item>
    <item Name="TheValue" Valuetype="System.Int32">327</item>
    <item Name="Num" Valuetype="System.String">1104</item>
  </Honda>
</rootTag>

我想把这个写到Excel文件，我有三个标签在底部。每辆车一辆，丰田一辆，马自达一辆，本田一辆。在每个选项卡，我想有一个表的数据为每辆车，因为所有的汽车具有相同的名称将具有相同数量的属性我试图从xml中获得一个数据集，但这似乎不能正常工作。在数据集中，我得到了以下5个表

1 ) rootTag
2 ) Toyota <= only has 2 columns "car" and "0" 
3 ) item  <== it has all the data I need but it is a mess
4 ) Mazda <= only has 2 columns "car" and "0" 
5 ) Honda <= only has 2 columns "car" and "0" 
here is the code I used 



public static DataSet ConvertXMLToDataSet(string xmlData)
    {
        StringReader stream = null;
        XmlTextReader reader = null;
        try
        {
            DataSet xmlDS = new DataSet("rootTag");
            stream = new StringReader(xmlData);
            // Load the XmlTextReader from the stream
            reader = new XmlTextReader(stream);
            xmlDS.ReadXml(reader);
            return xmlDS;
        }
        catch
        {
            return null;
        }
        finally
        {
            if (reader != null) reader.Close();
        }
    }// Use 

}

我也有XML模式，但我不确定如何在我的情况下使用它。由于

转换XML到数据集或Excel表

我使用数据集来导出(和加载)配置文件之类的XML文件。加载代码看起来不错，只是我使用了StreamReader而不是StringReader。XML是如何创建的?您发布的XML不会导入到数据集/表中。我建议循环遍历XML节点并将值添加到预构建的数据集中。然后，您可以使用下面的代码来创建正确的模式。

    dsRelease _Data = null; // Dataset.
    private void Load_Data()
    {
        StreamReader sr;
        XmlReader reader;
        _Data = new dsRelease(); //new dataset
        sr = new StreamReader(_SavePath, Encoding.UTF8);
        reader = new XmlTextReader(sr);
        _Data.ReadXml(reader, XmlReadMode.ReadSchema);
        if (reader != null) reader.Close();
        if (sr != null) sr.Close();
    }
    public void Save_Data(string savepath)
    {
        XmlTextWriter writer = null;
        writer = new XmlTextWriter(savepath, Encoding.UTF8);
        //Write Dataset.
        _Data.WriteXml(writer, XmlWriteMode.WriteSchema);
    }

试试下面的代码:

var ds = new DataSet();
var xml = XElement.Load("test.xml");
var cars = xml.Elements().GroupBy(elem => elem.Name, elem => elem.Elements());
foreach (var car in cars)
{
    var dt = new DataTable(car.Key.LocalName);
    foreach (var elem in car.First())
    {
        dt.Columns.Add(elem.Attribute("Name").Value,
            Type.GetType(elem.Attribute("Valuetype").Value));
    }
    foreach (var elem in car)
    {
        dt.Rows.Add(elem.Select(x => x.Value).ToArray());
    }
    ds.Tables.Add(dt);
}
相关文章：