Microsoft EXCEL 2013: Использование WEBSERVICE, FILTERXML и ENCODEURL

EXCEL 2013

В Microsoft Excel 2013 появились новые функции для работы с Веб-сервисами:

• WEBSERVICE (ВЕБСЛУЖБА)
• FILTERXML (ФИЛЬТР.XML)
• ENCODEURL (КОДИР.URL)

Все вместе эти функции используются общения с различными веб-сервисами. В том числе и для расшифровке ответов.

Как узнать по адресу в какой стране этот адрес находится

Попалась мне задача сопоставить адрес со страной. Так вот, одним из вариантов решения задачи является использование какого-то веб-сервиса. На входе адрес в виде строки, а на выходе, страна. И так для каждого адреса в Excel-таблице.

В качестве такого веб-сервиса попался Yahoo! Для начала работы необходимо получить Application ID и проверить права на использование сервиса.

За дело!

Используем WEBSERVICE(“полный URL вместе с параметрами“), чтобы получить информацию на основании строки адреса. А чтобы в URL закодировать параметры, надо использовать ENCODEURL.

Получаем что-то типа вот этого:

=WEBSERVICE("http://where.yahooapis.com/v1/places.q('"& ENCODEURL([@Address]) &"');start=0;count=5?appid=_YKSHRFGKHkerhguerg85egKJGHKDFT8giQ-")

AppID явно заменён на рандомный набор символов.

В результате получаем вот такой XML:

xml version=""1.0"" encoding=""UTF-8""?>

<places xmlns=""http:=""//where.yahooapis.com/v1/schema.rng"" xmlns:yahoo=""http://www.yahooapis.com/v1/base.rng"" yahoo:start=""0"" yahoo:count=""1"" yahoo:total=""1"">

<place yahoo:uri=""http:=""//where.yahooapis.com/v1/place/26629944"" xml:lang=""en-US"">

  <woeid>26629944</woeid>

  <placeTypeName code=""11="""">Postal Code</placeTypeName>

  <name>YO1 7PA</name>

  <country type=""Country="""" code=""GB="""" woeid=""23424975="""">United Kingdom</country>

  <admin1 type=""Country="""" code=""GB-ENG="""" woeid=""24554868="""">England</admin1>

  <admin2 type=""County="""" code=""GB-NYK="""" woeid=""12602195="""">North Yorkshire</admin2>

  <admin3 type=""Local="" Administrative="" Area="""" code="""" woeid=""12696148="""">York City</admin3>

  <locality1 type=""Town="""" woeid=""41415="""">York</locality1>

  <locality2></locality2>

  <postal type=""Postal="" Code="""" woeid=""26629944="""">YO1 7PA</postal>

  <centroid>

    <latitude>53.958382</latitude>

    <longitude>-1.074100</longitude>

  </centroid>

  <boundingBox>

    <southWest>

      <latitude>53.958080</latitude>

      <longitude>-1.074890</longitude>

    </southWest>

    <northEast>

      <latitude>53.958630</latitude>

      <longitude>-1.072840</longitude>

    </northEast>

  </boundingBox>

 <areaRank>0</areaRank>

 <popRank>0</popRank>

 <timezone type=""Time="" Zone="""" woeid=""28350903="""">Europe/London</timezone>

</place></places>

Теперь расшифровываем полученный XML.

=FILTERXML([@WebService],"places/place/country")

Вот и получаем вот такую чудную картинку:

Проблемы

Прилуки не только в Украине, но и в Беларуси. И этот адрес я брал из карты Беларуси. И таких случаев достаточно. С другой стороны, понаплодили Парижей, да Бостонов, панимаешь!

P.S. Нашел еще вот такую статью http://scriptogr.am/richie/post/using-viralheats-sentiment-analysis-api-through-excel-2013