Komponent HotXLS tworzy wykresy przebiegu w czasie (Sparklines) — miniaturowe wykresy rysowane przez program Excel wewnątrz pojedynczej komórki — w Delphi i C++Builderze za pomocą jednego wywołania: metoda TXLSXWorksheet.AddSparklineGroup tworzy grupę wykresów dla wskazanego zakresu danych, a zapisanie skoroszytu umieszcza ją jako blok rozszerzenia x14 w sekcji arkusza extLst. Arkusz ten jest renderowany w Excelu 2010 i nowszych, natomiast starsze czytniki pomijają go bez zgłaszania błędów. Bez obiektów wykresów, bez elementów rysunków, bez kotwiczenia
Scenariusz, w którym warto zastosować wykresy przebiegu w czasie, jest zawsze taki sam. Skoroszyt panelu podsumowania (dashboard) zawiera kilkaset wierszy — po jednym dla każdego produktu, regionu czy konta — z dwunastoma miesięcznymi wartościami, a użytkownik oczekuje miniaturowego wykresu trendu na końcu każdego wiersza. Gdyby chcieć to zrealizować za pomocą tradycyjnych wykresów, należałoby stworzyć setki obiektów DrawingML, z których każdy posiada własną strukturę XML, relacje i geometrię zakotwiczenia — nie wspominając o wpływie takiego rozwiązania na rozmiar pliku i czas jego otwierania. Grupa wykresów przebiegu w czasie to pojedynczy blok XML informujący program: „narysuj małą linię z zakresu B2:M2 w komórce N2 oraz z B3:M3 w N3” — powielony niewielkim kosztem dla dowolnej liczby wierszy
Dlaczego wykresy przebiegu w czasie to rozszerzenie x14, a nie tradycyjny wykres?
Wykresy przebiegu w czasie wprowadzono w programie Excel 2010, trzy lata po zamrożeniu bazowego schematu ECMA-376, dlatego nie mogły one trafić bezpośrednio do głównej struktury arkusza bez zakłócenia pracy istniejących czytników. Rozwiązaniem Microsoftu jest mechanizm list rozszerzeń: arkusz może kończyć się elementem <extLst> zawierającym bloki <ext>, z których każdy oznaczony jest identyfikatorem URI wskazującym na daną funkcję ([MS-XLSX] §2.3.6). Wykresy przebiegu w czasie używają identyfikatora URI {05C60535-1F16-4fd2-B633-F4F36F0B64E0} oraz przestrzeni nazw x14. Czytnik rozpoznający ten identyfikator GUID przetwarza dany blok; czytniki starsze mają za zadanie go pominąć. To cała historia kompatybilności wstecznej i powód, dla którego Excel 2007 otwiera skoroszyt z wykresami bez problemów — po prostu wyświetla wartości liczbowe bez miniaturowych wykresów
Warto zwrócić uwagę na jeden szczegół tego mechanizmu, ponieważ przeczy on częstym założeniom: na poziomie głównym arkusza nie jest wymagana deklaracja mc:Ignorable. Bramka identyfikatora GUID w sekcji ext jest w zupełności wystarczająca, a parser Excela deklaruje przestrzeń nazw x14 lokalnie w elemencie <ext>, zamiast modyfikować korzeń dokumentu. HotXLS stosuje ten sam schemat, co pozwala zachować pozostałą strukturę XML arkusza w nienaruszonym stanie. Na tym polega również różnica strukturalna w porównaniu z tradycyjnymi wykresami: tradycyjny wykres to osobny plik DrawingML z własnym procesem renderowania, co opisano w towarzyszącym artykule o wykresach, obrazach i rysunkach, podczas gdy wykres przebiegu w czasie to metadane arkusza, które rysuje sama siatka kalkulacyjna
Jak programowo dodać wykresy przebiegu w czasie do pliku XLSX?
Funkcja TXLSXWorksheet.AddSparklineGroup(AType, ASourceRange, ALocation) to najkrótsza droga: tworzy grupę wykresów o podanym typie, przypisuje jej domyślny kolor serii Excela i dodaje pierwszy wykres mapujący zakres źródłowy arkusza na komórkę docelową. Zwracany obiekt TXLSXSparklineGroup pozwala na dodawanie kolejnych wykresów za pomocą metody Add w postaci par F/Sqref dla każdego wiersza, współdzielących typ i formatowanie grupy. W przypadku panelu podsumowania to idealny schemat: jedna grupa, jeden zestaw opcji wizualnych i setki powiązanych wykresów
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Grp: TXLSXSparklineGroup;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('dashboard.xlsx');
Sheet := Book.Sheets[0];
// Jedna grupa: każdy wiersz produktu współdzieli typ i formatowanie
Grp := Sheet.AddSparklineGroup(xlsxSparklineLine,
'Sheet1!B2:M2', 'N2');
for Row := 3 to 201 do
Grp.Add(Format('Sheet1!B%d:M%d', [Row, Row]),
Format('N%d', [Row]));
Book.SaveAs('dashboard-trends.xlsx');
finally
Book.Free;
end;
end;Znaczniki, kolory i skalowanie osi w modelu obiektowym
Klasa TXLSXSparklineGroup udostępnia kompletny zestaw opcji znany z paska narzędzi Excela w postaci zwykłych właściwości. Sześć właściwości logicznych pozwala wyróżnić punkty danych: Markers (znaczniki dla każdego punktu wykresu liniowego), HighPoint (punkt najwyższy), LowPoint (punkt najniższy), NegativePoints (punkty ujemne), FirstPoint (punkt pierwszy) oraz LastPoint (punkt ostatni). Odpowiada im osiem właściwości kolorów ARGB — ColorSeries, ColorNegative, ColorAxis, ColorMarkers, ColorFirst, ColorLast, ColorHigh, ColorLow — przy czym wartość 0 oznacza pominięcie zapisu koloru w pliku, przez co Excel zastosuje własne ustawienia domyślne. Ten sam w pełni nieprzezroczysty format ARGB obowiązuje w całej bibliotece, więc zapis $FFD00000 oznacza ten sam kolor czerwony, co w stylach formatowania warunkowego
Grp := Sheet.AddSparklineGroup(xlsxSparklineStacked,
'Sheet1!B2:M2', 'N2');
Grp.NegativePoints := True; // wyróżnienie miesięcy ze stratą
Grp.ColorNegative := $FFD00000;
Grp.HighPoint := True;
Grp.ColorHigh := $FF00B050;
Grp.MinAxisType := xlsxSparklineAxisGroup; // jedna skala dla wszystkich wierszy
Grp.MaxAxisType := xlsxSparklineAxisGroup;
Grp.DisplayEmptyCellsAs := xlsxSparklineEmptyGap;Właściwości osi wymagają krótkiego zastanowienia przed zaakceptowaniem wartości domyślnych. Właściwości MinAxisType oraz MaxAxisType obsługują skalowanie indywidualne, grupowe lub niestandardowe: skalowanie indywidualne dopasowuje każdy miniaturowy wykres do jego własnego minimum i maksimum, co sprawia, że wiersz wahający się między 99 a 101 wygląda równie dynamicznie jak wiersz, którego wartości rosną trzykrotnie. Skalowanie grupowe umieszcza wszystkie wykresy na wspólnej osi pionowej, dzięki czemu wysokość wykresu oznacza tę samą wartość w każdym wierszu — co zazwyczaj jest pożądane w panelach porównawczych. Z kolei właściwość DisplayEmptyCellsAs określa zachowanie wykresu liniowego w przypadku pustej komórki źródłowej: pozostawienie przerwy, traktowanie jako zero lub połączenie sąsiednich punktów
Co HotXLS zapisuje w sekcji arkusza extLst podczas zapisu
Podczas zapisu HotXLS serializuje każdą grupę jako element x14:sparklineGroup element zawierający atrybuty typu i przełączników, kolory jako elementy podrzędne oraz listę x14:sparklines, której elementy łączą formułę zakresu xm:f z komórką docelową xm:sqref. Cała konstrukcja znajduje się w elemencie <extLst><ext uri="{05C60535-1F16-4fd2-B633-F4F36F0B64E0}"> na końcu pliku arkusza, z zadeklarowaną przestrzenią nazw x14 bezpośrednio w bloku, dokładnie tak, jak zapisuje to program Excel. Arkusz, który nie zawiera żadnych wykresów przebiegu w czasie, w ogóle nie generuje sekcji extLst, stąd pliki, w których nie użyto tej funkcji, są identyczne bajt po bajcie z plikami tworzonymi przez wcześniejsze wersje HotXLS — zgodnie z zasadą zachowawczego zapisu opisaną w artykule o infrastrukturze bezstratnego odczytu i zapisu
Ważny przy porównywaniu wyników z Excelem lub ręcznej analizie kodu XML jest jeden szczegół specyfikacji: domyślna grubość linii (lineWeight) w schemacie to 0,75 punktu, a nie 1,25 punktu, jak podają niektóre źródła nieoficjalne — [MS-XLSX] definiuje atrybut CT_SparklineGroup@lineWeight z domyślną wartością 0,75, co odpowiada grubości faktycznie stosowanej przez interfejs Excela. HotXLS przestrzega tej wartości domyślnej poprzez jej pomijanie: ustawienie LineWeight na wartość inną niż 0,75 powoduje zapisanie atrybutu, pozostawienie wartości 0,75 pomija go. Pomyłka w którąkolwiek stronę daje pliki renderujące linie nieco grubsze lub cieńsze niż skoroszyt zapisany przez Excel
Odczyt i zapis, kopiowanie arkuszy i rozszerzenia, których HotXLS nie modeluje
Otwieranie plików to lustrzane odbicie procesu zapisu. Gdy HotXLS otwiera skoroszyt zawierający wykresy przebiegu w czasie — niezależnie od tego, czy został on zapisany przez HotXLS, czy przez program Excel — parser reaguje na identyfikator GUID w sekcji ext i odtwarza każdą grupę, jej elementy, kolory oraz opcje osi w modelu obiektowym, dzięki czemu cykl otwórz-edytuj-zapisz zachowuje je w całości. Kopiowanie arkusza do innego skoroszytu klonuje jego grupy wykresów wraz z pozostałą zawartością arkusza, co ułatwia automatyczne generowanie raportów na podstawie szablonów: zachowaj ostylowany arkusz szablonu, kopiuj go na potrzeby każdego raportu i zaktualizuj zakresy danych
Lista extLst może również zawierać bloki rozszerzeń, dla których HotXLS nie posiada modelu obiektowego — Excel zapisuje w niej bloki x14 dla funkcji takich jak fragmentatory (slicers) czy rozszerzone formatowanie warunkowe. Od wersji v2.131.0 te obce bloki są w całości zachowywane przy odczycie i zapisie: parser przechwytuje je, udostępnia w trybie tylko do odczytu poprzez właściwość Worksheet.RawWorksheetExts i zapisuje z powrotem, a bloki te wędrują wraz z arkuszem podczas kopiowania do innego skoroszytu. Zachowany kod XML jest rekonstruowany ze zdarzeń parsera, dzięki czemu jest semantycznie równoważny z oryginałem, choć nie identyczny bajt po bajcie — jednak program Excel go akceptuje i dane funkcje działają poprawnie. Praktyczna korzyść w środowisku produkcyjnym: otwarcie skoroszytu klienta i jego ponowne zapisanie nie powoduje już usunięcia elementów, których HotXLS nie obsługuje
Szczera granica możliwości: wykresy przebiegu w czasie renderują się w programie Excel 2010 i nowszych oraz w każdym innym czytniku obsługującym rozszerzenie x14; Excel 2007 wyświetla same dane liczbowe bez miniaturowych wykresów — to celowy zabieg projektowy mający na celu łagodne ograniczenie funkcji. Trzy typy wykresów przebiegu w czasie, osiem właściwości kolorów, przełączniki punktów i trzy tryby osi wyczerpują zakres opcji dostępnych w oknie konfiguracyjnym programu Excel. Obsługa wykresów przebiegu w czasie, wraz z pozostałą częścią opisanego tu silnika XLSX, wchodzi w skład HotXLS Delphi Excel Component dla Delphi i C++Builder