前言

4個月前接了一個小任務，其中需要將Excel收集的數(shù)據(jù)轉換為XML，這里主要用到VBA進行開發(fā)，自己之前也沒使用過VBA，知道這個東西能極大提高使用Excel的效率。通過這次開發(fā)，熟悉了一下VBA，也踩了一些坑，記錄一下供大家參考。

參考

VBA 格式化輸出XML(UTF-8無BOM編碼)
https://blog.csdn.net/luwhite/article/details/52343305
[求助] 求大神，用vba 將excel內(nèi)容導出為xml格式文件
http://club.excelhome.net/thread-1335579-1-1.html

以上兩篇給我提供了轉換xml的基礎思路，基本可以在第一篇的基礎上實現(xiàn)基本功能。
https://docs.microsoft.com/en-us/office/vba/api/excel.range.address 這里是微軟關于Excel中一些對象的定義，搞清楚各個層級的對象，對于開發(fā)能提高效率。當然還有菜鳥教程中關于XML的資料也是需要學習一下的，以前學Java沒有系統(tǒng)了解過xml，通過這個學習也對xml有了總體的認識。

主要結構創(chuàng)建語句

由于代碼在公司電腦中無法拷出，只能重新打出來少量的代碼段，如有錯誤請見諒，并聯(lián)系我改正
以下內(nèi)容有些是一些處理方法，有些是脫坑經(jīng)驗（不過4個月過去忘了不少了···）

關閉打開屏幕刷新
這個功能可以在更新處理Excel中的數(shù)據(jù)時不會閃來閃去。

Application.ScreenUpdating = False       '關閉屏幕刷新
Application.ScreenUpdating = True       '打開屏幕刷新

創(chuàng)建xml文件及根節(jié)點對象

'先創(chuàng)建為Object，然后在設定為XML DOM，這樣可以把Excel文件發(fā)給其他用戶使用的時候，
'不需要讓他們在使用Excel時進入宏的界面進行設置“工具-引用”
Dim xmlDoc As Object
Dim rootNode As Object
Dim header As Osbject
Set xmlDom = CreateObject("MSXML2.DOMDocument")
'以下為設置xml內(nèi)的內(nèi)容了，包括一些根節(jié)點，命名空間等
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)

添加子節(jié)點

'定義一個新的對象
Dim xxxNode As Object
Set xxxNode = xmlDoc.createElement("xxxxxx")
Set tempNode = rootNode.appendChild(xxxNode)
'上面這個set一定要賦值給一個節(jié)點，可以是臨時節(jié)點，不然VBA會報錯

在節(jié)點中添加文字信息
如果想在一個<></>對之間添加一些文字信息，不能直接設置這個節(jié)點的value值，而是應該在這個節(jié)點下再掛一個TextNode

Set newNode = xmlDoc.createTextNode(xxxStr)  'xxxStr是字符串
Set tempNode = parentNode.appendChild(newNode)

給節(jié)點添加屬性
這個屬性是添加在<>中的，如<name nameType="01">中的nameType
做法和之前創(chuàng)建根節(jié)點添加命名空間一樣

xxxNode.setAttribute "nameType", 0001

格式化xml并保存為文件
先給出代碼再一點點解釋

Dim xmlStr As String
Dim xmlFileName As String
xmlFileName = "xxxx" & "xxx" & ".xml"    '拼接出文件名
xmlStr = PrettyPrintXml(xmlDoc)     '這里用的是自定義的方法，從第一篇博客中學來的，xmlDoc是我們前面創(chuàng)建的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的創(chuàng)建，而在過程中還需要從Excel中取值，校驗等等，一些技巧和踩坑也在這里記錄一下，主要的語法還是需要找本書看看。VBA的輪子可能不太多，也不太好找，所以自己實現(xiàn)功能寫起來會比較麻煩。

適當抽取一些函數(shù)出來，可以寫在另外一個模塊里面，也可以寫在相關sheet頁的
清除單元格內(nèi)容，先激活某個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)
單元格內(nèi)容查找
Worksheets("xxxx").Range("A:A").Find(str, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True) Find中有好幾個參數(shù)，參數(shù)的具體使用需要看微軟的官方文檔
判斷字符串是否是一個數(shù)字
IsNumeric(str)
在字符串中查找字符的位置
InStr(str, "x")
單元格偏移
range.Offset(x, y) 縱向偏移x個單元格，橫向偏移y格單元格
跳轉GoTo
在程序中的某個位置設置標志標志:
GoTo 標志 雖然這種跳轉的做法并不是很好的習慣，但是我為了方便，或者說用其他方法實現(xiàn)很麻煩還是這樣做了，大型工程還是不要這么玩好。
高亮單元格
range.Interior.Color = 65535 其實就是設置一個顏色
清除單元格內(nèi)容
Cells(x, y).ClearContents
清除單元格格式
Cell(x, y).Interior.Pattern = xlNone
將一片區(qū)域的單元格合并
range.Merge
設置單元格中內(nèi)容的對齊方式，對齊方式的常量可以查手冊

Cells.VerticalAlignment = xlCenter
Cells.HorizontalAlignment = xlCenter

設置單元格的邊框，邊框樣式參見官方手冊
range.Borders.LineStyle = xlContinuous

寫在最后

為了方便自己和他人使用，VBA的代碼寫完后，可以在Excel中加個按鈕調用，方便至極。如果不是考慮到非IT人員的使用的話，我應該是不會再選VBA了，編輯器看著累，沒有提示功能啥的，感覺很多在Java中普通的功能都不知道在里面怎么實現(xiàn)，最后只能通過其他不優(yōu)雅的方式實現(xiàn)（輪子少）。要是有空的話，我可能會借復習Python的機會，將它以界面的形式實現(xiàn)，同時加入更多的功能吧。

本站僅提供存儲服務，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權內(nèi)容，請點擊舉報。

打開APP，閱讀全文并永久保存查看更多類似文章

傳智播客：ajax:Dom處理xml - habernate的日志 - 網(wǎng)易博客

使用js解析xml文檔和xml字符串（ie和火狐）

Unity3D研究院之使用 C#合成解析XML與JSON（四十一） | 雨松MOMO程序研究院

在excel表格里，在b列如何實現(xiàn)b=b a這樣的循環(huán)引用？

【VBA】excel中 #N/A = Error 2042

14.Excel VBA-單元格的基本操作之選擇、插入、刪除

更多類似文章 >>

国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

前言

參考

主要結構創(chuàng)建語句

其他技巧

寫在最后