NCR &#xxxxx; 處理方式：Java & VBA
工作上常常需要把資料庫的東西匯出，但有一些資料是以 NCR (Numeric Character Reference) 的方式儲存或呈現，這些在網頁上看都沒有什麼問題，但在 Excel 裡面「你好嗎」如果是 NCR 就會看到這樣「你好嗎」，這邊記錄用 VBA 跟 Java 個別的處理方式。
先來搞清楚 NCR 的構成吧！大致上會是以 &# 開頭，中間串接個數字，最後以分號 ; 結尾，大概就是像這樣
你 => 你
好 => 好
嗎 => 嗎
上面幾個是 Decimal NCR 十進位制的 NCR，下面放幾個 16 進制的 NCR 來比較看看
你 => 你
好 => 好
嗎 => 嗎
想要知道 10 進位跟 16 進位 NCR 的請前往維基百科，小蛙這邊簡單歸納自己的做法：
- 10 進制的 pattern 為 &#(wwwww); -> w 為 10 進制數字
- 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 轉換方式。
Excel 相關文章：