Excel实现图片与Base64字符串的相互转化

F2967527 2022-10-19 发布于北京

展开全文

需求描述

关于图片的使用，我们可以通过以下的方式实现：

将图片存储在本地磁盘，直接通过本地地址或软件访问使用
将图片存储在云盘，通过Url链接地址访问
将图片以Base64字符串的形式存储于文件或数据库中，以字符串的形式使用

如何通过Excel VBA实现图片与Base64字符串的相互转化以在Excel场景下更好的使用图片

需求分析

图片的本质是一个二进制流的文件，我们可以通过'ADODB.Stream'来进行文件的图片读取和创建

需求实现

实现将本地图片转化为Base64的字符串

'@米可爱分享'@Excel https://mp.weixin.qq.com/mp/appmsgalbum?action=getalbum&album_id=2170702502340427778'@付费咨询 https://work.weixin.qq.com/kfid/kfce4e306b8e3c2d528'实现将本地图片转化为Base64的字符串Public Function ConvertImageToBase64(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 ConvertImageToBase64 = Replace(objXmlElem.Text, vbLf, '')

Set objStream = Nothing Set objXmlDoc = Nothing Set objXmlElem = NothingEnd Function

将Base64字符串转化为图片

'@米可爱分享'@Excel https://mp.weixin.qq.com/mp/appmsgalbum?action=getalbum&album_id=2170702502340427778'@付费咨询 https://work.weixin.qq.com/kfid/kfce4e306b8e3c2d528'将Base64字符串转化为图片Public Sub ConvertBase64ToImage(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 = NothingEnd Sub

需求演示

Function ConvertImageToBase64Test() As String ConvertImageToBase64Test = ConvertImageToBase64('D:\Meekou\Meekou.Live\20220416\米可爱分享.jpg') Debug.Print ConvertImageToBase64TestEnd Function

Sub ConvertBase64ToImageTest() Dim imagebase64 As String imagebase64 = ConvertImageToBase64Test() ConvertBase64ToImage 'D:\Meekou\Meekou.Live\20220416\米可爱分享_New.jpg', imagebase64End Sub

视频讲解