none
Coding RRS feed

  • Question

  • Hi,

    I have below XML:

    <?xml version="1.0"?>

    <wfm:TXLife xmlns:wfm="http://abcd.com/enterprise/Workforce123/v1">

    <Info>
      <wfm:Party><wfm:FirstName>ABCD</wfm:FirstName>
        <wfm:LastName>EFGH</wfm:LastName>
        <wfm:ID>1234</wfm:ID>
      </wfm:Party>
      <wfm:Party>
        <wfm:FirstName>IJKL</wfm:FirstName>
        <wfm:LastName>MNOP</wfm:LastName>
        <wfm:ID>4321</wfm:ID>
      </wfm:Party>
    </Info>
    </wfm:TXLife>

    Now i want to read the records from this XML that is First name ,last name and ID and want to compare it with excel. 

    i want this as a console application in .net

    Can anyone please help??

    Friday, December 15, 2017 5:39 AM

All replies

  • Hi,

    I have one XML file where i have records as first name last name and ID of 2 employees.

    I need to read this records one by one and compare it with excel data .

    (here it should get matched as my excel have same 2 employees data) but i want generalise solution.

    can you please help

    Tuesday, December 12, 2017 4:54 AM
  • Hello onkardeshpande,

    Which application are you developing? You could read the node information from xml and then iterate through data in worksheet for comparing.

    Here is the VBA code example.

    Simply xml file

    <users>
    <user><id>1</id><first_name>Test_First_Name_1</first_name><last_name>Test_Last_Name_1</last_name></user>
    <user><id>2</id><first_name>Test_First_Name_2</first_name><last_name>Test_Last_Name_2</last_name></user>
    </users>

    Simply VBA code

    Sub TEST()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Set objDOM = CreateObject("Microsoft.XMLDOM")
    objDOM.Load ("C:\Users\Administrator\Desktop\Test.xml")
    Set objNodes = objDOM.SelectNodes("users/user")
    For Each objNode In objNodes
    ID = objNode.SelectSingleNode("id").Text
    firstname = objNode.SelectSingleNode("first_name").Text
    lastname = objNode.SelectSingleNode("last_name").Text
    For i = 1 To lastRow
    If ws.Cells(i, 1).Text = ID And ws.Cells(i, 2).Text = firstname And ws.Cells(i, 3).Text = lastname Then
    'do what you want to do here
    ws.Rows(i).Interior.Color = vbRed
    End If
    Next i
    Next objNode
    End Sub
    

    Best Regards,

    Terry


    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, December 13, 2017 3:09 AM
  • hello

    I want this in C# .net console application


    Wednesday, December 13, 2017 5:47 AM
  • Hello onKardeshpande,

    You could try to refer to below code.

    Add reference to Microsoft.Office.Interop.Excel And System.Drawing.

    Also need 

    using System.Xml;
    using Excel=Microsoft.Office.Interop.Excel;
    Excel.Application xlApp = new Excel.Application();
                xlApp.Visible = true;
                Excel.Workbook wb = xlApp.Workbooks.Open(@"C:\Users\Administrator\Desktop\Book1.xlsm");
                Excel.Worksheet ws = wb.ActiveSheet;
                int lastRow = ws.Cells[ws.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
                XmlDocument doc = new XmlDocument();
                doc.Load(@"C:\Users\Administrator\Desktop\Test.xml");
                string id;
                string firstname;
                string lastname;
                foreach (XmlNode node in doc.SelectNodes("users/user"))
                {
                    id = node.SelectSingleNode("id").InnerText;
                    firstname = node.SelectSingleNode("first_name").InnerText;
                    lastname = node.SelectSingleNode("last_name").InnerText;
                    for (int i = 1; i <= lastRow; i++)
                    {
                        if (ws.Cells[i, 1].Text == id && ws.Cells[i, 2].Text == firstname && ws.Cells[i, 3].Text == lastname)
                        {
                            //do what you want to do
                            ws.Rows[i].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); ;
                        }
                    }
                }
                Console.ReadLine();

    Best Regards,

    Terry


    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.

    • Proposed as answer by Tony---- Tuesday, December 19, 2017 8:42 AM
    Friday, December 15, 2017 8:19 AM
  • Hi,

    I have below XML file

    <?xml version="1.0"?>

    <wfm:TXLife xmlns:wfm="http://abcd.com/enterprise/Workforce123/v1">

    <Info>
      <wfm:Party><wfm:FirstName>ABCD</wfm:FirstName>
        <wfm:LastName>EFGH</wfm:LastName>
        <wfm:ID>1234</wfm:ID>
      </wfm:Party>
      <wfm:Party>
        <wfm:FirstName>IJKL</wfm:FirstName>
        <wfm:LastName>MNOP</wfm:LastName>
        <wfm:ID>4321</wfm:ID>
      </wfm:Party>
    </Info>
    </wfm:TXLife>
    Now i want to read the records from this XML that is First name ,last name and ID and want to compare it with excel. 

    i want this as a console application in .net

    Can anyone please help??

    • Merged by Fei Hu Wednesday, December 20, 2017 2:38 AM The same question
    Tuesday, December 19, 2017 5:28 AM
  • For the first part related to reading data, consider XML Serialisation. Or try another simple method too:

    XmlDocument doc = new XmlDocument();
    doc.Load( @"C:\MyFile.xml" );
    var nsm = new XmlNamespaceManager( doc.NameTable );
    nsm.AddNamespace( "wfm", "http://abcd.com/enterprise/Workforce123/v1" );
    
    foreach( XmlElement party in doc.SelectNodes( "/*/Info/wfm:Party", nsm ) )
    {
       string firstname = party.SelectSingleNode( "wfm:FirstName", nsm ).InnerText;
       string lastname = party.SelectSingleNode( "wfm:LastName", nsm ).InnerText;
       string id = party.SelectSingleNode( "wfm:ID", nsm ).InnerText;
    
       Console.WriteLine( "{0} {1} {2}", firstname, lastname, id );
       //. . .
    }
    

    • Proposed as answer by Stanly Fan Wednesday, December 20, 2017 2:20 AM
    Tuesday, December 19, 2017 6:23 AM
  • Hi onkardeshpande,

    Thanks for posting in MSDN Forum.

    Has your thread below resolved?

    #Coding

    https://social.msdn.microsoft.com/Forums/office/en-US/10cbf798-572e-4b7f-b30b-cde798273e70/coding?forum=exceldev

    If it has, I would suggest you mark the helpful reply as answer, which is the way to close a thread here.

    If not, please feel free to keep following.

    Regards,

    Tony


    Help each other

    Tuesday, December 19, 2017 8:41 AM
  • Hi onkardeshpande,

    For the first question, please refer to the solution provided by Viorel, then what do you mean about "compare it with excel"?

    Maybe you can read some records from excel cells, and return the value, then compare them one by one.

    Regards,

    Stanly


    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, December 20, 2017 2:26 AM