Temat: Integracja Excel i R z wykorzystaniem VBA

(miałem odświeżyć wątek Nowy interfejs R -> .NET, ale zbyt mocno skupiłem się na VBA)

Zadaniem jest udostępnienie wybranych algorytmów wykonywanych w R użytkownikom znającym tylko program Excel. Moje doświadczenie z różnymi wtyczkami R<->Excel jest niestety negatywne. Dodam, że niestety w projekcie nie będzie wykorzystana wersja 2007 i 2010 Excel. Typowy scenariusz wygląda następująco :

Użytkownik w Excel uruchamia dodatek, pojawia się formularz, wybiera pole 'y', wybiera przycisk 'zaznacz dane', zaznacza dane w Excel, potem to samo dla zmiennej 'x', ustawia inne parametry i wybiera przycisk 'glm', wtedy następuje przekazanie danych z kolumn arkusza odpowiadających 'y' oraz 'x' do R i wykonanie obliczeń. Następnie dane są przekazywane do Excela i wyświetlane w formularzu, użytkownik może je wkleić do Excela lub zmienić parametry i powtórzyć procedurę estymacji.

Myślałem, żeby stworzyć dodatek w VBA i komunikować się z R uruchamiając go w trybie wsadowym funkcją 'Shell', VBA miałby za zadanie zapisać na dysku dane 'y' i 'x', następnie uzupełnić napisany wcześniej w R skrypt parametrami wybranymi przez użytkownika i uruchomić go w R. Niestety taki scenariusz pozwala tylko na jednokierunkową komunikację :

Excel->VBA(zapisanie 'y','x', uruchomienie sparametryzowanego skryptu w R)->R(odczyt 'x','y',obliczenia, zapisanie wyniku na dysku)->VBA(odczyt wyniku, wklejenie danych do formularza)

,jednak to rozwiązanie jest nieeleganckie, niebezpieczne (chyba można zawiesić system), mało elastyczne np. odczyt wyniku obliczeń z R przy użyciu VBA, to praca na pliku tekstowym.
Ponadto widzę poważny problem w samej jednokierunkowości takiego rozwiązania, jeśli po zakończeniu estymacji użytkownik chciałby przeprowadzić prognozę, to albo estymacja musiałaby zostać powtórzona (co na przykład przy lasach losowych może zająć kilkanaście minut) lub zapisany powinien zostać wcześniej obszar roboczy, następnie załadowany i prognoza przeprowadzona.

Mógłbym jeszcze zamiast trybu wsadowego skorzystać ze schowka systemowego i emulacji klawiatury funkcją 'SendKeys', ale wydaje mi się, że nie tędy droga. czy mógłbym wypróbować coś jeszcze korzystając z VBA ? A może R ma równiej jeszcze coś do zaoferowania w tym temacie ?

Temat: Integracja Excel i R z wykorzystaniem VBA

Parę opcji masz :)

Pierwsza, najbardziej "pro", "lux" i "tip top" to znany Ci RExcel oparty o StatConnector - darmowy do celów niekomercyjnych z wkurzającym splash screenem. RExcel to excelowe "opakowanie" na funkcje "wystawione" przez StatConnector.
* http://www.goldenline.pl/grupy/Komputery_Internet/r/r-...
* http://cran.r-project.org/contrib/extra/dcom/00ReadMe....
* https://www.unt.edu/rss/class/splus/UsingTheRDCOMserver...

Sam StatConnector jest bardzo wygodny, dobrze opisany (dużo tutoriali) i wprost uwielbiałem go, zanim nie wprowadzili tego splash screena :/ VBA jest wprost idealny (nawet bardziej niż inne nowoczesne języki programowania) do obsługi obiektów COM.

Oprócz tego mamy jeszcze rcom na licencji GPL, ale jest on bardzo niskopoziomowy.

Z poziomu VBA możesz także uruchamiać regularne DLLki, więc mógłbyś sie pokusić o zalinkowanie do R.DLL, ale to będzie paskudna robota.
http://msdn.microsoft.com/en-us/library/aa235591%28v=v...

Jakimś tam pomysłem jest faktycznie korzystanie z shell i rscript.exe ze skryptem jako parametrem, który zapisuje wyniki do CSV lub XSL(x), które potem odczytujesz z poziomu VBA. Masz więc komunikację dwukierunkową o tyle, że możesz programowo odczytać wyniki z pliku.

Wreszcie - możesz wykorzystać DDE z poziomu Excela. Masz wtedy również dwukierunkową komunikację. Było o tym tutaj (Twój wątek :)) - http://www.goldenline.pl/grupy/Komputery_Internet/r/cz...

Temat: Integracja Excel i R z wykorzystaniem VBA

Dzisiaj wypróbować jeszcze jeden pomysł, prawie całkowicie pomijający VBA (pracuję na Excel 2007/2010):

1. Excel posiada kontrolkę ActiveX o nazwie Microsoft Web Bowser, można ją wstawić do arkusza lub do formularza VBA.
2. Przy pomocy makra można ustawić która strona będzie wyświetlana w tej kontrolce.
3. R posiada pakiety Shiny pozwalający na tworzenie interaktywnych stron internetowych, może nawet nie uploadować swoje dane i dokonać ich analizy Galeria Shiny, mój pomysł był taki żeby wyświetlić taką stronę w kontrolce w Excel.

Niestety Shiny nie działa z IE 8,9(częściowo, a u mnie całkowicie), gdyż opiera się na WebSocket Shiny and IE z IE 10 również często zgłaszana jest niekompatybilność. Dlatego problemy występują również z kontrolką Microsoft Web Bowser opiera się na czymś pośrednim pomiędzy IE 8 i IE 9.

Próbuję teraz znaleźć nie-microsoftową kontrolkę przeglądarki jednak znalazłem tylko wygasłe projekty sprzed lat :
http://www.iol.ie/~locka/mozilla/control.htm#introduction
https://code.google.com/p/geckofx/

Powyższe rozwiązanie byłoby o tyle wygodne, że mógłbym się skupić na pakiecie Shiny, zamiast na VBA, co na pewno jest plusem, VBA byłby wykorzystany tylko po to by zautomatyzować uploadowanie danych z Excela na stronę z Widget'em Shiny (i odwrotnie).

Spróbuję jeszcze skorzystać z pakietu gWidgetsWWW bo kusi mnie umieszczenie strony html bezpośrednio w Excel - od strony wizualnej ma ono dużo do zaoferowania.

Temat: Integracja Excel i R z wykorzystaniem VBA

Pomysł ciekawy, ale... nadal nie dostaniesz wyników do Excela, ani ich nie odczytasz z niego. To będzie po prostu "interfejs graficzny do R osadzony w Excelu". Będziesz musiał tak czy siak pracować na bazach danych (Jet) albo plikach CSV generowanych/odczytywanych przez Excela.

Nie pamiętam za dobrze, ale R miał też inne, choć bardzo proste interfejsy, RWeb albo coś takiego.

Temat: Integracja Excel i R z wykorzystaniem VBA

Kontrolki ActiveX wstawiane na arkusze to zło. Jeżeli chodzi o kontrolki wrzucane do formularzy VBA to już lepiej o ile excel na którym pracujesz jest na użytek własny (nie idzie do klienta). Niestety w przeciwnym przypadku trzeba będzie często poprawiać pliki z powodu różnych aktualizacji Microsoftu.

Jeżeli już chciałbyś iść w tym kierunku to skorzystałbym po prostu z R postawionego na serwerze i się z nim komunikował za pomocą kontrolki IE. Poniżej prosty kod logujący się na takie konto. Niestety jeszcze nie obczaiłem (nie poświęciłem na to odpowiednio dużo czasu), jak wpisać kod (wydobywanie informacji to po prostu parsowanie strony) :D ... Strasznie kombinowany temat :)

W module:

Option Explicit



Public oIE As Object



Sub EXCEL_R()

    Dim oUsernameTextBox As Object

    Dim oPasswordTextBox As Object

    Dim oLoginButton As Object

    Dim oConsole As Object

    

    Set oIE = CreateObject("InternetExplorer.Application")

    

    oIE.navigate "http://www.ADRES.pl/auth-sign-in"

    

    oIE.Visible = True

    

    Do While (oIE.Busy) ' TODO, dopracowac

        DoEvents

    Loop

    

    Set oUsernameTextBox = oIE.document.getelementbyid("username")

    oUsernameTextBox.Value = "username"

    

    Set oPasswordTextBox = oIE.document.getelementbyid("password")

    oPasswordTextBox.Value = "password"

    

    Set oLoginButton = oIE.document.GetElementsbyClassname("fancy")

    oLoginButton(0).Click

    

    Set oConsole = oIE.document.getelementbyid("rstudio_console_input")



    Debug.Assert False

    

    oIE.Close

    Set oIE = Nothing

End Sub

Ten post został edytowany przez Autora dnia 21.01.15 o godzinie 08:38

Temat: Integracja Excel i R z wykorzystaniem VBA

Panowie, szkoda zachodu na parsowanie HTMLa i stawianie serwerów. Chyba, że już są postawione.

Proszę bardzo, ktoś ładnie opisał przedstawione wyżej rozwiązanie z VBA i rscript: http://shashiasrblog.blogspot.com/2013/10/vba-front-en...
+ komentarzeTen post został edytowany przez Autora dnia 21.01.15 o godzinie 09:33

Temat: Integracja Excel i R z wykorzystaniem VBA

Przepraszam, poniosło mnie.

Na pewno wadą tego rozwiązania poprzez R na serwerze jest to, że i tak trzeba byłoby komunikować się przez pliki (np. jak mamy tabele z bardzo dużą liczbą wyników).

To straszna zabawa z połączeniem Excela z R tylko przy pomocy VBA, już lepiej promować R i nauczyć użytkowników Excela obsługi R ;), tym bardziej że jeżeli chodzi o zadania typu wstaw parametr i kliknij ok, to można takie proste formatki robić z poziomu R i generować z automatu xls lub xlsx (nawet sformatowane) (???).

Temat: Integracja Excel i R z wykorzystaniem VBA

Zawsze należy dążyć do dostosowania narzędzi do potrzeb, a nie odwrotnie. Chyba, że nasze potrzeby znacznie wykraczają poza możliwości ich realizacji właściwymi narzędziami. Jak trzeba, to się integruje nawet R i SQL Serverem :)

Doraźne szkolenie użytkownika z zakresu nowego programu tylko po to, by mógł sobie wykonać kilka obliczeń, jest zazwyczaj złym pomysłem. Użytkownik ma wykonać swoją pracę i na nią przeznaczyć maksimum wysiłku. Jeśli nauka nowego narzędzia ma być opłacalna, to musi być ono: a) dostosowane do potrzeb i zdolności poznawczych danej osoby, b) realizować zadanie w możliwie najprostszy sposób minimalizując ryzyko powstania problematycznych sytuacji (o które w R nietrudno), c) być wykorzystywane wielokrotnie, a nie tylko raz (strata czasu).

R i Excel to produkty o różnym przeznaczeniu i różnej docelowej grupie użytkowników. Jednym nie zastąpimy łatwo drugiego, ponieważ realizują inne (choć nieraz pokrywające się) cele w różny sposób. Często nawet w skomplikowanych rozwiązaniach analitycznych silniki obliczeniowe (R, SAS, SPSS), SQL i inne narzędzia BI są głęboko ukryte przed użytkownikiem końcowym, udostępniając mu jedynie odpowiedni do charakteru jego pracy interfejs, nierzadko właśnie w Excelu. Użytkownik nie może "zgubić sedna problemu" na tle obsługi narzędzi.

Wracając do tematu: trzeba sobie odpowiedzieć na pewne pytania, a potem dobrać narzędzia do ich realizacji.

1. czy R ma pamiętać wyniki pośrednie analiz? (z opisu wynika że tak). Jeśli tak, to shell+rscript odpada, bo za każdym razem wyniki, np. modele się stracą. Pewnym rozwiązaniem jest zapisywanie obiektów modeli do pliku *.rdata przed wyjściem i potem wczytywanie ich na starcie. Zalety: prosta integracja, Wady: skomplikowane, dużo "dłubaniny".

Najlepszym, wprost idealnym rozwiązaniem będzie tutaj praca z sesją R, a zatem pozostaje interfejs COM albo DDE. Z dostępnych rozwiązań StatConnector będzie najwygodniejszy (pamiętajmy, że do celów niekomercyjnych). Ilość potrzebnej nauki VBA będzie relatywnie mała w relacji do potencjalnych korzyści z integracji obu systemów.
Gotowym rozwiązaniem jest oczywiście RExcel.

SendKeys to straszna dłubanina i wystarczy że się na chwilę zmieni tytuł okna albo uruchomi drugie... albo zmieni język.

1a. jeśli projekt ma być wykorzystywany komercyjnie, to należałoby w napisać obiekt COMowy np. w C#(.NET), który już może spokojnie komunikować się z R na wiele sposobów. Funkcje takiego obiektu COMowego można wtedy wywoływać z poziomu VBA.

2. czy R ma być tylko dodatkiem do Excela dla użytkowników korzystających z niego na co dzień? Jeśli tak, to konieczna jest integracja obu narzędzi.

- czy Excel ma być tylko interfejsem do R? Jeśli tak, to:

a) czy potrzebna jest praca z siatką danych (arkusze, zakładki, autofiltr, formatowanie warunkowe, wiele wykresów, kontrola dostepu do komórek danych, czytelnie opisany interfejs) - jeśli tak, to nadal R nie zastąpi Excela i trzeba je łączyć: R do obliczeń, Excel do pracy z danymi i ich wizualizacji.

b) czy potrzebne jest po prostu wprowadzenie kilku parametrów i odczytanie kilku liczb? Jeśli tak - lepiej zbudować dedykowany interfejs graficzny, np. z użyciem gWidgets (albo któregoś z bardziej niskopoziomowych toolkitów) i zaprezentować gotowe, samowystarczalne rozwiązanie, zwłaszcza jeśli wykorzystamy R w wersji "portable".