ExcelでVBAを書かずにJSON形式で返却されるはてブ数を取得

知り合いから「Excel 2013で追加された「WEBSERVICE」関数を使って、マクロを使わずWeb APIを利用する。」を教えて頂いたのでそれを応用。

また「はてブAPIはJSON形式で値を返却*1」と「ExcelでJSONを扱おうとするとVBAが必要っぽい」という条件があわさったので、いっちょj2xproxy.pyというJSONをXMLに変換するプロキシをPythonで書きました。

と言ってもJSONからXMLへ変換するロジックはGitHubのここにあるものをそのまま使用しています。もちろん以下のようにIronPythonからでも実行可能です。

"C:\Program Files (x86)\IronPython 2.7\ipy.exe" j2xproxy.py

どのような感じになるか

Excel 2013で追加されたFILTERXML関数と、WEBSERVICE関数を使用します。関数の詳しい説明は元ネタブログを参照ください。

で、動作としてはWEBSERVICE関数を使って一旦「127.0.0.1:8111」上のJSON2XMLプロキシへリスエストを投げて、取得したXMLをFILTERXML関数でxpathパースしてブックマーク数をセルに表示しています。

ただしブックマーク数が多いと返却されるXMLのサイズが、WEBSERVICE関数の文字制限に引っかかるため正常に取得出来ません。

j2xproxy.py（JSON形式で返却されたWEBサービスからの戻り値をXML形式に変換するプロキシ）

#!/usr/bin/env python
#
# JSON2XML Proxy
# Usage: j2xproxy.py
#
# URL: http://127.0.0.1:8111/fname?reload={true|false}&url=http://...
#   -> Get json data, then save it to fname as XML. So you can get 
#      json data as XML by just GET request.
# queries
#   reload: true|false
#     If true, always get fresh content. Otherwise search downloaded
#     content before. Default value is "false".
#   url: string
#     URL has to respond plain json data which means it's not allowed
#     callback or something like that. This can be both URL encoded and
#     not encoded one.

import os, sys, urllib2, urlparse, json
from SimpleHTTPServer import BaseHTTPServer, SimpleHTTPRequestHandler
from xml.dom.minidom import Document

def get_content(url, fname):
  print "Getting content from %s, then save it to %s as XML."%(url,fname)
  content = urllib2.urlopen(url).read()
  jsondata = json.loads(content)
  doc = parse_doc("json", jsondata)
  fp = open(fname,"w")
  fp.write(doc.toprettyxml(encoding="utf-8", indent="  "))
  fp.close()

# From https://github.com/axet/json2xml/blob/master/json2xml.py
def parse_element(doc, root, j):
  if isinstance(j, dict):
    for key in j.keys():
      value = j[key]
      if isinstance(value, list):
        for e in value:
          elem = doc.createElement(key)
          parse_element(doc, elem, e)
          root.appendChild(elem)
      else:
        if key.isdigit():
          elem = doc.createElement('item')
          elem.setAttribute('value', key)
        else:
          elem = doc.createElement(key)
        parse_element(doc, elem, value)
        root.appendChild(elem)
  elif isinstance(j, str) or isinstance(j, unicode):
    text = doc.createTextNode(j)
    root.appendChild(text)
  elif isinstance(j, numbers.Number):
    text = doc.createTextNode(str(j))
    root.appendChild(text)
  else:
    raise Exception("bad type '%s' for '%s'" % (type(j), j,))

# From https://github.com/axet/json2xml/blob/master/json2xml.py
def parse_doc(root, j):
  doc = Document()
  if root is None:
    if len(j.keys()) > 1:
      raise Exception('Expected one root element, or use --root to set root')
    root = j.keys()[0]
    elem = doc.createElement(root)
    j = j[root]
  else:
    elem = doc.createElement(root)
  parse_element(doc, elem, j)
  doc.appendChild(elem)
  return doc

class CustomHTTPRequestHandler(SimpleHTTPRequestHandler):
  def do_GET(self):
    # Parse request URL
    o =urlparse.urlparse(self.path)
    fname = o[2].split("/")[-1]
    params = dict([urllib2.splitvalue(x) for x in o[4].split("&")])
    url = params.get("url",None)
    reld = params.get("reload",False)
    if reld:
      reld = reld.lower()=="true" and True or False
    # Handle request
    if url:
      if not os.path.exists(fname) or reld:
        get_content(urllib2.unquote(url), fname)
    # Call standard GET procedure
    SimpleHTTPRequestHandler.do_GET(self)

def main():
  listenport = 8111
  listen = ('127.0.0.1', listenport)
  httpd = BaseHTTPServer.HTTPServer(listen, CustomHTTPRequestHandler)
  httpd.serve_forever()

if __name__=="__main__":
  main()

やってみた感想

Excel自体はものの数分で出来上がったので楽ちんでした。サムネイルのURLも簡単に取得できたのですが、Excelへの画像埋め込み方法が分からずじまいだったので触れずに終了です。

*1:書き終わった後ではてなブックマーク件数取得APIを見つけたけどj2xproxy.pyが無駄になるので全力で無視