VB.net数据集到XML采购订单 [英] VB.net Dataset to XML Purchase Order
本文介绍了VB.net数据集到XML采购订单的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
现在我很难以获取XML以特定方式保存元素，基本上看起来像采购订单？
我目前的代码和我的excel文件的截图都在下面。任何帮助都不胜感激。
如果我的代码工作，XML文件如何应该：
<？xml version =1.0encoding =ASCIIstandalone =yes？>
< CustomerPurchaseOrder xmlns =http://www.dummysite.com/>
< CustomerPurchaseOrderFile>
< FirstName> John< / FirstName>
< LastName> Smith< / LastName>
< OrderDate> 2015-12-11< / OrderDate>
< SpecialInstructions>离开门前< / SpecialInstructions>
< LineItems>
< LineItem>
< ItemDescription> Brown Shirt< / ItemDescription>
< QTY> 1< / QTY>
< Price> $ 12.99< / Price>
< / LineItem>
< LineItem>
< ItemDescription>黑色鞋子对< / 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>小麦面包< / 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>
我目前获得什么 - 不是我想要的：
<？xml version =1.0standalone =yes？>
< CustomerPurchaseOrder xmlns =http://www.dummysite.com>
< CustomerPurchaseOrderFile>
< FirstName> John< / FirstName>
< LastName> Smith< / LastName>
< OrderDate> 2015-12-11T00：00：00-08：00< / OrderDate>
< SpecialInstructions>离开前面< / SpecialInstructions>
< ItemDescription> Brown Shirt< / ItemDescription>
< QTY> 1< / QTY>
< Price> 12.99< / Price>
< / CustomerPur chaseOrderFile>
< CustomerPurchaseOrderFile>
< ItemDescription>黑色鞋子对< / 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>小麦面包< / ItemDescription>
< QTY> 3< / QTY>
< Price> 5.99< / Price>
< / CustomerPurchaseOrderFile>
< CustomerPurchaseOrderFile>
< ItemDescription> TV Samsung 40< / ItemDescription>
< QTY> 1< / QTY>
< Price> 539.99< / Price>
& / CustomerPurchaseOrderFile>
< / CustomerPurchaseOrder>
我的VB.Net代码
导入System.Data.OleDb
公共类Form1
Dim myDS As DataSet
Dim myDT As DataTable = New DataTable（CustomerPurchaseOrderFile）
Dim myFilePath As String（）
'加载Excel文件按钮
Private Sub loadFileBtn_Click（sender As Object，e As EventArgs）处理loadFileBtn.Click
'选择excel文件
'文件对话框属性
OpenFileDialog1.Filter =Excel Files（* .xls，* .xlsx）| * .xls; *。xlsx
OpenFileDialog1.FilterIndex = 2
OpenFileDialog1.InitialDirectory =C：\
saveXMLBtn.Enabled = False
Dim checkOpenDialog作为Dialo gResult = OpenFileDialog1.ShowDialog（）
Dim myConnection As String，excelConn As OleDbConnection
Dim myAdapter As OleDbDataAdapter
'import file using OleDB connections into datatable - >数据集 - > xml
如果没有文件加载，尝试
'禁用保存按钮
如果checkOpenDialog = DialogResult.Cancel然后
saveXMLBtn.Enabled = False
ElseIf checkOpenDialog = DialogResult.None Then
saveXMLBtn.Enabled = False
ElseIf checkOpenDialog = DialogResult.OK然后
myFilePath = OpenFileDialog1.FileNames
myConnection =Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + myFilePath（0）+;扩展属性=Excel 12.0; HDR =是; IMEX = 1;
excelConn =新的OleDbConnection（myConnection）
excelConn.Open（）
myAdapter = New OleDbDataAdapter（select * from [Sheet1 $]，excelConn）
myDT = New DataTable（）
myDS = New DataSet（）
myDS.Tables.Add（myDT）
myDS.Merge（myDT）
myDS.DataSetName =CustomerP urchaseOrder
myDS.Namespace =http://www.dummysite.com
myDS.Prefix =
myAdapter.Fill（myDS，CustomerPurchaseOrderFile）
myDS .AcceptChanges（）
excelConn.Close（）
'dataset - >字符串存储
Dim storeXML As String = myDS.GetXml
'在文本框中预览
xmlPreviewBox.Text = storeXML
End If
'将数据集保存到字符串
Catch ex As Exception
MsgBox（ex.ToString）
最后
如果myFilePath IsNot Nothing然后
MsgBox（预装XML文件加载）
saveXMLBtn。 Enabled = True
End If
End尝试
End Sub
'将转换的Excel保存到XML文件
Private Sub saveXMLBtn_Click（sender As Object，e As EventArgs）处理saveXMLBtn.Click
'文件对话框属性
SaveFileDialog1.Filter =XML文件（* .xml）| * .xml
SaveFileDialog1.FilterIndex = 1
SaveFileDialog1 .InitialDirectory =C：\
saveXMLBtn.Enabled = False
SaveFileDialog1.ShowDialog（）
myFilePath = SaveFileDialog1.FileNames
尝试
如果SaveFileDialog1.FileName<> 然后
'myDS.WriteXml（myFilePath，XmlWriteMode.IgnoreSchema）
myDS.WriteXml（myFilePath（0））'的作品和上面的工作相同
结束If
Catch ex作为异常
MsgBox（ex.ToString）
最后
MsgBox（XML文件成功保存）
结束尝试
End Sub
结束类
解决方案
看起来有三个不同的部分。第一个是一组PO，所以定义它像这样
Dim protoPurchaseOrders As XElement =< PurchaseOrders>
< / PurchaseOrders>
下一部分是一个PO
Dim protoPO As XElement =< PO>
< firstname>< / firstname>
< lastame>< / lastame>
< orderdate>< / orderdate>
< specialinstructions>< / specialinstructions>
< items>< / items>
< / PO>
最后，您将有添加到PO中的项目的订单项。
Dim protoitem As XElement =< item>
< description>< / description>
< QTY>< / QTY>
< price>< / price>
< / item>
这些原型可用于创建您想要的。这是一些代码，它模拟了一组两个PO和一些订单项。 请注意，原型仅用于创建新的元素。
Dim orderfile As New XElement （protoPurchaseOrders）
对于x As Integer = 1到2'模拟两个客户PO的
Dim aPO作为新XElement（protoPO）'创建一个PO并填写空白
aPO。< firstname> ; .Value = x.ToString
aPO。< lastame> .Value = x.ToString
aPO。< orderdate> .Value = DateTime.Now.AddDays（x）.ToShortDateString
aPO。< specialinstructions> .Value =SI& x.ToString
对于i As Integer = x To 3'创建行项目并填写空白
Dim item As 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）'将项目添加到PO
下一个
orderfile.Add（aPO）'向订单添加PO
下一个
'orderfile.Save（path goes here ）
这增加了一个不在你想要的 '，但我认为是需要的。
上面的输出是
<&个PurchaseOrders GT;
< 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>
I've coded a program in VB.net that has allowed me to convert an Excel file to XML using OleDB strings, read it into a Datatable, then into a Dataset, and lastly converted the Dataset to XML, which can be saved into another file.
Now I'm stumped on how can I get the XML to save the elements in a specific way, basically to look like a purchase order?
All of my current code and screenshot of my excel file is below. Any help is appreciated.
How the XML File Should Look If My 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 at 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 I Currently Get - Not What I 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 at 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 As DataSet
Dim myDT As DataTable = New DataTable("CustomerPurchaseOrderFile")
Dim myFilePath As String()
'Load Excel File Button
Private Sub loadFileBtn_Click(sender As Object, e As 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 As DialogResult = OpenFileDialog1.ShowDialog()
Dim myConnection As String, excelConn As OleDbConnection
Dim myAdapter As OleDbDataAdapter
'import file using OleDB connections into datatable -> dataset -> xml
Try
'disable save button if no file is loaded
If checkOpenDialog = DialogResult.Cancel Then
saveXMLBtn.Enabled = False
ElseIf checkOpenDialog = DialogResult.None Then
saveXMLBtn.Enabled = False
ElseIf checkOpenDialog = DialogResult.OK Then
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 * from [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 As String = myDS.GetXml
'preview in text box
xmlPreviewBox.Text = storeXML
End If
'save dataset to string
Catch ex As Exception
MsgBox(ex.ToString)
Finally
If myFilePath IsNot Nothing Then
MsgBox("Preview of XML File Loaded.")
saveXMLBtn.Enabled = True
End If
End Try
End Sub
'Save Converted Excel to XML File
Private Sub saveXMLBtn_Click(sender As Object, e As 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 <> "" Then
'myDS.WriteXml(myFilePath, XmlWriteMode.IgnoreSchema)
myDS.WriteXml(myFilePath(0)) 'works and above works the same
End If
Catch ex As Exception
MsgBox(ex.ToString)
Finally
MsgBox("XML File Saved Successfully.")
End Try
End Sub
End Class
解决方案
It looks like there are three distinct parts. The first is a group of PO's, so define it like this
Dim protoPurchaseOrders As XElement = <PurchaseOrders>
</PurchaseOrders>
The next part is a PO
Dim protoPO As XElement = <PO>
<firstname></firstname>
<lastame></lastame>
<orderdate></orderdate>
<specialinstructions></specialinstructions>
<items></items>
</PO>
and finally you have the line items that will be added to the items in the PO.
Dim protoitem As XElement = <item>
<description></description>
<QTY></QTY>
<price></price>
</item>
These prototypes can be used to create what you want. Here is some code that simulates a group of two PO's with some line items. Note that the prototypes are ONLY used in the creation of new xelements.
Dim orderfile As New XElement(protoPurchaseOrders)
For x As Integer = 1 To 2 'simulate two customer PO's
Dim aPO As New XElement(protoPO) 'create a PO and fill in the 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
For i As Integer = x To 3 'create line items and fill in the blanks
Dim item As 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 to PO
Next
orderfile.Add(aPO) 'add PO to orders
Next
'orderfile.Save("path goes here")
This adds one level of abstraction(PO) that wasn't in the 'what you want', but I think it is needed.
The output from the 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>
这篇关于VB.net数据集到XML采购订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
