通过这个 宏，可以配置excel模板文件，编辑维护springboot工程application.properties配置文件的配置项，并一键生成配置文件。
Option Explicit
Sub Dealwith()
'统计时间
Dim S1 As Long
S1 = Timer
'声明变量及类型
Dim irow As Integer, n As Integer, Arr, Brr
'取活动单元表的第一列最后一个有值的行的下一行行号
irow = Cells(Cells.Rows.Count, 1).End(3).Row
'拆分成irow行，3列
Arr = [a1].Resize(irow, 3).Value
'定义数组大小
ReDim Brr(1 To irow - 1, 1 To 2)
'UBound返回数组指示维度的最大值。
For n = 2 To UBound(Arr)
'将Arr数组的2个值用=拼接起来
Brr(n - 1, 1) = Arr(n, 1) & "=" & Arr(n, 2)
Brr(n - 1, 2) = Arr(n, 3)
Next
'将拼接结果写到E列
'[e2].Resize(irow - 1, 1) = Brr
'生成properties文件
Dim FilePath As String
'获取当前文件路径
FilePath = ThisWorkbook.Path & "\"
Dim FileName As String
'读取当前文件名并去除后缀
'FileName = Left(Application.ActiveWorkbook.Name, Len(Application.ActiveWorkbook.Name) - 4)
'文件名改为指定文件名
FileName = FilePath & "application" & ".properties"
'判断文件是否存在，如果存在就删除
'If Dir(FileName) <> "" Then
' Kill FileName
'End If
Dim fileSaveName As String, outStream As Object, binStream As Object
'第一个参数为文件名，第二个参数为文件格式，第4个参数为弹窗标题
fileSaveName = Application.GetSaveAsFilename(FileName, fileFilter:="*.properties,*.properties")
'fileSaveName = Application.GetSaveAsFilename(FileName, "*.properties", , "配置文件另存为")
'创建写入数据的流,且指定为utf8编码
Set outStream = CreateObject("ADODB.Stream")
outStream.Open
outStream.Charset = "utf-8"
outStream.Type = 2
Set binStream = CreateObject("ADODB.Stream")
binStream.Open
binStream.Type = 1
'Dim Cell As Range, i As Integer
'循环读E列拼接好的值
'For Each Cell In Range("E2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
' If Not IsEmpty(Cell) Then
' ' 开始写入数据
' outStream.WriteText (Cell.Value & vbCrLf) ' & vbCrLf 为换行符
' End If
'
'Next
For n = 1 To UBound(Brr)
If Not IsEmpty(Brr(n, 2)) Then
' 开始写入数据
outStream.WriteText ("## " & Brr(n, 2) & vbCrLf) ' & vbCrLf 为换行符
End If
If Not IsEmpty(Brr(n, 1)) Then
' 开始写入数据
outStream.WriteText (Brr(n, 1) & vbCrLf) ' & vbCrLf 为换行符
End If
Next
outStream.Position = 3
outStream.CopyTo binStream
binStream.SaveToFile fileSaveName, 2 '需要将内容偏移两位去掉“UTF-8+”的bom，fileSaveName为写入数据的文件路径和名字
binStream.Close
outStream.Close
MsgBox "配置文件生成结束，本次运行消耗时间" & Timer - S1 & "秒!" '句放在宏的最后一行
End Sub
