Parsing XML file for element nodes then storing text in different arrays for loading into Excel

Question

I want to parse an XML file I will receive from amazon, then take the data and input it into a custom invoice in Excel and print.

I've completed most of this task, but I'm running into difficulties when a customer has 2 parts to their address component such as a <AdressLine1> and a <AddressLine2> field, then my two array's don't equal up and it throws an error in my loop in excel_create when I try to write the values into the excel cell.

Here's my sample XML file:

<ListOrdersResponse xmlns="https://mws.amazonservices.com/Orders/2013-09-01">
<ListOrdersResult>
    <NextToken>M1JyahCtnbyaJqJYLDm0ZIfVkJJPpovR78ndw9jqj1Q/69u0e9w5/rQzazHyYVyLqBXdLk4iogxpJASl2BeRe8j0Nx3CIFZQQMZu4ky1kPOeXaRUvtI0tSJ0wMvlylZkWQWPqGlbsnPaEpJjLWtrc+QJ10Z22+qvY/7NeGX36M2lPNv+vCtnKbuZIF9n45mtnrZ4AbBdBTfK25ICwjklgZC1Ikmk90PCwrOwRyEF9TC5HmwX5IAmwKfxnqm3JqvZfwylPI5qZH51NMrpM18PB5GesRwkZOQCT2ijH+OaBgLthhfBGj6DwqJZsZEQ+rF525news0zC1z2PvV1ggto2Jcae56fKGloAreSIoDY6+Vu4dey78hEtutyjMqWzuEhtk8n/YPwqr6cgigTv6iG1zzBuIzaoIZ5Q2QZkesSbBJRVhUbQn8uebbKT14gSEgJXREjaOhXHEOu88CBt+gncOCR1k9eFxFwh3b1o15VFoq4DX+xmmNRmA==</NextToken>
    <CreatedBefore>06/27/2015 00:00:00</CreatedBefore>
    <Orders>
        <Order>
            <AmazonOrderId>102-0657116-1777031</AmazonOrderId>
            <PurchaseDate>06/25/2015 19:48:25</PurchaseDate>
            <LastUpdateDate>06/25/2015 20:18:42</LastUpdateDate>
            <OrderStatus>Unshipped</OrderStatus>
            <FulfillmentChannel>MFN</FulfillmentChannel>
            <SalesChannel>Amazon.com</SalesChannel>
            <ShipServiceLevel>Std Cont US Street Addr</ShipServiceLevel>
            <ShippingAddress>
                <Name>dan hendricks</Name>
                <AddressLine1>THE BOTIQUE &amp;amp; TAPES</AddressLine1>
                <AddressLine2>330 MAIN ST</AddressLine2>
                <City>LUBBOCK</City>
                <StateOrRegion>TX</StateOrRegion>
                <PostalCode>07345-1742</PostalCode>
                <CountryCode>US</CountryCode>
                <Phone>2738953644</Phone>
            </ShippingAddress>
            <OrderTotal>
                <CurrencyCode>USD</CurrencyCode>
                <Amount>52.00</Amount>
            </OrderTotal>
            <NumberOfItemsShipped>0</NumberOfItemsShipped>
            <NumberOfItemsUnshipped>1</NumberOfItemsUnshipped>
            <PaymentExecutionDetail />
            <PaymentMethod>Other</PaymentMethod>
            <MarketplaceId>ATVPDKIKX0DER</MarketplaceId>
            <BuyerEmail>ted101@marketplace.amazon.com</BuyerEmail>
            <BuyerName>dan hendricks</BuyerName>
            <ShipmentServiceLevelCategory>Standard</ShipmentServiceLevelCategory>
            <ShippedByAmazonTFM>False</ShippedByAmazonTFM>
            <OrderType>StandardOrder</OrderType>
            <EarliestShipDate>06/26/2015 02:00:00</EarliestShipDate>
            <LatestShipDate>06/30/2015 01:59:59</LatestShipDate>
            <EarliestDeliveryDate>07/01/2015 02:00:00</EarliestDeliveryDate>
            <LatestDeliveryDate>07/08/2015 01:59:59</LatestDeliveryDate>
            <IsBusinessOrder>False</IsBusinessOrder>
            <IsPrime>False</IsPrime>
            <IsPremiumOrder>False</IsPremiumOrder>
        </Order>
        <Order>
            <AmazonOrderId>103-6846365-0842605</AmazonOrderId>
            <PurchaseDate>06/26/2015 11:06:16</PurchaseDate>
            <LastUpdateDate>06/26/2015 11:36:33</LastUpdateDate>
            <OrderStatus>Unshipped</OrderStatus>
            <FulfillmentChannel>MFN</FulfillmentChannel>
            <SalesChannel>Amazon.com</SalesChannel>
            <ShipServiceLevel>Std Cont US Street Addr</ShipServiceLevel>
            <ShippingAddress>
                <Name>bob dylan</Name>
                <AddressLine1>130 CHERRY LANE</AddressLine1>
                <City>MIAMI</City>
                <StateOrRegion>FLORIDA</StateOrRegion>
                <PostalCode>83420-9595</PostalCode>
                <CountryCode>US</CountryCode>
                <Phone>3076595976</Phone>
            </ShippingAddress>
            <OrderTotal>
                <CurrencyCode>USD</CurrencyCode>
                <Amount>43.00</Amount>
            </OrderTotal>
            <NumberOfItemsShipped>0</NumberOfItemsShipped>
            <NumberOfItemsUnshipped>1</NumberOfItemsUnshipped>
            <PaymentExecutionDetail />
            <PaymentMethod>Other</PaymentMethod>
            <MarketplaceId>ATVPDKIKX0DER</MarketplaceId>
            <BuyerEmail>jfkdkl@marketplace.amazon.com</BuyerEmail>
            <BuyerName>Matt dylan</BuyerName>
            <ShipmentServiceLevelCategory>Standard</ShipmentServiceLevelCategory>
            <ShippedByAmazonTFM>False</ShippedByAmazonTFM>
            <OrderType>StandardOrder</OrderType>
            <EarliestShipDate>06/29/2015 02:00:00</EarliestShipDate>
            <LatestShipDate>07/01/2015 01:59:59</LatestShipDate>
            <EarliestDeliveryDate>07/02/2015 02:00:00</EarliestDeliveryDate>
            <LatestDeliveryDate>07/09/2015 01:59:59</LatestDeliveryDate>
            <IsBusinessOrder>False</IsBusinessOrder>
            <IsPrime>False</IsPrime>
            <IsPremiumOrder>False</IsPremiumOrder>
        </Order>
        <Order>
            <AmazonOrderId>109-5667692-9305063</AmazonOrderId>
            <PurchaseDate>06/26/2015 15:33:15</PurchaseDate>
            <LastUpdateDate>06/26/2015 16:03:34</LastUpdateDate>
            <OrderStatus>Unshipped</OrderStatus>
            <FulfillmentChannel>MFN</FulfillmentChannel>
            <SalesChannel>Amazon.com</SalesChannel>
            <ShipServiceLevel>Std Cont US Street Addr</ShipServiceLevel>
            <ShippingAddress>
                <Name>susie orman</Name>
                <AddressLine1>2452 POEF CREEK RD</AddressLine1>
                <City>NEW YORK</City>
                <StateOrRegion>NY</StateOrRegion>
                <PostalCode>28538-8554</PostalCode>
                <CountryCode>US</CountryCode>
                <Phone>8782827332</Phone>
            </ShippingAddress>
            <OrderTotal>
                <CurrencyCode>USD</CurrencyCode>
                <Amount>135.00</Amount>
            </OrderTotal>
            <NumberOfItemsShipped>0</NumberOfItemsShipped>
            <NumberOfItemsUnshipped>1</NumberOfItemsUnshipped>
            <PaymentExecutionDetail />
            <PaymentMethod>Other</PaymentMethod>
            <MarketplaceId>ATVPDKIKX0DER</MarketplaceId>
            <BuyerEmail>sdafgeg@marketplace.amazon.com</BuyerEmail>
            <BuyerName>Susie orman</BuyerName>
            <ShipmentServiceLevelCategory>Standard</ShipmentServiceLevelCategory>
            <ShippedByAmazonTFM>False</ShippedByAmazonTFM>
            <OrderType>StandardOrder</OrderType>
            <EarliestShipDate>06/29/2015 02:00:00</EarliestShipDate>
            <LatestShipDate>07/01/2015 01:59:59</LatestShipDate>
            <EarliestDeliveryDate>07/02/2015 02:00:00</EarliestDeliveryDate>
            <LatestDeliveryDate>07/09/2015 01:59:59</LatestDeliveryDate>
            <IsBusinessOrder>False</IsBusinessOrder>
            <IsPrime>False</IsPrime>
            <IsPremiumOrder>False</IsPremiumOrder>
        </Order>
    </Orders>
</ListOrdersResult>
<ResponseMetadata>
    <RequestId>519992f0-701c-4211-9f3d-3c73b520c626</RequestId>
</ResponseMetadata>

Then this is my code for parsing it and storing it in my excel file:

using System;
using System.IO;
using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;
using System.Linq;
using System.Xml.Linq;
using System.Collections;
using System.Collections.Generic;


class Program
{
    static void Main()
    {
        Parse p = new Parse();
        file_create f = new file_create();

        ArrayList collection = p.Names("Name");
        ArrayList collection2 = p.Names("AddressLine1");
        ArrayList collection3 = p.Names("AddressLine2");
        ArrayList collection4 = p.Names("City");
        ArrayList collection5 = p.Names("StateOrRegion");

        string[] myArray = (string[])collection.ToArray(typeof(string));
        string[] myArray2 = (string[])collection2.ToArray(typeof(string));
        string[] myArray3 = (string[])collection3.ToArray(typeof(string));
        string[] myArray4 = (string[])collection4.ToArray(typeof(string));
        string[] myArray5 = (string[])collection5.ToArray(typeof(string));

        for (int i = 0; i < myArray.Length; i++)
        {
            myArray[i] = myArray[i].ToUpper();
        }

    /*    foreach(string i in myArray)
        {
           int count = 0;
           count++;        
           Console.WriteLine("Name " + count + " is: " + i);
        }

        foreach (string i in myArray2)
        {
            int count = 0;
            count++;
            Console.WriteLine("Name " + count + " is: " + i);
        }
*/
        int count = 0;
        foreach (string i in myArray3)
        {
            count++;
            Console.WriteLine("Name " + count + " is: " + i);
        }
        Console.ReadLine();

        f.Excel_Create(myArray, myArray2, myArray3, myArray4, myArray5);

        //  string uppername = name.ToUpper();
        //  f.Excel_Create(uppername);

    }

    public class Parse
    {
      public ArrayList Names(string a)
        { 
            var filepath = Path.Combine(Directory.GetCurrentDirectory(), "Perls.xml");
        XmlDocument xmlDoc = new XmlDocument();
        ArrayList collection = new ArrayList();
        ArrayList adcollection = new ArrayList();


        if (File.Exists(filepath))
        {
            xmlDoc.Load(filepath);

            switch (a)
            {
                case "Name":

                    XmlNodeList names = xmlDoc.GetElementsByTagName("Name");

                    foreach (XmlNode node in names)
                    {
                        XmlElement namesElement = (XmlElement)node;

                        collection.Add(namesElement.InnerText);
                    }
                    break;
                case "AddressLine1":

                    XmlNodeList addressline1 = xmlDoc.GetElementsByTagName("AddressLine1");

                    foreach (XmlNode node in addressline1)
                    {
                        XmlElement namesElement = (XmlElement)node;

                        collection.Add(namesElement.InnerText);
                    }
                    break;

                case "AddressLine2":

                    XmlNodeList addressline2 = xmlDoc.GetElementsByTagName("AddressLine2");

                    foreach (XmlNode node in addressline2)
                    {
                        XmlElement namesElement = (XmlElement)node;

                        collection.Add(namesElement.InnerText);
                    }
                    break;

                case "City":

                    XmlNodeList City = xmlDoc.GetElementsByTagName("City");

                    foreach (XmlNode node in City)
                    {
                        XmlElement namesElement = (XmlElement)node;

                        collection.Add(namesElement.InnerText);
                    }
                    break;

                case "StateOrRegion":

                    XmlNodeList State = xmlDoc.GetElementsByTagName("StateOrRegion");

                    foreach (XmlNode node in State)
                    {
                        XmlElement namesElement = (XmlElement)node;

                        collection.Add(namesElement.InnerText);
                    }

                    break;                   
            }

        }
        return collection;
     }
  }

    public class file_create
    {
        public void Excel_Create(string[] a, string[] b, string[] c, string[] d, string[] e)
        {
            var mySheet = Path.Combine(Directory.GetCurrentDirectory(), "Isopropanol - Tech Grade.xlsx");
            Excel.Application xlApp = new Excel.Application();
            xlApp.Visible = true;


            if (xlApp == null)
            {
                Console.WriteLine("Excel is not properly installed!!");
                return;
            }

            try
            {
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(mySheet);
                Excel.Sheets xlSheets = xlWorkbook.Worksheets;

                string currentSheet = "Invoice";
                Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlSheets.get_Item(currentSheet);

                int num = 10106;

                for (int i = 0; i < a.Length; i++)
                {
                    Excel.Range invoicenum = (Excel.Range)xlWorksheet.get_Range("NO", "NO");
                    invoicenum.Value2 = "M" + num;

                    num++;

                    Excel.Range xlCell = (Excel.Range)xlWorksheet.get_Range("data5", "data5");
                    xlCell.Value2 = a[i];
                    Excel.Range xlCell2 = (Excel.Range)xlWorksheet.get_Range("E13", "E13");
                    xlCell2.Value2 = b[i];
                    if(c[i] != null)
                    {
                        Excel.Range xlCell3 = (Excel.Range)xlWorksheet.get_Range("E14", "E14");
                        xlCell3.Value2 = c[i];

                        Excel.Range xlCell4 = (Excel.Range)xlWorksheet.get_Range("E15", "E15");
                        xlCell4.Value2 = d[i] + ", " + e[i];
                    }

                }
                xlWorksheet.PrintOutEx();

            }
            catch (Exception ex)
            {
                xlApp.Quit();

            }
        }
    }
}

if(c[i] != null) Whenever this test fails, I get an error. I believe it is because when I stored the AddressLine2 node it doesn't create the array the same size as the rest of my arrays since there is only one customer with a 2nd address.

Anyways and help is appreciated.

UPDATED CODE:

using System;
using System.IO;
using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;
using System.Linq;
using System.Xml.Linq;
using System.Collections;
using System.Collections.Generic;
using System.Net;
using System.Runtime.InteropServices;


class Program
{
    static void Main()
    {

        var path = Path.Combine(Directory.GetCurrentDirectory(), "perls.xml");

        var file = XDocument.Load(path);

        string xml = file.ToString();

        var doc = XDocument.Parse(xml);

        var addresses = Addresses(doc);

        WritetoExcel(addresses);
    }


        private static IEnumerable<Address> Addresses(XContainer doc)
    {
        XNamespace ns = "https://mws.amazonservices.com/Orders/2013-09-01";

        return from address in doc.Descendants(ns + "ShippingAddress")
               select new Address
               {
                   Name = (string) address.Element(ns + "Name"),
                   AddressLine1 = (string) address.Element(ns + "AddressLine1"),
                   AddressLine2 = (string) address.Element(ns + "AddressLine2"),
                   City = (string) address.Element(ns + "City"),
                   State = (string) address.Element(ns + "StateOrRegion")
               };
    }

        private static void WritetoExcel(IEnumerable<Address> addresses)
        {
            var mySheet = Path.Combine(Directory.GetCurrentDirectory(), "Isopropanol - Tech Grade.xlsx");
            Excel.Application xlApp = new Excel.Application();
            xlApp.Visible = true;


            if (xlApp == null)
            {
                Console.WriteLine("Excel is not properly installed!!");
                return;
            }

            try
            {
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(mySheet);
                Excel.Sheets xlSheets = xlWorkbook.Worksheets;

                string currentSheet = "Invoice";
                Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlSheets.get_Item(currentSheet);

                int num = 10106;

                foreach(var address in addresses)
                {

                    Excel.Range invoicenum = (Excel.Range)xlWorksheet.get_Range("NO", "NO");
                    invoicenum.Value2 = "M" + num;

                    num++;

                    Excel.Range xlCell = (Excel.Range)xlWorksheet.get_Range("data5", "data5");
                  //  Excel.Range c_xlCell = (Excel.Range)xlWorksheet.get_Range("");
                    xlCell.Value2 = address.Name.ToUpper();
                   // xlCell.Copy();


                    Excel.Range xlCell2 = (Excel.Range)xlWorksheet.get_Range("E13", "E13");
                    string a = WebUtility.HtmlDecode(address.AddressLine1);
                    xlCell2.Value2 = a;

                    Excel.Range xlCell3 = (Excel.Range)xlWorksheet.get_Range("E14", "E14");
                    Excel.Range xlCell4 = (Excel.Range)xlWorksheet.get_Range("E15", "E15");

                    if (address.AddressLine2 != null)
                    {

                        xlCell3.Value2 = address.AddressLine2;
                        xlCell4.Value2 = address.City + ", " + address.State;

                    }

                    else
                    {
                        xlCell3.Value2 = address.City + ", " + address.State;
                        xlCell4.Value2 = "";
                    }

                    Excel.Range sourceRange = (Excel.Range)xlWorksheet.get_Range("data5", "E15");
                    Excel.Range destinationRange = (Excel.Range)xlWorksheet.get_Range("J12", "J15");

                    sourceRange.Copy(Type.Missing);
                    destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValuesAndNumberFormats, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
                }


                GC.Collect();
                GC.WaitForPendingFinalizers();

                Marshal.FinalReleaseComObject(xlSheets);
                Marshal.FinalReleaseComObject(xlWorksheet);


                xlWorkbook.SaveAs("Isopropanol - Tech Grade.xlsx", Excel.XlFileFormat.xlWorkbookDefault);
                xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
                Marshal.FinalReleaseComObject(xlWorkbook);

                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp);

// xlWorksheet.PrintOutEx();
            }
            catch (Exception ex)
            {
                xlApp.Quit();

            }         
        }
    }

    public class Address
    {
        public string Name { get; set; }
        public string AddressLine1 { get; set; }
        public string AddressLine2 { get; set; }
        public string City { get; set; }
        public string State { get; set; }
    }

[Never use 2 dots with com objects](http://stackoverflow.com/a/158752/5045688). Don't forget release com objects. — Alexander Petrov, Jul 01 '15 at 21:44
Thanks alex.b. First whenever the first element is copied the strange text & is included. Is there a way to ignore these characters? I guess it is xml formatting code? The first iteration through the loop in the method Excel_Create is fine and the correct values are printed the if statement is encountered and evaluated as a true, and the statements are correctly executed. — Daniel Taki, Jul 01 '15 at 23:27
However, the 2nd time through the loop there isn't a value in array c[1] and an error is thrown. The error is: Exception:Thrown: "Index was outside the bounds of the array." (System.IndexOutOfRangeException) A System.IndexOutOfRangeException was thrown: "Index was outside the bounds of the array." Time: 7/1/2015 6:29:14 PM Thread:[13248] — Daniel Taki, Jul 01 '15 at 23:30

Charles Mager · Accepted Answer · 2015-07-06T09:34:22.407

You are correct that AddressLine2 is your issue, as only one of your customers has a second address line. You would need to ensure you return a blank line for each address that doesn't contain this element (otherwise you won't know which AddressLine2 related to which address) - this isn't terribly straightforward given how you are reading the XML.

Writing code in this way is prone to these kind of errors and some of the naming is hurting readability. For example, naming your collections/arrays as collection1, collection2 or a, b, c means you have to constantly scroll up and down to work out what they actually represent.

Naming them names, cities etc. would be an improvement, but even better than that, why not create an Address class?

public class Address
{
    public string Name { get; set; }
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string City { get; set; }
    public string StateOrRegion { get; set; }    
}

Then, instead of lots of copy/paste code in Parse, simply use LINQ to XML to create your Address objects.

private static IEnumerable<Address> Addresses(XContainer doc)
{
    XNamespace ns = "https://mws.amazonservices.com/Orders/2013-09-01";

    return from address in doc.Descendants(ns + "ShippingAddress")
           select new Address
           {
               Name = (string)address.Element(ns + "Name"),
               AddressLine1 = WebUtility.HtmlDecode((string)address.Element(ns + "AddressLine1")),
               AddressLine2 = (string)address.Element(ns + "AddressLine2"),
               City = (string)address.Element(ns + "City"),
               StateOrRegion = (string)address.Element(ns + "StateOrRegion"),
           };
}

Note you can use WebUtility.HtmlDecode to decode your double-escaped text (e.g. to decode THE BOTIQUE &amp; TAPES to THE BOTIQUE & TAPES). This may apply to all lines rather than just AddressLine1.

Then you can implement your Excel writing method with a signature something like this:

private static void WriteToExcel(IEnumerable<Address> addresses)

I'll leave the implementation as it's unclear what you're doing in Excel_Create - it seems to iterate all your customer addresses overwriting the values in the sheet each time, so I'm not sure it's correct as it stands.

You main method would be reduced to:

var path = Path.Combine(Directory.GetCurrentDirectory(), "Perls.xml");

var doc = XDocument.Load(path);

var addresses = Addresses(path);

WriteToExcel(addresses);

A working demo putting it all together is here: https://dotnetfiddle.net/k1AJ8H. This reads from an XML string and writes the addresses to the console

Awesome Charles! I"ll definitely be using this. Some of the concepts I'm still unfamiliar with but I literally hand copied everything you wrote. Going to read up some more so I don't bother you with more questions if I can find them out with a little googling. But for the excel_create method, I wanted the code to iterate over all the customer addresses and overwrite the values, however, I put the `xlWorksheet.PrintOutEx();` outside the loop while I was testing it. I didn't want to be printing out a bunch of invoices every time and forgot to put it back in when I posted it here. — Daniel Taki, Jul 02 '15 at 15:40
Nice, it works well Charles! However, in the 1st AddressLine1 the text is `THE BOTIQUE & TAPES`. Did you manually remove the `&amp` from the name? If not, how do you create an exception for these characters so that the enumerator doesn't include them. — Daniel Taki, Jul 06 '15 at 00:41
I removed from the dotnetfiddle demo as they seemed to have an issue in how this was saved and shared (basically, it worked when I used it but when you clicked the link it changed the XML by unescaping it). The data you have appears to have been escaped twice - `&` will be escaped to `&`, and then when written to XML it's being escaped again to `&amp`. Reading using LINQ to XML will get you back to `&`, then you could use `WebUtility.HtmlDecode` to get back to `&`. I'll update. — Charles Mager, Jul 06 '15 at 09:27
Thanks! The WebUtility.HtmlDecode method worked perfectly. I stored the address.AdressLine1 into a string and then called the HtmlDecode method on it. I put my updated code back in my original question. This is getting fun! Now I want to change the product description within my invoice based on a different set of XML data I will receive from amazon. This data will give me the `product title` and from there I want to generate a different set of info for the product description that my invoice contains. — Daniel Taki, Jul 06 '15 at 17:57
I also want to be able to create an executable file on my desktop that will launch a windows web form and ask the user for a starting invoice number then another button to fetch invoices when pressed. — Daniel Taki, Jul 06 '15 at 17:58
I'd suggest asking a new question when you have a specific problem. At the moment, that sounds a little too broad for the SO format. — Charles Mager, Jul 06 '15 at 17:59
Ok thanks Charles! I'll need to hack away at it a bit before I post a specific question. Appreciate it! — Daniel Taki, Jul 06 '15 at 19:23

Parsing XML file for element nodes then storing text in different arrays for loading into Excel

1 Answers1