Duplikowanie arkusza XLSX w Delphi przy użyciu HotXLS

Masz już jeden arkusz zrobiony dokładnie tak, jak trzeba. Pas nagłówka jest scalony, szerokości kolumn pasują do danych, dwa górne wiersze są zamrożone, obszar wydruku i marginesy są ustawione pod czysty eksport A4, a karta ma kolor, dzięki któremu dział finansowy łatwo ją znajdzie. Teraz raport potrzebuje dwunastu takich arkuszy, po jednym na region, wszystkie mają startować z tego samego układu. Odtwarzanie tego arkusza w kodzie dwanaście razy to prosty sposób na pojawienie się drobnych rozjazdów: w regionie 7 jedna kolumna będzie o punkt węższa, w regionie 11 zniknie zamrożenie, i nikt tego nie zauważy, dopóki PDF nie trafi na biurko menedżera. W praktyce potrzebujesz programistycznej wersji polecenia Excela z menu kontekstowego Move or Copy, Create a copy: wziąć gotowy arkusz i wybijać z niego niezależne duplikaty

Silnik XLSX w HotXLS, natywnej bibliotece dla Delphi i C++Builder, która odczytuje i zapisuje pliki Excel bez automatyzowania samego Excela, potrafił już przenosić arkusze, usuwać arkusze i kopiować zakresy komórek między arkuszami. Do wersji v2.91.0 nie potrafił jednak sklonować całego arkusza jednym wywołaniem. To wydanie dodaje dwa punkty wejścia: TXLSXWorksheet.CopyFrom, który kopiuje stan na poziomie arkusza z jednego worksheet na drugi, oraz TXLSXSheets.Duplicate, który dodaje nowy arkusz i uruchamia CopyFrom za Ciebie. Najciekawsze nie jest samo to, że coś kopiuje. Istotna jest świadomie wyznaczona granica między tym, co jest kopiowane głęboko, a tym, co nie jest, oraz powód, dla którego ta granica przebiega właśnie tam

Jedno wywołanie do sklonowania gotowego arkusza

Operacją wysokiego poziomu jest Duplicate. Przekazujesz do niej 1-based index arkusza źródłowego, a ona zwraca zupełnie nowy worksheet odzwierciedlający układ i dane oryginału. Konwencja indeksowania jest zgodna z Items[] po stronie XLSX, więc pierwszy arkusz ma indeks 1, a nie 0; jeśli podasz indeks spoza zakresu, dostaniesz nil zamiast wyjątku, czyli ten sam kontrakt błędu, którego używa reszta kolekcji arkuszy XLSX

var
  Book: TXLSXWorkbook;
  Template, Copy: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Template := Book.Sheets.Add('Template');
    Template.Cells[1, 1].Value := 'Quarterly Statement';
    Template.Range['A1:C1'].Merge;
    Template.ColWidth[1] := 18;
    Template.FreezePanes(2, 1);          // freeze top row + first column
    Template.TabColorIsAuto := False;
    Template.TabColor := $FF1F4E79;

    // Clone with an explicit name...
    Copy := Book.Sheets.Duplicate(1, 'Region-North');
    // ...or let it pick the Excel-style default name.
    Copy := Book.Sheets.Duplicate(1);    // -> "Template (2)"

    Book.SaveAs('regions.xlsx');
  finally
    Book.Free;
  end;
end;

W tym fragmencie warto zatrzymać się przy dwóch rzeczach. Po pierwsze FreezePanes przyjmuje argumenty w kolejności row-first, FreezePanes(ARow, ACol), więc jest spójne z indeksowaniem w Cells[Row, Col]; duplikat dziedziczy dokładnie ten sam podział zamrożenia. Po drugie, metoda nazywa się Duplicate, a nie bardziej oczywiście Copy, i nie jest to kwestia stylu. Copy to standardowa procedura z modułu System, stale używana przy stringach i tablicach dynamicznych. Metoda o nazwie Copy w klasie przesłaniałaby ją wewnątrz ciał metod i tworzyła dokładnie ten rodzaj niejednoznaczności rozwiązywania nazw, który wraca do Ciebie za pół roku. Duplicate omija cały problem i wygląda poprawnie w miejscu wywołania

Domyślna nazwa trzyma się reguły samego Excela

Gdy wywołasz przeciążenie z jednym argumentem albo przekażesz pusty string z nazwą, nowy arkusz otrzyma nazwę źródła z dopiskiem (2), a ten dopisek będzie zwiększany, aż nazwa stanie się unikalna. Zduplikuj arkusz Template raz, a dostaniesz Template (2); zduplikuj go ponownie, a dostaniesz Template (3), ponieważ Template (2) jest już zajęte. To odtwarza sposób, w jaki Excel sam nadaje nazwy w poleceniu Create a copy, więc skoroszyt wygenerowany przez Twój kod wygląda tak, jak użytkownik oczekiwałby po ręcznym duplikowaniu. Sprawdzenie unikalności działa na żywej kolekcji arkuszy, więc omija również nazwy utworzone ręcznie, a nie tylko te pochodzące z wcześniejszych duplikacji

Jeśli generujesz jeden arkusz na region albo na miesiąc, lepiej oprzeć się na przeciążeniu z jawną nazwą. Przewidywalny schemat Region-North, Region-South łatwiej później adresować niż ciąg przyrostków (2), (3), a przy tym zachowuje czytelność nazw zdefiniowanych i formuł między arkuszami

Co CopyFrom kopiuje głęboko

Wewnątrz Duplicate dodaje arkusz, a potem wywołuje CopyFrom(ASource), które możesz także wywołać bezpośrednio, gdy chcesz sklonować zawartość do arkusza utworzonego wcześniej. CopyFrom od razu zabezpiecza dwa przypadki degeneracyjne: kopiowanie z nil albo kopiowanie arkusza na niego samego kończy się natychmiastowym powrotem bez żadnej akcji. Wszystko po tym to już właściwe kopiowanie, celowo szerokie zakresem

Najpierw idą dane komórek. CopyFrom pyta źródło o jego UsedRange, czyli ścisły bounding box wypełnionych komórek i scalonych obszarów, po czym ponownie wykorzystuje istniejący mechanizm CopyRangeTo, aby przenieść do celu każdą wartość, formułę i indeks stylu przypisany do komórki, zaczynając od A1. Poza samymi komórkami odtwarza pełną warstwę stanu arkusza, która sprawia, że szablon wygląda na ukończony:

Scalone zakresy, odtworzone po współrzędnych tak, aby pas nagłówka obejmował ten sam prostokąt
Szerokości kolumn i wysokości wierszy, wraz z listami ukrytych, zwiniętych i obrysowanych poziomów, kopiowane dosłownie, tak aby niestandardowe wiersze i kolumny zgadzały się co do joty
Freeze panes oraz stan widoku: poziom powiększenia, wyświetlanie siatki i wartości zerowych, kierunek right-to-left oraz typ widoku
Stan ochrony wraz z opcjami uprawnień dla poszczególnych akcji, dzięki czemu zablokowany szablon pozostaje zablokowany dokładnie w ten sam sposób
Cały blok page setup: marginesy, orientacja, rozmiar papieru, skalowanie i fit-to-page, obszar wydruku, print titles, nagłówki i stopki oraz flagi print-gridlines i print-headings
Zakres AutoFilter, kolor karty oraz widoczność arkusza

W efekcie powstaje arkusz, który drukuje się, filtruje i prezentuje identycznie jak źródło. A ponieważ komórki, scalenia i listy wymiarów są fizycznie odtwarzane w nowym arkuszu zamiast współdzielone przez aliasy, duplikat pozostaje całkowicie niezależny. Wpisz 999 do komórki w kopii, a źródło zachowa swoją pierwotną wartość; ta niezależność jest najważniejszą cechą klona przeznaczonego do równoległych raportów regionalnych, a dostarczony przykład SheetCopy sprawdza to wprost

Co pozostaje płytkie i dlaczego

Teraz uczciwa część historii. Wykresy, osadzone obrazy, tabele XLSX, data validations i reguły conditional formatting nie są kopiowane. To udokumentowana i świadomie wyznaczona granica, a nie przeoczenie, więc warto rozumieć jej uzasadnienie, aby zaplanować pracę wokół niej zamiast dać się zaskoczyć

Każda z tych kolekcji niesie własną tożsamość i referencje, które nie przetrwają naiwnego kopiowania pól. Wykres wskazuje źródłowy zakres danych i posiada relację drawing w pakiecie OOXML; sklonowanie obiektu bez przemapowania relacji i odwołań serii daje wykres renderujący się na złych danych albo pakiet, który Excel oznaczy jako wymagający naprawy. Tabela ma nazwę, która musi być unikalna w skoroszycie, wiersz nagłówka powiązany z konkretnymi kolumnami i własną automatycznie generowaną relację. Conditional formats i data validations przyczepiają się do zakresów współrzędnych, a w przypadku walidacji mogą dodatkowo odwoływać się formułą do innych zakresów. Poprawne deep-copy któregokolwiek z tych elementów oznacza przepisywanie referencji i nadawanie nowych tożsamości, czyli realną pracę z realnymi trybami awarii. Zrobienie tego w połowie, przez skopiowanie obiektu bez jego referencji, jest gorsze niż brak kopiowania: powstaje plik otwierający się z komunikatem o naprawie i po cichu gubiący część zawartości. Dlatego silnik kopiuje te rzeczy, które potrafi skopiować czysto, a kolekcje niosące referencje pozostawia wywołującemu, bo to on wie, na co cel powinien wskazywać

W praktyce oznacza to, że workflow dla bogatszego szablonu wygląda tak: zduplikuj arkusz, aby dostać komórki, układ i ustawienia wydruku, a następnie odtwórz na kopii wykres, tabelę, validations albo conditional formats tym samym API, którego użyłeś za pierwszym razem. Ponieważ tworzysz je ponownie względem zakresów należących już do duplikatu, referencje wychodzą poprawne z samej konstrukcji. Dla wykresu, który odczytuje A1:C10, dodaj nowy wykres w kopii, wskazujący na zakres A1:C10 w kopii; jeśli chcesz zachować żywy AutoFilter, zauważ, że zakres filtra faktycznie się przenosi, więc trzeba ponownie zastosować tylko kryteria kolumn. Reguły conditional formatting i data validation dodasz ponownie przez te same wywołania opisane w artykule o scalonych komórkach i układzie szablonu raportu, który omawia tabelę scaleń i model zakresów dziedziczony przez kopię

Gdzie duplikowanie pasuje do pipeline raportowego

Duplikowanie arkusza jest naturalnym uzupełnieniem generowania opartego na placeholderach. Podejście oparte na tokenach opisane w przewodniku po generowaniu raportów z szablonów w Delphi rozwiązuje problem wpisywania danych do układu, który edytują inni ludzie; duplikowanie rozwiązuje problem wielokrotnego użycia tego układu w jednym skoroszycie. Połączenie obu daje czysty wzorzec: trzymaj jeden nienaruszony arkusz Template z tokenami, scaleniami i ustawieniami wydruku, a potem dla każdego regionu lub okresu wywołaj Duplicate, wypełnij tokeny klona odpowiednim wycinkiem danych i przejdź dalej. Nienaruszony szablon nigdy nie jest modyfikowany, więc pozostaje wiarygodnym źródłem dla kolejnego klona, a każdy arkusz wyjściowy startuje z układu identycznego bajt po bajcie

Jedna uwaga o kolejności oszczędza całą klasę nieporozumień. Duplikuj arkusz przed wlaniem do niego danych, a nie po. Szablon powinien przechowywać strukturę i formatowanie, a nie liczby z poprzedniego kwartału, a klonowanie pustego sformatowanego arkusza oznacza, że każdy duplikat startuje na czysto. Jeśli zduplikujesz arkusz, który już zawiera dane, te dane też pójdą dalej, ponieważ CopyFrom wiernie kopiuje used range; czasem właśnie tego chcesz, ale w raporcie rozgałęzianym na wiele arkuszy zwykle nie

Szybki nawyk weryfikacyjny

Ponieważ podział na deep-copy i shallow-copy jest niewidoczny, dopóki go świadomie nie sprawdzisz, lepiej wbudować w zadanie pięciolinijkową kontrolę niż zakładać, że wszystko się przeniosło. Po duplikowaniu odczytaj z powrotem sygnały strukturalne, które klon powinien odziedziczyć, i potwierdź asercjami, że zgadzają się ze źródłem

Copy := Book.Sheets.Duplicate(1, 'Region-North');
WriteLn(Format('merged=%d  colA=%.1f  freezeRow=%d  tabAuto=%d',
  [Copy.MergedCells.Count, Copy.ColWidth[1],
   Copy.FreezeRow, Integer(Copy.TabColorIsAuto)]));
// Prove independence: mutate the copy, confirm the source is untouched.
Copy.Cells[2, 2].Value := 999;
// Template.Cells[2, 2].Value is still whatever it was.

Liczba scaleń, szerokość kolumny, zamrożony wiersz i flaga koloru karty powiedzą Ci, czy warstwa, którą jest kopiowana, rzeczywiście się przeniosła. Osobno, w każdym arkuszu zawierającym wykres, tabelę, validations albo conditional formats, traktuj te elementy jako listę do odbudowania w kopii: ich brak jest zamierzony, a naprawa to kilka wywołań, nie zgłoszenie błędu. Ten model myślenia, głęboko tam, gdzie jest bezpiecznie, i płytko tam, gdzie pękłyby referencje, w pełni wyjaśnia, jak dobrze używać tej funkcji

Duplikowanie arkuszy oraz opisane tutaj kopiowanie stanu arkusza przez CopyFrom są dostępne od wersji v2.91.0 natywnego komponentu arkuszy kalkulacyjnych HotXLS dla Delphi, razem z uruchamialnym przykładem SheetCopy, który od początku do końca przechodzi przez cały cykl clone-and-mutate