Wątek przeniesiony 2023-05-11 15:19 z Nietuzinkowe tematy przez Riddle.

Operacja na pliku arkusza z formułami i makrami po stronie serwera

0

Mam dość rozbudowany arkusz XLSM z kilkoma zakładkami i ogromną ilością formuł i makr.
Działam w PHP.
Przy użyciu biblioteki PHPExcel jestem w stanie odczytać ten plik, wstawić wartości początkowe i na tym zabawa się kończy.

Niestety nie jestem w stanie uzyskać wyniku danej formuły.
Samą formułę jestem w stanie za pomocą w/w biblioteki odczytać ale próba uzyskania wyniku kończy się wyjątkiem.
Z tego co czytałem niestety PHPExcel nie wspiera w 100% formuł i makr.

Być może staram się wynaleźć koło od nowa?
Zależy mi na wrzuceniu swoich danych wejściowych (z bazy danych) w konkretne komórki pliku bazowego (XLSM), automatycznym przeliczeniu wszystkich formuł bazując na nowo wprowadzonych danych i dostęp do wynikowego pliku.
Może to być CSV, "płaski" XLSX z wynikami (bez makr i formuł). Ważne, żeby dało się wszystkie komórki później pobrać/odczytać.

Szukałem ale nie znalazłem narzędzia w PHP, które spełni moje oczekiwania.
Czy jest może jakieś inne rozwiązanie (po stronie serwerowej), które zrealizuje opisane zadanie?

Każda sugestia będzie na pewno bardzo pomocna :)

0

PHPSpredsheet to następca PHPExcel, która wspiera PHP 8.
Także niespodziewałbym się tutaj znacznej różnicy.
Jaki to wyjątek Ci rzuca? Bo z doświadczenia wiem, że włączenie obsługi formuł przy parsowaniu pliku powoduje naprawdę duże zużycie pamięci.

0

@jurek1980
Skorzystałem z biblioteki zaproponowanej przez @hzmzp
O dziwo to funkcjonuje. Udaje mi się wczytać plik XLSM, zmienić wartość początkową i wszystkie formuły działają zwracając poprawny wynik.

Niestety nie mogło obyć się też bez problemów... Nie jestem w stanie zapisać pliku wynikowego. Nie ważne czy do XLSM, XLS, XLSX czy nawet HTML (biblioteka ma taką opcję). Zawsze dostaję błąd (poniżej przy próbie zapisu do XLSX)

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Formula Error: An unexpected error occurred in 
public_html/xlsm/PhpSpreadsheet/Calculation/Calculation.php:5463 Stack trace: #0 public_html/xlsm/PhpSpreadsheet/Calculation/Calculation.php(4550): PhpOffice\PhpSpreadsheet\Calculation\Calculation->raiseFormulaError('Formula Error: ...') #1 
public_html/xlsm/PhpSpreadsheet/Calculation/Calculation.php(3761): PhpOffice\PhpSpreadsheet\Calculation\Calculation->internalParseFormula('IF($Q$30="NZ",'...', Object(PhpOffice\PhpSpreadsheet\Cell\Cell)) #2
public_html/xlsm/PhpSpreadsheet/Calculation/Calculation.php(3535): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue('IF($Q$30="NZ",'...', 'F166', Object(PhpOffice\PhpSpreadsheet\Cell\Cell)) #3 
public_html/xlsm/PhpSpreadsheet/Cell/Cell.php in public_html/xlsm/PhpSpreadsheet/Cell/Cell.php on line 397

Co ciekawe znalazłem tę komórkę w której jest niby wyjątek ( z tą konkretną formuła ) i korzystając z getCalculatedValue() zwraca prawidłowy wynik. Czyli sam silnik obliczania działa.
Formuła ma postać:
=IF($Q$30="NZ",'XXX'!C6,'YYY'!C6)
gdzie XXX i YYY to kolejne arkusze pliku XLSM (zakładki)

Ogólnie jak dla mnie plik wynikowy to może być "płaski" plik XLSX - pozbawiony formuł i makr.
Najlepiej jakby zapisywał tylko kolumny A-D pierwszego arkusza (zakładki). Reszta jest nieistotna.
Ważne żeby formatowanie dokumentu pozostało bez zmian (scalani, układ, czcionki, formatowania itp).

2

być może w samym silniku jest jakiś bug, możesz zrobić report i czekać aż ktoś się tym zajmie lub samemu spróbować naprawić ten błąd
zerknolem w kod gdzie poleciał błąd i komentarz mnie rozbawił

// I don't even want to know what you did to get here
2

No to jak potrzebujesz już tylko plik wynikowy i nie będzie to działanie powtarzalne, to bym tą jedną formułę przepisał na jakąś funkcję i wykonał w PHPie.
Jak to ma działać codziennie przez najbliższe X lat to poprawka do kodu jak pisał @hzmzp
Na koniec jak wybierzesz zapisz do CSV to powinno zapisać przetworzone dane.

0

z tego co na szybko przeglądnąłem wynika że ma problem z policzeniem wartości formuły dla innej zakładki bo nie ma jej jeszcze przetworzonej w cache ale to tylko moje domysły

0

@hzmzp jakiś pomysł jak wyłuskać adres komórki, w którym jest dana formuła (wyrzucona w tym wyjątku)?

0
var_dump($cell);

w Calculation.php(4550) ale nie wiem czy o to chodzi i w czym miało by to pomóc?

0

@hzmzp Patrząc na plik w Excelu za każdym razem przy odpaleniu mam POPUP.
Jeśli wybiorę "Nie Aktualizuj" to część komórek ma niepoprawne adresowanie.
Błąd wyrzucony pochodzi z formuły o postaci:
IF($Q$30="NZ",XXX!#REF!,YYY!C101)
Czyli mamy błąd #REF i to on "psuje" zabawę

Jeśli wybiorę opcję "Aktualizuj" to takich błędów nie ma.

Spróbuje pozbyć się tego popupa w jakiś sposób (jeszcze nie wiem jak bo zawsze wyskakuje nawet jak ponownie zapisze) i zobaczymy czy coś się ruszy w temacie
Muszę zlokalizować to łącze i się go pozbyć...

screenshot-20230512101328.png

2

coś masz nie tak jak powinno zaadresowane


Błąd #REF! jest wyświetlany, gdy formuła odwołuje się do nieprawidłowej komórki. Najczęściej tak się stanie, gdy zostaną usunięte komórki, do których odwołują się formuły, lub zostanie do nich wklejona zawartość.

0

Problem prawie w całości rozwiązany. Dziękuję za pomoc :)

Mam jeszcze małe perturbacje z zapisem do XLSX -> błąd z biblioteką ZipStream, ale to już chyba kwestie serwerowe.
XLS -> zapisuje gubiąc formatowanie.

Niemniej jednak wskazana biblioteka doskonale radzi sobie z formułami o ile plik wejściowy jest wolny od błędów (#ARG! i #REF!), a także nie zawiera linkowań do zewnętrznych zasobów.

1 użytkowników online, w tym zalogowanych: 0, gości: 1