前言
4个月前接了一个小任务,其中需要将Excel收集的数据转换为XML,这里主要用到VBA进行开发,自己之前也没使用过VBA,知道这个东西能极大提高使用Excel的效率。通过这次开发,熟悉了一下VBA,也踩了一些坑,记录一下供大家参考。
参考
-
VBA 格式化输出XML(UTF-8无BOM编码)
https://blog.csdn.net/luwhite/article/details/52343305 -
[求助] 求大神,用vba 将excel内容导出为xml格式文件
http://club.excelhome.net/thread-1335579-1-1.html以上两篇给我提供了转换xml的基础思路,基本可以在第一篇的基础上实现基本功能。
-
https://docs.microsoft.com/en-us/office/vba/api/excel.range.address 这里是微软关于Excel中一些对象的定义,搞清楚各个层级的对象,对于开发能提高效率。当然还有菜鸟教程中关于XML的资料也是需要学习一下的,以前学Java没有系统了解过xml,通过这个学习也对xml有了总体的认识。
主要结构创建语句
由于代码在公司电脑中无法拷出,只能重新打出来少量的代码段,如有错误请见谅,并联系我改正
以下内容有些是一些处理方法,有些是脱坑经验(不过4个月过去忘了不少了···)
- 关闭打开屏幕刷新
这个功能可以在更新处理Excel中的数据时不会闪来闪去。
Application.ScreenUpdating = False '关闭屏幕刷新
Application.ScreenUpdating = True '打开屏幕刷新
- 创建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)
- 添加子节点
'定义一个新的对象
Dim xxxNode As Object
Set xxxNode = xmlDoc.createElement("xxxxxx")
Set tempNode = rootNode.appendChild(xxxNode)
'上面这个set一定要赋值给一个节点,可以是临时节点,不然VBA会报错
- 在节点中添加文字信息
如果想在一个<></>对之间添加一些文字信息,不能直接设置这个节点的value值,而是应该在这个节点下再挂一个TextNode
Set newNode = xmlDoc.createTextNode(xxxStr) 'xxxStr是字符串
Set tempNode = parentNode.appendChild(newNode)
- 给节点添加属性
这个属性是添加在<>中的,如<name nameType="01">
中的nameType
做法和之前创建根节点添加命名空间一样
xxxNode.setAttribute "nameType", 0001
- 格式化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的轮子可能不太多,也不太好找,所以自己实现功能写起来会比较麻烦。
-
适当抽取一些函数出来,可以写在另外一个模块里面,也可以写在相关sheet页的
-
清除单元格内容,先激活某个worksheet
Worksheets("xxxx").Acticate
Cells(i, j).ClearContents
-
替换字符串中的非法字符
tempStr = Replace(tempStr, "<", "")
-
取从字符串中第i个到第j个
Mid(tempStr, i, j)
-
去除字符串左右空格
Trim(str)
其实还有去除左边空格的方法和去除右边空格的方法 -
获取当前时间
Now()
-
获取时间中的年份、月、日
Year(Now()) Month() Day()
-
格式化时间
Format(Date, "yyyy-MM-dd")
Format(Time, "hh:mm:ss")
- 判断单元格是否是时间格式
VarType(range) = vbDate
- 合并单元格相关
Cells(i, "C").MergeArea
返回这个单元格的合并单元格对象 - 取左边或右边几个字符
Left(str, 2)
Right(str, 2)
- 单元格内容查找
Worksheets("xxxx").Range("A:A").Find(str, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True)
Find中有好几个参数,参数的具体使用需要看微软的官方文档 - 判断字符串是否是一个数字
IsNumeric(str)
- 在字符串中查找字符的位置
InStr(str, "x")
- 单元格偏移
range.Offset(x, y)
纵向偏移x个单元格,横向偏移y格单元格 - 跳转GoTo
在程序中的某个位置设置标志标志:
GoTo 标志
虽然这种跳转的做法并不是很好的习惯,但是我为了方便,或者说用其他方法实现很麻烦还是这样做了,大型工程还是不要这么玩好。 - 高亮单元格
range.Interior.Color = 65535
其实就是设置一个颜色 - 清除单元格内容
Cells(x, y).ClearContents
- 清除单元格格式
Cell(x, y).Interior.Pattern = xlNone
- 将一片区域的单元格合并
range.Merge
- 设置单元格中内容的对齐方式,对齐方式的常量可以查手册
Cells.VerticalAlignment = xlCenter
Cells.HorizontalAlignment = xlCenter
- 设置单元格的边框,边框样式参见官方手册
range.Borders.LineStyle = xlContinuous
写在最后
为了方便自己和他人使用,VBA的代码写完后,可以在Excel中加个按钮调用,方便至极。如果不是考虑到非IT人员的使用的话,我应该是不会再选VBA了,编辑器看着累,没有提示功能啥的,感觉很多在Java中普通的功能都不知道在里面怎么实现,最后只能通过其他不优雅的方式实现(轮子少)。要是有空的话,我可能会借复习Python的机会,将它以界面的形式实现,同时加入更多的功能吧。