none
CONVERSION OF EXCEL TO XML FORMAT RRS feed

  • Question

  • Hi All,

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

    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

    LicenseNbr PolicyNumber
    P100000 GL41123
    P100000 TGL41124
    P100000 GL411235
    P100000 GL411236

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


    • Edited by Abdul shif Wednesday, November 8, 2017 2:02 PM
    Wednesday, November 8, 2017 2:00 PM

All replies

  • 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?

    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 

    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; }
    }


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, November 8, 2017 3:01 PM
    Moderator
  • Hi Michael,Thanks much!

    First I have tried with simple data not looping through license number.

    Sorry for the inconvenience, Actually i have 2 doubts in this

    First will be: I used this sample code to generate into xml:but the format of parent and child node confuses me. Can u pls look into the below and the xml format i have generated.

    The output for the which i got doesnt match with the xm format i have shared previously:

    This is the below format i got like but i need </contact> should come before </Insurer>(that is before root node) but i am getting in the below format . Can u pls help on this

    <?xml version="1.0" encoding="UTF-8"?>
    <BatchDataset SubmissionType="INS" ReportingState="FL" Year="2016" Quarter="4" SchemaVersion="1.0">
    <Insurer>
    <NAICNumber>A21346</NAICNumber>
    <Name>Bob’s </Name>
    <Contact>
    <FirstName>aaa</FirstName>
    <LastName>bbb</LastName>
    <EmailAddress>ccc</EmailAddress>
    </Contact>
    <ContactAddress>
    <Address>Sarah.Smith@domain.com</Address>
    <City>1234 Elm St</City>
    <Statecode>Tallahassee</Statecode>
    <Postalcode>FL</Postalcode>
    <Countrycode>32301</Countrycode>
    </ContactAddress>
    <PhoneNumber>
    <Areacode>US</Areacode>
    <Prefix>850</Prefix>
    <Line>555</Line>
    </PhoneNumber>
    <Fax>5555</Fax>
    </Insurer>

    static void Main(string[] args)

            {         

                string Exfilepath = @"C:\Users\Desktop\test.xlsx";

                DataTable dt = new DataTable();

                DataRow dr = null;

                string fetch = null;

                System.Data.OleDb.OleDbConnection MyConnection = null;

                System.Data.DataSet DtSet = null;

                System.Data.OleDb.OleDbDataAdapter MyCommand = null;

                // MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + Exfilepath + "';Extended Properties=Excel 8.0;");

     

                //If you MS Excel 2007 then use below lin instead of above line

                MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Exfilepath + "';Extended Properties=Excel 12.0;");

     

                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);

                DtSet = new System.Data.DataSet();

                MyCommand.Fill(DtSet, "[Sheet1$]");

     

     

                XmlDocument xmlDoc = new XmlDocument();

                XmlNode docNode = xmlDoc.CreateXmlDeclaration("1.0", "UTF-8", null);

                xmlDoc.AppendChild(docNode);

                XmlNode rootNode = xmlDoc.CreateElement("BatchDataset");

                XmlAttribute attribute1 = xmlDoc.CreateAttribute("SchemaVersion");

                XmlAttribute attribute2 = xmlDoc.CreateAttribute("Quarter");

                XmlAttribute attribute3 = xmlDoc.CreateAttribute("Year");

                XmlAttribute attribute4 = xmlDoc.CreateAttribute("ReportingState");

                XmlAttribute attribute5 = xmlDoc.CreateAttribute("SubmissionType");

     

                attribute1.Value = "1.0";

                attribute2.Value = "4";

                attribute3.Value = "2016";

                attribute4.Value = "FL";

                attribute5.Value = "INS";

                rootNode.Attributes.Append(attribute1);

                rootNode.Attributes.Append(attribute2);

                rootNode.Attributes.Append(attribute3);

                rootNode.Attributes.Append(attribute4);

                rootNode.Attributes.Append(attribute5);

                xmlDoc.AppendChild(rootNode);       

                DataTable dt1 = DtSet.Tables[0];

                for (int i = 0; i < dt1.Rows.Count; i++)

                {

                    if (!string.IsNullOrEmpty(dt1.Rows[i][1].ToString()))

                    {

                        //Insurer

                        XmlNode FieldsNode = xmlDoc.CreateElement("Insurer");

                        rootNode.AppendChild(FieldsNode);

                       

                       

                        XmlElement NAIC = xmlDoc.CreateElement("NAICNumber");

                        XmlElement name = xmlDoc.CreateElement("Name");

                        NAIC.InnerText = dt1.Rows[i][0].ToString();

                        name.InnerText = dt1.Rows[i][1].ToString();

                        FieldsNode.AppendChild(NAIC);

                        FieldsNode.AppendChild(name);

     

                        XmlNode Contact = xmlDoc.CreateElement("Contact");

                        FieldsNode.AppendChild(Contact);      

                        XmlElement FirstName = xmlDoc.CreateElement("FirstName");

                        XmlElement LastName = xmlDoc.CreateElement("LastName");

                        XmlElement EmailAddress = xmlDoc.CreateElement("EmailAddress");

                        FirstName.InnerText = dt1.Rows[i][2].ToString();

                        LastName.InnerText = dt1.Rows[i][3].ToString();

                        EmailAddress.InnerText = dt1.Rows[i][4].ToString();

                        Contact.AppendChild(FirstName);

                        Contact.AppendChild(LastName);

                        Contact.AppendChild(EmailAddress);

                       

                        

     

                        XmlElement ContactAddress = xmlDoc.CreateElement("ContactAddress");

                        XmlElement Address = xmlDoc.CreateElement("Address");

                        XmlElement City = xmlDoc.CreateElement("City");

                        XmlElement Statecode = xmlDoc.CreateElement("Statecode");

                        XmlElement Postalcode = xmlDoc.CreateElement("Postalcode");

                        XmlElement Countrycode = xmlDoc.CreateElement("Countrycode");

                        Address.InnerText = dt1.Rows[i][5].ToString();

                        City.InnerText = dt1.Rows[i][6].ToString();

                        Statecode.InnerText = dt1.Rows[i][7].ToString();

                        Postalcode.InnerText = dt1.Rows[i][8].ToString();

                        Countrycode.InnerText = dt1.Rows[i][9].ToString();

                        ContactAddress.AppendChild(Address);

                        ContactAddress.AppendChild(City);

                        ContactAddress.AppendChild(Statecode);

                        ContactAddress.AppendChild(Postalcode);

                        ContactAddress.AppendChild(Countrycode);

                        FieldsNode.AppendChild(ContactAddress);

     

                        XmlElement PhoneNumber = xmlDoc.CreateElement("PhoneNumber");

                        XmlElement Areacode = xmlDoc.CreateElement("Areacode");

                        XmlElement Prefix = xmlDoc.CreateElement("Prefix");

                        XmlElement Line = xmlDoc.CreateElement("Line");

                        Areacode.InnerText = dt1.Rows[i][10].ToString();

                        Prefix.InnerText = dt1.Rows[i][11].ToString();

                        Line.InnerText = dt1.Rows[i][12].ToString();

                        PhoneNumber.AppendChild(Areacode);

                        PhoneNumber.AppendChild(Prefix);

                        PhoneNumber.AppendChild(Line);

                        FieldsNode.AppendChild(PhoneNumber);

     

                        XmlElement Fax = xmlDoc.CreateElement("Fax");

                        Fax.InnerText = dt1.Rows[i][13].ToString();

                        FieldsNode.AppendChild(Fax);

                        //rootNode.AppendChild(FieldsNode);

                       // rootNode.AppendChild(Contact);

     

                        //Broker

                      

                        XmlNode Broker = xmlDoc.CreateElement("Brokers");

                        rootNode.AppendChild(Broker);

     

                        XmlElement BrokerId = xmlDoc.CreateElement("BrokerId");

                        Broker.AppendChild(BrokerId);

                       

                        XmlElement LicenseNo = xmlDoc.CreateElement("LicenseNumber");

                        XmlElement Firstname = xmlDoc.CreateElement("Firstname");

                        XmlElement Lastname = xmlDoc.CreateElement("Lastname");

                        LicenseNo.InnerText = dt1.Rows[i][15].ToString();

                        Firstname.InnerText = dt1.Rows[i][2].ToString();

                        Lastname.InnerText = dt1.Rows[i][3].ToString();

                        BrokerId.AppendChild(LicenseNo);

                        BrokerId.AppendChild(Firstname);

     

                        XmlElement Policy = xmlDoc.CreateElement("Policies");

                       

                        XmlNode PolicyId = xmlDoc.CreateElement("PolicyId");

                        Broker.AppendChild(PolicyId);

                        XmlElement PolicyNumber = xmlDoc.CreateElement("PolicyNumber");

                        XmlElement EffectiveDate = xmlDoc.CreateElement("EffectiveDate");

                        XmlElement ExpirationDate = xmlDoc.CreateElement("ExpirationDate");

                        XmlElement InsuredName = xmlDoc.CreateElement("InsuredName");

                        PolicyNumber.InnerText = dt1.Rows[i][16].ToString();

                        EffectiveDate.InnerText = dt1.Rows[i][17].ToString();

                        ExpirationDate.InnerText = dt1.Rows[i][18].ToString();

                        InsuredName.InnerText = dt1.Rows[i][19].ToString();

                        PolicyId.AppendChild(PolicyNumber);

                        PolicyId.AppendChild(EffectiveDate);

                        PolicyId.AppendChild(ExpirationDate);

                        PolicyId.AppendChild(InsuredName);

                        Broker.AppendChild(Policy);

     

     

                

                    }

                }

     

                xmlDoc.Save("C:\\xmlconver2.xml");


            }

    Thursday, November 9, 2017 9:05 AM
  • If the XML isn't in the format you want it is because you're hooking a child to the wrong parent. This is easy to do when working with XmlDocument because it is a tough API. XDocument would clean this up nicely. Here's an updated version of your code using XDocument. By using XDocument you can more easily see the nesting of the objects that are going on. This makes it far easier to notice that you're rooting contact to the wrong child. It can be easily fixed by moving the Contact XElement to the correct location in the code. 

    //Using pure LINQ
    
    //Set up XML file and root element
    var doc = new XDocument(
                    new XDeclaration("1.0", "UTF-8", "yes"), 
                    new XElement("BatchDataset", 
                        new XAttribute("SchemaVersion", "1.0"),
                        new XAttribute("Quarter", "4"),
                        new XAttribute("Year", "2016"),
                        new XAttribute("ReportingState", "FL"),
                        new XAttribute("SubmissionType", "INS")
                    )
                );
    var root = doc.Root;
    
    //Using raw ADO.NET here, might do better to normalize into a business object
    var dt1 = LoadData();
    foreach (var row in dt1.Rows.OfType<DataRow>())
    {
        //Skip if name is empty
        if (String.IsNullOrEmpty(row.Field<string>(1)))
            continue;
    
        //Insurer
        root.Add(new XElement("Insurer", 
                    new XElement("NAICNumber", row[0]),
                    new XElement("Name", row[1])
                    )
                );
    
        //Contact - at root is I believe what you wanted
        root.Add(new XElement("Contact",
                    new XElement("FirstName", row[2]),
                    new XElement("LastName", row[3]),
                    new XElement("EmailAddress", row[4]),
    
                    //Making a guess here but I think you want the ContactAddress under Contact, not Insurer??
                    new XElement("ContactAddress",
                        new XElement("Address", row[5]),
                        new XElement("City", row[6]),
                        new XElement("Statecode", row[7]),
                        new XElement("Postalcode", row[8]),
                        new XElement("Countrycode", row[9])
                        ),
    
                    //Same thing for PhoneNumber??
                    new XElement("PhoneNumber", 
                        new XElement("Areacode", row[10]),
                        new XElement("Prefix", row[11]),
                        new XElement("Line", row[12])
                        ),
    
                    //Fax??
                    new XElement("Fax", row[13])
                    )
                );
    
        //Broker
        root.Add(new XElement("Broker",
                    new XElement("BrokerId", 
                        new XElement("LicenseNumber", row[15]),
                        new XElement("Firstname", row[2]),
                        new XElement("Lastname", row[3])
                        )
                    ),
    
                    //Policies - but this seems like 1, shouldn't this be Policies with child elements called Policy??
                    new XElement("Policies",
                        new XElement("PolicyNumber", row[16]),
                        new XElement("EffectiveDate", row[17]),
                        new XElement("ExpirationDate", row[18]),
                        new XElement("InsuredName", row[19])
                        )
                );                              
    }

    Another benefit of XDocument is that it supports any # of children in each of the methods so you don't have to worry about tracking parents, building arrays, etc. Yet another benefit is that XElement accepts object as the content. So you don't have to explicitly convert everything to strings. This dramatically cleans up the code.

    If you need to do much more with the raw table then you should consider perhaps using a business object instead. This would allow you to move the monolithic XML generation into extension classes which make may it easier to manage. Alternatively you might be able to use XML serialization but it tends to add namespaces which most people don't want.

    Going back to your original question, this does not handle the grouping that you mentioned you needed. That is where you would do better to forego DataSet and use business objects. Then you can enumerate each insurer and their policies. The above code would work mostly unchanged other than changing from row[#] to a property name. But for the policies element you would then using another foreach to enumerate the policies of the insurer. 


    Michael Taylor http://www.michaeltaylorp3.net

    • Proposed as answer by Fei HuModerator Wednesday, November 15, 2017 10:34 AM
    Thursday, November 9, 2017 4:25 PM
    Moderator
  • This is not an answer to this question, but a example how to convert Excel to XML and vice versa using 3rd party library.

    https://code.msdn.microsoft.com/Convert-Excel-to-XML-and-8995eb98

    Friday, November 10, 2017 7:48 AM
  • Hello ,

    Is there anything updated? If the issues has been solved, you could close the thread by marking as answer, This can be beneficial to other community members reading this thread.

    Sincerely,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 15, 2017 10:35 AM
    Moderator