Excel-Daten mit bedingter Formatierung in JavaScript visualisieren

Eine Verkaufstabelle mit zwanzig Zahlenspalten ist korrekt und unlesbar. Das Auge kann 43.210 nicht schnell genug mit 38.900 über eine Zeile hinweg vergleichen, um das schwache Quartal zu finden, und die Person, die den Bericht liest, weiß das – deshalb bittet sie um ein Diagramm. Aber ein Diagramm pro Spalte bedeutet zwanzig Diagramme, und schon ist das Arbeitsblatt eine Galerie statt einer Tabelle.

Datenbalken, Farbskalen und Symbolsätze lösen dies innerhalb der Zellen selbst. Ein Balken wächst proportional zum Wert. Eine Farbe verschiebt sich von blass zu gesättigt, wenn die Zahl steigt. Ein Symbol ändert seine Form, wenn der Wert eine Schwelle überschreitet. Keine davon fügt Zeilen, Spalten oder schwebende Objekte hinzu – die Visualisierung sitzt in der Zelle, die bereits die Zahl enthält. Alle drei sind Formen der bedingten Formatierung von Excel, und Spire.XLS for JavaScript wendet sie über eine einzige API im Browser auf WebAssembly an, wobei Dateien durch ein virtuelles Dateisystem (VFS) wandern und kein Backend erforderlich ist.

Zur Projekteinrichtung siehe Integrating Spire.XLS for JavaScript in a React Project. Die folgenden Beispiele gehen davon aus, dass das Paket installiert und das WebAssembly-Modul initialisiert wurde.

Warum nicht einfach ein Diagramm hinzufügen

Diagramme und In-Cell-Visualisierungen beantworten dieselbe Frage – „Wie lassen sich diese Werte vergleichen?“ –, passen aber zu unterschiedlichen Momenten:

Wenn das Ziel darin besteht, eine Zahlentabelle ohne Umbau des Layouts überfliegbar zu machen, ist die In-Cell-Visualisierung das leichtere Werkzeug. Die drei folgenden Abschnitte behandeln jeweils einen Typ, und sie teilen mehr API, als sie sich unterscheiden – was das Erste ist, das man wissen sollte.

Voraussetzungen

Sie benötigen ein React-Projekt mit installiertem Spire.XLS for JavaScript und initialisiertem WebAssembly-Modul, erreichbar unter window.wasmModule.spirexls. Das Beispiel lädt eine Schriftart und eine Verkaufsdatendatei in das VFS und speichert mit dem Versionsflag für Excel 2010, der frühesten Version, die diese Typen der bedingten Formatierung unterstützt.

Eine API, drei Visualisierungen

Alle drei Typen folgen derselben Aufrufkette. Die einzige Zeile, die sich ändert, ist die Zuweisung von FormatType:

sheet.ConditionalFormats.Add()  →  xcfs.AddRange(range)  →  format = xcfs.AddCondition()  →  format.FormatType = ???

Die gemeinsame Aufrufkette ist der Grund, warum die drei Codebeispiele unten ähnlich aussehen – sie sind dieselbe Operation mit einem anderen Formattyp. Die Unterschiede liegen darin, was jeder Typ erzeugt und wann man zu ihm greifen würde, was die Vergleichstabelle weiter unten in diesem Artikel behandelt.

Datenbalken: Größenordnung auf einen Blick

Ein Datenbalken zeichnet ein horizontales farbiges Band innerhalb jeder Zelle, und die Länge des Bandes ist proportional zum Wert der Zelle im Verhältnis zum übrigen ausgewählten Bereich. Der größte Wert füllt die Zelle; der kleinste füllt einen schmalen Streifen. Eine Zeile mit Datenbalken zu überfliegen ist dieselbe geistige Operation wie das Überfliegen eines Balkendiagramms, nur dass die Zahlen darunter sichtbar bleiben.

Die Schritte sind:

1. Laden Sie die Schriftart und die Testdatendatei in das VFS.
2. Laden Sie die Arbeitsmappe und rufen Sie das Arbeitsblatt ab.
3. Rufen Sie ConditionalFormats.Add auf, um ein bedingtes Format zu erstellen, und binden Sie den Datenbereich mit AddRange.
4. Rufen Sie AddCondition auf, um eine Bedingung hinzuzufügen, setzen Sie FormatType auf DataBar und legen Sie die Balkenfarbe fest.
5. Speichern Sie die Arbeitsmappe.

function App() {
  const applyDataBars = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'SalesData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Select the data range that receives the data bars
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add a data bar condition and set the bar color
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.DataBar;
    format.DataBar.BarColor = xlsModule.Color.get_CadetBlue();

    // Save the workbook
    const outputFileName = "ApplyDataBars.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Dispose of the workbook object to free resources
    workbook.Dispose();

    // Read the result file from the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Apply Data Bars</h1>
      <button onClick={applyDataBars}>Start</button>
    </div>
  );
}

export default App;

Datenbalken auf eine Verkaufszahlen-Tabelle angewendet, Balkenlänge proportional zum Zellenwert

Nur numerische Zellen erhalten Balken – Textzellen innerhalb des Bereichs werden übersprungen. Das ist zu erwarten: Ein Datenbalken drückt eine relative Größenordnung aus, und Text hat keine Größenordnung auszudrücken. Halten Sie den Bereich auf die numerische Fläche begrenzt; das Einbeziehen einer Produktnamensspalte oder einer Kopfzeile verursacht keinen Fehler, aber diese Zellen zeigen nichts an.

Farbskalen: Heatmap ohne Diagramm

Eine Farbskala schattiert jede Zelle danach, wo ihr Wert zwischen Minimum und Maximum des Bereichs liegt. Es sind keine Farbargumente erforderlich – wenn keine angegeben werden, ist das Ergebnis eine zweifarbige Skala, die am Minimum Orange und am Maximum Blassgelb annimmt, wobei Zwischenwerte proportional schattiert werden. Der Effekt ist eine Heatmap, die in die Datentabelle eingebettet ist: heiße und kalte Stellen sind sichtbar, ohne zu sortieren oder ein Diagramm zu erstellen.

Die Schritte sind dieselben wie bei Datenbalken, mit FormatType auf ColorScale gesetzt und ohne zusätzliche Eigenschaften:

1. Laden Sie die Schriftart und die Testdatendatei in das VFS.
2. Laden Sie die Arbeitsmappe und rufen Sie das Arbeitsblatt ab.
3. Rufen Sie ConditionalFormats.Add auf, um ein bedingtes Format zu erstellen, und binden Sie den Datenbereich mit AddRange.
4. Rufen Sie AddCondition auf, um eine Bedingung hinzuzufügen, und setzen Sie FormatType auf ColorScale.
5. Speichern Sie die Arbeitsmappe.

function App() {
  const applyColorScales = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'SalesData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Select the data range that receives the color scales
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add a color scale condition; colors transition with the values
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.ColorScale;

    // Save the workbook
    const outputFileName = "ApplyColorScales.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Dispose of the workbook object to free resources
    workbook.Dispose();

    // Read the result file from the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Apply Color Scales</h1>
      <button onClick={applyColorScales}>Start</button>
    </div>
  );
}

export default App;

Farbskalen auf eine Verkaufszahlen-Tabelle angewendet, Schattierung von Orange bis Blassgelb

Wo Datenbalken die absolute Größenordnung durch die Balkenlänge zeigen, zeigen Farbskalen die relative Position durch den Farbton. Ein Wert in der Mitte des Bereichs erhält einen Mittelton, unabhängig davon, ob der Bereich von 1 bis 100 oder von 10.000 bis 50.000 reicht – die Schattierung ist positionell, nicht absolut.

Symbolsätze: Statusbänder

Ein Symbolsatz platziert ein unterschiedliches Symbol in jeder Zelle, je nachdem, in welches Band der Wert fällt. Das Beispiel verwendet drei Ampeln: Rot für das untere Drittel, Gelb für die Mitte, Grün für das oberste. Anders als Datenbalken und Farbskalen, die einen kontinuierlichen Verlauf vermitteln, vermitteln Symbolsätze eine diskrete Kategorie – „das ist niedrig“, „das ist mittel“, „das ist hoch“ –, was eher einer Statusanzeige als einer Messung entspricht.

Die Schritte unterscheiden sich nur in FormatType und der Auswahl des Symbolstils:

1. Laden Sie die Schriftart und die Testdatendatei in das VFS.
2. Laden Sie die Arbeitsmappe und rufen Sie das Arbeitsblatt ab.
3. Rufen Sie ConditionalFormats.Add auf, um ein bedingtes Format zu erstellen, und binden Sie den Datenbereich mit AddRange.
4. Rufen Sie AddCondition auf, um eine Bedingung hinzuzufügen, setzen Sie FormatType auf IconSet und geben Sie den Symbolsatztyp an.
5. Speichern Sie die Arbeitsmappe.

function App() {
  const applyIconSets = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and the test data file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'SalesData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Select the data range that receives the icon sets
    const dataRange = sheet.Range.get("B2:E9");

    // Create a conditional format and bind it to that range
    const xcfs = sheet.ConditionalFormats.Add();
    xcfs.AddRange(dataRange);

    // Add an icon set condition and set the icon style to three traffic lights
    const format = xcfs.AddCondition();
    format.FormatType = xlsModule.ConditionalFormatType.IconSet;
    format.IconSet.IconSetType = xlsModule.IconSetType.ThreeTrafficLights1;

    // Save the workbook
    const outputFileName = "ApplyIconSets.xlsx";
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Dispose of the workbook object to free resources
    workbook.Dispose();

    // Read the result file from the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Apply Icon Sets</h1>
      <button onClick={applyIconSets}>Start</button>
    </div>
  );
}

export default App;

Symbolsätze auf eine Verkaufszahlen-Tabelle angewendet, Ampel-Symbole basierend auf Wertbändern

Ein Symbolsatz unterteilt den Bereich in Bänder, sodass dasselbe Symbol in unterschiedlichen Bereichen eine unterschiedliche Wertespanne abdeckt. In einem Bereich von 10 bis 90 deckt das grüne Symbol etwa 60 bis 90 ab; in einem Bereich von 10 bis 900 deckt es etwa 600 bis 900 ab. Die Bänder sind relativ, nicht absolut – was die richtige Voreinstellung für eine Tabelle ist, in der jede Spalte ihre eigene Skala hat, aber gut zu wissen, wenn Sie eine feste Schwelle erwarten.

Die Wahl zwischen den dreien

Alle drei werden auf einen Bereich angewendet, alle drei leben innerhalb der Zellen, und alle drei sind bedingte Formatierung. Die Wahl hängt davon ab, was der Leser mit den Zahlen tun muss:

Die drei schließen sich nicht gegenseitig aus. Ein Arbeitsblatt kann im selben Speichervorgang Datenbalken auf den Umsatzspalten und Symbolsätze auf der Wachstumsratenspalte tragen, weil jeder Aufruf von ConditionalFormats.Add ein unabhängiges Format erstellt, das an seinen eigenen Bereich gebunden ist.

Anpassen des Erscheinungsbilds von Datenbalken

Die Füllfarbe eines Datenbalkens stammt aus DataBar.BarColor. Wenn nur FormatType ohne BarColor gesetzt wird, ergibt sich das Standardblau. Ein Rahmen ist ebenfalls verfügbar, hat aber eine Abhängigkeit: Der Rahmentyp muss festgelegt werden, bevor die Rahmenfarbe wirksam wird.

// Set the border type first so that the border color takes effect
format.DataBar.BarBorder.Type = xlsModule.DataBarBorderType.DataBarBorderSolid;
format.DataBar.BarBorder.Color = xlsModule.Color.get_Red();

// Fill color of the bar
format.DataBar.BarColor = xlsModule.Color.get_GreenYellow();

Das Setzen von BarBorder.Color allein, ohne vorher BarBorder.Type festzulegen, hat keine Wirkung – der Rahmen wird nicht gezeichnet, weil kein Rahmentyp deklariert wurde. Farbskalen und Symbolsätze haben keine entsprechenden Eigenschaften für das Erscheinungsbild; ihre Gestaltung wird durch den Formattyp und – bei Symbolsätzen – durch die Enumeration IconSetType bestimmt.

Häufige Probleme

Textzellen im Zielbereich zeigen keine Datenbalken. Das ist zu erwarten. Ein Datenbalken drückt eine relative Größenordnung aus, und nur numerische Zellen haben eine Größenordnung. Textzellen werden stillschweigend übersprungen – kein Fehler, kein Balken. Begrenzen Sie den Bereich auf die numerische Fläche.

Alle Datenbalken sind im Standardblau. DataBar.BarColor wurde nach der Zuweisung von FormatType nicht gesetzt. Setzen Sie es auf einen beliebigen xlsModule.Color-Wert, um die Füllung zu ändern.

Die Rahmenfarbe des Datenbalkens wird nicht angezeigt. Der Rahmentyp wurde nicht zuerst festgelegt. Weisen Sie DataBar.BarBorder.Type vor DataBar.BarBorder.Color zu – die Farbe wird erst wirksam, wenn ein durchgehender Rahmentyp deklariert wurde.

Keine sichtbare Änderung nach dem Anwenden einer bedingten Formatierung. Prüfen Sie, ob der an AddRange übergebene Bereich mit der Stelle übereinstimmt, an der sich die Daten tatsächlich befinden. Ein Bereich, der auf leere Zellen zeigt, erzeugt keinen Fehler und kein sichtbares Ergebnis.

FAQ

Kann ich mehr als eine bedingte Formatierung auf denselben Bereich anwenden?

Ja. Jeder Aufruf von ConditionalFormats.Add erstellt ein unabhängiges Format. Zwei Formate können auf denselben Bereich abzielen, obwohl das visuelle Ergebnis des Stapelns eines Datenbalkens und einer Farbskala auf denselben Zellen verwirrend sein kann – es ist üblicherweise klarer, verschiedene Typen auf verschiedene Bereiche anzuwenden.

Welche Excel-Versionen unterstützen diese Typen der bedingten Formatierung?

Datenbalken, Farbskalen und Symbolsätze wurden in Excel 2007 eingeführt. Das Beispiel speichert mit ExcelVersion.Version2010, um die Kompatibilität sowohl mit Excel 2010 als auch mit späteren Versionen sicherzustellen.

Muss Excel installiert sein, um bedingte Formatierung anzuwenden?

Nein. Die Tabellenkalkulations-Engine ist im Paket enthalten und läuft als WebAssembly im Browser. Die bedingte Formatierung wird als Standard-XML innerhalb der .xlsx-Datei geschrieben, und Excel rendert sie beim Öffnen der Datei.

Kann ich benutzerdefinierte Schwellenwerte für Symbolsätze festlegen?

Die Enumeration IconSetType wählt einen vordefinierten Symbolstil mit vordefinierten Bandgrenzen. Das Beispiel verwendet ThreeTrafficLights1, das den Bereich in drei gleiche Bänder unterteilt.

Bleibt die bedingte Formatierung erhalten, wenn die Datei in Excel geöffnet und erneut gespeichert wird?

Ja. Die bedingte Formatierung ist Teil der gespeicherten Formatregeln des Arbeitsblatts, kein Rendering-Artefakt. Excel liest, bewahrt und wendet dieselben Regeln bei der Neuberechnung erneut an.

Siehe auch

• Integrating Spire.XLS for JavaScript in a React Project
• Spire.XLS for JavaScript Product Page