图片与base64字符串的相互转化

将本地图片转化为base64字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Public Function ConvertFileToBase64(filePath As String) As String

    Const UseBinaryStreamType = 1

    Dim objStream: Set objStream = CreateObject("ADODB.Stream")
    Dim objXmlDoc: Set objXmlDoc = CreateObject("Microsoft.XMLDOM")
    Dim objXmlElem: Set objXmlElem = objXmlDoc.createElement("tmp")
    
    objStream.Open
    objStream.Type = UseBinaryStreamType
    objStream.LoadFromFile filePath
    objXmlElem.DataType = "bin.base64"
    objXmlElem.nodeTypedValue = objStream.Read
    ConvertFileToBase64 = Replace(objXmlElem.Text, vbLf, "")
    
    Set objStream = Nothing
    Set objXmlDoc = Nothing
    Set objXmlElem = Nothing
    
End Function

将base64字符串保存为本地图片

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Public Sub ConvertBase64ToFile(filePath As String, strBase64 As String)

    Const UseBinaryStreamType = 1
    Const SaveWillCreateOrOverwrite = 2

    Dim objStreamOutput: Set objStreamOutput = CreateObject("ADODB.Stream")
    Dim objXmlDoc: Set objXmlDoc = CreateObject("Microsoft.XMLDOM")
    Dim objXmlElem: Set objXmlElem = objXmlDoc.createElement("tmp")
    
    objXmlElem.DataType = "bin.base64"
    objXmlElem.Text = strBase64
    objStreamOutput.Open
    objStreamOutput.Type = UseBinaryStreamType
    objStreamOutput.Write = objXmlElem.nodeTypedValue
    objStreamOutput.SaveToFile filePath, SaveWillCreateOrOverwrite
    
    Set objStreamOutput = Nothing
    Set objXmlDoc = Nothing
    Set objXmlElem = Nothing
    
End Sub

VBA将base64字符串插入Excel图片

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Sub InsertImgFromBase64(sheet As String, address As String, varImgName As String, varBase64 As String)
    Dim tempFile As String
    'Save base64 string to temp folder
    tempFile = Environ("temp") & "\" & varImgName
    
    'save byte array to temp file
    Open tempFile For Binary As #1
       Put #1, 1, DecodeBase64(varBase64)
    Close #1

    'insert image from temp file
    Sheets(sheet).Range(address).Select
    Sheets(sheet).Pictures.Insert tempFile

    'kill temp file
    Kill tempFile
    
End Sub

Private Function DecodeBase64(ByVal strData As String) As Byte()

    Dim objXML As Object 'MSXML2.DOMDocument
    Dim objNode As Object 'MSXML2.IXMLDOMElement

    'get dom document
    Set objXML = CreateObject("MSXML2.DOMDocument")

    'create node with type of base 64 and decode
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.Text = strData
    DecodeBase64 = objNode.nodeTypedValue

    'clean up
    Set objNode = Nothing
    Set objXML = Nothing

End Function

Sub InsertImgFromBase64Test()
    InsertImgFromBase64 "Sheet1", "E15", "test2.png", Range("A1")
End Sub

Scripts实现Excel Online插入base64图片

1
2
3
4
5
6
7
function main(workbook: ExcelScript.Workbook, sheetName: string, address: string, base64ImageString: string) {
  let sheet = workbook.getWorksheet(sheetName);
  let range = sheet.getRange(address);
  let image = sheet.addImage(base64ImageString);
  image.setTop(range.getTop());
  image.setLeft(range.getLeft());
}