【水晶报表函数集】货币大写

阿泰 2010-04-20 06:57:35

加精

水晶报表中如何创建和使用函数请参考：【水晶报表内功心法】--公式、函数与运行时总计

函数使用Basic语法





'#############################################################################

'货币转换为中文汉字表述

'

'函数名称：CNMoney

'参数：ls

'返回值：转换后的字符串

'

'整理人：阿泰

'版本历史

'2010-04-20：首次编译，修正0参数，修正小于10的值不能正常显示的BUG

'

'本文函数来源于 feng442624978，原帖地址：

'http://topic.csdn.net/u/20100303/15/0f0ceca7-d29d-4269-b0f5-17ea09d0f139.html

'#############################################################################



Function CNMoney(ls As Number) As String

    Dim dx_sz As String

    Dim dx_dw As String

    Dim str_int As String

    Dim str_dec As String

    Dim dx_str As String

    Dim fu As String

    Dim a As String

    Dim b As String

    Dim c As String

    Dim d As String

    Dim b2 As String

    Dim num_int As Number

    Dim num_dec As Number

    Dim len_int As Number

    Dim i As Number

    Dim a_int As Number

    Dim pp As Number



    dx_sz = "零壹贰叁肆伍陆柒捌玖"

    dx_dw = "万仟佰拾亿仟佰拾万仟佰拾圆"

    

    If ls = 0 Then

        CNMoney = "零圆整"

        Exit Function

    End If

    

    If ls < 0 Then

        ls = Abs(ls)

        fu = "负"

    Else

        fu = ""

    End If



    dx_str = CStr(ls)

    dx_str = Replace(dx_str, "￥", "")

    dx_str = Replace(dx_str, ",", "")

    If (ls >= 0) And (ls < 1) Then dx_str = "0" + dx_str

    

    pp = InStr(dx_str, ".")

    If pp > 0 Then

        str_int = Mid(dx_str, 1, InStr(dx_str, ".") - 1)

    Else

        str_int = dx_str

    End If



    num_int = tonumber(str_int)



    If (ls > 0) And (ls < 1) Then

        num_dec = ls * 100

    Else

        num_dec = (ls - num_int) * 100

    End If



    str_dec = totext(num_dec)

    str_dec = Replace(str_dec, "￥", "")



    len_int = Len(str_int)

    dx_str = ""

    For i = 1 To len_int

        a = Mid(str_int, i, 1)

        a_int = tonumber(a)

        b = Mid(dx_sz, (a_int + 1), 1)

        c = Mid(dx_dw, (13 - len_int + i), 1)

        If dx_str <> "" Then

            d = Mid(dx_str, Len(dx_str) - 1, 1)

        Else

            d = ""

        End If

        If (b = "零") And ((d = "零") Or (b = b2) Or (c = "圆") Or (c = "万") Or (c = "亿")) Then b = ""

        If (a = "0") And (c <> "圆") And (c <> "万") And (c <> "亿") Then c = ""

        If ((c = "圆") Or (c = "万") Or (c = "亿")) And (d = "零") And (a = "0") Then

            dx_str = Mid(dx_str, 1, Len(dx_str) - 2)

            d = Mid(dx_str, Len(dx_str) - 1, 2)

            If ((c = "圆") And (d = "万")) Or ((c = "万") And (d = "亿")) Then c = ""

        End If

        dx_str = dx_str + b + c

        b2 = b

    Next i



    '处理金额小于1的情况

    If Len(dx_str) < 2 Then dx_str = ""

    If (num_dec < 10) And (ls > 0) Then

        a_int = tonumber(str_dec)

        b = Mid(dx_sz, (a_int + 1), 1)

        If num_dec = 0 Then dx_str = dx_str + "整"

        If num_dec > 0 Then dx_str = dx_str + "零" + b + "分"

    End If

    If num_dec >= 10 Then

        a_int = tonumber(Mid(str_dec, 1, 1))

        a = Mid(dx_sz, (a_int + 1), 1)

        a_int = tonumber(Mid(str_dec, 2, 1))

        b = Mid(dx_sz, (a_int + 1), 1)

        If a <> "零" Then a = a + "角"

        If b <> "零" Then b = b + "分" Else b = ""

        dx_str = dx_str + a + b

    End If

        

    dx_str = fu + dx_str



    dx_str = Replace(dx_str, "零亿", "亿")

    dx_str = Replace(dx_str, "零万", "万")

    dx_str = Replace(dx_str, "零千", "千")

    dx_str = Replace(dx_str, "零圆", "圆")





    CNMoney = dx_str

End Function