Lesson preparation usually means doing the same work twice. A textbook lays out concepts, worked examples and exercises chapter by chapter, complete and detailed; a deck has to be reorganised into pages fit for the classroom. Teachers condense the material into a handful of points per slide, add the examples and diagrams, split the exercises into one question per page, and prepare answers for review. None of this is technically hard, but it is time-consuming, and every textbook revision means doing it all again.

The Presentation AI in Spire.Agent.Office hands this step to an AI agent: pass the PDF textbook in as an attachment, describe in natural language how the deck should be organised and presented, and the agent reads the material and generates the slides. It produces either a lecture deck (one page per section, with concepts, examples and speaker notes) or an exercise deck (one question per page, with answers revealed as animations on the page).

Comparison with Traditional SDK API

This article shows how to use Spire.Agent.Office to generate PowerPoint decks from a PDF textbook. Two cases demonstrate the two typical uses — "generating a lecture deck from a textbook" and "generating an exercise deck from a textbook":

• Case 1: Generate a lecture deck from a textbook
• Case 2: Generate an exercise deck from a textbook

For product installation and SpireToken configuration, please refer to Integrating Spire.Agent.Office in a .NET Project. The examples below assume that Spire.Agent.Office has been installed and SpireToken has been configured.

Case 1: Generate a lecture deck from a textbook

A textbook is complete and logically coherent, but it cannot be projected as it is — a whole paragraph becomes a wall of small text on screen, and the hierarchy that the textbook conveys through layout has to be rebuilt with pages. What a teacher needs to do is reorganise it section by section: keep that section's core concepts, formulas and typical worked examples, rewrite long passages into points that read well in class, and carry over the textbook's diagrams. Each page must hold enough to support a full lesson without copying whole paragraphs, or it becomes too dense to read. The deck as a whole then uses one consistent layout: a light colour theme, page-by-page animations and a few supporting images, with body text no smaller than 24 pt.

How the examples are presented matters most. A textbook usually derives a formula or theorem before stating the conclusion; if the deck only carries the conclusion across, students cannot see where it comes from, so the instruction requires examples to keep their full derivation. What the teacher needs to be reminded of while teaching — the emphasis on that page, the common mistakes and the pacing — goes into the speaker notes, invisible when projected and shown only in Presenter View. The example below passes the PDF textbook in as an attachment:

using Spire.Agent.Office.AI;
using Spire.Agent.Office.Extensions;
using Spire.Presentation;

// the textbook file passed in as an attachment
string[] attachmentPaths = new string[]
{
    @"E:\textbook\Proportion.pdf"
};

// PPT-related configuration
string inputPath = null;  // the deck is generated from scratch, so no input PPT is needed
string savePath = @"E:\textbook\Proportion.ppt";  // result path
string key = "**************************";  // SpireToken key
string instruction =
    "Read the attached PDF textbook and generate a lecture deck as follows:" +
    "1. Follow the textbook's original chapter order and omit nothing;" +
    "2. Make each page substantial: list the chapter's core concepts and formulas, together with the textbook's typical worked examples;" +
    "3. Worked examples must show the full derivation steps, not just the conclusion;" +
    "4. Keep the diagrams, tables and data from the textbook and use them to support the explanation so that students can follow;" +
    "5. Write speaker notes for each page covering the key points, common mistakes and pacing;" +
    "6. Apply one consistent layout to every page, use a light colour theme, add appropriate animations and images, and keep body text at 24 pt or larger;" +
    "Save the final result as a PPT file";

// Call the PPT document processing function
AIResult result = ExecuteDemoPpt(instruction, inputPath, savePath, key, attachmentPaths);

// Execute the PPT AI processing
static AIResult ExecuteDemoPpt(string instruction, string inputPath, string savePath, string key, string[] attachmentPaths)
{
    // Create the AIOptions configuration object
    AIOptions options = new AIOptions();
    options.SpireToken = key;  // set the SpireToken key

    // Process the PPT document with a Presentation object
    using (Presentation ppt = new Presentation())
    {
        // Load the PPT document from file (not needed when generating a deck from scratch)
        if (!string.IsNullOrEmpty(inputPath) && File.Exists(inputPath))
        {
            ppt.LoadFromFile(inputPath);
        }
        // Create the AI document processor
        AIDocumentProcessor processor = ppt.AI(options);

        // Execute the AI instruction
        return processor.ExecuteInstruction(ppt, instruction, savePath, attachmentPaths);
    }
}

A lecture deck generated from the textbook

Case 2: Generate an exercise deck from a textbook

The exercise set is after-class practice: students work through it on their own, and the teacher walks through it question by question in the next lesson. On slides the exercises need one question per page, with the answer built as an animation that only appears when the teacher reaches that question, so students judge for themselves first and then check their reasoning against the walkthrough.

Moving a set of exercises onto slides, however, is a fair amount of work: the exercises run as a continuous sequence of numbered items packed several to a page, so each one has to be picked out and re-laid-out one question per page; the tables and diagrams inside the questions must be carried over too, or students cannot read the question; and the answers needed for the walkthrough have to be worked out one by one and built as animations. This case treats the exercise deck as a standalone deliverable: pass the PDF of the after-class exercises in as an attachment, and the AI extracts and lays out each question, with the question always on the page and the answer revealed as an animation when the teacher triggers it:

using Spire.Agent.Office.AI;
using Spire.Agent.Office.Extensions;
using Spire.Presentation;

// the textbook's exercise section passed in as an attachment
string[] attachmentPaths = new string[]
{
    @"E:\textbook\ProportionExercises.pdf"
};

// PPT-related configuration
string inputPath = null;  // the deck is generated from scratch, so no input PPT is needed
string savePath = @"E:\textbook\ProportionExercises.ppt";  // result path
string key = "**************************";  // SpireToken key
string instruction =
    "Read the attached PDF textbook exercises and generate an exercise deck as follows:" +
    "1. Follow the textbook's original question order, one question per page, omitting and merging nothing;" +
    "2. Keep the full question text, options and diagrams so that students can answer directly;" +
    "3. Show the answers as animations;" +
    "4. Apply one consistent layout to every page, use a light colour theme, add appropriate animations and images, and keep body text at 24 pt or larger;" +
    "Save the final result as a PPT file";

// Call the PPT document processing function
AIResult result = ExecuteDemoPpt(instruction, inputPath, savePath, key, attachmentPaths);

// Execute the PPT AI processing
static AIResult ExecuteDemoPpt(string instruction, string inputPath, string savePath, string key, string[] attachmentPaths)
{
    // Create the AIOptions configuration object
    AIOptions options = new AIOptions();
    options.SpireToken = key;  // set the SpireToken key

    // Process the PPT document with a Presentation object
    using (Presentation ppt = new Presentation())
    {
        // Load the PPT document from file (not needed when generating a deck from scratch)
        if (!string.IsNullOrEmpty(inputPath) && File.Exists(inputPath))
        {
            ppt.LoadFromFile(inputPath);
        }
        // Create the AI document processor
        AIDocumentProcessor processor = ppt.AI(options);

        // Execute the AI instruction
        return processor.ExecuteInstruction(ppt, instruction, savePath, attachmentPaths);
    }
}

An exercise deck generated from the textbook

FAQ

The body text is copied straight from the textbook and is too small to project

Cause: The instruction does not say what each page should hold, so the AI carries whole paragraphs onto the page and shrinks the font to fit them in.

Solution: Ask explicitly for each page to list the core concepts and formulas with the typical worked examples, and to keep the textbook's diagrams and tables, rather than copying whole paragraphs across; also set a minimum font size (for example, "keep body text at 24 pt or larger"). Constrain only the font size and the enlarged text overflows the page; constrain only the form of the content with no lower bound and the layout may still be squeezed.

A long textbook takes a very long time, or times out

Cause: Deck generation runs page by page, and the AI repeatedly reads and writes the document, sending the existing content back to the model each time. The more pages there are and the denser the text on each, the faster token use and elapsed time grow.

Solution: Set TimeoutMs (in milliseconds) when creating AIOptions. A full textbook of several dozen pages can be given 10 minutes (600000).

AIOptions options = new AIOptions();
options.TimeoutMs = 600000;  // allow 10 minutes for a long textbook
options.SpireToken = key;

Getting a SpireToken Key

• Contact sales@e-iceblue.com or visit https://www.e-iceblue.com/TemLicense.html to obtain a trial/commercial API key

Configure it in your code:

AIOptions options = new AIOptions();
options.SpireToken = key;

The number of atoms in a chemical formula, the exponent in a mathematical expression, the square or cube on a unit symbol, the charge at the end of an ion — these characters are set one size smaller and shifted off the baseline, and that is how readers expect to find them. Typed at the same size as the surrounding text, they have to be inferred from context. Excel rich text lets individual characters inside a single cell carry their own formatting, and superscript and subscript are the two forms used most often. Spire.XLS for JavaScript performs this directly in the browser through WebAssembly, managing input and output files with a virtual file system (VFS) and requiring no backend service.

This article covers two key features:

• Set the Subscript of Text
• Set the Superscript of Text

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is already installed and the WebAssembly module has been initialized.

Set the Subscript of Text

Superscript and subscript are not whole-cell font properties: RichText.SetFont applies a font to a slice of the text inside a cell rather than to the cell as a whole. Three things have to line up — write the text as a rich-text run, prepare a font that carries the shift, then apply that font to the character range that should move. The steps are as follows:

1. Load the font into the VFS.
2. Create a workbook and get the first worksheet.
3. Write the sample labels, then the chemical formulas with RichText.Text.
4. Create a font object with workbook.CreateFont() and set IsSubscript to true.
5. Apply the font to the digits with RichText.SetFont(startIndex, endIndex, font), then save the workbook.

Here is a complete code example that sets the subscript of cell text in React:

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

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

    // Load the font into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Create a new workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    const sheet = workbook.Worksheets.get(0);

    // Write the header row and the sample labels
    sheet.Range.get("A1").Text = "Item";
    sheet.Range.get("B1").Text = "Result";
    sheet.Range.get("A2").Text = "Water";
    sheet.Range.get("A3").Text = "Sulfuric acid";

    // Write the chemical formulas
    const waterRange = sheet.Range.get("B2");
    waterRange.RichText.Text = "H2O";
    const acidRange = sheet.Range.get("B3");
    acidRange.RichText.Text = "H2SO4";

    // Create a font and set IsSubscript to true
    const font = workbook.CreateFont();
    font.IsSubscript = true;

    // Apply the subscript font to the digits only: indices start at 0 and the range is inclusive
    waterRange.RichText.SetFont(1, 1, font);
    acidRange.RichText.SetFont(1, 1, font);
    acidRange.RichText.SetFont(4, 4, font);

    // Widen columns A and B so the rich text is fully visible
    sheet.Range.get("A1:A3").ColumnWidth = 18;
    sheet.Range.get("B1:B3").ColumnWidth = 18;

    // Save the workbook
    const outputFileName = "SetSubscript.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // 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>Set Subscript</h1>
      <button onClick={setSubscript}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of setting the subscript of text:

Set the Superscript of Text

Superscript is the mirror image of subscript; the only difference is the direction the font shifts — up rather than down. Superscript suits powers and exponents — the squares in the Pythagorean theorem, the squared cm2 of an area unit, the cubed m3 of a volume unit. A single RichText.SetFont call covers one contiguous range, so text such as a2 + b2 = c2, with three characters to raise, takes three calls. The steps are as follows:

1. Load the font into the VFS.
2. Create a workbook and get the first worksheet.
3. Write the sample labels, then the text that receives the formatting with RichText.Text.
4. Create a font object with workbook.CreateFont() and set IsSuperscript to true.
5. Apply the font to the characters that should be raised with RichText.SetFont(startIndex, endIndex, font), then save the workbook.

Here is a complete code example that sets the superscript of cell text in React:

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

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

    // Load the font into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Create a new workbook and get the first worksheet
    const workbook = new xlsModule.Workbook();
    const sheet = workbook.Worksheets.get(0);

    // Write the header row and the sample labels
    sheet.Range.get("A1").Text = "Item";
    sheet.Range.get("B1").Text = "Result";
    sheet.Range.get("A2").Text = "Pythagorean theorem";
    sheet.Range.get("A3").Text = "Area unit";
    sheet.Range.get("A4").Text = "Volume unit";

    // Write the text that receives the formatting
    const theoremRange = sheet.Range.get("B2");
    theoremRange.RichText.Text = "a2 + b2 = c2";
    const areaRange = sheet.Range.get("B3");
    areaRange.RichText.Text = "cm2";
    const volumeRange = sheet.Range.get("B4");
    volumeRange.RichText.Text = "m3";

    // Create a font and set IsSuperscript to true
    const font = workbook.CreateFont();
    font.IsSuperscript = true;

    // Apply the superscript font to the characters that should be raised
    theoremRange.RichText.SetFont(1, 1, font);
    theoremRange.RichText.SetFont(6, 6, font);
    theoremRange.RichText.SetFont(11, 11, font);
    areaRange.RichText.SetFont(2, 2, font);
    volumeRange.RichText.SetFont(1, 1, font);

    // Widen columns A and B so the rich text is fully visible
    sheet.Range.get("A1:A4").ColumnWidth = 18;
    sheet.Range.get("B1:B4").ColumnWidth = 18;

    // Save the workbook
    const outputFileName = "SetSuperscript.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // 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>Set Superscript</h1>
      <button onClick={setSuperscript}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of setting the superscript of text:

FAQ

How do I remove a superscript or subscript that has already been set?

Cause: Superscript and subscript are formats on individual characters. Calling SetFont again over the same range overwrites those characters with the new font's properties, so applying a font without the shift clears the effect.

Solution: Create a font with IsSubscript or IsSuperscript set to false, and call SetFont over the original range:

// Overwrite the subscript with a plain font
const normal = workbook.CreateFont();
normal.IsSubscript = false;
range.RichText.SetFont(1, 1, normal);

How can I tell whether a character is superscript when reading the file back?

Cause: The shift is a format on the character, not part of the cell's text. Range.Text, Range.Value and RichText.Text all return plain text, so they cannot show which characters carry it.

Solution: Read the font at the character's position and check its superscript and subscript properties. Positions count from 0, the same as with SetFont:

// Read the font at index 1 — the middle "2" in H2O
const font = range.RichText.GetFont(1);
console.log(font.IsSubscript);   // true
console.log(font.IsSuperscript); // false

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.

Spotting the order numbers that were entered twice, the ratings that sit below the average, or the handful of largest and smallest orders in a table of several hundred rows is slow work by eye, and easy to get wrong. Conditional formatting hands that judgement to Excel: once a rule is written, the matching cells carry the color themselves, and the marks follow the data when it changes. This article covers the three most common kinds of rule — whether a value repeats, how it compares with the average, and where it ranks. Spire.XLS for JavaScript runs all of this in the browser on WebAssembly, managing input and output files through a virtual file system (VFS) with no backend service required.

This article covers three features:

• Highlight duplicate and unique values
• Highlight values above or below average
• Highlight top or bottom ranked values

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialized.

Highlight duplicate and unique values

A conditional formatting rule hangs off a conditional format set: sheet.ConditionalFormats.Add() returns one set, you point it at the cells it applies to, and then add a single condition whose color you set. Duplicate and unique values are two sides of the same comparison — the first matches cells whose value appears more than once in the range, the second matches cells that appear exactly once — so they are usually written as a pair that brings both the repeated order numbers and the one-off ones into view. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook and get the worksheet.
3. Add a conditional format set with sheet.ConditionalFormats.Add() and point it at the order number column with AddRange.
4. Add a condition with AddCondition, set its FormatType to DuplicateValues, and set the fill color.
5. Add a second set on the same range in the same way, switch the type to UniqueValues, set another fill color, and save the workbook.

The full code sample below shows how to highlight duplicate and unique values in React:

function App() {
  const highlightDuplicateValues = 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 = 'ConditionalFormatData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

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

    // Add one conditional format set, applied to the order number column
    const duplicateFormats = sheet.ConditionalFormats.Add();
    duplicateFormats.AddRange(sheet.Range.get("C2:C10"));

    // Add a duplicate-value condition, filling order numbers that appear more than once in IndianRed
    const duplicateCondition = duplicateFormats.AddCondition();
    duplicateCondition.FormatType = xlsModule.ConditionalFormatType.DuplicateValues;
    duplicateCondition.BackColor = xlsModule.Color.get_IndianRed();

    // Add another conditional format set, applied to the same range
    const uniqueFormats = sheet.ConditionalFormats.Add();
    uniqueFormats.AddRange(sheet.Range.get("C2:C10"));

    // Add a unique-value condition, filling order numbers that appear once in Yellow
    const uniqueCondition = uniqueFormats.AddCondition();
    uniqueCondition.FormatType = xlsModule.ConditionalFormatType.UniqueValues;
    uniqueCondition.BackColor = xlsModule.Color.get_Yellow();

    // Save the workbook
    const outputFileName = "HighlightDuplicateValues.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // 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>Highlight Duplicate and Unique Values</h1>
      <button onClick={highlightDuplicateValues}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of highlighting duplicate and unique values:

Highlight values above or below average

The average is the yardstick most people reach for when judging a set of numbers. Conditional formatting has a condition built for exactly that comparison: call AddAverageCondition for the above-average or the below-average case, and Excel works out the average of the range itself, then compares each cell against it — one color above the line, another below. Excel computes the average when the file is opened, so editing the data reshuffles the marks along with it. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook and get the worksheet.
3. Add a conditional format set with sheet.ConditionalFormats.Add() and point it at the customer rating column with AddRange.
4. Call AddAverageCondition with AverageType.Below and set the fill color.
5. Add a second set on the same range in the same way, pass AverageType.Above instead, set another fill color, and save the workbook.

The full code sample below shows how to highlight values above or below average in React:

function App() {
  const highlightAverageValues = 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 = 'ConditionalFormatData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

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

    // Add one conditional format set, applied to the customer rating column
    const belowFormats = sheet.ConditionalFormats.Add();
    belowFormats.AddRange(sheet.Range.get("E2:E10"));

    // Add a below-average condition, filling with SkyBlue
    const belowCondition = belowFormats.AddAverageCondition(xlsModule.AverageType.Below);
    belowCondition.BackColor = xlsModule.Color.get_SkyBlue();

    // Add another conditional format set, applied to the same range
    const aboveFormats = sheet.ConditionalFormats.Add();
    aboveFormats.AddRange(sheet.Range.get("E2:E10"));

    // Add an above-average condition, filling with Orange
    const aboveCondition = aboveFormats.AddAverageCondition(xlsModule.AverageType.Above);
    aboveCondition.BackColor = xlsModule.Color.get_Orange();

    // Save the workbook
    const outputFileName = "HighlightAverageValues.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // 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>Highlight Above and Below Average Values</h1>
      <button onClick={highlightAverageValues}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of highlighting values above or below average:

Highlight top or bottom ranked values

Leaderboard questions — which two orders are the largest, which three teams have the lowest completion rate — are hard to answer row by row, but a rank condition only needs the number of places. AddTopBottomCondition ranks the values inside the range you set and colors the cells that come out highest or lowest; it takes two arguments: one decides whether to take the highest or the lowest, the other is how many. The ranking stays inside that range and ignores everything else on the sheet. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook and get the worksheet.
3. Add a conditional format set with sheet.ConditionalFormats.Add() and point it at the order amount column with AddRange.
4. Call AddTopBottomCondition with TopBottomType.Top and a count of 2, then set the fill color.
5. Add a second set on the same range in the same way, pass TopBottomType.Bottom instead, set another fill color, and save the workbook.

The full code sample below shows how to highlight top or bottom ranked values in React:

function App() {
  const highlightRankedValues = 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 = 'ConditionalFormatData.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

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

    // Add one conditional format set, applied to the order amount column
    const topFormats = sheet.ConditionalFormats.Add();
    topFormats.AddRange(sheet.Range.get("D2:D10"));

    // Add a top-2 condition, filling the two largest orders with Red
    const topCondition = topFormats.AddTopBottomCondition(xlsModule.TopBottomType.Top, 2);
    topCondition.FormatType = xlsModule.ConditionalFormatType.TopBottom;
    topCondition.BackColor = xlsModule.Color.get_Red();

    // Add another conditional format set, applied to the same range
    const bottomFormats = sheet.ConditionalFormats.Add();
    bottomFormats.AddRange(sheet.Range.get("D2:D10"));

    // Add a bottom-2 condition, filling the two smallest orders with ForestGreen
    const bottomCondition = bottomFormats.AddTopBottomCondition(xlsModule.TopBottomType.Bottom, 2);
    bottomCondition.FormatType = xlsModule.ConditionalFormatType.TopBottom;
    bottomCondition.BackColor = xlsModule.Color.get_ForestGreen();

    // Save the workbook
    const outputFileName = "HighlightRankedValues.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // 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>Highlight Top and Bottom Ranked Values</h1>
      <button onClick={highlightRankedValues}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of highlighting top or bottom ranked values:

FAQ

Why is the color I set not showing up?

Cause: The color belongs on the condition object, which is what AddCondition, AddAverageCondition or AddTopBottomCondition returns. Assigning BackColor to the outer conditional format set instead is accepted and raises no error, but the rule that reaches the file carries no fill and the cells stay as they were.

Solution: Assign the color to the condition object — the return value of those methods:

// The condition object is what carries the color
const formats = sheet.ConditionalFormats.Add();
formats.AddRange(sheet.Range.get("C2:C10"));
const condition = formats.AddCondition();
condition.FormatType = xlsModule.ConditionalFormatType.DuplicateValues;
condition.BackColor = xlsModule.Color.get_IndianRed();

Why can't I read a conditional format's fill color back from the cell?

Cause: The color of a conditional format does not belong to the cell's own formatting. It is stored with the rule in the file's conditional formatting settings, and the cell keeps the style it already had, so the color read from the cell is the same before and after the rule is added.

Solution: Read the color from the condition object — the return value of AddCondition, AddAverageCondition or AddTopBottomCondition:

// The conditional format color lives on the condition object, not in the cell style
const formats = sheet.ConditionalFormats.Add();
formats.AddRange(sheet.Range.get("C2:C10"));
const condition = formats.AddCondition();
condition.FormatType = xlsModule.ConditionalFormatType.DuplicateValues;
condition.BackColor = xlsModule.Color.get_IndianRed();

// This reads back the color that was just set
console.log(condition.BackColor);

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.

A chart makes the data clear; it says nothing about whose brand it belongs to, or what the one-line conclusion is. The usual fix in a report is a logo in one corner of the chart plus a note such as "Online: 1,208K USD in total" in the empty space — putting the conclusion where the reader's eye already is instead of starting another paragraph of prose. In Excel these elements belong to the chart's own shape layer, positioned against the chart rather than against the cells, and that is where code that adds them most often goes wrong. The plot area's default white background is a separate matter again: it can be swapped for a light texture so that the chart and the rest of the report look like one piece. Spire.XLS for JavaScript does all of this in the browser on top of WebAssembly, managing input and output files through a virtual file system (VFS), with no backend service required.

This article covers three key features:

• Insert a picture into a chart
• Insert a text box into a chart
• Fill the plot area with a picture

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialised.

Insert a picture into a chart

Charts in a report often need to carry a brand mark or a product shot. Once a picture is inside the chart it becomes part of it: move or resize the chart and the picture comes along, and copying the chart into another document or exporting it as an image keeps the picture too. A picture floating above the cells, by contrast, is out of alignment the moment the chart moves. The steps are:

1. Load the font, the test data file and the picture into the VFS.
2. Load the workbook with workbook.LoadFromFile and take the first chart on the first worksheet.
3. Add the picture to the chart with chart.Shapes.AddPicture; the value it returns is that shape.
4. Set the shape's Left, Top, Width and Height. A shape inside a chart is measured against the chart itself: each of the four is in units of 1/4000 of the chart's width (Left, Width) or height (Top, Height).
5. Save the workbook with workbook.SaveToFile.

The complete code example below shows how to insert a picture into a chart in React:

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

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

    // load the font, the test data file and the picture into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'ChartReport.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);
    await window.spire.FetchFileToVFS('logo.png', '', `${process.env.PUBLIC_URL}static/image/`);

    // load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // take the first worksheet and the chart on it
    const sheet = workbook.Worksheets.get(0);
    const chart = sheet.Charts.get(0);

    // insert the picture into the chart; the object returned is that picture
    const picture = chart.Shapes.AddPicture('logo.png');

    // place it in the top-right corner and scale it down. Left unset, the picture is laid out at
    // its natural pixel size and usually covers most of the chart
    picture.Left = 2850;   // 2850/4000 from the left edge of the chart
    picture.Top = 110;     // 110/4000 from the top edge of the chart
    picture.Width = 900;   // width 900/4000
    picture.Height = 532;  // height 532/4000, matching the 320x120 of the source picture

    // save the workbook
    const outputFileName = 'AddPictureInChart.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

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

    // read the result file from the VFS and start 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>Insert a Picture and a Text Box in a Chart</h1>
      <button id="add-picture-in-chart" onClick={addPictureInChart}>Add a picture to the chart</button>
    </div>
  );
}

export default App;

After running, inserting a picture into a chart:

Insert a text box into a chart

A chart shows a trend but cannot state a conclusion. A total for one series, a year-on-year remark or a callout on an outlier can all be written straight onto the chart with a text box, sparing the reader a second trip to the body text for the number. A text box is a shape inside the chart like the picture, measured on the same scale; the difference is that its size has to be sized to the length of the text. Leave it too narrow and the text wraps, and the wrapped line is clipped by the box height — it looks as though half the words went missing. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook with workbook.LoadFromFile and take the first chart on the first worksheet.
3. Create the text box inside the chart with chart.Shapes.AddTextBox.
4. Set Left, Top, Width and Height according to the length of the text so that the content fits on one line.
5. Write the text into the Text property.
6. Centre the text with HAlignment and VAlignment, taking the values from xlsModule.CommentHAlignType and xlsModule.CommentVAlignType.
7. Set the fill, the border colour and the border width with Fill.ForeColor, Line.ForeColor and Line.Weight, so that the box stands out on the chart.
8. Save the workbook with workbook.SaveToFile.

The complete code example below shows how to insert a text box into a chart in React:

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

    // check that 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 = 'ChartReport.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // take the first worksheet and the chart on it
    const sheet = workbook.Worksheets.get(0);
    const chart = sheet.Charts.get(0);

    // Add a text box to the chart
    const textBox = chart.Shapes.AddTextBox();

    // set the position and size of the text box, again in 1/4000 of the chart
    textBox.Left = 450;
    textBox.Top = 530;
    textBox.Width = 2100;
    textBox.Height = 340;

    // write the text
    textBox.Text = 'Online: 1,208K USD in total';

    // centre the text and give the box a pale yellow fill and a blue border so that it stands
    // out on the chart
    textBox.HAlignment = xlsModule.CommentHAlignType.Center;
    textBox.VAlignment = xlsModule.CommentVAlignType.Center;
    textBox.Fill.ForeColor = xlsModule.Color.FromArgb(255, 255, 245, 214);
    textBox.Line.ForeColor = xlsModule.Color.FromArgb(255, 46, 106, 176);
    textBox.Line.Weight = 1;

    // save the workbook
    const outputFileName = 'AddTextBoxInChart.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

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

    // read the result file from the VFS and start 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>Insert a Picture and a Text Box in a Chart</h1>
      <button id="add-textbox-in-chart" onClick={addTextBoxInChart}>Add a text box to the chart</button>
    </div>
  );
}

export default App;

After running, inserting a text box into a chart:

Fill the plot area with a picture

The white background of the plot area is the chart's default, and a report that has been around for a while starts to look the same everywhere. Replacing it with a light texture keeps the columns, the gridlines and the axis labels perfectly legible while giving the background some depth, and pulls the chart into the same visual language as the rest of the report. The fill and the picture shapes placed on the chart do not interfere with each other; the two can be used together. The steps are:

1. Load the font, the test data file and the background picture into the VFS.
2. Load the workbook with workbook.LoadFromFile and take the first chart on the first worksheet.
3. Build an xlsModule.Stream in memory from the background picture.
4. Hand it to chart.PlotArea.Fill.CustomPicture; the second parameter, name, names an existing texture in the workbook, and 'None' is passed when there is none.
5. Save the workbook with workbook.SaveToFile.

The complete code example below shows how to fill the plot area with a picture in React:

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

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

    // load the font, the test data file and the background picture into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'ChartReport.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);
    await window.spire.FetchFileToVFS('background.png', '', `${process.env.PUBLIC_URL}static/image/`);

    // load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // take the first worksheet and the chart on it
    const sheet = workbook.Worksheets.get(0);
    const chart = sheet.Charts.get(0);

    // read the background picture into a memory stream and use it to fill the plot area
    const background = new xlsModule.Stream('background.png');
    chart.PlotArea.Fill.CustomPicture({ im: background, name: 'None' });

    // save the workbook
    const outputFileName = 'FillPlotAreaWithPicture.xlsx';
    workbook.SaveToFile({ fileName: outputFileName });

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

    // read the result file from the VFS and start 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>Insert a Picture and a Text Box in a Chart</h1>
      <button id="fill-plot-area" onClick={fillPlotAreaWithPicture}>Fill the plot area with a picture</button>
    </div>
  );
}

export default App;

After running, filling the plot area with a picture:

FAQ

Can I add an arrow or a callout line to a chart?

Solution: An arrow or a callout line that points at something has no API of its own, but a text box can stand in for one: stretch it into a thin strip, drop the fill and keep only the border, and place it where the callout should point.

When filling with a picture, is it the plot area or the whole chart that gets filled?

Cause: PlotArea.Fill and ChartArea.Fill are two different objects. The first covers only the region enclosed by the axes, leaving the chart title, the legend and the axis labels outside the picture. The second covers the entire chart, so the title and the legend end up on top of the picture as well.

Solution: Pick whichever one you need. After the fill, read Fill.FillType; it returns ShapeFillType.Picture on success:

// fill only the plot area: the title, the legend and the axis labels stay as they were
const background = new xlsModule.Stream('background.png');
chart.PlotArea.Fill.CustomPicture({ im: background, name: 'None' });

// fill the whole chart: the picture runs under the title and the legend
chart.ChartArea.Fill.CustomPicture({ im: new xlsModule.Stream('background.png'), name: 'None' });

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.

A finished report is often reused for a different brand or a different department, and the colours have to follow. The awkward part is that a workbook holds two kinds of colour. One is a hard-coded RGB value that belongs to the single cell it sits in. The other points at a theme slot: the title bar, the header row, the banded rows and the borders all look different, yet all of them read from the same set of slots. The first kind has to be changed cell by cell, and one missed cell gives the old palette away. The second kind repaints the entire sheet from a single slot. Spire.XLS for JavaScript does this in the browser on top of WebAssembly, managing input and output files through a virtual file system (VFS), with no backend service required.

This article covers three key features:

• Replace an accent colour in the theme
• Apply a custom colour scheme
• Reuse another workbook's theme

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialised.

Replace an accent colour in the theme

Most reports lean on a single colour: the header row is filled with it, the title bar takes a darker shade, the banded rows a lighter one, and the borders a paler one still. Those shades were not mixed by hand one at a time — they are the same slot read at different tint levels. Reskinning therefore needs no colour picking at all: replace that one slot with the new brand colour and every shade is recomputed, so the whole sheet, chart included, lands on the new palette. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook with workbook.LoadFromFile.
3. Replace the xlsModule.ThemeColorType.Accent1 slot with workbook.SetThemeColor, giving the new colour through xlsModule.Color.FromArgb.
4. Save the workbook with workbook.SaveToFile; the title bar, header row, banded rows and borders change together.

The complete code example below shows how to replace an accent colour in the theme in React:

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

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

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

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // Replace accent 1 of the theme: the title bar, the header row, the banded rows and the
    // borders all follow it
    workbook.SetThemeColor(
      xlsModule.ThemeColorType.Accent1,
      xlsModule.Color.FromArgb(255, 46, 125, 91),
    );

    // Save the workbook
    const outputFileName = "SetThemeColor.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // Dispose of the workbook object to release 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>Set Workbook Theme</h1>
      <button id="set-theme-color" onClick={setThemeColor}>Replace a Theme Colour</button>
    </div>
  );
}

export default App;

After running, replacing an accent colour in the theme:

Apply a custom colour scheme

Replacing one accent colour unifies the body of the table, but the other series in the chart, the total row and any warning colour stay on the old palette — they read from other slots in the theme. To move a document onto a different scheme outright, change all six accent slots together, so every element that references the theme lands on the new colours at once instead of one changing and a string of others lagging behind. Reading the current values first leaves a baseline to check the result against. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook with workbook.LoadFromFile, then read the R, G and B of the current accent 1 with workbook.GetThemeColor to keep as a baseline.
3. Put the six slot-and-colour pairs into an array, the colours again built with xlsModule.Color.FromArgb.
4. Walk the array and call workbook.SetThemeColor on each entry, so all six accent colours change in one pass.
5. Save the workbook with workbook.SaveToFile.

The complete code example below shows how to apply a custom colour scheme in React:

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

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

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

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // Read the current accent colour back first, so it can be compared with the new one
    const before = workbook.GetThemeColor(xlsModule.ThemeColorType.Accent1);
    console.log(`Accent 1 before the change: R=${before.R} G=${before.G} B=${before.B}`);

    // All six accent colours change in one go, so the whole scheme moves together
    const scheme = [
      [xlsModule.ThemeColorType.Accent1, xlsModule.Color.FromArgb(255, 109, 46, 95)],
      [xlsModule.ThemeColorType.Accent2, xlsModule.Color.FromArgb(255, 18, 89, 94)],
      [xlsModule.ThemeColorType.Accent3, xlsModule.Color.FromArgb(255, 138, 106, 22)],
      [xlsModule.ThemeColorType.Accent4, xlsModule.Color.FromArgb(255, 47, 93, 58)],
      [xlsModule.ThemeColorType.Accent5, xlsModule.Color.FromArgb(255, 67, 48, 122)],
      [xlsModule.ThemeColorType.Accent6, xlsModule.Color.FromArgb(255, 138, 59, 46)],
    ];
    for (const [themeColorType, color] of scheme) {
      workbook.SetThemeColor(themeColorType, color);
    }

    // Save the workbook
    const outputFileName = "ApplyThemeScheme.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // Dispose of the workbook object to release 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>Set Workbook Theme</h1>
      <button id="apply-theme-scheme" onClick={applyThemeScheme}>Apply a Custom Colour Scheme</button>
    </div>
  );
}

export default App;

After running, applying a custom colour scheme:

Reuse another workbook's theme

Once a colour scheme is signed off it usually already lives in a workbook — the designer's sample, last quarter's report, or the company template. Copying the hex values slot by slot is tedious and easy to get a digit wrong. The theme is itself part of the workbook, so the whole theme can be taken across, carrying both dark and light background pairs and the hyperlink colours with it, and every theme-referenced colour in the target workbook is repainted. The workbook the theme comes from need not match the target's layout at all — how many rows and columns it holds, which data sits in them and which kind of chart it draws make no difference. What crosses over is the theme; the target's own data and chart are left untouched. The steps are:

1. Load the font and both workbook files into the VFS.
2. Load the target workbook with workbook.LoadFromFile, then the theme provider with themeWorkbook.LoadFromFile.
3. Copy the provider's theme across in one piece with workbook.CopyTheme(themeWorkbook).
4. Save the target workbook with workbook.SaveToFile, then call Dispose on both workbook objects.

The complete code example below shows how to reuse another workbook's theme in React:

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

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

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

    // Load the workbook that is to be reskinned
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // Load the workbook the theme comes from
    const themeWorkbook = new xlsModule.Workbook();
    themeWorkbook.LoadFromFile({ fileName: themeFileName });

    // Copy the theme of the source workbook over in one piece
    workbook.CopyTheme(themeWorkbook);

    // Save the workbook
    const outputFileName = "CopyWorkbookTheme.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // Dispose of both workbook objects to release resources
    workbook.Dispose();
    themeWorkbook.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>Set Workbook Theme</h1>
      <button id="copy-workbook-theme" onClick={copyWorkbookTheme}>Reuse Another Workbook&apos;s Theme</button>
    </div>
  );
}

export default App;

After running, reusing another workbook's theme:

FAQ

Which colours change with the theme, and which do not

Cause: Colours fall into two kinds. A colour taken from Theme Colors in Excel changes together with the theme; a colour taken from Standard Colors is a fixed value and stays as it is.

Solution: To have a colour change with the theme, select the cell in Excel and pick it from Theme Colors. Colours written in code are all fixed values and do not change with the theme:

const cell = sheet.Range.get('A1');

// A fixed colour: it does not change with the theme
cell.Style.Interior.Color = xlsModule.Color.FromArgb(255, 192, 0, 0);

Can the chart alone be restyled, leaving the table untouched?

Cause: The theme belongs to the whole workbook and makes no distinction between the table and the chart. When the theme changes, every object that takes its colour from the theme changes with it, and the chart is one of them. A chart series holds no colour of its own — it takes the colour from the theme at draw time — so the chart always follows, and one side cannot change on its own.

Solution: To restyle only the chart, leave the theme alone and set the colour on the series directly. That writes a fixed colour into the chart, which the theme does not affect:

const chart = sheet.Charts.get(0);
const serie = chart.Series.get(0);

serie.Format.Fill.FillType = xlsModule.ShapeFillType.SolidColor;
serie.Format.Fill.ForeColor = xlsModule.Color.FromArgb(255, 46, 125, 91);

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.

Worksheet data is rarely a tidy rectangle. Detail rows are interrupted by subtotal lines, group headings and notes, and the chart is meant to draw the detail alone; other numbers never reach a cell at all, because they come back from an API, are computed in code, or are simply a set of targets for this one report. Both cases defeat the usual routine of selecting a block and inserting a chart. Spire.XLS for JavaScript does this in the browser on top of WebAssembly, managing input and output files through a virtual file system (VFS), with no backend service required.

This article covers two key features:

• Create a chart from a discontinuous data source
• Create a chart without a data source

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialised.

Create a chart from a discontinuous data source

Subtotal rows, group headings and notes all cut a table of details into several blocks. Selecting the whole column and inserting a chart makes no distinction between them and the detail rows, so they are drawn as columns too: in the sample data a subtotal follows every two quarters, and reading the whole column turns six columns into nine and roughly doubles the height of every region. A series can be given its data as several non-adjacent blocks joined into a single reference, so the chart takes only the rows inside those blocks and skips over the rest. The table then needs no rearranging for the sake of the chart, and the subtotal rows can stay where they are. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook with workbook.LoadFromFile and get the first worksheet with workbook.Worksheets.get(0).
3. Add a column chart with sheet.Charts.Add and set chart.SeriesDataFromRange to false, declaring that the series data is supplied block by block in code rather than taken from chart.DataRange.
4. Add a series with chart.Series.Add and set serie.Name to the Value of the header cell.
5. Take each region's quarter rows with Range.get and join them with AddCombinedRange, producing one reference for the category labels and one for the values.
6. Repeat the same joins for the second series so both share the same category labels, then save the workbook.

The complete code example below shows how to create a chart from a discontinuous data source in React:

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

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

    // Load the font and the sample workbook into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'ChartSourceData.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);

    // Add a column chart; the series data is given block by block in code, not as one range
    const chart = sheet.Charts.Add({ chartType: xlsModule.ExcelChartType.ColumnClustered });
    chart.ChartTitle = "Quarterly Sales by Region";
    chart.ChartTitleArea.Size = 12;
    chart.SeriesDataFromRange = false;

    // Place the chart on the worksheet
    chart.TopRow = 12;
    chart.BottomRow = 28;
    chart.LeftColumn = 1;
    chart.RightColumn = 10;

    // Join the three regions' quarter rows into one reference, skipping the subtotal rows
    const categoryLabels = sheet.Range.get("A2:A3")
      .AddCombinedRange(sheet.Range.get("A5:A6"))
      .AddCombinedRange(sheet.Range.get("A8:A9"));

    // Online series: the name comes from the header, the values are joined the same way
    const onlineSerie = chart.Series.Add();
    onlineSerie.Name = sheet.Range.get("B1").Value;
    onlineSerie.CategoryLabels = categoryLabels;
    onlineSerie.Values = sheet.Range.get("B2:B3")
      .AddCombinedRange(sheet.Range.get("B5:B6"))
      .AddCombinedRange(sheet.Range.get("B8:B9"));

    // In-store series: it shares the same category labels
    const storeSerie = chart.Series.Add();
    storeSerie.Name = sheet.Range.get("C1").Value;
    storeSerie.CategoryLabels = categoryLabels;
    storeSerie.Values = sheet.Range.get("C2:C3")
      .AddCombinedRange(sheet.Range.get("C5:C6"))
      .AddCombinedRange(sheet.Range.get("C8:C9"));

    // Save the workbook
    const outputFileName = "DiscontinuousData.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // 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>Discontinuous Data Chart</h1>
      <button id="discontinuous-data" onClick={chartFromDiscontinuousData}>Create a chart from a discontinuous source</button>
    </div>
  );
}

export default App;

After running, the chart created from a discontinuous data source:

Create a chart without a data source

A chart normally takes its data from cells, but not always: a target may live in a configuration file, a summary figure may come back from an API, or the numbers may just be a set of constants for a demonstration. None of them has landed in the worksheet, so there is no range for the chart to point at. A series can carry its values inside itself instead, which lets the chart stand without any worksheet data behind it; the numbers follow the code and are regenerated with it, so no copy has to be maintained in the sheet for the sake of the chart. Because the values come from nowhere on the sheet, the chart carries no text categories either, and the horizontal axis is numbered 1, 2, 3. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook with workbook.LoadFromFile and get the first worksheet with workbook.Worksheets.get(0); the new chart still has to hang on an existing worksheet.
3. Add a column chart with sheet.Charts.Add, and set the chart title and position.
4. Add a series with chart.Series.Add and set serie.Name to the series name.
5. Box each number with xlsModule.Int32.Create, put them in order into serie.EnteredDirectlyValues, and save the workbook.

The complete code example below shows how to create a chart without a data source in React:

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

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

    // Load the font and the sample workbook into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'ChartSourceData.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);

    // Add a column chart; this time the numbers come from no cell at all
    const chart = sheet.Charts.Add({ chartType: xlsModule.ExcelChartType.ColumnClustered });
    chart.ChartTitle = "Sales Target by Region";
    chart.ChartTitleArea.Size = 12;

    // Place the chart on the worksheet
    chart.TopRow = 12;
    chart.BottomRow = 28;
    chart.LeftColumn = 1;
    chart.RightColumn = 10;

    // Add a series and write the numbers into EnteredDirectlyValues one by one
    // No category labels are given, so the chart numbers them 1, 2, 3
    const targetSerie = chart.Series.Add();
    targetSerie.Name = "Sales Target";
    targetSerie.EnteredDirectlyValues = [
      xlsModule.Int32.Create(260),
      xlsModule.Int32.Create(210),
      xlsModule.Int32.Create(190),
    ];

    // Save the workbook
    const outputFileName = "NoSourceData.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // 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>Chart Without a Data Source</h1>
      <button id="no-source-data" onClick={chartWithoutSourceData}>Create a chart without a data source</button>
    </div>
  );
}

export default App;

After running, the chart created without a data source:

FAQ

Does the series name have to point at a cell?

Cause: serie.Name takes a plain string. Whatever you assign becomes the series name and is what the legend shows; after saving it sits in the series itself rather than in a reference to a cell. Reading the Value of a header cell is just another way to get there, not a requirement. The values and the category labels still have to come from a range — only the name can stand free of one.

Solution: assign the text straight to serie.Name:

const serie = chart.Series.Add();
serie.Name = "Sales Target";
serie.CategoryLabels = sheet.Range.get("A2:A3").AddCombinedRange(sheet.Range.get("A5:A6")).AddCombinedRange(sheet.Range.get("A8:A9"));
serie.Values = sheet.Range.get("B2:B3").AddCombinedRange(sheet.Range.get("B5:B6")).AddCombinedRange(sheet.Range.get("B8:B9"));

Do both series need their own category labels?

Cause: CategoryLabels belongs to the series itself. When a second series is added it does not pick up the setting from the one before it, so it has to be assigned again.

Solution: join the blocks once, keep the result in a variable and point each series at it, instead of writing AddCombinedRange out twice:

const labels = sheet.Range.get("A2:A3")
  .AddCombinedRange(sheet.Range.get("A5:A6"))
  .AddCombinedRange(sheet.Range.get("A8:A9"));

onlineSerie.CategoryLabels = labels;
storeSerie.CategoryLabels = labels;

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.

When a chart is inserted, Excel determines a scale automatically: the lower and upper bounds of the value axis and its intervals are all derived from the data range. These defaults are adequate in most cases, but they fall short in two situations. First, when the data is concentrated in a narrow band that sits well away from zero, the automatic scale lifts the bottom of the axis to just below the data, so a modest fluctuation is drawn as a near full-height swing and the differences between the columns bear no proportion to the differences between the values. Second, because the tick labels follow the formatting of the source cells, values carrying decimals fill the value axis with fractional digits, which are both harder to read and wider on the page. Axis formatting addresses both: setting the scale and the units keeps the axis from shifting with the data, holds the column heights in proportion to the values, and places every chart on the same measure, choosing the tick marks and the label position controls where the readings fall, rewriting the number format makes the labels easier to compare, and adding titles to both axes tells the reader what each direction represents. Spire.XLS for JavaScript does all of this in the browser on top of WebAssembly, managing input and output files through a virtual file system (VFS), with no backend service required.

This article covers two key features:

• Format the value axis scale
• Add axis titles

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialised.

Format the value axis scale

An automatic scale is derived by Excel from the data range, and it is chosen to make the chart look even rather than to make the readings easy to compare. When the data is concentrated in a narrow band that sits well away from zero, that derivation lifts the bottom of the axis to just below the data: with the axis no longer starting at 0, the columns only show their differences from one another, and a modest fluctuation is drawn as a near full-height swing, out of proportion to the values behind it. The tick labels, too, follow the display format of the source cells by default, so data carrying decimals produces a run of fractional values along the value axis, which is both harder to read and wider on the page. Setting the value axis scale by hand addresses both at once: once the scale and the intervals are given by the code, the bottom and top of the axis no longer shift with the data, the column heights stay in proportion to the values, and a set of charts can be measured against the same ruler; once the label format is independent of the source cells, the readings are more even as well. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook with workbook.LoadFromFile and get the first worksheet with workbook.Worksheets.get(0).
3. Add a column chart with sheet.Charts.Add, point chart.DataRange at the sales column, and assign the month column to serie.CategoryLabels.
4. On chart.PrimaryValueAxis, set the scale with MinValue and MaxValue, and the steps of the major and minor divisions with MajorUnit and MinorUnit.
5. Set the types of the major and minor tick marks with MajorTickMark and MinorTickMark, and the position of the tick labels with TickLabelPosition.
6. Set NumberFormat to #,##0 for the label number format, set IsSourceLinked to false to unlink the format from the source cells, and save the workbook.

The complete code example below shows how to format the value axis scale in React:

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

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

    // Load the font and the sample workbook into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'ChartAxisData.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);

    // Add a column chart whose data range is the sales column
    const chart = sheet.Charts.Add({ chartType: xlsModule.ExcelChartType.ColumnClustered });
    chart.ChartTitle = "Monthly Sales";
    chart.ChartTitleArea.IsBold = true;
    chart.ChartTitleArea.Size = 12;
    chart.DataRange = sheet.Range.get("B1:B9");
    chart.SeriesDataFromRange = false;
    chart.PlotArea.Visible = false;

    // Place the chart on the worksheet
    chart.TopRow = 10;
    chart.BottomRow = 28;
    chart.LeftColumn = 2;
    chart.RightColumn = 10;

    // Take the series and use the month column as its category labels
    const serie = chart.Series.get(0);
    serie.CategoryLabels = sheet.Range.get("A2:A9");

    // Set the value axis bounds: 0 at the bottom, 5000 at the top
    const valueAxis = chart.PrimaryValueAxis;
    valueAxis.MinValue = 0;
    valueAxis.MaxValue = 5000;

    // Set the major unit to 1000 and the minor unit to 500
    valueAxis.MajorUnit = 1000;
    valueAxis.MinorUnit = 500;

    // Major tick marks outside the axis, minor tick marks inside
    valueAxis.MajorTickMark = xlsModule.TickMarkType.TickMarkOutside;
    valueAxis.MinorTickMark = xlsModule.TickMarkType.TickMarkInside;

    // Tick labels sit next to the axis, which the category axis crosses at 0
    valueAxis.TickLabelPosition = xlsModule.TickLabelPositionType.TickLabelPositionNextToAxis;
    valueAxis.CrossesAt = 0;

    // Give the tick labels a thousands separator and drop the decimals
    valueAxis.NumberFormat = "#,##0";

    // Unlink the source format so the tick labels follow NumberFormat
    valueAxis.IsSourceLinked = false;

    // Save the workbook
    const outputFileName = "AxisFormat.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // 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>Value Axis Scale</h1>
      <button onClick={formatValueAxis}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of formatting the value axis scale:

Add axis titles

The axes carry only their own divisions and categories, so the chart itself does not state what each data point represents or what unit the numbers are in; opening the file again after some time, a reader often has to check the chart title first to confirm the subject. Axis titles supply precisely that context: the category axis title states what each data point represents, the value axis title states the unit of the numbers, and together with the chart title they complete the chart so that it can be read without its surrounding context. The size of an axis title can also be adjusted separately, which keeps it distinct from the chart title. The steps are:

1. Load the font and the test data file into the VFS.
2. Load the workbook with workbook.LoadFromFile and get the first worksheet with workbook.Worksheets.get(0).
3. Add a column chart with sheet.Charts.Add, point chart.DataRange at the sales column, and assign the month column to serie.CategoryLabels.
4. Assign the title text to chart.PrimaryCategoryAxis.Title and chart.PrimaryValueAxis.Title respectively.
5. Set both titles to size 12 through the Font.Size of the two axis objects, and save the workbook.

The complete code example below shows how to add titles to the axes in React:

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

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

    // Load the font and the sample workbook into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'ChartAxisData.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);

    // Add a column chart whose data range is the sales column
    const chart = sheet.Charts.Add({ chartType: xlsModule.ExcelChartType.ColumnClustered });
    chart.ChartTitle = "Monthly Sales";
    chart.ChartTitleArea.IsBold = true;
    chart.ChartTitleArea.Size = 12;
    chart.DataRange = sheet.Range.get("B1:B9");
    chart.SeriesDataFromRange = false;
    chart.PlotArea.Visible = false;

    // Place the chart on the worksheet
    chart.TopRow = 10;
    chart.BottomRow = 28;
    chart.LeftColumn = 2;
    chart.RightColumn = 10;

    // Take the series and use the month column as its category labels
    const serie = chart.Series.get(0);
    serie.CategoryLabels = sheet.Range.get("A2:A9");

    // Give the category axis and the value axis their titles
    chart.PrimaryCategoryAxis.Title = "Month";
    chart.PrimaryValueAxis.Title = "Sales (CNY)";

    // Set both axis titles to size 12
    chart.PrimaryCategoryAxis.Font.Size = 12;
    chart.PrimaryValueAxis.Font.Size = 12;

    // Save the workbook
    const outputFileName = "AxisTitle.xlsx";
    workbook.SaveToFile({ fileName: outputFileName });

    // 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>Axis Titles</h1>
      <button onClick={setAxisTitle}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of adding axis titles:

FAQ

MinorUnit is set but no minor tick marks show up

Cause: MinorUnit determines only the interval of the minor divisions — how many steps each major division is split into. Whether those steps are drawn as short strokes is decided by a separate property. MinorTickMark defaults to TickMarkNone, and in that state the interval is in effect but no strokes are drawn.

Fix: Set MinorTickMark at the same time, taking its value from TickMarkType:

const valueAxis = chart.PrimaryValueAxis;
valueAxis.MinorUnit = 500;
valueAxis.MinorTickMark = xlsModule.TickMarkType.TickMarkInside;

CrossesAt is set but the category axis does not move

Cause: CrossesAt determines where the category axis meets the value axis, and the move becomes visible only when it is set to a position on the scale other than the one the category axis already occupies. With the value axis starting at 0, the category axis already sits at the bottom of the axis line, so setting CrossesAt to 0 leaves it in place.

Fix: Set CrossesAt to another position on the value axis scale, 3000 for example, and the category axis moves up to that level:

const valueAxis = chart.PrimaryValueAxis;
valueAxis.MinValue = 0;
valueAxis.MaxValue = 5000;
valueAxis.CrossesAt = 3000;

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.

A workbook exported by a reporting system usually carries nothing but the system name as its author, with the title, subject and keywords left blank; teams meanwhile need to attach fields the export does not produce — an export batch, an owner, an approval state — for archiving and searching. None of this takes up a cell: it all lives in the workbook's document properties, which Excel shows under "File > Info > Properties". Spire.XLS for JavaScript writes them in the browser on top of WebAssembly, using a virtual file system (VFS) for input and output files, so no back-end service is needed.

This article covers three feature points:

• Set the summary properties of a workbook
• Add custom properties
• Update the value of a custom property

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialised.

Set the summary properties of a workbook

The summary properties are the layer of description Excel shows in File Explorer and under "File > Info", and the first thing a search of the archive picks up. A workbook produced by a program usually has only the system name as its author and leaves the other entries blank; filling them in gives the file a readable context as it moves between people. The text entries take a plain assignment, while the two date entries have to be given a date object. The steps are:

1. Load the workbook and take the summary property collection from workbook.DocumentProperties.
2. Assign the text entries — Title, Subject, Author, Keywords, Comments and Category — directly.
3. Assign Company and Manager the same way; Excel carries these two in its extended properties.
4. Set CreatedTime and LastSaveTime to Date objects.
5. Save the workbook.

Here is a complete code example showing how to set the summary properties of a workbook in React:

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

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

    // Load the font into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Load the Excel file into the VFS
    const inputFileName = 'WorkbookProperties.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile(inputFileName);

    // Set the text entries of the summary properties
    const summary = workbook.DocumentProperties;
    summary.Title = 'Q3 2026 Sales Report';
    summary.Subject = 'Quarterly results by sales department';
    summary.Author = 'E-iceblue';
    summary.Keywords = 'sales, report, Excel';
    summary.Comments = 'Summary filled in after the export';
    summary.Category = 'Sales Report';

    // Set the entries carried by the extended properties
    summary.Company = 'E-iceblue';
    summary.Manager = 'Sales Manager';

    // Set the document dates; a Date object is required here
    summary.CreatedTime = new Date(2026, 8, 1);
    summary.LastSaveTime = new Date(2026, 8, 20);

    // Save the workbook
    const outputFileName = 'SetSummaryProperties.xlsx';
    workbook.SaveToFile(outputFileName);

    // Release resources
    workbook.Dispose();

    // Read the result file back out of the VFS and download it
    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>Set workbook summary properties</h1>
      <button onClick={setSummaryProperties}>Start</button>
    </div>
  );
}

export default App;

The effect of setting the summary properties of a workbook:

Add custom properties

Custom properties carry the business fields the summary properties have no room for, such as an export batch, a contact phone number, a revision number or an approval date. Unlike the summary properties they have no fixed set of entries: the caller decides the name and the type, and Excel accepts text, integer, decimal, boolean and date-and-time values. The steps are:

1. Load the workbook and take the custom property collection from workbook.CustomDocumentProperties.
2. Call Add to append a property; the name and the value can be written as a named object such as { strName, boolValue }, or passed directly as two arguments.
3. Pick the member that matches the type of the value: intValue for an integer, dblValue for a decimal.
4. Pass a Date object as dtValue for a date-and-time value.
5. Save the workbook.

Here is a complete code example showing how to add custom properties to a workbook in React:

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

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

    // Load the font into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Load the Excel file into the VFS
    const inputFileName = 'WorkbookProperties.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile(inputFileName);

    // Add a boolean property; _MarkAsFinal marks the document as final
    workbook.CustomDocumentProperties.Add({ strName: '_MarkAsFinal', boolValue: true });

    // Add a text property; a name and a value can also be passed directly
    workbook.CustomDocumentProperties.Add('The Editor', 'E-iceblue');

    // Add an integer property
    workbook.CustomDocumentProperties.Add({ strName: 'Phone number', intValue: 81705109 });

    // Add a decimal property
    workbook.CustomDocumentProperties.Add({ strName: 'Revision number', dblValue: 7.12 });

    // Add a date and time property
    workbook.CustomDocumentProperties.Add({ strName: 'Revision date', dtValue: new Date(2026, 8, 1) });

    // Save the workbook
    const outputFileName = 'AddCustomProperties.xlsx';
    workbook.SaveToFile(outputFileName);

    // Release resources
    workbook.Dispose();

    // Read the result file back out of the VFS and download it
    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>Add custom properties</h1>
      <button onClick={addCustomProperties}>Start</button>
    </div>
  );
}

export default App;

The effect of adding custom properties:

Update the value of a custom property

The value of a business field changes with the way it is counted: an export record count, say, has to be rewritten to a new figure once the data is topped up. Custom properties offer no member that assigns a value directly, but Add is keyed on the name — calling it again for a name that already exists does not append a duplicate entry, it replaces the value of the existing one. The steps are:

1. Load the workbook and take the custom property collection from workbook.CustomDocumentProperties.
2. Call Add again, passing the same name as the existing entry together with the new value.
3. Save the workbook.

Here is a complete code example showing how to update a custom property of a workbook in React:

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

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

    // Load the font into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Load the Excel file into the VFS
    const inputFileName = 'WorkbookProperties.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile(inputFileName);

    // Rewrite the exported record count; adding an existing name overwrites it
    workbook.CustomDocumentProperties.Add({ strName: 'ExportedRecords', intValue: 256 });

    // Save the workbook
    const outputFileName = 'UpdateCustomProperties.xlsx';
    workbook.SaveToFile(outputFileName);

    // Release resources
    workbook.Dispose();

    // Read the result file back out of the VFS and download it
    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>Update the value of a custom property</h1>
      <button onClick={updateCustomProperties}>Start</button>
    </div>
  );
}

export default App;

The effect of updating the value of a custom property:

FAQ

Why does assigning to CreatedTime throw "Assert failed: Value is not a Date"

Cause: CreatedTime and LastSaveTime take a JavaScript Date object and nothing else. A date string such as '2026-09-01', a timestamp number, or a hand-made object that only carries a toISOString() method all fail the type check and throw Assert failed: Value is not a Date.

Solution: build the Date with new Date(...) first, then assign it:

// 1 September 2026; months count from 0, so 8 means September
workbook.DocumentProperties.CreatedTime = new Date(2026, 8, 1);

Why does assigning to the Value of a custom property throw ArgumentNull_Generic

Cause: the Value property only reads. Assigning to it throws ArgumentNull_Generic Arg_ParamName_Name, value, and the original value does not change. Updating an existing property has to go through Add.

Solution: overwrite the original value with a same-name Add:

workbook.CustomDocumentProperties.Add({ strName: 'ExportedRecords', intValue: 256 });

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.

The real dividers in a spreadsheet are not blank cells but borders: financial reports use lines of different weights to separate the header row from the totals, while data handed to a downstream system has to go out with the frames stripped off. Doing that by hand — selecting each block and opening Format Cells — is slow and hard to repeat. Spire.XLS for JavaScript does the same work in the browser on top of WebAssembly, using a virtual file system (VFS) for input and output files, so no back-end service is needed.

This article covers four feature points:

• Add a border to a selected cell or a cell range
• Add a border to the range that holds the data
• Add left, top, right, bottom and diagonal borders to a cell
• Remove the borders of a cell or a cell range

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has been initialised.

Add a border to a selected cell or a cell range

Given a table with no lines at all, the most direct approach is to put the frame back with BorderAround and BorderInside: the first one draws the outline, the second one the grid lines inside the range. Together they frame a whole block of data in one go, and they can also single out one cell — the header cell, say — so that it stands out from a sheet full of identical lines.

The steps are:

1. Take the cell range you want to frame with Range.get, by address
2. Call BorderAround to draw the outline, with the line style taken from the LineStyleType enumeration
3. Call BorderInside to add the separators between the cells inside the range
4. Take the header cell on its own and call BorderAround again on it, this time with a medium line

The line style is passed in object notation, as { borderLine }.

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

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

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

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

    // Take the B2:E6 range, frame it with thin lines and add thin inner lines
    const dataRange = sheet.Range.get('B2:E6');
    dataRange.BorderAround({ borderLine: xlsModule.LineStyleType.Thin });
    dataRange.BorderInside({ borderLine: xlsModule.LineStyleType.Thin });

    // Give the B2 header cell a medium border on all four sides
    sheet.Range.get('B2').BorderAround({ borderLine: xlsModule.LineStyleType.Medium });

    // Save the result file
    const outputFileName = 'AddBorderToCells.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

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

    // Read the result file back out of the VFS and download it
    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>Add or Remove Cell Borders</h1>
      <button onClick={addBorderToCells}>Add a border to a cell or range</button>
    </div>
  );
}

export default App;

The effect of adding a border to the data range and to the header cell:

Add a border to the range that holds the data

The previous section hard-codes the address B2:E6, which has to be edited as soon as rows or columns are added to the table. The AllocatedRange property returns the allocated range of a worksheet — the rectangle that actually holds the data — so the address maintains itself, and the outline can be given a different style from the header frame to set the table apart from the text around it.

The steps are:

1. Get the range that holds the data through AllocatedRange, with no address to maintain
2. Call BorderAround with a medium dashed line, so the outline contrasts with the solid frames used elsewhere
3. Call BorderInside to add thin lines inside the range, keeping the rows separated

function App() {
  const addBorderToDataRange = async () => {
    const xlsModule = window.wasmModule?.spirexls;
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'CellBorders.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // AllocatedRange returns the range that holds the data, so no address is needed
    const dataRange = sheet.AllocatedRange;

    // Give the outline a medium dashed border and the inside thin lines
    dataRange.BorderAround({ borderLine: xlsModule.LineStyleType.MediumDashed });
    dataRange.BorderInside({ borderLine: xlsModule.LineStyleType.Thin });

    const outputFileName = 'AddBorderToDataRange.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });
    workbook.Dispose();

    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>Add or Remove Cell Borders</h1>
      <button onClick={addBorderToDataRange}>Add a border to the data range</button>
    </div>
  );
}

export default App;

The effect of adding a dashed outline and thin inner lines to the data range:

Add left, top, right, bottom and diagonal borders to a cell

BorderAround gives all four edges the same style, which is not enough when the requirement is "a thick red line on the left and a double line along the bottom". Handling the edges one at a time solves it: Borders.get takes a single edge by its BordersLineType member, and its LineStyle and Color can then be set independently, so every edge can have its own line style and colour. The two diagonal directions are available the same way.

The steps are:

1. Take the cell to style with Range.get
2. Take the left, top, right and bottom edges through Borders.get(BordersLineType.EdgeLeft) and its siblings
3. Set LineStyle on each edge, using a thick line, a dotted line, a slanted dash-dot line and a double line
4. Set Color on each edge, using red, brown, dark grey and orange red
5. Take another cell, fetch its diagonal-down edge with BordersLineType.DiagonalDown and set its line style

function App() {
  const addEdgeBorders = async () => {
    const xlsModule = window.wasmModule?.spirexls;
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'CellBorders.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });
    const sheet = workbook.Worksheets.get(0);

    // Give each of the four edges of B4 its own line style and colour
    const cell = sheet.Range.get('B4');
    const edgeSpecs = [
      ['EdgeLeft', xlsModule.LineStyleType.Thick, xlsModule.Color.get_Red()],
      ['EdgeTop', xlsModule.LineStyleType.Dotted, xlsModule.Color.get_Brown()],
      ['EdgeRight', xlsModule.LineStyleType.SlantedDashDot, xlsModule.Color.get_DarkGray()],
      ['EdgeBottom', xlsModule.LineStyleType.Double, xlsModule.Color.get_OrangeRed()],
    ];
    for (const [edge, lineStyle, color] of edgeSpecs) {
      const border = cell.Borders.get(xlsModule.BordersLineType[edge]);
      border.LineStyle = lineStyle;
      border.Color = color;
    }

    // Add a diagonal-down border to E6
    const diagonal = sheet.Range.get('E6').Borders.get(xlsModule.BordersLineType.DiagonalDown);
    diagonal.LineStyle = xlsModule.LineStyleType.Thin;

    const outputFileName = 'AddEdgeBorders.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });
    workbook.Dispose();

    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>Add or Remove Cell Borders</h1>
      <button onClick={addEdgeBorders}>Set the four edges and a diagonal</button>
    </div>
  );
}

export default App;

The effect of styling the four edges of a cell and adding a diagonal line:

Remove the borders of a cell or a cell range

When an internal report goes out to a customer, or its data is fed to a downstream system, borders are often just noise: they make the data look like a finished report, and a parser that walks the sheet region by region can pick them up as content. Removing them is as simple as setting them — assign LineStyleType.None to the range's Borders.LineStyle and every line inside it goes at once.

The steps are:

1. Take the worksheet that holds the framed report
2. Take the cell range to clean up with Range.get
3. Set the range's Borders.LineStyle to LineStyleType.None, clearing every border inside it

function App() {
  const removeBorders = async () => {
    const xlsModule = window.wasmModule?.spirexls;
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'CellBorders.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: inputFileName });

    // The second worksheet holds the same report, already framed with borders
    const sheet = workbook.Worksheets.get(1);

    // Set every border in the B2:E6 range to None
    sheet.Range.get('B2:E6').Borders.LineStyle = xlsModule.LineStyleType.None;

    const outputFileName = 'RemoveBorders.xlsx';
    workbook.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });
    workbook.Dispose();

    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>Add or Remove Cell Borders</h1>
      <button onClick={removeBorders}>Remove cell borders</button>
    </div>
  );
}

export default App;

The effect of clearing every border inside the range:

FAQ

Setting a top edge on a whole range puts a line above every row

Cause: Borders.get(BordersLineType.EdgeTop) returns the top edge of every cell in the range, not the outline of the range itself. Used on B2:E6 as it stands, every row inside the range gets a top edge of its own, so four extra lines appear in the middle of the table.

Solution: narrow the range to a single row or column, and the edge that comes back sits on the outline. Take the top from the first row, the bottom from the last row, the left from the first column and the right from the last column:

// Top: the first row only
sheet.Range.get('B2:E2').Borders.get(xlsModule.BordersLineType.EdgeTop).LineStyle = xlsModule.LineStyleType.Thin;
// Bottom: the last row only
sheet.Range.get('B6:E6').Borders.get(xlsModule.BordersLineType.EdgeBottom).LineStyle = xlsModule.LineStyleType.Thin;
// Left: the first column only
sheet.Range.get('B2:B6').Borders.get(xlsModule.BordersLineType.EdgeLeft).LineStyle = xlsModule.LineStyleType.Thin;
// Right: the last column only
sheet.Range.get('E2:E6').Borders.get(xlsModule.BordersLineType.EdgeRight).LineStyle = xlsModule.LineStyleType.Thin;

BorderInside throws when it is called on a single cell

Cause: BorderInside means "the separators inside the range", and a single cell has no inside, so passing one throws This method doesn't support for single cell. No result file is written either.

Solution: use BorderAround to frame a single cell, or set its edges one at a time as in the previous section:

sheet.Range.get('B2').BorderAround({ borderLine: xlsModule.LineStyleType.Thin });

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.

Contracts, quotations and slide decks are often embedded straight into an Excel workbook: what you see on the sheet is an icon or a thumbnail, while the real document data sits in the xl/embeddings part of the package. Pulling those attachments out for archiving one by one means opening each object and saving a copy by hand.Spire.XLS for JavaScript does the job in the browser through WebAssembly, using a virtual file system (VFS) for the input and the output, with no backend service involved.

This article covers two feature points:

• Read the OLE object information of a worksheet
• Extract OLE object payloads by type

For installation and project configuration, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is installed and the WebAssembly module has finished initializing.

Read the OLE object information of a worksheet

Before extracting anything it helps to know what a workbook actually carries. A listing sets out the kind, original name, cell and size of every embedded object in one place and serves as an attachment register, so archiving or handing the file over does not mean double-clicking each icon in turn.

The steps are:

1. Use HasOleObjects to check whether the worksheet holds any embedded objects, and stop early if it does not
2. Walk every object of the worksheet through the OleObjects collection
3. Read four pieces of information off each object:
   • ObjectType: the kind of object, such as WordDocument or PowerPointPresentation
   • OleOriginName: the file name the object had before it was embedded
   • Location: the cell the object sits on
   • the length of OleData: the data size
4. Join the four fields into one line per object and write the listing to ListOleObjects.txt

The example below collects those fields into a plain text listing:

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

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

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

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

    // Check whether the worksheet contains OLE objects
    if (!sheet.HasOleObjects) {
      alert('The worksheet contains no OLE objects.');
      return;
    }

    // Collect the listing as text: a header line, then one tab-separated line per object
    const lines = [['No.', 'Object Type', 'Original File Name', 'Location', 'Data Size (bytes)'].join('\t')];

    // Walk the OLE objects of the worksheet, one line per object
    let index = 1;
    for (const oleObject of sheet.OleObjects) {
      lines.push([
        index,
        String(oleObject.ObjectType),
        String(oleObject.OleOriginName),
        String(oleObject.Location.RangeAddress),
        oleObject.OleData.length,
      ].join('\t'));
      index += 1;
    }

    // Release the workbook object
    workbook.Dispose();

    // Write the listing to a text file in the VFS
    const outputFileName = 'ListOleObjects.txt';
    window.dotnetRuntime.Module.FS.writeFile(outputFileName, lines.join('\n'));

    // Read the result file from VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'text/plain;charset=utf-8' });
    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>List OLE Objects</h1>
      <button onClick={listOleObjects}>List OLE objects</button>
    </div>
  );
}

export default App;

The effect of reading the OLE object information:

Extract OLE object payloads by type

A listing only says what is embedded; archiving the attachment itself means getting the document out. An OLE object carries its payload as a byte array, so writing those bytes out restores the document exactly as it was embedded, with no re-typesetting or format conversion.

The steps are:

1. Use HasOleObjects to confirm the worksheet holds embedded objects
2. Walk every object in the OleObjects collection and decide the name and MIME type of the output file from ObjectType
3. Take the raw bytes of the object through OleData, write them into the virtual file system, read them back and wrap them in a downloadable Blob
4. Dispose of the workbook once the walk is done, then trigger the downloads one by one

The example below walks every object of the worksheet and writes the Word, PowerPoint and PDF attachments out as .docx, .pptx and .pdf files, offering each one for download:

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

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

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

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

    // Collect the extracted files so the page can offer them for download
    const results = [];

    // Walk the OLE objects of the worksheet and write each one out in its own format
    if (sheet.HasOleObjects) {
      for (const oleObject of sheet.OleObjects) {
        const type = oleObject.ObjectType;
        let outputFileName = '';
        let mimeType = '';

        // Word document
        if (type === xlsModule.OleObjectType.WordDocument) {
          outputFileName = 'ExtractWord.docx';
          mimeType = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document';
        }
        // PowerPoint presentation: .pptx and .sldx fall under two different enum members
        else if (
          type === xlsModule.OleObjectType.PowerPointPresentation ||
          type === xlsModule.OleObjectType.PowerPointSlide
        ) {
          outputFileName = 'ExtractPowerPoint.pptx';
          mimeType = 'application/vnd.openxmlformats-officedocument.presentationml.presentation';
        }
        // PDF document
        else if (type === xlsModule.OleObjectType.AdobeAcrobatDocument) {
          outputFileName = 'ExtractPdf.pdf';
          mimeType = 'application/pdf';
        }

        // Any other type is left alone
        if (!outputFileName) continue;

        // Write the raw data of the object into the virtual file system
        window.dotnetRuntime.Module.FS.writeFile(outputFileName, oleObject.OleData);
        const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
        results.push({
          name: outputFileName,
          url: URL.createObjectURL(new Blob([fileArray], { type: mimeType })),
        });
      }
    }

    // Release the workbook object
    workbook.Dispose();

    // Trigger the downloads one by one
    results.forEach(({ name, url }) => {
      const a = document.createElement('a');
      a.href = url;
      a.download = name;
      a.click();
      URL.revokeObjectURL(url);
    });
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Extract OLE Objects</h1>
      <button onClick={extractOleObjects}>Extract OLE objects by type</button>
    </div>
  );
}

export default App;

The effect of extracting the attachment documents:

FAQ

OleObjectType.PowerPointSlide never matches a PowerPoint attachment

Cause: OleObjectType splits objects by the real format of the embedded file. A .pptx presentation reports PowerPointPresentation; only slideshow formats such as .sldx and .ppt land on PowerPointSlide, so testing for the latter alone misses the vast majority of PowerPoint attachments.

Solution: match both enum members in the branch, for example:

else if (
  type === xlsModule.OleObjectType.PowerPointPresentation ||
  type === xlsModule.OleObjectType.PowerPointSlide
) {
  outputFileName = 'ExtractPowerPoint.pptx';
}

Only the attachments of the first worksheet come out

Cause: OleObjects is a per-worksheet collection. The example takes the first sheet through Worksheets.get(0) and walks the OleObjects of that sheet alone, so objects embedded on any other worksheet are never visited. When the attachments are spread over several sheets, the extraction silently comes up short, with no error to show for it.

Solution: walk the worksheets instead, taking the OleObjects of each one in turn:

for (let i = 0; i < workbook.Worksheets.Count; i++) {
  const worksheet = workbook.Worksheets.get(i);
  for (const oleObject of worksheet.OleObjects) {
    // write the attachment out by type
  }
}

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.