下图显示了4列
这个
出口
按钮打开一个弹出窗口,允许用户使用
转换
按钮
一旦用户单击
转换
按钮,将下面的xml数据生成到
默认.xml
<?xml version="1.0"?>
<data>
<student><id>1</id>
<name>Jad</name>
<age>25</age>
<mark>17</mark>
</student>
</data>
到目前为止,输出对我来说还不错,但是我希望添加更多功能,我尝试在用户按钮点击时动态添加一个“Mark”列,如下所示
一旦用户点击
添加标记
,因此在每个按钮上单击2个字段将出现
材料标记
),预期的excel表可能如下所示
<?xml version="1.0"?>
<data>
<student><id>1</id>
<name>Jad</name>
<age>25</age>
<materials>
<material>
<name>Maths</name>
<mark>17</marks>
</material>
<material>
<name>Physics</name>
<mark>18</marks>
</material>
</materials>
</student>
</data>
我用来生成XML文件的函数如下所示
Function fGenerateXML(rngData As Range, rootNodeName As String) As String
'===============================================================
' XML Tags
' Table
Const HEADER As String = "<?xml version=""1.0""?>"
Dim TAG_BEGIN As String
Dim TAG_END As String
Const NODE_DELIMITER As String = "/"
'===============================================================
Dim intColCount As Integer
Dim intRowCount As Integer
Dim intColCounter As Integer
Dim intRowCounter As Integer
Dim rngCell As Range
Dim strXML As String
' Initial table tag...
TAG_BEGIN = vbCrLf & "<" & rootNodeName & ">"
TAG_END = vbCrLf & "</" & rootNodeName & ">"
strXML = HEADER
strXML = strXML & TAG_BEGIN
With rngData
' Discover dimensions of the data we
' will be dealing with...
intColCount = .Columns.Count
intRowCount = .Rows.Count
Dim strColNames() As String
ReDim strColNames(intColCount)
' First Row is the Field/Tag names
If intRowCount >= 1 Then
' Loop accross columns...
For intColCounter = 1 To intColCount
' Mark the cell under current scrutiny by setting
' an object variable...
Set rngCell = .Cells(1, intColCounter)
' Is the cell merged?..
If Not rngCell.MergeArea.Address = _
rngCell.Address Then
MsgBox ("!! Cell Merged ... Invalid format")
Exit Function
End If
strColNames(intColCounter) = rngCell.Text
Next
End If
Dim Nodes() As String
Dim NodeStack() As String
' Loop down the table's rows
For intRowCounter = 2 To intRowCount
strXML = strXML & vbCrLf & TABLE_ROW
ReDim NodeStack(0)
' Loop accross columns...
For intColCounter = 1 To intColCount
' Mark the cell under current scrutiny by setting
' an object variable...
Set rngCell = .Cells(intRowCounter, intColCounter)
' Is the cell merged?..
If Not rngCell.MergeArea.Address = _
rngCell.Address Then
MsgBox ("!! Cell Merged ... Invalid format")
Exit Function
End If
If Left(strColNames(intColCounter), 1) = NODE_DELIMITER Then
Nodes = Split(strColNames(intColCounter), NODE_DELIMITER)
' check whether we are starting a new node or not
Dim i As Integer
Dim MatchAll As Boolean
MatchAll = True
For i = 1 To UBound(Nodes)
If i <= UBound(NodeStack) Then
If Trim(Nodes(i)) <> Trim(NodeStack(i)) Then
'not match
'MsgBox (Nodes(i) & "," & NodeStack(i))
MatchAll = False
Exit For
End If
Else
MatchAll = False
Exit For
End If
Next
' add close tags to those not used afterwards
' don't count it when no content
If Trim(rngCell.Text) <> "" Then
If MatchAll Then
strXML = strXML & "</" & NodeStack(UBound(NodeStack)) & ">" & vbCrLf
Else
For t = UBound(NodeStack) To i Step -1
strXML = strXML & "</" & NodeStack(t) & ">" & vbCrLf
Next
End If
If i < UBound(Nodes) Then
For t = i To UBound(Nodes)
' add to the xml
strXML = strXML & "<" & Nodes(t) & ">"
If t = UBound(Nodes) Then
strXML = strXML & Trim(rngCell.Text)
End If
Next
Else
t = UBound(Nodes)
' add to the xml
strXML = strXML & "<" & Nodes(t) & ">"
strXML = strXML & Trim(rngCell.Text)
End If
NodeStack = Nodes
Else
' since its a blank field, so no need to handle if field name repeated
If Not MatchAll Then
For t = UBound(NodeStack) To i Step -1
strXML = strXML & "</" & Trim(NodeStack(t)) & ">" & vbCrLf
Next
End If
ReDim Preserve NodeStack(i - 1)
End If
' the last column
If intColCounter = intColCount Then
' add close tags to those not used afterwards
If UBound(NodeStack) <> 0 Then
For t = UBound(NodeStack) To 1 Step -1
strXML = strXML & "</" & Trim(NodeStack(t)) & ">" & vbCrLf
Next
End If
End If
Else
' add close tags to those not used afterwards
If UBound(NodeStack) <> 0 Then
For t = UBound(NodeStack) To 1 Step -1
strXML = strXML & "</" & Trim(NodeStack(t)) & ">" & vbCrLf
Next
End If
ReDim NodeStack(0)
' skip if no content
If Trim(rngCell.Text) <> "" Then
strXML = strXML & "<" & Trim(strColNames(intColCounter)) & ">" & Trim(rngCell.Text) & "</" & Trim(strColNames(intColCounter)) & ">" & vbCrLf
End If
End If
Next
Next
End With
strXML = strXML & TAG_END
' Return the HTML string...
fGenerateXML = strXML
End Function
有关更多信息,请参阅此链接
https://www.codeproject.com/Articles/6950/Export-Excel-to-XML-in-VBA
如果你有什么建议,请告诉我。
