W celu tłumaczenia na inne języki, skorzystaj z Google Translatora.
Witryna ta jest optymalizowana pod tłumaczenie automatyczne – używamy prostego języka. Gdyby coś było niejasne – prosimy o informację zwrotną.
Uwaga – po tłumaczeniu automatycznym, przykłady mogą nie działać poprawnie.

O Usługach Sieciowych AFIN (AFIN Web Services, AFinWS)

Idea AFinWS w skrócie

Po co ładować całą stronę z kursami walut? Weź tylko ten, Ciebie interesujący!
To samo z ceną jakiegoś produktu z marketu lub aukcji internetowej. Funkcja do Wikipedii? Do kursów akcji? Do prognozy pogody? On-line? W każdym Excelu, nawet korporacyjnym (zabezpieczenia stacji roboczej)? Czemu nie? Zobacz przykłady usługi GETDATATEXT.
=webservice(http://afin.net/ws/getdatatext.php?key=0123456789&url=https://internetowykantor.pl/kurs-franka/&rgx=actual-type.{1,20}&rpl2=[^0-9,]*(:))

Przekaż dane z jednego komputera na drugi - natychmiast, bez mejli, logowań, chmur. Sprawdź usługi PUTVALUE https://tiny.pl/ttwbg i GETVALUE https://tiny.pl/ttwbt .

Użyj funkcji SŁOWNIE (INWORDS)!
Myślisz, że w Excelu, Libre Office Calc albo Google Sheets nie ma funkcji „SŁOWNIE(liczba)”, która daje tekst, potrzebny na każdej fakturze? Mylisz się, jest taka funkcja – zobacz usługę INWORDS.
W Excelu - wpisz w komórkę Excela formułę: =WEBSERVICE("http://afin.net/ws/inwords.php?key=0123456789&val=123,45&curr=EUR")
W Google Sheets - wpisz w komórkę Arkuszy Google formułę: =IMPORTDATA("http://afin.net/ws/inwords.php?key=0123456789&val=123,45&curr=EUR")
W Libre Office Calc - wpisz w komórkę formułę: =USŁ.INTER("http://afin.net/ws/inwords.php?key=0123456789&val=123,45&curr=EUR")
W każdym z powyższych przypadków otrzymasz wartość tekstową: „one hundred and twenty three EUR 45/100”. Normalnie, żaden z tych arkuszy takiej funkcji nie oferuje.

Wykorzystaj Internet nie do konsumpcji informacji tak, jak chcą tego jej producenci (czyli twórcy stron i serwisów www), tylko tak, jak Ty chcesz.

MANIFEST. Dlaczego powstały Usługi Sieciowe AFIN?

Internet jest fajny, ale jakiś taki niedorobiony – zawsze daje nam ładnie opakowaną, ale zbyt przeładowaną, informację.
I to do przeglądarki. A my chcemy z aktualnej informacji internetowej korzystać np. w arkuszach kalkulacyjnych – dlatego właśnie stworzyliśmy Usługi Sieciowe AFIN (AFIN Web Service).
Chcemy z informacji internetowej korzystać wybiórczo, czyli brać tylko to, co chcemy i o co my zapytamy – tak, jak to się dzieje w architekturze „klient-serwer” w bazach danych.
„Chmura” nie jest rozwiązaniem samym w sobie – jest tylko rozwinięciem idei „paczki danych” (strony internetowej, skoroszytu, tabeli przy zapytaniu OLEDB, pliku tekstowego - tzw. pliku płaskiego), dowolnego innego pliku, czy też horrendalnie nieprawidłowego w sensie architektury „klient-serwer”, „obiektu połączenia danych”, znanego z Microsoft Excel Power Pivot, Power Query, itd.)
Ba, my chcemy informację internetową TWORZYĆ(!), czyli zapisywać NASZE WARTOŚCI tak, żeby przez Internet były natychmiast widoczne dla innych. Po to ludzie wymyślili i wdrażają „chmury”, po to miliony analityków w korporacjach i nie tylko przesyłają między sobą gigabajty arkuszy kalkulacyjnych. Czy muszą? Odpowiedź znajdziesz poniżej.

Wykorzystujemy tutaj prostą i naczelną zasadę, że tylko URL jest jawny i tylko owym URL-em się posługujemy w wymianie informacji. Bo URL-a czyta dowolna przeglądarka oraz – uwaga! – standardowa funkcja Excela od wersji 2013 o nazwie WEBSERVICE(url), funkcja Google Sheets o nazwie IMPORTDATA(url). Zapewne będą inne, kolejne – to trend bardzo rozwojowy – od niedawna, funkcja taka jest też w Libre Office Calc i nosi nazwę USŁ.INTER(url) .

(Funkcje dodatku AFinScript: GETDATATEXT, GETDATAXML, GETDATAJSON i inne robią to od lat i to również w starszych wersjach Excela, dostarczając dodatkowej funkcjonalności. Zapraszamy na http://afin.net/afinscript )

Próbujemy wszystko parametryzować, żeby odczyt był jak najbardziej zgodny z naszą potrzebą. Parametryzować standardowymi metodami arkusza kalkulacyjnego, czyli jak najprościej.
Nie stosujemy żadnych działań ukrytych poza wybiórczym tworzeniem logu zapytań w wersji darmowej AFinWS.

Serwis podlega ciągłym modyfikacjom, nie gwarantujemy stałości adresów URL ani nazw, czy też funkcjonalności AFinWS. Docelowo usług będzie znacznie więcej.
Jeżeli podoba Ci się to, co widzisz, masz szanse kupić abonament na AFinWS już teraz, w bardzo promocyjnej cenie. Skontaktuj się z nami.

Przy tworzeniu AFinWS my się nieźle bawimy, czego życzymy również Tobie.
http://ft.com (podmieniony tekst o cookies na stronie Financial Times – zrób komuś podobny żart)

Bardzo-bardzo szybki efekt, czyli na skróty (ćwiczenie na ok. 2 minuty)

1. Pobierz plik http://afin.net/ws/samples/putgetvalue.zip . Rozpakuj go gdziekolwiek – są w nim dwa skoroszyty Excela – PUTVALUE.XLSX i GETVALUE.XLSX .
Testowe skoroszyty NIE ZAWIERAJĄ MAKR ANI ŻADNYCH ELEMENTÓW UKRYTYCH – Excel, przy otwieraniu, najpierw, wyświetli komunikat, że pliki pochodzą z lokalizacji internetowej, co jest oczywiste – potwierdź to ostrzeżenie. Jeżeli Excel wyświetli komunikat, że skoroszyt zawiera NIEZNANĄ funkcję WEBSERVICE oznacza to, że nie masz wersji Excela 2013 lub wyższej. Jeżeli wyświetli ostrzeżenie O WYŁĄCZENIU funkcji WEBSERVICE – zaakceptuj je – funkcja WEBSERVICE jest STANDARDOWĄ funkcją Excela od wersji „2013” wzwyż. Uwaga - funkcja ta nie jest dostępna w aplikacji Excel ani na komputerach firmy Apple, ani w aplikacjach webowych Excel na urządzeniach mobilnych – tam jednak również można przetestować tę funkcjonalność, po prostu wpisując pełny URL w pasek adresu przeglądarki internetowej. Jeżeli coś uporczywie idzie nieprawidłowo, skorzystaj z przykładu prostego funkcji „putvalue” i „getvalue” poniżej. Jeżeli posiadasz Excela 2010 lub wcześniejszą wersję 2007, również możesz uruchomić AfinWS, sposób jest opisany w tym dokumencie.

2. Otwórz te pliki Excelem, ale – uwaga – możesz to zrobić na dowolnych komputerach świata, mogą to być różne, nawet bardzo oddalone fizycznie, komputery, w dowolnych sieciach, byle te komputery były podłączone do Internetu. Polecamy, oczywiście, działanie na dwóch różnych komputerach, najlepiej w różnych sieciach lokalnych, ale jeżeli chcesz zrobić szybki test, może to być jeden – Twój – komputer. Pliki otworzone? Jesteś gotowy?

3. W skoroszycie putvalue.XLSX wpisz w żółte pole dowolną, TWOJĄ WARTOŚĆ liczbową (wymyśl jakąś, dowolną, liczbę, np. 20) i naciśnij [ENTER] – zatwierdziłeś wartość, która jest parametrem funkcji WEBSERVICE(url) w komórce poniżej – ta komórka z funkcją powinna zmienić wartość na tekst „twoja_wartość (MyValue)”, czyli „20 (MyValue)”. Jeżeli funkcja nie zmieniła wartości – oznacza to, że masz włączone „przeliczanie ręczne” – naciśnij więc skrót klawiszowy [Ctrl+Alt+F9]. W tym skoroszycie to wszystko, możesz go zamknąć, a nawet usunąć z dysku. Skoroszyt PUTVALUE.XLSX nie jest już potrzebny, wykonał już swoją pracę – przekazał twoją wartość do tymczasowego magazynu wartości AfinWS.

4. W skoroszycie GETVALUE.XLSX wartość w zielonym polu nie zmieni się automatycznie, naciśnij (naciskaj by odświeżyć) skrót klawiszowy [Ctrl+Alt+F9]. Funkcja powinna się odświeżyć i wyświetlić Twoją wartość (tj. ciąg znaków „20”, które Excel rozpozna jako liczbę). To wszystko, koniec testu.

5. Połączyłeś dwa skoroszyty z zupełnie innych światów ze sobą. Jeżeli zrobiłeś to na jednym komputerze – to tylko przypadek. Możesz to zrobić na dwóch, dowolnych komputerach. Prosto, szybko. Po co wysyłać wielomegabajtowe skoroszyty mejlem? Poczytaj dalej i, oczywiście, testuj do woli.

Użycie Usług Sieciowych AFIN

Przeglądarka internetowa (dowolna)

W dowolnej przeglądarce internetowej – poprzez zwykłe wpisanie lub kliknięcie URL-a, np. http://afin.net/ws/userinfo.php?key=0123456789&inf=ip.
Można do tego celu wykorzystać również formularze webowe, np. http://afin.net/ws/forms/put , http://afin.net/ws/forms/get lub http://afin.net/ws/forms/getdatatext

Microsoft Excel

W programie Microsoft Excel w wersjach od „2013” wzwyż, funkcjonalności AfinWS można używać poprzez użycie standardowej funkcji WEBSERVICE(url) – jako normalnej (!) funkcji arkuszowej – np. =WEBSERVICE(http://afin.net/ws/userinfo.php?key=0123456789&inf=ip) – jest to excelowa funkcja do wyświetlania twojego adresu IP. Jeżeli funkcja zwraca wartość liczbową, nie zwraca jej jako liczbę w sensie formatu danych Excela, tylko jako ciąg znaków, ale gdy ma ona postać np. „2,01” to w Excelu, gdy format separatora dziesiętnego liczby w systemie to „,” (przecinek), Excel przedstawi ją jako liczbę. Gdy tak nie jest, warto doprowadzić (za pomocą argumentu „rpl2”, opisanego w tym dokumencie, format liczby do postaci „2,01” a następnie użyć zagnieżdżenia funkcji WEBSERVICE() w funkcji WARTOŚĆ(), czyli =WARTOŚĆ(WEBSERVICE(URL)). Wtedy Excel przyjmuje to jako liczbę.

Można też użyć usług jako „wejścia”, czyli URL dla tzw. kwerend webowych, dostępnych zarówno w starszych, jak i nowszych wersjach Excela, w tym w programie Power Query, który do tematu kwerend nic nowego nie wnosi, ale tu ludzie szukają.

Jeżeli Twój Excel jest w wersji niższej, niż 2013, np. 2010, 2007, 2003 i nie posiada funkcji „WEBSERVICE” również możesz używać AfinWS. Funkcję WEBSERVICE() można zastąpić poprzez funkcję UDF – WEBSERVICEbyAF(), napisaną w VBA, którą trzeba wpisać w moduł VBA – sposób opisano w tym dokumencie. Zwraca ona wynik wywołania danego URL, czyli imituje funkcjonalność funkcji WEBSERVICE().

UDF: WEBSERVICEbyAF() - Gotowa funkcja VBA, zastępująca funkcję WEBSERVICE() w starszych wersjach Excela, działająca identycznie

Kod funkcji WEBSERVICEbyAF(strURL) © AFIN, 2019
Uwaga: Tekst „{double quotation mark}”, po przekopiowaniu do Edytora Visual Basic, musi być zamieniony na jeden znak - podwójny cudzysłów. Skrót klawiaturowy: [Ctrl+H].

'>This function calls the specified URL and returns its answer. This is a replacement for WEBSERVICE() function in latest Excel versions.

Function WEBSERVICEbyAF(ByVal strUrl As String)

'*****************************************************************

'* Copyright: AFIN Wojciech Gardzinski 2019, Wroclaw, Poland, EU *

'*****************************************************************

Dim loHttpObj As Object

On Error GoTo LErr0

Application.Volatile True

Set loHttpObj = CreateObject({double quotation mark}MSXML2.XMLHTTP{double quotation mark})

'Set loHttpObj = CreateObject({double quotation mark}WinHttp.WinHttpRequest.5.1{double quotation mark}) 'This is an another object to use, if the previous one fails.

loHttpObj.Open {double quotation mark}GET{double quotation mark}, strUrl, False

loHttpObj.send

WEBSERVICEbyAF = loHttpObj.responseText

Exit Function

LErr0:

WEBSERVICEbyAF = "WEBSERVICEbyAF:" & Err.Description

End Function

Google Sheets

W formułach Google Sheets, poprzez użycie standardowej funkcji IMPORTDATA(url) – należy uważać, żeby w wyniku działania funkcji nie pojawił się „,” (przecinek) albo tabulator, gdyż wtedy funkcja IMPORTDATA zapisze wynik w dwóch (lub więcej) kolumnach. Gdy nie będzie mogła tego zrobić, bo np. kolumny po prawej są zajęte – wyświetli błąd „REF”. Sposób wyeliminowanie tego błędu – poprzez użycie parametru rpl2 – zobacz usługa GETDATATEXT.
UWAGA – w funkcjach Google Sheets URL wynik jest cache-owany, czyli przeliczenie funkcji wymaga jego URL-a. Jak to zrobić, jest wytłumaczone w dokumencie.

Libre Office

W Libre Office Calc poprzez użycie funkcji WEB.SERVICE (pol. USŁ.INTER), z identycznymi parametrami.
UWAGA – w funkcjach Libre Office URL wynik jest cache-owany, czyli przeliczenie funkcji wymaga jego URL-a. Jak to zrobić, jest wytłumaczone poniżej, wyszukaj tekst „forcecalc=”.

Open Office (Oracle, Apache, etc)

Open Office jest projektem przejętym przez firmę Oracle i, jako taki, ulega powolnemu wygaszeniu. Open Office Calc funkcjonalności funkcji WEBSERVICE() nie obsługuje. Być może się to zmieni. Możemy dostarczyć makro OpenOffice, które tę funkcjonalność zapewnia.

Uwagi ogólne nt. przeliczania funkcji w arkuszach kalkulacyjnych, argument „forcecalc=”

Formuły w arkuszach kalkulacyjnych można dalej zagnieżdżać lub dowolnie komplikować itd., zgodnie z zasadami użycia formuł w arkuszach kalkulacyjnych. Formuły takie można też DOWOLNIE PARAMETRYZOWAĆ, na zasadzie zwykłej konkatenacji (łączenia tekstów) w argumencie funkcji, w tym argumencie URL-a – poczytaj o tym w Pomocy Google Sheets, Pomocy Excela lub w dowolnym innym źródle informacji – jest to umiejętność powszechnie znana.

W arkuszach kalkulacyjnych występuje pewien problem, nieznany raczej użytkownikom normalnych funkcji Excela (i innych), a mianowicie problem, znany w VBA - „Application.VOLATILE” (tak się ten temat, metoda obiektu Application, nazywa w excelowym VBA). Aby funkcje internetowe się odświeżały przy normalnym przeliczaniu, trzeba jakkolwiek zmienić ich argument – w przypadku sztywnego URL-a jest to niemożliwe. Sposób na to jednak jest – należy tak zmienić URL-a, wywołującego usługę, żeby funkcja WEBSERVICE (i jej odpowiedniki) otrzymała za każdym razem INNY ARGUMENT, a należy pamiętać, że funkcja ta niekiedy nie odwołuje się do innego miejsca arkuszowego – musi być to zmiana sztuczna. I tak na przykład:
=HTMLTABLETOARRAY("http://afin.net/ws/getvalue.php?key=0123456789&atr=MyPutTable&forcecalc="&RND())
(w polskim Excelu jest to funkcja LOS() ).
ma część sztywną, i wystarczającą do jednokrotnego zadziałania usługi
http://afin.net/ws/getvalue.php?key=0123456789&atr=MyPutTable
oraz część zmienną, która jest zmieniana losowo, wyłącznie po to, że by URL się zmienił, czyli, żeby argument funkcji WEBSERVICE się zmienił, gdyż funkcja „nie wie”, czy zmieniamy część ważną, czy nie: &forcecalc="&RND()

W naszych publikacjach, filmach itd. używamy sztucznego, całkowicie wymyślonego argumentu „forcecalc” lub „fc”, co jest skrótem od „force calculation” = „wymuś przeliczenie”.

Argument ten może się nazywać dowolnie, poza zastrzeżonymi nazwami argumentów. Argument ten nie ma żadnego innego zastosowania, poza powyższym.

Regulamin

Regulamin używania Usług Sieciowych AFIN w wersji darmowej jest prosty:
1. Usług Sieciowych AFIN, w wersji darmowej, używam na własną odpowiedzialność i bez żadnej gwarancji ich jakości i dostępności.
2. Przyjmuję do wiadomości, że otrzymuję produkt bezpłatny, i, w związku z tym, czynnie(!) dzielę się opinią na jego temat i czynnie(!) wspieram rozwój tych usług.
3. Donacje mile widziane: konto: 89102052260000670206657474, treść: „AFIN Web Services”

PUT- & GET- VALUE, Usługa Sieciowa – Wymiana danych pomiędzy aplikacjami klienckimi – przeglądarkami internetowymi, arkuszami kalkulacyjnymi (i innymi) – wyłącznie za pomocą URL-a – putvalue.php, getvalue.php

Przykłady proste usług putvalue i getvalue

1. (putvalue) http://afin.net/ws/putvalue.php?atr=aaa&val=20

Lub, w Excelu, jako zwykła funkcja excelowa:
=WEBSERVICE("http://afin.net/ws/putvalue.php?atr=aaa&val=20"&"&fc="&LOS())
Wynik działania; wartość tekstowa „20 (aaa)”, czytaj: zapisano wartość „20” dla atrybutu „aaa”, czyli od tej chwili usługa wie, że dla klienta „0123456789” (domyślnego) wartość „aaa” wynosi 20.
Można stosować dowolną ilość dowolnych atrybutów, pisząc je jako jeden tekst. Można je rozdzielać jakimś wybranym znakiem, np. chcąc zapisać wartość 20 dla atrybutu „aaa”, dla momentu czasowego „20190310”, można to zrobić tak: http://afin.net/ws/putvalue.php?atr=aaa|20190310&val=20 . Znak „|” jest zastosowany przykładowo. Nie należy stosować znaków, które powodują problem z jednoznacznym zapisem/odczytem URL-a, zapisem/odczytem pliku na dysku, separacją wartości liczbowych, itp. Można natomiast ułatwić sobie jednoznaczne traktowanie przekazywanych wartości atrybutów, stosując tzw. atrybuty nazwane, np. zapis typu „operator=operator1; data=20190310; dotyczy=sprzedaż;” itd. Atrybuty są traktowane jako ciąg znaków, więc ich kolejność ma znaczenie. Znaki specjalne, np. spacje czy średniki, też są znakami.

2. (getvalue) http://afin.net/ws/getvalue.php?atr=aaa
Lub, w Excelu, jako zwykła funkcja excelowa:
=WEBSERVICE("http://afin.net/ws/getvalue.php?atr=aaa"&"&fc="&LOS())
Wynik działania: wartość tekstowa(!) „20”, czyli, jeżeli chcemy ją mieć np. w Excelu jako wartość liczbową, funkcję odczytującą trzeba dodatkowo zagnieździć - zamienić na wartość liczbową funkcją WARTOŚĆ().
Odczytanie powyższej wartości nie kasuje jej z serwera – można ją wielokrotnie odczytywać i pozostaje ona na serwerze do czasu ponownego jej nadpisania. Usługa, sama z siebie, nie dostarcza usługi archiwizacji w czasie. Można to jednak łatwo zrobić, podając jako atrybut nie samo „aaa”, tylko np. „aaa|20190310”, czyli odczyt tej wartości będzie możliwy tylko tak: http://afin.net/ws/getvalue.php?key=0123456789&atr=aaa|20190310

Wywołanie usługi „putvalue” i jej parametry

http://afin.net/ws/putvalue.php
key – klucz (opcjonalnie, usługa płatna), domyślnie: klucz testowy, darmowy = „0123456789”,
atr – ciąg tekstowy, zawierający atrybuty tekstowe (wymagane),
val – zapisywana wartość dla danego atrybutu (wymagane). Można to rozumieć jako dodanie wiersza w tabeli (wartości dla najprostszego przykładu powyżej)
pwd – (opcjonalnie) hasło szyfrowania informacji, odbiorca usługi GETVALUE musi użyć takiego samego argumentu, żeby odczytać wartość

Tabela (wirtualna – taka tabela nie istnieje – pokazana w celu lepszego zrozumienia funkcjonalności)

Atr	Val
Aaa	20

Wpisanie po raz kolejny wartości dla identycznego atrybutu, spowoduje nadpisanie wartości, nie dodając nowego wiersza – kolumna ‘atr’ jest jej kluczem głównym (unikatowym polem wyszukiwania).

Wywołanie usługi „getvalue” i jej parametry

http://afin.net/ws/getvalue.php
key – klucz (opcjonalnie, usługa płatna), domyślnie: klucz testowy, darmowy = „0123456789”,
atr – ciąg tekstowy, zawierający atrybuty tekstowe (wymagane),
pwd – (opcjonalnie) hasło szyfrowania informacji, gdy nadawca usługi PUTVALUE użył tego argumentu, należy podać tu to samo hasło, żeby odczytać wartość

Odczyt jest podobny do wykonania zapytania w języku SQL: SELECT [val] FROM [tabela] WHERE [atr]=’aaa’ .

UWAGA – nie ma możliwości użycia innych operatorów, niż operator prostego przyrównania wartości atrybutu, czyli znaku równości „=„.

Użycie tablicowe usługi putvalue i jej późniejszy odczyt różnymi metodami

Istnieje możliwość użycia usługi putvalue w odniesieniu do wielu wartości naraz, czyli do tablicy wartości, a nawet do tablicy tablic, czyli wielu dwuwymiarowych tabel jednocześnie, np. zakres nieciągły w Excelu, będący zbiorem rozdzielnych zakresów ciągłych (bardzo rzadko stosowane, ale, teoretycznie, bardzo efektywne). Aby tego dokonać, argument &val= usługi PUTVALUE musi w jednym tekście zawierać ów, nieraz bardzo duży” zbiór wartości, ograniczony wszak maksymalną długością URL-a, czyli ok. 2000 znaków. Do zapisu wartości tablicowych w informatyce stosuje się różne formaty zapisu danych: HTML, XML, JSON, pliki tekstowe typu txt i csv, przy czym za najefektywniejszy uznawany jest format JSON – jest on jednak nieintuicyjny w odczycie dla człowieka. Ponieważ dane te wysyłamy do usługi po to, żeby ktoś je mógł odczytać, musimy to zrobić łatwo i czytelnie po stronie wejścia (wysyłanie danych) oraz łatwo i czytelnie po stronie wyjścia (odczyt danych) – robimy to więc formatem HTML – przy zapisie tylko tabel jest on efektywny, jak XML, a dużo od XML czytelniejszy dla człowieka. Używamy tylko tagów <table>, <tr> i <td>, plus tagi otwierające. Żeby jednak z naszych danych w Excelu taki tekst HTML powstał, należy użyć jakiejś funkcjonalności, której w standardzie Excela nie ma. Cel, czyli zapis obszaru danych w Excelu jako tablicy w tekście, osiągamy, albo stosując nasz, specjalny zapis, tzw. skrócony HTML, albo używając pełnej funkcji, o której poniżej.

Zapis tablicy wartości, tzw. „skrócony HTML” – zapis tablicy wartości za pomocą formuły, składającej teksty

Gdy musimy zapisać tablicę wartości do tekstu, bo tylko tekst możemy wpisać w URL, jako wartość argumentu &val=, wystarczy, że każdy wiersz poprzedzimy znakiem „;” (średnik), a każdą kolumnę znakiem „\” (lewy ukośnik, backslash), tj. np. „;\kolumna1\kolumna2;\a\1;\b\2” jest równoznaczne z tabelą:

	A	B
1	kolumna1	kolumna2
2	a	1
3	b	2

Wartość taką, oczywiście, uzyskuje się formułami arkuszowymi, kombinując zapis poszczególnych wierszy w zapis sumaryczny, przedstawiony w D5 – przykład poniżej.

Rys. 1. Formuły

	A	B	C	D
1	kolumna1	kolumna2		=„\”&A1&”\”&B1
2	a	1		=„\”&A2&”\”&B2
3	b	2		=„\”&A3&”\”&B3
4
5				=„;”&D1&”;”&D2&”;”&D3
6
7	=WEBSERVICE(„http://afin.net/ws/putvalue.php?key=0123456789&atr=tablica&val=„&D5)

Rys. 2. Wartości formuł z rys. 1.
Wartość komórki A7 już po prawidłowej odpowiedzi usługi sieciowej PUTVALUE.

	A	B	C	D
1	kolumna1	kolumna2		\kolumna1\kolumna2
2	a	1		\a\1
3	b	2		\b\2
4
5				;\kolumna1\kolumna2;\a\1;\b\2
6
7	2019-11-14 08:25:49; 75 chars: „<html><body><table><tr><td>kolumna1<td>kolumna2<tr><td>a<td>1<tr><td>b<td>2” AS [tablica]

Spróbuj sam!

Tabela poniżej to dokładnie to samo, co tabele powyżej, z tym, że jest przygotowana do operacji kopiuj-wklej do Excela. ZAZNACZ całą tabelę, SKOPIUJ ją do schowka, w Excelu (wersja 2013+) ustaw się w komórce A1 i WKLEJ całość – formuły powinny przeliczyć się poprawnie.

kolumna1	kolumna2	=„\”&A1&”\”&B1
a	1	=„\”&A2&”\”&B2
b	2	=„\”&A3&”\”&B3

		=„;”&D1&”;”&D2&”;”&D3

=WEBSERVICE(„http://afin.net/ws/putvalue.php?key=0123456789&atr=MyPutTable&val=„&D5)

Gotowa formuła odczytu powyższej tabeli danych usługą sieciową GETVALUE oraz funkcją HTMLTABLETOARRAY (kod VBA tej funkcji znajduje się 2 akapity poniżej):

=HTMLTABLETOARRAY(„http://afin.net/ws/getvalue.php?key=0123456789&atr=MyPutTable&forcecalc=„&RND())

Uwaga – funkcja wymaga dostosowania! Funkcja RND() to najprostsza funkcja losowa w każdym Excelu – konieczne jest jej poprawienie na wersję z danej wersji językowej Excela – w polskim Excelu jest to funkcja LOS().
Jest to funkcja (formuła) tablicowa – zatwierdzenie przez [Ctrl+Shift+Enter]

UDF: RANGETOHTMLTABLE() - Gotowa funkcja VBA do konwersji wartości z obszaru na tekst HTML

Jeżeli jednak obszar jest za duży do wprowadzania powyższych formuł lub chcemy to robić szybciej, wystarczy dodać następującą funkcję do VBA.

Tworzymy ją w VBA w następujący sposób: w Excelu przechodzimy do Edytora VB Wstążka>Deweloper>ikona:Visual Basic [Alt+F11], w bieżącym projekcie (skoroszycie) tworzymy nowy moduł: Menu>Insert>Module [Alt+I, M], jeżeli nowy moduł zawiera jakiś nagłówek, to go czyścimy i wklejamy następujący kod funkcji VBA. Należy pamiętać, że skoroszyty z kodem VBA zapisujemy potem z rozszerzeniem XLSM, a otwierając je później akceptujemy użycie makr w tym skoroszycie.

Kod funkcji RangeTOHtmlTable(loSelectedRange) © AFIN, 2019
Uwaga: Tekst „{double quotation mark}”, po przekopiowaniu do Edytora Visual Basic, musi być zamieniony na jeden znak - podwójny cudzysłów. Skrót klawiaturowy: [Ctrl+H].

'>This function gets a table with data from an Excel range and returns a HTML text

Function RangeToHtmlTable(ParamArray loSelectedRange())

'*****************************************************************

'* Copyright: AFIN Wojciech Gardzinski 2019, Wroclaw, Poland, EU *

'*****************************************************************

Dim loRanges As Variant, loTable As Variant

Dim lsOut As String

Dim llTblNo As Long, llRowNo As Long, llColNo As Long

Application.Volatile True

On Error GoTo LErr0

For Each loRanges In loSelectedRange

loTable = loRanges.Value

llTblNo = llTblNo + 1

lsOut = lsOut & Chr(10) & {double quotation mark}<table>{double quotation mark} & Chr(10)

For llRowNo = LBound(loTable, 1) To UBound(loTable, 1)

lsOut = lsOut & {double quotation mark}<tr>{double quotation mark}

For llColNo = LBound(loTable, 2) To UBound(loTable, 2)

lsOut = lsOut & {double quotation mark}<td>{double quotation mark} & loTable(llRowNo, llColNo) & {double quotation mark}</td>{double quotation mark}

Next llColNo

lsOut = lsOut & {double quotation mark}</tr>{double quotation mark} & Chr(10)

Next llRowNo

lsOut = lsOut & {double quotation mark}</table>{double quotation mark} & Chr(10)

Next loRanges

RangeToHtmlTable = {double quotation mark}<html><body>{double quotation mark} & Chr(10) & lsOut & Chr(10) & {double quotation mark}</body></html>{double quotation mark}

GoTo LEnd

LErr0:

RangeToHtmlTable = {double quotation mark}#0:{double quotation mark} & Err.Description

GoTo LEnd

LEnd:

End Function

Użycie kodu VBA jest na własną odpowiedzialność.

Aby skorzystać z usługi PUTVALUE w odniesieniu do tablicy, zapisanej powyższą funkcją do obszaru, np. Range(„A1:D10”), należy użyć argument &val=RangeToHtmlTable(A1:D10”), czyli, w całości:
=WEBSERVICE(„http://afin.net/ws/putvalue.php?key=0123456789&atr=tablica&val=„&RangeToHtmlTable(A1:D10))

Odczyt tablicy wartości różnymi metodami

Gdzie „tablica”, czyli wartość &atr= jest naszą nazwą tworzonej tablicy wartości.

Odczyt może się odbyć za pomocą usługi GETVALUE, np. http://afin.net/ws/getvalue.php?key=0123456789&atr=tablica, jednak w ten sposób jest polecany tylko w Google Sheets (funkcja IMPORTHTML) lub, lokalnie, w Excelu z dodatkiem AFinScript (funkcja GETHTMLTABLE), które to metody potrafią odczytać HTML i ułożyć wartości w odpowiednim zakresie komórek w arkuszu. Można też wzbogacić swojego Excela o gotową funkcję HTMLTABLETOARRAY(), jej kod przedstawiono poniżej.

W przeglądarce internetowej najlepiej jednak użyć usługi sieciowej GETHTMLTABLE, gdzie zamiast zmiennej &url= w URL użyć należy zmiennej &atr= , w naszym przypadku będzie to &atr=tablica .

Przykład użycia usługi sieciowej GETHTMLTABLE w całości: http://afin.net/ws/gethtmltable.php?key=0123456789&atr=tablica&inx=1&out=html

UDF: HTMLTABLETOARRAY() - Gotowa funkcja VBA do konwersji tekstu HTML na tablicę wartości do jednoczesnego odczytu tablicowego – funkcja tablicowa(!)

Sposób wpisania funkcji do własnego modułu VBA jest opisany powyżej. Dokładnie tak samo postępujemy z funkcją poniżej. Pamiętaj o wpisaniu funkcji jako funkcji tablicowej, czyli z użyciem [Ctrl+Shift+Enter].

Funkcja HTMLTABLETOARRAY pobiera dane z gotowego HTML-a lub z URL-a, gdzie taki HTML się znajduje i – wpisana tablicowo(!) – rozpisuje kod HTML na wiersze i kolumny, tworząc normalną tabelę danych.

Kod funkcji HTMLTABLETOARRAY(strURL_or_strHTML, [intTableNo], [blnAllValuesAsText]) © AFIN, 2019
Uwaga: Tekst „{double quotation mark}”, po przekopiowaniu do Edytora Visual Basic, musi być zamieniony na jeden znak - podwójny cudzysłów. Skrót klawiaturowy: [Ctrl+H].

'>This function gets a table from HTML text or from URL with table data and returns a table for Excel array formulas.

Function HTMLTABLETOARRAY( ByVal strURL_or_strHTML As String, Optional ByVal intTableNo As Integer, Optional ByVal blnAllValuesAsText As Boolean = 0)

'*****************************************************************

'* Copyright: AFIN Wojciech Gardzinski 2019, Wroclaw, Poland, EU *

'*****************************************************************

Dim loHtmlObj As Object, loXmlObj As Object

Dim loTables As Object, loRows As Object, loColumns As Object

Dim loTable As Object, loRow As Object, loColumn As Object

Dim lsHtmlText As String, lsValue As String

Dim llTableNo As Long, llRowNo As Long, llColumnNo As Long

Dim llTablesCount As Long, llRowsCount As Long, llColumnsCount As Long

Dim lbColumnsCountSet As Boolean, lbDebug As Boolean

Dim t

Application.Volatile True

On Error GoTo LErr0

llTableNo = 0: llRowNo = 0: llColumnNo = 0: lbColumnsCountSet = 0

If intTableNo = 0 Then intTableNo = 1

lbDebug = 0 '>if lbDebug=1 then see immediate window

'>HTML Text

If UCase(Left(strURL_or_strHTML, 7)) = {double quotation mark}HTTP://{double quotation mark} Or UCase(Left(strURL_or_strHTML, 8)) = {double quotation mark}HTTPS://{double quotation mark} Then

Set loXmlObj = CreateObject({double quotation mark}Microsoft.XMLHTTP{double quotation mark})

loXmlObj.Open {double quotation mark}GET{double quotation mark}, strURL_or_strHTML, False

loXmlObj.send

lsHtmlText = loXmlObj.responseText

Else

lsHtmlText = strURL_or_strHTML

End If

'>Headers->(normal) Columns

lsHtmlText = Replace(lsHtmlText, {double quotation mark}<th{double quotation mark}, {double quotation mark}<td{double quotation mark}, , , vbTextCompare)

lsHtmlText = Replace(lsHtmlText, {double quotation mark}</th>{double quotation mark}, {double quotation mark}</td>{double quotation mark}, , , vbTextCompare)

'>HTML object

Set loHtmlObj = CreateObject({double quotation mark}HtmlFile{double quotation mark})

loHtmlObj.body.innerHTML = lsHtmlText

'>Tables object

Set loTables = loHtmlObj.body.getElementsByTagName({double quotation mark}table{double quotation mark})

llTablesCount = loTables.Length

For llTableNo = 0 To llTablesCount - 1

If llTableNo = intTableNo - 1 Then

Set loTable = loTables(llTableNo)

'>Rows object

Set loRows = loTable.getElementsByTagName({double quotation mark}tr{double quotation mark})

llRowsCount = loRows.Length

For llRowNo = 0 To llRowsCount - 1

Set loRow = loRows(llRowNo)

Set loColumns = loRow.getElementsByTagName({double quotation mark}td{double quotation mark})

llColumnsCount = loColumns.Length

'>Columns object

Set loColumns = loRow.getElementsByTagName({double quotation mark}td{double quotation mark})

llColumnsCount = loColumns.Length

If Not lbColumnsCountSet Then

ReDim t(llRowsCount - 1, llColumnsCount - 1)

lbColumnsCountSet = True

End If

For llColumnNo = 0 To llColumnsCount - 1

Set loColumn = loColumns(llColumnNo)

lsValue = loColumn.innerText

If Len(lsValue) > 0 Then t(llRowNo, llColumnNo) = lsValue

'>Change text values to numbers if possible (default)

If Not blnAllValuesAsText And IsNumeric(Val(t(llRowNo, llColumnNo))) And Len(lsValue) = Len(CStr(Val(lsValue))) Then

t(llRowNo, llColumnNo) = Val(t(llRowNo, llColumnNo))

End If

If lbDebug Then Debug.Print {double quotation mark}V:{double quotation mark}, llTableNo, llRowNo, llColumnNo, lsValue

Next llColumnNo

Next llRowNo

End If

Next llTableNo

HTMLTABLETOARRAY = t

GoTo LEnd

LErr0:

HTMLTABLETOARRAY = {double quotation mark}#0:{double quotation mark} & Err.Description

GoTo LEnd

LEnd:

Set loHtmlObj = Nothing

Set loXmlObj = Nothing

Set loTables = Nothing

Set loRows = Nothing

Set loColumns = Nothing

Set loTable = Nothing

Set loRow = Nothing

Set loColumn = Nothing

End Function

Użycie kodu VBA jest na własną odpowiedzialność. Funkcję HTMLTABLETOARRAY można również stosować, podając jako jej 1. argument gotowy tekst HTML, dostarczony np. przez zagnieżdżoną funkcję WEBSERVICE na normalnych zasadach zagnieżdżania funkcji Excela.

GETDATATEXT, Usługa Sieciowa – Czytanie dowolnego tekstu lub tablicy tekstów (ciągów znaków), lub całej strony html z podmienionymi tekstami (ciągami znaków) – getdatatext.php

(Pomoc do REGEX – Regular Expressions: http://regexone.com i wiele innych witryn o REGEX)

Przykłady proste usługi „getdatatext”

1. http://afin.net/ws/getdatatext.php?key=0123456789&url=http://afin.net/ws/samples/testfile1.txt&rgx=[A-Z]
- wybór tylko wielkich liter z tekstu http://afin.net/ws/samples/testfile1.txt

2. http://afin.net/ws/getdatatext.php?key=0123456789&url=http://afin.net/ws/samples/testfile1.txt&rpl1=[abc](:)X&rgx=[A-Z] – zamień małe litery a,b,c na wielką literę X, a następnie odczytaj wielkie litery

Wywołanie usługi „getdatatext” i jej parametry

http://afin.net/ws/getdatatext.php
key – klucz (opcjonalnie, usługa płatna), domyślnie: klucz testowy, darmowy = „0123456789”,
url – adres strony internetowej (wymagane)
rpl1 – ciąg REGEX do zamiany znaków PRZED ODCZYTEM (opcjonalnie). Patrz wyjaśnienie szczegółowe poniżej.
rgx – ciąg REGEX do wyboru ciągów tekstowych, spełniających warunek, określony filtrem REGEX
rpl2 – ciąg REGEX do zamiany znaków PO ODCZYCIE (opcjonalnie). Patrz wyjaśnienie szczegółowe poniżej.
hdr – nagłówek (opcjonalnie)
sep – separator tablicy wyjściowej (opcjonalnie, wartość domyślna </br></br>). Usługa może zwrócić tablicę wartości, standardowo rozdzielaną ciągiem „</br></br>„, czyli podwójnym złamaniem linii, co dobrze wygląda w przeglądarce internetowej, ale w arkuszu kalkulacyjnym powoduje nieczytelność wartości.
inx – jeśli zwracana jest tablica wartości, parametr inx określa kolejny numer pozycji z tej tablicy (opcjonalnie)

Można do tego celu wykorzystać również formularz webowy http://afin.net/ws/forms/getdatatext .

Wyjaśnienie szczegółowe działania argumentów rpl1 (działa przed wyszukaniem, czyli na wejściu) i rpl2 (po wyszukaniu, czyli na wyjściu) – określa wielokrokowy i wielowariantowy sposób zamiany tekstu. Jest to ciąg bloków zamian „From1(:)To1<spacja>From2(:)To2<spacja>From3(:)To3<spacja> (…) From(n)(:)To(n)”, na przykład: „&text=abcd&repl2=a(:)A c(:)z” da w wyniku „AbZd”, bo oznacza „zamień „a” na „A”, a potem, czyli w drugim kroku, „c” na „z”. Ciąg „From”, czyli parametr, co zamienić, jest ciągiem wieloznacznym REGEX (wszystkie spacje zastąpione przez „\s”), ciąg „To”, czyli na co zamienić, jest ciągiem zwykłym, z tym, że podobnie, jak w ciągu „From”, nie może zawierać spacji, bo spacja jest separatorem bloków zamian. Jeżeli chcemy zamienić coś na spację (w REGEX: „\s”), np. spację na spację (bardzo częsty przypadek), piszemy „&text=Ala ma kota&Ala\sma(:)Basia\skarmi”, co daje w wyniku „Basia karmi kota”.

Przykłady użytkowe

1. Zdania ze słowem „Hrabia” z „Pana Tadusza” (polska epopeja narodowa)
http://afin.net/ws/getdatatext.php?key=0123456789&url=https://wolnelektury.pl/katalog/lektura/pan-tadeusz.html&rgx=.*Hrabia.*&sep=%3C/br%3E

2. Dane o firmie po podaniu numeru NIP (polski numer podatkowy) w URL-u
http://afin.net/ws/getdatatext.php?key=0123456789&url=https://mapa.targeo.pl/7541012774/nip/firma

3. Wybór z Ustawy o VAT zdań, zawierających określone słowo, tu: „porno” 😊
http://afin.net/ws/getdatatext.php?key=0123456789&rgx=.*porno.*&url=https://www.lexlege.pl/ustawa-o-podatku-od-towarow-i-uslug/

GETHTMLTABLE, Usługa Sieciowa – Czytanie wybranych tabel z dokumentu HTML z możliwością konwersji danych na pojedynczą tabelę HTML (znaczne odchudzenie zawartości), XML (struktura, jak w HTML, czyli powszechnie znana, ale filtrowana XPath), na tablicę danych (w celu importu do Excela dla analizy tabelą przestawną) oraz formatu JSON. Opcja posiada możliwość zmiany tekstu za pomocą REGEX.

(Pomoc do REGEX – Regular Expressions: http://regexone.com )

Przykłady proste usługi „gethtmltable”

1. http://afin.net/ws/gethtmltable.php?key=0123456789&url=https://www.nbp.pl/home.aspx?f=/kursy/kursya.html&inx=44&out=html – przykład na wybór jednej, wybranej tabeli w celu ograniczenia ilości przekazywanych danych (znaczne przyspieszenie poboru tabeli z Internetu)

2. http://afin.net/ws/gethtmltable.php?key=0123456789&url=http://www.afin.net/samples/Data/ODBC/SalesInHTML/Region2.htm&out=json - przykład formatu JSON:
„{„1”:{„1”:{„1”:”MIASTO”,”2”:”REGION”},”2”:{„1”:”WROCLAW”,”2”:”Polud”},”3”:{„1” (…)

3. http://afin.net/ws/gethtmltable.php?key=0123456789&url=https://meteomodel.pl/prognoza-pogody/?stacja=2191&out=html&inx=&rpl2=\.(:), - nie podano parametru inx – przykład, np. testowego, pobrania wielu tabel z nierozpoznanego źródła HTML, zawierającego wiele tabel

Wywołanie usługi „gethtmltable” i jej parametry

http://afin.net/ws/gethtmltable.php
key – klucz (opcjonalnie, usługa płatna), domyślnie: klucz testowy, darmowy = „0123456789”,
url – adres strony internetowej (wymagane)
inx – jeśli zwracana jest tablica wartości, parametr inx określa numer pozycji z tej tablicy (opcjonalnie)
rpl2 – ciąg REGEX do zamiany znaków PO ODCZYCIE (opcjonalnie), użycie tak, jak w usłudze GETDATATEXT.
out – format wyjściowy: table (domyślnie), html, xml, json, array
xpath – jeśli parametr out ma wartość „xml” (&out=xml), można wtedy dodatkowo(!) filtrować dane tak, jakby strona z tabelami lub wybrana tabela była zapisana w XML – ze znacznikami, takimi jak w HTML, bez żadnych formatów (atrybutów), czyli /body/table/tr/td , np. //tr[td[1]=10]/td[3] – pobiera wartość z tabeli, z wiersza, w którym pierwsza kolumna ma wartość 10, z kolumny 3.

Przykłady użytkowe

1. Dane meteorologiczne – historyczne dane pomiarowe dla zadanej daty
http://afin.net/ws/gethtmltable.php?key=0123456789&url=https://meteomodel.pl/dane/historyczne-dane-pomiarowe/?data=2019-12-31&rodzaj=st&imgwid=351160424&dni=100&ord=asc&out=html

2. Cena ropy Brent na giełdzie
http://afin.net/ws/gethtmltable.php?key=0123456789&url=https://markets.ft.com/data&out=xml&xpath=//tr[contains(td[1],%20%27Brent%20Crude%20Oil%27)]/td[2]&inx=3&rpl2=\.(:),

3. Informacja o mieście dla, zadanego w URL, numeru IP
http://afin.net/ws/gethtmltable.php?key=0123456789&url=https://www.ip-adress.com/ip-address/ipv4/91.246.214.217&out=xml&xpath=//tr[td[1]=%27City%27]/td[2]

4. Informacja o imieniu i nazwisku urzędującego prezydenta miasta w Polsce
http://afin.net/ws/gethtmltable.php?key=0123456789&url=https://pl.wikipedia.org/wiki/Wroc%C5%82aw&out=xml&xpath=//tr[contains(td[1],%20%27Prezydent%27)]/td[2]

5. Informacja o wadze wybranego telefonu komórkowego z portalu MGSM
http://afin.net/ws/gethtmltable.php?key=0123456789&url=https://www.mgsm.pl/pl/katalog/huawei/p10lite/&out=xml&xpath=//tr[contains(td[1],%27Waga%27)]/td[2]

6. Tabela notowań akcji indeksu WIG30 na Warszawskiej Giełdzie Papierów Wartościowych
http://afin.net/ws/gethtmltable.php?key=0123456789&url=https://www.bankier.pl/inwestowanie/profile/quote.html?symbol=WIG30&out=html&inx=2

INWORDS, Usługa Sieciowa – popularna funkcja SŁOWNIE(liczba) – inwords.php. Poprzednia nazwa: VALTOSTR (value to string)

Przykłady proste usługi „inwords”

1. http://afin.net/ws/inwords.php?key=0123456789&val=1.23 - zwraca tekst: „one 23/100”

2. http://afin.net/ws/inwords.php?key=0123456789&val=123,45&lang=pl = „sto dwadziescia trzy zlote 45/100” (polski standard)

3. http://afin.net/ws/inwords.php?key=0123456789&val=123,05&lang=pl&curr=PLN&cnts=gr = „sto dwadziescia trzy zlote 5gr”

4. http://afin.net/ws/inwords.php?key=0123456789&val=123456789012,34 = „one hundred and twenty three bilion four hundred and fifty six milion seven hundred and eighty nine thousand twelve 34/100”

5. http://afin.net/ws/inwords.php?key=0123456789&val=9876&lang=de&let3=1 = „NEU#ACH#SIE#SEC#0/100”

Wywołanie usługi „inwords” i jej parametry

http://afin.net/ws/inwords.php
key – klucz (opcjonalnie, usługa płatna), domyślnie: klucz testowy, darmowy = „0123456789”,
val – wartość liczbowa do przetworzenia na tekst (ciąg znaków). Zakres 0-999.999.999.999,99 , bez separatorów tysięcznych, separatorem dziesiętnym jest kropka lub przecinek)
lang – dwuznakowy skrót języka. Obsługiwane wartości: „en” (angielski, domyślny), „pl” (polski), „de” (niemiecki, tylko dla opcji „3 znaki”). Lokalne, językowe znaki diakrytyczne w obecnej wersji są pomijane.
curr – oznaczenie waluty, np. curr=EUR
cnts – oznaczenie setnych części jednostki danej waluty, gdy pusty, stosowany jest uniwersalny zapis x/100
let3 – trzyznakowa, skrócona wersja zapisu liczby wielocyfrowej, np. ONE#TWO#THR#00/100, przykłady: let3=1 (włącza opcję trzyznakową) lub let3=0 (nie włącza tej opcji, domyślne)

USERINFO, Usługa Sieciowa – Informacja o użytkowniku – userinfo.php

Przykłady proste usługi „userinfo”

1. http://afin.net/ws/userinfo.php?key=0123456789 - pełna informacja o wywołaniu internetowym: czas wywołania (time), numer IP (ip), nazwa serwera (host), użytkownik (user)
2. http://afin.net/ws/userinfo.php?key=0123456789&inf=ip – pobierz numer IP

Wywołanie usługi „userinfo” i jej parametry

http://afin.net/ws/userinfo.php
key – klucz (opcjonalnie, usługa płatna), domyślnie: klucz testowy, darmowy = „0123456789”,
inf – typ zwracanej informacji, gdy nie podano prawidłowo, zwracana jest pełna informacja
inf=time – czas wywołania usługi w formie liczby całkowitej – po podzieleniu przez 10.000.000 otrzymuje się liczbę-czas identyczny, jak czas w Excelu (liczba dni od 1900-01-01, czas jest ułamkiem, np. 43782 to 2019-11-13) – jest to czas serwerowy, niezależny od strefy czasowej aplikacji wywołującej.
inf=ip – numer IP
inf=host – nazwa serwera
inf=user – informacja o użytkowniku

„FUNCTIONS” (FUNKCJE), Usługa Sieciowa – rozszerzanie funkcjonalności Excela – functions.php

Wywołanie usługi „functions” i jej parametry

http://afin.net/ws/functions.php
key – klucz (opcjonalnie, usługa płatna), domyślnie: klucz testowy, darmowy = „0123456789”,
fun – nazwa (identyfikator) wywoływanej funkcji, na chwilę obecną dostępne są funkcje:

„quarter_from_date”

Funkcja „quarter_from_date” (date) oczekuje argumentu date (data) w formie tekstu „RRRR-MM-DD”, np. „2019-11-13”. Zwraca numer kwartału z daty, czyli liczbę: f(x)={1,2,3,4}

Przykład:

http://afin.net/ws/functions.php?key=0123456789&fun=quarter_from_date&date=2019-11-13 ; wynik: „4”

„regex_replace”

Funkcja „regex_replace” (text, rpl2) zamienia wszystkie wystąpienia ciągu, określonego w argumencie rpl2, w ciągu text – zamian tych może być wiele i mogą być wieloznaczne, patrz opis argumentu rpl2 (lub rpl1) w Usłudze Sieciowej GETDATATEXT

Przykład:

http://afin.net/ws/functions.php?key=0123456789&fun=regex_replace&text=aAbBcC&rpl2=[A-Z](:)X ; wynik: „aXbXcX”, działanie: zamiana wszystkich wielkich liter na wielką literę „X”

„value_from_list”

Funkcja „value_from_list” (text, sep, inx, rpl2) wybiera, n-ty (określony argumentem inx) element z ciągu text, rozdzielony separatorem sep, z możliwą zamianą wyniku poprzez specyfikację w argumencie rpl2. UWAGA: separator może być wieloznakowym ciągiem REGEX, a argument rpl2 jest wyspecyfikowany w opisie Usługi Sieciowej GETDATATEXT.

Przykład:

http://afin.net/ws/functions.php?key=0123456789&fun=value_from_list&text=a;b,c.d&sep=[;,\.]&inx=2 ; wynik: „b”, działanie: wybiera 2. element z listy rozdzielanej albo „;” (średnik), albo „,” (przecinek), albo „.” (kropka, w REGEX kropkę musi poprzedzać znak wyjścia (Escape), czyli „\”)

MYSQLQUERY, MYSQLEXECUTE, Usługa Sieciowa – Czytanie i zapis z/do bazy danych MySQL– mysqlquery.php, mysqlexecute.php

Przykłady proste usługi „mysqlquery”

1. http://afin.net/ws/mysqlquery.php?key=0123456789&srv=afin.vot.pl&uid=afin_Sales&pwd=afin&db=afin_Sales&sql=SELECT * FROM faktura

Oczywiście, adres strony (url) można modyfikować do woli, np.
2. http://afin.net/ws/mysqlquery.php?key=0123456789&srv=afin.vot.pl&uid=afin_Sales&pwd=afin&db=afin_Sales&sql=SELECT * FROM odbiorca WHERE miasto=’WARSZAWA’
3. http://afin.net/ws/mysqlquery.php?key=0123456789&srv=afin.vot.pl&uid=afin_Sales&pwd=afin&db=afin_Sales&sql=SELECT nazwa AS Customer, SUM(wart_net) AS Sales FROM faktura f GROUP BY Customer ORDER BY Sales DESC

Przykład jest testową bazy danych AFIN, zawiera dane handlowe w tabelach, wymienionych poniżej. Znaki „|” w nazwach są wstawione wyłącznie w celu uniknięcia automatycznego przetłumaczenia nazwy tabeli. Nazwy tabel i nazwy kolumn nie zawierają znaków „|”.
„t|o|w|a|r” (product), „p|o|z|y|c|j|a” (sales record), „f|a|k|t|u|r|a” (invoice), „o|d|b|i|o|r|c|a” (customer), „r|e|g|i|o|n” (region), „z|a|p|l|a|t|a” (payment). Są one połączone sprzężeniami – standardowa relacyjna baza danych.
Lista sprzężeń:
t|o|w|a|r|.|i|n|d|e|k|s|_|t|o|w=p|o|z|y|c|j|a|.|i|n|d|e|k|s|_|t|o|w ,
p|o|z|y|c|j|a|.|n|r|_|f|a|k|t=f|a|k|t|u|r|a|.|n|r|_|f|a|k|t ,
f|a|k|t|u|r|a|.|n|a|z|w|a=o|d|b|i|o|r|c|a|.|n|a|z|w|a ,
o|d|b|i|o|r|c|a|.|r|e|g|i|o|n=r|e|g|i|o|n|.|r|e|g|i|o|n ,
f|a|k|t|u|r|a|.|n|r|_|f|a|k|t=z|a|p|l|a|t|a|.|n|r|_|f|a|k|t .

Wykorzystanie bazy wewnętrznej Usług

AFinWS posiadają własną bazę danych, wykorzystywaną przez wiele funkcji

http://afin.net/ws/mysqlquery.php?key=0123456789&srv=afin1&sql=SELECT * FROM _tblTest

{temat do opisania}

Wywołanie usługi „mysqlquery” i jej parametry

http://afin.net/ws/mysqlquery.php
key – klucz (opcjonalnie, usługa płatna), domyślnie: klucz testowy, darmowy = „0123456789”,
srv – serwer bazy danych MySql, porty domyślne,
uid – nazwa użytkownika,
port – numer portu, jeżeli inny, niż domyślny 3306
pwd – hasło,
db – baza danych,
sql – zapytanie SQL, zwracające wartość zwykłą, pojedynczą (nie tablicę wartości) lub tablicę wartości
nohdr (0,1) – wyłączenie nagłówka przy zwrocie tablicy danych, domyślnie – 0.
out – format zwracanych danych (html, xml, value). Domyślnie – html. Gdy podano out=value, formatowanie html jest wyłączone i zwracana jest wyłącznie wartość, nie trzeba już podawać argumentu nohdr=1.

Usługa „mysqlexecute” nie jest dostępna w wersji darmowej Usług Sieciowych AFIN.

UPLOADXSLX, Usługa Sieciowa – Zapis całego pliku XLSX lub XLSM do bazy danych usługi sieciowej – uploadxlsx.php

Zapis poprzez formularz webowy

http://afin.net/ws/uploadxlsx.php

W darmowej wersji Usług Sieciowych AFIN możliwy jest zapis plików do wielkości 100 kB.

Usługa UPLOADXLSX, jednym ruchem(!), pobiera dane z dysku lokalnego i zapisuje do repozytorium usługi w formie wielu formatów danych. Po załadowaniu pliku, w kolejnym kroku, można odczytać dane, załadowane na serwer usługi, oraz odnośniki do tych plików z danymi. Dane zachowywane są w formatach:

1. Tabela długa (XML, format: „addr, value”)

2. JSON

3. Tabela (CSV, format: „F1, F2, F3, …”)

4. Zestaw tabel (HTML, arkusze, kolejno, jako tabele HTML)

5. Dane tablicy PHP (format: „FileName; WorksheetName; Row; Column; Value;”

6. Tekst komendy SQL: INSERT INTO

7. Baza danych – dostęp limitowany

8. Zestaw tabel (XML jak HTML)

{temat do opisania}

Zapis poprzez komendę biblioteki CURL

Zapis pliku do repozytorium danych usługi dokonać można też za pomocą lokalnej biblioteki CURL, wykorzystując powyższą usługę sieciową, ale bez formularza, który wymaga akcji operatora.
W okno „uruchom” Windows [Windows+R] wpisujemy

curl -F uploaded_file=@C:\AnyPath\YourXlsxFile.xlsx http://afin.net/ws/uploadxlsx_info.php

i plik natychmiast pojawia się w repozytorium usługi. Stosowanie biblioteki CURL jest bardzo wygodne i polecane przy upload-ach batch-owych (programowe ładowanie plików). Można to zaprogramować w zadaniach Windows i ładować wiele plików bez przerywania działania innych programów i każdorazowej ingerencji operatora.

Jeżeli nie działa wywołanie curl.exe domyślnie ze ścieżki C:\WINDOWS, można skopiować program-bibliotekę curl.exe do innego folderu i uzupełnić ścieżkę w wywołaniu.

„Biała lista podatników VAT”, Usługa Sieciowa – Specjalizowany dla polskiego podatnika VAT serwis, dostarczający informacji, czy dany podatnik VAT znajduje się na tzw. „Białej Liście” aktywnych płatników VAT – bialalista.php

Wywołanie

http://afin.net/ws/bialalista.php

Polskie prawo podatkowe wymaga od firm sprawdzania, czy kontrahent, któremu wysyłamy przelew środków finansowych, znajduje się lub nie na tzw. „Białej Liście Podatników VAT” – po szczegóły prawne odsyłamy do odpowiednich dokumentów polskiego Ministerstwa Finansów (MF).

Technicznie usługa polega na tym, że codziennie (również w weekendy i święta) na stronie MF publikowany jest plik tzw. „Białej Listy”. Niniejsza usługa plik ten pobiera ok. godz. 6.00 i przetwarza dane na postać, która może być odczytana z przeglądarki internetowej, po podaniu odpowiedniego URL lub z funkcji DOWOLNEGO arkusza kalkulacyjnego, oferującego funkcję internetową WEBSERVICE(url) (w MS Excel), IMPORTDATA(url) (w Google Sheets) lub USŁ.INTER(url) (Libre Office PL).

key – klucz (opcjonalnie, usługa płatna), domyślnie: klucz testowy, darmowy = „0123456789”,
nip – numer NIP,
data – data sprawdzenia w formacie „YYYYMMDD”, domyślnie: data bieżąca, patrz uwagi
nrb – numer rachunku bankowego do sprawdzenia

Ważne informacje, dotyczące usługi „bialalista”

Wszelkie pliki są archiwizowane lokalnie u dostawcy usługi i można uzyskać odpłatną informację nt. danego kontrahenta w dowolnej dacie wstecz.
Dodatkowe informacje: A_F_I_N_a_t_A_F_I_N_p_o_i_n_t_N_E_T

Przykłady proste usługi „bialalista”

1. http://afin.net/ws/bialalista.php?key=0123456789&nip=8960006997&nrb=92124034641111001048159563

Przykład użytkowy – gotowy skoroszyt Excela do pobrania

http://afin.net/ws/samples/Biala%20Lista%20Podatnik%c3%b3w%20VAT%20-%20us%c5%82uga%20AFIN%20Web%20Services%20-%20przyk%c5%82ad%20%231.xlsx

GETDATAGSHEETS, Usługa Sieciowa – odczyt danych z, udostępnionego publicznie do odczytu, dokumentu usługi Google Sheets (w skrócie: GS) – getdatagsheets.php

Wywołanie

http://afin.net/ws/getdatagsheets.php

Przykłady proste usługi „getdatagsheets”

1. Przykład pobierania danych z wyznaczonego obszaru lub całego arkusza:
http://afin.net/ws/getdatagsheets.php?key=0123456789&id=1DdwTn3wsFjbhPfOVGIc-3mNAxJyLiTK2p9qHuGRTfyk&sheet=19Rows_15Columns&range=A1:O20&rpl2=\.(:),

2. Przykład pobierania danych za pomocą użycia GQL (Google Query Language) – sample: Customers’ ranking over the sum of sales:
http://afin.net/ws/getdatagsheets.php?key=0123456789&id=1DdwTn3wsFjbhPfOVGIc-3mNAxJyLiTK2p9qHuGRTfyk&sheet=19Rows_15Columns&range=A1:O20&rpl2=\.(:),&query=SELECT%20K,%20SUM(N)%20GROUP%20BY%20K%20ORDER%20BY%20SUM(N)%20DESC%20LABEL%20K%20%22Customer%22,%20SUM(N)%20%22Sum%20of%20sales%22

key – klucz (opcjonalnie, usługa płatna), domyślnie: klucz testowy, darmowy = „0123456789”,
id – numer identyfikacyjny skoroszytu (dokumentu) GS,
sheet – nazwa arkusza GS, (domyślnie: arkusz pierwszy od lewej),
range – numer identyfikacyjny arkusza GS, (domyślnie: cały arkusz – wszystkie komórki arkusza)
query – Google Query Language, podobny do grupy komend DQL (Data Querying Language) języka SQL (Structured Query Language) w dialekcie Oracle – deklaratywny język zapytań, gdzie klauzula FROM jest zastąpiona tabelą-obszarem, określonym parą argumentów „sheets” i „range”,
rpl2 – parametr zamiany znaków, patrz inne usługi AFIN Web Services

GFN (= GETFROMNET), Usługa Sieciowa – użycie funkcji O UKRYTEJ FUNKCJONALNOŚCI

Wywołanie

http://afin.net/ws/gfn.php

Przykłady proste usługi „gfn”

<opis>

key – klucz (opcjonalnie, usługa płatna), domyślnie: klucz testowy, darmowy = „0123456789”,
rpl0 – unikalny dla tej usługi parametr rpl0, identyczny w działaniu, jak parametry rpl1 i rpl2 w innych usługach, służy do zamiany tekstu na bazie REGEX – uwaga – jeszcze przed „wybudowaniem” przez tę usługę docelowego URL-a. Przypomnijmy – rpl1 służy do zamiany znaków przed wybraniem danych (zbiór wejściowy), rpl2 po ich wybraniu (zbiór wyjściowy), a rpl0 do zamiany jeszcze samego URL-a, zanim dane w ogóle trafią do usługi wybierającej. Ma to bardzo poważne zastosowanie klienckie. Za łóżmy, że chcemy uruchomić jakąś usługę, wyciągającą kurs CHF z Internetu. Żeby to zrobić budujemy url, w którym występuje niezrozumiały lub niepożądany, bo np. w jakimś języku, ciąg znaków, np. „kurs-franka” i „kurs-dolara”, jak w przykładzie:
=webservice(http://afin.net/ws/getdatatext.php?key=0123456789&url=https://internetowykantor.pl/kurs-franka/&rgx=actual-type.{1,20}&rpl2=[^0-9,]*(:))
Chcąc, żeby klient odwoływał się użytkowo, tj. tak, jak my chcemy „CHF” i „USD”, tworzymy argument rpl0=CHF(:)kurs-franka USD(:)kurs-dolara&(…)
i wszystkie argumenty posiadające tekst CHF i USD są odpowiednio zamieniane, jeszcze przed wywołaniem URL-a, tak że URL wywołany jest prawidłowo technicznie, a klient ma wywołanie użytkowe.

ENCRYPT & DECRYPT (=CRYPTING), Usługa Sieciowa – Szyfrowanie informacji, przekazywanej przez otwarty Internet, również przez arkusze kalkulacyjne

Wywołanie

http://afin.net/ws/encrypt.php , http://afin.net/ws/decrypt.php

Przykłady proste usługi „crypting”

<opis>

Przykłady użytkowe – gotowe skoroszyty Excela do pobrania

1. Try to decode this info in an open worksheet!
http://afin.net/ws/samples/ENCODED%20info%20in%20the%20Excel%20worksheet%20EN%20PL.xlsx

2. The SUPER PASSWORD sheet – your crypted passwords are very safe in an open worksheet!
http://afin.net/ws/samples/The%20SUPER%20PASSWORD%20worksheet%20by%20AFIN%20Web%20Services.xlsx