Osadzanie obiektów wykresów na arkuszach z HotXLS

HotXLS potrafi położyć wykres bezpośrednio na arkuszu, zakotwiczony w zakresie komórek, zamiast oddawać mu dedykowany arkusz wykresów. W terminach BIFF8 to znaczy zapisanie kształtu rysunku z rekordem OBJ typu 5 i zaparkowanie substreamu wykresu na końcu strumienia rekordów arkusza, czyli dokładnie ten layout, który produkuje Excel, i dokładnie to miejsce, gdzie reader spodziewa się go znaleźć

To rozróżnienie ma znaczenie dla każdego, kto generuje raporty operacyjne. Arkusz wykresów to dobry dom dla pojedynczej wizualizacji na pierwszy plan. Miesięczne zestawienie regionalne chce wykres obok liczb, które podsumowuje, na tym samym arkuszu, wyskalowany do bloku komórek, do którego należy, żeby czytelnik przewinął raz, zamiast przeskakiwać między kartami i gubić kontekst

Czytanie już było, zapisu nie było

Warto nazwać tę asymetrię, bo kształtuje ona pracę. HotXLS umiał już czytać osadzone wykresy: gdy strumień rekordów arkusza zawiera BOF oznaczony jako substream wykresu, parser przełącza kontekst, zbiera rekordy wykresu i przy zamykającym EOF oddaje je kształtowi rysunku, którego wprowadził rekord OBJ. Tą ścieżką przeszedł każdy skoroszyt napisany przez Excela, jaki biblioteka kiedykolwiek otworzyła

Brakowało strony autorskiej, a pożyteczną konsekwencją jest to, że nowy writer miał precyzyjną specyfikację do trafienia: wyprodukuj układ bajtów, który istniejący reader już umie z powrotem dopiąć. Nie ma lepszego kryterium akceptacji dla funkcji formatu binarnego niż niezależnie napisany reader, którego nie mogłeś zmienić

Z czego składa się osadzony wykres

Trzy kawałki muszą się zgadzać. Warstwa rysowania dokłada kształt kontrolki hosta, warstwa obiektów rekord OBJ, którego wspólne dane obiektu deklarują typ obiektu 5, a strumień rekordów sam substream wykresu. Flagi opcji na rekordzie OBJ to te, które Excel pisze dla ramki wykresu: positioned, locked, automatic line i automatic fill, i to one sprawiają, że osadzony wykres zachowuje się jak natywny, gdy użytkownik w niego kliknie

HotXLS kotwiczy substream wykresu BIFF8 do arkusza Delphi przez trzy zgodne kawałki: kształt kontrolki hosta warstwy rysowania, rekord OBJ, którego wspólne dane obiektu deklarują typ obiektu 5, oraz łańcuch rekordów wykresu zaparkowany na końcu strumienia rekordów arkusza, gdzie BOF wykresu przełącza kontekst parsera, a zamykający EOF dopina rekordy z powrotem — Trzy warstwy niosą jeden osadzony wykres: kształt rysunku go kotwiczy, rekord OBJ typuje go jako hosta wykresu, a substream wykresu na końcu strumienia arkusza dostarcza rekordy, które reader dopina z powrotem

Kotwica zasługuje na wzmiankę, bo to popularne źródło błędów off-by-one. API HotXLS bierze numery wierszy i kolumn liczone od jedynki, zgodnie z resztą biblioteki, a client anchor zapisywany do pliku jest liczony od zera. Konwersja dzieje się wewnątrz AddChartObject, więc wołający zostają w układzie współrzędnych, którego używają wszędzie indziej, ale każdy, kto porównuje hex dump z własnym wywołaniem, musi pamiętać, po której stronie tej granicy czyta

var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  Series: array[0..1] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('regional-sales.xls');
    Sheet := Book.Sheets[0];

    FillChar(Series, SizeOf(Series), 0);
    Series[0].Name := 'Actual';
    Series[0].Categories := 'Data!$A$2:$A$13';
    Series[0].Values := 'Data!$B$2:$B$13';
    Series[0].DataLabels.ShowValue := True;
    Series[0].HasDataLabels := True;

    Series[1].Name := 'Target';
    Series[1].Categories := 'Data!$A$2:$A$13';
    Series[1].Values := 'Data!$C$2:$C$13';
    Series[1].SecondaryAxis := True;

    // Zakotwiczony w E2:M20 na tym arkuszu, liczone od jedynki
    Sheet.AddChartObject(xlsChartTypeColumn, 'Regional sales',
      'Month', 'Amount', Series, 2, 5, 20, 13);

    Book.SaveToFile('regional-sales-charted.xls');
  finally
    Book.Free;
  end;
end;

FillChar na tablicy serii to nie dekoracja. TXLSChartSeriesInfo wozi kilka opcjonalnych podrekordów — etykiety danych, styl per seria, linie trendu i słupki błędów — każdy za bramką booleanową, a częściowo zainicjalizowany rekord na stosie wręczy emitterowi flagi, których nikt nie ustawił. Wyzeruj tablicę, potem ustaw pola, o które ci chodzi

Które referencje serii przyjmuje ścieżka osadzona?

Zwykłe zakresy w stylu A1 wewnątrz tego samego skoroszytu i to ograniczenie jest świadome, a nie przeoczenie. Każda referencja jest rozwiązywana względem listy arkuszy skoroszytu i zamieniana na indeks referencji zewnętrznej, którego potrzebują rekordy wykresu. Nazwany zakres albo referencja do zewnętrznego skoroszytu schodzą na placeholder z wyrażeniem sparsowanym o zerowej długości, więc wykres zapisuje się czysto, ale ta konkretna seria nie ma źródła danych, dopóki nie skierujesz jej na zakres

Tylko zwykłe zakresy w stylu A1 wewnątrz tego samego skoroszytu kompilują się do referencji serii wykresu; wszystko inne zapisuje się czysto jako placeholder, dopóki nie zostanie przekierowane, a pełna ścieżka mieszka na AddChartSheet

Powód to czysty inżynierski kompromis. Pełna ścieżka kompilacji referencji istnieje na trasie arkusza wykresów, owinięta wewnątrz warstwy kolekcji arkuszy, i jej czyste wyciągnięcie oznaczałoby zduplikowanie stu linii logiki rozwiązywania dla przypadku rzadkiego w praktyce. Osadzony wykres prawie zawsze kreśli komórki z własnego arkusza albo sąsiedniego arkusza danych. Referencje nazwane i zewnętrzne są obsłużone na trasie arkusza wykresów przez AddChartSheet, więc nic nie jest niedostępne, po prostu sięga się z innego punktu wejścia

Wszystko inne w modelu serii działa identycznie na obu trasach. Wiązanie osi wtórnej, styl linii, wypełnienia i markera per seria, linie trendu, słupki błędów i etykiety danych to wszystko części TXLSChartSeriesInfo i wszystkie emitowane tak samo, więc definicja wykresu może przenosić się między obiektem osadzonym a arkuszem wykresów, zmieniając tylko wywołanie. Mechanikę grup osi za flagą osi wtórnej opisuje grupy osi wtórnej przy zapisie BIFF

Dlaczego tytuł wykresu czytał się jako dwa znaki?

Bo liczbę znaków podano tam, gdzie oczekiwano liczby bajtów, a ciągi BIFF Unicode czynią tę pomyłkę łatwą do wpisania i trudną do zobaczenia. Krótki ciąg BIFF Unicode zaczyna się od liczby znaków i bajtu flag, a bajt flag niesie bit high-byte mówiący, czy payload ma jeden bajt na znak, czy dwa. Przeczytasz payload 16-bitowy z liczbą znaków tak, jakby była długością w bajtach, i dostaniesz dokładnie połowę ciągu: seria o nazwie Sales wraca jako Sa, a tytuł wykresu ucina się tak samo, bo tytuły i etykiety serii dzielą ścieżkę dekodowania

Co czyni ten defekt godnym uwagi, to to, że nawrócił się trzy razy w tej samej rodzinie rekordów: raz w nazwach linii trendu, raz w nazwach wykresów przestawnych i raz w tytułach wykresów. Każde wystąpienie wyglądało jak świeży błąd w nowej funkcji. Wszystkie trzy były tym samym brakującym mnożeniem. Reguła, która to w końcu zamknęła, jest mechaniczna i powinna być stosowana bez osądu: ilekroć czytasz któryś z tych ciągów, zajrzyj najpierw do flagi high-byte i pomnóż liczbę znaków przez szerokość payloadu, zanim dotkniesz bufora. Detale na poziomie rekordów są w artykule dekodowanie liczby znaków XLUnicodeString i flagi high-byte

// Osadzony wykres dzieli warstwę rysowania z obrazami i kształtami,
// więc istniejący rysunek na arkuszu zostaje zachowany. AddChartObject
// zwraca indeks utworzonego obiektu
var
  ObjIndex: Integer;
begin
  ObjIndex := Sheet.AddChartObject(xlsChartTypeLine, 'Trend',
    'Week', 'Units', Series, 2, 8, 18, 16);
  if ObjIndex < 0 then
    raise Exception.Create('chart object was not created');
end;

Gdzie osadzone wykresy się mieszczą na tle alternatyw

Istnieją trzy trasy i odpowiadają na różne pytania. Osadzony obiekt wykresu należy obok swoich danych na arkuszu i tego chcą większość raportów. Arkusz wykresów pasuje do pojedynczej wizualizacji prezentacyjnej i daje pełną ścieżkę kompilacji referencji. Zachowanie istniejącego wykresu z załadowanego pliku nietkniętego to właściwa odpowiedź, gdy skoroszyt przyszedł z Excela z formatowaniem, którego nikt nie chce, by biblioteka reinterpretowała; to zachowanie pass-through opisuje zachowany ChartML i wykresy kombinowane

Ponieważ osadzony wykres jedzie warstwą rysowania, współistnieje z obrazami i kształtami na tym samym arkuszu, zamiast je zastępować, a ogólny model tej warstwy opisuje wykresy, obrazy i rysunki w HotXLS. Wszystkie trzy trasy są częścią komponentu arkuszy HotXLS dla Delphi, więc wybór dotyczy tego, jak raport ma wyglądać, a nie tego, co biblioteka potrafi wyrazić

Punkt metodologiczny jest tym wartym zapamiętania. Gdy funkcja formatu binarnego ma istniejącego readera, buduj writeera pod readera, a nie pod swoje czytanie specyfikacji. Reader koduje lata kontaktu z plikami, które prawdziwe aplikacje faktycznie produkowały, włącznie z fragmentami luźno opisanymi w specyfikacji, a writer, który go zadowala, jest znacznie bardziej skłonny zadowolić i Excela