转换为十六进制字符串为base64在Excel函数 [英] Convert a hex string to base64 in an excel function

问题描述

我有需要转换为Base64十六进制值的长度的字符串。

我在寻找一个简单的格式细胞功能，如= Hex2b64（十六进制串），将接受十六进制字符任意长度。我已经在这里和那里发现碎片，但没有一个简单的code座。

我一直使用 http://home.paulschou.net/tool​​s/xlate/为了做我的手动转换到这一点。转换运作良好，并在数据被所有相关数据库接收并适当地进行解析。

数据我收到的psented二进制十六进制再$ P $，这已被转换多个块和串联成按照项目文档长十六进制字符串，我无法得知。

一个典型的输入字符串是：
十六进制= 00014088F6650101393939393939392D30304646463238313030000343332353430342D35353FA10000002805900100002805

和对应的输出将是：
B64 = AAFAiPZlAQE5OTk5OTk5LTAwRkZGMjgxMDAAA0MzI1NDA0LTU1P6EAAAAoBZABAAAoAF

解决方案

 功能Hex2Base64（BYVAL strHex）
    昏暗arrBytes
    如果len（strHex）mod2';＆GT; 0，则
        strHex =左（strHex，莱恩（strHex） -  1）及0＆放大器;右（strHex，1）
    万一
    随着的CreateObject（Microsoft.XMLDOM）。的createElement（objNode）
        .DataType =bin.hex
        的.text = strHex
        arrBytes = .nodeTypedValue
    结束与
    随着的CreateObject（Microsoft.XMLDOM）。的createElement（objNode）
        .DataType =bin.base64
        .nodeTypedValue = arrBytes
        Hex2Base64 =。文本
    结束与
结束功能
 

I have a lengthy string of hex values that need to be converted to base64.

I'm looking for a simple format cell function such as =Hex2b64(Hexstring) that will accept any length of hex characters. I have found pieces here and there but no one simple code block.

I have been using http://home.paulschou.net/tools/xlate/ in order to do my conversion manually up to this point. The conversion works well and the data is received by all relevant databases and parsed appropriately.

The data I am receiving is hex represented binary, which has been converted in multiple blocks and concatenated into long hex strings in accordance with project documentation that I am not privy to.

A typical Input String would be: Hex= 00014088F6650101393939393939392D30304646463238313030000343332353430342D35353FA10000002805900100002805

and the corresponding output would be: B64 = AAFAiPZlAQE5OTk5OTk5LTAwRkZGMjgxMDAAA0MzI1NDA0LTU1P6EAAAAoBZABAAAoAF

解决方案

Function Hex2Base64(byVal strHex)
    Dim arrBytes
    If Len(strHex) Mod 2 <> 0 Then
        strHex = Left(strHex, Len(strHex) - 1) & "0" & Right(strHex, 1)
    End If
    With CreateObject("Microsoft.XMLDOM").createElement("objNode")
        .DataType = "bin.hex"
        .Text = strHex
        arrBytes = .nodeTypedValue
    End With
    With CreateObject("Microsoft.XMLDOM").createElement("objNode")
        .DataType = "bin.base64"
        .nodeTypedValue = arrBytes
        Hex2Base64 = .Text
    End With
End Function

这篇关于转换为十六进制字符串为base64在Excel函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！