我们知道,用XML文件传输文件有很多好处,现在我们来做一个简单的小程序--一个省对应它的市在列表中显示出来
首先我们得制作一个窗体,具体布局如下图
首先我们得有一些基本信息,省市的基本信息
然后我们制作一个窗体,布局如运行效果图所示,其次我们得写两个函数,一个是读取XML文件的函数,将XML文件中(市)读取出来,一个是生成XML文件,将Excel表中的内容保存为XML文件(一个省对应一个XML文件),对应的在后期读取时,只要读取对应的省的XML文件我们就可以读取出它所有的市的内容
1.生成XML文件
写入XML文件
Private Sub WriteToXml(proName As String, cityName As String)
Dim xDoc As New DOMDocument60, Pro As IXMLDOMElement, City As IXMLDOMElement
If Len(Dir(ThisWorkbook.Path & "\temp\" & proName & ".xml")) = 0 Then '不存在这样的文件
Set Pro = xDoc.createElement("province") '创建根节点
xDoc.appendChild Pro '根节点加入到文档
Dim Pi As IXMLDOMProcessingInstruction
Set Pi = xDoc.createProcessingInstruction("xml", "version='1.0' encoding='utf-8'")
Call xDoc.InsertBefore(Pi, xDoc.ChildNodes(0))
Else
xDoc.Load ThisWorkbook.Path & "\temp\" & proName & ".xml" '加载文档
Set Pro = xDoc.DocumentElement '获取根节点
End If
Set City = xDoc.createElement("City") '创建“市”节点
City.Text = cityName
Pro.appendChild City '添加到根节点。
xDoc.Save ThisWorkbook.Path & "\temp\" & proName & ".xml" '保存XML文档
End Sub
2.读取XML文件
Private Function readfromxml(proName As String) As String()
Dim xDoc As New DOMDocument60, Pro As IXMLDOMElement, arrcity() As String
xDoc.Load ThisWorkbook.Path & "\temp\" & proName & ".xml" '打开XML文件
Set Pro = xDoc.DocumentElement '获取根节点
ReDim arrcity(Pro.ChildNodes.Length - 1) As String '确认数组的长度
Dim i As Integer
For i = 0 To Pro.ChildNodes.Length - 1
arrcity(i) = Pro.ChildNodes(i).Text
Next i
readfromxml = arrcity '返回函数值,返回数组
End Function
3.有了这两个基本的函数我们就可以在相应的事件中编写代码了,完整的具体代码如下
Option Explicit
'读取指定省份的城市,返回数据到一个数组
Private Function readfromxml(proName As String) As String()
Dim xDoc As New DOMDocument60, Pro As IXMLDOMElement, arrcity() As String
xDoc.Load ThisWorkbook.Path & "\temp\" & proName & ".xml" '打开XML文件
Set Pro = xDoc.DocumentElement '获取根节点
ReDim arrcity(Pro.ChildNodes.Length - 1) As String '确认数组的长度
Dim i As Integer
For i = 0 To Pro.ChildNodes.Length - 1
arrcity(i) = Pro.ChildNodes(i).Text
Next i
readfromxml = arrcity '返回函数值,返回数组
End Function
'写入XML文件
Private Sub WriteToXml(proName As String, cityName As String)
Dim xDoc As New DOMDocument60, Pro As IXMLDOMElement, City As IXMLDOMElement
If Len(Dir(ThisWorkbook.Path & "\temp\" & proName & ".xml")) = 0 Then '不存在这样的文件
Set Pro = xDoc.createElement("province") '创建根节点
xDoc.appendChild Pro '根节点加入到文档
Dim Pi As IXMLDOMProcessingInstruction
Set Pi = xDoc.createProcessingInstruction("xml", "version='1.0' encoding='utf-8'")
Call xDoc.InsertBefore(Pi, xDoc.ChildNodes(0))
Else
xDoc.Load ThisWorkbook.Path & "\temp\" & proName & ".xml" '加载文档
Set Pro = xDoc.DocumentElement '获取根节点
End If
Set City = xDoc.createElement("City") '创建“市”节点
City.Text = cityName
Pro.appendChild City '添加到根节点。
xDoc.Save ThisWorkbook.Path & "\temp\" & proName & ".xml" '保存XML文档
End Sub
'市列表的数据随着省份的变动而变动
Private Sub ComboBox1_Change()
ComboBox2.List = readfromxml(ComboBox1.Text)
End Sub
Private Sub CommandButton1_Click()
Dim i As Integer
For i = 2 To 309
WriteToXml Cells(i, 1).Value, Cells(i, 2).Value
Next i
End Sub
Private Sub UserForm_Initialize()'窗体加载事件
'用字典来集合省名称
Dim d As Object, i As Integer
Set d = CreateObject("scripting.dictionary")
For i = 2 To 309
d(Cells(i, 1).Value) = ""
Next i
ComboBox1.List = d.keys
End Sub
最后我们来看一下生成的XML文件
打开其中一个文件,会发现它是用子节点进行保存的