将EXCEL转换为XML格式 [英] CONVERSION OF EXCEL TO XML FORMAT

问题描述

大家好，

在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>

推荐答案

真的取决于您如何从Excel中读取数据。如果您需要在没有任何中间处理的情况下执行此操作，那么为什么不直接将Excel直接保存为XML？

It really depends upon how you're reading that data from Excel. If you need to do this without any intermediate processing then why not just have Excel save the data directly as XML?

如果您需要在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格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！