Excel-Sparklines in Delphi: HotXLS und die x14-Erweiterung

HotXLS erstellt Excel-Sparklines — die Mini-Diagramme, die Excel in einer einzelnen Zelle zeichnet — in Delphi und C++Builder mit einem einzigen Aufruf: TXLSXWorksheet.AddSparklineGroup erstellt eine Sparkline-Gruppe über einen Datenbereich, und beim Speichern der Arbeitsmappe wird diese als x14-Erweiterungsblock in der extLst du Arbeitsblatts geschrieben, die von Excel 2010 und neuer gerendert und von älteren Readern anstandslos übersprungen wird. Keine Diagrammobjekte, keine Zeichnungsteile, keine Verankerungen

Das Szenario, das Sparklines so wertvoll macht, ist immer dasselbe. Eine Dashboard-Arbeitsmappe enthält einige hundert Zeilen, eine pro Produkt, Region oder Konto, jeweils mit zwölf Monatszahlen, und jemand wünscht sich ein Trend-Symbol am Ende jeder Zeile. Wenn Sie dies mit echten Diagrammen modellieren, erstellen Sie Hunderte von DrawingML-Diagrammteilen, jeder mit seinem eigenen XML-Teil, seiner eigenen Beziehung und Verankerungsgeometrie, ganz zu schweigen von den Auswirkungen auf die Dateigröße und die Ladezeit. Eine Sparkline-Gruppe ist ein einzelner XML-Block, der sagt: "Zeichne eine winzige Linie von B2:M2 in N2 und von B3:M3 in N3", was kostengünstig für beliebig viele Zeilen wiederholt werden kann

Warum sind Sparklines eine x14-Erweiterung statt eines Diagramms?

Sparklines wurden mit Excel 2010 eingeführt, drei Jahre nach dem Einfrieren des ECMA-376-Basisschemas, sodass sie nicht direkt im Markup des Arbeitsblatts platziert werden konnten, ohne alle vorhandenen Reader zu beschädigen. Die Antwort von Microsoft ist der Mechanismus der Erweiterungslisten: Ein Arbeitsblatt kann mit einem <extLst>-Element enden, das <ext>-Blöcke enthält, von denen jeder mit einer URI gekennzeichnet ist, die das Feature identifiziert ([MS-XLSX] §2.3.6). Sparklines verwenden die URI {05C60535-1F16-4fd2-B633-F4F36F0B64E0} und den Namespace x14. Ein Reader, der die GUID erkennt, analysiert den Block; ein Reader, der dies nicht tut, muss ihn überspringen. Das ist die gesamte Vorwärtskompatibilitätsgeschichte, und deshalb öffnet Excel 2007 eine Sparkline-Arbeitsmappe sauber — es zeigt einfach die Quellzahlen ohne die Symbole an

Ein Detail dieses Mechanismus ist hervorzuheben, da es einer verbreiteten Annahme widerspricht: Auf der Root-Ebene des Arbeitsblatts ist keine mc:Ignorable-Deklaration erforderlich. Das Vorhandensein der ext-GUID reicht aus, und Excels eigener Writer deklariert den x14-Namespace lokal auf dem <ext>-Element, anstatt die Root-Ebene zu verändern. HotXLS folgt demselben Muster, wodurch das restliche XML des Arbeitsblatts unberührt bleibt. Dies ist auch der strukturelle Unterschied zu echten Diagrammen: Ein Diagramm ist ein separater DrawingML-Teil mit einer eigenen Rendering-Pipeline, wie im Begleitartikel über Diagramme, Bilder und Zeichnungen beschrieben, während eine Sparkline Arbeitsblatt-Metadaten sind, die das Tabellengitter selbst zeichnet

Wie fügt man Sparklines programmgesteuert zu einer XLSX-Datei hinzu?

TXLSXWorksheet.AddSparklineGroup(AType, ASourceRange, ALocation) ist der direkte Weg: Es erstellt eine Gruppe des angegebenen Typs, befüllt sie mit Excels Standard-Reihenfarbe und fügt die erste Sparkline hinzu, die einen blattqualifizierten Quellbereich auf eine Host-Zelle abbildet. Die zurückgegebene TXLSXSparklineGroup akzeptiert dann über ihre Add-Methode weitere Sparklines, ein F/Sqref-Paar pro Zeile, die alle den Typ und die Formatierung der Gruppe teilen. Für den Dashboard-Fall ist das genau die Struktur, die Sie benötigen: eine Gruppe, ein Satz visueller Optionen, Hunderte von Elementen

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Grp: TXLSXSparklineGroup;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('dashboard.xlsx');
    Sheet := Book.Sheets[0];

    // One group: every product row shares type and formatting
    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;

TXLSXSparklineType deckt die drei Typen ab, die Excel anbietet: xlsxSparklineLine für Trendlinien, xlsxSparklineColumn für winzige Balkendiagramme und xlsxSparklineStacked für Gewinn/Verlust-Streifen, bei denen jeder Wert als Auf- oder Ab-Block dargestellt wird. Jede zugehörige Sparkline besteht aus nur zwei Zeichenketten — F, dem blattqualifizierten Quellbereich, und Sqref, der Zelle, in der das Symbol gezeichnet wird —, sodass pro Zeile nichts weiter konfiguriert werden muss. Gruppen können über die Sammlung Worksheet.SparklineGroups auch vollständig von Hand erstellt werden, wenn Sie mit einer leeren Gruppe statt mit der voreingestellten Standardeinstellung beginnen möchten

Markierungen, Farbslots und Achsenskalierung im typisierten Modell

TXLSXSparklineGroup macht die gesamte Optionspalette aus der Excel-Registerkarte "Sparklinetools" als einfache Eigenschaften zugänglich. Sechs boolesche Schalter heben Datenpunkte hervor: Markers für jeden Punkt auf einer Linien-Sparkline, sowie HighPoint, LowPoint, NegativePoints, FirstPoint und LastPoint. Acht ARGB-Farbslots sind ihnen paarweise zugeordnet — ColorSeries, ColorNegative, ColorAxis, ColorMarkers, ColorFirst, ColorLast, ColorHigh, ColorLow — mit der Konvention, dass ein auf 0 belassener Slot in der Datei weggelassen wird und Excel seine eigenen Standardwerte anwendet. Dieselbe vollständig deckende ARGB-Form wird in der gesamten Bibliothek verwendet, sodass $FFD00000 hier dasselbe Rot bedeutet wie in den Formatierungsmöglichkeiten für bedingte Formatierungen

Grp := Sheet.AddSparklineGroup(xlsxSparklineStacked,
  'Sheet1!B2:M2', 'N2');
Grp.NegativePoints := True;          // losing months stand out
Grp.ColorNegative := $FFD00000;
Grp.HighPoint := True;
Grp.ColorHigh := $FF00B050;
Grp.MinAxisType := xlsxSparklineAxisGroup;   // one scale for all rows
Grp.MaxAxisType := xlsxSparklineAxisGroup;
Grp.DisplayEmptyCellsAs := xlsxSparklineEmptyGap;

Die Achseneigenschaften verdienen eine kurze Überlegung, bevor Sie die Standardeinstellungen übernehmen. MinAxisType und MaxAxisType erlauben jeweils eine individuelle, Gruppen- oder benutzerdefinierte Skalierung: Die individuelle Skalierung dehnt jede Sparkline auf ihr eigenes Minimum und Maximum aus, wodurch eine Zeile, die zwischen 99 und 101 schwankt, genauso dramatisch aussieht wie eine Zeile, die sich verdreifacht. Die Gruppenskalierung setzt alle Elemente auf eine gemeinsame vertikale Skala, sodass die Symbolhöhe in jeder Zeile dieselbe Menge darstellt — was bei einem vergleichenden Dashboard meist erwünscht ist. DisplayEmptyCellsAs bestimmt, wie sich eine leere Quellzelle auf eine Linie auswirkt: eine Lücke hinterlassen, als Null zählen oder die Lücke durch das Verbinden der Nachbarn überbrücken

Was HotXLS beim Speichern in die extLst des Arbeitsblatts schreibt

Beim Speichern serialisiert HotXLS jede Gruppe als ein x14:sparklineGroup-Element, das den Typ und die Schalterattribute enthält, die Farbslots als Kindelemente und eine x14:sparklines-Liste, deren Elemente eine xm:f-Bereichsformel mit einer xm:sqref-Hostzelle verknüpfen. Die gesamte Struktur befindet sich innerhalb von <extLst><ext uri="{05C60535-1F16-4fd2-B633-F4F36F0B64E0}"> am Ende des Arbeitsblatteils, wobei der Namespace x14 direkt auf dem Block deklariert ist, genau wie Excel es schreibt. Ein Arbeitsblatt ohne Sparkline-Gruppen erzeugt überhaupt kein extLst, sodass Dateien, die diese Funktion nie verwendet haben, Byte für Byte identisch mit den von früheren HotXLS-Versionen erzeugten Dateien ausgegeben werden — dieselbe konservative Schreibdisziplin, die im Artikel über die verlustfreie Round-Trip-Infrastruktur beschrieben wird

Ein Detail der Spezifikation ist wichtig, wenn Sie die Ausgabe mit der von Excel vergleichen oder das XML manuell überprüfen: Der Schema-Standardwert für lineWeight beträgt 0,75 Punkte, nicht die 1,25, die einige sekundäre Quellen nennen — [MS-XLSX] definiert CT_SparklineGroup@lineWeight mit einem Standardwert von 0,75, was der Linienstärke entspricht, die Excels Benutzeroberfläche tatsächlich anwendet. HotXLS respektiert den Standardwert durch Weglassen: Wenn Sie LineWeight auf einen anderen Wert als 0,75 festlegen, wird das Attribut geschrieben, belassen Sie es bei 0,75, wird es weggelassen. Dies in der einen oder anderen Richtung falsch zu machen, führt zu Dateien, die sich optisch geringfügig dicker oder dünner darstellen als dieselbe in Excel gespeicherte Arbeitsmappe

Round-Trips, Blattkopien und Erweiterungen, die HotXLS nicht abbildet

Das Öffnen ist das Spiegelbild des Speicherns. Wenn HotXLS eine Arbeitsmappe öffnet, die Sparklines enthält — unabhängig davon, ob HotXLS oder Excel sie geschrieben hat —, schaltet sich der Parser auf die Sparkline-ext-GUID auf und rekonstruiert jede Gruppe, jedes Element, jede Farbe und jede Achsenoption im typisierten Modell, sodass ein Zyklus aus Öffnen, Bearbeiten und Speichern sie vollständig erhält. Das Kopieren eines Arbeitsblatts in eine andere Arbeitsmappe klont seine Sparkline-Gruppen zusammen mit dem Rest des Blatts, was eine vorlagengesteuerte Berichtserstellung erleichtert: Behalten Sie ein formatiertes Vorlagenblatt bei, kopieren Sie es pro Bericht und passen Sie die Bereiche neu an

Die extLst kann auch Erweiterungsblöcke enthalten, für die HotXLS kein typisiertes Modell besitzt — Excel schreibt x14-Blöcke für Features wie Datenschnitte (Slicer) und erweiterte bedingte Formatierungen in dieselbe Liste. Seit v2.131.0 jene fremden Blöcke bleiben über einen Round-Trip hinweg unverändert erhalten, anstatt verworfen zu werden: Der Parser erfasst sie, macht sie über Worksheet.RawWorksheetExts schreibgeschützt zugänglich und schreibt sie beim Speichern wieder zurück. Sie wandern mit einem Blatt mit, wenn dieses in eine andere Arbeitsmappe kopiert wird. Das erhaltene XML wird aus Parser-Ereignissen rekonstruiert, sodass es semantisch äquivalent zum Original und nicht Byte-identisch ist, aber Excel akzeptiert es und die Funktionen bleiben erhalten. Die praktische Konsequenz ist diejenige, auf die es in der Produktion ankommt: Das Öffnen der Arbeitsmappe eines Kunden und das erneute Speichern entfernt keine Erweiterungen mehr, die HotXLS nicht explizit verstehen sollte

Die ehrliche Grenzlinie: Sparklines werden in Excel 2010 und neuer sowie in jedem anderen Reader gerendert, der die x14-Erweiterung implementiert; Excel 2007 zeigt die data ohne die Symbole an, was der Degradierung entspricht, für die das Format entworfen wurde. Die drei Sparkline-Typen, die acht Farbslots, die Punktschalter und die drei Achsenmodi decken die gesamte Optionspalette ab, die Excels eigener Dialog bietet. Die Sparkline-Unterstützung wird zusammen mit dem Rest der hier gezeigten XLSX-Engine in der HotXLS Delphi Excel-Komponente für Delphi und C++Builder ausgeliefert