如何批量处理excel中的某一列md5加密

最新推荐文章于 2024-05-10 10:28:10 发布

毛依依

最新推荐文章于 2024-05-10 10:28:10 发布

阅读量4.2k

点赞数

分类专栏： EXCEL 文章标签： excel MD5

本文链接：https://blog.csdn.net/baidu_15873551/article/details/97532836

版权

当使用Excel存储数据并需要对手机号进行MD5加密时，可以使用VB宏实现。通过在Excel选项中启用“开发工具”菜单，添加宏加载项，调用Md5_String_Calc函数。在新的列中输入公式，例如=Md5_String_Calc(A2)，然后拖拽完成批量加密。最后，验证加密结果以确保正确性。

摘要由CSDN通过智能技术生成

背景：在用excel作为数据存储的时候，发现有部分需求需要对手机号进行md5加密

使用VB编写有关md5加密的宏

' Standard MD5 implementation optimised for the Visual Basic environment.
Private Const OFFSET_4 = 4294967296#
Private Const MAXINT_4 = 2147483647
Private State(4) As Long
Private ByteCounter As Long
Private ByteBuffer(63) As Byte
Private Const S11 = 7
Private Const S12 = 12
Private Const S13 = 17
Private Const S14 = 22
Private Const S21 = 5
Private Const S22 = 9
Private Const S23 = 14
Private Const S24 = 20
Private Const S31 = 4
Private Const S32 = 11
Private Const S33 = 16
Private Const S34 = 23
Private Const S41 = 6
Private Const S42 = 10
Private Const S43 = 15
Private Const S44 = 21
Property Get RegisterA() As String
    RegisterA = State(1)
End Property
Property Get RegisterB() As String
    RegisterB = State(2)
End Property

Property Get RegisterC() As String
    RegisterC = State(3)
End Property

Property Get RegisterD() As String
    RegisterD = State(4)
End Property
Public Function Md5_String_Calc(SourceString As String) As String
    MD5Init
    MD5Update LenB(StrConv(SourceString, vbFromUnicode)), StringToArray(SourceString)
    MD5Final
    Md5_String_Calc = GetValues
End Function
Public Function Md5_File_Calc(InFile As String) As String
    On Error GoTo errorhandler
    GoSub begin

errorhandler:
    DigestFileToHexStr = ""
    Exit Function

begin:
    Dim FileO As Integer
    FileO = FreeFile
    Call FileLen(InFile)
    Open InFile For Binary Access Read As #FileO
    MD5Init
    Do While Not EOF(FileO)
        Get #FileO, , ByteBuffer
        If Loc(FileO) < LOF(FileO) Then
            ByteCounter = ByteCounter + 64
            MD5Transform ByteBuffer
        End If
    Loop
    ByteCounter = ByteCounter + (LOF(FileO) Mod 64)
    Close #FileO
    MD5Final
    Md5_File_Calc = GetValues
End Function
Private Function StringToArray(InString As String) As Byte()
    Dim I As Integer, bytBuffer() As Byte
    ReDim bytBuffer(LenB(StrConv(InString, vbFromUnicode)))
    bytBuffer = StrConv(InString, vbFromUnicode)
    StringToArray = bytBuffer
End Function
Public Function GetValues() As String
    GetValues = LongToString(State(1)) & LongToString(State(2)) & LongToString(State(3)) & LongToString(State(4))
End Function
Private Function LongToString(Num As Long) As String
    Dim A As Byte, B As Byte, C As Byte, D As Byte
    A = Num And &HFF&
    If A < 16 Then LongToString = "0" & Hex(A) Else LongToString = Hex(A)
    B = (Num And &HFF00&) \ 256
    If B < 16 Then LongToString = LongToString & "0"