vb.net xml 解析
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity)
自1999年成立以来,ECB网站就以XML Feed的形式提供主要货币的汇率。 文件具有以下格式(为简洁起见,将其简化)
<gesmes:Envelope>
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time="2011-09-09">
<Cube currency="USD" rate="1.3817"/>
<Cube currency="JPY" rate="107.48"/>
<Cube currency="BGN" rate="1.9558"/>
<Cube currency="CZK" rate="24.430"/>
<Cube currency="DKK" rate="7.4473"/>
<Cube currency="GBP" rate="0.86590"/>
</Cube>
</Cube>
</gesmes:Envelope>
HERE (and updated once daily) that contain:
这里 (每天更新一次)包含:
1. The days FX rates
1.天汇率
2. The last 90 days' FX rates
2.最近90天的外汇汇率
3. All daily FX rates since 1999
3.自1999年以来的所有每日外汇汇率
The XML file provided is by no means a regular XML file, and the code provided in the developer section is PHP. After several days' research and search for simple code to parse the XML file from the ECB, it became clear that there needs to be a simpler way to parse these files.
所提供的XML文件绝不是常规XML文件,而在developer部分中提供的代码是PHP。 经过几天的研究并搜索了用于从ECB解析XML文件的简单代码,很明显,需要一种更简单的方法来解析这些文件。
I set out to resolve this by utilising the WebClient Class to download the files and parse them using the XMLTextReader Class.
我着手通过利用WebClient类下载文件并使用XMLTextReader类解析它们来解决此问题。
Firstly, I use the webclient's DownloadDataAsync method and add handlers for the progress changed (for monitoring rogress) and another handler for when the method completes. This is inside a Boolean function which requires an integer value (0,1 or 2) to select the file to download (daily, 90 day and since 1999 respectively). Heres the code:
首先,我使用webclient的DownloadDataAsync方法,并为更改的进度添加处理程序(用于监视进程),并为该方法完成时添加另一个处理程序。 这是布尔函数内部的函数,布尔函数需要一个整数值(0、1或2)来选择要下载的文件(分别为90天和1999年以来)。 这是代码:
Function GetECB_FX(ByVal XMLFile As Integer) As Boolean
If My.Computer.Network.IsAvailable Then
Dim xURI As String = Nothing
Select Case XMLFile
Case 0
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"
Case 1
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml"
Case 2
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml"
End Select
Try
Using qClient As New Net.WebClient
qClient.Headers.Add("Accept-Encoding", "gzip")
qClient.DownloadDataAsync(New Uri(xURI))
AddHandler qClient.DownloadProgressChanged, AddressOf FXDownloadProgressCallback
AddHandler qClient.DownloadDataCompleted, AddressOf FXDownloadComplete1
End Using
Catch ex As Exception
Label3.Text = "Error downloading FX Rates. " & Err.Number & ": " & Err.Description
Return False
End Try
Else
Label3.Text = "Please connect to the internet to download FX Rates."
Return False
End If
Return True
End Function
DownloadDataAsync method, it is non blocking and therefore will not freeze the UI while the download is taking place. Heres the code for the prgress handler.
Sub FXDownloadProgressCallback(ByVal sender As Object, ByVal e As Net.DownloadProgressChangedEventArgs)
xTimer.Interval = 10000
Dim xRec, xTot As Double
xRec = FormatNumber(e.BytesReceived / 1048576, 2)
xTot = FormatNumber(e.TotalBytesToReceive / 1048576, 2)
Label3.Text = "FX Rates download: " & e.ProgressPercentage & "% Completed. (" & xRec & "MB of " & xTot & "MB)"
End Sub
1. It parses the downloaded XML file.
1.解析下载的XML文件。
2. It sets up the datatable to hold the data and populates it.
2.它设置数据表来保存数据并填充它。
1. Parsing the XML file
1.解析XML文件
The DownloadDataAsync method downloads the specified resource as a Byte array and does not block the calling thread. The resource is then returned as the result in the DownloadDataCompletedEvent
DownloadDataAsync方法将指定的资源下载为Byte数组,并且不会阻塞调用线程。 然后,将资源作为结果返回到MemoryStream读取它,因此:
Using xReader As New XmlTextReader(New MemoryStream(e.Result))
dictionaries
词典
in parisng the XML. They are the xDic which holds the individual rates as the file is parsed. The key is the currency and the value is the rate to the EURO, and the FXDic whose key is the FX date and value is the xDic above.
在解析XML中。 它们是xDic ,它在解析文件时保存各个速率。 密钥是货币,价值是对欧元的汇率,而FXDic,其密钥是FX日期,值是
Due to the way the file is laid out, we use the XmlTextReader to read a node at a time using its Read method. The ONLY nodes of interest are name Cube and for our values, these node SHOULD have one or two attributes. We use Select to determine this and:
由于文件的布局方式,我们使用XmlTextReader通过其Read方法一次读取一个节点。 唯一感兴趣的节点是名称Cube ,对于我们的值,这些节点应具有一个或两个属性。 我们使用Select来确定并:
a) If ther is one node - This MUST contain the date. Parse the value of the date string and add it to our FXDic as a key, i.e
a)如果是一个节点-必须包含日期。 解析日期字符串的值并将其作为键添加到我们的FXDic中,即
xDate = Date.ParseExact(xReader.Value, "yyyy-MM-dd", CultureInfo.InvariantCulture)
FXDic.Add(xDate, Nothing)
b below), and also to reset the Dictionary for those values, and then move to the time attribute that holds the date i.e
b ),并为这些值重置字典,然后移至保存日期的时间属性,即
If xDic.Keys.Count > 0 Then FXDic.Item(xDate) = xDic
xDic = New SortedDictionary(Of String, Double)
xReader.MoveToAttribute("time")
currency attribute to get the ISO 4217 currency and funds name and code elements and get its value, then move to the rate attribute to get the FX rate, finally adding these to the xDic, i.e
货币属性以获取ISO 4217货币和资金名称及代码元素并获取其值,然后移至rate属性以获取FX汇率,最后将它们添加到
xReader.MoveToAttribute("currency")
Dim GCur = xReader.Value
xReader.MoveToAttribute("rate")
Dim GRate = xReader.Value
xDic.Add(GCur, GRate)
While xReader.Read()) we need to add the last xDic to the FXDic because after reading the last two attribute Code node, we never encounter another one attribute code node, thus
当使用xReader.Read()时,我们需要将最后一个
If xDic.Keys.Count > 0 Then FXDic.Item(xDate) = xDic
2. Setting up the DataTable and populating it.
2.设置并填充数据表。
First, I initialize a new list of string and add ALL the Keys in each xDic. This may seem futile but ensures that should the ECB include another currency in the future, this code still functions. Heres the code for that:
首先,我初始化一个新的字符串列表,并在每个
Dim HDList As New List(Of String)
Array.ForEach(FXDic.Keys.ToArray, Sub(x) If Not FXDic.Item(x) Is Nothing Then HDList.AddRange(FXDic.Item(x).Keys.ToArray))
Dim FXTable As DataTable = New DataTable
With FXTable
.TableName = "ECB_FXTabe"
.Columns.Add(New DataColumn("Date") With {.DataType = GetType(System.DateTime), .ColumnName = "Date", .Unique = True})
Array.ForEach(HDList.Distinct.OrderBy(Function(x) x).ToArray, Sub(x) .Columns.Add(New DataColumn(x) With {.DataType = GetType(System.Double), .DefaultValue = 1.0, .ColumnName = x}))
End With
NOTE:I use distinct items (and sort them) from the list to create the table columns, i.e HDList.Distinct.OrderBy(Fu
注意:我使用列表中的不同项(并对其进行排序)来创建表列,即HDList.Distinct.OrderBy(Fu nction(x)x) 。
Finally, it is a matter of populating the the datatable with our values, by first declaring a new datarow and then populating it with the values.
最后,首先声明一个新的数据行,然后使用值填充它,这就是用我们的值填充数据表的问题。
Dim xRow As DataRow
For Each q In FXDic.Keys 'q is the value for date column
xRow = FXTable.NewRow()
xRow.Item("Date") = q
Array.ForEach(FXDic.Item(q).Keys.ToArray, Sub(x) xRow.Item(x) = CDbl(FXDic.Item(q).Item(x)))
FXTable.Rows.Add(xRow)
Next
And here is the complete code listing.
这是完整的代码清单。
Function GetFX(Optional ByVal xURI As String = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml") As Boolean
If My.Computer.Network.IsAvailable Then
Select Case My.Settings.FXLink
Case 0
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"
Case 1
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml"
Case 2
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml"
End Select
Try
Using qClient As New Net.WebClient
qClient.Headers.Add("Accept-Encoding", "gzip")
qClient.DownloadDataAsync(New Uri(xURI))
AddHandler qClient.DownloadProgressChanged, AddressOf FXDownloadProgressCallback
AddHandler qClient.DownloadDataCompleted, AddressOf FXDownloadComplete1
End Using
Catch ex As Exception
Label3.Text = "Error downloading FX Rates. " & Err.Number & ": " & Err.Description
Return False
End Try
Else
Label3.Text = "Please connect to the internet to download FX Rates."
Return False
End If
Return True
End Function
Sub FXDownloadProgressCallback(ByVal sender As Object, ByVal e As Net.DownloadProgressChangedEventArgs)
xTimer.Interval = 10000
Dim xRec, xTot As Double
xRec = FormatNumber(e.BytesReceived / 1048576, 2)
xTot = FormatNumber(e.TotalBytesToReceive / 1048576, 2)
Label3.Text = "FX Rates download: " & e.ProgressPercentage & "% Completed. (" & xRec & "MB of " & xTot & "MB)"
End Sub
Sub FXDownloadComplete1(ByVal sender As Object, ByVal e As Net.DownloadDataCompletedEventArgs)
Dim FXDic As New Dictionary(Of Date, SortedDictionary(Of String, Double))
Dim xDic As New SortedDictionary(Of String, Double)
Dim xDate As Date = Nothing
Using xReader As New XmlTextReader(New MemoryStream(e.Result))
While xReader.Read()
If String.IsNullOrEmpty(xReader.Name) Then Continue While
If Not xReader.Name = "Cube" Then Continue While
Select Case xReader.AttributeCount
Case 1
If xDic.Keys.Count > 0 Then FXDic.Item(xDate) = xDic
xDic = New SortedDictionary(Of String, Double)
xReader.MoveToAttribute("time")
xDate = Date.ParseExact(xReader.Value, "yyyy-MM-dd", CultureInfo.InvariantCulture)
FXDic.Add(xDate, Nothing)
Case 2
xReader.MoveToAttribute("currency")
Dim GCur = xReader.Value
xReader.MoveToAttribute("rate")
Dim GRate = xReader.Value
xDic.Add(GCur, GRate)
End Select
xReader.MoveToNextAttribute()
End While
If xDic.Keys.Count > 0 Then FXDic.Item(xDate) = xDic
End Using
Dim HDList As New List(Of String)
Array.ForEach(FXDic.Keys.ToArray, Sub(x) If Not FXDic.Item(x) Is Nothing Then HDList.AddRange(FXDic.Item(x).Keys.ToArray))
Dim FXTable As New DataTable
With FXTable
.TableName = "ECB_FXTabe"
.Columns.Add(New DataColumn("Date") With {.DataType = GetType(System.DateTime), .ColumnName = "Date", .Unique = True})
Array.ForEach(HDList.Distinct.OrderBy(Function(x) x).ToArray, Sub(x) .Columns.Add(New DataColumn(x) With {.DataType = GetType(System.Double), .DefaultValue = 1.0, .ColumnName = x}))
End With
Dim xRow As DataRow
For Each q In FXDic.Keys 'q is the value for date column
xRow = FXTable.NewRow()
xRow.Item("Date") = q
Array.ForEach(FXDic.Item(q).Keys.ToArray, Sub(x) xRow.Item(x) = CDbl(FXDic.Item(q).Item(x)))
FXTable.Rows.Add(xRow)
Next
End Sub
Please note, depending on what you want to do with the ECB data, there are some copyright considerations. Using it as above presents no problems for your own use. However, if you intend to publish or distribute then you do need to be familiar and compliant with the ECB terms : http://www.ecb.europa.eu/home/html/disclaimer.en.html
请注意,根据您对ECB数据的处理方式,有一些版权注意事项。 如上所述使用它不会给您自己使用带来任何问题。 但是,如果您打算发布或分发,那么您确实需要熟悉并遵守ECB条款: http : //www.ecb.europa.eu/home/html/disclaimer.en.html
vb.net xml 解析