Excel里内嵌在线翻译

本来寻思着继续写点系统运行日志跟踪技术的，但早晨哥家领导从单位打来电话，让帮助她的闺蜜搞一个excel翻译的问题，总部IT搞不定。我过去是用excel做了几年工作，却都是些数学计算，跟翻译也扯不上啊；领导交代的任务，还是要认真对待，要不然下个月零花钱没着落！幸好，最后被哥顺利搞定，晚上领导很开心，话今天倍有面子，公司总部热烈谈论技术男的厉害。唉，哥用十多年时间来揣摩如何做嵌入式系统，没得到几个小红花；而每每耍些雕虫小技，却能迎来一堆粉丝，真不知是喜是悲了。拿出来分享下，或许能帮到更多人。

领导交代完不一会，收到具体问题和要求。看上去没什么，就纳闷几个IT是不是偷懒来着。下面是那个要做的工作的图：

Excel表里，产品名称是日文，韩文的，为了方便不懂外文的同事，要增加一列中文翻译。公式呢，她们从网络上找到了：
：=FILTERXML(WEBSERVICE("http://fanyi.youdao.com/translate?&i="&A1&"&doctype=xml&version"),"//translation")，
但问题是，英文的蛮好，其它语言日文韩文等都不好使。哥的任务就是解决这个多语言问题。哥平常搞惯了嵌入式系统和后台程序，网络搞的确实不多，应该还能凑合应对。

直觉告诉我，需要在URL上加上语言选项。于是乎，先将这段http地址放到浏览器里，看看得到的xml是什么。

<response type="EN2ZH_CN" errorCode="0" elapsedTime="1">
  <input>
    <![CDATA[ " ]]>
  </input>
  <translation>
    <![CDATA[ ” ]]>
  </translation>
</response>

很显然，EN2ZH_CN就是那个翻译语言选项，我得先找到其它语言是怎么定义的，然后才能考虑如何提交这些额外参数。

打开有道的在线翻译，查看它的源码，找到了这些：

<ul id="customSelectOption">
<li class="on"><a val="AUTO" href="./在线翻译_有道_files/在线翻译_有道.html">自动检测语言</a></li>
<li><a val="ZH_CN2EN" href="./在线翻译_有道_files/在线翻译_有道.html">中文　»　英语</a></li>
<li><a val="ZH_CN2JA" href="./在线翻译_有道_files/在线翻译_有道.html">中文　»　日语</a></li>
<li><a val="ZH_CN2KR" href="./在线翻译_有道_files/在线翻译_有道.html">中文　»　韩语</a></li>
<li><a val="ZH_CN2FR" href="./在线翻译_有道_files/在线翻译_有道.html">中文　»　法语</a></li>
<li><a val="ZH_CN2RU" href="./在线翻译_有道_files/在线翻译_有道.html">中文　»　俄语</a></li>
<li><a val="ZH_CN2SP" href="./在线翻译_有道_files/在线翻译_有道.html">中文　»　西语</a></li>
<li><a val="EN2ZH_CN" href="./在线翻译_有道_files/在线翻译_有道.html">英语　»　中文</a></li>
<li><a val="JA2ZH_CN" href="./在线翻译_有道_files/在线翻译_有道.html">日语　»　中文</a></li>
<li><a val="KR2ZH_CN" href="./在线翻译_有道_files/在线翻译_有道.html">韩语　»　中文</a></li>
<li><a val="FR2ZH_CN" href="./在线翻译_有道_files/在线翻译_有道.html">法语　»　中文</a></li>
<li><a val="RU2ZH_CN" href="./在线翻译_有道_files/在线翻译_有道.html">俄语　»　中文</a></li>
<li><a val="SP2ZH_CN" href="./在线翻译_有道_files/在线翻译_有道.html">西语　»　中文</a></li>
</ul>

就是这些，不费功夫，日文转中文就是JA2ZH_CN嘛！开始往URL里添加这个额外参数。哥也不知道他们是怎么定义的，只能猜猜猜。这个过程挺无聊的，先对着那个在线翻译，找可能的定义方式，试了好久也没成功。最后，看着那个返回xml结果，突然想，他们不会是把请求和结果定义成一个样子吧？

果然，就是返回结果里的type字段。不过还是没法高兴。日文，韩文，法文全都没反应。哥开始怀疑，是不是type字段也不是我想象的语言控制字段。

继续做实验，于是有了下面两个url访问：

http://fanyi.youdao.com/translate?&type=EN2ZH_CN&i="who?谁"&doctype=xml&version
运行结果：

<response type="EN2ZH_CN" errorCode="0" elapsedTime="7">
<input>
<![CDATA[ "who?谁" ]]>
</input>
<translation>
<![CDATA[ “谁?谁” ]]>
</translation>
</response>

 

http://fanyi.youdao.com/translate?&type=ZH_CN2EN&i="who?谁"&doctype=xml&version

运行结果：

<response type="ZH_CN2EN" errorCode="0" elapsedTime="6">
<input>
<![CDATA[ "who?谁" ]]>
</input>
<translation>
<![CDATA[ "Who the who?" ]]>
</translation>
</response>

得，哥被人耍了，有道这个页面压根就没给提供中英之外的选择；哥需要另外找翻译服务。首先想到的是百度，查了一下，baidu提供翻译的API，接口还蛮简单的；当然，也查了下youdao，他们也有API。对比之后发现，两家差不多，只是语法上略微差别。于是乎，让她们申请ID先。很简单，免费的，几分钟后就给我发来了账户信息。

继续开工，做出了下面这个URL（neverland是网上找到的账户，人家介绍怎么用有道API，哥借用下。同学们记得谢谢neverland的奉献，要用的多，自己申请哈）：
http://fanyi.youdao.com/openapi.do?keyfrom=neverland&key=969918857&type=data&doctype=xml&version=1.1&q=キラキラリボンカチューシャ
***注意咯，q后面的待翻译文本，不能用引号，否则不翻译。***
得到翻译结果：

<youdao-fanyi>

<errorCode>0</errorCode>

<query>

<![CDATA[ キラキラリボンカチューシャ ]]>

</query>

<!-- 有道翻译 -->

<translation>

<paragraph>

<![CDATA[ 闪闪发光的丝带发箍 ]]>

</paragraph>

</translation>

</youdao-fanyi>

万事俱备，开始修改excel的公式，如下：

=_xlfn.FILTERXML(_xlfn.WEBSERVICE("http://fanyi.youdao.com/openapi.do?keyfrom=neverland&key=969918857&type=data&doctype=xml&version=1.1&q="&F3&""),"//paragraph")

咦，咋还是不好使？英文的可以了，其它的全都不行！嗯，应该是编码问题。URL是必须ascii八位元的，除了英文，其它文字都是unicode或是其它宽字符编码的，需要转换下格式。查了下帮助，URLENCODE函数可以帮助解决，如下：
=_xlfn.ENCODEURL(F3)

再修改翻译公式，得到：
=_xlfn.FILTERXML(_xlfn.WEBSERVICE("http://fanyi.youdao.com/openapi.do?keyfrom=yamaxun&key=1400504075&type=data&doctype=xml&version=1.1&q="&H3&""),"//paragraph")
注意咯，这里编码和翻译没法合并，具体原因自己思考！

OK，传改完的excel过去，打完收工。剩下的就是excel的拖动复制工作，不用哥多嘴罗嗦。