Jak zoptymalizować obsługę arkusza kalkulacyjnego z bardzo skomplikowanymi formułami?

Przede nną dość trudne zadanie mające na celu stworzenie oprogramowania dla klienta. Niestety klient raczej z tych trudnych, ciężko znoszących zmiany...
Tutaj pojawia się więc problem, który staram się rozwiązać. Obecnie wszystko opiera się o pliki Excel'a, ale nie są to zwykłe pliki (xlsx) tylko pliki z makrami i dość skomplikowanymi funkcjami (xlsm). Udało mi się to rozgryźć i dzięki PHPSpreadsheet jestem w stanie podstawić swoje dane i uzyskać wynik (nawet pomimo baaardzo skomplikowanych formuł).

Ogromnym minusem wykorzystania tej biblioteki jest potężna zasobożerność oraz stosunkowo długi czas przetwarzania pliku.

Docelowo całość będzie obsługiwana przez lokalnie postawiony serwer (środowisko windows server).
Tutaj więc rodzi się moje pytanie.

Czy z poziomu PHP jestem w stanie wymusić uruchomienie pełnoprawnego Excel'a, następnie "wstrzyknąć" mu w odpowiednie komórki dane (pobrane z mySQL), zmusić go do przeliczenia formuł i zapisania pliku wynikowego + wyciągnięcia określonych komórek (ich zawartości) i zapisanie ich do bazy w celu szybkiego dostępu?

Myślę, że takie rozwiązanie byłoby dużo szybsze niż to co jest realizowane obecnie przy użyciu biblioteki PHPSpreadsheet
Ktoś może miał podobne doświadczenie?

Jestem otwarty na każde sugestie :)

Jak już realizujemy troszkę szalone pomysły klientów to ja mogę polecić walkę z Component Object Model (COM)
https://www.php.net/manual/en/ref.com.php

Swego czasu wydawało mi się to cudowne rozwiązanie (sam COM a nie COM w PHP), ale tylko póki nie zacząłem tego używać,
ale to nie szkodzi abym polecał,
Nie każda technologia jest dla każdego :D

@Marius.Maximus
Czy serwer musi być odpowiednio skonfigurowany (zainstalowany jakiś konkretny pakiet) żeby to zadziałało czy jest to standard dla Microsoft Server?
Administracją serwerów się nie zajmuję ale żeby ewentualnie z tym zacząć działać pasowałoby mi takie środowisko uzyskać. Zasada jest prosta - to co chce, to mi zainstalują. Ale w sumie to nie wiem jeszcze co chce :P
Jakieś proste przykłady ze zwykłym plikiem Excel widzę w załączonym linku, niestety nie wiem jak sprawa będzie wyglądała z moimi potworkami...

Podstawa to zainstalowany excel na OS
Nie mam też pojęcia czy to ruszy w kontekście serwera HTTP

Przykład w powershell https://woshub.com/read-write-excel-files-powershell/

NewUser2k13 napisał(a):

Przede nną dość trudne zadanie mające na celu stworzenie oprogramowania dla klienta. Niestety klient raczej z tych trudnych, ciężko znoszących zmiany...
Tutaj pojawia się więc problem, który staram się rozwiązać. Obecnie wszystko opiera się o pliki Excel'a, ale nie są to zwykłe pliki (xlsx) tylko pliki z makrami i dość skomplikowanymi funkcjami (xlsm). Udało mi się to rozgryźć i dzięki PHPSpreadsheet jestem w stanie podstawić swoje dane i uzyskać wynik (nawet pomimo baaardzo skomplikowanych formuł)

Ogromnym minusem wykorzystania tej biblioteki jest potężna zasobożerność oraz stosunkowo długi czas przetwarzania pliku.

Docelowo całość będzie obsługiwana przez lokalnie postawiony serwer (środowisko windows server).
Tutaj więc rodzi się moje pytanie.

Z dalszej części wnioskuję że chyba nie 'calosc' będzie obsługiwana przez serwer.

Czy z poziomu PHP jestem w stanie wymusić uruchomienie pełnoprawnego Excel'a, następnie "wstrzyknąć" mu w odpowiednie komórki dane (pobrane z mySQL), zmusić go do przeliczenia formuł i zapisania pliku wynikowego + wyciągnięcia określonych komórek (ich zawartości) i zapisanie ich do bazy w celu szybkiego dostępu?

A czy tych obliczeń nie możesz zrobić po stronie serwera?

Myślę, że takie rozwiązanie byłoby dużo szybsze niż to co jest realizowane obecnie przy użyciu biblioteki PHPSpreadsheet
Ktoś może miał podobne doświadczenie?

Jestem otwarty na każde sugestie :)

Nie jestem programistą niestety
ale naklikalem w życiu trochę w VBA, i po Twoim opisie przychodzi mi do glowy:

1. Serwer zbiera cos, później wyrzucasz do csv. Do tych obecnych exceli klient dopisuje niewiele kodu i te csv zczytuje, robi co chce, i
   (a) można kawałek kodu do wrzucenia bezpośrednio na serwer z samego Excela po obrobce (ja zwykle komunikował em się z mssql, ale myślę że mysql też można bez problemu)
   (B) excel zbiera csv, przelicza, kawałek kodu który wyrzuca csv z Excela a Ty te csv wrzucasz na serwer z pomocą PHP

   Wydaje mi się, że mniej pracy niż kodowanie wszystkiego w PHP

2. To co excel przelicza (surówka jest z serwera, mechanika w Excelu, i na serwer później wraca) może da się zrobić na serwerze?

Polecam zrobić serwis w dowolnym języku który będzie się komunikował z excelem lub go obrabiał a z zewnątrz byłby widziany jako zwykłe REST API albo RPC.
Dzięki temu możesz wykorzystać dowolny język który ma oficjalne wsparcie i biblioteki napisane przez twórców excela, bez korzystania z mało wspieranych third party bibliotek w PHP czy jeszcze gorzej - korzystania z COM w PHP.
W PHP zostałby czysty kod bez zbędnej magii który komunikowałby się z tym API w standardowy sposób, w przyszłości niezależnie i łatwo będzie też można pozbyć się excela i przepisać mechanikę z excela na normalny kod.

W punkt! Tylko, znając życie, trudny klient - old school to ciężko będzie te excel wyeliminować pewnie.
PS niestety nie mam jeszcze uprawnień żeby like dać ;)

Miałem podobny przypadek.
Rozwiązanie nr 1: wyłącznie makr i skryptów na arkuszu wstrzyknięcie danych. Zapis nowego pliku i możliwość pobrania. Średnio przydatne. Zawsze okazywało się, że formuła X nie zadziałała jednak jak potrzeba na nowych danych. Zero debugowania po ludzku. Nie rozwojowe. I choćby Ci mówili, że nigdy nic nie zmienią. Wierz mi, zmienią.
Opcja nr 2. Przeniesienie formuł do skryptu i wyplucie poprawnych danych. Minus sporo programowania i debugowania, żeby to działało "jak było".
Opcja 3: rezygnacja z PHPa na rzecz jakiegoś PowerBI czy innego przetwarzania danych z bazy.
Opcja 4: podłączenie obecnego Excela do bazy. Excel ma takie funkcje. Rezygnujesz z PHP i do arkusza dane pobierają się bazy. Minus to usługa select dla X użytkowników. Dać im Insert to zbrodnia. Zawsze trafi się, zastępstwo i jak skrypt nie zadziała to osoba ponowi próbę 12 razy,.a Ty będziesz potem rzeźbił w bronzie.

Wypróbowałem chyba wszystkie drogi i jak mogę coś polecić to idź w czysty język programowania. Więcej roboty na start ale masz możliwość testów, rozwoju, spójności danych. I nie ma że jak Pani Krysia wbije coś do Excela to się sformatuje jako data, a cały skrypt w komórce ZZX453 wypluje #### zamiast 15.

@obscurity
Pliki xlsm to ''must have'. Nie da się tego wyeliminować. Plik bazowy - zawierający strukturę i wszystkie funkcje - musi być.

Pliki te posiadają jeden arkusz na dane wejściowe. Gdy je zmienimy w 10 innych arkuszach zmieniają się wartości komórek. Większość korzysta z makr/funkcji.
Moim zadaniem jest odpalenie tych wszystkich formuł z konkretnytmi danymi.
Np wpisanie w komórkę A10 wartości "3" wymusza przeliczenie wszystkich komórek, które pośrednio lub bezpośrednio z tej komórki korzystają

Co masz na myśli "komunikował się z Excelem"? W jaki sposób?

@jurek1980
a) Rozwiązanie nr 1
Jak to rozwiązuje mój problem? Jak się pozbędę makr i funkcji to jak dostanę wyliczone przez nie dane? Dostane to co jest domyślnie w szablonie. Jak zmienie komórkę A10 to nic się nie zmieni w innych arkuszach.

b) Opcja nr 2
Nie ma szans... jest to (śmiało mogę powiedzieć) kilka tysięcy formuł na każdy pojedynczy plik.... Te xlsm'y były tworzone przez ostatnich kilka lat... nie ma szans że uda się to przepisać... dodatkowo te pliki są ciągle aktualizowane (zmieniane formuły)... W takim przypadku wszystko co nowe trzeba by od nowa przenosić.

c) Opcja 3
Tylko, że przetwarzanie pliku XLSM to tylko 1/10 całej aplikacji... reszta musi być typowo webowa... Ogólnie z Excela mamy dane i później te dane są wykorzystywane do wielu modułów.

d) Opcja 4
to najsensowniejsze o ile da się zrobić komunikację dwustronną.
Czyli Excel pobierze Dane wejściowe z bazy, przeliczy je i wyśle wyniki z wszystkich arkuszy ponownie do bazy (inna tabela).
Oczywiście wszystko MUSI być bez udziału klienta.... Zadanie użytkownika to tylko kliknięcie na 1 przycisk w obrębie strony www i wtedy te czary mają się zadziać.
Oczywiście jak kliknie w link na telefonie, to musiałoby to się wszystko na serwerze uruchomić. Raczej nie widze możliwości?

jurek1980 - reasumując.
Tutaj właśnie klient nie ma dostępu do pliku XLSM.
Plik XLSM jest bazą i zbiorem funkcji (obliczeń). Uzytkownik ma kliknąć na przycisk i to ma mu się przeliczyć.

Najbardziej trywialny przykład.

Klient widzi na stronie www tabelkę:
Wysokość | Szerokość | Akcja
3000mm 4000mm < button1 >
1500mm 2000mm < button2 >
300mm 7500mm < button3 >

Wysokość i szerokość to moje dane wejściowe przechowywane w bazie.
Po kliknięciu na < button2 > musi zostać otwarty plik xlsm,
w komórkę A2 Arkusz1 ma zostać wpisana wartość 1500
w komórkę A3 Arkusz1 ma zostać wpisana wartość 2000

Wstawienie tych wartości powoduje przeliczenie wszystkich komórek, które w sposób bezpośredni lub pośredni korzystają z komórek A2 i A3
Czyli np
w komórce B10 Arkusz2 mamy funkcję liczącą pole (na podstawie komórek A2 i A3 z Arkusz1)
w komórce B11 Arkusz2 mamy funkcję liczącą obwód (na podstawie komórek A2 i A3 z Arkusz1)

Następnie muszę wszystko z określonych zakładek (czyli wyniki obliczeń) zapisać ponownie do bazy.

Akcja klienta sprowadza się TYLKO do kliknięcia w < button >

To niestety, albo nadajesz PHP owi tyle zasobów, że ten plik działał, albo piszesz wszystko w PHPie czy innym języku. Inaczej co tydzień będziesz szukał po dwa dni co się stało że się zesra.... Szczera prawda i moje doświadczenie. Klient musi przełknąć zmianę technologii.

@jurek1980
Obecnie mam to zrobione na zasadzie czekania na response. Wtedy użytkownik nic nie może zrobić dopóki cały skrypt przetwarzający się nie zakończy i nie zwróci informacji o sukcesie/błędzie.

Obróbka pojedynczego pliku to +/- 15sekund. Im więcej danych wsadowych, tym dłuższy czas oczekiwania.
Pytanie czy nie lepiej:
a) Każde robić asynchronicznie (równolegle) - boje się, że to zabije serwer
b) Zrobić tak jak jest teraz ale całość asynchronicznie (nie czekając na response). Dać tylko status na "processing", a jak w tle się skończy przetwarzać to zmienić status i powiadomić o zakończeniu przetwarzania.

Jeszcze mam z tyłu głowy inne rozwiązanie... czyli ładować wszystkie dane, które mają zostać przeliczone do kolejki i odpalać cały skrypt CRON'em, gdy serwer jest bezczynny (czyli np po godz 16:00, gdy wszyscy kończą pracę).

NewUser2k13 napisał(a):

Co masz na myśli "komunikował się z Excelem"? W jaki sposób?

mam na myśli znalezienie najprostszego sposobu w google, pomijając wymóg PHP. Wydaje się że najłatwiej komunikuje się przez .NETowe biblioteki dostarczane przez microsoft https://learn.microsoft.com/en-us/dotnet/csharp/advanced-topics/interop/how-to-access-office-interop-objects
Więc idea jest po prostu taka że robisz to w dowolny sposób, taki żeby mógł się komunikować z excelem lub obrabiać dane w sensownym czasie i jednocześnie łatwo wystawić API do tego.

NewUser2k13 napisał(a):

a) Każde robić asynchronicznie (równolegle) - boje się, że to zabije serwer
b) Zrobić tak jak jest teraz ale całość asynchronicznie (nie czekając na response). Dać tylko status na "processing", a jak w tle się skończy przetwarzać to zmienić status i powiadomić o zakończeniu przetwarzania.

dobre pomysły, na pewno w przypadku długich zadań lepiej to robić asynchronicznie, user nie będzie musiał czekać tylko może później wrócić na stronę i sprawdzić odpowiedź, proponuję też kolejkowanie zadań tak żeby mieć kontrolę nad używanymi zasobami i żeby jeden użytkownik nie mógł ci zabić serwera