将EXCEL转换为XML格式

本文介绍了将EXCEL转换为XML格式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

大家好，

在c＃.net中将excel转换为xml逻辑需要一些帮助

Need a small help for the conversion of excel to xml logic in c#.net

这是以下格式我需要转换为excel 如果相同的许可证号具有不同的策略，我如何处理逻辑。任何人都可以帮助解释代码的逻辑。非常感谢提前

This is the below format i need to convert from excel and also if the same license number has different policies how can i work on the logic . can anyone help on the logic of code. Many thanks in advance

< Employee> ; $
< Emp ID =" 1">

< LicenseNumber> 999999< / LicenseNumber>

< FirstName> abc< ; / FirstName>

< LastName> vvv< / LastName>

< Policies>

< Policy Xml_PolicyID =" 3" >
$
< PolicyNumber> 123456< / PolicyNumber>

< EffectiveDate> 2015-04-17< / EffectiveDate>

< ExpirationDate> 2014-04-18< / ExpirationDate>

< InsuredName> abbbcdd< / InsuredName>

< Transactions>

<交易Xml_TransactionID =" 3">

< EffectiveDate> 2018-04-15< / EffectiveDate>

< / Transaction>

< / Transactions>

< /政策>

< / Policies>

< / Emp>

< / Employee>

<Employee>
<Emp ID="1">
<LicenseNumber>999999</LicenseNumber>
<FirstName>abc</FirstName>
<LastName>vvv</LastName>
<Policies>
<Policy Xml_PolicyID="3">
<PolicyNumber>123456</PolicyNumber>
<EffectiveDate>2015-04-17</EffectiveDate>
<ExpirationDate>2014-04-18</ExpirationDate>
<InsuredName>abbbcdd</InsuredName>
<Transactions>
<Transaction Xml_TransactionID="3">
<EffectiveDate>2018-04-15</EffectiveDate>
</Transaction>
</Transactions>
</Policy>
</Policies>
</Emp>
</Employee>

推荐答案

如果您需要在C＃中执行此操作，则可以使用XDocument和LINQ，因为你想要一个干净的XML文件。但是您为Excel发布的示例并没有接近您的XML所显示的所有数据。您的XML基于单个员工，但Excel信息
似乎与许可证相关联。鉴于您的XML，似乎单个员工与单个许可证捆绑在一起

If you need to do this in C# you can use XDocument and LINQ, since you want a clean XML file. But the example you posted for Excel doesn't have anywhere near all the data your XML is showing. Your XML is based upon a single employee but the Excel information seems to be tied to a license. Given your XML it appears a single employee is tied to a single license

鉴于您需要对XML进行分组和生成，我会先将组分组到业务对象中（行更难以工作）用）。然后转换为XML。以下代码是使用扩展方法的一种方法。如果数据很复杂，那么一个完全独立的
xml编写器类可能会更好。

Given that you need to group and generate XML I'd say group first into business objects (rows are harder to work with). Then convert to the XML. The following code is one approach using extension methods. If the data is complex then a completely separate xml writer class would probably be better.

class Program
{
    static void Main ( string[] args )
    {
        var dt = LoadExcelDataAsDataTable();

        //Group the data by license number
        var licenses = from r in dt.Rows.OfType<DataRow>()
                        group r by r.Field<string>("LicenseNbr") into g
                        select new Employee() { LicenseNumber = g.Key, Policies = g.ToPolicy().ToList() };

        //Now generate XML
        var employees = from e in licenses
                        select e.ToXml();
    }
}

static class PolicyExtensions
{
    public static IEnumerable<Policy> ToPolicy ( this IEnumerable<DataRow> source )
    {
        foreach (var item in source)
            yield return item.ToPolicy();
    }

    public static Policy ToPolicy ( this DataRow source )
    {
        return new Policy()
        {
            PolicyNumber = source.Field<string>("PolicyNumber")
        };
    }

    public static XElement ToXml ( this Employee source )
    {
        return new XElement("employee",
                new XElement("LicenseNumber", source.LicenseNumber),
                new XElement("Policies",
                    from p in source.Policies
                    select p.ToXml()
                    )
                );
    }

    public static XElement ToXml ( this Policy source )
    {
        return new XElement("Policy",
                new XElement("PolicyNumber", source.PolicyNumber)
                );
    }
}

class Employee
{
    public string LicenseNumber { get; set; }
    public List<Policy> Policies { get; set; }
}

class Policy
{
    public string PolicyNumber { get; set; }
}

这篇关于将EXCEL转换为XML格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

firstName