2012年5月25日 星期五

數字轉國字格式-Crystal Reports公式 (Crystal Reports)

參考引用
---
數字轉換成支票格式的國字 銷售報表還蠻常看到的
先於公式欄式 新增公式名稱 num_chinese
右鍵編輯按ctrl+T
選擇basic語法 (非公式專家模式)
{pssales.Total_amt}是我要轉換的資料庫欄位名稱
輸入以下程式碼 儲存
Dim intPos As Number
Dim intI As Number
Dim intLength As Number
Dim strChaneseNo As String
Dim strCardinal1 As String
Dim strCardinal2 As String
Dim strTotal As String
Dim strTemp As String
Dim strX As String

strTotal = Cstr({pssales.Total_amt})
intPos = Instr(strTotal, ",")

While intPos > 1
  strTemp = strTemp & Left(strTotal, intPos-1)
  strTotal = Mid(strTotal, intPos + 1)
  intPos = Instr(strTotal, ",")
Wend
strTotal = strTemp & strTotal
if Instr(strTotal,".") > 1 Then
   strTotal = Mid(strTotal, 1, Len(strTotal) -3)
end if

strChaneseNo = "零壹貳參肆伍陸柒捌玖"
strCardinal1 = " 拾佰仟 拾佰仟 拾佰仟 拾佰仟"
strCardinal2 = "圓萬億兆"

intLength = Len(strTotal)
For intI = 1 To intLength Step 1
  If Val(Mid(strTotal, intI, 1)) = 0 AND (Val(Mid(strTotal, intI+1, 1)) = 0 Or (intLength - intI + 1 ) Mod 4 = 1) Then
    strX = strX & ""
  Else
    strX = strX & Mid(strChaneseNo, Val(Mid(strTotal, intI, 1)) +1 , 1)
  End If
  If (intLength - intI + 1) Mod 4 = 1 Or Val(Mid(strTotal, intI, 1)) = 0 Then
    strX=strX & ""
  Else
    strX = strX & Mid(strCardinal1, (intLength - intI + 1), 1)
  End If
  If (intLength - intI + 1) Mod 4 = 1 Then
    strX = strX & Mid(strCardinal2, (intLength - intI + 1) \ 4 + 1, 1)
    If IntI > 3 And (IntLength - IntI + 1) > 1 Then
        If Mid(strTotal,intI - 3, 4) = "0000" Then
      strX = Left(strX, Len(strX) - 1)
    End if
    End If
  End If
Next

Formula = strX & "整"

沒有留言:

張貼留言