Excel VBA生成xml文件及一些技巧

前言

4个月前接了一个小任务，其中需要将Excel收集的数据转换为XML，这里主要用到VBA进行开发，自己之前也没使用过VBA，知道这个东西能极大提高使用Excel的效率。通过这次开发，熟悉了一下VBA，也踩了一些坑，记录一下供大家参考。

参考

1. VBA 格式化输出XML(UTF-8无BOM编码)
   https://blog.csdn.net/luwhite/article/details/52343305

2. [求助] 求大神，用vba 将excel内容导出为xml格式文件
   http://club.excelhome.net/thread-1335579-1-1.html

   以上两篇给我提供了转换xml的基础思路，基本可以在第一篇的基础上实现基本功能。

3. https://docs.microsoft.com/en-us/office/vba/api/excel.range.address 这里是微软关于Excel中一些对象的定义，搞清楚各个层级的对象，对于开发能提高效率。当然还有菜鸟教程中关于XML的资料也是需要学习一下的，以前学Java没有系统了解过xml，通过这个学习也对xml有了总体的认识。

主要结构创建语句

由于代码在公司电脑中无法拷出，只能重新打出来少量的代码段，如有错误请见谅，并联系我改正
以下内容有些是一些处理方法，有些是脱坑经验（不过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 = xmlDoc.createElement("xxxx:xxx")
rootNode.setAttribute "version",  "1.0"
rootNode.setAttribute "xmlns:xxxx", "http://xxxx.xxxx.xxx"
rootNode.setAttribute "xmlns:xxxx", "http://xxxx.xxxx.xxx"
Set xmlDoc.DocumentElement = rootNode
Set header = xmlDoc.createProcessingInstruction("xml", "version='1.0' encoding='UTF-8'")
xmlDoc.InsertBefore header, xmlDoc.ChildNodes(0)

3. 添加子节点

'定义一个新的对象
Dim xxxNode As Object
Set xxxNode = xmlDoc.createElement("xxxxxx")
Set tempNode = rootNode.appendChild(xxxNode)
'上面这个set一定要赋值给一个节点，可以是临时节点，不然VBA会报错

4. 在节点中添加文字信息
   如果想在一个<></>对之间添加一些文字信息，不能直接设置这个节点的value值，而是应该在这个节点下再挂一个TextNode

Set newNode = xmlDoc.createTextNode(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
	writer.omitXMLDeclaration = True
	reader.contentHandler = writer
	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的机会，将它以界面的形式实现，同时加入更多的功能吧。