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

DatePicker в Excel

Использовал я в одном проекте DTimePicker который уже версии 6.0 и является ActiveX компонентом. Вроде и проверил я его наличие "по-умолчанию" на многих компьютерах, включая самые древние, да видать не на всех... Вобщем, проблема - оказывается существуют древние компьютеры на которых не установлен этот компонент.

А как обойтись без ActiveX ?

А очень просто! http://forums.techguy.org/business-applications/1003518-solved-excel-vba-datepicker-replaces.html
Это полный аналог Date Picker которые на 100% реализован средствами VBA
Просто скопировал код в свой проект, поменял кое-какие методы управления - и теперь мой проект не зависит от наличия или отсутствия каких-то там ActiveX!

PS. Когда будете переводить на свой язык, вот вам подсказка - http://support.microsoft.com/kb/213833

Выгрузка данных в Excel с использованием OpenXML PowerTools

Хочу поделиться. Довольно часто возникают задачи по выгрузке большого количества данных в Excel-файл. И если это делать поячеесто, то времени больше тратиться на Excel, а не на само получение данных. Ну конечно есть еще такие способы как работа с целым диапазоном (.Range) но тут мне встретилась в Сети вот такая библиотека.


OpenXML PowerTools

Как бороться с офисным хламом

Есть такой вид файлов - офисный хлам. Большей частью это развлекательные файлики. Фильмы, музыка, презенташки, картинки, сборники анекдотов...


Как сделать так чтобы пользователи не хранили эти файлы у себя?

Автоматическая отправка документов клиентам по электронной почте

Обычная бизнес-задача: При формировании расходной накладной отправлять клиенту по электронной почте XLS-файл с электронной копией этой расходной накладной.

Плёвая задача! - сказали программисты
..и забацали следующий алгоритм: