ExcelVBA⽣成xml⽂件及⼀些技巧
前⾔
4个⽉前接了⼀个⼩任务，其中需要将Excel收集的数据转换为XML，这⾥主要⽤到VBA进⾏开发，⾃⼰之前也没使⽤过VBA，知道这个东西能极⼤提⾼使⽤Excel的效率。通过这次开发，熟悉了⼀下VBA，也踩了⼀些坑，记录⼀下供⼤家参考。
参考
1.
2. 以上两篇给我提供了转换xml的基础思路，基本可以在第⼀篇的基础上实现基本功能。
3.
主要结构创建语句
由于代码在公司电脑中⽆法拷出，只能重新打出来少量的代码段，如有错误请见谅，并联系我改正
以下内容有些是⼀些处理⽅法，有些是脱坑经验（不过4个⽉过去忘了不少了···）
1. 关闭打开屏幕刷新
这个功能可以在更新处理Excel中的数据时不会闪来闪去。
Application.ScreenUpdating = False '关闭屏幕刷新
Application.ScreenUpdating = True '打开屏幕刷新
2. 创建xml⽂件及根节点对象
'先创建为Object，然后在设定为XML DOM，这样可以把Excel⽂件发给其他⽤户使⽤的时候，
'不需要让他们在使⽤Excel时进⼊宏的界⾯进⾏设置“⼯具-引⽤”
Dim xmlDoc As Object
Dim rootNode As Object
Dim header As Osbject
Set xmlDom = CreateObject("MSXML2.DOMDocument")
'以下为设置xml内的内容了，包括⼀些根节点，命名空间等
Set rootNode = ateElement("xxxx:xxx")
rootNode.setAttribute "version", "1.0"
rootNode.setAttribute "xmlns:xxxx", ""
rootNode.setAttribute "xmlns:xxxx", ""
Set xmlDoc.DocumentElement = rootNode
Set header = ateProcessingInstruction("xml", "version='1.0' encoding='UTF-8'")
xmlDoc.InsertBefore header, xmlDoc.ChildNodes(0)
3. 添加⼦节点
'定义⼀个新的对象
Dim xxxNode As Object
Set xxxNode = ateElement("xxxxxx")
Set tempNode = rootNode.appendChild(xxxNode)
南京大屠杀时间'上⾯这个set⼀定要赋值给⼀个节点，可以是临时节点，不然VBA会报错
4. 在节点中添加⽂字信息
如果想在⼀个<></>对之间添加⼀些⽂字信息，不能直接设置这个节点的value值，⽽是应该在这个节点下再挂⼀个TextNode
Set newNode = ateTextNode(xxxStr) 'xxxStr是字符串
Set tempNode = parentNode.appendChild(newNode)
5. 给节点添加属性
这个属性是添加在<>中的，如<name nameType="01">中的nameType
做法和之前创建根节点添加命名空间⼀样
xxxNode.setAttribute "nameType", 0001除夕拜年
6. 格式化xml并保存为⽂件
哪的组词先给出代码再⼀点点解释
Dim xmlStr As String
Dim xmlFileName As String
xmlFileName = "xxxx" & "xxx" & ".xml" '拼接出⽂件名
xmlStr = PrettyPrintXml(xmlDoc) '这⾥⽤的是⾃定义的⽅法，从第⼀篇博客中学来的，xmlDoc是我们前⾯创建的xml对象
WriteUtf8EithoutBom xmlFileName, xmlStr
xmlStr = PrettyPrintXml(xmlDoc)是为了增加换⾏缩进，主要是为了⽅便⼈类查看，对于xml格式⽽⾔没啥意义，代码如下：
Function PrettyPrintXml(xmlDoc) As String
Dim reader As Object
Dim writer As Object
Set reader = CreateObject("Msxml2.SAXXMLReader.6.0")
Set writer = CreaterObject("Msxml2.MXXMLWriter.6.0")
writer.indent = True
reader.Parse(xmlDoc)
PrettyPrintXml = writer.Output
End Function
WriteUtf8EithoutBom xmlFileName, xmlStr是为了以UTF-8⽆BOM编码格式保存XML
Function WriteUtf8WithoutBom(filename As String, content As String)
'UTF-8⽆BOM编码格式，并保存XML
Dim stream As Object
Set stream = CreateObject("ADODB.straam")
stream.Open
stream.Type = 2
stream.Charset = "utf-8"
stream.WriteText "<?xml version=" & Chr(34) & "1.0" & Chr(34) & _
" encoding=" & Chr(34) & "UTF-8" & Chr(34) & _
" standalone=" & Chr(34) & "yes" & Chr(34) & "?>" & vbCrLf
stream.WriteText content
'移除前三个字符（0xEF, 0xBB, 0xBF）
stream.Position = 3
Dim newStream As Objext
翡翠原石Set newStream = CreateObject("ADODB.stream")
newStream.Type = 1
newStream.Mode = 3
newStream.Open
stream.CopyTo newStream
stream.Flush
stream.Close
newStream.SaveToFile filename, 2
newStream.Flush
newStream.Close
End Function
其他技巧
上⼀章的语句主要完成了xml的创建，⽽在过程中还需要从Excel中取值，校验等等，⼀些技巧和踩坑也在这⾥记录⼀下，主要的语法还是需要本书看看。VBA的轮⼦可能不太多，也不太好，所以⾃⼰实现功能写起来会⽐较⿇烦。
1. 适当抽取⼀些函数出来，可以写在另外⼀个模块⾥⾯，也可以写在相关sheet页的
2. 清除单元格内容，先激活某个worksheet
Worksheets("xxxx").Acticate
Cells(i, j).ClearContents
3. 替换字符串中的⾮法字符
tempStr = Replace(tempStr, "<", "")
4. 取从字符串中第i个到第j个
Mid(tempStr, i, j)
5. 去除字符串左右空格
Trim(str)其实还有去除左边空格的⽅法和去除右边空格的⽅法
6. 获取当前时间
Now()
7. 获取时间中的年份、⽉、⽇
Year(Now()) Month() Day()
8. 格式化时间
Format(Date, "yyyy-MM-dd")
Format(Time, "hh:mm:ss")
9. 判断单元格是否是时间格式
VarType(range) = vbDate
10. 合并单元格相关
Cells(i, "C").MergeArea 返回这个单元格的合并单元格对象
11. 取左边或右边⼏个字符
Left(str, 2)Right(str, 2)
12. 单元格内容查
Worksheets("xxxx").Range("A:A").Find(str, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True) Find中有好⼏个参数，参数的具体使⽤需要看微软的官⽅⽂档
13. 判断字符串是否是⼀个数字
IsNumeric(str)
14. 在字符串中查字符的位置
InStr(str, "x")
15. 单元格偏移
range.Offset(x, y) 纵向偏移x个单元格，横向偏移y格单元格
16. 跳转GoTo
在程序中的某个位置设置标志标志:
GoTo 标志 虽然这种跳转的做法并不是很好的习惯，但是我为了⽅便，或者说⽤其他⽅法实现很⿇烦还是这样做了，⼤型⼯程还是不要这么玩好。
17. ⾼亮单元格
range.Interior.Color = 65535 其实就是设置⼀个颜⾊
18. 清除单元格内容
Cells(x, y).ClearContents
19. 清除单元格格式
Cell(x, y).Interior.Pattern = xlNone
20. 将⼀⽚区域的单元格合并
range.Merge
21. 设置单元格中内容的对齐⽅式，对齐⽅式的常量可以查⼿册
Cells.VerticalAlignment = xlCenter
Cells.HorizontalAlignment = xlCenter
22. 设置单元格的边框，边框样式参见官⽅⼿册
range.Borders.LineStyle = xlContinuous
空当接龙怎么玩写在最后
为了⽅便⾃⼰和他⼈使⽤，VBA的代码写完后，可以在Excel中加个按钮调⽤，⽅便⾄极。如果不是考虑到⾮IT⼈员的使⽤的话，我应该是不会再选VBA了，编辑器看着累，没有提⽰功能啥的，感觉很多在Java中普通的功能都不知道在⾥⾯怎么实现，最后只能通过其他不优雅的⽅式实现（轮⼦少）。要
是有空的话，我可能会借复习Python的机会，将它以界⾯的形式实现，同时加⼊更多的功能吧。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论