Metoda AddCopy w HotXLS kopiuje arkusz z jednego skoroszytu Excela do drugiego, dekompilując każdą formułę na tym arkuszu do tekstu w stylu A1 i rekompilując ten tekst wewnątrz skoroszytu docelowego, zamiast kopiować bezpośrednio skompilowane drzewo formuły, ponieważ odwołania serii wykresu, indeksy czcionek tekstu sformatowanego i numeracja odwołań zewnętrznych są przypisywane niezależnie wewnątrz każdego pliku skoroszytu
Awaria ujawnia się dokładnie w takim skoroszycie, jakiego można by się spodziewać: zadanie na koniec miesiąca, które pobiera jeden arkusz z raportu każdego oddziału i dołącza go do pliku zbiorczego. Otwórz wynik, a wykres sum częściowych rysuje zupełnie inne liczby innego oddziału, notatka, która w źródle była pogrubiona i czerwona, wraca do zwykłego czarnego tekstu, a formuła, która kiedyś pobierała stawkę podatku z towarzyszącego skoroszytu wyszukiwania, teraz pokazuje zamrożoną liczbę, której nikt nie potrafi wyjaśnić. Nic tu nie zgłasza wyjątku — plik się otwiera, liczby wyglądają wiarygodnie, a szkoda tkwi tam, dopóki ktoś nie zauważy wykresu z niewłaściwym tytułem obok siebie
Dlaczego AddCopy nie może po prostu skopiować skompilowanego drzewa formuły?
AddCopy nie może przenieść skompilowanego drzewa formuły bez zmian, ponieważ skompilowana formuła BIFF nie jest samodzielnym tekstem — to sekwencja tokenów, a kilka z tych tokenów to małe liczby całkowite, które rozwiązują się poprawnie wyłącznie wewnątrz skoroszytu, który je wygenerował. Odwołanie 3D, takie jak Sheet2!A1:A10, nie niesie ze sobą dosłownej nazwy Sheet2 po skompilowaniu; niesie pole, które specyfikacja BIFF nazywa ixti (HotXLS zachowuje tę samą wartość we własnym skompilowanym drzewie pod nazwą pola FExternID), indeks do prywatnej tabeli EXTERNSHEET tego skoroszytu, ponumerowanej w taki sposób, w jaki dany konkretny skoroszyt akurat zarejestrował swoje arkusze i zewnętrzne książki. Przenieś token bez zmian do skoroszytu, którego tabela EXTERNSHEET została zbudowana w innej kolejności, a indeks 3 przestaje oznaczać Sheet2 — oznacza tam ten arkusz, który akurat zajmuje slot 3, a Excel nie ma sposobu, by zasygnalizować ten błąd, ponieważ z punktu widzenia formatu pliku formuła jest całkowicie poprawnie sformułowana. To dokładnie ta awaria, której ma zapobiegać TXLSWorksheets.AddCopy: wywołana z kolekcji arkuszy dowolnego skoroszytu w kodzie Delphi lub C++Buildera, kopiuje arkusz — wartości komórek, formaty, formuły, wykresy, komentarze, scalenia, ustawienia strony i więcej — ze skoroszytu źródłowego, który może, ale nie musi, być tym, na którym ją wywołujesz, i dołącza wynik do skoroszytu docelowego pod wybraną przez ciebie nazwą lub jednoznacznie odróżnioną kopią oryginału
var
Summary, Branch: IXLSWorkbook; // interface-counted: do not Free
begin
Summary := TXLSWorkbook.Create;
Branch := TXLSWorkbook.Create;
Branch.Open('branch-east.xls');
// Appends a copy of Branch's first sheet onto Summary, renamed to
// stay unique inside the destination workbook
Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
Summary.SaveAs('consolidated.xls');
end;Rozwiązanie: dekompilacja do tekstu, rekompilacja w miejscu docelowym
HotXLS rozwiązuje problem indeksowania, nigdy nie pozwalając, aby samo skompilowane drzewo przekroczyło granicę skoroszytu. Dla każdej komórki z formułą przy kopiowaniu między skoroszytami AddCopy dekompiluje formułę źródłową do tego samego tekstu w stylu A1, jaki użytkownik zobaczyłby w pasku formuły Excela, a następnie przekazuje ten tekst skoroszytowi docelowemu, który parsuje go z powrotem w drzewo, używając od zera własnych tabel — odwołanie kwalifikowane nazwą arkusza, takie jak Data!D2:D100, jest w tym momencie zwykłym łańcuchem znaków, a łańcuch znaczy to samo w każdym skoroszycie, więc jeśli skoroszyt docelowy ma już arkusz o nazwie Data, odwołanie rozwiązuje się poprawnie bez żadnego tłumaczenia indeksów, ponieważ nigdy nie było w locie surowego indeksu do przetłumaczenia. HotXLS płaci za tę podróż w obie strony tylko wtedy, gdy musi: kopiowanie arkusza wewnątrz tego samego skoroszytu idzie tańszą ścieżką, gdzie skompilowane drzewo jest po prostu duplikowane w pamięci, ponieważ każdy indeks w nim jest już ważny tam, gdzie pozostaje, a objazd przez tekst uruchamia się dopiero, gdy AddCopy wykryje, że źródło i cel są naprawdę różnymi instancjami skoroszytów. Warto też precyzyjnie określić, czym to przepisywanie nie jest. Nie ma nic wspólnego z przesuwaniem wierszy i kolumn, które uruchamia się przy wstawianiu lub usuwaniu wierszy wewnątrz jednego arkusza, co szczegółowo omawia towarzyszący artykuł — ten silnik przepisuje tekst A1 w miejscu, aby śledzić komórki, które przesunęły się o kilka wierszy w górę lub w dół wewnątrz jednego skoroszytu, podczas gdy ten działa, gdy formuła całkowicie opuszcza skoroszyt, który ją skompilował, gdzie przesunięte wiersze nie są problemem, a prywatna numeracja skoroszytu jest
// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);Co jeśli skoroszyt docelowy nie ma jeszcze tego arkusza albo tej nazwy?
Rekompilacja w AddCopy udaje się tylko wtedy, gdy skoroszyt docelowy ma już wszystko, do czego odwołuje się tekst formuły, a dwie luki, które ujawniają się w praktyce, to arkusz o tej samej nazwie, który nie został jeszcze skopiowany w tej partii, oraz nazwa zdefiniowana w zakresie skoroszytu, która nigdy wcześniej nie istniała w miejscu docelowym. HotXLS nie zgłasza wyjątku, gdy rekompilacja zawiedzie w połowie kopiowania arkusza — przypisanie Value komórki po cichu zapisuje tekst formuły jako zwykły łańcuch znaków, celowy, możliwy do zbadania tryb awarii, a nie cichy, ponieważ komórka z formułą, która niespodziewanie pokazuje dosłowny tekst, taki jak =SUM(Q1!B2:B12), zamiast obliczonej liczby, jest sygnałem, że coś wcześniej w kopiowaniu się nie rozwiązało. Zanim się podda, AddCopy próbuje jednej naprawy: przechodzi przez drzewo składniowe nieudanej formuły, zbierając każdy identyfikator nazwy zdefiniowanej, którego formuła dotyka, i dla każdej nazwy w zakresie skoroszytu, która istnieje w źródle, ale jeszcze nie w miejscu docelowym, kopiuje tę nazwę i ponownie kompiluje ten sam tekst po raz drugi. Nazwy w zakresie arkusza znajdują się poza tym, co ta naprawa potrafi naprawić, ponieważ nazwa widoczna tylko dla formuł na jednym arkuszu skoroszytu źródłowego nie ma równoważnego slotu, do którego mogłaby migrować, a miejsce docelowe, które już posiada nazwę o tej samej pisowni, pozostaje nietknięte zamiast nadpisane, przy założeniu, że nazwa celowo utworzona wcześniej przez wywołującego jest tą, którą chce on zachować. Wewnątrz jednego skoroszytu wyszukiwanie nazwy dla formuły międzyarkuszowej automatycznie przechodzi z zakresu arkusza do zakresu skoroszytu, co jest mechanizmem opisanym w artykule HotXLS o nazwach zdefiniowanych i formułach międzyarkuszowych; przekroczenie prawdziwej granicy skoroszytu całkowicie usuwa tę siatkę bezpieczeństwa, i nazwa musi zostać celowo przeniesiona, albo formuła, która od niej zależy, degraduje się do tekstu
Odwołania serii wykresu wymagają tej samej naprawy, ale innej ścieżki kodu
Seria wykresu HotXLS, która rysuje zakres komórek, napotyka dokładnie ten sam problem z numeracją co zwykła formuła komórki, ponieważ odwołanie zakresu danych wykresu to również skompilowany strumień tokenów formuły — specyfikacja BIFF nazywa rekord, który go niesie, BRAI ([MS-XLS] sekcja 2.4.51) — ale AddCopy nie może tego naprawić, ponownie używając zwykłej ścieżki wczytywania wykresu, ponieważ to właśnie ta ścieżka tworzy ten błąd. Gdy rekord wykresu jest parsowany z dysku w zwykłym toku otwierania pliku, jego drzewo formuły jest budowane przez przetłumaczenie surowych bajtów przez dowolną instancję kalkulatora, która akurat parsuje; podaj surowe bajty BRAI wykresu źródłowego przez zwykły ładowacz rekordów skoroszytu docelowego zamiast tego, a ixti osadzony w tych bajtach zostanie rozwiązany względem tabeli EXTERNSHEET miejsca docelowego, więc seria po cichu wskaże ten arkusz, który zajmuje ten slot tam — ta sama klasa błędu co skopiowanie skompilowanego drzewa komórki bez zmian, tylko trudniejsza do zauważenia, ponieważ nikt nie czyta formuł serii wykresu tak, jak czyta formuły komórek. HotXLS unika tej pułapki za pomocą dedykowanej ścieżki klonowania: TXLSCustomChart.AssignFrom kopiuje dosłownie bajty nagłówka każdego rekordu wykresu niebędące formułą, a następnie odbudowuje dołączony zakres za pomocą tego samego prymitywu dekompilacji i rekompilacji, który jest używany dla zwykłych komórek, więc nowe drzewo jest konstruowane od zera względem tabeli EXTERNSHEET miejsca docelowego, a nie reinterpretowane względem niej po fakcie
Ten sam problem numeracji, jeden indeks czcionki na raz
Nie każda liczba lokalna dla skoroszytu wewnątrz wykresu lub komórki z tekstem sformatowanym jest formułą, a indeks czcionki to ta sama klasa problemu w miniaturze. Przebiegi tekstu sformatowanego, obok dwóch dodatkowych typów rekordów wykresu, które niosą czcionkę podpisu lub osi, przechowują odwołanie do czcionki jako surowy indeks liczby całkowitej do własnej tabeli czcionek posiadającego je skoroszytu, a ten indeks nic nie znaczy w tabeli innego skoroszytu — mógłby równie łatwo wskazywać tam na zupełnie inny krój, rozmiar lub kolor. HotXLS rozwiązuje to według wartości, a nie według numeru: odnajduje rzeczywiste atrybuty czcionki pod tym indeksem w tabeli źródłowej, znajduje lub tworzy pasujący wpis w tabeli czcionek miejsca docelowego, i przepisuje zapisany indeks tak, aby wskazywał na ten nowy slot. Jedna osobliwość formatu sprawia, że samo wyszukiwanie jest kłopotliwe — indeks numerowany w pliku pomija slot 4, lukę w numeracji udokumentowaną w [MS-XLS] sekcja 2.5.339, więc kod musi przesunąć indeks o jeden w dół przed porównaniem czcionek i o jeden w górę przed zapisaniem wyniku
// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
Inc(Ifnt);Co dzieje się z formułą, która już wskazuje poza skoroszyt?
Formuła, która sięga do trzeciego skoroszytu, zanim jeszcze wywołasz AddCopy, to jedyny przypadek, którego podróż tekstowa nie potrafi obsłużyć, ponieważ własny dekompilator formuły-do-tekstu w HotXLS celowo nie syntetyzuje tekstu w nawiasach [Book]Sheet! dla odwołania zewnętrznego, a kompilator po drugiej stronie też nie akceptuje takiej składni jako danych wejściowych — więc ten jeden przypadek przechodzi przez drugi mechanizm, który w ogóle nie dotyka tekstu. Gdy opisana wyżej naprawa migracji nazw wciąż pozostawia komórkę jako łańcuch znaków, a skoroszyt źródłowy ma prawdziwą nazwę pliku, AddCopy zmienia strategię: kopiuje głęboko samo skompilowane drzewo formuły, a nie jego tekst, a następnie przekazuje kopię dedykowanemu przebiegowi ponownego wiązania, RebindExternRefsInTree, który przechodzi przez nie węzeł po węźle. Dla każdego znalezionego odwołania do zakresu ten przebieg rozwiązuje wpis EXTERNSHEET źródła z powrotem do pary nazw arkuszy i rejestruje, lub ponownie wykorzystuje, równoważny wpis we własnych tabelach odwołań zewnętrznych miejsca docelowego, tworząc zupełnie nowe łącze do zewnętrznego skoroszytu, jeśli miejsce docelowe nigdy wcześniej nie odwoływało się do tego pliku źródłowego
Właśnie tutaj problem numeracji lokalnej dla skoroszytu jest najbardziej dosłowny, ponieważ token odwołania zewnętrznego łączy trzy osobne współrzędne w jednym polu, i każda z nich jest prywatna dla skoroszytu, który ją zapisał: który skoroszyt zewnętrzny, slot we własnej liście zewnętrznych książek miejsca docelowego, przypisany w takiej kolejności, w jakiej ten skoroszyt akurat je zarejestrował; który arkusz wewnątrz własnej listy arkuszy tego zewnętrznego skoroszytu, przechowywany jako indeks jednostkowy (1-based) w zakresie konkretnie tej zewnętrznej książki, zupełnie inna domena numeracji niż własne wewnętrzne identyfikatory arkuszy miejsca docelowego; oraz sam zakres komórek, zwykłe współrzędne wiersza i kolumny, które nie wymagają tłumaczenia, ponieważ nigdy nie były relatywne względem skoroszytu. Pomyl się w którymkolwiek z pierwszych dwóch, a Excel wciąż otworzy plik, wciąż pokaże formułę i obliczy ją względem niewłaściwych komórek zewnętrznych bez żadnego protestu. Jeden rodzaj węzła pokonuje nawet to ponowne wiązanie na poziomie drzewa: odwołanie do nazwy zdefiniowanej, indeks do prywatnej tabeli nazw własnego skoroszytu dokładnie tak samo, jak indeks arkusza jest prywatny dla własnego EXTERNSHEET, bez dostępnej równoważnej naprawy na poziomie drzewa — w chwili, gdy przebieg ponownego wiązania napotka gdziekolwiek w drzewie odwołanie do nazwy, porzuca całą formułę, zamiast zapisać ją częściowo poprawną. Nawet gdy ponowne wiązanie się powiedzie, komórka docelowa nie pokazuje świeżo przeliczonej liczby; pokazuje wartość, którą komórka źródłowa już posiadała w chwili kopiowania, przechowywaną w buforowanym slocie w taki sam sposób, w jaki sam Excel buforuje ostatnią znaną wartość dowolnego odwołania zewnętrznego, dopóki jawnie nie odświeżysz łączy, co jest właściwym zachowaniem domyślnym, ponieważ przeliczanie na żywym łączu do innego pliku to dokładnie ten rodzaj operacji, którą chcesz wyzwolić raz, świadomie, a nie przy każdym otwarciu
Co ten projekt cię kosztuje
Mechanizm dekompilacji i rekompilacji w AddCopy nie jest darmowy, i koszt ten warto zaplanować przed skryptowaniem dużego zadania konsolidacyjnego, a nie po. Kopiowanie arkusza wewnątrz tego samego skoroszytu idzie tanią ścieżką, bezpośrednim duplikowaniem skompilowanego drzewa w pamięci, ponieważ każdy indeks w nim jest już ważny w skoroszycie, w którym pozostaje; kopiowanie między skoroszytami płaci za prawdziwe parsowanie każdej komórki z formułą, dekompilację do tekstu, a następnie ponowną kompilację tego tekstu od zera, i choć różnica nie jest warta mierzenia na arkuszu z kilkudziesięcioma formułami, skoroszyt źródłowy z dziesiątkami tysięcy komórek z formułami, skopiowany jako jeden arkusz spośród wielu w zadaniu wsadowym, powinien liczyć się z tym, że rekompilacja zdominuje czas wykonania, a nie operacje wejścia/wyjścia pliku wokół niej. Kolejność kopiowania ma znaczenie z drugiego powodu poza szybkością: formuła, która odwołuje się do arkusza, do którego AddCopy jeszcze nie dotarło w tej partii, nie przechodzi rekompilacji z tego samego powodu co formuła odwołująca się do arkusza, który naprawdę nie istnieje, więc zadanie, które kopiuje arkusz B przed arkuszem A, od którego zależy jego formuła, zobaczy, jak ta formuła degraduje się dokładnie tak, jak opisano powyżej, do tekstu łańcuchowego lub zapasowego odwołania zewnętrznego wskazującego z powrotem prosto na plik źródłowy, z którego właśnie pochodziła. A ponieważ każdy skoroszyt źródłowy w partii konsolidacyjnej jest zwykle tworzony niezależnie, warto jawnie przetestować pod kątem jednego trybu awarii, przed którym żaden pojedynczy plik źródłowy nigdy nie mógłby ostrzec — pięć skoroszytów oddziałowych, z których każdy sumuje liczby oddziału partnerskiego, może połączyć się w prawdziwe odwołanie cykliczne wewnątrz skoroszytu zbiorczego, mimo że żaden pojedynczy plik źródłowy nigdy takiego nie zawierał, cykl, który istnieje dopiero, gdy każdy arkusz wyląduje w tym samym miejscu, a przeliczenie uruchomi się nad połączonym zestawem
Kopiowanie arkuszy między skoroszytami jest standardowym zachowaniem AddCopy w komponencie HotXLS Delphi Excel dla Delphi i C++Buildera; strona produktu zawiera pełne odniesienie do API arkuszy i skoroszytów, w tym zachowanie wykresów, tekstu sformatowanego i odwołań zewnętrznych opisane tutaj