NCR &#xxxxx; 處理方式:Java & VBA

工作上常常需要把資料庫的東西匯出,但有一些資料是以 NCR (Numeric Character Reference) 的方式儲存或呈現,這些在網頁上看都沒有什麼問題,但在 Excel 裡面「你好嗎」如果是 NCR 就會看到這樣「你好嗎」,這邊記錄用 VBA 跟 Java 個別的處理方式。

先來搞清楚 NCR 的構成吧!大致上會是以 &# 開頭,中間串接個數字,最後以分號 ; 結尾,大概就是像這樣

你 => 你
好 => 好
嗎 => 嗎

上面幾個是 Decimal NCR 十進位制的 NCR,下面放幾個 16 進制的 NCR 來比較看看

你 => 你
好 => 好
嗎 => 嗎

想要知道 10 進位跟 16 進位 NCR 的請前往維基百科,小蛙這邊簡單歸納自己的做法:

  1. 10 進制的 pattern 為 &#(wwwww);    -> w 為 10 進制數字
  2. 16 進制的 pattern 為 &#x(hhhh);         -> h 為 16 進制

Java 版本 NCR 處理

Java code 是大概寫一下,裡面沒有加入任何防呆跟錯誤處理,例如:Integer.parseInt 這邊有可能 parse 到不是 integer 而拋出錯誤,請務必注意(乖小孩要自己加上喔);或是 pattern 可以寫得更限縮一些。這次主要是要放上 VBA 的 Code,順道把 Java 的一起擺上來作為紀念幫助有需要的人,看出哪裡有問題的就 … 自己修改囉!也可以在下面留言給小蛙 ~ 感恩!

public static String convertDecimalNCRToString(String txt){
	Pattern p = Pattern.compile("&#(.*?);");
	Matcher m = p.matcher(txt);
	while(m.find()){
		String match = m.group(1);
		if(!match.startsWith("x")){
			txt = txt.replaceAll("&#" + m.group(1) + ";", "" + (char)Integer.parseInt(m.group(1)));
		}else{
			txt = txt.replaceAll("&#" + m.group(1) + ";", "" + (char)Integer.parseInt(m.group(1).replaceFirst("x", ""), 16));
		}
	}
	return txt;
}

VBA 版本 NCR 處理

放上 VBA 的 Code,大致上的概念是差不多的,不過這裡要注意,小蛙剛好有機會想說寫看看 VBA,New RegExp 用這個類別的時候,不知道怎麼用一直出現未定義的錯誤,後來才知道要去設定 References,參考這裡。另外,要列出最後一列的寫法是 Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row,最後一欄的寫法是 Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column,如果用到的是 excel 內建函數或方法的話,前面要加上 WorksheetFunction.。

2018-07-24 更新:

原本的寫法會造成 〹〹 這種連續 NCR 解析錯誤 Orz… 加上每次都要設定 References 感覺很麻煩,也改寫成 CreateObject(“vbscript.regexp”),這樣以後可以直接使用,不用再去設定 References。(注意:如果 excel 裡的 NCR 非常多,執行的時候會很慢,請耐心等待)

Sub ConvNCR()
    'Dim regex As New RegExp
    Dim regex As Object
    Set regex = CreateObject("vbscript.regexp")
    Dim i, j As Integer
    Dim s As String
    Dim r As String

    'pattern of Decimal NCR
    regex.Pattern = "&#((\w+));"
    regex.Global = True
    regex.IgnoreCase = True
    regex.MultiLine = True

    For i = 1 To Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
        For j = 1 To Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
            s = Cells(i, j).Value
            r = s
            If Not IsNull(s) Then
                If regex.Test(s) Then
                    c = regex.Execute(s).Count
                    For w = 0 To c - 1
                        t = regex.Execute(s)(w).SubMatches(0)
                        If InStr(t, "x") Then
                           'to deal with hex NCR ሴ
                            t = WorksheetFunction.Hex2Dec(Right(t, 4))
                        End If
                        'relace by real "word"
                        r = Replace(r, regex.Execute(s)(w), WorksheetFunction.Unichar(t), , 1)
                        Cells(i, j).Value = r
                    Next w
                End If
            End If
        Next j
    Next i
End Sub

以上,有需要的人夾去配吧!最後附上很有用的 編碼轉換工具 (iframe 內嵌於本站) 以及 Javascript 轉換方式

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *