POST запрос через Excel

POST запрос через Excel

Вводная часть

Это первая статья посвященная объединению сайта, вернее ботом что был создан, и программой Excel. В ней мы покажем, как можно пользоваться возможностями созданных ботов в совершенно, на первый взгляд, произвольных программах.

Здесь не будет  рассказываться о написании формул решения кубических и квадратных уравнений в ячейках или макросах программы. Это слишком банально и скучно. Речь о другом. Как с помощью Excel отправить POST запрос на сайт и обработать полученный JSON ответ.

Это очень интересная тема и с учётом все большего распространения распределенных, облачных вычислений и сервисов, будет всегда востребована.

Хотелось бы заметить, что таким образом вы, пользуясь сервисами по модели SaaS,  можете внедрять подобное решение,в  любое приложение, и в любую операционную систему которая Вам может придти в голову. Главное что бы был Интернет.

ФУНКЦИЯ  POST запроса  через Excel

Сама функция простая и листинг её представлен ниже:

Function jsonx(KEY, TXT As Range)
For Each ccc In TXT
body = body + " " + str(ccc.value)
Next
Dim sURL As String
Dim oHttp As Object
Set oHttp = CreateObject("MSXML2.ServerXMLHTTP")
sURL = "http://jabber.pozitiv-r.ru/scripts/reqexcel.php"
oHttp.Open "POST", sURL, False
oHttp.setrequestheader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
oHttp.setrequestheader "Content-Type", "application/x-www-form-urlencoded"
oHttp.send ("from=www&key=" + KEY + "&body=" + body)
result = oHttp.ResponseText
With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "\[(.+)\]"
        Set objMatches = .Execute(result)
        Dim a() As String
        a = Split(objMatches.Item(0).submatches(0), ",")
        Dim arr As Variant
        ReDim arr(0 To 3)
        For I = 0 To UBound(a)
        arr(I) = a(I)
        Next I
End With
jsonx = arr
End Function
 
Рассмотрим её по частям.
For Each ccc In TXT
body = body + " " + str(ccc.value)
Next
 
Полученный диапазон ячеек  преобразуется в строку  "значение пробел значение пробел и т.д."
 
Dim sURL As String
Dim oHttp As Object
Set oHttp = CreateObject("MSXML2.ServerXMLHTTP")
sURL = "http://jabber.pozitiv-r.ru/scripts/reqexcel.php"
oHttp.Open "POST", sURL, False
oHttp.setrequestheader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
oHttp.setrequestheader "Content-Type", "application/x-www-form-urlencoded"
oHttp.send ("from=www&key=" + KEY + "&body=" + body)
result = oHttp.ResponseText
 
Далее мы формируем POST запрос нашему боту с необходимыми параметрами: Адресом страницы где обитает бот(http://jabber.pozitiv-r.ru/scripts/reqexcel.php), необходимыми заголовками oHttp.setrequestheader, и непосредственно замим запросом который содержит необходимые ключи и входные данные oHttp.send ("from=www&key=" + KEY + "&body=" + body)
 
Получаем ответ от сервера в переменную result = oHttp.ResponseText
 
Ответ приходит в JSON формате и к сожалению,  я не нашел, за короткое время, нормального, универсального, без внешних библиотек парсера JSON запроса. Удивительна недальновидность сотрудников Microsoft которые не включили в язык VBA (Visual Basic for Application) поддержку JSON, на протяжении уже стольких лет. 
 
Поэтому ответ  начинаем парсить с помощью регулярных выражений и за это у нас отвечает блок
With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "\[(.+)\]"
        Set objMatches = .Execute(result)
 
 
Для тех кто знаком с регулярными выражениями этот блок окажется очень простым и банальным. Для тех же, кто с ними не знаком, объясним, что мы из всего JSON  запроса  выбираем данные которые заключены между двух квадратных кавычек. Этими данными являются корни уравнения
так как корни уравнения разделены запятой то, разобъем по этому символу полученную строку и запишем результат в массив
       
         Dim a() As String
 
        a = Split(objMatches.Item(0).submatches(0), ",")
 
То есть в массиве  у нас хранятся корни уравнения
А далее мы  эти корни уравнения просто записываем в ячейки откуда эта функция была вызвана.
 
        Dim arr As Variant
        ReDim arr(0 To 3)
        For I = 0 To UBound(a)
        arr(I) = a(I)
        Next I
End With
jsonx = arr
 
Скриншот экрана  вычисления уравнений  в Excel  Вы видите ниже
 
POST запросы в Excel
 
А сама функция представлена была выше.
 
В чем прелесть? в том, что незная, ни одной формулы, не погружаясь в мелочность расчетов и выверки их, Вы можете простой функцией  получить ответ от онлайн калькулятора в свой проект ( в нашем примере Excel проект)
 

Описание функции 

 
Давайте рассмотрим еще поподробнее какие же входные данные   есть у нашей функции?
 
Function jsonx(KEY, TXT As Range)
есть KEY  - это ключ. В нашем примере это ключ "ur" который говорит функции что надо получить ответ именно от бота который считает уравнения.
есть TXT  - это диапазон ячеек, где хранятся входные данные.
 
Ключ может быть любой. Например поставив ключ op_i мы сможем рассчитывать определитель комплексной матрицы, поставив ключ treug мы сможем создать в Excel универсальный "решатель" треугольников. 
Список ключей можно получить по разному. Через XMPP клиента, достаточно послать два знака вопроса ??
Через Веб сайт, на каждой странице в разделе синтаксис есть ключ, который Вам нужен.
 
Таким образом Вы, обладая необходимыми ключами и синтаксисом написания запроса, можете погрузится в область облачных вычислений базируясь на офисной программе Excel. 
 
Для тех, кто хорошо разбирается в VBA не составит труда расширить функционал, например приведя комплексные корни в нормальный вид ( автору не хватило терпения это доделать.. все таки VBA не моё :) )
Для всех остальных это позволит открыть возможность изучать, творить и создавать свои  небольшие облачные вычисления. 
 
И наш сайт  и автор  готов всегда помочь в стремлении к развитию человека.
 
И напоследок домашнее задание:
 
Попробуйте  с помощью вышеуказанной функции, не обладая ни одной, из математических формул
 
- посчитать комплексную обратную матрицу в Excel 
- посчитать площадь многоугольника в Excel 
- посчитать систему комплексных линейных уравнений в Excel
 
Поиск по сайту