i've coded program in vb.net has allowed me convert excel file xml using oledb strings, read datatable, dataset, , lastly converted dataset xml, can saved file.
now i'm stumped on how can xml save elements in specific way, purchase order?
all of current code , screenshot of excel file below. appreciated.
how xml file should if code worked:
<?xml version="1.0" encoding="ascii" standalone="yes"?> <customerpurchaseorder xmlns="http://www.dummysite.com/"> <customerpurchaseorderfile> <firstname>john</firstname> <lastname>smith</lastname> <orderdate>2015-12-11</orderdate> <specialinstructions>leave @ front door</specialinstructions> <lineitems> <lineitem> <itemdescription>brown shirt</itemdescription> <qty>1</qty> <price>$12.99</price> </lineitem> <lineitem> <itemdescription>black shoes pair</itemdescription> <qty>1</qty> <price>$45.89</price> </lineitem> <lineitem> <itemdescription>oranges</itemdescription> <qty>5</qty> <price>$8.99</price> </lineitem> </lineitems> <firstname>lisa</firstname> <lastname>lane</lastname> <orderdate>2016-01-12</orderdate> <specialinstructions /> <lineitems> <lineitem> <itemdescription>wheat bread loaf</itemdescription> <qty>3</qty> <price>$5.99</price> </lineitem> <lineitem> <itemdescription>tv samsung 40"</itemdescription> <qty>1</qty> <price> $539.99</price> </lineitem> </lineitems> </customerpurchaseorderfile> </customerpurchaseorder> what - not want:
<?xml version="1.0" standalone="yes"?> <customerpurchaseorder xmlns="http://www.dummysite.com"> <customerpurchaseorderfile> <firstname>john</firstname> <lastname>smith</lastname> <orderdate>2015-12-11t00:00:00-08:00</orderdate> <specialinstructions>leave @ front door</specialinstructions> <itemdescription>brown shirt</itemdescription> <qty>1</qty> <price>12.99</price> </customerpurchaseorderfile> <customerpurchaseorderfile> <itemdescription>black shoes pair</itemdescription> <qty>1</qty> <price>45.89</price> </customerpurchaseorderfile> <customerpurchaseorderfile> <itemdescription>oranges</itemdescription> <qty>5</qty> <price>8.99</price> </customerpurchaseorderfile> <customerpurchaseorderfile> <firstname>lisa</firstname> <lastname>lane</lastname> <orderdate>2016-01-12t00:00:00-08:00</orderdate> <itemdescription>wheat bread loaf</itemdescription> <qty>3</qty> <price>5.99</price> </customerpurchaseorderfile> <customerpurchaseorderfile> <itemdescription>tv samsung 40"</itemdescription> <qty>1</qty> <price>539.99</price> </customerpurchaseorderfile> </customerpurchaseorder> my vb.net code
imports system.data.oledb public class form1 dim myds dataset dim mydt datatable = new datatable("customerpurchaseorderfile") dim myfilepath string() 'load excel file button private sub loadfilebtn_click(sender object, e eventargs) handles loadfilebtn.click 'choose excel file 'file dialog box properties openfiledialog1.filter = "excel files (*.xls, *.xlsx)|*.xls;*.xlsx" openfiledialog1.filterindex = 2 openfiledialog1.initialdirectory = "c:\" savexmlbtn.enabled = false dim checkopendialog dialogresult = openfiledialog1.showdialog() dim myconnection string, excelconn oledbconnection dim myadapter oledbdataadapter 'import file using oledb connections datatable -> dataset -> xml try 'disable save button if no file loaded if checkopendialog = dialogresult.cancel savexmlbtn.enabled = false elseif checkopendialog = dialogresult.none savexmlbtn.enabled = false elseif checkopendialog = dialogresult.ok myfilepath = openfiledialog1.filenames myconnection = "provider=microsoft.ace.oledb.12.0;data source=" + myfilepath(0) + ";extended properties=""excel 12.0;hdr=yes;imex=1;""" excelconn = new oledbconnection(myconnection) excelconn.open() myadapter = new oledbdataadapter("select * [sheet1$]", excelconn) mydt = new datatable() myds = new dataset() myds.tables.add(mydt) myds.merge(mydt) myds.datasetname = "customerpurchaseorder" myds.namespace = "http://www.dummysite.com" myds.prefix = "" myadapter.fill(myds, "customerpurchaseorderfile") myds.acceptchanges() excelconn.close() 'dataset -> string storage dim storexml string = myds.getxml 'preview in text box xmlpreviewbox.text = storexml end if 'save dataset string catch ex exception msgbox(ex.tostring) if myfilepath isnot nothing msgbox("preview of xml file loaded.") savexmlbtn.enabled = true end if end try end sub 'save converted excel xml file private sub savexmlbtn_click(sender object, e eventargs) handles savexmlbtn.click 'file dialog box properties savefiledialog1.filter = "xml files (*.xml)|*.xml" savefiledialog1.filterindex = 1 savefiledialog1.initialdirectory = "c:\" savexmlbtn.enabled = false savefiledialog1.showdialog() myfilepath = savefiledialog1.filenames try if savefiledialog1.filename <> "" 'myds.writexml(myfilepath, xmlwritemode.ignoreschema) myds.writexml(myfilepath(0)) 'works , above works same end if catch ex exception msgbox(ex.tostring) msgbox("xml file saved successfully.") end try end sub end class 
it looks there 3 distinct parts. first group of po's, define this
dim protopurchaseorders xelement = <purchaseorders> </purchaseorders> the next part po
dim protopo xelement = <po> <firstname></firstname> <lastame></lastame> <orderdate></orderdate> <specialinstructions></specialinstructions> <items></items> </po> and have line items added items in po.
dim protoitem xelement = <item> <description></description> <qty></qty> <price></price> </item> these prototypes can used create want. here code simulates group of 2 po's line items. note prototypes used in creation of new xelements.
dim orderfile new xelement(protopurchaseorders) x integer = 1 2 'simulate 2 customer po's dim apo new xelement(protopo) 'create po , fill in blanks apo.<firstname>.value = x.tostring apo.<lastame>.value = x.tostring apo.<orderdate>.value = datetime.now.adddays(x).toshortdatestring apo.<specialinstructions>.value = "si " & x.tostring integer = x 3 'create line items , fill in blanks dim item new xelement(protoitem) item.<description>.value = "desc " & i.tostring item.<qty>.value = i.tostring item.<price>.value = i.tostring("c2") apo.<items>.lastordefault.add(item) 'add item po next orderfile.add(apo) 'add po orders next 'orderfile.save("path goes here") this adds 1 level of abstraction(po) wasn't in 'what want', think needed.
the output above is
<purchaseorders> <po> <firstname>1</firstname> <lastame>1</lastame> <orderdate>1/30/2016</orderdate> <specialinstructions>si 1</specialinstructions> <items> <item> <description>desc 1</description> <qty>1</qty> <price>$1.00</price> </item> <item> <description>desc 2</description> <qty>2</qty> <price>$2.00</price> </item> <item> <description>desc 3</description> <qty>3</qty> <price>$3.00</price> </item> </items> </po> <po> <firstname>2</firstname> <lastame>2</lastame> <orderdate>1/31/2016</orderdate> <specialinstructions>si 2</specialinstructions> <items> <item> <description>desc 2</description> <qty>2</qty> <price>$2.00</price> </item> <item> <description>desc 3</description> <qty>3</qty> <price>$3.00</price> </item> </items> </po> </purchaseorders> 
Comments
Post a Comment