HotXLS Diagramme und Bilder: Excel-Zeichnungen in Delphi

Alles, was über dem Raster eines Arbeitsblatts schwebt (ein Diagramm, ein Logo, ein Stempel, ein Hinweisfeld), ist ein Zeichnungsobjekt, und ein Zeichnungsobjekt ist über zwei Dinge definiert: was es ist und wo es verankert ist. Der Anker ist der Teil, den die meisten falsch machen. Ein Diagramm wohnt nicht in einer Zelle; es sitzt in einem Rechteck, das an eine Spanne von Zeilen und Spalten geheftet ist, und die Daten, die es darstellt, sind ein getrennter Satz von A1-Bezügen, von dem der Anker nichts weiß. Verschieben Sie den Rahmen, und die Darstellung bleibt, wo sie war. Fügen Sie darunter Zeilen ein, und der Rahmen rutscht mit ihnen nach unten. Diese beiden Koordinatensysteme auseinanderzuhalten ist der größte Teil dessen, was Zeichnungscode gutartig macht

HotXLS ist eine native Object-Pascal-Bibliothek, die XLS und XLSX ohne Excel-Automatisierung liest und schreibt, und sie trägt zwei getrennte Zeichnungsmodelle, weil die beiden Dateiformate Zeichnungen unterschiedlich ablegen. Das BIFF8-Format .xls hält Diagramme auf eigenen, dafür vorgesehenen Blättern und schwebende Formen in einem OfficeArt-Stream, der am Arbeitsblatt hängt. Das OOXML-Format .xlsx kann ein Diagramm mitten im Raster einbetten, verankert an einem Zellrechteck, neben derselben Sorte schwebender Bilder und Formen. Das Objektmodell spiegelt diese Trennung, und die Fehlschläge, über die zu schreiben sich lohnt, entstehen alle daraus, dass man die Regeln des einen Formats auf das andere anwendet

Welcher Container was aufnehmen kann

Die Wahl des Containers muss vor jedem Diagrammcode stehen, denn die verfügbaren Objekttypen unterscheiden sich zwischen beiden:

• XLS (BIFF8): Diagramme leben auf eigenen Diagrammblättern, die über AddChartSheet auf der Sheets-Auflistung entstehen. Bilder, Textfelder, Rechtecke, Ovale und Linien sind OfficeArt-Formen, die über die Shapes-Auflistung des Arbeitsblatts verwaltet werden. Es gibt keine API, um ein Diagramm in ein normales Arbeitsblattraster einzubetten
• XLSX (OOXML): Diagramme lassen sich mit TXLSXWorksheet.AddChart direkt in ein Arbeitsblatt einbetten, verankert an einem Zellrechteck, oder mit TXLSXWorkbook.AddChartSheet auf einem eigenen Diagrammblatt ablegen. Bilder kommen mit AddImage oder AddImageFromFile hinein, schwebende Beschriftungen mit AddTextBox

Eine Anforderung in der Form "ein Dashboard-Blatt mit dem Diagramm neben den Zahlen" ist also in Wahrheit eine Anforderung nach .xlsx. In .xls können Sie sie nur annähern, indem Sie das Diagramm auf ein eigenes Blatt schieben, was verändert, wie der Nutzer durch die Datei navigiert, und ebenso verändert, wie sich Ihr Code verhalten muss. Das Blatt, das AddChartSheet auf der XLS-Seite zurückgibt, ist ein Diagramm-Substream, kein Raster: Schreibt man mit Cells.Item hinein, entsteht ein inkonsistenter Zeichnungsstream, der fehlerfrei erzeugt wird und den Excel dann beim Öffnen verwirft. Das Diagramm verschwindet einfach, und nichts im Build-Log sagt, warum. Behandeln Sie das zurückgegebene Blatt als reines Diagrammblatt, und die ganze Klasse der Meldungen über fehlende Diagramme löst sich auf

Ein Diagramm in ein XLSX-Arbeitsblatt einbetten

Der XLSX-Weg ist der mit Bewegungsspielraum, und hier werden die beiden Koordinatensysteme aus der Einleitung konkret. Das Ankerrechteck, das an AddChart übergeben wird, ist in Zeilen und Spalten des Arbeitsblatts ausgedrückt und legt fest, wo der Diagrammrahmen sitzt. Die Datenreihen sind als absolute A1-Bezüge samt Blattname ausgedrückt. Beide sind unabhängig: Sie können den Rahmen ans andere Ende des Blattes schieben, und er stellt weiterhin dieselben Zellen dar

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Chart: TXLSXChart;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Sheet.Cells[1, 1].Value := 'Region';
    Sheet.Cells[1, 2].Value := 'Revenue';
    Sheet.Cells[2, 1].Value := 'East';
    Sheet.Cells[2, 2].Value := 1184350;
    Sheet.Cells[3, 1].Value := 'Central';
    Sheet.Cells[3, 2].Value := 902210;
    Sheet.Cells[4, 1].Value := 'West';
    Sheet.Cells[4, 2].Value := 1010675;

    // Rahmen verankert an den Zeilen 6..22, Spalten 1..8
    Chart := Sheet.AddChart(xlsxChartColumn, 'Revenue by Region', 6, 1, 22, 8);
    Chart.AddSeries('Revenue', 'Sales!$A$2:$A$4', 'Sales!$B$2:$B$4');
    Chart.ValueAxisTitle := 'USD';

    Sheet.AddImageFromFile(1, 5, 'logo.png');
    Book.SaveAs('dashboard.xlsx');
  finally
    Book.Free;
  end;
end;

Das Argument, das zubeißt, ist der Bereichsstring, den man AddSeries in die Hand drückt. Er ist ein Literal, im Moment des Aufrufs festgehalten, und er ahnt nichts davon, dass Sie danach vielleicht noch zwanzig Datenzeilen anhängen. Bauen Sie ihn aus einer Zeilenzahl, die Sie nach dem Schreiben der Daten ermittelt haben, niemals davor. Punkt- und Blasendiagramme belegen dieselben zwei Argumente mit anderer Bedeutung: Der Kategorienbereich liefert jetzt die X-Werte und der Wertebereich die Y-Werte, und der Blasenradius stammt aus einem dritten Bezug, der über BubbleSizeRange auf der zurückgegebenen TXLSXChartSeries gesetzt wird. Lesen Sie den Aufruf als "X, Y, Größe" statt als "Kategorien, Werte", sobald Sie die Familie der Säulen- und Balkendiagramme verlassen

TXLSXChartType umfasst Säulen-, Balken-, Linien-, Kreis-, Flächen-, Ring-, Punkt-, Blasen- und Netzdiagramme, was das alltägliche Berichtsrepertoire abdeckt. Für ein seitenfüllendes Diagramm ohne umgebendes Raster liefert Book.AddChartSheet ein Blatt, dessen Eigenschaft IsChartSheet true ist. Es ist das .xlsx-Gegenstück zum alten Diagrammblatt und trägt dieselbe Erwartung: Schreiben Sie keine Zellinhalte hinein

Bilder kommen als Bytes hinein und werden in EMU bemaßt

Es gibt zwei Überladungen zum Einfügen eines Bildes, und sie zu verwechseln ist der Bildfehler, der im Code-Review am häufigsten auftaucht. AddImage(ARow, ACol, AData, AFormat) will die bereits kodierten Bildbytes in AData: den Rohinhalt einer PNG-, JPEG-, GIF- oder BMP-Datei. Übergeben Sie ihr einen Dateipfad, haben Sie einen vierzig Byte langen String gespeichert, den kein Betrachter dekodieren kann, also genau die Meldung über ein kaputtes Bildsymbol, die Sie nach dem Ausrollen nicht suchen wollen. Liegt die Quelle als Datei auf der Platte, rufen Sie stattdessen AddImageFromFile auf und lassen Sie die Bibliothek die Bytes lesen und das Format bestimmen

Dann kommt die Bemaßung. DrawingML misst nicht in Pixeln; es misst in English Metric Units, wobei 914400 EMU einen Zoll ergeben und bei 96 DPI 9525 EMU ein Pixel. Das Objekt TXLSXImage stellt WidthEMU und HeightEMU bereit, ein Logo, das 180 mal 60 Pixel groß erscheinen soll, braucht also 1714500 mal 571500 EMU. Legen Sie diese Umrechnung in eine benannte Konstante und rechnen Sie dagegen. Magische Zahlen wie 1714500, über den Code verstreut, sind unlesbar und beim ersten Ändern der Ziel-DPI still falsch. Ankerzeile und Ankerspalte sind übrigens 1-basiert, passend zur restlichen Zell-API und nicht zur 0-basierten EMU-Rechnung

Diagrammblätter und Formen in alten XLS-Dateien

Auf der BIFF8-Seite nimmt die reichhaltigere Überladung von AddChartSheet den Diagrammtyp, die Achsentitel und ein offenes Array von TXLSChartSeriesInfo-Datensätzen entgegen, in denen jeder Datensatz einen Namen sowie einen Kategorien- und einen Wertebereich als Strings hält. Schwebende Formen sind eine andere Sache: Sie kommen auf das Datenarbeitsblatt selbst, über dessen Shapes-Auflistung, nicht auf das Diagrammblatt

var
  Book: IXLSWorkbook;
  Data, Trend: IXLSWorksheet;
  Series: array[0..0] of TXLSChartSeriesInfo;
begin
  Book := TXLSWorkbook.Create;   // schnittstellengezählt: nicht selbst freigeben
  Data := Book.Sheets.Add;
  Data.Name := 'Data';
  Data.Cells.Item[1, 1].Value := 'Month';
  Data.Cells.Item[1, 2].Value := 'Units';
  Data.Cells.Item[2, 1].Value := 'Apr';
  Data.Cells.Item[2, 2].Value := 1530;
  Data.Cells.Item[3, 1].Value := 'May';
  Data.Cells.Item[3, 2].Value := 1721;

  Series[0].Name := 'Units';
  Series[0].Categories := 'Data!$A$2:$A$3';
  Series[0].Values := 'Data!$B$2:$B$3';
  Trend := Book.Sheets.AddChartSheet('Trend', xlsChartTypeLine,
    'Units sold', 'Month', 'Units', Series);
  // Trend ist ein Diagramm-Substream: niemals Zellmethoden darauf aufrufen

  Data.Shapes.AddTextBox('Source: ERP nightly export', 6, 1, 8, 4);
  Data.Shapes.AddPicture('approved-stamp.bmp');
  Book.SaveAs('trend.xls');
end;

Zwei Details zur Lebensdauer sind hier wichtig, und sie ziehen in entgegengesetzte Richtungen. TXLSWorkbook wird über die Schnittstelle IXLSWorkbook gehalten und ist referenzgezählt, ein eigener Aufruf von Free löst also eine doppelte Freigabe aus. TXLSXWorkbook aus den vorigen Abschnitten ist ein gewöhnliches Objekt und muss in einem try..finally freigegeben werden. Derselbe Prüfer, der ein fehlendes Free auf der XLSX-Seite anmerkt, muss auf der XLS-Seite ein vorhandenes anmerken, was eine echte Stolperfalle ist, wenn Sie in einer Unit mit beiden Formaten arbeiten. Die Formenhelfer selbst sind einheitlich: AddRectangle, AddOval und AddLine, dazu DeleteInRange, um einen Bereich von Zeichnungen zu leeren, verankern alle über Zeilen- und Spaltenpaare, sodass eine Vorlage, die darüber Zeilen einfügt, sie zusammen mit dem Raster verschiebt

Eine weitere Eigenschaft verdient sich bei alten Dateien ihren Platz. TXLSPicture.TransparentColor maskiert eine gewählte Hintergrundfarbe aus einer Bitmap heraus, und so legen Sie einen nicht rechteckigen Stempel (ein "Approved"-Siegel, ein Wasserzeichen) über das Raster, in einem Format, dessen BIFF-Darstellung PNG-Alpha nie gelernt hat. Setzen Sie die Farbe, gegen die der Stempel gestaltet wurde, und das umgebende Rechteck verschwindet

Designfarben überleben keinen BIFF8-Rundlauf

OOXML-Zeichnungsfüllungen können auf einen Designfarbenplatz zeigen, weshalb das Umfärben einer ganzen .xlsx-Datei über den Austausch ihres Designs billig ist. BIFF8-Zeichnungsdatensätze haben keinen solchen Platz. Wendet HotXLS eine Designfarbe auf eine XLS-Zeichnung an, löst es die Farbe zu einem literalen RGB-Wert auf und speichert diesen; der Designindex, aus dem sie stammte, ist in dem Augenblick verloren, in dem die Datei geschrieben wird, und erneutes Öffnen kann ihn nicht zurückholen. Das erwischt besonders White-Label-Berichtswerkzeuge, also die Sorte, die dasselbe erzeugte Dokument für viele Kunden neu einkleidet. Halten Sie die Zuordnung von Design zu RGB in Ihrer eigenen Konfiguration und wenden Sie sie bei jeder Erzeugung neu an, statt zu erwarten, sie aus einer gespeicherten .xls-Datei zurücklesen zu können

Eine verwandte Entscheidung taucht auf der Leistungsseite auf. Der XLS-Fassade lässt sich sagen, dass sie die Zeichnungsebene gar nicht erst analysieren soll, wenn Sie von einer großen Altdatei nur die Zellwerte brauchen, indem Sie _DisableGraphics auf true setzen, und das spart bei Massenlesevorgängen echte Zeit. Der Haken ist endgültig: Eine so geöffnete Arbeitsmappe hat keinen OfficeArt-Stream im Speicher, das Speichern schreibt die Zeichnungen also aus der Welt. Halten Sie das Flag für rein lesende Auswertungsläufe zurück. Das größere Leistungsbild steht in unseren Notizen zur Leistung bei großen Arbeitsmappen in HotXLS

Anker stabil halten, während sich das Raster ändert

Berichte bleiben selten so groß, wie sie erzeugt wurden, und hier zahlt sich das Ankermodell aus der Einleitung aus. Die strukturellen Operationen der XLSX-Fassade (InsertRows, DeleteRows und die Spaltenentsprechungen) verschieben die abhängigen Ebenen zusammen mit den Zellen. Verbundene Bereiche, Hyperlinks, Kommentare, fixierte Fenster, Filterbereiche, bedingte Formate, Gültigkeitsprüfungen, Tabellen, definierte Namen und, für dieses Thema, Bild- und Diagrammanker wandern alle gemeinsam. Ein bei Zeile 1 verankertes Logo bleibt oben, wenn darunter zehn Zeilen hinzukommen. Ein unter dem Datenblock verankerter Diagrammrahmen rutscht nach unten, während der Block wächst. Das Einzige, was nicht umgeschrieben wird, ist ein Bereichsstring, den Sie vor dem Einfügen als Literal festgehalten haben, denn er ist bloß Text, den die Bibliothek keinen Grund hat noch einmal anzusehen. Damit steht die sichere Reihenfolge für eine Vorlagenbefüllung fest: zuerst die Daten schreiben und umformen, und Diagramme sowie Bilder als letzten Durchgang anlegen, mit jedem Bereichsstring abgeleitet aus den Zeilenzahlen, die Sie nach den Einfügungen haben, nicht davor

Zwei kleinere Werkzeuge vervollständigen den Platzierungskasten. TXLSTextBox.SetArea auf der XLS-Seite verankert ein vorhandenes Textfeld oder eine AutoForm auf einem neuen Zellrechteck neu, was besser ist, als es zu löschen und neu anzulegen, wenn sich ein Fußblock verschiebt. Und die Bitmap-Überladung von AddPicture nimmt eine lebende TBitmap samt optionalem Transparenz-Flag entgegen, sodass alles, was Ihr eigener VCL-Code zeichnen kann (eine Anzeige, ein Sparkline-Streifen, ein Diagrammtyp, den die native Liste nicht anbietet), direkt ins Blatt gestempelt werden kann, ohne vorher eine temporäre Datei zu schreiben

Diagramme und Bilder sind fast immer die abschließende Schicht auf einem bereits strukturierten Bericht, weshalb die Vorarbeit entscheidet, ob sie sauber landen. Das Befüllen der Daten, auf die ein Diagramm verweist, behandelt die vorlagengesteuerte Berichtserzeugung, und das Stabilhalten des Rasters unter Ihren Ankern ist das Thema von verbundenen Zellen und Layoutkontrolle. Die vollständige Klassen- und Methodendokumentation finden Sie auf der Produktseite der HotXLS Delphi Component