c# - Parsing XML file for element nodes then storing text in different arrays for loading into Excel -
i want parse xml file receive amazon, take data , input custom invoice in excel , print.
i've completed of task, i'm running difficulties when customer has 2 parts address component such <adressline1> , <addressline2> field, 2 array's don't equal , throws error in loop in excel_create when try write values excel cell.
here's 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 street addr</shipservicelevel> <shippingaddress> <name>dan hendricks</name> <addressline1>the botique &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 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 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 code parsing , storing in 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)); (int = 0; < myarray.length; i++) { myarray[i] = myarray[i].toupper(); } /* foreach(string in myarray) { int count = 0; count++; console.writeline("name " + count + " is: " + i); } foreach (string in myarray2) { int count = 0; count++; console.writeline("name " + count + " is: " + i); } */ int count = 0; foreach (string 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 not 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; (int = 0; < 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 test fails, error. believe because when stored addressline2 node doesn't create array same size rest of arrays since there 1 customer 2nd address.
anyways , 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 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 not 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 = 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; } }
you correct addressline2 issue, 1 of customers has second address line. need ensure return blank line each address doesn't contain element (otherwise won't know addressline2 related address) - isn't terribly straightforward given how reading xml.
writing code in way prone these kind of errors , of naming hurting readability. example, naming collections/arrays collection1, collection2 or a, b, c means have scroll , down work out represent.
naming them names, cities etc. improvement, better that, why not create 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, use linq xml create address objects.
private static ienumerable<address> addresses(xcontainer doc) { xnamespace ns = "https://mws.amazonservices.com/orders/2013-09-01"; return 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 can use webutility.htmldecode decode double-escaped text (e.g. decode the botique &amp; tapes the botique & tapes). may apply lines rather addressline1.
then can implement excel writing method signature this:
private static void writetoexcel(ienumerable<address> addresses) i'll leave implementation it's unclear you're doing in excel_create - seems iterate customer addresses overwriting values in sheet each time, i'm not sure it's correct stands.
you main method 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 here: https://dotnetfiddle.net/k1aj8h. reads xml string , writes addresses console
Comments
Post a Comment