Komponent HotXLS automatycznie koryguje odwołania w formułach podczas wstawiania lub usuwania wierszy bądź kolumn w arkuszu XLSX. Metody silnika InsertRows, DeleteRows, InsertCols oraz DeleteCols modyfikują każdą ocalałą formułę tak, by jej odwołania A1 — względne, bezwzględne oraz zakresy — wskazywały na te same dane po edycji struktury, a odwołania do usuniętych bloków stawały się błędami #REF!, co odpowiada zachowaniu programu Excel
Błąd, któremu to zapobiega, jest jednym z najtrudniejszych do wykrycia podczas generowania raportów. Indeksujący zapisuje codzienne dane w komórkach C2:C9 z formułą =SUM(C2:C9) poniżej, a w kolejnym kroku wstawia wiersz nagłówka na samej górze. Jeśli silnik przeniósłby tylko wartości komórek, pozostawiając tekst formuły bez zmian, funkcja SUM nadal odwoływałaby się do C2:C9, podczas gdy dane znajdują się obecnie w C3:C10 — w rezultacie suma bezgłośnie pomija ostatni dzień i podwójnie liczy nagłówek. Żaden wyjątek nie zostanie zgłoszony, plik otworzy się poprawnie, a wynik będzie po prostu błędny. Przed wersją 2.160 silnik HotXLS XLSX pozostawiał formuły nienaruszone podczas edycji struktury; od wersji 2.160 ich modyfikacja następuje automatycznie i nie wymaga ustawiania żadnej flagi
Co dzieje się z formułami, gdy wstawiasz wiersz w Excelu?
Zasada w Excelu mówi, że odwołania podążają za danymi, a nie adresami. Gdy wstawiany jest wiersz, każde odwołanie, którego indeks wiersza znajduje się w punkcie wstawienia lub poniżej niego, przesuwa się w dół o liczbę wstawionych wierszy; odwołania w całości powyżej punktu wstawienia pozostają nienaruszone. Usuwanie wierszy działa według tej samej reguły w odwrotnym kierunku: odwołania poniżej usuniętego bloku przesuwają się w górę, a odwołania do samego usuniętego bloku stają się błędami #REF!, ponieważ komórki, do których się odwoływały, już nie istnieją. Kolumny zachowują się identycznie wzdłuż drugiej osi. Biblioteka arkuszy kalkulacyjnych, która ma poprawnie współpracować z użytkownikami Excela, musi dokładnie odtwarzać te mechanizmy, ponieważ użytkownicy wnioskują o swoich formułach w ten właśnie sposób
Elementem, który zaskakuje programistów, jest to, że odwołania bezwzględne również się przesuwają
Znaki kotwicy $ w $B$2 decydują o tym, co dzieje się, gdy formuła jest kopiowana lub przeciągana do innej komórki — nie robią one nic podczas edycji struktury. Wstaw wiersz powyżej wiersza 2, a Excel zmieni odwołanie $B$2 na $B$3, zachowując znaki dolara, ponieważ wartość, od której zależy formuła, fizycznie przeniosła się do wiersza 3. Silnik, który przesuwałby wyłącznie odwołania względne, uszkodziłby dokładnie te formuły, które użytkownicy zakotwiczyli najbardziej celowo. HotXLS przesuwa obie formy i zachowuje znaki $ w zmodyfikowanym tekście
Jak HotXLS automatycznie przesuwa odwołania do formuł?
Wszystkie cztery metody edycji struktury w klasie TXLSXWorksheet są przekazywane do jednego silnika geometrii: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). Metoda InsertRows(BeforeRow, Count) wywołuje go z dodatnią wartością zmiany wiersza (row delta), DeleteRows(StartRow, Count) z wartością ujemną, a metody dla kolumn działają identycznie w osi kolumn. Procedura najpierw przenosi same komórki — usuwając te, które trafiają do skasowanego bloku — a następnie przechodzi przez każdą ocalałą komórkę z formułą i przekazuje jej tekst do skanera XlsxAdjustFormulaRowColRefs, który odnajduje odwołania typu A1 i modyfikuje ich składowe wierszy oraz kolumn zgodnie z przesunięciem. Ten sam etap przenosi również scalone zakresy, hiperłącza, komentarze, obrazy, wykresy, formatowanie warunkowe, walidację danych oraz zakresy tabel, dzięki czemu cały arkusz przesuwa się spójnie
// Układ przed edycją:
// C2..C9 dane dzienne
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // jeden pusty wiersz przed wierszem 2
// Układ po wywołaniu:
// C3..C10 dane dzienne
// C11 =SUM(C3:C10) -- zakres przesunął się wraz z danymiWarto poznać kilka szczegółów działania skanera. Funkcja XlsxAdjustFormulaRowColRefs rozpoznaje odwołania do pojedynczych komórek we wszystkich czterech formach kotwiczenia (A1, $A1, A$1, $A$1) oraz zakresy z dwoma punktami skrajnymi, takie jak A1:B3, dostosowując każdy punkt końcowy niezależnie. Formuły, których tekst zaczyna się już od znaku # — wskaźnika błędu z poprzedniej edycji — są pomijane, zamiast ponownego skanowania. Ponadto metoda InsertCols dodaje własny szczegół zachowujący spójność z Excelem: nowo wstawione kolumny dziedziczą szerokość po swoim lewym sąsiedzie, co odpowiada działaniu polecenia Wstaw kolumny arkusza w programie Excel
Kiedy usunięte odwołanie staje się błędem #REF!?
Zasada przesunięcia dla pojedynczego indeksu wiersza lub kolumny ma trzy możliwe wyniki. Indeks przed punktem edycji pozostaje bez zmian. Indeks w punkcie edycji lub za nim przesuwa się o wskazaną wartość. Z kolei przy usuwaniu, indeks wewnątrz skasowanego bloku nie ma już żadnej poprawnej nowej wartości — komórka zniknęła — więc skaner zamienia całe odwołanie w błąd #REF!. Wspaniałe jest to, że w przypadku odwołania do zakresu, oba punkty skrajne są sprawdzane według tej samej reguły, a jeśli którykolwiek z nich trafi do usuniętego bloku, całe odwołanie jest przekształcane w błąd #REF!, zamiast pozostawiać je częściowo poprawne
// A12 zawiera =A4+A6+A10
Sheet.DeleteRows(5, 3); // usuń wiersze 5..7
// The formula, now in A9, reads =A4+#REF!+A7
// A4 : powyżej usuniętego bloku, bez zmian
// A6 : wewnątrz wierszy 5..7, zniknęło -> #REF!
// A10 : poniżej bloku, przesuwa się w górę -> A7Wygenerowanie wyraźnego błędu #REF! zamiast bezgłośnego przepięcia to właściwy kompromis, stosowany również przez Excel. Formuła wskazująca na sąsiednią komórkę po tym, jak jej rzeczywiste dane wejściowe zostały skasowane, zwracałoby pozornie poprawną liczbę; błąd #REF! propaguje się przez formuły zależne i ujawnia się już przy pierwszym teście. Ta sama konwersja zachodzi w osi kolumn
// E1 zawiera =B1*$C$1
Sheet.DeleteCols(3, 1); // usuń kolumnę C
// The formula, now in D1, reads =B1*#REF!
// Zakotwiczenie bezwzględne nie ochroniło $C$1 — sama komórka zniknęłaKtóre formy odwołań nie są modyfikowane?
Skaner przetwarza odwołania A1 do tego samego arkusza posiadające jawny zapis litery kolumny oraz numeru wiersza. Warto doprecyzować, co nie podlega tym modyfikacjom. Odwołania do całych kolumn, takie jak A:A, oraz do całych wierszy, takie jak 1:1, nie posiadają jednej ze składowych, stąd skaner pozostawia je w oryginalnej postaci. Strukturyzowane odwołania do tabel (Table1[Amount]) are likewise passed through untouched. Modyfikacja działa wyłącznie na notacji A1 — jeśli Twój kod buduje formuły w stylu R1C1, przekonwertuj je na notację A1 przed edycją struktury, jak opisano w towarzyszącym artykule o notacji formuł R1C1 w Delphi
Konstrukcje międzyarkuszowe oraz na poziomie skoroszytu są przetwarzane w osobnych etapach, a nie przez skaner tekstu komórek
Po dostosowaniu edytowanego arkusza, funkcja ShiftSheetGeometry propaguje te same zmiany geometrii do formuł w innych arkuszach odwołujących się do edytowanego arkusza, a także do zakresów serii wykresów, wewnętrznych celów hiperłączy i nazw zdefiniowanych (defined names). Nazwy zdefiniowane zyskują dodatkowe zabezpieczenie na poziomie cyklu życia arkusza: od wersji 2.150 usunięcie arkusza zamienia każdy kwalifikator SheetN! w formule nazwy zdefiniowanej w błąd #REF!, a zmiana nazwy arkusza aktualizuje kwalifikator, dzięki czemu nazwy nigdy nie wskazują na nieistniejący arkusz. Kwestie współdziałania nazw i formuł międzyarkuszowych opisano w artykule o nazwach zdefiniowanych i formułach międzyarkuszowych
Ponowne obliczenie po przesunięciu
Korekta odwołań modyfikuje tekst formuły — nie przelicza jednak wyników. Po edycji struktury buforowane wartości zapisane przy formułach opisują starą geometrię, dlatego zalecany potok to: najpierw wykonaj wszystkie wstawienia i usunięcia, następnie uruchom jednokrotne przeliczenie, a na koniec zapisz plik. Przesunięcie przed przeliczeniem utrzymuje spójność informacji o zależnościach — każde zmodyfikowane odwołanie wskazuje na swój rzeczywisty element nadrzędny, co jest niezbędne dla silnika przeliczania przyrostowego i jego grafu zależności do wyznaczenia minimalnego zestawu zmienionych komorek. Jeśli zmodyfikowana formuła zawiera teraz błąd #REF!, przeliczenie natychmiast ujawni ten błąd w pliku zamiast pozostawiania nieaktualnej wartości
Dostosowanie odwołań w formułach to standardowe zachowanie silnika XLSX w komponencie HotXLS Delphi Excel Component dla Delphi i C++Builder; strona produktu zawiera pełną dokumentację API edycji arkuszy, w tym pokazane tu metody wstawiania i usuwania