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

放上 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 非常多,執行的時候會很慢,請耐心等待)

以上,有需要的人夾去配吧!最後附上很有用的 編碼轉換工具 以及 Javascript 轉換方式

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *